CODEWITHSUNDEEP
pythonpandasdataframe
lucaschn
lucaschn

Reputation: 363

Pandas - Return last/first day of the month in a custom Datetime Index

I'm using a multi index columns dataframe with custom dates (specific holidays,weekdays..).

DatetimeIndex(['1989-01-31', '1989-02-01', '1989-02-02', '1989-02-03',
           '1989-02-06', '1989-02-07', '1989-02-08', '1989-02-09',
           '1989-02-10', '1989-02-13',
           ...
           '2019-02-25', '2019-02-26', '2019-02-27', '2019-02-28',
           '2019-03-01', '2019-03-04', '2019-03-05', '2019-03-06',
           '2019-03-07', '2019-03-08'],
          dtype='datetime64[ns]', length=7585, freq=None)

I need to slice it for first or last day of the month from the index. Due to holidays,... some first/last day of the month of the index would not match with freq = 'BM'. No need to mention i cannot use resample(),...

Here an example:

import pandas as pd
import numpy as np
idx = pd.DatetimeIndex(['1989-01-31', '1989-02-01', '1989-02-02', '1989-02-03','1989-02-06', '1989-02-07', '1989-02-08', '1989-02-09','1989-02-10', '1989-02-13', '2019-02-25', '2019-02-26', '2019-02-27', '2019-02-28','2019-03-01', '2019-03-04', '2019-03-05', '2019-03-06','2019-03-07', '2019-03-08'], dtype='datetime64[ns]')

numbers = [0, 1, 2]
colors = [u'green', u'purple']
col = pd.MultiIndex.from_product([numbers, colors],names=['number', 'color'])

df = pd.DataFrame(np.random.rand(len(idx),len(col)),index =idx,columns=col)

number            0                 1                 2         
color         green   purple    green   purple    green   purple
2018-06-05  0.64943  0.64943  0.64943  0.64943  0.64943  0.64943
etc...

Expected Output:

2018-06-29  0.64943  0.64943  0.64943  0.64943  0.64943  0.64943

How could i do this please ?

thanks

Upvotes: 2

Views: 2042

Answers (1)

Rich Andrews
Rich Andrews

Reputation: 1680

You need to use a Grouper on your DataFrame. Using the mcve in the above question:

# Month End
df.groupby(pd.Grouper(freq='M')).last()

# Month Start
df.groupby(pd.Grouper(freq='MS')).first()

Note: Grouping in this manner groups by the DateTimeIndex month, whose group min and max months are calendrical and not necessarily in the index.

So we can go after a grouping of our own, requiring attention to months repeating over the years.

grpr = df.groupby([df.index.year, df.index.month])
data = []
for g, gdf in grpr:
    data.append(gdf.loc[gdf.index.min()])
    data.append(gdf.loc[gdf.index.max()])

new_df = pd.DataFrame(data)
new_df
number             0                   1                   2          
color          green    purple     green    purple     green    purple
1989-01-31  0.246601  0.915123  0.105688  0.645864  0.845655  0.339800
1989-01-31  0.246601  0.915123  0.105688  0.645864  0.845655  0.339800
1989-02-01  0.694509  0.665852  0.593890  0.715831  0.474022  0.011742
1989-02-13  0.770202  0.452575  0.935573  0.554261  0.235477  0.475279
2019-02-25  0.626251  0.826958  0.617132  0.118507  0.079782  0.183616
2019-02-28  0.740565  0.131821  0.968403  0.981093  0.211755  0.806868
2019-03-01  0.812805  0.379727  0.758403  0.345361  0.908825  0.166638
2019-03-08  0.238481  0.045592  0.740523  0.201989  0.432714  0.672510

It is correct to see duplication because gdf.index.min() may equal gdf.index.max(). A check would eliminate duplication when iterating over the groups.

Upvotes: 3

Related Questions