CODEWITHSUNDEEP
pythonpandas
FooBar
FooBar

Reputation: 16488

Merge DateTimeIndex with month-year

I have two data frames. One has a precise (daily) DateTimeIndex. I have used that index to create monthly statistics using groupby(['groupid', pd.TimeGrouper('1M', closed='left', label='left')]).

Now I would like to merge the information back to the original data frame. However, the date-time labels of the collapsed data frame do of course not correspond exactly to the original DateTimeIndex. So then I'd like to match them to the corresponding month-year information.

How would I do that?

                         statistics 
  date          groupid            
  2001-01-31          1          10
  2001-02-31          1          11

and original data frame

  date          groupid         foo
  2001-01-25          1           1
  2001-01-28          1           2
  2001-02-02          1           4

With expected output

  date          groupid         foo     statistics
  2001-01-25          1           1             10
  2001-01-28          1           2             10
  2001-02-02          1           4             11

Upvotes: 3

Views: 1105

Answers (1)

jezrael
jezrael

Reputation: 862661

You can create new columns with month period by to_period and then merge, also is necessary change 2001-02-31 to 2001-02-28 in df1, because 31. February does not exist:

df1['per'] = df1.index.get_level_values('date').to_period('M')
df2['per'] = df2.date.dt.to_period('M')
print (df1)
                    statistics     per
date       groupid                    
2001-01-31 1                10 2001-01
2001-02-28 1                11 2001-02

print (df2)
        date  groupid  foo     per
0 2001-01-25        1    1 2001-01
1 2001-01-28        1    2 2001-01
2 2001-02-02        1    4 2001-02

print (pd.merge(df2, df1.reset_index(level=1), on=['per','groupid'], how='right')
         .drop('per', axis=1))

        date  groupid  foo  statistics
0 2001-01-25        1    1          10
1 2001-01-28        1    2          10
2 2001-02-02        1    4          11

Upvotes: 3

Related Questions