I have a data frame as below:
| A-open | A-close | A-total | G-open | G-close | G-total | F-open | F-close | F-total |
|---|---|---|---|---|---|---|---|---|
| 1 | 4 | 0 | 3 | 5 | 0 | 8 | 2 | 0 |
I want to sum all open and close for each title, my desirable table is:
| A-open | A-close | A-total | G-open | G-close | G-total | F-open | F-close | F-total |
|---|---|---|---|---|---|---|---|---|
| 1 | 4 | 5 | 3 | 5 | 8 | 8 | 2 | 10 |
>Solution :
Get a list of the different sets of letter columns, by splitting all the column names by ‘-‘ and keeping the first letter/letters, putting the output into a set to remove duplicates:
letters = list({i.split('-')[0] for i in df.columns.to_list()})
print(letters)
{'A', 'F', 'G'}
Then you can loop through these, and calculate the total column for each, by adding the open and close:
for l in letters:
df[l + '-total'] = df[l + '-open'] + df[l + '-close']
Final df:
A-open A-close A-total G-open G-close G-total F-open F-close F-total
0 1 4 5 3 5 8 8 2 10