Merge pandas dataframe where columns don't match

Question

I have different data in two dataframes. Both have two columns called Date and data corresponding to those date. However both the dates are of different frequencies.

Dataframe1 contains data at the end of month. So there is only one entry for every month. Dataframe2 contains dates which are not separated evenly. That is it may contain multiple dates from same month. For example if Dataframe1 contains 30 Apr 2014, Dataframe2 may contain 01 May 2014, 07 May 2014 and 22 May 2014.

I want to merge the data frames in a way so that data from Dataframe1 corresponding to 30 Apr 2014 appears against all dates in May 2014 in Dataframe2. Is there any simple way to do it?

What things have you tried? Do you have some example code? Could you add an example dataframe assignment that we can play with? — tmthydvnprt
– tmthydvnprt, Commented May 9, 2015 at 21:38
So is the date in df1 always 1 entry per month which corresponds to the following month in df2? — EdChum
– EdChum, Commented May 9, 2015 at 21:39

EdChum · Accepted Answer · 2015-05-09 22:01:04Z

My approach would be to add a month column for df1 that is the current month + 1 (you'll need to roll December over to January which just means substituting 13 for 1). Then I'd set the index of df1 to this 'month' column and call map on df2 against the month of the 'date' column, this will perform a lookup and assign the 'val' value:

In [70]:
# create df1
df1 = pd.DataFrame({'date':[dt.datetime(2014,4,30), dt.datetime(2014,5,31)], 'val':[12,3]})
df1
Out[70]:
        date  val
0 2014-04-30   12
1 2014-05-31    3
In [74]:
# create df2
df2 = pd.DataFrame({'date':['01 May 2014', '07 May 2014', '22 May 2014', '23 Jun 2014']})
df2['date'] = pd.to_datetime(df2['date'], format='%d %b %Y')
df2
Out[74]:
        date
0 2014-05-01
1 2014-05-07
2 2014-05-22
3 2014-06-23
In [75]:
# add month column, you'll need to replace 13 with 1 for December
df1['month'] = df1['date'].dt.month+1
df1['month'].replace(13,1)
df1
Out[75]:
        date  val  month
0 2014-04-30   12      5
1 2014-05-31    3      6

In [76]:
# now call map on the month attribute and pass df1 with the index set to month
df2['val'] = df2['date'].dt.month.map(df1.set_index('month')['val'])
df2
Out[76]:
        date  val
0 2014-05-01   12
1 2014-05-07   12
2 2014-05-22   12
3 2014-06-23    3

Collectives™ on Stack Overflow

Merge pandas dataframe where columns don't match

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related