How to make pd.to_datetime insert last day of month instead of first when input is limited to 'yyyy-mm'?

Question

I've got a pandas dataframe with a timeseries of the form:

Date  value
2020-01  1
2020-02  2
2020-03  3 

I'd like to efficiently make this into a datetime series using for example pd.to_datetime.

This can easily be done like below if you'd like the inserted day part of the date to be 01

code:

import pandas as pd
df = pd.DataFrame({'date': {0: '2020-01', 1: '2020-02', 2: '2020-03'},
                  'value': {0: 1, 1: 2, 2: 3}})
df['datetime']=pd.to_datetime(df['date'])
df

output:

      date  value   datetime
0  2020-01      1 2020-01-01
1  2020-02      2 2020-02-01
2  2020-03      3 2020-03-01

As you can see, it's assumed that the preferred day of month is the first day of the month. But how can you make sure that you get the last day of the month instead?

desired output:

      date  value   datetime
0  2020-01      1 2020-01-31
1  2020-02      2 2020-02-28
2  2020-03      3 2020-03-31

Answer 1

I think not possible in to_datetime, possible solution is add values after converting to datetimes:

df['datetime']=pd.to_datetime(df['date']) + pd.offsets.MonthEnd()
print (df)
      date  value   datetime
0  2020-01      1 2020-01-31
1  2020-02      2 2020-02-29
2  2020-03      3 2020-03-31