CODEWITHSUNDEEP
pythonpandas
Manz
Manz

Reputation: 605

How to find the End Date of the month and convert the date format using Python

having a column where Month and year is given, Using Month and year need to find the Last date of the month, and hence convert the date format.

Where need to update StartDay using Date1 and EndDay using Date2.

Input Data

Date1     Date2    StartDay      EndDay

Apr2020  Dec2020  
Nov2020  Feb2021
Sep2018  Oct2020
Jan2020  Nov2020
Mar2018  Sep2020

Expected Output

Date1     Date2     StartDay         EndDay

Apr2020  Dec2020    2020-04-01       2020-12-31
Nov2020  Feb2021    2020-11-01       2021-12-28
Sep2018  Oct2020    2018-09-01       2020-10-31
Jan2020  Nov2020    2020-01-01       2020-11-30
Mar2018  Sep2020    2018-03-01       2020-09-30

Code been using So Far:

df['month'] = df.Date2.str[:3]
df['year'] = df.Date2.str[3:7]

df['Date1'] = df.Date1.str[:3] + '-' + df.Date1.str[3:]
df['col'] = '01-' + df['Date1'].astype(str)
df['StartDay'] = pd.to_datetime(df['StartDay'], errors='coerce')
df['StartDay'] = df['StartDay'].dt.strftime('%Y-%m-%d')

How to find the End Date using the column Date2, Please suggest.

Upvotes: 0

Views: 45

Answers (1)

jezrael
jezrael

Reputation: 862511

You can use %b%Y for match months first 3 letters with years in YYYY, so then only need to add pandas.tseries.offsets.MonthEnd:

df['StartDay'] = pd.to_datetime(df['Date1'], errors='coerce', format='%b%Y')
df['EndDay'] = (pd.to_datetime(df['Date2'], errors='coerce', format='%b%Y') + 
                pd.offsets.MonthEnd(1))
print (df)
     Date1    Date2   StartDay     EndDay
0  Apr2020  Dec2020 2020-04-01 2020-12-31
1  Nov2020  Feb2021 2020-11-01 2021-02-28
2  Sep2018  Oct2020 2018-09-01 2020-10-31
3  Jan2020  Nov2020 2020-01-01 2020-11-30
4  Mar2018  Sep2020 2018-03-01 2020-09-30

Upvotes: 1

Related Questions