CODEWITHSUNDEEP
rrbindsqldfcbind
Connor Uhl
Connor Uhl

Reputation: 75

Conditional bind - Create new data frame based on values in 2 other data frames

I have two data frames such as

df1 <- data.frame(Company = c('A','B','C','D','E','F'),
                  `X1980` = c(21,NA,53,57,11,26),
                  `X1981` = c(35,33,45,NA,NA,12))
df2 <- data.frame(Company = c('A','B','C','D','E','F'),
                  `X1980` = c(1,0,0,1,1,0),
                  `X1981` = c(1,1,1,0,1,0))

I would like to create a new data frame (df3) keeping the company column as is. The values for the years 1980 and 1981 should depend on values in df2: if value one then insert value from df1, otherwise insert "NA". The resulting data frame should represent the following:

result df3
        Company  1980  1981
           A      21    35
           B      NA    33
           C      NA    45
           D      57    NA
           E      11    NA
           F      NA    NA

Thanks for the help! If there's any way for me to improve the question, then just let me know.

Upvotes: 0

Views: 471

Answers (1)

Gregor Thomas
Gregor Thomas

Reputation: 145975

Change 0s in df2 to NA and multiply:

df2[df2 == 0] = NA
df2[-1] = df1[-1] * df2[-1]
df2
#   Company X1980 X1981
# 1       A    21    35
# 2       B    NA    33
# 3       C    NA    45
# 4       D    57    NA
# 5       E    11    NA
# 6       F    NA    NA

This works because NA * x = NA and 1 * x = x. It relies on the columns being in the same order if df1 and df2, but that could be easily adjusted if need be.

Upvotes: 2

Related Questions