Add up rows according to year

Question

My dataframe look like this:

            Dividends  Stock Splits
Date                               
2014-08-13       0.28           0.0
2015-04-27       0.30           0.0
2015-07-31       0.30           0.0
2016-05-05       0.30           0.0
2016-08-15       0.30           0.0
2017-05-04       0.30           0.0
2017-08-11       0.33           0.0
2018-05-03       0.50           0.0
2018-08-08       0.60           0.0
2019-05-02       0.60           0.0
2019-05-17       0.30           0.0
2019-08-05       0.30           0.0
2019-11-18       0.30           0.0
2020-04-07       0.33           0.0
2020-05-12       0.33           0.0

I like to add up the dividends for each year and display as below:

Any help would be very much appreciated. Many Thanks!

score 2 · Accepted Answer · answered Aug 08 '20 at 05:12

You can convert the index 'Date' to datetime using pd.to_datetime, and then try groupby+sum based on index year:

df.index=pd.to_datetime(df.index)
df.groupby(df.index.year)['Dividends'].sum()

Output:

Date
2014    0.28
2015    0.60
2016    0.60
2017    0.63
2018    1.10
2019    1.50
2020    0.66

score 0 · Answer 2 · answered Aug 08 '20 at 05:10

make sure that your dataframe is indexed by date and columns are numeric data types
then it's a simple case of resample()

data = """ Date           Dividends  Stock Splits
2014-08-13       0.28           0.0
2015-04-27       0.30           0.0
2015-07-31       0.30           0.0
2016-05-05       0.30           0.0
2016-08-15       0.30           0.0
2017-05-04       0.30           0.0
2017-08-11       0.33           0.0
2018-05-03       0.50           0.0
2018-08-08       0.60           0.0
2019-05-02       0.60           0.0
2019-05-17       0.30           0.0
2019-08-05       0.30           0.0
2019-11-18       0.30           0.0
2020-04-07       0.33           0.0
2020-05-12       0.33           0.0"""
a = [[t.strip() for t in re.split("  ",l) if t.strip()!=""]  for l in [re.sub("([0-9]?[ ])*(.*)", r"\2", l) for l in data.split("\n")]]
df = pd.DataFrame(a[1:], columns=a[0])
df["Date"] = pd.to_datetime(df["Date"])
df = df.set_index("Date").astype({"Dividends":"float64","Stock Splits":"float64"})
df.resample("Y").agg({"Dividends":"sum"})

output

    Dividends
Date    
2014-12-31  0.28
2015-12-31  0.60
2016-12-31  0.60
2017-12-31  0.63
2018-12-31  1.10
2019-12-31  1.50
2020-12-31  0.66

Add up rows according to year

2 Answers2