Joining multiple data frames based on columns values using dplyr

Question

I have three similar data frames as below:

df1<-data.frame(Campaign_Name=c("Z019","Z005","Z019","Z005","Z019"),
            Sunday_endwk=c("20190106","20190113","20190113","20190106","20190106"),
            Actual_Sales=c(12,2,5,10,12.11),
            Predictions=c(11.9,2.03,5.1,10.5,11.7),
            Version=c("layer_1","layer_1","layer_1","layer_1","layer_1"),
            Adj_Rsquared=c(0.85,0.85,0.85,0.85,0.85))
df1

    Campaign_Name Sunday_endwk Actual_Sales Predictions Version Adj_Rsquared
1          Z019     20190106        12.00       11.90 layer_1         0.85
2          Z005     20190113         2.00        2.03 layer_1         0.85
3          Z019     20190113         5.00        5.10 layer_1         0.85
4          Z005     20190106        10.00       10.50 layer_1         0.85
5          Z019     20190106        12.11       11.70 layer_1         0.85

Similarly, the other two dfs are:

df2<-data.frame(Campaign_Name=c("Z019","Z019","Z005","Z005"),
                Sunday_endwk=c("20190106","20190113","20190106","20190113"),
                Actual_Sales=c(12.2,2.2,5.2,10.2),
                Predictions=c(11.8,2.05,5.4,10.1),
                Version=c("layer_2","layer_2","layer_2","layer_2"),
                Adj_Rsquared=c(0.88,0.88,0.88,0.88))  
#df2

df3<-data.frame(Campaign_Name=c("Z005","Z019","Z019","Z005","Z019"),
                Sunday_endwk=c("20190106","20190106","20190120","20190113","20190113"),
                Actual_Sales=c(12,2,5,10,12),
                Predictions=c(11.9,2.03,5.1,10.5,12.3),
                Version=c("layer_3","layer_3","layer_3","layer_3","layer_3"),
                Adj_Rsquared=c(0.82,0.82,0.82,0.82,0.82))
#df3

## expected output

I am trying to merge as well as transform to wide format all the 3 dfs based on combination of Campaign_Name + Sunday_endwk (both should match-common across 3 dfs) as below:

  Campaign_Name Sunday_endwk Actual_Sales_layer_1 Predictions_layer_1 Adj_Rsquared_layer_1 Actual_Sales_layer_2
1          Z019     20190106                   12               11.90                 0.85                 12.2
2          Z005     20190113                    2                2.03                 0.85                 10.2
3          Z019     20190113                    5                5.10                 0.85                  2.2
4          Z005     20190106                   10               10.50                 0.85                  5.2
  Predictions_layer_2 Adj_Rsquared_layer_2 Actual_Sales_layer_3 Predictions_layer_3 Adj_Rsquared_layer_3
1               11.80                 0.88                    2                2.03                 0.82
2               10.10                 0.88                   10               10.50                 0.82
3                2.05                 0.88                   12               12.30                 0.82
4                5.40                 0.88                   12               11.90                 0.82

If either of the values of Campaign_Name + Sunday_endwk are not present in any df, that row:

Can be omitted
Retained with NAs for other columns

Also in a df, Campaign_Name + Sunday_endwk combination may not be unique.

Any help here will be appreciated.

Thanks.

`Reduce(function(x, y) merge(x, y, by = c('Campaign_Name', 'Sunday_endwk')), list(df1, df2, df3))` ? — Ronak Shah, Mar 11 '21 at 05:18
This works but if the rows of data frame increase , it takes a lot of time to execute. — Nishant, Mar 11 '21 at 12:02

Jon Spring · Accepted Answer · 2021-03-12T06:11:06.303

library(tidyverse)
bind_rows(df1, df2, df3, .id = "week") %>%
  rowid_to_column() %>%   # Added for nonunique combos of Camp/Sunday_endwk
  pivot_wider(c(Campaign_Name, Sunday_endwk, rowid), 
              names_from = week, values_from = Actual_Sales:Adj_Rsquared)

Result:

# A tibble: 5 x 14
  Campaign_Name Sunday_endwk Actual_Sales_1 Actual_Sales_2 Actual_Sales_3 Predictions_1 Predictions_2 Predictions_3 Version_1 Version_2 Version_3 Adj_Rsquared_1 Adj_Rsquared_2 Adj_Rsquared_3
  <chr>         <chr>                 <dbl>          <dbl>          <dbl>         <dbl>         <dbl>         <dbl> <chr>     <chr>     <chr>              <dbl>          <dbl>          <dbl>
1 Z019          20190106                 12           12.2              2         11.9          11.8           2.03 layer_1   layer_2   layer_3             0.85           0.88           0.82
2 Z005          20190113                  2           10.2             10          2.03         10.1          10.5  layer_1   layer_2   layer_3             0.85           0.88           0.82
3 Z019          20190113                  5            2.2             12          5.1           2.05         12.3  layer_1   layer_2   layer_3             0.85           0.88           0.82
4 Z005          20190106                 10            5.2             12         10.5           5.4          11.9  layer_1   layer_2   layer_3             0.85           0.88           0.82
5 Z019          20190120                 NA           NA                5         NA            NA             5.1  NA        NA        layer_3            NA             NA              0.82

I am not getting the desired result as i end up getting values in form of list-col.This maybe due to non-unique `Campaign_Name + Sunday_endwk` combination as in df1. — Nishant, Mar 11 '21 at 12:00
Try adding `rowid_to_column()` as a step right after `bind_rows`, and add `rowid` along with Campaign_Name and Sunday_endwk as columns not to pivot. Does that fix it? — Jon Spring, Mar 11 '21 at 17:05

Joining multiple data frames based on columns values using dplyr

1 Answers1