Python Pandas 实现 Excel 的灵活操作

示例 Excel 数据

我们以下面Excel 为例，演示Python Pandas Excel操作。

pd.read_excel的主要参数

• io: excel文档路径。
• sheetname : 读取的excel指定的sheet页，若多个则为列表。
• header ：设置读取的excel第一行是否作为列名称。
• skiprows：省略指定行数的数据。
• skip_footer：省略从尾部数的int行数据。
• index_col：设置读取的excel第一列是否作为行名称。
• names：设置每列的名称，数组形式参数。
• usecols：读取指定的列, 也可以通过名字或索引值。

读取Excel文件

通过指定表单名的方式来读取，指定列的数据。

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])print(excel)

加载的Excel数据类型为DataFrame，执行上述代码，输出结果为：

普及一 下 DataFrame：

• DataFrame这种列表式的数据结构和Excel工作表非常类似，其设计初衷是将Series的使用场景由一维扩展到多维。DataFrame由按一定顺序的多列数据组成，各列的数据类型可以有所不同(数值、字符串、布尔值)。
• Series对象的Index数组存放有每个元素的标签，而DataFrame对象有所不同，它有两个索引数组。第一个索引数组与行有关，它与Series的索引数组极为相似。 每个标签与标签所在行的所有元素相关联。而第二个数组包含一系列标签，每个标签与一列数据相关联。
• DataFrame还可以理解为一个由Series组成的字典，其中每一列的列名为字典的键，每一个Series作为字典的值。

使用 iloc 从DataFrame中筛选数据

iloc 语法

 data.iloc[<row selection>, <column selection>]

iloc 在Pandas中是用来通过数字来选择数据中具体的某些行和列。可以设想每一行都有一个对应的下标（0,1,2，...)，通过 iloc 我们可以利用这些下标去选择相应的行数据。同理，对于行也一样，通过这些下标也可以选择相应的列数据。需要注意的是0表示第一行，并不包含表头。

选择单行或单列

选择数据中的第一行

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])# 选择第1行数据print(excel.iloc[0])# 执行上述代码，输出结果为:时间            2020-10-12 20:49:06产品名称       【拍卖交易】25000万金=362.25元 收购比例                        69.01收购金额RMB                    362.25收购金币数量                      25000Name: 0, dtype: objectProcess finished with exit code 0

选择数据中的最后一行

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])# 选择第1行数据print(excel.iloc[-1])

选择数据中的第一列

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])# 选择第1行数据print(excel.iloc[:, 0])  # 执行上述代码，输出结果为:0    2020-10-12 20:49:061    2020-10-12 20:42:152    2020-10-12 22:26:303    2020-10-13 19:48:594    2020-10-12 20:55:085    2020-10-12 20:53:326    2020-10-12 20:58:147    2020-10-12 21:00:168    2020-10-12 21:57:059    2020-10-12 21:56:04Name: 时间, dtype: objectProcess finished with exit code 0

选择数据中的最后一列

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])# 选择第1行数据 print(excel.iloc[:, -1])  

行列混合选择

选择数据中的第 1-3 行的所有列

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])# 数据中的第 1-3 行 所有列print(excel.iloc[0:3])# 执行上述代码，输出结果为:         时间                     产品名称             收购比例  收购金额RMB  收购金币数量0  2020-10-12 20:49:06   【拍卖交易】25000万金=362.25元   69.01   362.25   250001  2020-10-12 20:42:15      【拍卖交易】50000万金=725元   68.97   725.00   500002  2020-10-12 22:26:30  【拍卖交易】120000万金=1741.2元   68.92  1741.20  120000Process finished with exit code 0

选择数据中的前2列的所有行

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])# 选择数据中的前2列的所有行print(excel.iloc[:, 0:2])

选择第 0, 2, 4行 和 第 1,3 列

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])# 选择第 0, 2, 4行 和 第 1,3 列print(excel.iloc[[0, 2, 4], [1, 3]])

选择第0-3行 的 0-2列

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])# 选择第0-3行 的 0-2列print(excel.iloc[0:3, 0:2])

