resolve the last day of the month python

Question

I have a column in my DATA in df from which I need to create a new column to extract the last working day of the month or the last day of the month of that specific month.

df = pd.DataFrame({
           'DATA':['2021-10-06','2021-10-05','2021-10-04','2021-10-01','2021-09-30','2021-09-29 ','2021-09-28','2021-09-27 ']               
          })
print(df)

I managed to do this with the help of BMonthEnd() this way.

df['DATA2'] = pd.to_datetime(df['DATA']) + BMonthEnd()

But when I arrive on the last working day of a given month it skips to the following month.

a way to use the last date of that month can be used.

Ex Using BMonthEnd()

   DATA        DATA2
2021-10-06  2021-10-29
2021-10-05  2021-10-29
2021-10-04  2021-10-29
2021-10-01  2021-10-29
2021-09-30  2021-10-29  ** Erro, Correct = 2021-09-30
2021-09-29  2021-09-30
2021-09-28  2021-09-30
2021-09-27  2021-09-30

is there any way to reolver? can be using another way

This is noted in the comment of the last answer here https://stackoverflow.com/questions/27218543/how-can-i-select-last-business-day-of-the-month-in-pandas — Chris, Nov 11 '21 at 14:11

score 0 · Answer 1 · answered Nov 11 '21 at 14:14

0

It's lame that you have to workaround what looks like a bug, but you could manually capture the dates, and join on the month.

import pandas as pd
df = pd.DataFrame({
           'DATA':['2021-10-06','2021-10-05','2021-10-04','2021-10-01','2021-09-30','2021-09-29','2021-09-28','2021-09-27 ']               
          })
df['m'] = pd.to_datetime(df['DATA']).dt.month

eom=pd.date_range('1/1/2021', periods=12, freq='BM').to_frame(name='DATA2')
eom['m'] = eom['DATA2'].dt.month
df.merge(eom, on='m').drop(columns='m')

answered Nov 11 '21 at 14:14

Chris

15,819
3
24
37

1

It's not a bug. All the "End" offsets (MonthEnd/BMonthEnd _etc_) behave this way. You can avoid this behaviour by explicitly using (0) which will go to the end of the _current month_ even if it is the last day. `df['DATA2'] = pd.to_datetime(df['DATA']) + BMonthEnd(0)`. [This answer](https://stackoverflow.com/a/43177530/15497888) by [Martien Lubberink](https://stackoverflow.com/users/5318986/martien-lubberink) explains this with `MonthEnd` – Henry Ecker Nov 11 '21 at 14:25

score -1 · Answer 2 · answered Nov 11 '21 at 14:12

Use this:

import pandas as pd
from pandas.tseries.offsets import BMonthEnd
df = pd.DataFrame({
           'DATA':['2021-10-06','2021-10-05','2021-10-04','2021-10-01','2021-09-30','2021-09-29 ','2021-09-28','2021-09-27 ']               
          })
df['DATA2'] = pd.to_datetime(df['DATA']) + BMonthEnd(0)
print(df)

output:

          DATA      DATA2
0   2021-10-06 2021-10-29
1   2021-10-05 2021-10-29
2   2021-10-04 2021-10-29
3   2021-10-01 2021-10-29
4   2021-09-30 2021-09-30
5  2021-09-29  2021-09-30
6   2021-09-28 2021-09-30
7  2021-09-27  2021-09-30

resolve the last day of the month python

2 Answers2