python

import pandas as pd
import numpy as np
df1 = pd.read_excel('C:\Users\ASUS\Desktop/school.xlsx')
df2 = pd.read_excel('C:\Users\ASUS\Desktop/school.xlsx','Sheet2')
pd.set_option("display.precision",2)
pd.set_option("display.max_columns",None)
d1=pd.DataFrame(df1)
d2=pd.DataFrame(df2)
d3=d1.join(d2.set_index("姓名"),on="姓名")
#1
print(d3)
#2
print("按总分排序：\n",d3.sort_values(["总分"]))
#3
print(d3[d3.语文<60],d3[d3.数学<60],d3[d3.英语<60])
print(d3.iloc[0:,3:6]<60)
#4
print(d3['语文'].max(),d3['语文'].min(),d3['语文'].mean())
#5
d5=d3[d3.班级=='3班']
print(d5['语文'].mean())
#6
d6a=d3[d3.班级=='1班']
d6b=d3[d3.班级=='2班']
d6c=d3[d3.班级=='3班']
print(d6a['数学'].max(),d6a['数学'].min(),d6a['数学'].mean())
print(d6b['数学'].max(),d6b['数学'].min(),d6b['数学'].mean())
print(d6c['数学'].max(),d6c['数学'].min(),d6c['数学'].mean())
#7
group=d3.groupby("性别")
print(group.get_group('男'))
print(group.agg([np.max,np.min,np.mean]))