1

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?

Improve this question
2
  • What things have you tried? Do you have some example code? Could you add an example dataframe assignment that we can play with? 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? Commented May 9, 2015 at 21:39

1 Answer 1

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.