第4章Excel數(shù)據(jù)處理講授：毛草根CONTENTS目錄01DataFrame表格數(shù)據(jù)的基本處理02數(shù)據(jù)的增加、修改和刪除03數(shù)據(jù)計(jì)算04Python處理Excel的相關(guān)技術(shù)數(shù)據(jù)抽取01數(shù)據(jù)抽取的概念數(shù)據(jù)抽取是從各類(lèi)數(shù)據(jù)源，如數(shù)據(jù)庫(kù)、文件系統(tǒng)等提取所需數(shù)據(jù)的過(guò)程，為后續(xù)數(shù)據(jù)處理奠定基礎(chǔ)。02數(shù)據(jù)抽取的重要性它能整合分散數(shù)據(jù)，提高分析效率，確保數(shù)據(jù)質(zhì)量，助力決策者做出正確決策。例如企業(yè)不同系統(tǒng)數(shù)據(jù)經(jīng)抽取整合，便于統(tǒng)一分析。03數(shù)據(jù)抽取的主要方法關(guān)系型數(shù)據(jù)庫(kù)用SQL查詢(xún)，文件用相應(yīng)讀取工具，網(wǎng)頁(yè)可編寫(xiě)爬蟲(chóng)，還能借助ETL工具或API獲取數(shù)據(jù)。04DataFrame數(shù)據(jù)抽取方法在Pandas的DataFrame中，可按列、按行、按條件及結(jié)合行列條件抽取數(shù)據(jù)，提升處理效率與準(zhǔn)確性。05Python處理Excel數(shù)據(jù)對(duì)比VBA的優(yōu)勢(shì)Python數(shù)據(jù)處理能力強(qiáng)，有豐富第三方庫(kù)；安全性高，病毒防護(hù)能力強(qiáng)；跨平臺(tái)兼容性好，可在多系統(tǒng)運(yùn)行。06Excel數(shù)據(jù)案例-工資表簡(jiǎn)表以“工資表簡(jiǎn)表”為例，存于C:PYEXCEL工資表簡(jiǎn)表.xlsx，用Pandas處理表格數(shù)據(jù)，如抽取數(shù)據(jù)等操作。DataFrame表格數(shù)據(jù)的基本處理01行數(shù)據(jù)處理-行索引行索引的基本原理創(chuàng)建DataFrame對(duì)象時(shí)，Pandas自動(dòng)為行分配索引，默認(rèn)從0開(kāi)始的整數(shù)序列，用于唯一標(biāo)識(shí)行數(shù)據(jù)。查看默認(rèn)行索引使用df.index可查看DataFrame的默認(rèn)行索引，如RangeIndex(start=0,stop=7,step=1)。自定義行索引通過(guò)df.index=新索引列表自定義行索引，需確保新索引列表長(zhǎng)度與DataFrame行數(shù)一致且唯一。如指定“編號(hào)”為索引：df=df.set_index('編號(hào)')。重置行索引用df_reset=df.reset_index()可重置為默認(rèn)整數(shù)索引，在數(shù)據(jù)處理中需熟練掌握索引設(shè)置方法。行數(shù)據(jù)處理-抽取單個(gè)單元數(shù)據(jù)抽取原理通過(guò)行和列的標(biāo)識(shí)信息，精準(zhǔn)定位并提取表格中的單個(gè)數(shù)據(jù)。loc方法操作使用loc：df.loc[行標(biāo)簽,列標(biāo)簽]，基于標(biāo)簽索引抽取數(shù)據(jù)，如df.loc[1,'補(bǔ)貼']。iloc方法操作使用iloc：df.iloc[行位置,列位置]，基于整數(shù)位置索引抽取數(shù)據(jù)，如df.iloc[2,3]抽取第三行第四列數(shù)據(jù)。程序示例與結(jié)果說(shuō)明以工資表簡(jiǎn)表為例，運(yùn)行程序可抽取相應(yīng)單個(gè)單元數(shù)據(jù)，需確保行標(biāo)簽、列標(biāo)簽或行位置、列位置有效。行數(shù)據(jù)處理-抽取一行數(shù)據(jù)抽取原理依據(jù)行的標(biāo)識(shí)信息，將整行數(shù)據(jù)提取出來(lái)。loc抽取一行數(shù)據(jù)使用loc：df.loc[行標(biāo)簽]，如df.loc[0]抽取第一行數(shù)據(jù)，返回Series對(duì)象。iloc抽取一行數(shù)據(jù)使用iloc：df.iloc[行位置]，如df.iloc[1]抽取第二行數(shù)據(jù)，同樣返回Series對(duì)象。示例結(jié)果與注意事項(xiàng)以工資表簡(jiǎn)表運(yùn)行程序展示抽取結(jié)果，要保證行標(biāo)簽和行位置的有效性。行數(shù)據(jù)處理-抽取多行數(shù)據(jù)抽取任意多行數(shù)據(jù)可使用loc或iloc方法，如df.loc[[0,2]]顯示編號(hào)為101、201的數(shù)據(jù)；df.iloc[[0,1]]顯示編號(hào)為101、102的數(shù)據(jù)。抽取連續(xù)多行數(shù)據(jù)運(yùn)用切片操作，如df.iloc[0:3]抽取前三行數(shù)據(jù)，切片不包含結(jié)束位置對(duì)應(yīng)的行。按條件抽取行數(shù)據(jù)采用布爾索引，如df[df['扣款']>0]抽取扣款大于0的行，條件表達(dá)式列名須存在且數(shù)據(jù)類(lèi)型支持比較操作。修改數(shù)據(jù)01修改列標(biāo)題運(yùn)用rename方法，如df.rename(columns={原列名:新列名},inplace=True)，可將“編號(hào)”列重命名為“員工編號(hào)”

