Substract 2 Columns and show the current Value on a new one-CodePudding

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