Jay khan Jay khan - 27 days ago 16
R Question

Carry last Factor observation forward and backward in group of rows in R

Suppose I have dataset which looks like

ID Name
1 JAY
1
1 JAY
2 LAY
2 LAY
2
3 NA
3 KAY
3


I want to fill the rows with missing values(empty or with NA) based on the observation already available in the group. So the resultant data frame will look like

ID Name
1 JAY
1 JAY
1 JAY
2 LAY
2 LAY
2 LAY
3 KAY
3 KAY
3 KAY


I tried using
na.locf
but it didnt work for non numeric value.

DF1 = setDT(DF)[, N := na.locf(na.locf(Name(NA_real_^!Name),na.rm=FALSE), fromLast=TRUE, na.rm=FALSE), ID][is.na(N), N := 0]

Answer

One option is after grouping by 'ID', subset the 'Name' that are not NA and not a blank (nzchar(Name)), get the last observation (tail(...)) and assign (:=) it to 'Name'.

setDT(DF)[, Name := tail(Name[!is.na(Name) & nzchar(Name)], 1), by = ID]
DF
#   ID Name
#1:  1  JAY
#2:  1  JAY
#3:  1  JAY
#4:  2  LAY
#5:  2  LAY
#6:  2  LAY
#7:  3  KAY
#8:  3  KAY
#9:  3  KAY

If the 'Name' column is factor change the nzchar(Name) to nzchar(as.character(Name))


Or specify the logical vector in the 'i' and assign (:=) the last observation (Name[.N]) to 'Name' after grouping by 'ID'

setDT(DF)[!is.na(Name) & nzchar(Name), Name := Name[.N], ID]

Note: For the second solution to work, the 'Name' should be character class.

Comments