Merging two dataframes on similar columns

Question

I have the following two dataframes, which are snippets out of a large dataset:

df1: date key number 2000 1 50 2001 1 40 2000 2 600 2001 2 650 df2: key key2 1 A 2 B 3 C

I want to add the key2 column to the df1 column matched on "key". The result should look the following:

date key number key2 2000 1 50 A 2001 1 40 A 2000 2 600 B 2001 2 650 B

To do this, I am using the following command:

result = pd.merge(df1, df2, how="left", on="key")

However, this also adds the key2 "C" to the dataset, which I do not want to be added. I only want the variable key2 be appended to the df1 based on the keys of df1. The information in df2 which does not match on key in df1 should be dropped. Therefore, my result dataframe should have one column more than df1 and the exact amount of rows.

Does anybody know why merge "left" does not work here, because if I run the code like this, my result dataframe has 1 column more - as desired-, but also more rows than df1, which I do not want.

If i run your code, result does not have columns with key2 'C'. — Deb
– Deb, Commented Aug 23, 2017 at 9:23

cs95 · Accepted Answer · 2017-08-23 09:20:20Z

You can use pd.Series.replace:

In [242]: df1['key2'] = df1.key.replace(dict(df2.values)); df1 Out[242]: date key number key2 0 2000 1 50 A 1 2001 1 40 A 2 2000 2 600 B 3 2001 2 650 B

You can also use df.merge specifying left_on and right_on columns for the merge:

In [251]: df1.merge(df2, left_on='key', right_on='key') Out[251]: date key number key2 0 2000 1 50 A 1 2001 1 40 A 2 2000 2 600 B 3 2001 2 650 B

In fact, you can omit the keyword arguments, pd.merge(df1, df2) also works (for your example).

freddy888 · Accepted Answer · 2017-08-23 09:47:15Z

Thanks for the replies. I actually got it done via:

result= df1.join(df2, how="left", on="key", lsuffix='_', rsuffix='_')

I dont know why this does not yield the same result as merge...

You did not provide an MVCE that satisfactorily encapsulated your problem.

Collectives™ on Stack Overflow

Merging two dataframes on similar columns

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related