pandas 取excel 中的某一列_幹貨Python Pandas 做資料分析之玩轉 Excel 報表分析

本篇文章選自作者在 GitChat 的分享，若有什麼問題，可在公衆号回複「小助手」添加小助手微信，邀請你進入技術交流群。

各位朋友大家好，非常榮幸和大家聊一聊用 Python Pandas 處理 Excel 資料的話題。因為工作中一直在用 Pandas，是以積累了一些小技巧，在此借 GitChat 平台和大家分享一下心得。在開始之前我推薦大家下載下傳使用 Anaconda，裡面包含了 Spyder 和 Jupyter Notebook 等內建工具。到百度搜尋一下就可以找到官方下載下傳連結，下載下傳個人版就可以(本文使用的 Python 版本為 3.6.0 ，隻要大家用的是 Python 3，那麼文法就和文中幾乎沒有差異)。

一、資料的讀取

在工作中，實驗資料和工作表格通常存儲在 Excel 的檔案中。也有人使用資料庫，資料庫本身自帶簡單的求和、計數等功能。如果做深入的資料分析，就離不開像 Python Pandas、TensorFlow 等專業工具了。資料庫導出的資料檔案通常為 CSV、UNL 格式。CSV 和 UNL 格式資料也可以用 Excel 打開并正常顯示為表格，它們是使用特殊分隔符(比如 ,、| 或 ;)的文本型資料檔案。用 UltraEdit 之類的純文字編輯器打開的樣子是這樣的：

1.1 讀取 CSV 檔案

read_csv

是 Pandas 讀取 CSV 檔案時使用的方法。

import pandas as pd     #首先引入pandas包，并稱它為pd

fpath=r'e:\tj\zt1802\car.csv'      #定義檔案所在的位置

    df=pd.read_csv(fpath,header=0,index_col=None,encoding='gbk')     #read_csv讀取資料

• encoding='gbk'

   在這裡指定了檔案的編碼格式，不設定此選項時 Pandas 預設使用 UTF-8 來解碼。

• header=0

   是指将檔案中第 0 行(正常人了解應該是第一行)作為“列名”。如果沒有設定則預設取第一行，設定為 None 的時侯 Pandas 會用自然數 0、1、2……來辨別列名。DataFrame 中的列名叫 columns，行名叫 index，因為是用來索引資料。是以 columns 和 index 在 Pandas 中被定成了 “index 類型”。

• index_col=None

   的意思是，檔案中沒有資料作為“行名”index，這時 Pandas 會從 0、1、2、3 做自然排序配置設定給各條記錄。當然也可以取資料中的第一列為行索引 index_col=0，比如學号、股票代碼、資料庫導出資料的主鍵。
• 讀出的資料存在名為 df 的 DataFrame 中，可以将 DataFrame 簡單了解為一個二維資料表或矩陣。一維向量(或一個序列)在 Pandas 中被稱為 Series，DataFrame 的一行或一列就是一個 Series。
• 這裡還有一點需要注意，就是沒有使用 sep 選項，sep 用來指定資料分割符。如 

  df=pd.read_csv("e:/tj/zt1802/ins.unl",sep="|")

  ，這裡就是明确指定文本資料使用“|”做資料的分割符。我有時侯會用 DbVisualizer 讀取資料庫資料，然後導出 CSV 檔案，因為預設使用 Tab 而不是逗号做了分割符。這時侯 sep 參數就是“/t”。

1.2 讀取 Excel 檔案

Pandas 讀取 Excel 檔案的文法格式和讀取 CSV 檔案是相似的，但使用的 Pandas 方法略有不同，需要使用單獨的支援子產品 xlrd。如果出現以下類似報錯：

ImportError: No module named 'xlrd'

用 pip 安裝一下就不會再報錯了。

pip install xlrd

Pandas 讀取 Excel 的文法如下：

pandas.read_excel(io，sheet_name = 0，header = 0，index_col = None，usecols = None，dtype = None, ...)

   #本人常用操作方法

   fpath='E:/TJ/xtxy/

   vfile='市場表.xlsx'

   vdate='2018年9月份'

   vdate1='2017年9月份'

   sheet1='表1-各主體累計營業收入'

   xy18=pd.read_excel(fpath+vdate+vfile, sheetname=sheet1,index_col=[0,1],header=[1,2],skiprows=0,skipfooter=3)

