Cumulative sum based on another column's boolean value

user_17577981 0
Viewed 65

I have a pandas dataframe with the following format

name | is_valid | account | transaction 
Adam |  True    |  debit  |   +10       
Adam |  False   |  credit |   +10       
Adam |  True    |  credit |   +10       
Benj |  True    |  credit |   +10       
Benj |  False   |  debit  |   +10       
Adam |  True    |  credit |   +10

I want to create two new columns credit_cumulative and debit_cumulative. For credit_cumulative, it counts the cumulative sum of the transaction column for the corresponding person, and for the corresponding account in that row, the transaction column will count only if is_valid column is true. debit_cumulative wants to behave in the same way.

In the above example, the result should be:

from | is_valid | account | transaction | credit_cumulative | debit_cumulative
Adam |  True    |  debit  |   +10       |       0           |        10
Adam |  False   |  credit |   +10       |       0           |        10
Adam |  True    |  credit |   +10       |       10          |        10
Benj |  True    |  credit |   +10       |       10          |        0
Benj |  False   |  debit  |   +10       |       10          |        0
Adam |  True    |  credit |   +10       |       20          |        10

To illustrate, the first row is Adam, and account is debit, is_valid is true, so we increase debit_cumulative by 10.

For the second row, is_valid is negative. So transaction does not count. Name is Adam, is credit_cumulative and debit_cumulative will remain the same.

All rows shall behave this way.

Here is the code to the original data I described:

d = {'name': ['Adam', 'Adam', 'Adam', 'Benj', 'Benj', 'Adam'], 'is_valid': [True, False, True, True, False, True], 'account': ['debit', 'credit', 'credit', 'credit', 'debit', 'credit'], 'transaction': [10, 10, 10, 10, 10, 10]}
df = pd.DataFrame(data=d)
cumsum pandas-groupby pandas python
2 Answers

Try:

# credit

mask = df.is_valid.eq(True) & df.account.eq("credit")
df.loc[mask, "credit_cumulative"] = (
    df[mask].groupby(["name", "account"])["transaction"].cumsum()
)

df["credit_cumulative"] = df.groupby("name")["credit_cumulative"].apply(
    lambda x: x.ffill().fillna(0)
)

# debit

mask = df.is_valid.eq(True) & df.account.eq("debit")
df.loc[mask, "debit_cumulative"] = (
    df[mask].groupby(["name", "account"])["transaction"].cumsum()
)

df["debit_cumulative"] = df.groupby("name")["debit_cumulative"].apply(
    lambda x: x.ffill().fillna(0)
)

print(df)

Prints:

   name  is_valid account  transaction  credit_cumulative  debit_cumulative
0  Adam      True   debit           10                0.0              10.0
1  Adam     False  credit           10                0.0              10.0
2  Adam      True  credit           10               10.0              10.0
3  Benj      True  credit           10               10.0               0.0
4  Benj     False   debit           10               10.0               0.0
5  Adam      True  credit           10               20.0              10.0
user_100359850

Here are a few ways to do what your question asks:

Method 1:

dfc = pd.concat([
    df[['name','is_valid']], 
    df.transaction[df.account=='credit'].reindex(df.index, fill_value=0).rename('credit_cumulative'),
    df.transaction[df.account=='debit'].reindex(df.index, fill_value=0).rename('debit_cumulative')
], axis=1)
dfc.loc[~dfc.is_valid, ['credit_cumulative', 'debit_cumulative']] = 0
df = pd.concat([df, dfc.drop(columns='is_valid').groupby('name').cumsum()], axis=1)

Output:

   name  is_valid account  transaction  credit_cumulative  debit_cumulative
0  Adam      True   debit           10                  0                10
1  Adam     False  credit           10                  0                10
2  Adam      True  credit           10                 10                10
3  Benj      True  credit           10                 10                 0
4  Benj     False   debit           10                 10                 0
5  Adam      True  credit           10                 20                10

Explanation:

  • Create a new dataframe that partitions transaction into two new columns for credit and debit and adds these to the name and is_valid columns of the original dataframe
  • Zero out these new columns where is_valid is False
  • Use groupby().cumsum() to aggregate these columns by name
  • Use concat() to add the cumsum() columns to the original dataframe.

Method 2:

If we want, we can go further and replace the dfc assignment by factoring out the similar processing of credit and debit into a list comprehension as in the following:

dfc = pd.concat([
    df[['name','is_valid']], 
    *[df.transaction[df.account==transType].reindex(df.index, fill_value=0).rename(
    transType + '_cumulative') for transType in ('credit', 'debit')]
], axis=1)

Method 3:

Another dfc assignment alternative using unstack() is this:

dfc = pd.concat([
    df[['name','is_valid']],
    df.set_index('account', append=True)['transaction'].unstack(level=-1, fill_value=0).rename(
    columns={x:x + '_cumulative' for x in df.account.unique()})
], axis=1)
user_181354540
Related