Pandas Sum Every Value Over Certain Month Range Every Year

I want to sum data from January through June every year. I have a dataframe that looks like this: Date Value 1980-01-01 2 1980-02-01 3 1980-03-01 3 1980-04-01 2 1980-05-01 3

Solution 1:

idx = pd.Series(pd.date_range('1/1/2018', periods=100, freq='MS'),name='date')
df = pd.DataFrame(range(len(idx)), index=idx,columns=['A'])

Filter then resample.

>>>sums = df.loc[df.index.month.isin([1,2,3,4,5,6])].resample('YS').sum()>>>sums['A'].values
array([ 15,  87, 159, 231, 303, 375, 447, 519, 390], dtype=int64)
>>>sums
              A
date           
2018-01-01   15
2019-01-01   87
2020-01-01  159
2021-01-01  231
2022-01-01  303
2023-01-01  375
2024-01-01  447
2025-01-01  519
2026-01-01  390
>>>

---

I assumed that date was the index in your example. If it is a column change you need to use the dt accessor in the filter and specify the column name in resample.

dfa = pd.DataFrame({'date':idx,'A':range(len(idx))})
>>> sums = dfa.loc[dfa.date.dt.month.isin([1,2,3,4,5,6])].resample('YS',on='date').sum()
>>> sums['A'].values
array([ 15,  87, 159, 231, 303, 375, 447, 519, 390], dtype=int64)

You could also resample/aggregate on a six month frequency and just take every other result - it seems to work even if months are missing from the Series.

>>>dfq = dfa.loc[::2]>>>dfq.head()
        date  A
0 2018-01-01  0
2 2018-03-01  2
4 2018-05-01  4
6 2018-07-01  6
8 2018-09-01  8
>>>dfc = dfq.resample('6MS', on='date').sum()>>>dfc.loc[::2].head()
              A
date           
2018-01-01    6
2019-01-01   42
2020-01-01   78
2021-01-01  114
2022-01-01  150

---

If the DataFrame only contains the first six months of each year then you don't need to filter. Just resample.

>>>dfb = dfa.loc[dfa.date.dt.month.isin([1,2,3,4,5,6])]>>>dfb.resample('YS',on='date').sum().head()
              A
date           
2018-01-01   15
2019-01-01   87
2020-01-01  159
2021-01-01  231
2022-01-01  303
>>>