Reputation: 19
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
Reputation: 35604
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