pandas记录——02.excel文件的读写

系列文章目录

01.csv文件的读写
02.excel文件的读写

---

---

前言

本系列用于记录在pandas使用过程中的遇到的各个功能。
本章节记录对于Excel文件的读写。

---

一、Excel文件的读取与写入

注意：pandas读取Excel文件是调用xlrd进行的，请务必安装xlrd 1.2.0版本！！

• xlrd 1.1.0版本读取xls文件会报错，读取xlsx文件正常
• xlrd 2.0.1版本（目前最新）读取xlsx文件会报错，读取xls文件正常
  附安装方式：

pip uninstall xlrd
pip install xlrd==1.2.0

1.read_excel()

read_excel()函数用于读取xls和xlsx文件，输出内容为DataFrame格式。
read_excel()包含许多可配置参数：

def read_excel(
    io,
    sheet_name=0,
    header=0,
    names=None,
    index_col=None,
    usecols=None,
    squeeze=False,
    dtype=None,
    engine=None,
    converters=None,
    true_values=None,
    false_values=None,
    skiprows=None,
    nrows=None,
    na_values=None,
    keep_default_na=True,
    na_filter=True,
    verbose=False,
    parse_dates=False,
    date_parser=None,
    thousands=None,
    comment=None,
    skipfooter=0,
    convert_float=True,
    mangle_dupe_cols=True,
    storage_options: StorageOptions = None,):

本章主要介绍read_excel()以下常用参数内容。

1.1 io

可以接收str, bytes, ExcelFile, xlrd.Book, path对象, 以及 file-like对象。这里的str是一个有效的文件路径字符串、path对象可以是pathlib库中提供的Path类也可以是os库中提供的 os.PathLike， file-like对象一般是open函数返回的文件流对象。

import pandas as pd 
pd.read_excel("girl.xls")

结果：

这里可以结合pandas的ExcelFile来使用，ExcelFile可以一次将文件中的所有sheet读入到内存中，减少IO次数。并且ExcelFile的实例对象有一个属性sheet_names，可以查看实例中的sheet名列表

excel = pd.ExcelFile("girl.xls")
excel.sheet_names

['Sheet1', 'Sheet2']

结合with使用：

with pd.ExcelFile("girl.xls") as excel:
df = pd.read_excel(excel, 0)

1.2 sheet_name

设置读取的Excel表中的sheet，默认为sheet = 0，即只读取第一个sheet。

• 可以设置sheet为索引值：0，1，2，…，返回值为DataFrame，适用于已知sheet内容；
• 可以设置sheet为list，例如[0, 1]，返回值为dict，key为sheet的索引值，value为sheet的DataFrame内容；
• 可以设置sheet为None，返回所有sheet的，dict，key为sheet的字符串格式的名称，value为sheet的DataFrame内容；

pd.read_excel("girl.xls", sheet = 1)
pd.read_excel("girl.xls", sheet = [0, 1])
pd.read_excel("girl.xls", sheet = None)

1.3 header

从现有表格中选择一行设置为DataFrame的列名称，默认为0，即设置第一行为列名称。

• 若设置为整数n，则表示将表格中的n行设置为列名称
• 若设置为None，则表示不考虑列名称
• 若设置为list，则将此n行组合为列名称

但是需要注意与1.4的name的关联关系，此处与read_csv()方法存在不同。

pd.read_excel("girl.xls", header=1) # 不指定names，指定header为1，则选取第二行当做表头，第二行下

结果：

1.4 name

设置DataFrame的列名称，需要为list格式，list中元素个数需要与列对齐，否则会将最后“list中元素个数”行设置为名称，前面的行统一被设置为index。

要注意的是，这里和read_csv函数中有所不同，在read_csv函数中，只指定names参数，header就为None，但是在read_excel函数中header默认值始终为0，因此此时如果不将header手动调整为None，第一行数据会首先被当做表头读入，然后被names参数定义的表头替换，这样数据就少了一行了。如果文件中有表头，但是我们对表头不满意，使用这个参数结合参数header=0来修改表头。

此处的内容可以查看01.csv文件的读写 对应章节。

1.5 index_col

默认生成的DataFrame的索引默认是0 1 2 3…，也可以set_index，但是也可以在读取的时候就指定某个列为索引。

另外除了指定单个列，还可以指定多个列，比如[“id”, “name”]。并且我们除了可以输入列的名字之外，还可以输入对应的索引。比如:“id”、“name”、“address”、"date"对应的索引就分别是0、1、2、3。

1.6 usecols

指定需要导出的列名称，当列有很多，而我们不想要全部的列、而是只要指定的列就可以使用这个参数。

1.7 skiprows、nrows和skipfooter

skiprows表示读取数据时哪些行想要跳过，接收一个int，表示前n行跳过。接收一个int列表，会跳过列表中指定的行。还可以接收一个可调用对象。

nrows指定我们想读取数据中多少行。

skipfooter接收一个整数n，表示不读取数据的最后n行。

2. to_excel()

to_excel()方法的功能是将DataFrame对象写入到Excel工作表中。
to_excel包含以下众多的参数：

