R Merge Without Duplicating Columns
Take a closer look at R Merge Without Duplicating Columns in our detailed breakdown.
I have two dataframes. For example
require('xlsx')
csvData <- read.csv("myData.csv")
xlsData <- read.xlsx("myData.xlsx")
csvData looks like this:
Period CPI VIX
1 0.029 31.740
2 0.039 32.840
3 0.028 34.720
4 0.011 43.740
5 -0.003 35.310
6 0.013 26.090
7 0.032 28.420
8 0.022 45.080
xlsData looks like this:
Period CPI DJIA
1 0.029 12176
2 0.039 10646
3 0.028 11407
4 0.011 9563
5 -0.003 10708
6 0.013 10776
7 0.032 9384
8 0.022 7774
When I merge this data, the CPI data is duplicated, and a suffix is put on the header, which is problematic (I have many more columns in my real df's).
mergedData <- merge(xlsData, csvData, by = "Period")
mergedData:
Period CPI.x VIX CPI.y DJIA
1 0.029 31.740 0.029 12176
2 0.039 32.840 0.039 10646
3 0.028 34.720 0.028 11407
4 0.011 43.740 0.011 9563
5 -0.003 35.310 -0.003 10708
6 0.013 26.090 0.013 10776
7 0.032 28.420 0.032 9384
8 0.022 45.080 0.022 7774
I want to merge the data frames without duplicating columns with the same name. For example, I want this kind of output:
Period CPI VIX DJIA
1 0.029 31.740 12176
2 0.039 32.840 10646
3 0.028 34.720 11407
4 0.011 43.740 9563
5 -0.003 35.310 10708
6 0.013 26.090 10776
7 0.032 28.420 9384
8 0.022 45.080 7774
I don't want to have to use additional 'by' arguments, or dropping columns from one of the df's, because there are too many columns that are duplicated in both df's. I'm just looking for a dynamic way to drop those duplicated columns during the merge process.
Thanks!
2 Answers
You can skip the by argument if the common columns are named the same.
From ?merge:
By default the data frames are merged on the columns with names they both have, but separate specifications of the columns can be given by
by.xandby.y.
Keeping that in mind, the following should work (as it did on your sample data):
merge(csvData, xlsData)
# Period CPI VIX DJIA
# 1 1 0.029 31.74 12176
# 2 2 0.039 32.84 10646
# 3 3 0.028 34.72 11407
# 4 4 0.011 43.74 9563
# 5 5 -0.003 35.31 10708
# 6 6 0.013 26.09 10776
# 7 7 0.032 28.42 9384
# 8 8 0.022 45.08 7774
You can also index your specific column of interest by name. This is useful if you just need a single column/vector from a large data frame.
Period <- seq(1,8)
CPI <- seq(11,18)
VIX <- seq(21,28)
DJIA <- seq(31,38)
Other1 <- paste(letters)[1:8]
Other2 <- paste(letters)[2:9]
Other3 <- paste(letters)[3:10]
df1<- data.frame(Period,CPI,VIX)
df2<- data.frame(Period,CPI,Other1,DJIA,Other2,Other3)
merge(df1,df2[c("Period","DJIA")],by="Period")
> merge(df1,df2[c("Period","DJIA")],by="Period")
Period CPI VIX DJIA
1 1 11 21 31
2 2 12 22 32
3 3 13 23 33
4 4 14 24 34
5 5 15 25 35
6 6 16 26 36
7 7 17 27 37
8 8 18 28 38
Sarah Jenkins is a veteran tech journalist with over 12 years of experience covering artificial intelligence, mobile innovations, and digital ethics. Her insights have appeared in leading technology publications worldwide.
