CODEWITHSUNDEEP
pythonpandasdatetime-format
pentavol
pentavol

Reputation: 119

Convert specific date format in the ending day of previous month in pandas

I have the following table format

 Date1     | Date2     | other columns
 12FEB2019 | 12OCT2020 | ...
 19FEB2019 | 08NOV2021 | ...
 ...       | ...       | ...

I want to convert it into

 Date1     | Date2     | other columns
 31JAN2019 | 30SEP2020 | ...
 31JAN2019 | 31OCT2021 | ...
 ...       | ...       | ...

Here is what I tried for Date1 (and similarly for Date2) until now:

from pandas.tseries.offsets import MonthEnd
df['Date1'] = pd.to_datetime(df.Date1) - MonthEnd(1)

However, the results have a different format (e.g. 2020-09-30 instead of 30SEP2020), not what I expected What should I add to my code in order to get the desired format?

Upvotes: 0

Views: 38

Answers (2)

Oddaspa
Oddaspa

Reputation: 888

You should look into strftime documentation.

Here is an example

date = pd.to_datetime("31JAN2019")
date.strftime("%d%b%Y")
>> 31Jan2019

You could use the .upper() method for strings to get the output in all caps.

In your example you could apply the above as such:

df['Date1'] = pd.to_datetime(df.Date1) - MonthEnd(1)
df['Date1'] = df['Date1'].apply(lambda x: x.strftime("%d%b%Y").upper())

Upvotes: 2

Rob Raymond
Rob Raymond

Reputation: 31236

After doing date logic, convert it back to a string using strftime()

df = pd.read_csv(io.StringIO("""Date1     | Date2     | other columns
 12FEB2019 | 12OCT2020 | ...
 19FEB2019 | 08NOV2021 | ..."""), sep="|")
df.columns = [c.strip() for c in df.columns]


df.assign(**{c:(pd.to_datetime(df[c]) - pd.tseries.offsets.MonthEnd(1)).dt.strftime("%d%b%Y").str.upper()
 for c in df.columns if c.startswith("Date")})
Date1 Date2 other columns
0 31JAN2019 30SEP2020 ...
1 31JAN2019 31OCT2021 ...

Upvotes: 1

Related Questions