• 這裡的 io 就是之前的 fpath，即檔案位置。如果檔案命名很有規律并且經常使用，可以用字元串拼接方式構造檔案位置路徑，這樣友善換檔案和下次再用。

• sheet_name=0

   是指讀取 Excel 檔案中的第一個工作表，這裡也可以直接指定名字 sheet_name = '工作表名稱'，如果不指定就預設讀取第一個。

• skiprows=0

   就是讀取資料時跳過第一行。這是因為 Excel 第一行為文本标題，如果 

  skipfooter=3

  ，就是跳過資料尾部的 3 行。
• 如果資料分析隻使用檔案中的若幹列，那麼 

  usecols=[A:E]

  很有用了，意思是隻讀取 A 到 E 列到 Pandas。

• dtype

   參數用來指定特定列的資料類型，參數傳遞為字典，如 

  {‘a’:np.float64,‘b’:np.int32}

  。

• parse_dates:

   可以傳遞一個清單比如 [1,3,5]，這樣就會把 1、3、5 列作為日期格式傳遞給 DataFrame，當然也可以後期再改。
• 在讀檔案時你可能發現了， 

  index_col

   和 

  header

   傳遞了清單 

  index_col=[0,1]

  , 

  header=[1,2]

  。這表示用 2 行、2 列分别做列索引 column 和行索引 index。這時侯的 column 和 index 被稱為 MultiIndex。為了保證源資料的規範整潔，通常是需要避免使用 MultiIndex 的。但在對 Excel 做處理時就無可避免，我們後面很快會說到關于 MultiIndex 的資料篩選。

1.3 批量讀取資料檔案

在實驗室或工作場景中經常會遇到處理大量資料結構相同的源資料。怎麼批量将這些資料導入同一個 DataFrame 呢？看下面的代碼：

 #讀取資料

   import pandas as pd

   import numpy as np

   import glob,os

   path=r'e:\tj\month\fx1809'

   file=glob.glob(os.path.join(path, "zk*.xls"))

   print(file)

   dl= []

   for f in file:

       dl.append(pd.read_excel(f,header=[0,1],index_col=None))

   df=pd.concat(dl)

首先确定自己資料檔案的存放位置，然後利用 Python 的 glob 子產品，模糊比對路徑下以“zk”開頭，且字尾為“.xls”的檔案。之後把完整的路徑名存儲到 file 的清單中。

print(file)

可以顯示比對到的檔案。

生成一個空清單 dl，利用 append 方法将各個檔案分别讀入并存儲到 dl。dl 清單中的每個元素就是一個 DataFrame。然後利用 concat 方法把這些結構相同的 DataFrame 合并到一起。預覽 DataFrame 内容可以使用 df.head(5) 或者 df.tail(1)。

二、資料的處理

了解完基礎的資料讀取，我們再來試一試利用 NumPy 生成一組實驗資料來繼續學習。

   import numpy as np

   import pandas as pd

   MyCol=list('ABCDE')

   MyIndex=pd.DatetimeIndex(start='2018-01-01',periods=3,freq='D')

   df=pd.DataFrame(np.random.rand(15).reshape(3,5),index=MyIndex,columns=MyCol)

生成資料的效果如下：

這一段代碼中：

• 這裡預先定義了 DataFrame 的 columns 和 index。DatetimeIndex 用來生成日期格式的 index，periods 是生成的步長，freq 是時間頻率(可以選擇月 M、日 D、小時 H。同時可以在 M 、D、 H 加上數字，比如 12H，就是生成以 12 小時為間隔的序列)。然後利用 pd.DataFrame 建立了一個 DataFrame。

• random.rand(15)

   是 NumPy 庫用來建立随機數的， 

  reshape(3,5)

   将矩陣轉化成 3 行 x 5 列。 

  pd.DataFrame

   如果不設定 columns 和 index 時，columns 和 index 将使用 0、1、2、3 自然數做名字。 目前實驗的資料有了，就開始跟着練練一代碼吧！

