Home > database >  Substract 2 Columns and show the current Value on a new one
Substract 2 Columns and show the current Value on a new one

Time:12-02

I have this DataFrame:

Names Account_1 Account_2 ID_Movement Less_1 Less_2
Peter 35 70 Movement_1 0 5
Peter 35 70 Movement_2 6 0
Peter 35 70 Movement_3 1 0
Peter 35 70 Movement_4 0 2
Jhon 55 60 Movement_5 6 0
Jhon 55 60 Movement_6 0 2
Jhon 55 60 Movement_7 0 3
Jhon 55 60 Movement_8 12 0
Jhon 55 60 Movement_9 6 0
William 34 88 Movement_10 0 8
William 34 88 Movement_11 0 9
William 34 88 Movement_12 0 5

I was trying to create a new column with the current value of the account of each person with this code:

s = (df['Account_1']).sub(df['Less_1']).groupby(df['Names']).cumsum() df2['New_Account1'] = s

Desired Output:

Names Account 1 Account 2 ID_Movement Less_1 Less_2 New_Account1 New_Account2
Peter 35 70 Movement_1 0 11 35 59
Peter 35 70 Movement_2 6 0 29 59
Peter 35 70 Movement_3 6 0 23 59
Peter 35 70 Movement_4 0 4 23 55
Jhon 55 60 Movement_5 6 0 49 60
Jhon 55 60 Movement_6 0 14 49 46
Jhon 55 60 Movement_7 0 13 49 33
Jhon 55 60 Movement_8 12 0 37 33
Jhon 55 60 Movement_9 6 0 31 33
William 34 88 Movement_10 12 0 22 88
William 34 88 Movement_11 0 9 22 79
William 34 88 Movement_12 0 5 22 74

CodePudding user response:

Use groupby.cumsum and subtraction with to_numpy():

df[['New_Account1', 'New_Account2']] = (df[['Account_1', 'Account_2']]
                                        - df.groupby('Names')[['Less_1', 'Less_2']]
                                            .cumsum().to_numpy()
                                        )

Output:

      Names  Account_1  Account_2  ID_Movement  Less_1  Less_2  New_Account1  New_Account2
0     Peter         35         70   Movement_1       0       5            35            65
1     Peter         35         70   Movement_2       6       0            29            65
2     Peter         35         70   Movement_3       1       0            28            65
3     Peter         35         70   Movement_4       0       2            28            63
4      Jhon         55         60   Movement_5       6       0            49            60
5      Jhon         55         60   Movement_6       0       2            49            58
6      Jhon         55         60   Movement_7       0       3            49            55
7      Jhon         55         60   Movement_8      12       0            37            55
8      Jhon         55         60   Movement_9       6       0            31            55
9   William         34         88  Movement_10       0       8            34            80
10  William         34         88  Movement_11       0       9            34            71
11  William         34         88  Movement_12       0       5            34            66

CodePudding user response:

Try to use lambda

df['New_Account1'] = df.apply(lambda row : (row['Account 1'] - row['Less_1']), axis = 1)

CodePudding user response:

Perharps you need to use a groupby

  • Related