CODEWITHSUNDEEP
rconditional-statements
Patrick Burke
Patrick Burke

Reputation: 13

How to populate a column using multiple conditionals across 2 dataframes?

Im trying to populate a column with values based on two conditionals across two separate dataframes. So,

df1$day == df2$day & df1$hour == df2$hour then fill df1$X with df2$depth

I struggle because I am not asking it to populate it with a generic value (i.e. if x==y, then y2=1). I am trying to get it select values across multiple rows. A mock example:

df1                         df2
day   hour   X              day  hour   depth 
1      10    NA             1    10     50
1      11    NA             1    11     10
2      5     NA             1    3      100
5      9     NA             5    9      50
6      20    NA             7    17     80
7      17    NA             10    4     65

Any help would be greatly appreciated.

Upvotes: 1

Views: 30

Answers (2)

akrun
akrun

Reputation: 887118

An easier option is join from data.table

library(data.table)
setDT(df1)[df2, X := depth, on = .(day, hour)]
df1
#   day hour  X
#1:   1   10 50
#2:   1   11 10
#3:   2    5 NA
#4:   5    9 50
#5:   6   20 NA
#6:   7   17 80

In base R, we can use match

df1$X <- with(df1, df2$depth[match(paste(day, hour), paste(df2$day, df2$hour))])

data

df1<- data.frame(day = c(1, 1, 2, 5:7), hour = c(10:11, 5, 9, 20, 17),
         X = NA_integer_)
df2 <- data.frame(day = c(1, 1, 1, 5, 7, 10), hour = c(10, 11, 3, 9, 
       17, 4), depth = c(50, 10, 100, 50, 80, 65))

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 388982

Using dplyr, we can do a left_join and then rename the depth column as X

library(dplyr)
left_join(df1, df2, by = c("day", "hour")) %>%
   select(-X) %>%
   rename(X = depth)

#  day hour  X
#1   1   10 50
#2   1   11 10
#3   2    5 NA
#4   5    9 50
#5   6   20 NA
#6   7   17 80

If the X column is not always NA you could use coalesce. 

left_join(df1, df2, by = c("day", "hour")) %>%
   mutate(X = coalesce(depth, X)) %>%
   select(names(df1))

Or in base R : 

merge(df1, df2, all.x = TRUE)[-3]

Upvotes: 0

Related Questions