Find the end of the month of a Pandas DataFrame Series

You can use pandas.tseries.offsets.MonthEnd:

from pandas.tseries.offsets import MonthEnddf['Date'] = pd.to_datetime(df['Date'], format="%Y%m") + MonthEnd(1)

The 1 in MonthEnd just specifies to move one step forward to the next date that's a month end. (Using 0 or leaving it blank would also work in your case). If you wanted the last day of the next month, you'd use MonthEnd(2), etc. This should work for any month, so you don't need to know the number days in the month, or anything like that. More offset information can be found in the documentation.

Example usage and output:

df = pd.DataFrame({'Date': [200104, 200508, 201002, 201602, 199912, 200611]})df['EndOfMonth'] = pd.to_datetime(df['Date'], format="%Y%m") + MonthEnd(1)     Date EndOfMonth0  200104 2001-04-301  200508 2005-08-312  201002 2010-02-283  201602 2016-02-294  199912 1999-12-315  200611 2006-11-30

Agreed that root offers is the right method. However, readers who blindly use MonthEnd(1) are in for a surprise if they use the last date of the month as an input:

In [4]: pd.Timestamp('2014-01-01') + MonthEnd(1)Out[4]: Timestamp('2014-01-31 00:00:00')In [5]: pd.Timestamp('2014-01-31') + MonthEnd(1)Out[5]: Timestamp('2014-02-28 00:00:00')

Using MonthEnd(0) instead gives this:

In [7]: pd.Timestamp('2014-01-01') + MonthEnd(0)Out[7]: Timestamp('2014-01-31 00:00:00')In [8]: pd.Timestamp('2014-01-31') + MonthEnd(0)Out[8]: Timestamp('2014-01-31 00:00:00')

Example to obtain the month end as a string:

from pandas.tseries.offsets import MonthEnd(pd.Timestamp.now() + MonthEnd(0)).strftime('%Y-%m-%dT00:00:00')# '2014-01-31T00:00:00'

The end of the month can be the last day/minute/second/millisecond/microsecond/nanosecond of the month depending upon the offset needed by your use case. Given a date, to derive the last unit of the month, use the applicable anchored offset semantics. For example:

import pandas as pddef last_second_of_month(date: str) -> str:    return str(pd.Timestamp(date) + pd.offsets.MonthBegin() - pd.offsets.Second())

As needed, replace Second() above with Day(), Minute(), Milli(), Micro(), or Nano().

Here is an alternative implementation with the same result:

import pandas as pddef last_second_of_month(date: str) -> str:    return str((pd.Timestamp(date) + pd.offsets.MonthEnd(0)).date()) + " 23:59:59"

Examples:

>>> last_second_of_month('2020-10')'2020-10-31 23:59:59'>>> last_second_of_month('2020-10-01')'2020-10-31 23:59:59'>>> last_second_of_month('2020-10-15')'2020-10-31 23:59:59'>>> last_second_of_month('2020-10-30')'2020-10-31 23:59:59'>>> last_second_of_month('2020-10-31')'2020-10-31 23:59:59'

As a cautionary note, do not use pd.Timestamp(date) + pd.offsets.MonthEnd() + pd.offsets.Day() - pd.offsets.Second() as it doesn't work as required for the last date of a month. This observation about pd.offsets.MonthEnd(1) is credited to the answer by Martien.