hi I have a dataset that looks much like this data frame below:
#Table1 :
print("Table1: Current Table")
data = [['ALFA', 35, 47, 67, 44, 193],
['Bravo', 51, 52, 16, 8, 127],
['Charlie', 59, 75, 2, 14, 150],
['Delta', 59, 75, 2, 34, 170],
['Echo', 59, 75, 2, 14, 150],
['Foxtrot', 40, 43, 26, 27, 136],
['Golf', 35, 31, 22, 13, 101],
['Hotel', 89, 58, 24, 34, 205]]
df = pd.DataFrame(data, columns= ['Objects', 'Column1', 'Column2', 'Column3', 'Column4', 'Total'])
#df.loc[:,'Total'] = df.sum(axis=1)
print(df)
i would want to get the percentage of all cells against their row totals (calculated in column 'Total') such that it looks this:
#Table2 :
print('Table2: Expected Outcome')
data2 = [['ALFA',18.1, 24.4, 34.7, 22.8, 193],
['Bravo',40.2, 40.9, 12.6, 6.3, 127],
['Charlie',39.3, 50.0, 1.3, 9.3, 150],
['Delta',34.7, 44.1, 1.2, 20.0, 170],
['Echo',39.3, 50.0, 1.3, 9.3, 150],
['Foxtrot',29.4, 31.6, 19.1, 19.9, 136],
['Hotel',34.7, 30.7, 21.8, 12.9, 101],
['Golf',43.4, 28.3, 11.7, 16.6, 205]]
df2 = pd.DataFrame(data2, columns= ['Objects', 'Column1', 'Column2', 'Column3', 'Column4', 'Total']) #.round(decimals=1)
#df.loc[:,'Total'] = df.sum(axis=1)
print(df2)
I am not really interested if the total column does change, is recalculated or have to be dropped in the process; but for completeness sake it would be good to have a 'Total' column along with the cells' percentages
Use fast vecorized division all columns without Objects/Total by DataFrame.div:
c = df.columns.difference(['Objects','Total'])
df[c] = df[c].div(df['Total'], axis=0).mul(100)
print(df)
Objects Column1 Column2 Column3 Column4 Total
0 ALFA 18.134715 24.352332 34.715026 22.797927 193
1 Bravo 40.157480 40.944882 12.598425 6.299213 127
2 Charlie 39.333333 50.000000 1.333333 9.333333 150
3 Delta 34.705882 44.117647 1.176471 20.000000 170
4 Echo 39.333333 50.000000 1.333333 9.333333 150
5 Foxtrot 29.411765 31.617647 19.117647 19.852941 136
6 Golf 34.653465 30.693069 21.782178 12.871287 101
7 Hotel 43.414634 28.292683 11.707317 16.585366 205