Compare pairs of 2 data tables and correct the values in the first one based on the second one

Question

Let's have the following 2 data tables. 1st one is collected data with errors and the second one contains correct possible pairs for x1 and x2:

1st one:

+----------+-----+------+
|    x1    |  x2 |  x3  |
+----------+-----+------+
| march    |  3  |  198 |
| april    |  4  | 4984 |
| february |  2  |  498 |
| march    |  35 |  984 |
| aripl    |  4  |  498 |
+----------+-----+------+

2nd one:

+----------+----+
|    x1    | x2 |
+----------+----+
| january  |  1 |
| february |  2 |
| march    |  3 |
| april    |  4 |
| may      |  5 |
+----------+----+

I want to find incorrect rows in the first table and correct them based on the second one. So the output should look like this:

+----------+----+------+
|    x1    | x2 |  x3  |
+----------+----+------+
| march    |  3 |  198 |
| april    |  4 | 4984 |
| february |  2 |  498 |
| march    |  3 |  984 |
| april    |  4 |  498 |
+----------+----+------+

It should check for the x1 if it is the correct name and then add the correct number or if x2 is the correct number then add the correct name.

For the first part, I guess the answer is here. Although I need to fit it for my situation somehow (so any help with it would be also appreciated). For the second part, all I know is using "for" and "if" which is not acceptable (or even not possible) for the speed issues.

DanY · Accepted Answer · 2018-09-02T00:49:38.767

You can merge by name to find the correct number, and then you can merge by number to find the correct name.

Example data

library(data.table)

dt1 <- data.table(
    x1 = c("march", "april", "february", "march", "aripl"),
    x2 = c(3,4,2,35,4),
    x3 = c(198,4984,498,984,498)
)

dt2 <- data.table(
    x1 = c("january", "february", "march", "april", "may"),
    x2 = 1:5
)

Solution:

# fix number by merging via name
result <- merge(dt1, dt2, by="x1", all.x=T)
result[ , corr_num := ifelse(is.na(x2.y), x2.x, x2.y)]
result[ , c("x2.x", "x2.y") := NULL]

# fix name by merging via number
result <- merge(result, dt2, by.x="corr_num", by.y="x2", all.x=T)
result[ , corr_name := x1.y]
result[ , c("x1.x", "x1.y") := NULL]

Equivalent solution:

result <- merge(merge(dt1, dt2, by="x1", all.x=T), dt2, by.x="x2.x", by.y="x2", all.x=T)
result[ , corr_num  := ifelse(is.na(x2.y), x2.x, x2.y)]
result[ , corr_name := ifelse(is.na(x1.y), x1.x, x1.y)]
result[ , grep("\\.", names(result)) := NULL]

Result

> result[ , .(corr_name, corr_num, x3)]
   corr_name corr_num   x3
1:  february        2  498
2:     march        3  198
3:     april        4 4984
4:     april        4  498
5:     march        3  984

Do a merge, but omit the `all.x=TRUE` argument. Merges by default keep only matched rows, so the default will do what you want. In my example code above, I assumed you wanted to keep all of the rows in `dt1` regardless of whether or not they matched to `dt2`, which is why I added in the `all.x` arg. — DanY, Sep 02 '18 at 16:04
Thanks! In some cases I may keep all the rows and in some I want to delete them. — Pan Miroslav, Sep 03 '18 at 09:13

Compare pairs of 2 data tables and correct the values in the first one based on the second one

1 Answers1