python学习笔记：EXCEL文件处理

使用pandas数据分析库

一、读取EXCEL文件

1.1 pandas.read_excel介绍

        读取Excel文件可以使用pandas.read_excel(params)：

• 将一个excel文件读取为DataFrame格式数据
• 支持文件：以xls、xlsx、xlsm、xlsb、odf、odt为扩展名的文件
• 可读取Excel的1个或多个子表
• params参数包括：

参数	支持的数据类型	用途
io	str：有效的文件路径字符串，如：r"C:\XX.xslx"（例1） ExcelFile：一种类对象，将一个有多个子表的Excel文件包装在一起，为的是在处理多个子表的Excel文件时，读取Excel文件至内存仅需1次，可提升性能，但在使用前需先使用pd.ExcelFile类实例化产生ExcelFile对象。（例2） xlrd.Book：一种类对象，xlrd.Book对象由xlrd库中的xlrd.open_workbook()函数和on_demand=True参数创建。（例3） path对象：pathlib库中的Path类、os库中的os.PathLike file-like对象：open函数返回的文件流对象 bytes：IObytes？	指向需要读取的文件
sheet_name	default：0（默认读取的sheet1）。 str：sheet名称的字符串。 int：sheet位置编号，从0开始：sheet1为0，以此类推。 list：读取多个子表，即：sheet位置编号或sheet名称的列表。 None：读取所有子表，读取所有子表时使用ExcelFile类更方便和高效（例5） 当读取多张子表时，返回一个字典dict，该dict的键由读取的子表原名称组成，如：'Sheet1'、'Shee2'......（注意S大写），每个键对应的值为各个子表以DataFrame格式存储的数据。	指定读取的子表
header	default：0，默认第一行为表头。 int：表头在文件中行号的索引，从0开始，即：0为第一行，以此类推。 list of int：多级索引，将list中的指定行对应组合为索引。 None：无表头，自动从0开始编号作为列索引。（例6）	是否读取表头，表头行号索引
names	default：None list：列名的列表。（例7） 如果原表无表头，则使用前需指定header=None，否则使用names后少第一行数据； 如果原表有表头，应当使用时指定header=0，否则使用names后原表头行成为数据。	读取文件时创建表头
index_col	default：None，不指定专门的行索引。 int：将第X列作为行索引（index）。 list of int：将list中的多列组合为多级索引，作为行索引（multi-index）（例8）。	指定列为行索引
usecols	default：None str：第X列（只能是表格自动对列的字母编号，如'A'等），或者多列，如'A:E'（包括'A'和'E'列）、'A, E:F'（中间用逗号隔开） list of str：多列，str只能是表头对应列名 list of int：多列，int是表格对应的第X列 callable：？（例9）	读取指定列或多列
dtype	接收一个类型名，如np.str_、str等，这时候所有列都将被设置为这种数据类型。如果接收一个字典，可以为数据中每个列单独指定数据类型，如{'id':str,'height':np.float64}表示将id列定义为str类型，height列定义为numpy中的float64类型，未指定的列则由read_excel函数自己推断。	指定各列的数据类型
squeeze	如果数据中只有一列，或者函数的返回值只有一列，默认返回DataFrame。这个参数指定为True时，可让返回Series。	设置DataFrame还是Series类型

1.2 举例

文件地址：r"C:\...\Desktop\new.xlsx"

包含3张子表：Sheet1、运营问题统计和Sheet3，其中：Sheet3为空表。

•  例1：IO为字符串

import pandas as pd
#IO为Excel文件路径，字符串类型
data = pd.read_excel(r"C:\...\Desktop\msn.xlsx")

• 例2：IO为ExcelFile对象 

import pandas as pd

#（2）IO为excelFile对象

file = pd.ExcelFile(r"C:\...\Desktop\new.xlsx")   #实例化ExcelFile对象
file_names          #查看读取的file的所有子表名
>>>['Sheet1','运营问题统计','sheet3']

data2 = pd.read_excel(file)   #读取file的第1个子表（默认sheet_name=0） 

• 例3：IO为xlrd.Book对象

import pandas as pd
import xlrd

#（3）IO为xlrd.Book对象
 xlrd_book = xlrd.open_workbook(r"C:\...\Desktop\newtest.xls", on_demand=True)   #注意：不支持.xlsx扩展名的Excel文件！

type(xlrd_book)
>>><class 'xlrd.book.Book'>

data3 = pd.read_excel(xlrd_book)    #读取xlr.Book对象数据

• 例4：sheet_name用法

import pandas as pd

