Merge unequal dataframes and replace missing rows with 0

Question

I have two data frames  one with only characters and the other one with characters and values   df1   data frame x c  a    b    c    d    e    df2   data frame x c  a    b    c   y   c 0 1 0   merge df1  df2    x y 1 a 0 2 b 1 3 c 0    I want to merge df1 and df2  The characters a  b and c merged good and also have 0  1  0 but d and e has nothing  I want d and e also in the merge table  with the 0 0 condition  Thus for every missing row at the df2 data frame  the 0 must be placed in the df1 table  like     x y 1 a 0 2 b 1 3 c 0 4 d 0 5 e 0

User · Answer

Another alternative with data table   EXAMPLE DATA  dt1  lt - data table df1  dt2  lt - data table df2  setkey dt1 x  setkey dt2 x    CODE  dt2 dt1 list y ifelse is na y  0 y

User · Answer

all  option does not work anymore  The new parameter is   x   pd merge df1  df2  how  outer

User · Answer

Take a look at the help page for merge  The all parameter lets you specify different types of merges  Here we want to set all   TRUE  This will make merge return NA for the values that don t match  which we can update to 0 with is na     zz  lt - merge df1  df2  all   TRUE  zz is na zz    lt - 0   gt  zz   x y 1 a 0 2 b 1 3 c 0 4 d 0 5 e 0   Updated many years later to address follow up question  You need to identify the variable names in the second data table that you aren t merging on - I use setdiff   for this  Check out the following     df1   data frame x c  a    b    c    d    e   NA   df2   data frame x c  a    b    c   y1   c 0 1 0   y2   c 0 1 0     merge as before df3  lt - merge df1  df2  all   TRUE   columns in df2 not in df1 unique df2 names  lt - setdiff names df2   names df1   df3 unique df2 names  is na df3   unique df2 names     lt - 0    Created on 2019-01-03 by the reprex package  v0 2 1

User · Answer

Or  as an alternative to  Chase s code  being a recent plyr fan with a background in databases   require plyr  zz lt -join df1  df2  type  left   zz is na zz    lt - 0

User · Answer

I used the answer given by Chase  answered May 11  11 at 14 21   but I added a bit of code to apply that solution to my particular problem   I had a frame of rates  user  download  and a frame of totals  user  download  to be merged by user  and I wanted to include every rate  even if there were no corresponding total   However  there could be no missing totals  in which case the selection of rows for replacement of NA by zero would fail   The first line of code does the merge   The next two lines change the column names in the merged frame   The if statement replaces NA by zero  but only if there are rows with NA     merge rates and totals  replacing absent totals by zero graphdata  lt - merge rates  totals  by c  user   all x T  colnames graphdata  colnames graphdata    download x      download rate  colnames graphdata  colnames graphdata    download y      download total  if any is na graphdata download total          graphdata is na graphdata download total    download total  lt - 0

User · Answer

Assuming df1 has all the values of x of interest  you could use a dplyr  left join   to merge and then either a base  replace   or tidyr  replace na   to replace the NAs as 0s   library tidyverse     dplyr only  df new  lt -    left join df1  df2  by    x     gt      mutate y   replace y  is na y   0      dplyr and tidyr  df new  lt -    left join df1  df2  by    x     gt      mutate y   replace na y  0      In the sample data column  x  is a factor  which will give a warning with the join  This can be prevented by converting to a character before the join  df new  lt -    left join df1   gt   mutate x   as character x                 df2   gt   mutate x   as character x                 by    x     gt        mutate y   replace y  is na y   0

[r] Merge unequal dataframes and replace missing rows with 0

Examples related to r

Examples related to merge

Examples related to dataframe