R: how to merge two data.frame into one with matching IDs repeating itself and sometimes missing

user_8528710 0
Viewed 141

I have two machines inspect different aspects of a product. So I have two sets of data with two systems generating IDs but the IDs are referring to the same product getting inspected. And the IDs in both system reset themselves every 10 products inspected. And sometimes one of the systems went wrong and has no ID or wrong ID generated. So the data sets look like this:

 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

I want to merge the two data sets into one so I have both systems' inspection values for the same product. The expected data sets should look like this:

  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

I tried several merge functions, the problem is that the ID resets itself every 10 product. There is no unique ID to match, the results I got from merge() function is all messed up. The real data sets usually has > 10,000 lines of data per production run. It's impossible to pick out those problems line by line. Can any one give some help ? Thanks.

match dataframe statistics r merge
1 Answers

Here's my approach using some sample data I generated to look like yours. In my sample data, "n/a" is coded as NA, so if you want to match perfectly I would re-code occurrences of "n/a" to missing values (NA).

First I load the package dplyr and create some sample data:

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

A preview of the first dataset looks like this:

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

And the second:

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

Now I use dplyr to create a lag variable, but you could use other functions to create your lag variable. Because there are only missings in the second dataset, I only apply the steps to the second dataset, though you could replicate it for the first as well.

Also, the lag variable won't fully work when there are two consecutive missing values like you have in your data. I've added a "while loop" to run the process until all missings are taken care of.

Finally, I create the unique IDs and merge it all together. 

### 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
user_61066080
Related