CODEWITHSUNDEEP
pythonpandas
Learner
Learner

Reputation: 157

Merge pandas dataframe where columns don't match

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?

Upvotes: 1

Views: 1145

Answers (1)

EdChum
EdChum

Reputation: 394071

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

Upvotes: 1

Related Questions