按班級(jí)分析成績(jī)結(jié)構(gòu)主講人：岑遠(yuǎn)紅重慶市九龍坡職業(yè)教育中心問(wèn)題描述教師和學(xué)校為了掌握學(xué)生的學(xué)習(xí)情況，需要對(duì)學(xué)生成績(jī)的結(jié)構(gòu)進(jìn)行分析，查看各分?jǐn)?shù)段的學(xué)生人數(shù)占比，這是教學(xué)質(zhì)量分析中常用的操作，最后要得到一個(gè)表格，分別展示各學(xué)科的優(yōu)分率、及格率和平均分，用以分析成績(jī)結(jié)構(gòu)。姓名C語(yǔ)言(70分)數(shù)據(jù)結(jié)構(gòu)(70分)算法分析(60分)邱俊松675353胡艷675452孔航675254黃莉645553古瑞616050............卿成16513歐金霞10617晏麗7917肖權(quán)利8512齊云瑞000表5.2.1學(xué)生專(zhuān)業(yè)課成績(jī)表輸出結(jié)果

C語(yǔ)言(70分)數(shù)據(jù)結(jié)構(gòu)(70分)算法分析(60分)

班級(jí)優(yōu)分率及格率平均分優(yōu)分率及格率平均分優(yōu)分率及格率平均分2019級(jí)計(jì)算機(jī)1班42%65%470%20%2612%42%322019級(jí)計(jì)算機(jī)2班59%80%552%57%4220%72%402019級(jí)計(jì)算機(jī)3班19%71%447%29%350%40%312019級(jí)計(jì)算機(jī)4班28%72%480%5%282%35%33表5.2.2成績(jī)結(jié)構(gòu)分析表

題前思考問(wèn)題描述問(wèn)題解答結(jié)果表中是否有分組？是按哪一列來(lái)分的組？

需要根據(jù)班級(jí)對(duì)學(xué)生分組對(duì)哪些列進(jìn)行了統(tǒng)計(jì)分析？

對(duì)“C語(yǔ)言(70分)”、“數(shù)據(jù)結(jié)構(gòu)(70分)”和“算法分析(60分)”三列進(jìn)行了統(tǒng)計(jì)分析采用了哪些方法對(duì)數(shù)據(jù)列進(jìn)行了匯總操作？

