R:如何将两个 data.frame 合并为一个，匹配的 ID 重复自身，有时会丢失

Question

我有两台机器检查产品的不同方面。所以我有两组数据，两个系统生成 ID，但这些 ID 指的是接受检查的同一产品。每检查 10 件产品，两个系统中的 ID 就会自行重置。有时其中一个系统出错，没有 ID 或生成错误的 ID。所以数据集看起来像这样:

 data set 1:                         data set 2:
 timestamp1    ID1    value1         timestamp2    ID2   value2
 9:01 am        1        0.1             9:02 am    1      10
 9:03 am        2        0.8             9:04 am    2      15
 9:05 am        3        0.1             9:07 am    3      10
 9:09 am        4        0.1             9:11 am    0      n/a   <-system wrong
 9:11 am        5        0.1             9:15 am    6      17    ID 4&5 missing
 9:13 am        6        0.1             9:16 am    7      10

 ...................... .....data continues

 9:21 am        9        0.1             9:22 am     10     19
 9:22 am        10        0.1            9:23 am     1      10 <- ID back to 1
 9:23 am        1        0.1             9:24 am     2      10
 9:01 am        2        0.1             9:25 am     3      10

我想将两个数据集合并为一个数据集，这样我就可以获得两个系统对同一产品的检测值。预期的数据集应如下所示:

  timestamp1    ID1    value1    timestamp2  ID2   value2
  9:01 am        1        0.1      9:02 am    1      10
  9:03 am        2        0.8      9:04 am    2      15
  9:05 am        3        0.1      9:07 am    3      10
  9:09 am        4        0.1      9:11 am   n/a     n/a   <-system wrong
  9:11 am        5        0.1        n/a     n/a     n/a
  9:13 am        6        0.1      9:15 am    6      17
  9:14 am        7        0.1      9:16 am    7      10

   ..................data continues

  9:23 am        1        0.1      9:23 am    1      10 <- ID back to 1
  9:01 am        2        0.1      9:25 am    2      10

我尝试了几种合并功能，问题是 ID 每 10 个产品都会自行重置。没有匹配的唯一 ID，我从 merge() 函数得到的结果全乱了。真实数据集通常在每个生产运行中有 > 10,000 行数据。不可能逐行找出那些问题。任何人都可以提供帮助吗？谢谢。

Answer 1

这是我的方法，使用我生成的一些样本数据看起来像您的数据。在我的示例数据中，“n/a”被编码为 NA，因此如果您想完美匹配，我会将出现的“n/a”重新编码为缺失值 (NA)。

首先我加载包 dplyr 并创建一些示例数据:

library(dplyr)

### Create some example data ###
time <- Sys.time()
time_seq1 <- seq(time, time + 40, 2)
time_seq2 <- seq(time, time + 40, 3)

id <- rep(seq(1, 10, 1), 3)
id1 <- id[1:length(time_seq1)]
id2 <- id[1:length(time_seq2)]

df1 <- data.frame(time1 = time_seq1, 
                  ID1 = id1,
                  value1 = round(rnorm(length(time_seq1), 0.5, 0.2),1))

df2 <- data.frame(time2 = time_seq2, 
                  ID2 = id2,
                  value2 = round(rnorm(length(time_seq2), 15, 2),0))

df2[5, 2:3] <- NA
df2[6, ]    <- NA

第一个数据集的预览如下所示:

head(df1, 11)
                 time1 ID1 value1
1  2017-11-10 10:18:07   1    0.6
2  2017-11-10 10:18:09   2    0.4
3  2017-11-10 10:18:11   3    0.4
4  2017-11-10 10:18:13   4    0.7
5  2017-11-10 10:18:15   5    0.4
6  2017-11-10 10:18:17   6    0.1
7  2017-11-10 10:18:19   7    0.8
8  2017-11-10 10:18:21   8    0.5
9  2017-11-10 10:18:23   9    0.5
10 2017-11-10 10:18:25  10    0.7
11 2017-11-10 10:18:27   1    0.1

第二个:

head(df2, 11)
                 time2 ID2 value2
1  2017-11-10 10:18:07   1     14
2  2017-11-10 10:18:10   2     16
3  2017-11-10 10:18:13   3     15
4  2017-11-10 10:18:16   4     19
5  2017-11-10 10:18:19  NA     NA
6                 <NA>  NA     NA
7  2017-11-10 10:18:25   7     17
8  2017-11-10 10:18:28   8     14
9  2017-11-10 10:18:31   9     13
10 2017-11-10 10:18:34  10     13
11 2017-11-10 10:18:37   1     14

现在我使用 dplyr 创建滞后变量，但您可以使用其他函数来创建滞后变量。因为第二个数据集中只有缺失值，所以我只将这些步骤应用于第二个数据集，尽管您也可以为第一个数据集复制它。

此外，当您的数据中存在两个连续缺失值时，滞后变量将无法完全发挥作用。我添加了一个“while 循环”来运行该过程，直到所有缺失都得到处理。

最后，我创建了唯一 ID 并将它们合并在一起。

### Add lag variable to create unique ID ###
while (sum(is.na(df2$ID2)) > 0) {
df2 <- df2 %>% 
  mutate(ID2_lag = lag(ID2)) %>% 
  mutate(ID2 = ifelse(is.na(ID2), ID2_lag + 1, ID2)) %>% 
  select(-ID2_lag)
}

### Create unique ID
df1$ID <- cumsum(df1$ID1)
df2$ID <- cumsum(df2$ID2)

### Merge ###
df <- full_join(df1, df2) %>% 
  select(ID, ID1, ID2, time1, time2, value1, value2)

head(df, 11)
   ID ID1 ID2               time1               time2 value1 value2
1   1   1   1 2017-11-10 09:59:33 2017-11-10 09:59:33    0.4     15
2   3   2   2 2017-11-10 09:59:35 2017-11-10 09:59:36    0.7     15
3   6   3   3 2017-11-10 09:59:37 2017-11-10 09:59:39    0.6     18
4  10   4   4 2017-11-10 09:59:39 2017-11-10 09:59:42    0.5     18
5  15   5   5 2017-11-10 09:59:41 2017-11-10 09:59:45    0.3     NA
6  21   6   6 2017-11-10 09:59:43                <NA>    0.9     NA
7  28   7   7 2017-11-10 09:59:45 2017-11-10 09:59:51    0.6     14
8  36   8   8 2017-11-10 09:59:47 2017-11-10 09:59:54    0.6     16
9  45   9   9 2017-11-10 09:59:49 2017-11-10 09:59:57    0.5     13
10 55  10  10 2017-11-10 09:59:51 2017-11-10 10:00:00    0.8     13
11 56   1   1 2017-11-10 09:59:53 2017-11-10 10:00:03    0.5     15