CODEWITHSUNDEEP
r
gerard1801
gerard1801

Reputation: 23

Replace missing values with the values of related ID

I have a dataframe with missing values. 

df1 <- data.frame(ID = c(1, 2, 3, 4, 5, 6), value1 = c(23, 14, NA, 45, NA, NA),
                 value2 = c(25, 15, NA, 34, NA, NA), value3 = c(33, 29, NA, 29, NA, NA))

ID  value1 value2 value3
1   23     25     33
2   14     15     29
3   NA     NA     NA
4   45     34     29
5   NA     NA     NA
6   NA     NA     NA

And a dataframe with id relations.

df2 <- data.frame(ID1 = c(1, 2, 4), ID2 = c(3, 5, 6))

ID1 ID2
1   3
2   5
4   6

I want to replace the missing values, with the values of the related ID. So the dataframe will look like this.

ID value1 value2 value3
1  23     25     33 
2  14     15     29
3  23     25     33
4  45     34     29
5  14     15     29
6  45     34     29

Any help would be appreciated.

Upvotes: 2

Views: 64

Answers (2)

GKi
GKi

Reputation: 39707

You can use as @FannieY already suggested a for loop. In addition I test with is.na to avoid to overwrite existing values.

for(i in seq_len(nrow(df2))) {
  idx <- is.na(df1[df2[i,2],-1])
  df1[df2[i,2],-1][idx]  <- df1[df2[i,1],-1][idx]
}
df1
#  ID value1 value2 value3
#1  1     23     25     33
#2  2     14     15     29
#3  3     23     25     33
#4  4     45     34     29
#5  5     14     15     29
#6  6     45     34     29

Upvotes: 1

FannieY
FannieY

Reputation: 124

you will need a for-loop like this:

for (i in seq_along(df2[, "ID2"])) {   
df1[df2[i, "ID2"], c("value1", "value2", "value3")] <- df1[df2[i, "ID1"], c("value1", "value2", "value3")]   }

Upvotes: 2

Related Questions