，inplace=True確保直接修改原DataFrame。02修改行標(biāo)題通過(guò)直接賦值index屬性或者使用set_index方法。例如df.index=['新索引1','新索引2',...]，也可使用列表推導(dǎo)式df.index=['員工'+str(i+1).zfill(3)foriinrange(len(df))]生成新索引。03修改單元格數(shù)據(jù)使用loc或iloc屬性，如df.loc[行索引,列名]=新值

，可將“員工001”的“獎(jiǎng)金”改為650，即df.loc['員工001','獎(jiǎng)金']=650。04綜合示例與說(shuō)明程序示例先展示原DataFrame，經(jīng)列標(biāo)題、行標(biāo)題及單元格數(shù)據(jù)修改后展示新DataFrame并保存。修改時(shí)要注意新列名不沖突、新索引唯一、定位準(zhǔn)確及數(shù)據(jù)類(lèi)型一致，操作前建議備份數(shù)據(jù)。按列增加數(shù)據(jù)基本原理在Pandas的DataFrame里，可通過(guò)直接賦值或者使用loc屬性添加新列。直接賦值是在DataFrame中創(chuàng)建新列并賦予數(shù)據(jù)列表；loc屬性雖主要用于索引，但也能添加新列，新列通常自動(dòng)添加到末尾。直接賦值操作方法采用“df[新列名]=數(shù)據(jù)列表”形式，如在工資表中添加“獎(jiǎng)金”列，df['獎(jiǎng)金']=[500,600,700,800,900,1000,1100]，需保證列表長(zhǎng)度與DataFrame行數(shù)一致。loc屬性操作方法使用“df.loc[:,新列名]=數(shù)據(jù)列表”，如添加“考核”列，df.loc[:,'考核']=['一般','優(yōu)秀','一般','一般','優(yōu)秀','良好','一般']，此方法與直接賦值效果相同，直接賦值更簡(jiǎn)潔。程序示例與說(shuō)明示例程序中，先讀取工資表簡(jiǎn)表，增加“獎(jiǎng)金”和“考核”列后展示DataFrame，并保存到新文件。要注意數(shù)據(jù)列表長(zhǎng)度匹配，以及新列數(shù)據(jù)類(lèi)型可能需轉(zhuǎn)換的情況。按行增加數(shù)據(jù)增加原理向DataFrame添加新行，需先創(chuàng)建包含新行數(shù)據(jù)的字典，鍵為DataFrame列名，值為對(duì)應(yīng)數(shù)據(jù)，再將字典轉(zhuǎn)換為DataFrame對(duì)象，最后用pd.concat函數(shù)拼接。創(chuàng)建新行數(shù)據(jù)字典確保字典鍵與DataFrame現(xiàn)有列名一致，例如增加新員工工資數(shù)據(jù)，new_row={'編號(hào)':401,'姓名':'張偉','部門(mén)':'人事部','基礎(chǔ)工資':3800,'補(bǔ)貼':800,'扣款':200,'獎(jiǎng)金':400,'考核':'一般'}。轉(zhuǎn)換與拼接操作將字典轉(zhuǎn)換為DataFrame：new_row_df=pd.DataFrame([new_row])，然后使用pd.concat([df,new_row_df],ignore_index=True)拼接，ignore_index=True可重新生成連續(xù)整數(shù)索引。示例與注意事項(xiàng)程序示例展示增加新行后的DataFrame并保存。要注意新行字典鍵與列名一致，謹(jǐn)慎使用ignore_index=True參數(shù)，避免丟失原索引信息。行間計(jì)算單擊此處添加正文行間計(jì)算概念行間計(jì)算是對(duì)表格行數(shù)據(jù)處理，計(jì)算行與行數(shù)據(jù)關(guān)系或生成新行數(shù)據(jù)，分連續(xù)與不連續(xù)行間計(jì)算。連續(xù)行間計(jì)算-添加總計(jì)行到表格末尾基本原理是對(duì)連續(xù)行數(shù)據(jù)匯總計(jì)算，結(jié)果作新行添加到DataFrame末尾。方法是用sum方法匯總數(shù)值列，loc方法添加新行。如工資表計(jì)算應(yīng)發(fā)、實(shí)發(fā)工資后添加總計(jì)行。連續(xù)行間計(jì)算-根據(jù)條件在指定位置插入?yún)R總行依據(jù)特定條件，如部門(mén)，在DataFrame指定位置插入?yún)R總行。先篩選數(shù)據(jù)，計(jì)算合計(jì)，定位插入位置，用pd.concat插入。如工資表在財(cái)務(wù)部后插入總計(jì)行。不連續(xù)行間計(jì)算選擇特定行計(jì)算，結(jié)果插入指定位置。先條件篩選行，計(jì)算后用pd.concat插入。如工資表按編號(hào)篩選行計(jì)算合計(jì)并插入。刪除行和列數(shù)據(jù)刪除原理與drop方法在Pandas庫(kù)中，drop方法用于刪除DataFrame中的行或列數(shù)據(jù)，通過(guò)指定axis參數(shù)確定刪除列（axis=1）還是行（axis=0，默認(rèn)值），inplace參數(shù)控制是否直接修改原始DataFrame。刪除行數(shù)據(jù)操作使用df.drop(index=[行索引],inplace=True)語(yǔ)句，如df.drop(index=7,inplace=True)刪除索引為7的行，index可指定單個(gè)或多個(gè)索引。刪除列數(shù)據(jù)操作使用df.drop(columns=[列名],inplace=True)語(yǔ)句，如df.drop(columns=['考核'],inplace=True)刪除“考核”列，columns可指定單個(gè)或多個(gè)列名。刪除行和列數(shù)據(jù)程序示例與注意要點(diǎn)程序示例先展示原DataFrame，刪除“考核”列與索引為7的行后展示新DataFrame并保存。操作前確認(rèn)列名或行索引準(zhǔn)確，可批量刪除，注意inplace=True的使用及顯式指定index和columns參數(shù)提升程序可讀性。數(shù)據(jù)的增加、修改和刪除02行列移動(dòng)列移動(dòng)原理與方法通過(guò)調(diào)整列順序?qū)崿F(xiàn)列移動(dòng)，先使用df.columns.tolist()獲取所有列名，再用remove()移除目標(biāo)列，用insert()將目標(biāo)列插入指定位置，最后用df[columns]重新排列列順序。列移動(dòng)示例以將“扣款”列移動(dòng)到“補(bǔ)貼”列之前為例，先獲取列名列表，移除“扣款”列，在“補(bǔ)貼”列索引位置插入“扣款”列，重新排列后展示移動(dòng)后的數(shù)據(jù)。行移動(dòng)原理與方法通過(guò)篩選和重新排序?qū)崿F(xiàn)行移動(dòng)，使用布爾索引篩選目標(biāo)行，如篩選“銷(xiāo)售部”員工，再用pd.concat()將目標(biāo)行與其他行拼接，設(shè)置ignore_index=True重置索引。行移動(dòng)示例程序示例先篩選出“銷(xiāo)售部”員工和其他部門(mén)員工，然后將“銷(xiāo)售部”員工拼接至表格最前面，展示行移動(dòng)后的數(shù)據(jù)并保存修改后的數(shù)據(jù)到新文件。列間計(jì)算單擊此處添加正文列間計(jì)算概念列間計(jì)算指對(duì)表格列數(shù)據(jù)進(jìn)行數(shù)學(xué)運(yùn)算，確定列數(shù)據(jù)間關(guān)系，常用于會(huì)計(jì)財(cái)務(wù)確定關(guān)鍵指標(biāo)。連續(xù)列間計(jì)算-列計(jì)算后將結(jié)果增加到最后一列對(duì)連續(xù)列運(yùn)算，結(jié)果作新列添加到表格末尾。用Pandas讀取Excel文件，計(jì)算并處理空值。如工資表計(jì)算應(yīng)發(fā)、實(shí)發(fā)工資添加到末尾。連續(xù)列間計(jì)算-列間計(jì)算后將結(jié)果插入到指定位置計(jì)算后按條件將結(jié)果插入指定行后。先計(jì)算各列總和，創(chuàng)建新行數(shù)據(jù)并插入。如工資表在補(bǔ)貼、扣款列后插入應(yīng)發(fā)、實(shí)發(fā)工資列。不連續(xù)列間計(jì)算從不相鄰列提取數(shù)據(jù)計(jì)算，結(jié)果插入指定位置。用Pandas列選擇功能提取數(shù)據(jù)，計(jì)算后用pd.concat或loc插入新行。如工資表計(jì)算特殊項(xiàng)目并插入。數(shù)據(jù)計(jì)算03選擇特定行進(jìn)行匯總基本原理及方法依據(jù)特定條件篩選DataFrame中的行，計(jì)算篩選后的行，結(jié)果插入指定位置。用條件篩選行，計(jì)算并插入結(jié)果。程序示例-辦公室部門(mén)行匯總以工資表為例，先計(jì)算應(yīng)發(fā)、實(shí)發(fā)工資，篩選辦公室部門(mén)行匯總，設(shè)置匯總行名稱(chēng)，插入到第2行之后。列匯總列匯總概念對(duì)DataFrame列數(shù)據(jù)匯總計(jì)算，用于計(jì)算總和、平均值等統(tǒng)計(jì)量，分連續(xù)列匯總和選擇特定列匯總。連續(xù)列匯總對(duì)連續(xù)列執(zhí)行特定計(jì)算，結(jié)果作新行添加到DataFrame底部。用sum()、mean()等方法計(jì)算，結(jié)果作新行添加。如工資表選擇列求和并添加新行。選擇特定列進(jìn)行匯總依據(jù)條件篩選列，計(jì)算篩選后的列，結(jié)果以合適格式展示或存儲(chǔ)。選擇列名匯總，結(jié)果以Series或DataFrame存儲(chǔ)展示。如工資表匯總基礎(chǔ)工資和補(bǔ)貼列。行列的其他計(jì)算Pandas內(nèi)置計(jì)算方法Pandas為DataFrame行列提供豐富內(nèi)置計(jì)算方法，如求和、平均值、最大值、最小值、中位數(shù)等，助于洞察數(shù)據(jù)分布特征。程序示例-基本統(tǒng)計(jì)計(jì)算以工資表為例，計(jì)算應(yīng)發(fā)、實(shí)發(fā)工資后，對(duì)數(shù)值列進(jìn)行行內(nèi)求和、平均值、最大值、最小值、中位數(shù)計(jì)算并打印結(jié)果。數(shù)據(jù)分組統(tǒng)計(jì)(groupby()方法)groupby()方法基本原理groupby()方法是Pandas數(shù)據(jù)分組統(tǒng)計(jì)核心工具，按指定鍵分組，對(duì)每組應(yīng)用聚合函數(shù)計(jì)算，滿(mǎn)足分組統(tǒng)計(jì)需求。行分組統(tǒng)計(jì)-按照單一列分組統(tǒng)計(jì)按某列值分組數(shù)據(jù)，對(duì)每組應(yīng)用聚合函數(shù)。用df.groupby(by='列名')分組，sum()、mean()等計(jì)算。如工資表按部門(mén)分組統(tǒng)計(jì)基礎(chǔ)工資和補(bǔ)貼總和。行分組統(tǒng)計(jì)-按照多列分組統(tǒng)計(jì)根據(jù)多列值組合分組數(shù)據(jù)，應(yīng)用聚合函數(shù)。用df.groupby(by=['列名1','列名2'])分組，注意列組合唯一性，否則用agg()或drop_duplicates()處理。列分組統(tǒng)計(jì)對(duì)行（轉(zhuǎn)置后的列）分組統(tǒng)計(jì)，會(huì)計(jì)表格處理少見(jiàn)，可轉(zhuǎn)置DataFrame后用groupby()方法，此處不詳細(xì)探討。Excel數(shù)據(jù)處理常用方法分組計(jì)算按某一列分組并計(jì)算總和，函數(shù)格式df.groupby('分組列')['計(jì)算列'].sum()，如按產(chǎn)品名列分組計(jì)算銷(xiāo)售額總和

