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)

CodePudding user response：

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

CodePudding user response：

Here is a way to do what your question asks:

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.