Python Pandas datetime64 column filter by last business day in month-CodePudding

I would like to filter the df below by last business day in a month;

so the result should show only 2022-08-31 and 2022-07-29 as those were the last business days in the months.

Please keep in mind the original df has thousands of rows when writing the solution. Thanks!

import pandas as pd
d = pd.to_datetime(['2022-08-01','2022-08-02','2022-08-03','2022-08-04','2022-08-31',
                   '2022-07-01','2022-07-02','2022-07-03','2022-07-04','2022-07-29'])
df = pd.DataFrame()
df['date'] = d
print(df)

        date
0 2022-08-01
1 2022-08-02
2 2022-08-03
3 2022-08-04
4 2022-08-31
5 2022-07-01
6 2022-07-02
7 2022-07-03
8 2022-07-04
9 2022-07-29

CodePudding user response：

You can use pandas.date_range with a BM frequency then pandas.DataFrame.loc to filter.

list_bm = pd.date_range(df['date'].min(), df['date'].max(), freq='BM').tolist()

out = df.loc[df['date'].isin(list_bm)]

Note : 'BM' stands for Business Month end frequency.

print(out)

        date
4 2022-08-31
9 2022-07-29