Python 操作Excel操作總結,包括Series和Data Frame的互轉、使用pandas讀取Excel表格、python讀取多個資料表、python合并多個作業表以及寫入Excel檔案

pandas是一款基于NumPy的資料分析工具，它提供了大量的能使我們快捷處理資料的方法，

隨筆記錄所的所學，此博客為我記錄文章所用，發布到此，僅供網友閱讀參考，作者:北山啦

# -*- coding:utf-8 -*-
# @Address:https://beishan.blog.csdn.net/
# @Author:北山啦

• Series：一維陣列，與NumPy中的一維陣列相似，和Python自身的list也相似，區別自語Series中的資料只能是一種資料，而list中的資料可以不一樣
• Time-Series：以時間為索引的Series
• DataFrame：二維的表格型資料結構，經常用于處理Excel表格資料等，這也是我們本節課會重點講的內容
• Panel：三維陣列（0.25版本后，統一使用xarray，不再支持Panel）

Series和Data Frame的互轉

• 利用to_frame()實作Series轉DataFrame
• 利用squeeze()實作單列資料DataFrame轉Series

import pandas as pd
s = pd.Series(["北山啦","關注","點贊"])
s

0    北山啦
1     關注
2     點贊
dtype: object

s = s.to_frame(name="列名")
s

s.squeeze()

0    北山啦
1     關注
2     點贊
Name: 列名, dtype: object

使用pandas讀取Excel表格

在pandas中，讀取Excel非常簡單，它只有一個方法：readExcel()，但是的引數非常多

主要常用的引數，我們先對其進行了解：

• io：一般指定excel檔案路徑就可以了，也可以是其他Excel讀取物件如ExcelFile、xlrd.Book等
• sheet_name：用于指定作業表（sheet）名稱，可以是數字（作業表從0開始的索引）
• header：指定作為列名的行，默認為0，即第一行為列名，如果資料不含列名，則設為None
• names：指定新的列名串列，串列中元素個數和列數必須一致
• index_col：指定列為索引列，默認None指的是索引為0的第一列為索引列
• usecols：要決議資料的列，可以是int或者str的串列，也可以是以逗號分隔的字串(pandas 0.24新增功能)，例如：”A:F”，表示從A列到F列，”A,C,F”表示A、C、F三列，還可以寫成”A,C,F,K:Q”
• dtype：各列的資料型別，例如：{‘a’: np.float64, ‘b’: np.int32}
• converters：用于轉換各列資料的函式的字典資料，例如：{‘a’: func_1, ‘b’: func_2}

import pandas as pd
sheet = pd.read_excel(io="測驗資料.xlsx")
sheet.head()

我們先來看一下取回的資料的資料型別是什么，

print(type(sheet))

<class 'pandas.core.frame.DataFrame'>

可以看到，它就是我們前面提到的DataFrame資料，，直接通過它的列名稱來獲取即可，比如，要獲得所有的工資資訊，可以如下：

print(sheet['工資'])

0      7653
1      8799
2      9800
3     12880
4      3600
5      3800
6      8976
7     12000
8      8900
9      7688
10     6712
11     9655
12     6854
13     8122
14     6788
15     8830
Name: 工資, dtype: int64

可以看到它的所有的資料都列出來了，并且這一列資料的資料型別是int64，即64位整型，
得到這一列資料后，我們可以對它進行處理，

for i in sheet['工資']:
    print(i)

7653
8799
9800
12880
3600
3800
8976
12000
8900
7688
6712
9655
6854
8122
6788
8830

或者將它轉換成串列后再處理：

salaries = list(sheet['工資'])
print(salaries)

[7653, 8799, 9800, 12880, 3600, 3800, 8976, 12000, 8900, 7688, 6712, 9655, 6854, 8122, 6788, 8830]

計算大家的平均工資：

sum = 0
for i in salaries:
    sum += i
    
print(f"總工資：{sum}")
ave = sum / len(salaries)
print(f"平均工資：{ave}")

總工資：131057
平均工資：8191.0625

我們也可以對求和的方法，使用lambda運算式（匿名函式）結合reduce()函式進行，reduce()函式會對串列、元組等可遍歷的元素依次進行運算：將第一個元素和第二個元素進行運算，并將結果和第三個元素進行運算，直到最后一個元素，

import functools
sum = functools.reduce(lambda x, y: x + y, salaries)
print(sum)