使用iloc只选择了单独的一行或一列，返回的数据为 Series 类型，而如果选择了多行数据则会返回 DataFrame 类型，若只选择了一行，但需要要返回 DataFrame 类型，可以传入一个单值列表，如[1]，如下：

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])# 行选择：数据中的第一行print(excel.iloc[[0] ])# 列选择： 数据中的第二列print(excel.iloc[:, [1] ])

使用 loc 从DataFrame中筛选数据

data.loc[<row selection>, <column selection>]

ioc 用于以下两种场景：

• 使用 下标 查找
• 使用 条件 查找

使用 下标 查找

选择数据中的第一行

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])# 数据中的第一行print(excel.loc[[1]])

选择数据中的前两行

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])# 数据中的第1、第1行print(excel.loc[[1, 2]])# 执行上述代码，输出结果为:       时间                     产品名称              收购比例  收购金额RMB  收购金币数量1  2020-10-12 20:42:15      【拍卖交易】50000万金=725元   68.97    725.0   500002  2020-10-12 22:26:30  【拍卖交易】120000万金=1741.2元   68.92   1741.2  120000

选择第1到3行的 “时间”、“收购比例” 列

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])# 选择第1到3行的 “时间”、“收购比例” 列print(excel.loc[1: 3, ['时间','收购比例']])# 执行上述代码，输出结果为:              时间   收购比例1  2020-10-12 20:42:15  68.972  2020-10-12 22:26:30  68.923  2020-10-13 19:48:59  69.23

选择第1、2行的 '时间' 到 '收购比例' 列

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])# 数据中的第1、第1行print(excel.loc[[1, 2], '时间':'收购比例' ])# 执行上述代码，输出结果为:                    时间                     产品名称   收购比例1  2020-10-12 20:42:15      【拍卖交易】50000万金=725元   68.972  2020-10-12 22:26:30  【拍卖交易】120000万金=1741.2元   68.92

需要注意excel.loc[[1]] 不等价于 excel.iloc[[1]]，前者是选择 索引为1 的行，而后者是选择第1行，DataFrame的索引可以是数字或者是字符串。

使用逻辑判断选择数据

选择“收购比例”列等于69.01的 “时间”列到 “收购比例” 列的数据

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])# 选择“收购比例”列等于69.01的 “时间”列到 “收购比例” 列的数据print(excel.loc[excel['收购比例'] == 69.01 ,'时间':'收购比例'])# 执行上述代码，输出结果为:                    时间                    产品名称   收购比例0  2020-10-12 20:49:06  【拍卖交易】25000万金=362.25元   69.019  2020-10-12 21:56:04  【拍卖交易】19500万金=282.55元   69.01

同样，如果只选择了某一列，返回的数据是 Series 类型，若只选择了一行，但需要要返回 DataFrame 类型，可以传入一个单值列表，如[1]。

选择 “产品名称”列 的值中是以 '【拍卖交易】' 开头的行的所有列

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])# 选择 “产品名称”列 的值中是以 '【拍卖交易】' 开头的行的所有列print(excel.loc[excel['产品名称'].str.startswith('【拍卖交易】')])# 执行上述代码，输出结果为:             时间                     产品名称        收购比例  收购金额RMB  收购金币数量0  2020-10-12 20:49:06   【拍卖交易】25000万金=362.25元   69.01   362.25   250001  2020-10-12 20:42:15      【拍卖交易】50000万金=725元   68.97   725.00   500002  2020-10-12 22:26:30  【拍卖交易】120000万金=1741.2元   68.92  1741.20  1200003  2020-10-13 19:48:59     【拍卖交易】90000万金=1300元   69.23  1300.00   900006  2020-10-12 20:58:14      【拍卖交易】50000万金=726元   68.87   726.00   500009  2020-10-12 21:56:04   【拍卖交易】19500万金=282.55元   69.01   282.55   19500        

选择'收购金币数量' 等于[25000, 50000]值的行

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])# 选择'收购金币数量' 等于[25000, 50000]值的行print(excel.loc[excel['收购金币数量'].isin([25000, 50000])])# 执行上述代码，输出结果为:                 时间                    产品名称   收购比例  收购金额RMB  收购金币数量0  2020-10-12 20:49:06  【拍卖交易】25000万金=362.25元   69.01   362.25   250001  2020-10-12 20:42:15     【拍卖交易】50000万金=725元   68.97   725.00   500006  2020-10-12 20:58:14     【拍卖交易】50000万金=726元   68.87   726.00   50000        

