How To Join 2 Dataframe On Year And Month In Pandas?

I have 2 dataframe and I want to join them on the basis of month and year from a date without creating extra columns: example : df1 : date_1 value_1 2017-1-15 20 2017-1-31

Solution 1:

Here's a rudimentary but effective solution:

res=pd.merge(df1.assign(grouper=df1['date_1'].dt.to_period('M')),df2.assign(grouper=df2['date_2'].dt.to_period('M')),how='left',on='grouper')print(res)date_1value_1grouperdate_2value_202017-01-15       202017-01 2017-01-01     30.012017-01-31       302017-01 2017-01-01     30.022016-02-15       202016-02        NaTNaN

You can then remove unwanted columns:

res = res[['date_1', 'value_1', 'value_2']]

Solution 2:

Here is another way using lambda functions:

pd.merge(df,df2, left_on=df['date_1'].apply(lambda x: (x.year, x.month)),
         right_on=df2['date_2'].apply(lambda y: (y.year, y.month)),
         how='outer')[['date_1','value_1','value_2']]

    date_1value_1value_202017-01-152030.012017-01-313030.022016-02-1520NaN

Solution 3:

map

mapping = dict(zip(df2.date_2.dt.to_period('M'), df2.value_2))
df1.assign(value_2=df1.date_1.dt.to_period('M').map(mapping))

      date_1  value_1  value_2
02017-01-152030.012017-01-313030.022016-02-1520      NaN