。按某列分組計(jì)算平均值，函數(shù)格式df.groupby('分組列')['計(jì)算列'].mean()，分組數(shù)據(jù)含缺失值會(huì)自動(dòng)忽略

。多列分組計(jì)算，函數(shù)格式df.groupby(['分組列1','分組列2'])['計(jì)算列'].sum()，返回多層索引Series對(duì)象，可用reset_index()轉(zhuǎn)換為普通列。條件篩選與計(jì)算篩選滿(mǎn)足條件的數(shù)據(jù)并計(jì)算，函數(shù)格式df[df['列名']>條件值]，如篩選銷(xiāo)售額大于1000的數(shù)據(jù)，并計(jì)算其總和

。使用query方法篩選數(shù)據(jù)，函數(shù)格式df.query('條件表達(dá)式')，適用于復(fù)雜條件，如篩選‘1月銷(xiāo)售額’大于1000且‘5月銷(xiāo)售額’大于500的數(shù)據(jù)。Excel數(shù)據(jù)處理常用方法Excel基本統(tǒng)計(jì)計(jì)算以“C:PYEXCEL銷(xiāo)售數(shù)據(jù)明細(xì)表.xlsx”為例，計(jì)算列總和函數(shù)格式為df['列名'].sum()，如計(jì)算1月銷(xiāo)售額總和，若列含非數(shù)值數(shù)據(jù)，需用df['列名'].apply(pd.to_numeric,errors='coerce')轉(zhuǎn)換

