系列文章目录
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)