def to_excel(
        self,
        excel_writer,
        sheet_name: str = "Sheet1",
        na_rep: str = "",
        float_format: Optional[str] = None,
        columns: Optional[Sequence[Label]] = None,
        header: Union[Sequence[Label], bool] = True,
        index: bool = True,
        index_label: Optional[Union[Label, Sequence[Label]]] = None,
        startrow: int = 0,
        startcol: int = 0,
        engine: Optional[str] = None,
        merge_cells: bool = True,
        encoding: Optional[str] = None,
        inf_rep: str = "inf",
        verbose: bool = True,
        freeze_panes: Optional[Tuple[int, int]] = None,
    ) -> None:

本章以常用的以下参数进行介绍。

2.1 excel_writer

必须填写的内容项，需要以.xls或.xlsx文件为结尾。

• 若仅填写文件名，则会保存到此Python程序的地址
• 若填写绝对位置，则会按照位置，文件名称进行保存

dt.to_excel('girl.xls') #相对位置，保存在getwcd()获得的路径下
dt.to_excel('C:/Users/think/Desktop/girl.xls') #绝对位置

2.2 sheet_name

工作表的名称，可以接受字符串，默认为“sheet1”。由于to_excel是针对DataFrame对象，因此每次仅可以存入一个sheet中。
若存在多个DataFrame对象需要存入一张Excel中的多个sheet中，直接使用sheet_name = list()方式会出现最新的表格将前面的表格覆盖，最终导出的Excel文件仍旧是一个sheet。
此时需要借助ExcelWriter的方式：

writer = pd.ExcelWriter('C:/Users/think/Desktop/girl.xls')
dt1.to_excel(writer, sheet_name = 'sheet1')
dt2.to_excel(writer, sheet_name = 'sheet2')

2.3 na_rep

替换空值，若DataFrame中有值为空，则保存时也会保存为空值，配置此参数则可以设置空值内容。

dt.to_excel('C:/Users/think/Desktop/girl.csv',na_rep='NA') #确实值保存为NA，如果不写，默认是空

2.4 float_format

针对浮点数值，设置保存的小数位数。

dt.to_excel('C:/Users/think/Desktop/girl.csv',float_format='%.2f') #保留两位小数

2.5 columns

设置保存的列，若不配置则默认保存所有列。

dt.to_excel('C:/Users/think/Desktop/girl.csv',columns=['name']) #保存索引列和name列

2.6 header

设置是否保存列名，默认保存列名为第一行，若设置0则不保存列名。

dt.to_excel('C:/Users/think/Desktop/girl.csv',header=0) #不保存列名

2.7 index

设置是否保存行索引，默认保存行索引为第一列，若设置0则不保存。

dt.to_excel('C:/Users/think/Desktop/girl.csv',index=0) #不保存行索引

二、Excel文件写入方式扩展

1. 多个sheet写入到同一个Excel

当Pandas要写入多个sheet时，to_excel第一个参数excel_writer要选择ExcelWriter对象，不能是文件的路径。否则，就会覆盖写入。
ExcelWriter可以通过上下文管理器来执行，ExcelWriter可以看作一个容器，一次性提交所有to_excel语句后再保存，从而避免覆盖写入。

import pandas as pd

df1 = pd.DataFrame({'One': [1, 2, 3]})
df2 = pd.DataFrame({'Two': [4, 5, 6]})

# 方式1
with pd.ExcelWriter('excel1.xlsx') as writer:
    df1.to_excel(writer, sheet_name='Sheet1', index=False)
    df2.to_excel(writer, sheet_name='Sheet2', index=False)

# 方式2
writer = pd.ExcelWriter('excel1.xlsx')
df1.to_excel(writer, sheet_name='Sheet1', index=False)
df2.to_excel(writer, sheet_name='Sheet2', index=False)
writer.save()

2. 新增sheet，不覆盖已存在的sheet

默认配置下，上面代码会将excel1.xlsx中新增sheet1与sheet2，并且清除里面原有的sheet。若希望保留原有的sheet，则需要将ExcelWriter的参数mode=‘a’，模式改为新增，非写入（‘w’）

import pandas as pd

df3 = pd.DataFrame({'One': [7, 8, 9]})

writer = pd.ExcelWriter('excel1.xlsx', mode='a') # 不会清除Excel文件中原有的sheet
df3.to_excel(writer, sheet_name='Sheet3', index=False)
writer.save()

3. 修改原有sheet，不覆盖已存在的sheet

其实，这是一个折中的方案，先用openpyxl 读取到了excel1，载入excel1的内容到ExcelWriter中，再对Sheet4、Sheet5进行覆盖写入。
并且，也可以使用to_excel的参数startrow、startcol为写入的起始行列来修改原有sheet中的内容。

import pandas as pd
from openpyxl import load_workbook

df41 = pd.DataFrame({'Four': [44, 55, 66]})
df51 = pd.DataFrame({'Five': [77, 88, 99]})

with pd.ExcelWriter('excel1.xlsx') as writer:
    book = load_workbook('excel1.xlsx')
    writer.book = book # 读取excel
    writer.sheets = dict((ws.title, ws) for ws in book.worksheets) # 复制excel的所有表
    df41.to_excel(writer, sheet_name='Sheet4', index=False)
    df51.to_excel(writer, sheet_name='Sheet5', index=False, header=False, startrow=4, startcol=0)

---

参考：
Pandas写入Excel函数——to_excel 技术总结