How to sum up pandas columns only if the last digit of a column is less equal the last digit of this one

user_9194769 0
Viewed 38

So I have a pandas dataframe like this

contract account number1 number2
A1 A 3 1
B1 B 2 2
A2 A 1 3
A3 A 5 5
B2 B 3 4
C1 C 3 3

and I want to add two columns that sum up the number columns so far so for example, for A1 it would just be the numbers for A1, for A2 it would be the numbers for A1 and A2, for A3 it would be sum1 = number1 for A1 + number1 for A2 + number1 for A3, and so on. Basically sum the columns everything else with the same account whose last number of contract is less than the current one.

contract account number1 number2 sum1 sum2
A1 A 3 1 3 1
B1 B 2 2 2 2
A2 A 1 2 4 3
A3 A 5 5 9 8
B2 B 3 4 5 6
C1 C 3 3 3 3
pandas dataframe
3 Answers

assign, groupby and cumsum

df=df.assign(sum1=df.groupby('account')['number1'].cumsum(),sum2=df.groupby('account')['number2'].cumsum())



  contract account  number1  number2  sum1  sum2
0       A1       A        3        1     3     1
1       B1       B        2        2     2     2
2       A2       A        1        3     4     4
3       A3       A        5        5     9     9
4       B2       B        3        4     5     6
5       C1       C        3        3     3     3
user_89869750

IIUC, sort the values and groupby+agg:

df.join(
 df.sort_values(by='contract')
   .groupby('account')
   .agg(sum1=('number1','cumsum'),
        sum2=('number2','cumsum'))
 )

Output:

  contract account  number1  number2  sum1  sum2
0       A1       A        3        1     3     1
1       B1       B        2        2     2     2
2       A2       A        1        3     4     4
3       A3       A        5        5     9     9
4       B2       B        3        4     5     6
5       C1       C        3        3     3     3
user_163434640

Use groupby + cumsum:

df[['sum1', 'sum2']] = df.groupby('account')[['number1', 'number2']].cumsum()

Output:

>>> df
  contract account  number1  number2  sum1  sum2
0       A1       A        3        1     3     1
1       B1       B        2        2     2     2
2       A2       A        1        3     4     4
3       A3       A        5        5     9     9
4       B2       B        3        4     5     6
5       C1       C        3        3     3     3
Admin1
Related