How to find the last date of the month from current month (excel version of EOMONTH) in PYTHON?

user_13565916 0
Viewed 173

my df currently consists of only date column

date
28/09/1995
30/10/1993
26/02/2021
04/04/2020

I want to create 2 new columns called "end of month" which gives the last day of the month & "end of quarter" which gives last day of quarter

date         end of month  end of quarter
28/09/1995   30/09/1995    30/09/1995
30/10/1993   31/10/1993    31/12/1993
26/02/2021   28/02/2021    31/03/2021
04/04/2020   30/04/2020    30/06/2020

Kindly help me in solving this

pandas data-manipulation date datetime python
2 Answers

Try this:

import pandas as pd

df = pd.DataFrame({'date':['28/09/1995', '30/10/1993', '26/02/2021', '04/04/2020']})
df['date'] = pd.to_datetime(df['date'], dayfirst=True)
df['end of month'] = df['date'] + pd.offsets.MonthEnd(1)
df['end of quarter'] = df['date'] + pd.offsets.QuarterEnd(1)

        date end of month end of quarter
0 1995-09-28   1995-09-30     1995-09-30
1 1993-10-30   1993-10-31     1993-12-31
2 2021-02-26   2021-02-28     2021-03-31
3 2020-04-04   2020-04-30     2020-06-30
user_54175110

Using only the Python standard library:

from datetime import datetime, timedelta

def date_to_endofmonth(dt: datetime) -> datetime:
    # reset day to first day of month, add one month and subtract 1 day:
    return (datetime(dt.year + ((dt.month+1) // 12), ((dt.month+1) % 12) or 12, 1)
            - timedelta(1))

def date_to_endofquarter(dt: datetime) -> datetime:
    # from given month, calculate quarter end month, then hand over to date_to_endofmonth
    return date_to_endofmonth(datetime(dt.year, ((dt.month - 1) // 3) * 3 + 3, dt.day))

Ex:

for s in "28/09/1995", "30/12/1993", "26/02/2021", "04/04/2020":
    dt = datetime.strptime(s, "%d/%m/%Y")
    print(s, '->', date_to_endofmonth(dt).date(), '->', date_to_endofquarter(dt).date())

28/09/1995 -> 1995-09-30 -> 1995-09-30
30/12/1993 -> 1993-12-31 -> 1993-12-31
26/02/2021 -> 2021-02-28 -> 2021-03-31
04/04/2020 -> 2020-04-30 -> 2020-06-30
user_101974180
Related