本篇文章选自作者在 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)
可以显示匹配到的文件。