选择 收购比例 = 69.01 并且 产品名称 是以 '【拍卖交易】'开头的行

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])# 选择 收购比例 = 69.01 并且 产品名称 是以 '【拍卖交易】'开头的行print(excel.loc[excel['产品名称'].str.startswith('【拍卖交易】')                & (excel['收购比例'] == 69.01)])# 执行上述代码，输出结果为:                  时间                    产品名称   收购比例  收购金额RMB  收购金币数量0  2020-10-12 20:49:06  【拍卖交易】25000万金=362.25元   69.01   362.25   250009  2020-10-12 21:56:04  【拍卖交易】19500万金=282.55元   69.01   282.55   19500        

利用apply的lambda函数判断符合条件的行，如下选择 时间 列中20点的所有数据。

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])# 选择 时间 列中20点的所有数据print(excel.loc[          excel['时间']         .apply(lambda x: x.split()[1].split(':')[0] == '20')     ])# 执行上述代码，输出结果为:                时间                    产品名称   收购比例  收购金额RMB  收购金币数量0  2020-10-12 20:49:06  【拍卖交易】25000万金=362.25元   69.01   362.25   250001  2020-10-12 20:42:15     【拍卖交易】50000万金=725元   68.97   725.00   500004  2020-10-12 20:55:08      【当面交易】8000万金=116元   68.97   116.00    80005  2020-10-12 20:53:32     【邮寄交易】10000万金=144元   69.44   144.00   100006  2020-10-12 20:58:14     【拍卖交易】50000万金=726元   68.87   726.00   50000

利用apply的lambda函数判断符合条件的行的'时间', '产品名称' 列。

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])idx = excel['时间'].apply(lambda x: x.split()[1].split(':')[0] == '20')# 只选择 idx 值为True的那些行，并且只选择'时间', '产品名称'列print(excel.loc[idx, ['时间', '产品名称']])# 执行上述代码，输出结果为:                    时间                    产品名称0  2020-10-12 20:49:06  【拍卖交易】25000万金=362.25元 1  2020-10-12 20:42:15     【拍卖交易】50000万金=725元 4  2020-10-12 20:55:08      【当面交易】8000万金=116元 5  2020-10-12 20:53:32     【邮寄交易】10000万金=144元 6  2020-10-12 20:58:14     【拍卖交易】50000万金=726元 

Pandas中 apply、 applymap、 map 的区别：

• map仅是Series中的函数 ，map将函数应用于Series中的每一个元素。
• apply和applymap是仅是DataFrame 中的函数。
• apply 将函数作用于DataFrame中的每一个行或者列。
• applymap会将函数作用于DataFrame中的 每一个元素。

写 Excel 文件

Pandas可以写入一个或者多个工作簿，两种方法介绍如下：

将一个DafaFrame写入Excel

调用 to_excel() 方法即可实现，示例代码如下：

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])idx = excel['时间'].apply(lambda x: x.split()[1].split(':')[0] == '20')# 只选择 idx 值为True的那些行，并且只选择'时间', '产品名称'列data = excel.loc[idx, ['时间', '产品名称']]# 将数据写入 新的 文件中data.to_excel('数据爬取-筛选结果.xlsx', index=False)

多个数据写入多个excel的工作簿

这时需要调用通过 ExcelWriter() 方法打开一个已经存在的excel表格作为writer，然后通过to_excel()方法将需要保存的数据逐个写入excel，最后关闭writer。

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])idx = excel['时间'].apply(lambda x: x.split()[1].split(':')[0] == '20')# 存储sheet 与对应DataFrame数据sheet_dict = {}# 只选择 idx 值为True的那些行，并且只选择'时间', '产品名称'列data1 = excel.loc[idx, ['时间', '产品名称']]sheet_dict['sheet_name_1'] = data1# 只选择 idx 值为True的那些行，并且只选择'时间', '收购比例'列data2 = excel.loc[idx, ['时间', '收购比例']]sheet_dict['sheet_name_2'] = data2# 创建ExcelWriter对象writer = pd.ExcelWriter('数据爬取-筛选结果.xlsx')# 遍历所有sheet以及对应 DataFrame数据for sheet_name, data in sheet_dict.items():    # 将数据写入对应sheet中    data.to_excel(writer, sheet_name=sheet_name,index=False)# 如果省略该语句，则数据不会写入到上边创建的excel文件中writer.save()