2.1 資料選擇之 index 和 column

2.1.1 列印 columns 和 index 的名字

DataFrame 的預覽可以使用 head 和 tail 兩個方法，當查詢特殊行和列時就要用到 index 和 columns 了。首先需要确定的是，這些 index 和 column 的名字是什麼呢？

df.columns #顯示有哪些列名

df.index #顯示index的名字

2.1.2 利用 columns 和 index 檢索資料

在知道了 columns 和 index 的名字之後，就可以查詢特殊一列或一行了。

查詢行時需要用到 loc 方法，比如查詢日期為 2018-01-01 時，使用以下代碼：

df.loc['2018-01-01']

2.1.3 檢索多行或多列資料

當需要選擇多列資料時，需要在清單中傳遞一個清單，例如：

當查詢多行時就得使用清單中切片的方法：

df.loc['2018-01-01':'2018-01-03']

2.1.4 iloc 方法選擇資料

iloc 不使用 index 和 columns 的名字來選擇資料，而是用自然數。比如第一行資料用：

df.iloc[0]

選擇 0~2 行時用：

df.iloc[0:3]

選擇 0 行 0 列對應元素就是：

df.iloc[0,0]

2.1.5 index 和 columns 的重命名

當 index 和 columns 需要重命名時也很簡單，直接傳遞清單就可以

df.columns=['cat','dog','pig','tiger','monkey']

df.index=['A','B','C']

2.2 MultiIndex 的操作

為了實作在 MultiIndex 的學習，我們利用以下代碼生成一些實驗資料：

import numpy as np

   import pandas as pd

   itersA=[['jack','leo','tim'],['A','B','C']]

   itersB=[['CA','CB'],['key1','key2']]

   idxma=pd.MultiIndex.from_product(itersA,names=['one','two'])

   idxmb=pd.MultiIndex.from_product(itersB,names=['first','second'])    df=pd.DataFrame(np.random.rand(36).reshape(9,4),index=idxma,columns=idxmb)

資料：

運作

df.columns

和

df.index

可以分别顯示列和行的 MultiIndex 的結構：

   #df.columns的結構

   MultiIndex(levels=[['CA', 'CB'], ['key1', 'key2']],

          labels=[[0, 0, 1, 1], [0, 1, 0, 1]],

          names=['first', 'second'])

   #df.index的結構

   MultiIndex(levels=[['jack', 'leo', 'tim'], ['A', 'B', 'C']],

          labels=[[0, 0, 0, 1, 1, 1, 2, 2, 2], [0, 1, 2, 0, 1, 2, 0, 1, 2]],

          names=['one', 'two'])

以 df 的 index 為例：這裡面的 levels 是指 index 中各層所包含的元素，index 的

level=0

的這一層名字為one，包含元素 ['jack', 'leo', 'tim']。其中

level=1

這一層名字就是 two，元素就是 ['A', 'B', 'C']。labels 顯示了各層元素在 index 中的分布情況。

2.2.1 MultiIndex 的列選擇操作

在列的

level=0

上選擇其中一項：

df['CA']

在列的

level=0

上選擇其中多項：

df[['CA','CB']]

同時在列的

level=0

和

level=1

上選擇：

但是

df[['CA','CB']][['key1','key2']]

是不可以用的。

2.2.2 MultiIndex 的行選擇操作

使用 loc 選擇更加靈活一些，下面的代碼選擇了 index 中

level=0

的’leo’和 columns 中

level=0

的‘CB’。

df.loc['leo','CB']

組合使用 loc 和 slice 來選擇資料：

df.loc[(slice('leo','tim'),slice('B','C')),slice(None),slice('key1'))]

2.2.3 使用 xs 進行資料選擇

xs 是多重索引取值的另一個工具，需要分别指定索引的标簽名，同時說明 level。

axis=1

軸為 1 時是在列中操作，

axis=0

則是指行。

df.xs('key2',level=1,axis=1)

