join 2 dataframes using year and month parts of date time index

join_dataframes_using_parts_of_datetime_index.py

import pandas as pd

d = {'dat': ['2016-01-01', '2016-01-02', '2016-01-03', '2017-01-01', '2017-01-02', '2017-01-03'],'x': [1, 2, 3, 4, 5, 6]}
df1 = pd.DataFrame(d)
df1.set_index(['dat'], inplace=True)
df1.index = pd.to_datetime(df1.index)

d = {'dat': ['2016-01-01', '2017-01-01'],'y': [10, 11]}
df2 = pd.DataFrame(d)
df2.set_index(['dat'], inplace=True)
df2.index = pd.to_datetime(df2.index)

# create a dictionary mapping from df2 
# that translates the year/month period object to the corresponding value in column y
m = dict(zip(df2.index.to_period('M'), df2.y))

# use map to map the period-ized dates in df1.index to the correct y values
df1.assign(y=df1.index.to_period('M').map(m.get))