首页 互联网正文

Python 中利用Pandas处理复杂的Excel数据

qq841034526 互联网 2021-02-21 14:44:34 5420 0 python excel数据处理
Python 中利用Pandas处理复杂的Excel数据  python excel数据处理 第1张

关于Excel数据处理,很多同学可能使用过Pyhton的pandas模块,用它可以轻松地读取和转换Excel数据。但是实际中Excel表格结构可能比较杂乱,数据会分散不同的工作表中,而且在表格中分布很乱,这种情况下啊直接使用pandas就会非常吃力。本文虫虫给大家介绍使用pandas和openpyxl读取这样的数据的方法。


问题缘起

pandas read_excel函数在读取Excel工作表方面做得很好。但是,如果数据不是从头开始,不是从单元格A1开始的连续表格,则结果会不是很好。比如下面一个销售表,使用read_excel读取:


Python 中利用Pandas处理复杂的Excel数据  python excel数据处理 第2张



读取的结果如下所示:


Python 中利用Pandas处理复杂的Excel数据  python excel数据处理 第3张

结果中标题表头变成了Unnamed,而且还会额外增加很多职位NaN列,字段为空的列的值也会被转换为NaN,这显然不是我们所期望的。


header和usecols参数

对这样的非标准格式的表格,我们可以使用read_excel()的header和usecols参数来控制选择的需要读取的列。


import pandas as pdfrom pathlib import Pathsrc_file = 'sales.xlsx'

df = pd.read_excel(src_file, header=1, usecols='B:F')


Python 中利用Pandas处理复杂的Excel数据  python excel数据处理 第4张

结果的DataFrame包含了我们期望的数据。


代码中使用header和usecols参数设定了用于显示标题的列和需要读取的字段:


header参数为一个整数,从0开始索引,其为选择的行,比如1表示Excel中的第2行。


usecols参数设定选择的Excel列范围范围(A-…),例如,B:F表示读取B到F列。


在某些情况下,可能希望将列定义为数字列表。比如,可以定义整数列数:


df = pd.read_excel(src_file, header=1, usecols=[1,2,3,4,5])

这对对大型数据集(例如,每3列或仅偶数列)要遵循一定的数字模式,则这个参数方法会很有用。


usecols还可以设定从列名列表读取。比如上面的例子也可以这样写:


df = pd.read_excel(src_file,header=1,usecols=['item_type', 'order id', 'order date', 'state', 'priority'])

列顺序支持自由选择,这种命名列列表的方式实际中很有用。


usecols支持一个回调函数column_check,可通过该函数对数据进行处理。


下面是一个简单的示例:




def column_check(x): if 'unnamed' in x.lower(): return False if 'priority' in x.lower(): return False if 'order' in x.lower(): return True return True



df = pd.read_excel(src_file, header=1, usecols=column_check)

column_check按名称解析每列,每列通过定义True或False,来选择是否读取。


usecols也可以使用lambda表达式。下面的示例中定义的需要显示的字段列表。为了进行比较,通过将名称转换为小写来规范化。


cols_to_use = ['item_type', 'order id', 'order date', 'state', 'priority']df = pd.read_excel(src_file,header=1,usecols=lambda x: x.lower() in cols_to_use)

回调函数为我们提供了许多灵活性,可以处理Excel文件的实际混乱情况。


关于read_exce函数更多参数可以查看官方文档,下面是一个总结表格:


Python 中利用Pandas处理复杂的Excel数据  python excel数据处理 第5张

结合openpyxl

在某些情况下,数据甚至可能在Excel中变得更加复杂。在下面示例中,我们有一个ship_cost要读取的表。如果必须使用这样的文件,那么只用pandas函数和选项也很难做到。在这种情况下,可以直接使用openpyxl解析文件并将数据转换为pandas DataFrame。比如要读取下面示例的数据:


Python 中利用Pandas处理复杂的Excel数据  python excel数据处理 第6张



from openpyxl import load_workbookimport pandas as pdfrom pathlib import Pathsrc_file = ' sales1.xlsx'

加载整个工作簿:


cc = load_workbook(filename = src_file)

查看所有工作表:


cc.sheetnames

['sales', 'shipping_rates']


要访问特定的工作表:


sheet = cc['shipping_rates']

要查看所有命名表的列表:


sheet.tables.keys()

dict_keys(['ship_cost'])


该键对应于Excel中分配给表的名称。这样就可以设定要读取的Excel范围:


lookup_table = sheet.tables['ship_cost']

lookup_table.ref

'C8:E16'


这样就获得了要加载的数据范围。最后将其转换为pandas DataFrame即可。遍历每一行并转换为DataFrame:




data = sheet[lookup_table.ref]rows_list = []for row in data:cols = []for col in row:cols.append(col.value)rows_list.append(cols)df = pd.DataFrame(data=rows_list[1:], index=None, columns=rows_list[0])



结果数据框:


Python 中利用Pandas处理复杂的Excel数据  python excel数据处理 第7张

总结

在理想情况下,使用的数据将采用简单一致的格式。在本文中,我们介绍了在Pandas下通过参数轻松删除行和列以使其格式更加合理。尤其是结合openpyxl的情况下可以让我们读取Excel数据更加灵活,可以处理比较复杂的表格数据。


本文内容来自于站长博客网址为www.foosun.cn
版权声明

本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。

站长博客为互联网创造价值。

本文链接:https://www.foosun.cn/wz/9947.html

评论

控制面板

您好,欢迎到访网站!
  查看权限

最新发布

站长博客 - 为互联网创造价值的Blogger平台

https://www.foosun.cn/

| 浙ICP备20019018号-1

站长博客版权所有www.foosun.cn

浙ICP备20019018号

使用手机软件扫描微信二维码

添加站长商讨技术与服务内容

站长博客为您提供有用的互联网技术信息