Multi-index combined reverse cumsum in Pandas

user_11952348 0
Viewed 21

I'd like to sum over all remaining entries in a 2-d phase space. For a case where I have a 2d multi-index and I want to get the cumsum over each v1 individually, doing the below will suffice:

df['output'] = df.loc[::-1].groupby(['v1'])['value'].cumsum()[::-1]
v1 v2 value output
1 1 1 3
1 2 1. 2.
1 3 1 1.
2 1 1. 3.
2 2 1. 2.
2 3. 1. 1.
3 1 1. 3.
3 2 1. 2.
3 3 1. 1.

But I'd like to instead integrate over v1 and v2 simultaneously. e.g. if v1=2 and v2=3, I'd like the result (v1=2 & v2=3) + (v1=3 & v2&3). Or for the same table, the desired result:

v1 v2 value output
1 1 1 9
1 2 1. 6.
1 3 1 3.
2 1 1. 6.
2 2 1. 4.
2 3. 1. 2.
3 1 1. 3.
3 2 1. 2.
3 3 1. 1.

Any ideas how to do this?

pandas python
1 Answers

I am not sure of your exact requirements, but to do this within the data frame, it is best just to iterate over each row and sum over the values in the rows whose indices are greater than the index values, respectively, for the row:

    for i, row in df.iterrows():
        df.loc[i, 'output'] = df.loc[(df.v2 >= row.v2) & (df.v1 >= row.v1), 'value'].sum()

For the data frame given, this produces the expected result:

    df = pd.DataFrame({'v1': [1, 1, 1, 2, 2, 2, 3, 3, 3],
                       'v2': [1, 2, 3, 1, 2, 3, 1, 2, 3],
                       'value': [1, 1, 1, 1, 1, 1, 1, 1, 1]})
    for i, row in df.iterrows():
        df.loc[i, 'output'] = df.loc[(df.v2 >= row.v2) & (df.v1 >= row.v1), 'value'].sum()

    print(df)

        v1  v2  value  output
     0   1   1      1     9.0
     1   1   2      1     6.0
     2   1   3      1     3.0
     3   2   1      1     6.0
     4   2   2      1     4.0
     5   2   3      1     2.0
     6   3   1      1     3.0
     7   3   2      1     2.0
     8   3   3      1     1.0

Note that iterating over rows here is just as efficient (if not more) than calling groupby and cumsum. A better approach is to think of this as lower-triangular (sub) 2-D arrays in numpy and performing sum operations on those.

user_65416590
Related