MultiIndex 操作相對複雜，使用 MulitIndex 一般出現在從 Excel 讀取的資料中。對于實驗室資料來說，隻要保證源資料規範整潔就可不用 MultiIndex。一般來說遵循以下原則對後期資料處理會非常友善。

• 同一個資料表隻存儲同一實驗目的所産生的實驗資料。
• 每一個被觀測對象隻産生一條資料記錄。
• 每一個列必須為被觀測對像的一個觀測屬性。

2.3 資料内容相關操作

2.3.1 處理空值和重複記錄

了解完基礎的資料篩選，那麼我們進入資料處理的階段。在讀取 Excel 資料時，可能會遇到空值。空值并不是 0，而是沒有資料，在 Pandas 中被表示為 Nan。為了進行統計的友善我們會把空值填充為 0，其它内容，也可能删除掉。這裡我們先做一個帶有空值的 DataFrame。

• 查找空值： 

  df.isnull()

   會在有空值的位置顯示為 True。 

  df.notnull()

   會把非空值的位置顯示為 True，空值的位置顯示為 False。 

  df.D.isnull()

   中的 D 是列标簽的名字，這樣可以檢視某列中的空值。
• 删除空值： 

  df.dropna(how='any')

   可以删除空值，how 的參數還可以是 all。any 指記錄中隻要有一個位置出現空值即删除該記錄。all 表示當這一條記錄中所有地方都是空值時，才删除記錄。這裡引申一下去除重複值。自己用代碼試一試吧！
• 填充空值： 

  df.fillna(0)

   這裡是指把空值位置變成 0，也可以是其它數。 

  df.fillna(method='ffill')

   這裡是複空值上面的數值，參數為 bfill 是按它後面的值來填充。
• 重複記錄： 

  df.drop_duplicates(keep='first')

   用來删除重複記錄，并且保留重複記錄中的第一條記錄。當然參數也可以是 last。

2.3.2 利用資料内容進行資料篩選

通過構造一個和資料内容相關的表達式也可以實作資料篩選。

df[df['A']>0.5]

會傳回df中A列數值大于 0.5 的記錄。其中

df['A']>0.5

會傳回一個包含 True 和 False 的序列，然後

df[df['A']>0.5]

會将表達式傳回為 True 的記錄篩選出來。當然表達式中的運算符也可以是其它形式，這裡簡單列幾個。

• 等于：==
• 小于于等于：<=
• 不等于：!=
• 為空：isnull()
• 包含在：isin([list])

2.3.3 更改資料

• 類型更改：讀 Excel 之後有些資料類型不對，更改方法是使用 astype。 

  df['A']=df['A'].astype(int)

  這裡會把 A 列資料轉換為整型數。
• 數值更改：當需要對 DataFrame 中的每一個元素進行修改時，使用 

  applymap(f)

  。僅對一個 Series 更改時用 

  apply(f)

  。這裡的 f 是自定義 Python 函數。例如： 

  df.applymap(lambdax:np.round(x*100,2))

   将每個元素乘 100 後，取到 2 位小數。 

  

2.3.4 簡單統計和分組統計

Pandas 本身提供了一個非常簡單的統計方法，describe() 可以傳回統計的樣本數、平均值、标準差、最大值、最小值。

df.describe()

除此之後，還可以自定義統計方法。

• 查行方向中的最大值： 

  df.max(axis=1)

• 求列方向的彙總資料： 

  df.sum(axis=0)

• 求列方向的平均值： 

  df.mean()

   不使用 axis 時預設為列方向統計。

分組統計：為了進行分組統計，這裡在 DataFrame 中虛拟一個可以分組的列叫 kind。

df.groupby('kind')['A','C'].sum()

df.groupby('kind').agg({'A':np.mean,'B':['min','max']})

代碼中的 groupby 提供了分組依據，當配合使用 agg 時，可以對不同的列應用不同的統計方法。下例中以 kind 進行分組統計，A 列計算平均值，B 列計算最小、最大值。Pandas 的靈活強大感受到了嗎？

2.3.5 DataFrame 的合并

在資料分析中，經常需要合并兩個 DataFrame。通常有 2 種方法實作，就是 concat 和 merge。為了能夠明顯地看出效果，我們做一個 df1，使其和 df 的 column、index 部分一緻。

