Below is the input data
Name A B C D E F G Total
Ray 1 2 2 0 0 0 0 5
Tom 0 0 0 2 1 0 0 3
Sam 0 0 0 0 0 3 1 4
Below is the intended output
Name A B C D E F G Total A:B:C D:E F:G
Ray 1 2 2 0 0 0 0 5 20:40:40 0:0 0:0
Tom 0 0 0 2 1 0 0 3 0:0:0 67:33 0:0
Sam 0 0 0 0 0 3 1 4 0:0:0 0:0 75:25
CodePudding user response:
Idea is create columns groups in list cols
and then in loop divide selected columns by sum
, replace NaN
s, round and convert to integers, last join string:
#check columns names
print (df.columns.tolist())
['A', 'B', 'C', 'D', 'E', 'F', 'G', 'Total']
cols = [('A','B','C'), ('D','E'), ('F','G')]
for c in cols:
#for test
print (c)
print(df.loc[:, c])
df[f'{":".join(c)}'] = (df.loc[:, c]
.div(df.loc[:, c].sum(axis=1), axis=0)
.fillna(0)
.mul(100)
.round()
.astype(int)
.astype(str)
.agg(':'.join, axis=1))
print (df)
A B C D E F G Total A:B:C D:E F:G
0 1 2 2 0 0 0 0 5 20:40:40 0:0 0:0
1 0 0 0 2 1 0 0 3 0:0:0 67:33 0:0
2 0 0 0 0 0 3 1 4 0:0:0 0:0 75:25