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

Question

我让两台机器检查产品的不同方面。因此，我有两组数据，其中两个系统生成ID，但ID引用的是同一产品，正在接受检查。每检查10个产品，两个系统中的in就会自动重置。有时其中一个系统出错，没有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创建一个lag变量，但是您也可以使用其他函数来创建您的lag变量。因为在第二个数据集中只有未命中，所以我只将这些步骤应用于第二个数据集，尽管您也可以对第一个数据集进行复制。

此外，当数据中有两个连续的缺失值时，lag变量将不会完全工作。我添加了一个"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