分別采用求百分比和平均值的方法進(jìn)行匯總表5.2.3 問(wèn)題分析importpandasaspdimportnumpyasnpgrade=pd.read_excel(r"D:\pydata\項(xiàng)目五\計(jì)算機(jī)專(zhuān)業(yè)成績(jī).xlsx")grade=grade.loc[:,['姓名','班級(jí)','C語(yǔ)言(70分)','數(shù)據(jù)結(jié)構(gòu)(70分)','算法分析(60分)']]grade=pd.pivot_table(grade,①index=['班級(jí)’],②values=['C語(yǔ)言(70分)','數(shù)據(jù)結(jié)構(gòu)(70分)','算法分析(60分)'],③aggfunc={'C語(yǔ)言(70分)':[lambdas:s[s>=56].shape[0]/s.shape[0],lambdas:s[s>=42].shape[0]/s.shape[0],np.mean],④'數(shù)據(jù)結(jié)構(gòu)(70分)':[lambdas:s[s>=56].shape[0]/s.shape[0],lambdas:s[s>=42].shape[0]/s.shape[0],np.mean],'算法分析(60分)':[lambdas:s[s>=48].shape[0]/s.shape[0],lambdas:s[s>=36].shape[0]/s.shape[0],np.mean]})grade.rename({'<lambda_0>':'優(yōu)分率','<lambda_1>':'及格率','mean':'平均分'},axis=1,inplace=True)⑤grade.loc[:,(slice(None),['優(yōu)分率','及格率'])]=grade.loc[:,(slice(None),['優(yōu)分率','及格率'])].apply(lambdas:s.map(lambdax:format(x,".0%")),axis=1)⑥grade.loc[:,(slice(None),['平均分'])]=grade.loc[:,(slice(None),['平均分'])].apply(lambdas:s.map(lambdax:f'{x:.1f}'),axis=1)⑦程序代碼importpandasaspdimportnumpyasnpgrade=pd.read_excel(r"D:\pydata\項(xiàng)目五\計(jì)算機(jī)專(zhuān)業(yè)成績(jī).xlsx")grade=grade.loc[:,['姓名','班級(jí)','C語(yǔ)言(70分)','數(shù)據(jù)結(jié)構(gòu)(70分)','算法分析(60分)']]grade=pd.pivot_table(grade,①index=['班級(jí)’],②values=['C語(yǔ)言(70分)','數(shù)據(jù)結(jié)構(gòu)(70分)','算法分析(60分)'],③aggfunc={'C語(yǔ)言(70分)':[lambdas:s[s>=56].shape[0]/s.shape[0],lambdas:s[s>=42].shape[0]/s.shape[0],np.mean],④'數(shù)據(jù)結(jié)構(gòu)(70分)':[lambdas:s[s>=56].shape[0]/s.shape[0],lambdas:s[s>=42].shape[0]/s.shape[0],np.mean],'算法分析(60分)':[lambdas:s[s>=48].shape[0]/s.shape[0],lambdas:s[s>=36].shape[0]/s.shape[0],np.mean]})grade.rename({'<lambda_0>':'優(yōu)分率','<lambda_1>':'及格率','mean':'平均分'},axis=1,inplace=True)⑤grade.loc[:,(slice(None),['優(yōu)分率','及格率'])]=grade.loc[:,(slice(None),['優(yōu)分率','及格率'])].apply(lambdas:s.map(lambdax:format(x,".0%")),axis=1)⑥grade.loc[:,(slice(None),['平均分'])]=grade.loc[:,(slice(None),['平均分'])].apply(lambdas:s.map(lambdax:f'{x:.1f}'),axis=1)⑦程序代碼函數(shù)的第一個(gè)參數(shù)就是制作數(shù)據(jù)透視表的原始數(shù)據(jù)。importpandasaspdimportnumpyasnpgrade=pd.read_excel(r"D:\pydata\項(xiàng)目五\計(jì)算機(jī)專(zhuān)業(yè)成績(jī).xlsx")grade=grade.loc[:,['姓名','班級(jí)','C語(yǔ)言(70分)','數(shù)據(jù)結(jié)構(gòu)(70分)','算法分析(60分)']]grade=pd.pivot_table(grade,①index=['班級(jí)’],②values=['C語(yǔ)言(70分)','數(shù)據(jù)結(jié)構(gòu)(70分)','算法分析(60分)'],③aggfunc={'C語(yǔ)言(70分)':[lambdas:s[s>=56].shape[0]/s.shape[0],lambdas:s[s>=42].shape[0]/s.shape[0],np.mean],④'數(shù)據(jù)結(jié)構(gòu)(70分)':[lambdas:s[s>=56].shape[0]/s.shape[0],lambdas:s[s>=42].shape[0]/s.shape[0],np.mean],'算法分析(60分)':[lambdas:s[s>=48].shape[0]/s.shape[0],lambdas:s[s>=36].shape[0]/s.shape[0],np.mean]})grade.rename({'<lambda_0>':'優(yōu)分率','<lambda_1>':'及格率','mean':'平均分'},axis=1,inplace=True)⑤grade.loc[:,(slice(None),['優(yōu)分率','及格率'])]=grade.loc[:,(slice(None),['優(yōu)分率','及格率'])].apply(lambdas:s.map(lambdax:format(x,".0%")),axis=1)⑥grade.loc[:,(slice(None),['平均分'])]=grade.loc[:,(slice(None),['平均分'])].apply(lambdas:s.map(lambdax:f'{x:.1f}'),axis=1)⑦程序代碼關(guān)鍵字參數(shù)index表示行索引，指出統(tǒng)計(jì)之前的分組依據(jù)。importpandasaspdimportnumpyasnpgrade=pd.read_excel(r"D:\pydata\項(xiàng)目五\計(jì)算機(jī)專(zhuān)業(yè)成績(jī).xlsx")grade=grade.loc[:,['姓名','班級(jí)','C語(yǔ)言(70分)','數(shù)據(jù)結(jié)構(gòu)(70分)','算法分析(60分)']]grade=pd.pivot_table(grade,①index=['班級(jí)’],②values=['C語(yǔ)言(70分)','數(shù)據(jù)結(jié)構(gòu)(70分)','算法分析(60分)'],③aggfunc={'C語(yǔ)言(70分)':[lambdas:s[s>=56].shape[0]/s.shape[0],lambdas:s[s>=42].shape[0]/s.shape[0],np.mean],④'數(shù)據(jù)結(jié)構(gòu)(70分)':[lambdas:s[s>=56].shape[0]/s.shape[0],lambdas:s[s>=42].shape[0]/s.shape[0],np.mean],'算法分析(60分)':[lambdas:s[s>=48].shape[0]/s.shape[0],lambdas:s[s>=36].shape[0]/s.shape[0],np.mean]})grade.rename({'<lambda_0>':'優(yōu)分率','<lambda_1>':'及格率','mean':'平均分'},axis=1,inplace=True)⑤grade.loc[:,(slice(None),['優(yōu)分率','及格率'])]=grade.loc[:,(slice(None),['優(yōu)分率','及格率'])].apply(lambdas:s.map(lambdax:format(x,".0%")),axis=1)⑥grade.loc[:,(slice(None),['平均分'])]=grade.loc[:,(slice(None),['平均分'])].apply(lambdas:s.map(lambdax:f'{x:.1f}'),axis=1)⑦程序代碼關(guān)鍵字參數(shù)values表示要匯總的值。importpandasaspdimportnumpyasnpgrade=pd.read_excel(r"D:\pydata\項(xiàng)目五\計(jì)算機(jī)專(zhuān)業(yè)成績(jī).xlsx")grade=grade.loc[:,['姓名','班級(jí)','C語(yǔ)言(70分)','數(shù)據(jù)結(jié)構(gòu)(70分)','算法分析(60分)']]grade=pd.pivot_table(grade,①index=['班級(jí)’],②values=['C語(yǔ)言(70分)','數(shù)據(jù)結(jié)構(gòu)(70分)','算法分析(60分)'],③aggfunc={'C語(yǔ)言(70分)':[lambdas:s[s>=56].shape[0]/s.shape[0],lambdas:s[s>=42].shape[0]/s.shape[0],np.mean],④'數(shù)據(jù)結(jié)構(gòu)(70分)':[lambdas:s[s>=56].shape[0]/s.shape[0],lambdas:s[s>=42].shape[0]/s.shape[0],np.mean],'算法分析(60分)':[lambdas:s[s>=48].shape[0]/s.shape[0],lambdas:s[s>=36].shape[0]/s.shape[0],np.mean]})grade.rename({'<lambda_0>':'優(yōu)分率','<lambda_1>':'及格率','mean':'平均分'},axis=1,inplace=True)⑤grade.loc[:,(slice(None),['優(yōu)分率','及格率'])]=grade.loc[:,(slice(None),['優(yōu)分率','及格率'])].apply(lambdas:s.map(lambdax:format(x,".0%")),axis=1)⑥grade.loc[:,(slice(None),['平均分'])]=grade.loc[:,(slice(None),['平均分'])].apply(lambdas:s.map(lambdax:f'{x:.1f}'),axis=1)⑦程序代碼關(guān)鍵字參數(shù)aggfunc表示匯總函數(shù)，也就是對(duì)各列進(jìn)行的匯總操作。程序代碼

C語(yǔ)言(70分)數(shù)據(jù)結(jié)構(gòu)(70分)算法分析(60分)

班級(jí)<lambda_0><lambda_1>mean<lambda_0><lambda_1>mean<lambda_0><lambda_1>mean2019級(jí)計(jì)算機(jī)1班0.4250000.65000046.9250000.0000000.20000026.0000000.1250000.42500032.4000002019級(jí)計(jì)算機(jī)2班0.5869570.80434854.8695650.0217390.56521742.4130430.1956520.71739140.0652172019級(jí)計(jì)算機(jī)3班0.1904760.71428644.1428570.0714290.28571435.4047620.0000000.40476230.8571432019級(jí)計(jì)算機(jī)4班0.2750000.72500047.6250000.0000000.05000027.7000000.0250000.35000033.075000表5.2.4 pivot_table()函數(shù)的執(zhí)行結(jié)果importpandasaspdimportnumpyasnpgrade=pd.read_excel(r"D:\pydata\項(xiàng)目五\計(jì)算機(jī)專(zhuān)業(yè)成績(jī).xlsx")grade=grade.loc[:,['姓名','班級(jí)','C語(yǔ)言(70分)','數(shù)據(jù)結(jié)構(gòu)(70分)','算法分析(60分)']]grade=pd.pivot_table(grade,①index=['班級(jí)’],②values=['C語(yǔ)言(70分)','數(shù)據(jù)結(jié)構(gòu)(70分)','算法分析(60分)'],③aggfunc={'C語(yǔ)言(70分)':[lambdas:s[s>=56].shape[0]/s.shape[0],lambdas:s[s>=42].shape[0]/s.shape[0],np.mean],④'數(shù)據(jù)結(jié)構(gòu)(70分)':[lambdas:s[s>=56].shape[0]/s.shape[0],lambdas:s[s>=42].shape[0]/s.shape[0],np.mean],'算法分析(60分)':[lambdas:s[s>=48].shape[0]/s.shape[0],lambdas:s[s>=36].shape[0]/s.shape[0],np.mean]})grade.rename({'<lambda_0>':'優(yōu)分率','<lambda_1>':'及格率','mean':'平均分'},axis=1,inplace=True)⑤grade.loc[:,(slice(None),['優(yōu)分率','及格率'])]=grade.loc[:,(slice(None),['優(yōu)分率','及格率'])].apply(lambdas:s.map(lambdax:format(x,".0%")),axis=1)⑥grade.loc[:,(slice(None),['平均分'])]=grade.loc[:,(slice(None),['平均分'])].apply(lambdas:s.map(lambdax:f'{x:.1f}'),axis=1)⑦程序代碼將列名改為可讀性好的名稱(chēng)。程序代碼

C語(yǔ)言(70分)數(shù)據(jù)結(jié)構(gòu)(70分)算法分析(60分)

班級(jí)優(yōu)分率及格率平均分優(yōu)分率及格率平均分優(yōu)分率及格率平均分2019級(jí)計(jì)算機(jī)1班0.4250000.65000046.9250000.0000000.20000026.0000000.1250000.42500032.4000002019級(jí)計(jì)算機(jī)2班0.5869570.80434854.8695650.0217390.56521742.4130430.1956520.71739140.0652172019級(jí)計(jì)算機(jī)3班0.1904760.71428644.1428570.0714290.28571435.4047620.0000000.40476230.8571432019級(jí)計(jì)算機(jī)4班0.2750000.72500047.6250000.0000000.05000027.7000000.0250000.35000033.075000表5.2.5 修改列名之后的數(shù)據(jù)框importpandasaspdimportnumpyasnpgrade=pd.read_excel(r"D:\pydata\項(xiàng)目五\計(jì)算機(jī)專(zhuān)業(yè)成績(jī).xlsx")grade=grade.loc[:,['姓名','班級(jí)','C語(yǔ)言(70分)','數(shù)據(jù)結(jié)構(gòu)(70分)','算法分析(60分)']]grade=pd.pivot_table(grade,①index=['班級(jí)’],②values=['C語(yǔ)言(70分)','數(shù)據(jù)結(jié)構(gòu)(70分)','算法分析(60分)'],③aggfunc={'C語(yǔ)言(70分)':[lambdas:s[s>=56].shape[0]/s.shape[0],lambdas:s[s>=42].shape[0]/s.shape[0],np.mean],④'數(shù)據(jù)結(jié)構(gòu)(70分)':[lambdas:s[s>=56].shape[0]/s.shape[0],lambdas:s[s>=42].shape[0]/s.shape[0],np.mean],'算法分析(60分)':[lambdas:s[s>=48].shape[0]/s.shape[0],lambdas:s[s>=36].shape[0]/s.shape[0],np.mean]})grade.rename({'<lambda_0>':'優(yōu)分率','<lambda_1>':'及格率','mean':'平均分'},axis=1,inplace=True)⑤grade.loc[:,(slice(None),['優(yōu)分率','及格率'])]=grade.loc[:,(slice(None),['優(yōu)分率','及格率'])].apply(lambdas:s.map(lambdax:format(x,".0%")),axis=1)⑥grade.loc[:,(slice(None),['平均分'])]=grade.loc[:,(slice(None),['平均分'])].apply(lambdas:s.map(lambdax:f'{x:.1f}'),axis=1)⑦程序代碼將優(yōu)分率和及格率兩列數(shù)據(jù)改為百分比格式。程序代碼

C語(yǔ)言(70分)數(shù)據(jù)結(jié)構(gòu)(70分)算法分析(60分)

班級(jí)優(yōu)分率及格率平均分優(yōu)分率及格率平均分優(yōu)分率及格率平均分2019級(jí)計(jì)算機(jī)1班42%65%46.9250000%20%26.00000012%42%32.4000002019級(jí)計(jì)算機(jī)2班59%80%54.8695652%57%42.41304320%72%40.0652172019級(jí)計(jì)算機(jī)3班19%71%44.1428577%29%35.4047620%40%30.8571432019級(jí)計(jì)算機(jī)4班28%72%47.6250000%5%27.7000002%35%33.075000表5.2.6 設(shè)置百分比之后的數(shù)據(jù)框importpandasaspdimportnumpyasnpgrade=pd.read_excel(r"D:\pydata\項(xiàng)目五\計(jì)算機(jī)專(zhuān)業(yè)成績(jī).xlsx")grade=grade.loc[:,['姓名','班級(jí)','C語(yǔ)言(70分)','數(shù)據(jù)結(jié)構(gòu)(70分)','算法分析(60分)']]grade=pd.pivot_table(grade,①index=['班級(jí)’],②values=['C語(yǔ)言(70分)','數(shù)據(jù)結(jié)構(gòu)(70分)','算法分析(60分)'],③aggfunc={'C語(yǔ)言(70分)':[lambdas:s[s>=56].shape[0]/s.shape[0],lambdas:s[s>=42].shape[0]/s.shape[0],np.mean],④'數(shù)據(jù)結(jié)構(gòu)(70分)':[lambdas:s[s>=56].shape[0]/s.shape[0],lambdas:s[s>=42].shape[0]/s.shape[0],np.mean],'算法分析(60分)':[lambdas:s[s>=48].shape[0]/s.shape[0],lambdas:s[s>=36].shape[0]/s.shape[0],np.mean]})grade.rename({'<lambda_0>':'優(yōu)分率','<lambda_1>':'及格率','mean':'平均分'},axis=1,inplace=True)⑤grade.loc[:,(slice(None),['優(yōu)分率','及格率'])]=grade.loc[:,(slice(None),['優(yōu)分率','及格率'])].apply(lambdas:s.map(lambdax:format(x,".0%")),axis=1)⑥grade.loc[:,(slice(None),['平均分'])]=grade.loc[:,(slice(None),['平均分'])].apply(lambdas:s.map(lambdax:f'{x:.1f}'),axis=1)⑦程序代碼將平均為轉(zhuǎn)換為含有一位小數(shù)的字符串。程序代碼

C語(yǔ)言(70分)數(shù)據(jù)結(jié)構(gòu)(70分)算法分析(60分)

班級(jí)優(yōu)分率及格率平均分優(yōu)分率及格率平均分優(yōu)分率及格率平均分2019級(jí)計(jì)算機(jī)1班42%65%470%20%2612%42%322019級(jí)計(jì)算機(jī)2班59%80%552%57%4220%72%402019級(jí)計(jì)算機(jī)3班19%71%447%29%350%40%312019級(jí)計(jì)算機(jī)4班28%72%480%5%282%35%33表5.2.8將平均分小數(shù)位數(shù)設(shè)為0之后的結(jié)果 1.調(diào)用pandas.pivot_table（）函數(shù)，指定要統(tǒng)計(jì)的數(shù)據(jù)框、行索引(index)、數(shù)據(jù)列(values)和統(tǒng)計(jì)方法(aggfunc)2.將列名改為可讀性好的名稱(chēng)。3.將