CODEWITHSUNDEEP
rdata.table
Stinky_Goat
Stinky_Goat

Reputation: 135

Copy value to other NA cells based upon ID

I am trying to copy values from the diff column that has the value to the corresponding empty cells for the matching client_id. I am sure this is done easily in data.table, but I'm just unsure how.

So here is my data:

client_id <- c(1,1,1,2,2,3,3,3,3,4,4)
date <- c("1/1/2021","1/2/2021","1/3/2021","5/1/2021","10/1/2021","10/1/2021","11/1/2021","1/2/2021","10/9/2021","15/9/2021","16/10/2021")
date <- as.Date(date, '%d/%m/%Y')
score <- c(15,10,19,20,10,25,20,15,10,30,5)
diff <- c(NA, NA, -4, NA, 10, NA, NA, NA, 15, NA, 25)
df <- data.frame(client_id, date, score, diff)
df <-setDT(df)

And I'm wanting to end up with this:

client_id <- c(1,1,1,2,2,3,3,3,3,4,4)
date <- c("1/1/2021","1/2/2021","1/3/2021","5/1/2021","10/1/2021","10/1/2021","11/1/2021","1/2/2021","10/9/2021","15/9/2021","16/10/2021")
date <- as.Date(date, '%d/%m/%Y')
score <- c(15,10,19,20,10,25,20,15,10,30,5)
diff <- c(-4, -4, -4, 10, 10, 15, 15, 15, 15, 25, 25)
df <- data.frame(client_id, date, score, diff)
df <-setDT(df)

Thanks for any help!

Upvotes: 2

Views: 39

Answers (1)

r2evans
r2evans

Reputation: 160447

Many ways to do this, depending on your data. This is a "next observation carried backward" form of nafill:

df[, diff := nafill(diff, type = "nocb"), by = .(client_id)][]
#     client_id       date score  diff
#         <num>     <Date> <num> <num>
#  1:         1 2021-01-01    15    -4
#  2:         1 2021-02-01    10    -4
#  3:         1 2021-03-01    19    -4
#  4:         2 2021-01-05    20    10
#  5:         2 2021-01-10    10    10
#  6:         3 2021-01-10    25    15
#  7:         3 2021-01-11    20    15
#  8:         3 2021-02-01    15    15
#  9:         3 2021-09-10    10    15
# 10:         4 2021-09-15    30    25
# 11:         4 2021-10-16     5    25

Upvotes: 2

Related Questions