Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I combine two data-frames based on two columns? [duplicate]

Tags:

merge

dataframe

r

People also ask

How do I merge two Dataframes based on two columns?

How to join pandas dataframes on multiple columns? Note that, the list of columns passed must be present in both the dataframes. If the column names are different in the two dataframes, use the left_on and right_on parameters to pass your column lists to merge on.

How do I merge two Dataframes in pandas based on multiple common column?

To merge two Pandas DataFrame with common column, use the merge() function and set the ON parameter as the column name.

How do I merge two Dataframes based on a column in R?

The merge() function in base R can be used to merge input dataframes by common columns or row names. The merge() function retains all the row names of the dataframes, behaving similarly to the inner join. The dataframes are combined in order of the appearance in the input function call.


See the documentation on ?merge, which states:

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.x and by.y.

This clearly implies that merge will merge data frames based on more than one column. From the final example given in the documentation:

x <- data.frame(k1=c(NA,NA,3,4,5), k2=c(1,NA,NA,4,5), data=1:5)
y <- data.frame(k1=c(NA,2,NA,4,5), k2=c(NA,NA,3,4,5), data=1:5)
merge(x, y, by=c("k1","k2")) # NA's match

This example was meant to demonstrate the use of incomparables, but it illustrates merging using multiple columns as well. You can also specify separate columns in each of x and y using by.x and by.y.


Hope this helps;

df1 = data.frame(CustomerId=c(1:10),
             Hobby = c(rep("sing", 4), rep("pingpong", 3), rep("hiking", 3)),
             Product=c(rep("Toaster",3),rep("Phone", 2), rep("Radio",3), rep("Stereo", 2)))

df2 = data.frame(CustomerId=c(2,4,6, 8, 10),State=c(rep("Alabama",2),rep("Ohio",1),   rep("Cal", 2)),
             like=c("sing", 'hiking', "pingpong", 'hiking', "sing"))

df3 = merge(df1, df2, by.x=c("CustomerId", "Hobby"), by.y=c("CustomerId", "like"))

Assuming df1$Hobby and df2$like mean the same thing.


You can also use the join command (dplyr).

For example:

new_dataset <- dataset1 %>% right_join(dataset2, by=c("column1","column2"))