1

I am trying to update a df.1 with values from df.2 when the column values df.1$time and df.1$region matches df.2$time df.2$region, respectively

df.1

region, time, value
a, 1, 2
b, 1, 22
c, 1, 23
a, 2, na
b, 2, na
c, 2, na

df.1 = structure(list(region = c("a", "b", "c", "a", "b", "c"), time = c(1L, 
1L, 1L, 2L, 2L, 2L), value = c(2L, 22L, 23L, NA, NA, NA)), .Names = c("region", 
"time", "value"), row.names = c(NA, -6L), class = "data.frame")

df.2

region, time, value
a, 2, 45
b, 2, 54
c, 2, 56

df.2 = structure(list(region = c("a", "b", "c"), time = c(2L, 2L, 2L
), value = c(45L, 54L, 56L)), .Names = c("region", "time", "value"
), row.names = c(NA, -3L), class = "data.frame")

I am using merge(df.1, df.2, by=c(“region”, "time”), all.x=TRUE)

I am ending up new columns added up to the df.1 table, however I want only the values in df.1$value to be updated when a match is found. I also need to preserve all the values in df.1 table. Suggestions appreciated.

The desired ouput is as follows

region, time, value
    a, 1, 2
    b, 1, 22
    c, 1, 23
    a, 2, 45
    b, 2, 54
    c, 2, 56
madlymad
  • 6,367
  • 6
  • 37
  • 68
HexGuy
  • 38
  • 5
  • 1
    From the linked Q&A, you can load data.table and do `setDT(df.1)[df.2, on=.(region, time), value := i.value ]` – Frank Apr 21 '17 at 04:39
  • I tried the methods listed above, and the situation is little bit different, there is a repeating block in df.1 on the region column and in the df.2 only the matching set has to be retrieved matching two columns (region and time), Also, the df.1 has to be updated with the values, not created with a new column. – HexGuy Apr 21 '17 at 18:08
  • The code from my comment (similarly appearing in Uwe's answer in the link) seems to do the update correctly. Maybe you can edit to show your desired result to clarify. – Frank Apr 21 '17 at 18:14

0 Answers0