How do convert from MMM-YYYY to YYYY-MM-DD, setting DD to the last day of month in a data frame?

Question

I have a similar question on date conversion using data frame.

My data frame has two date string columns, hiredate and end_date, having different date formats, DD-MMM-YYYYand MMM-YY, respectively.

The column end_date has no DD to represent the day, so it should be added and set to the last day of the month.

Below is an example of the input data frame:

 empno  ename    hiredate end_date
     1 sreenu 17-Jun-2021   May-22

And this is the expected output after end_date conversion:

 empno  ename    hiredate   end_date
     1 sreenu 17-Jun-2021 2022-05-31

I want to create a method/function to identify all date columns in the data frame and then convert it to YYYY-MM-DD format.

Welcome to StackOverflow. What have you tried? And please take a look at https://stackoverflow.com/questions/42950/how-to-get-the-last-day-of-the-month to get the last day of the month in Python. — rajah9, Sep 21 '21 at 11:09
@rajah9, I tried this one before i posted. but above question is different than mine. I want create a method to convert DD-MM-YYYY and MMM-YY to YYYY-MM-DD format dynamically on my dataframe. — user16084293, Sep 21 '21 at 11:12

Corralien · Answer 1 · 2021-09-21T11:56:28.210

You can convert end_date to datetime according to the format '%B-%y' and add a MonthEnd(0) offset:

Input data:

>>> df
   empno   ename     hiredate end_date
0      1  sreenu  17-Jun-2021   May-22

Column transformation:

>>> df['end_date'] = pd.to_datetime(
    df['end_date'], format='%b-%y'
).add(pd.offsets.MonthEnd(0))

Output result:

>>> df
   empno   ename     hiredate   end_date
0      1  sreenu  17-Jun-2021 2022-05-31

score 0 · Answer 2 · answered Sep 21 '21 at 11:24

0

You can use the solution pointed out in the comment -

pd.to_datetime(df['end_date'], format='%b-%y').apply(lambda x: date(x.year, x.month, calendar.monthrange(x.year, x.month)[-1]))

answered Sep 21 '21 at 11:24

Mortz

2 Answers2