data.table in R: Replace a column value with a value from same column after matching two other columns values

4
votes

I'm not able to get a solution, for my below requirement. If a data.table(as below) has matching values in Col1 and Col3. Replace Col2 value(old with New-Val). 

Col1  Col2    Col3
1     old     a
1     old     a
1     New-Val a

After manipulating data table should look as below:

  Col1  Col2    Col3
   1     New-Val a
   1     New-Val a
   1     New-Val a

Update:

I've written New-Val for understanding the requirement. However I cannot match this value because it varies for different Col1 and Col3 values. For example as below:

Col1  Col2    Col3
1     blank   a
1     blank   a
1     New1    a
2     blank   b
2     new2    b
2     new2    b

Likewise the entries are huge. So I ideally want to match Col1 and Col3 and in Col2 it is blank(always) which is to be replaced irrespective of different matched Col1 and Col3 values.

This should be manipulated to:

Col1  Col2    Col3
1     New1    a
1     New1    a
1     New1    a
2     new2    b
2     new2    b
2     new2    b
2
rreplacedata.tablematch
Can you please elaborate in words what are you trying to do and make your example a bit more sophisticated rather than just one value in Col1 and Col3? – David Arenburg

2 Answers

4
votes

We can replace the "blank" values in "Col2" with NA and use na.locf to replace the NA with "New" values grouped by "Col1" and "Col3".

library(zoo)
dt[Col2=="blank", Col2 := NA]
dt[, Col2 := na.locf(Col2, fromLast=TRUE) ,.(Col1, Col3)]
dt
#   Col1 Col2 Col3
#1:    1 New1    a
#2:    1 New1    a
#3:    1 New1    a
#4:    2 new2    b
#5:    2 new2    b
#6:    2 new2    b

Or we can do without using any additional package

dt[, Col2 := Col2[Col2!='blank'][1L] , .(Col1, Col3)]
3
votes

Another option is to use a binary join combined with by = .EACHI- this will work for factors too

dt[dt[Col2 != "blank"], Col2 := i.Col2, on = c("Col1", "Col3"), by = .EACHI]
dt
#    Col1 Col2 Col3
# 1:    1 New1    a
# 2:    1 New1    a
# 3:    1 New1    a
# 4:    2 new2    b
# 5:    2 new2    b
# 6:    2 new2    b