Find the End of the Month of a Pandas Dataframe Series

Find the end of the month of a Pandas DataFrame Series

You can use pandas.tseries.offsets.MonthEnd:

from pandas.tseries.offsets import MonthEnd

df['Date'] = pd.to_datetime(df['Date'], format="%Y%m") + MonthEnd(0)

The 0 in MonthEnd just specifies to roll forward to the end of the given month. Note that if we'd used MonthEnd(1), then we'd have got the next date which is at the end of the month.
If you wanted the last day of the next month, you'd then add an extra MonthEnd(1), 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 EndOfMonth
0  200104 2001-04-30
1  200508 2005-08-31
2  201002 2010-02-28
3  201602 2016-02-29
4  199912 1999-12-31
5  200611 2006-11-30

Pandas get the Month Ending Values from Series

Use Grouper with GroupBy.last, forward filling missing values by ffill with Series.reset_index:

#if necessary
#df['date'] = pd.to_datetime(df['date'])

df = df.groupby(pd.Grouper(freq='m',key='date'))['totalShrs'].last().ffill().reset_index()
#alternative
#df = df.resample('m',on='date')['totalShrs'].last().ffill().reset_index()
print (df)
        date  totalShrs
0 2009-04-30    40000.0
1 2009-05-31    80000.0
2 2009-06-30   110000.0
3 2009-07-31   110000.0
4 2009-08-31   120000.0

Finding end of a month in a column of pandas dataframe

This behavior mentioned in docs:

When n is not 0, if the given date is not on an anchor point, it snapped to the next(previous) anchor point, and moved |n|-1 additional steps forwards or backwards.
. If the given date is on an anchor point, it is moved |n| points forwards or backwards.

You don't need np.where. The fix is simple. If you go forward, just subtract one days before adding anchor. If you go backward, add one day before subtracting anchor

You go forward to MonthEnd, so just subtract one day before adding anchor

df['CalcEnd'] = df['ActualDate']  - pd.offsets.Day() + pd.offsets.MonthEnd(1)

Out[370]:
  ActualDate    CalcEnd
0 2019-07-01 2019-07-31
1 2019-07-02 2019-07-31
2 2019-07-31 2019-07-31

How to get last day of each month from a data frame and remove the rest from the dataframe?

As you mentioned at the beginning of the question that you want to find the last day of Dec for each year, you can group the dates by year and get the last entry within a group by GroupBy.last(), as follows:

df.groupby(df['Date'].dt.year, as_index=False).last()

If you further want to find the last day of a month (as you mentioned at the end of the question), you can group the dates by year and month and get the last entry within a group by GroupBy.last(), as follows:

df.groupby([df['Date'].dt.year, df['Date'].dt.month], as_index=False).last()

How to get the last date of every date in a date column in Python so that the for loop can be avoided?

Repurposing this.

Use the given function:

import datetime

def last_day_of_month(any_day):
    # this will never fail
    # get close to the end of the month for any day, and add 4 days 'over'
    next_month = any_day.replace(day=28) + datetime.timedelta(days=4)
    # subtract the number of remaining 'overage' days to get last day of current month, or said programattically said, the previous day of the first of next month
    return next_month - datetime.timedelta(days=next_month.day)

Then after converting the 'Date' column to datetimes, apply last_day_of_month to all items in the Date column like so:

df2_1['Date']= df2_1['Date'].apply(last_day_of_month)

Get last date in each month of a time series pandas

Condla's answer came closest to what I needed except that since my time index stretched for more than a year I needed to groupby by both month and year and then select the maximum date. Below is the code I ended up with.

# tempTradeDays is the initial DatetimeIndex
dateRange = []  
tempYear = None  
dictYears = tempTradeDays.groupby(tempTradeDays.year)
for yr in dictYears.keys():
    tempYear = pd.DatetimeIndex(dictYears[yr]).groupby(pd.DatetimeIndex(dictYears[yr]).month)
    for m in tempYear.keys():
        dateRange.append(max(tempYear[m]))
dateRange = pd.DatetimeIndex(dateRange).order()

Find the end of the month of a Pandas DataFrame Series

You can use pandas.tseries.offsets.MonthEnd:

from pandas.tseries.offsets import MonthEnd

df['Date'] = pd.to_datetime(df['Date'], format="%Y%m") + MonthEnd(0)

The 0 in MonthEnd just specifies to roll forward to the end of the given month. Note that if we'd used MonthEnd(1), then we'd have got the next date which is at the end of the month.
If you wanted the last day of the next month, you'd then add an extra MonthEnd(1), 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 EndOfMonth
0  200104 2001-04-30
1  200508 2005-08-31
2  201002 2010-02-28
3  201602 2016-02-29
4  199912 1999-12-31
5  200611 2006-11-30

Want the last day of each month for a data frame in pandas

In [1]: df = pd.DataFrame({'a':range(1000)}, index=pd.date_range('2014-1-1', periods=1000))

In [2]: df.index.days_in_month
Out[2]: 
array([31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31,
       31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 28, 28, 28,

If instead the dates are in a column, not the index, you would do df['Date'].dt.days_in_month

Edit:

Above is if you had wanted the last day of the month by itself. Instead, it sounds like you want? prices.index = prices.index + pd.offsets.MonthEnd(0)

how to get the last day of the month from a given date

If you have a date d then the simplest way is to use the calendar module to find the number of days in the month:

datetime.date(d.year, d.month, calendar.monthrange(d.year, d.month)[-1])

Alternatively, using only datetime, we just find the first day of the next month and then remove a day:

datetime.date(d.year + d.month // 12, 
              d.month % 12 + 1, 1) - datetime.timedelta(1)

You might find the logic clearer if expressed as:

datetime.date(d.year + (d.month == 12), 
              (d.month + 1 if d.month < 12 else 1), 1) - datetime.timedelta(1)

---

Related Topics