131057

我們可以使用read_excel中的usecols引數，通過它指定我們需要讀取資料的列，它接收字串或者整數串列格式的資料，串列中列出我們想要取出資料的列的名稱或者索引，

import pandas as pd
sheet = pd.read_excel(io="測驗資料.xlsx", usecols=[2])
sheet

或者：

import pandas as pd
sheet = pd.read_excel(io="測驗資料.xlsx", usecols=['工資'])
sheet

如果想在讀取資料的時候，將原來的列的名字改成其他名字，則可以使用names引數指定為其他列名：

import pandas as pd
sheet = pd.read_excel(io="測驗資料.xlsx", names=['name','age','salary'])
sheet

需要注意的是，此時，我們如果要對這個DataFrame進行操作，就需要使用新的列名了， 如果我們想在取出工資資料的時候，以“￥12,345”的格式顯示，則可以在獲取資料的時候，就指定轉換函式：

import pandas as pd
def formatsalary(num):
    return f"￥{format(num,',')}"

sheet = pd.read_excel(io="測驗資料.xlsx", usecols=['工資'],converters={'工資':formatsalary})
sheet

上面通過converters指定了“工資”列，使用formatsalary函式來處理，所以取出來的資料就已經處理過的了，當然，我們也可以取出來后在對其進行格式化，
其他的引數，大家可以自己進行試驗，下面我們再來看一下，假設我要取出所有大于等于8000的工資，該如何進行處理呢？我們可以使用按照條件來獲取DataFrame的行資料：

import pandas as pd
sheet = pd.read_excel(io="測驗資料.xlsx", usecols=['工資'])
high_salary = sheet[sheet['工資'] >= 8000]
high_salary

如果想取得工資大于等于8000小于等于10000的資料：

import pandas as pd
sheet = pd.read_excel(io="測驗資料.xlsx")
high_salary = sheet[(sheet['工資'] >= 8000) & (sheet['工資'] <=10000)]
high_salary

如果只想顯示符合條件的姓名和工資，則可以通過串列的方式指定要顯示的列：

import pandas as pd
sheet = pd.read_excel(io="測驗資料.xlsx")
high_salary = sheet[(sheet['工資'] >= 8000) & (sheet['工資'] <=10000)][['姓名','工資']]
high_salary

讀取多個資料表

在上面的例子中，雖然在“測驗資料.xlsx”檔案中包含了兩個資料表（sheet），但它只讀取了第一個資料表的內容，如果我想把兩個資料表資料都讀取出來該怎么辦呢？可以指定sheet_name引數，它接收字串、數字、字串或數字串列以及None，如果指定為None，則回傳所有資料表資料，默認為0，即回傳第一個資料表資料，

import pandas as pd
sheet = pd.read_excel(io="測驗資料.xlsx", sheet_name=[0, 1])
sheet

{0:            姓名  年齡     工資
 0     OLIVER.  23   7653
 1      HARRY.  45   8799
 2     GEORGE.  34   9800
 3       NOAH.  54  12880
 4       JACK.  34   3600
 5      JACOB.  32   3800
 6   MUHAMMAD.  51   8976
 7        LEO.  46  12000
 8     Harper.  42   8900
 9     Evelyn.  38   7688
 10      Ella.  33   6712
 11     Avery.  26   9655
 12  Scarlett.  37   6854
 13   Madison.  41   8122
 14      Lily.  54   6788
 15   Eleanor.  28   8830,
 1:     姓名  年齡     工資
 0   張三  39  15000
 1   李四  43  16000
 2   李雷  25   6800
 3  韓梅梅  28  23000}

可以看到，得到了兩個資料表的資料，此時要得到資料表中的資料，就需要先通過sheet[0]、sheet[1]得到第一個資料表的所有資料，再在這個資料表資料中對資料進行處理了，例如：

sheet[1]

如果用的是資料表的名字，則應該寫成sheet[‘甲公司’]，
如果我們想把這兩個資料表的資料合并到一起，可以使用pandas中的concat()函式：

import pandas as pd
sheet = pd.read_excel(io="測驗資料.xlsx", sheet_name=[1, 0])
st = pd.concat(sheet,ignore_index = True)
st

這里ignore_index的意思是忽略各自的索引，統一使用新的索引，

合并多個作業表

