Geek Answers Handbook

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

I cannot get a solution, for my requirement below. If the data table (as shown below) has the corresponding values ​​in Col1 and Col3. Replace the value with Col2 (old with New-Val).

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

After processing the data table, you should look like this:

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

Update:

I wrote New-Val to understand this requirement. However, I cannot match this value because it varies for different values ​​of Col1 and Col3. For example, as shown below:

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

Similarly, the recordings are huge. Therefore, I ideally want to combine Col1 and Col3, and in Col2 - empty (always), which should be replaced regardless of different coinciding values ​​of Col1 and Col3.

This should be addressed:

Col1  Col2    Col3
1     New1    a
1     New1    a
1     New1    a
2     new2    b
2     new2    b
2     new2    b
+4
2

"" "Col2" NA na.locf, NA "" , "Col1" "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


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

- by = .EACHI - .

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
+3

More articles:


All Articles