I have a data frame like this.
| Company | Type | Balance |
|---|---|---|
| First | D | 15 |
| First | D | 30 |
| First | C | 40 |
| Second | D | 10 |
| Second | C | 12 |
| Second | C | 15 |
I want to create a function that adds balance if the type is C and subtract balance if the type is D. How can I do it in the pandas groupby method using apply function?
My output should be like this:
| Company | Type | Balance | New Balance |
|---|---|---|---|
| First | D | 15 | -15 |
| First | D | 30 | -45 |
| First | C | 40 | -5 |
| Second | D | 10 | -10 |
| Second | C | 12 | 2 |
| Second | C | 15 | 17 |
Use where:
df['New Balance'] = df['Balance'].where(df['Type'] != 'D', df['Balance']*-1) \
.groupby(df['Company']).cumsum()
print(df)
# Output
Company Type Balance New Balance
0 First D 15 -15
1 First D 30 -45
2 First C 40 -5
3 Second D 10 -10
4 Second C 12 2
5 Second C 15 17
Use:
temp = df['type']=='c'
df['new'] = temp.replace(False, -1).replace(True, 1)*df['bal']
df.groupby('company')['new'].cumsum()
output:
