Search code examples
rrbindsqldfcbind

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.


Solution

  • 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.