Home > Software engineering >  joining two dataframes on matching values of two common columns R
joining two dataframes on matching values of two common columns R

Time:10-08

I have a two dataframes A and B that both have multiple columns. They share the common columns "week" and "store". I would like to join these two dataframes on the matching values of the common columns.

For example this is a small subset of the data that I have:

A = data.frame(retailer = c(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2),
store = c(5, 5, 5, 5, 5, 5, 5, 5, 6, 6, 6, 6, 6, 6, 6, 6), 
week = c(2021100301, 2021092601, 2021091901, 2021091201, 2021082901, 2021082201, 2021081501, 2021080801,
          2021080101, 2021072501, 2021071801, 2021071101, 2021070401, 2021062701, 2021062001, 2021061301),
dollars = c(121817.9, 367566.7, 507674.5, 421257.8, 453330.3, 607551.4, 462674.8,
  464329.1, 339342.3, 549271.5, 496720.1, 554858.7, 382675.5,
  373210.9, 422534.2, 381668.6))

and

B = data.frame(
  week = c("2020080901", "2017111101", "2017061801", "2020090701", "2020090701", "2020090701",
           "2020091201","2020082301", "2019122201", "2017102901"),
  store = c(14071, 11468, 2428, 17777, 14821, 10935,  5127, 14772, 14772, 14772),
  fill = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
)

I would like to join these two tables on the matching week AND store values in order to incorporate the "fill" column from B into A. Where the values don't match, I would like to have a label "0" in the fill column, instead of a 1. Is there a way I can do this? I am not sure which join to use as well, or if "merge" would be better for this? Essentially I am NOT trying to get rid of any rows that do not have the matching values for the two common columns. Thanks for any help!

CodePudding user response:

We may do a left_join

library(dplyr)
library(tidyr)
A %>%
    mutate(week = as.character(week)) %>% 
    left_join(B) %>% 
    mutate(fill = replace_na(fill, 0))
  • Related