data1 = pd.read_excel(r"C:\...\Desktop\new.xlsx", sheet_name=1)  #读取第二张子表

data2 = pd.read_excel(r"C:\...\Desktop\new.xlsx", sheet_name='运营问题统计') #读取子表名称为运营事件统计的表

data3 = pd.read_excel(r"C:\...\Desktop\new.xlsx", sheet_name=None)  #读取所有子表
type(data3)
>>><class 'dict'>
data3.keys()
>>>dict_keys(['Sheet1', '运营问题统计', 'Sheet3'])
data3['Sheet3']           #输出第三张子表的数据，第三张为空表
>>>Empty DataFrame
   Columns: []
   Index: []

• 例5：使用ExcelFile类读取所有子表

file=pd.ExcelFile(r"C:\...\Desktop\new.xlsx")
with file as excel:    
    df1 = pd.read_excel(excel,0)            #读取第一张子表
    df2 = pd.read_excel(excel,1)            #读取第二张子表

• 例6：使用header

#（1）header=0
data = pd.read_excel(r"C:\...\Desktop\new.xlsx",header=0)  #读取第一行为表头（列索引）
data.index    #获取data的行索引
>>>RangeIndex(start=0, stop=109, step=1)
data.columns  #获取data的列索引
>>>Index(['序号', 'M号', 'Z号', '交付客户', '交付日期', '交付年份', '数量'], dtype='object')

#（2）header=1
data = pd.read_excel(r"C:\...\Desktop\new.xlsx",header=1) #获取第二行为表头（列索引）
data.columns
>>>Index([1, '106', 'B', 'E', 45259, '2015年', 'UEA'], dtype='object')

#（3）header=None
data = pd.read_excel(r"C:\...\Desktop\new.xlsx",header=None) #无表头
data.columns
>>>Int64Index([0, 1, 2, 3, 4, 5, 6], dtype='int64')   #自动编号

#（4）header=[0,1]
data = pd.read_excel(r"C:\...\Desktop\new.xlsx",header=[0,1]) #将第一行和第二行作为二级索引
data.columns
>>>MultiIndex([(  '序号',        1),
            ('M号',  '106'),
            ( 'Z号', 'B'),
            ('交付客户',   'E'),
            ('交付日期',    45259),
            ('交付年份',  '2015年'),
            ('数量',  'UEA')],
           )

• 例7：使用names设置表头

data = pd.read_excel(r"C:\Users\xiaoz\Desktop\ARJ21机队交付时间.xlsx",header=0,
       names=['A','B','C','D','E','F'])
data['A']
>>> 0        1
1        2
2        3
3        4
4        5
      ...
104    105
105    106
106    107
107    108
108    109
Name: A, Length: 109, dtype: int64

• 例8：使用index_col指定列作为行索引

#不使用index_col指定行索引
data1 = pd.read_excel(r"C:\...\Desktop\new.xlsx")
data1.index
>>>RangeIndex(start=0, stop=20789, step=1)

#使用index_col指定第一列作为行索引
data2 = pd.read_excel(r"C:\...\Desktop\new.xlsx", index_col=0)
data2.index
>>>Int64Index([    0,     1,     2,     3,     4,     5,     6,     7,     8,
                9,
            ...
            20779, 20780, 20781, 20782, 20783, 20784, 20785, 20786, 20787,
            20788],
           dtype='int64', length=20789)

• 例9：使用usecols读取指定列或多列

#读取1列，用str
data_onecol1 = pd.read_excel(r"C:\...\Desktop\论文数据整理\来源\new.xlsx", usecols='B')  #读取B列（第二列）
#读取1列，用list of str
data_onecol2 = pd.read_excel(r"C:\...\Desktop\论文数据整理\来源\new.xlsx", usecols=['发生时间'])   #读取列名为"发生时间"的列
#读取1列，用list of int
data_onecol3 = pd.read_excel(r"C:\...\Desktop\论文数据整理\来源\new.xlsx", usecols=[1])  #读取第二列

#读取多列，用str
data_col1 = pd.read_excel(r"C:\...\Desktop\论文数据整理\来源\new.xlsx", usecols='A,B')  #读取A、B列
#读取多列，用list
data_col2 = pd.read_excel(r"C:\...\Desktop\论文数据整理\来源\new.xlsx", usecols=['序号','发生时间'])    #读取列名为“序号”和“发生时间”的列
data_col3 = pd.read_excel(r"C:\...\Desktop\论文数据整理\来源\new.xlsx", usecols=[1,2])  #读取第2列和第3列