---

此外，我们也需要知道如何创建DataFrame数据的方式，如下：

由 列表 创建 DataFrame

import pandas as pdtest_list = [   [1, 2, 3],   [4, 5, 6],   [7, 8, 9]]# 创建DataFrame，并指定列名，不指定默认索引值d1 = pd.DataFrame(test_list,columns=['学号','年龄','班级'])print(d1)# 将数据写入 新的 文件中d1.to_excel('test.xlsx', index=False)

由字典创建 DataFrame

import pandas as pdtest_dict = pd.DataFrame({    '学号': [1, 2, 3, 4],    '年龄': [5, 6, 7, 8],    '班级': [9, 10, 11, 12]})# 创建DataFrame，通过 columns 筛选 test_dict 的数据d1 = pd.DataFrame(test_dict, columns=['学号','年龄'])print(d1)# 将数据写入 新的 文件中d1.to_excel('数据爬取-筛选结果.xlsx', index=False)

扩展: 如何 在Pandas中遍历 DataFrame数据的行

推荐以 Pandas 的方式迭代遍历DataFrame的行，可以使用：

DataFrame.iterrows()

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])idx = excel['时间'].apply(lambda x: x.split()[1].split(':')[0] == '20')# 只选择 idx 值为True的那些行，并且只选择'时间', '产品名称'列data = excel.loc[idx, ['时间', '产品名称']]for index, row in data.iterrows():    print(index, row['时间'], row['产品名称'])        # 执行上述代码，输出结果为:0 2020-10-12 20:49:06 【拍卖交易】25000万金=362.25元 1 2020-10-12 20:42:15 【拍卖交易】50000万金=725元 4 2020-10-12 20:55:08 【当面交易】8000万金=116元 5 2020-10-12 20:53:32 【邮寄交易】10000万金=144元 6 2020-10-12 20:58:14 【拍卖交易】50000万金=726元        

DataFrame.itertuples()

快于.iterrows()，但将索引与行项目一起返回，row[0]是索引，如果它们是无效的Python标识符，重复或以下划线开头，列名称将被重命名为位置名称。

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',              sheet_name='金币跌涨追踪记录',              usecols=['时间','产品名称','收购比例','收购金额RMB','收购金币数量'])idx = excel['时间'].apply(lambda x: x.split()[1].split(':')[0] == '20')# 只选择 idx 值为True的那些行，并且只选择'时间', '产品名称'列data = excel.loc[idx, ['时间', '产品名称']]for row in data.itertuples():    print(getattr(row, '时间'),getattr(row, '产品名称'))    # 或者通过索引方式获取    print(row[1],row[2])    

其他方式

也可以使用df.apply()遍历行并访问函数的多个列。

DataFrame.apply()

import pandas as pdexcel = pd.read_excel('数据爬取-金币涨跌追踪.xlsx',                      sheet_name='金币跌涨追踪记录',                      usecols=['时间', '产品名称', '收购比例', '收购金额RMB', '收购金币数量'])idx = excel['时间'].apply(lambda x: x.split()[1].split(':')[0] == '20')# 只选择 idx 值为True的那些行，并且只选择'收购金币数量', '收购比例'列data = excel.loc[idx, ['收购金币数量', '收购比例']]# 定义一个函数 def pay_rmb(number, rate):    '''   计算 收购金币数量/ 换算比例 = 花费rmb数量   '''    return number / rate# 增加收购金额RMB列，并通过遍历每一行 对该列赋值data['收购金额RMB'] = data.apply(lambda row: pay_rmb(row['收购金币数量'], row['收购比例']), axis=1)print(data)# 执行上述代码，输出结果为   收购金币数量   收购比例     收购金额RMB0   25000  69.01  362.2663381   50000  68.97  724.9528784    8000  68.97  115.9924605   10000  69.44  144.0092176   50000  68.87  726.005518