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

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

CodePudding user response：

Try this:

import pandas as pd
from pandas.tseries.offsets import MonthEnd, QuarterEnd

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']   MonthEnd(1)
df['end of quarter'] = df['date']   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

CodePudding user response：

A Python standard library version:

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