表

image.png

Code

import pandas as pd

df = pd.read_excel(io='exls/avg_and_sum.xlsx', index_col='ID')

# 读取操作列
df_cols = df[['SCORE_1', 'SCORE_2', 'SCORE_3']]
# 求和并添加求和列
df['Tatal'] = df_cols.sum(axis=1)
# 求平均值并添加列
df['Average'] = df_cols.mean(axis=1)

# 最下面添加一行，做数据汇总
# 对需要求和的列进行汇总
df_add_row_sum = df[['SCORE_1', 'SCORE_2', 'SCORE_3', 'Tatal', 'Average']].mean()
# 添加汇总名称
df_add_row_sum['NAME'] = 'SUM'
# 用append追加一行
df = df.append(df_add_row_sum, ignore_index=True)
print(df)

Output

   NAME  SCORE_1  SCORE_2  SCORE_3  Tatal    Average
0     A     53.0     90.0     46.0  189.0  63.000000
1     B     33.0     71.0     32.0  136.0  45.333333
2     C     55.0     95.0     44.0  194.0  64.666667
3     D     37.0     62.0     90.0  189.0  63.000000
4     E     36.0     44.0     31.0  111.0  37.000000
5     F     61.0     60.0     57.0  178.0  59.333333
6     G     64.0     39.0     87.0  190.0  63.333333
7     H     89.0     83.0     48.0  220.0  73.333333
8     I     80.0     77.0     65.0  222.0  74.000000
9     J     59.0     51.0     39.0  149.0  49.666667
10  SUM     56.7     67.2     53.9  177.8  59.266667

如果需要ID,代码可改为

import pandas as pd

df = pd.read_excel(io='exls/avg_and_sum.xlsx')

# 读取操作列
df_cols = df[['SCORE_1', 'SCORE_2', 'SCORE_3']]
# 求和并添加求和列
df['Tatal'] = df_cols.sum(axis=1)
# 求平均值并添加列
df['Average'] = df_cols.mean(axis=1)

# 最下面添加一行，做数据汇总
# 对需要求和的列进行汇总
df_add_row_sum = df[['SCORE_1', 'SCORE_2', 'SCORE_3', 'Tatal', 'Average']].mean()
# 添加汇总名称
df_add_row_sum['NAME'] = 'SUM'
df_add_row_sum['ID'] = '11'
# 用append追加一行
df = df.append(df_add_row_sum, ignore_index=True)
print(df)