CODEWITHSUNDEEP
r
cumulus
cumulus

Reputation: 19

Match and merge two data frames

I want to merge and match the following CSV files.

CSV 1

Year    Qrtrs   BD  BS  BY  All
1950    JAS     0   1   0   1
1950    OND     0   2   1   3
1951    JAS     1   0   4   5

CSV 2

Year    JFM AMJ JAS OND
1950    LN  LN  NN  LN
1951    LN  NN  EN  EN

And would like to get the ff.

Year    Qrtrs   CASE    BD  BS  BY  All
1950    JAS       NN    0   1   0   1
1950    OND       LN    0   2   1   3
1951    JAS       EN    1   0   4   5

I am new to R.Thank you for your help.

And also, I want to add the remaining Qrtrs in the final CSV and just put "0" in BD,BS,BY,ALL. Pls see below. 

Year    Qrtrs   CASE    BD  BS  BY  All
1950    JAS       NN    0   1   0   1
1950    OND       LN    0   2   1   3
1950    AMJ       LN    0   0   0   0
1950    JFM       LN    0   0   0   0

Thank you.!

Upvotes: 1

Views: 72

Answers (1)

Darren Tsai
Darren Tsai

Reputation: 35624

Data

df1 <- read.table(
  text = "Year    Qrtrs   BD  BS  BY  All
          1950    JAS     0   1   0   1
          1950    OND     0   2   1   3
          1951    JAS     1   0   4   5",
  header = T, stringsAsFactors = F
)

df2 <- read.table(
  text = "Year    JFM AMJ JAS OND
          1950    LN  LN  NN  LN
          1951    LN  NN  EN  EN",
  header = T, stringsAsFactors = F
)

I provide a method with gather{tidyr} and left_join{dplyr} :

library(tidyr)
library(dplyr)

df2.2 <- gather(df2, key = "Qrtrs", value = "CASE", - Year)

df2.2

#   Year Qrtrs CASE
# 1 1950   JFM   LN
# 2 1951   JFM   LN
# 3 1950   AMJ   LN
# 4 1951   AMJ   NN
# 5 1950   JAS   NN
# 6 1951   JAS   EN
# 7 1950   OND   LN
# 8 1951   OND   EN

left_join(df1, df2.2)

#   Year Qrtrs BD BS BY All CASE
# 1 1950   JAS  0  1  0   1   NN
# 2 1950   OND  0  2  1   3   LN
# 3 1951   JAS  1  0  4   5   EN

Additionally, here is a base way :

df2.stack <- stack(df2, select = -Year)
df2.2 <- cbind(df2$Year, df2.stack)
names(df2.2) <- c("Year", "CASE", "Qrtrs")
merge(df1, df2.2, by = c("Year", "Qrtrs"))

Upvotes: 2

Related Questions