多個EXCECL合并到一個作業表中，Python來幫你實作

# -*- coding:utf-8 -*-
# @Address:https://beishan.blog.csdn.net/
# @Author:北山啦
import pandas as pd
import os
path = r"五省PM2.5\archive"
dfs,index = [],0
for i in os.listdir(path):
    dfs.append(pd.read_csv(os.path.join(path,i)))
    print(f"正在合并{index+1}作業表")
    index += 1
df = pd.concat(dfs)
df.to_csv("資料匯總.csv",index=False)

正在合并1作業表
正在合并2作業表
正在合并3作業表
正在合并4作業表
正在合并5作業表
正在合并6作業表
正在合并7作業表

寫入Excel檔案

可以將DataFrame資料寫入到一個新的Excel檔案中，例如，我們可以將上面合并的兩個Excel資料表資料，寫入到新的Excel檔案中：

df = pd.DataFrame(st)
df.to_excel("合并工資報表.xlsx")

這里我們使用DataFrame上的to_excel()方法將資料寫入到Excel檔案中，它的原型是：to_excel(self, excel_writer, sheet_name=‘Sheet1’, na_rep=’’, float_format=None, columns=None, header=True, index=True, index_label=None, startrow=0, startcol=0, engine=None, merge_cells=True, encoding=None, inf_rep=‘inf’, verbose=True, freeze_panes=None)，常用的引數說明：

• excel_writer：需要指定一個寫入的檔案，可以是字串或者ExcelWriter物件
• sheet_name：寫入的作業表名稱，是一個字串，默認為’Sheet1’
• na_rep：當沒有資料的時候，應該填入的默認值，默認為空字串
• float_format：浮點數格式，默認為None，可以按照float_format="%.2f"這樣的方式指定
• columns：指定寫入的列名順序，是一個串列，
• header：是否有表頭，默認為True，可以是布爾型別或者字串串列，
• index：是否加上行索引，默認為True，
• index_label：索引標簽，可以是字串或者串列，默認為None，
• startrow：插入資料的起始行，默認為0，
• startcol：插入資料的其實列，默認0
• engine：使用的寫檔案引擎，例如：‘openpyxl’ 、 ‘xlsxwriter’
  當然，我們也可以不限于將一個Excel表中的資料寫入到另一個Excel檔案，我們自己在程式中運行得到的資料，也可以將其組織成DataFrame后，寫入到Excel檔案中，

import pandas as pd
df = pd.DataFrame({'姓名':['李雷', '韓梅梅', '小明',
                           '張三', '李四', '王五'],
                  '年齡':[31, 22, 30, 49, 38, 33]})
df.to_excel("員工表.xlsx", sheet_name="202002入職")

看看是不是寫入到檔案了：

f = pd.read_excel("員工表.xlsx")
f

可以看到，確實已經寫入進去了，
那如果要寫多個資料到一個Excel檔案的多個資料表（sheet）中，該怎么處理呢？此時可以使用下面的方法，

df1 = pd.DataFrame({'姓名':['李雷', '韓梅梅', '小明',
                           '張三', '李四', '王五'],
                  '年齡':[31, 22, 30, 49, 38, 33]})

df2 = pd.DataFrame({'Names': ['Andrew', 'Tomas', 'Larry',
                           'Sophie', 'Sally', 'Simone'],
                   'Age':[42, 37, 39, 35, 29, 27]})



dfs = {'國內員工':df1, '外籍員工':df2}
writer = pd.ExcelWriter('Employees.xlsx', engine='xlsxwriter')

for sheet_name in dfs.keys():
    dfs[sheet_name].to_excel(writer, sheet_name=sheet_name, index=False)
    
writer.save()

看看是不是已經寫入到檔案了：

sheet = pd.read_excel(io="Employees.xlsx", sheet_name=None)
sheet

{'國內員工':     姓名  年齡
 0   李雷  31
 1  韓梅梅  22
 2   小明  30
 3   張三  49
 4   李四  38
 5   王五  33,
 '外籍員工':     Names  Age
 0  Andrew   42
 1   Tomas   37
 2   Larry   39
 3  Sophie   35
 4   Sally   29
 5  Simone   27}

但是仔細看的話，會發現上面的外籍員工這個資料表，欄位Names和Age反了，這是因為DataFrame自動按照字母順序給我們排序了，要避免這種情況，需要在to_excel()中加上columns來指定表頭欄位順序：