concat 方法： 在不定義條件下進行，但會按索引進行自動比對。遇到不比對的索引則會增加行或列。效果如下：

merge 方法： 在自定義條件下進行，效果如下：

當 how 的參數為 right 時，出來的效果是按 df1 的索引 index 進行比對；

how='full'

時會出現從

2018-01-01

到

2018-01-04

共 4 條記錄；當

how='inner'

時出現的僅為 df 和 df1 的 index 的交集。merge 也可以用

left_on='左側某列'

和

right_on='右側某列'

來進行比對。

2.3.6 melt 和 pivot_table

melt： 可以用來進行列轉換。

id_vars

表示用來仍保留為 columns 的列，

value_vars

參數中對應列的 columns 名稱變成一列參數，其各列對應的數值則變成新的一列，就像做了一鍋東北亂炖。簡單地說：列變少了，行增加了。

pd.melt(df,id_vars=['kind'],value_vars=['A','B','C','D','E'],var_name='myvar',value_name='myval')

pivot_table： 和 Excel 中的資料透視表是一樣的，将之前被 melt 的 DataFrame，即現在的 dfm 的中的 kind 列元素變成了 columns，dfm 的 myvar 列變成了 index，資料區域是 myval，在透視過程中進行求和操作(即 np.sum)。

dfm.pivot_table(values='myval',index=['myvar'],columns='kind',aggfunc=np.sum)

2.3.7 導出 Excel 檔案

學會上面提到過的 Pandas 用法，就可以做很多工作了。對于處理好的資料如何儲存到 Excel 表格中呢？當生成 CSV 檔案時用 to_csv 方法，下例中會在

e:/tj/zt1802/

檔案夾下生成一個叫

newdata.csv

的檔案。

df.to_csv('e:/tj/zt1802/newdata.csv')

導出 Excel 檔案時略複雜：

   writer = pd.ExcelWriter("e:/new.xlsx", engine='xlsxwriter')

   df.to_excel(writer,sheet_name='df',merge_cells=True)

   ......    

   writer.save()

首先建立一個 ExcelWriter 的對象，這個 Excel 檔案如果不存在則會建立一個檔案。對于存在的檔案，

to_excel

會把 DataFrame 以追加的形式寫進這個檔案，在工作簿最後面追加為一個新的工作表。工作表的名稱為由

sheet_name

參數來進行自定義。如果 DataFrame 中有 MultiIndex，參數

merge_cells

設定為 True，就可以保證新工作表中寫入的資料自動生成帶合并單元格式。寫完成操作之後，不要忘記用

writer.save()

進行儲存關閉。

三、Matplotlib 基礎作圖

Excel 還有一個強大的功能就是作圖！Excel 能做到的，萬能的 Python 也可以。但是需要用到 Matplotlib 庫。這裡我們拿個例子把 Matplotlib 基礎作圖說一下。

3.1 引入畫圖包并做全局設定

import seaborn as sns

   #seaborn是在Matplotlib上封裝的，為了使用其樣式我們引入這個包。

   import matplotlib

   #引入matplotlib

   import matplotlib.pyplot as plt

   #我們需要用pylplot來畫圖

   sns.set_style("whitegrid")

   #我們選whitegrid主題樣式

   matplotlib.rcParams['font.sans-serif'] = ['SimHei']

   matplotlib.rcParams['font.family']='sans-serif'

   matplotlib.rcParams['axes.unicode_minus'] = False

   matplotlib.fontsize='20'

   #這一段設定了中文支援的字型，字型大小

3.2 建立畫布

首先建立 figure 和 axes 執行個體。figure 是畫布，axes 就是畫布裡的子分區。

fig, axs = plt.subplots(1, 2, figsize=(15,6), sharey=True)

• subplots(1, 2) 建立了包含一行兩列的畫布，即兩個 axes 執行個體。
• figsize 确定了畫布大小，sharey 控制共享坐标軸。如果沒有子圖，那麼 

  fig=plt.figure()

   就可以。

3.3 開始畫餅圖

