0

I am merging three data sets ( data frames) in R as follows: Prv_mnth_so3 has state, Product_Number , Quantity_On_Hand , Category and Lc_Amount

Prv_mnth_soqty <- Prv_mnth_so3 %>%
                    filter( Category== "ESC") %>%
                    group_by (state,Product_Number) %>%
                    summarise(qty = sum(Quantity_On_Hand))
                    #arrange(state,Product_Number)
Prv_mnth_so_esc_amt <- Prv_mnth_so3 %>%
                        filter( Category== "ESC") %>%
                        group_by (state,Product_Number) %>%
                        summarise(esc = sum(as.numeric(Lc_Amount))) %>%
                        arrange(state,Product_Number)
Prv_mnth_so_lom_amt <- Prv_mnth_so3 %>%
                        filter( Category== "LOM") %>%
                        group_by (state,Product_Number) %>%
                        summarise(lom = sum(Lc_Amount))%>%
                        arrange(state,Product_Number)
Prv_mnth_si <- merge(Prv_mnth_soqty, Prv_mnth_so_esc_amt , Prv_mnth_so_lom_amt,
                     by.x = c("state","Product_Number") , by.y = c("state","Product_Number"), by.z = c("state","Product_Number"), all = TRUE) ```
in out come (Prv_mnth_si ) I expect 5 variables as - State, Product_number), qty, esc and lom but I am not gettig lom in outcome, though in Prv_mnth_so_lom_amt, I can see lom variables is there
Ashish
  • 115
  • 3
  • 15
  • 2
    `merge` is for joining two df, i.e. your third df and by.z are not used. You need two merge statements to join three df. – stefan Oct 20 '20 at 13:31
  • Suggested dupe: [How to merge a list of data frames](https://stackoverflow.com/a/34393416/903061). – Gregor Thomas Oct 20 '20 at 13:34

1 Answers1

0

Since you are using the tidyverse you can use one of the join functions provided by the dplyr package.

Change the last line from:

Prv_mnth_si <- merge(Prv_mnth_soqty, 
                     Prv_mnth_so_esc_amt , 
                     Prv_mnth_so_lom_amt,
                     by.x = c("state","Product_Number") , 
                     by.y = c("state","Product_Number"), 
                     by.z = c("state","Product_Number"), 
                     all = TRUE)

to:

Prv_mnth_si <- full_join(Prv_mnth_soqty, Prv_mnth_so_esc_amt) %>%
    full_join(Prvmnth_so_lom_amt)
Andrew
  • 93
  • 8