讀Python數據分析基礎之Excel讀寫與處理
- 2019 年 10 月 8 日
- 筆記
對於業務型數據分析來說，Excel可以說是打交道最多的軟件了，可以說沒有之一。之前有比較系統地讀過《Python數據分析基礎》（Foundations for Analysis with Python），寫了一些筆記，這裡只選取關於Excel的部分。
這篇筆記不是講各類Excel函數和快捷鍵，而是講Python對Excel的 .xls 和 .xlsx 格式數據的讀寫和處理。《Python數據分析基礎》第82頁說：
Excel 是商業活動中不可或缺的工具，所以知道如何使用 Python 處理 Excel 數據可以使 你將 Python 加入到數據處理工作流中，進而從其他人那裡接收數據，並以他們習慣接受的 方式分享數據處理結果。
目錄
- Excel文件簡介
- Excel文件的讀取
- Excel文件的寫入
- pandas庫讀寫Excel
- 篩選與統計量計算
Excel文件簡介
Excel其實相信大家都不陌生，一個 .xls（Excel 2013後默認格式為 .xlsx ）文件是一個工作簿（workbook），包含多個表（worksheet），每個表內數據按照行列進行組織，書中第三章的用詞中，「文件」和「工作簿」表示同一個對象。拿本篇筆記用到的示例文件 sales_2015.xlsx 為例：
workbookAndWorkSheets
本篇筆記需要用到兩個庫： xlrd 和 xlwt，這兩個庫不是內置模塊，但安裝了Anaconda集成環境的話，這兩個庫被包含了，可以通過 import xlrd 和 import xlwt 進行測試，如果提示未安裝，可以通過命令行下用pip安裝，本處不展開。xlrd用來讀取Excel文件，xlwt用於構建Workbook對象進行Excel文件的創建和寫入數據。這兩個庫不能對Excel文件進行直接更改，因此思路是複製一份數據到內存進行分析計算，再寫入新Excel文件中。需要直接性地修改可以考慮VBA吧，VBA（Visual Basic for Applications）是目前 Office 套件支持的基於 Visual Basic 的宏語言，目前一般在Excel或PPT內進行編程實現高級效果（如Excel數據的批量修改）一般都用VBA，當然以後Office要內置Python了，現在學好Python以後就能很容易理解和定製化Excel的宏了。
Excel文件的讀取
通過xlrd庫的open_workbook()對Excel數據進行讀入。
#lrd讀取並輸出基本信息# -*- coding: utf-8 -*-def readExcel(fname): from xlrd import open_workbook workbook = open_workbook(fname) print('工作表數量:', workbook.nsheets) for worksheet in workbook.sheets(): #循環輸出表名 print("Worksheet name:", worksheet.name, "tRows:",worksheet.nrows, "tColumns:", worksheet.ncols) readExcel('sales_2015.xlsx')
輸出：
工作表數量: 3 Worksheet name: january_2015 Rows: 7 Columns: 5 Worksheet name: february_2015 Rows: 7 Columns: 5 Worksheet name: march_2015 Rows: 7 Columns: 5
Excel文件的寫入
寫入要用到 xlwt 庫，如下面的代碼，創建一個Workbook對象後，通過worksheet.write()寫入數據。因為使用with進行處理，所以不需要寫close()顯式關閉打開的文件。
#讀取一些Excel並寫入新表def parsingExcToWrite(in_file,out_file): from xlrd import open_workbook from xlwt import Workbook #導入一個 Workbook對象 output_workbook = Workbook() output_worksheet = output_workbook.add_sheet('jan_2015_output') #加一個新工作表到工作簿對象里 with open_workbook(in_file) as workbook: worksheet = workbook.sheet_by_name('january_2015') #引用上面創建的工作表 print('type(worksheet):',type(worksheet)) print('write()傳入參數類型:',type(worksheet.cell_value)) for row_index in range(worksheet.nrows): for column_index in range(worksheet.ncols): #枚舉寫入數據 output_worksheet.write(row_index, column_index, worksheet.cell_value(row_index, column_index)) output_workbook.save(out_file) in_f='sales_2015.xlsx'out_f='save_sales_2015.xlsx'parsingExcToWrite(in_f,out_f) #調用
輸出：
type(worksheet): <class 'xlrd.sheet.Sheet'>write()傳入參數類型: <class 'method'>
生成的文件效果如下：
save_sales_by_xlwt
上面寫成的函數parsingExcToWrite()就是是一個可以使用在讀取、處理、寫入的框架，如前所說，這兩個庫不能直接對Excel進行修改，那麼讀取相應的數據處理後再寫入新表就是很好的解決方案。 下面的處理代碼如果需要保存處理後的數據就可以基於這段代碼進行擴展。
pandas庫讀寫Excel
下面看看用pandas進行Excel讀取的操作， 讀只需要一句話（引入庫的不算在內），pd.read_excel(in_fname)，和前一篇筆記讀取csv的格式一樣，都是生成dataframe數據格式。寫入Excel通過pd.ExcelWriter()構建一個Excel寫入對象，再對這個對象操作，最後調用 .save()進行寫入到硬盤。
import pandas as pd in_f='sales_2015.xlsx'out_f='save_sales_2015_1.xlsx'data_frame = pd.read_excel(in_f, 'january_2015', index_col=None) #對 data_frame進行一些處理dframe_condition = data_frame[:] writer = pd.ExcelWriter(out_f) dframe_condition.to_excel(writer, sheet_name='sheet_name',index=False) writer.save() print('done')#輸出： done
篩選與統計量計算
因為pandas可以簡化一些操作，並且多練pandas是很有意義很重要的，所以下面篩選和統計量的計算都是基於pandas的處理。
有些時候，我們並不需要 Excel 文件中的所有行，特別是數據量很大但是我們只關心滿足一定條件的數據。例如，可能只需要包含一個特定的詞數值的那些行，或者只需要那些與一個具體日期相關聯的行數據。這時候我們就需要進行篩選，去掉不需要的行，只保留需要的行。
下面的代碼演示了篩選 Sale Amount 大於 $567.00 的行。我們可以通過改變代碼data_frame_value_meets_condition = data_frame[data_frame['Sale Amount'].astype(float) > 567.0] 來篩選行中的值滿足某個條件的數據。
#import pandas as pd #上面引入了這裡就不需要重複引入，如果是獨立的文件需要寫上這句def valConditionExc(in_file,out_file): data_frame = pd.read_excel(in_file, 'january_2015', index_col=None) data_frame_value_meets_condition = data_frame[data_frame['Sale Amount'].astype(float) > 567.0] writer = pd.ExcelWriter(out_file) data_frame_value_meets_condition.to_excel(writer, sheet_name='jan_15_output',index=False) writer.save()#行中的值匹配於特定模式def valMatchPattern(in_file,out_file): data_frame = pd.read_excel(in_file, 'january_2015', index_col=None) df_value_matp = data_frame[data_frame['Customer Name'].str.startswith("J")] writer = pd.ExcelWriter(out_file) df_value_matp.to_excel(writer, sheet_name='jan_15_output',index=False) writer.save() print(df_value_matp)#選擇滿足一定條件的特定列數據def selectColByIndex(in_file,out_file): data_frame = pd.read_excel(input_file, 'january_2015', index_col=None) df_col_by_index = data_frame.iloc[:, [1, 4]] writer = pd.ExcelWriter(output_file) df_col_by_index.to_excel(writer, sheet_name='jan_15_output',index=False) writer.save()in_f='sales_2015.xlsx'valConditionExc(in_f,'save_sales_2015_2.xlsx') valMatchPattern(in_f,'save_sales_2015_3.xlsx')
輸出：
Customer ID Customer Name Invoice Number Sale Amount Purchase Date0 1234 John Smith 100-0002 123 2015-01-014 5678 Jenny Walters 100-0006 345 2015-01-24
使用 pandas 基於列標題選取特定列，一種方式是在數據框名稱後面的方括號中將列名以字符串方式列出。另外一種方式是使用 loc 函數。如果使用 loc 函數，那麼需要在列標題列表前面加上一個冒號和一個逗號，表示你想為這些特定的列保留所有行。例如下面的代碼：
#import pandas as pd def selectAllColByName(in_file,out_file): data_frame = pd.read_excel(in_file, 'january_2015', index_col=None) selected_columns = data_frame.loc[:, ['Customer ID', 'Purchase Date']] #根據列標題選取特定列 writer = pd.ExcelWriter(out_file) selected_columns.to_excel(writer, sheet_name='jan_13_output',index=False) print(selected_columns) writer.save() in_f='sales_2015.xlsx'selectAllColByName(in_f,'save_sales_2015_4.xlsx')
輸出：
Customer ID Purchase Date0 1234 2015-01-011 2345 2015-01-062 3456 2015-01-113 4567 2015-01-184 5678 2015-01-245 6789 2015-01-31
大家可以根據代碼的效果區分上上部分代碼的data_frame.iloc[:, [1, 4]] 和上面的data_frame.loc[:, ['Customer ID', 'Purchase Date']]
.loc for label based indexing .iloc for positional indexing
數據裝入pandas的dataframe之後，除了進行篩選，計算一些統計量也是數據分析很重要的工作，描述性統計給我們提供了很多描述數據的指標，下面的代碼為工作表的銷售數據計算總數和均值。
#import pandas as pd def getSumAndAverage(in_f): all_worksheets = pd.read_excel(in_f,sheetname=None, index_col=None) workbook_total_sales = [] workbook_number_of_sales = [] workbook_mean_sales = [] for worksheet_name, w_data in all_worksheets.items(): total_sales = pd.DataFrame([float(str(value).strip('$').replace(',','')) for value in w_data.loc[:, 'Sale Amount']]).sum() #算一個表的總體銷售額 number_of_sales = len(w_data.loc[:, 'Sale Amount']) workbook_total_sales.append(total_sales) #裝入一個列表 workbook_number_of_sales.append(number_of_sales) mean_sales=total_sales/number_of_sales #均值 workbook_mean_sales.append(mean_sales) print(worksheet_name,'t total:',total_sales[0],'t num:',number_of_sales,'t mean:',mean_sales[0]) in_f='sales_2015.xlsx'getSumAndAverage(in_f)
輸出：
january_2015 total: 3201.0 num: 6 mean: 533.5february_2015 total: 55007.0 num: 6 mean: 9167.83333333march_2015 total: 246045.0 num: 6 mean: 41007.5
《Python數據分析基礎》第三章講了Excel文件的讀寫和處理，裏面提供了xlrd、xlwt進行讀寫處理以及對應的pandas庫進行讀寫和處理，本篇筆記基本覆蓋了第三章的重點內容，縮減了一些例子，在處理的部分都是用pandas庫進行，書中還提供了xlrd、xlwt進行處理的代碼，代碼比較長，需要理解這部分內容請閱讀原書。下一章進入數據庫的內容，數據庫也是數據分析師經常要操作的工具。之後在可視化部分中還會經常用到pandas，讀取數據後進行可視化是很美好的事。
