python - Merging two dataframes on similar columns -

i have following 2 dataframes, snippets out of large dataset:

df1:  date key    number  2000  1      50 2001  1      40 2000  2      600 2001  2      650  df2: key   key2 1       2       b  3       c 

i want add key2 column df1 column matched on "key". result should following:

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

to this, using following command:

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

however, adds key2 "c" dataset, not want added. want variable key2 appended df1 based on keys of df1. information in df2 not match on key in df1 should dropped. therefore, result dataframe should have 1 column more df1 , exact amount of rows.

does know why merge "left" not work here, because if run code this, result dataframe has 1 column more - desired-, more rows df1, not want.

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    1  2001    1      40    2  2000    2     600    b 3  2001    2     650    b 

---

you can use df.merge specifying left_on , right_on columns merge:

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

in fact, can omit keyword arguments, pd.merge(df1, df2) works (for example).

wiki