labels = list(tpie.index) #label是一個大蛋糕切開後，每一塊的名字。

   sizes = list(tpie['18年市場占比']) #這裡确定了每塊重多少。

   explode = (0.0,0.0,0.0, 0.0,0.0,0.0) #确定每塊離中心位置多遠

   axs[0].pie(sizes, explode=explode, labels=labels, autopct='%1.2f%%',shadow=False, startangle=45,textprops={'fontsize': 18})

   axs[0].set_title('18年1-3季度',fontsize='20')

   axs[0].axis('equal')

• axs[0]：表示畫布上第一塊要畫圖了，pie 表示餅，plot 是線，bar 是柱。
• autopct：會把 sizes 換算成百分數。
• shadow：确定是否畫陰影。
• textprops：配置資料标簽字型大小。
• set_title：給第一個子圖來個标題。
• axis('equal')：保證畫出來的圓不會變扁。

3.4 儲存圖像

 sizes = list(tpie['17年市場占比'])

       explode = (0.0,0.0,0.0, 0.0,0.0,0.0)

       axs[1].pie(sizes, explode=explode, labels=labels,autopct='%1.2f%%',shadow=False, startangle=45,textprops={'fontsize': 18})

       axs[1].set_title('17年1-3季度',fontsize='20')

       axs[1].axis('equal')  

       plt.savefig('e:/tj/month/fx1809/份額.png',dpi=600,bbox_inches = 'tight')  

       plt.show()

• axs[1]：開始了畫第二個子圖。
• plt.savefig：用來儲存圖像，第一個參數是存儲檔案位置及檔案名，dpi 用來确定輸出圖像分辯率。
• plt.show()：在 Jupyter Notebook 中顯示圖像。 上面這些代碼的出圖效果如下：

3.5 坐标軸及其它

下面的代碼畫了一個折線圖，我們利用它說一說坐标軸的設定。

x=range(len(t6['統計日期']))

   y=t6['承保數量(輛)']

   plt.rcParams['figure.figsize'] = (8.0, 4.0)

   plt.plot(x,y,marker="*",ms=15)

   plt.xticks(x, t6['統計日期'])

   #這一段用來添加資料标簽。

   for x, y in zip(range(len(t6['統計日期'])),t6['承保數量(輛)']):

       plt.text(x, y+0.3, str(y), ha='center', va='bottom', ontsize=15.5)

   plt.title('近年同期車險市場承保數量',fontsize='20')

   plt.margins(0,0)

   plt.ylabel('承保數量(輛)',fontsize='15')

   plt.xlabel('統計時間',fontsize='15')

   plt.ylim((500000,1000000))

   plt.xticks(fontsize=15)

   plt.yticks(fontsize=15)

• xticks 和 yticks 分别為橫、縱坐标的刻度設定。
• xlabel 和 ylabel 分别是橫、縱坐标軸的名稱。
• xlim 和 ylim 分别是手動調整橫、縱坐标軸顯示刻度的長度範圍。
• margins(0,0) 表示圖像不留白邊。

3.6 組合圖作圖方法

如果要作一個雙坐标軸的組合圖，需要先在 figure 畫布上生成一個 Axes 執行個體為 ax1、ax1 上畫了柱形圖。然後再用 twinx 添加一個次坐标軸 ax2。

 fig = plt.figure()

   ax1 = fig.add_subplot(111)

   ax1.bar(x, y1,alpha=.3,color='b')

   ax1.set_ylabel('累計保費(萬元)',fontsize='15')

   ax1.set_title("保費規模及同比增速對比圖",fontsize='20')

   plt.yticks(fontsize=15)

   plt.xticks(x,t1.index)

   plt.xticks(fontsize=15)

   ax2 = ax1.twinx()  # 添加次坐标軸

   ax2.plot(x, y2, 'r',marker='*',ms=10)

   ax2.plot(x, y3, 'g')

   ax2.set_xlim([-0.5,9.5])

   ax2.set_ylim([0,30])

   ax2.set_ylabel('同比增速(%)',fontsize='15')

   ax2.set_xlabel('同比增速(%)')

到這裡，全部内容已結束。希您能為您的工作帶來便利和效率。也歡迎大家和我交朋友。