Is there a pandas function to return instantaneous values from cummulated sum?

user_7277294 0
Viewed 110

I have a dataframe which columns have acummulated values, i.e. a financial report for all four quarters in a year. I need to de-accumulate the values in order to get the values for every period instead of the accumulated sum over time.

I've already built a function that uses loops for every column in the dataframe and substracts the previous column from the selected column (very inefficient). But in some cases, I have monthly data instead of quarterly, so the number of periods changes from 4 to 12.

Image of dataframe I have

I need a function that takes the number of periods (like a rolling sum that takes the number of windows as input) and outputs the dissagregated sum of the dataframe.

Thank you!

pandas numpy python
2 Answers

Take a diff within group. Need to .fillna to get the first value.

Sample Data

df = pd.DataFrame(np.random.randint(1, 10, (3, 8)))
df.columns = [f'{y}-{str(m).zfill(2)}' for y in range(2012, 2014) for m in range(1, 5)]
df = df.cumsum(1)  # For illustration, don't worry about across years.
df['tag'] = 'foo'

   2012-01  2012-02  2012-03  2012-04  2013-01  2013-02  2013-03  2013-04  tag
0        5        6       15       23       25       28       36       45  foo
1        5        9       14       17       24       27       31       38  foo
2        4       10       11       19       24       29       38       41  foo

Code:

df.groupby(df.columns.str[0:4], axis=1).diff(1).fillna(df)

   2012-01  2012-02  2012-03  2012-04  2013-01  2013-02  2013-03  2013-04  tag
0      5.0      1.0      9.0      8.0     25.0      3.0      8.0      9.0  foo
1      5.0      4.0      5.0      3.0     24.0      3.0      4.0      7.0  foo
2      4.0      6.0      1.0      8.0     24.0      5.0      9.0      3.0  foo
user_43333590

You can do those steps:

import pandas as pd
df = pd.DataFrame([[1, 3, 2], [100, 90, 110]], columns=['2019-01', '2019-02', '2019-03'], index=['A', 'B'])
df = df.unstack().reset_index(name='value').sort_values(['level_1', 'level_0'])
df['delta'] = df.groupby('level_1').diff()
df['delta'].fillna(df.value, inplace=True)
df.pivot(index='level_1', columns='level_0', values='delta')
user_48877480
Related