CODEWITHSUNDEEP
r
Maximilian
Maximilian

Reputation: 4229

Replace only last NA given ID from another column

Here is sample: data and wrong solution:

df <- data.frame(id=c(1,1,2,2,2,3,4,5,5), val=c(NA,1.5,1,NA,NA,5,NA,2,NA), rep=1:9)

Replace last NA given id from another column. Here is what I tried (among others):

funrep <- function(x) { is.na(tail(x, 1)) } 
df$val[as.logical(with(df, ave(val, id, FUN=funrep)))] <- df$rep[as.logical(with(df, ave(val, id, FUN=funrep)))]

The above replaces figures given id from another column where there is no NA.

Desired output:

 id val rep val2
  1  NA   1   NA 
  1 1.5   2  1.5
  2 1.0   3  1.0
  2  NA   4  NA
  2  NA   5  5 
  3 5.0   6  5.0
  4  NA   7  7
  5 2.0   8  2.0
  5  NA   9  9

EDIT : the values in column val2 has been corrected. Also I prefer base R solution. Thanks.

Upvotes: 1

Views: 52

Answers (1)

akrun
akrun

Reputation: 887711

Using data.table, we convert the 'data.frame' to 'data.table' (setDT(df), create "val2" column from "val", get the row index (.I) based on the logical condition grouped by "ID", extract that column ($V1). Using that index in the "i", we assign "val2" to those corresponding elements in "rep".

library(data.table)
i1 <- setDT(df)[, val2:= val][, .I[1:.N==.N & is.na(val)] , id]$V1
df[i1, val2:= as.numeric(rep)]
df
#     id val rep val2
#1:  1  NA   1   NA
#2:  1 1.5   2  1.5
#3:  2 1.0   3  1.0
#4:  2  NA   4   NA
#5:  2  NA   5  5.0
#6:  3 5.0   6  5.0
#7:  4  NA   7  7.0
#8:  5 2.0   8  2.0
#9:  5  NA   9  9.0

Using base R, we create a new column with transform, split the data.frame to list with split, use ifelse to change the values in 'val2' with 'rep' that satisfies the condition, and unsplit to get the expected output.

 unsplit(lapply(split(transform(df, val2=val), df$id), 
     function(x) transform(x, val2=ifelse(is.na(val2) & 
       1:nrow(x)==nrow(x), rep, val2))), df$id)

Or using ave

 i2 <- with(df, ave(is.na(val), id, FUN=function(x) x &
               seq_along(x)==length(x)))
 with(df, ifelse(i2, rep, val))

Upvotes: 1

Related Questions