jlln - 1 year ago 92
Python Question

# Calculating YTD totals in Pandas

I have a DataFrame that looks like this:

``````   FinancialYearStart  MonthOfFinancialYear  SalesTotal
0                2015                     1          10
1                2015                     2          10
2                2015                     5          10
3                2015                     6          50
4                2016                     1          10
5                2016                     3          20
6                2016                     2          30
7                2017                     6          70
8                2017                     7          80
``````

And I would like to calculate the YTD Sales total for each month, producing a table that looks like this:

``````  FinancialYearStart  MonthOfFinancialYear  SalesTotal  YTDTotal
0                2015                     1          10        10
1                2015                     2          10        20
2                2015                     5          10        30
3                2015                     6          50        50
4                2016                     1          10        60
5                2016                     3          20        80
6                2016                     2          30       110
7                2017                     6          70        70
8                2017                     7          80       150
``````

How might I achieve this?

More specifically, I actually need to calculate this on a group by group basis.

For example:

``````Year Month Customer TotalMonthlySales
2015 1 Dog 10
2015 2 Dog 10
2015 3 Cat 20
2015 4 Dog 30
2015 5 Cat 10
2015 7 Cat 20
2015 7 Dog 10
2016 1 Dog 40
2016 2 Dog 20
2016 3 Cat 70
2016 4 Dog 30
2016 5 Cat 10
2016 6 Cat 20
2016 7 Dog 10
``````

Would give:

``````Year Month Customer TotalMonthlySales YTDSales
2015 1 Dog 10 10
2015 2 Dog 10 20
2015 3 Cat 20 20
2015 4 Dog 30 50
2015 5 Cat 10 30
2015 7 Cat 20 40
2015 7 Dog 10 60
2016 1 Dog 40 40
2016 2 Dog 20 60
2016 3 Cat 70 70
2016 4 Dog 30 90
2016 5 Cat 10 80
2016 6 Cat 20 100
2016 7 Dog 10 100
``````

``````df['YTDSales'] = df.groupby(['Year','Customer'])['TotalMonthlySales'].cumsum()
print (df)
Year  Month Customer  TotalMonthlySales  YTDSales
0   2015      1      Dog                 10        10
1   2015      2      Dog                 10        20
2   2015      3      Cat                 20        20
3   2015      4      Dog                 30        50
4   2015      5      Cat                 10        30
5   2015      7      Cat                 20        50
6   2015      7      Dog                 10        60
7   2016      1      Dog                 40        40
8   2016      2      Dog                 20        60
9   2016      3      Cat                 70        70
10  2016      4      Dog                 30        90
11  2016      5      Cat                 10        80
12  2016      6      Cat                 20       100
13  2016      7      Dog                 10       100
``````

For first:

``````df['YTDTotal'] = df.groupby('FinancialYearStart')['SalesTotal'].cumsum()
print (df)
FinancialYearStart  MonthOfFinancialYear  SalesTotal  YTDTotal
0                2015                     1          10        10
1                2015                     2          10        20
2                2015                     5          10        30
3                2015                     6          50        80
4                2016                     1          10        10
5                2016                     3          20        30
6                2016                     2          30        60
7                2017                     6          70        70
8                2017                     7          80       150
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download