。計(jì)算平均值函數(shù)為df['列名'].mean()，會(huì)自動(dòng)忽略缺失值。計(jì)算最值函數(shù)分別是df['列名'].max()和df['列名'].min()，列含非數(shù)值數(shù)據(jù)會(huì)報(bào)錯(cuò)。計(jì)算標(biāo)準(zhǔn)差函數(shù)為df['列名'].std(ddof=1)，ddof=1計(jì)算樣本標(biāo)準(zhǔn)差，ddof=0計(jì)算總體標(biāo)準(zhǔn)差。Python處理Excel的相關(guān)技術(shù)04Excel數(shù)據(jù)處理常用方法排序按某一列排序，函數(shù)格式df.sort_values(by='列名',ascending=False)，ascending為T(mén)rue時(shí)升序，F(xiàn)alse時(shí)降序，如按1月銷(xiāo)售額列降序排列

。按多列排序，函數(shù)格式df.sort_values(by=['列1','列2'],ascending=[True,False])，需確保排序列數(shù)據(jù)類(lèi)型一致，如按產(chǎn)品名升序、銷(xiāo)售額降序排列。綜合計(jì)算示例對(duì)“C:PYEXCEL銷(xiāo)售數(shù)據(jù)明細(xì)表.xlsx”進(jìn)行綜合計(jì)算，先讀取文件，進(jìn)行基本統(tǒng)計(jì)計(jì)算，如每月銷(xiāo)售額總和、平均銷(xiāo)售額等

。再進(jìn)行分組計(jì)算，按產(chǎn)品名、類(lèi)別、地區(qū)分組計(jì)算每月銷(xiāo)售額總和

。接著添加新列計(jì)算每個(gè)產(chǎn)品總銷(xiāo)售額，篩選總銷(xiāo)售額大于10000的數(shù)據(jù)，最后按總銷(xiāo)售額降序排列并保存結(jié)果到新Excel文件。Excel表數(shù)據(jù)處理應(yīng)用pandas與PyQt結(jié)合展示數(shù)據(jù)利用pandas讀取“C:PYEXCEL銷(xiāo)售數(shù)據(jù)表.xlsx”，借助PyQt5庫(kù)美化顯示

。通過(guò)Sa