df1 = pd.DataFrame({'姓名':['李雷', '韓梅梅', '小明',
                           '張三', '李四', '王五'],
                  '年齡':[31, 22, 30, 49, 38, 33]})

df2 = pd.DataFrame({'Names': ['Andrew', 'Tomas', 'Larry',
                           'Sophie', 'Sally', 'Simone'],
                   'Age':[42, 37, 39, 35, 29, 27]})



dfs = {'國內員工':df1, '外籍員工':df2}
cols = {"國內員工":['姓名', '年齡'],"外籍員工":['Names','Age']}  # 指定列名順序
writer = pd.ExcelWriter('Employees.xlsx', engine='xlsxwriter')

for sheet_name in dfs.keys():
    dfs[sheet_name].to_excel(writer, sheet_name=sheet_name, index=False, columns = cols[sheet_name])
    
writer.save()

再來看看現在是否正確：

sheet = pd.read_excel(io="Employees.xlsx", sheet_name=None)
sheet

{'國內員工':     姓名  年齡
 0   李雷  31
 1  韓梅梅  22
 2   小明  30
 3   張三  49
 4   李四  38
 5   王五  33,
 '外籍員工':     Names  Age
 0  Andrew   42
 1   Tomas   37
 2   Larry   39
 3  Sophie   35
 4   Sally   29
 5  Simone   27}

現在沒問題了，
還可以使用前面讀寫檔案的時候的with … 這種方式，
上面的方式，會覆寫原來的檔案內容，如果要在原有的Excel表中加上一個新的資料表（sheet），可以通過下面的方式：

from openpyxl import load_workbook
book = load_workbook("Employees.xlsx")  # 加載原有的資料到Workbook

df3 = pd.DataFrame({'Names': ['Judy'],
                   'Age':[27]})

with pd.ExcelWriter('Employees.xlsx',
                    engine='openpyxl') as writer:  
    writer.book = book  # 讓writer加入原來的兩個workbook
    df3.to_excel(writer, sheet_name='候補員工', index=False, columns=['Names', 'Age'])
    writer.save()

import pandas as pd
sheet = pd.read_excel(io="Employees.xlsx", sheet_name=None)
sheet

{'國內員工':     姓名  年齡
 0   李雷  31
 1  韓梅梅  22
 2   小明  30
 3   張三  49
 4   李四  38
 5   王五  33,
 '外籍員工':     Names  Age
 0  Andrew   42
 1   Tomas   37
 2   Larry   39
 3  Sophie   35
 4   Sally   29
 5  Simone   27,
 '候補員工':   Names  Age
 0  Judy   27}

可以看到，在原來的Excel檔案中，已經加入了“候補員工”這個資料表，加入需要在某個資料表中加入資料（append），可以使用下面方式：

from openpyxl import load_workbook
book = load_workbook("Employees.xlsx")  # 加載原有的資料到Workbook

df4 = pd.DataFrame({'Names': ['Moore'],
                   'Age':[38]})

with pd.ExcelWriter('Employees.xlsx',
                    engine='openpyxl') as writer:  
    writer.book = book  # 讓writer加入原來的3個workbook
    writer.sheets = {ws.title: ws for ws in book.worksheets}
    start_row = writer.sheets['候補員工'].max_row
    df4.to_excel(writer, sheet_name='候補員工', index=False, columns=['Names', 'Age'], startrow=start_row,header=False)
    writer.save()

這里的要點是：使用startrow指定要插入資料的文字，這里還要注意我們是往某個已經存在的資料表插入資料，所以要指定正確的sheet_name，還有就是為了避免重復的表頭，將header設定成False，

import pandas as pd
sheet = pd.read_excel(io="Employees.xlsx", sheet_name=None)
sheet

{'國內員工':     姓名  年齡
 0   李雷  31
 1  韓梅梅  22
 2   小明  30
 3   張三  49
 4   李四  38
 5   王五  33,
 '外籍員工':     Names  Age
 0  Andrew   42
 1   Tomas   37
 2   Larry   39
 3  Sophie   35
 4   Sally   29
 5  Simone   27,
 '候補員工':    Names  Age
 0   Judy   27
 1  Moore   38}

到這里就結束了，如果對你有幫助，歡迎點贊關注評論，你的點贊對我很重要，