使用,Pandas,进行大型,Excel,文件处理

使用Pandas进行大型Excel文件处理

 

Python部落(www.freelycode.com)组织翻译, 禁止转载

今天我们将学习如何使用Pandas进行大文件处理，重点关注Excel文件的读取解析及对原始数据进行抽取加工。

 

本教程采用的Python（已通过64位版本的v2.7.9和v3.4.3的测试），Pandas （v0.16.1），和XlsxWriter（v0.7.3）。 我们建议使用Anaconda版本快速上手，因为它预装了所有需要的依赖库。

本文是由Python For Engineers的创始人Shantnu Tiwari 和 Real Python 上有趣的小伙伴们合作完成。

读取文件

我们要处理的第一份文件，是在1979-2004年间在英国发生的所有车祸的汇总数据中，抽取2000年发生在伦敦的所有事故数据。

Excel

首先，从data.gov.uk网站上下载源文件，并解压ZIP包。 然后找到Accidents7904.csv，并在Excel中打开。请注意，如果没有足够的内存，这很可能使您的计算机程序崩溃。

发生了什么事情呢？

你应该会看到一个“文件未完全加载”的错误，因为Excel一次只能处理一个百万行。 我们通过LibreOffice中进行测试，并收到了类似的错误- “数据不能被完全加载，因为已经超过了每个sheet表能够加载的最大行数。”

为了解决这个问题，我们需要使用Pandas打开这个文件。 在我们开始之前，可以在Github上获取最新源代码 。

Pandas

使用 virtualenv沙盒环境，创建一个新的工程目录，然后开始安装Pandas：

$ pip install pandas==0.16.1

现在，可以构建我们的脚本。创建一个名为pandas_accidents.py，然后添加以下代码：

import pandas as pd

 

# Read the file

data = pd.read_csv("Accidents7904.csv", low_memory=False)

# Output the number of rows

print("Total rows: {0}".format(len(data)))

# See which headers are available

print(list(data))

在这里，我们导入了Pandas模块用户读取文件--这可能需要一些时间，主要取决于你的系统拥有有多少内存 ，并且输出文件中所有行的数据具有和现有的表头数据（例如，列标题）。

当运行结束，你将看到：

总行数: 6224198

['\xef\xbb\xbfAccident_Index', 'Location_Easting_OSGR', 'Location_Northing_OSGR',

'Longitude', 'Latitude', 'Police_Force', 'Accident_Severity', 'Number_of_Vehicles',

'Number_of_Casualties', 'Date', 'Day_of_Week', 'Time', 'Local_Authority_(District)',

'Local_Authority_(Highway)', '1st_Road_Class', '1st_Road_Number', 'Road_Type',

'Speed_limit', 'Junction_Detail', 'Junction_Control', '2nd_Road_Class',

'2nd_Road_Number', 'Pedestrian_Crossing-Human_Control',

'Pedestrian_Crossing-Physical_Facilities', 'Light_Conditions', 'Weather_Conditions',

'Road_Surface_Conditions', 'Special_Conditions_at_Site', 'Carriageway_Hazards',

'Urban_or_Rural_Area', 'Did_Police_Officer_Attend_Scene_of_Accident',

'LSOA_of_Accident_Location']

哇，超过六百万行的数据！ 难怪Excel抑郁了。现在将注意力转移到头部，特别是第一个：

'\xef\xbb\xbfAccident_Index',

这应该读作Accident_Index，那么开头处额外的\xef\xbb\xbf是什么呢？ 好了，\x实际上意味着该值是十六进制的 ，这是一个字节顺序标记 ，表示该文本为Unicode。

为何这点对我们如此重要？

你不能假设你读取的文件是无干扰因素的。它们可能包含像这样多余的标记符号，从而使你的脚本无法正常运行。

如果一个文件是无干扰数据，那这个文件就是个好文件 - 但很多文件有丢失数据，或在内部数据格式上不一致，等等……。因此，任何时候你有一个文件来分析，你必须做的第一件事就是清理数据。要进行多少清理呢？要足以让你能够做一些分析。 遵循KISS原则。

你可能需要什么样的清理呢？

修复日期/时间，同样的文件中可能包含不同的日期格式，如美式（MM-DD-YY）或欧式（DD-MM-YY）的格式。 这些都需要归纳成一个共同的格式。

删除所有的空值。该文件可能有空白列和(或)行，在Pandas这些这将显示NaN（非数字）。 Pandas提供了一种简单的方法来移除空值：dropna()函数。 我们看到了一个这样的例子last blog post。

删除那些用自己的方式进入数据的任何垃圾值，这些都是无意义的垃圾值（像之前我们看到的字节顺序标记）。 但是，有时我们可以解决它们。例如在一个年龄被作为一个浮点数输入的（误操作）的数据集中， 用int()函数，则可以用来确保所有年龄段都是整数格式。

分析数据

你们当中知道SQL的小伙伴，可以使用SELECT，WHERE，AND / OR语句，使用不同的关键词来缩小搜索范围。我们可以通过Pandas做到同样的效果，并且是对程序员来说更友好的方式。

让我们开始吧，首先我们找到所有发生在星期天的事故。通过查看表头，我们将使用一个叫做Day_of_Weeks字段。

在你下载的ZIP文件，有一个Road-Accident-Safety-Data-Guide-1979-2004.xls文件，这个文件中包含我们代码所需要的额外的信息。如果你打开它，你会看到星期天的代号为1 。

print("\nAccidents")

print("-----------")

 

# Accidents which happened on a Sunday

accidents_sunday = data[data.Day_of_Week == 1]

print("Accidents which happened on a Sunday: {0}".format(

   len(accidents_sunday)))

这是多么简单。

在这里，我们锁定了Day_of_Weeks字段并返回一个有过滤条件day of week == 1的DataFrame表数据 。

当你运行代码时，应该看到：

Accidents

-----------

Accidents which happened on a Sunday: 693847

正如你所看到的，有693847事故发生在星期天。

让我们把我们的查询做的更加复杂：找出发生在周日，涉及超过20辆汽车的所有事故：

# Accidents which happened on a Sunday, > 20 cars

accidents_sunday_twenty_cars = data[

   (data.Day_of_Week == 1) & (data.Number_of_Vehicles > 20)]

print("Accidents which happened on a Sunday involving > 20 cars: {0}".format(

   len(accidents_sunday_twenty_cars)))

运行该脚本。 现在我们有10起事故：

Accidents

-----------

Accidents which happened on a Sunday: 693847

Accidents which happened on a Sunday involving > 20 cars: 10

让我们添加另一个条件 - 天气。

打开Road-Accident-Safety-Data-Guide-1979-2004.xls，并转到天气Sheet表。 你会看到代号2表示，“下雨没有强风”。

以下是我们的查询：

# Accidents which happened on a Sunday, > 20 cars, in the rain

accidents_sunday_twenty_cars_rain = data[

   (data.Day_of_Week == 1) & (data.Number_of_Vehicles > 20) &

   (data.Weather_Conditions == 2)]

print("Accidents which happened on a Sunday involving > 20 cars in the rain: {0}".format(

   len(accidents_sunday_twenty_cars_rain)))

因此，有四个事故发生在下雨的周日，涉及超过20辆汽车：

Accidents

-----------

Accidents which happened on a Sunday: 693847

Accidents which happened on a Sunday involving > 20 cars: 10

Accidents which happened on a Sunday involving > 20 cars in the rain: 4

我们可以根据需要使场景越来越复杂。但是现在，我们将停下来。因为我们的主要兴趣是看发生在伦敦的事故。

如果你再看一下Road-Accident-Safety-Data-Guide-1979-2004.xls文件，有一个叫Police Force的表。 代号1的意思为“警视厅”，这就是通常被称为苏格兰警署，也就负责伦敦大部分（但不是全部）的警察部队。 对于我们而言，这已足够用，我们可以提取像这样这个信息：

# Accidents in London on a Sunday

london_data = data[data['Police_Force'] == 1 & (data.Day_of_Week == 1)]

print("\nAccidents in London from 1979-2004 on a Sunday: {0}".format(

   len(london_data)))

运行该脚本，将创建了一个新的DataFrame表数据，包含着从1979至2004年星期天发生在“警视厅”的事故：

Accidents

-----------

Accidents which happened on a Sunday: 693847

Accidents which happened on a Sunday involving > 20 cars: 10

Accidents which happened on a Sunday involving > 20 cars in the rain: 4

 

Accidents in London from 1979-2004 on a Sunday: 114624

如果你想创建一个新的仅包含意外事故在2000年DataFrame表呢？

首先，我们需要通过pd.to_datetime() 函数，将日期格式转换为Python可以理解的格式。 这个函数可以将一个任何格式的日期数据，转换为使我们可以理解（YYYY-MM-DD）的格式。 然后，我们可以创建另一个仅包含2000年意外事故的DataFrame表：

# Convert date to Pandas date/time

london_data_2000 = london_data[

   (pd.to_datetime(london_data['Date'], coerce=True) >

       pd.to_datetime('2000-01-01', coerce=True)) &

   (pd.to_datetime(london_data['Date'], coerce=True) <

       pd.to_datetime('2000-12-31', coerce=True))

]

print("Accidents in London in the year 2000 on a Sunday: {0}".format(

len(london_data_2000)))

脚本执行后，你应该会看到：

Accidents which happened on a Sunday: 693847

Accidents which happened on a Sunday involving > 20 cars: 10

Accidents which happened on a Sunday involving > 20 cars in the rain: 4

 

Accidents in London from 1979-2004 on a Sunday: 114624

Accidents in London in the year 2000 on a Sunday: 3889

貌似这个结果有点混乱。通常情况下，如果要过滤一个数组数据，你只需要使用有限定条件for循环：

for data in array:

   if data > X and data < X:

       # do something

但是，你真的不应该定义自己的循环，因为许多高性能的库，例如Pandas，有对应的辅助函数。在这种情况下，上述代码遍历所有元件并过滤出的在数据集日期外的数据，然后，返回包含在日期范围内的数据。

干的不错！

转换数据

当公司的其他小伙伴深陷Excel无法自拔时，是不是有这样的机会，可以通过使用Pandas,将你生成的DataFrame表分享他们？

首先，我们需要做一些清理工作。还记得我们前面看到的字节顺序标记？ 当写这个数据到Excel文件时会产生问题-Pandas会抛出一个UnicodeDecodeError错误。为什么呢？ 因为文本的其余部分被解码为ASCII，但是十六进制值不能以ASCII表示。

我们可以将的一切编码为Unicode后写入，但要记住这个字节顺序标记是不必要的（对我们来说）。因此，我们通过重命名列标题来摆脱它：

london_data_2000.rename(

   columns={'\xef\xbb\xbfAccident_Index': 'Accident_Index'}, inplace=True)

这是通过Pandas进行重命名列的方式; 有点复杂，但是说实话， inplace = True是需要的，因为我们要修改现有的结构，并且不创建副本，这是Pandas的默认操作。

现在，我们可以将数据保存到Excel：

# Save to Excel

writer = pd.ExcelWriter(

   'London_Sundays_2000.xlsx', engine='xlsxwriter')

london_data_2000.to_excel(writer, 'Sheet1')

writer.save()

请确保安装XlsxWriter运行前：

pip install XlsxWriter==0.7.3

如果一切顺利，这应该已经创建了一个名为London_Sundays_2000.xlsx文件，然后保存我们的数据到工作表Sheet1。 打开此文件在Excel或LibreOffice中，并确认数据是正确的。

结论

那么，我们完成了哪些任务呢？ 我们通过Pandas处理了在Excel中无法打开大的文件─

1. 打开该文件。

2. 执行类SQL的查询方式来处理数据。

3. 创建了由原始数据的解析的子集数据的新XLSX文件。

请记住，即使该文件是近800MB，在大数据时代，它仍然是相当小的。 如果你想打开一个4GB的文件？ 即使你有8GB或更多的RAM，这可能仍然是不可能完成的。因为很多你的内存是被操作系统保和其他系统进程保留的。 事实上，当我首次读取800MB的文件时，我的笔记本电脑卡了几次。如果我打开一个4GB的文件，那对我的的笔记本电脑来说，不亚于一次心脏病发作。

那么我们该如何处理呢？

诀窍是不要一次性打的打开整个文件。这就是我们在接下来的博客文章需要了解的事情。

在此之前，分析自己的数据。

英文原文: https://realpython.com/blog/python/working-with-large-excel-files-in-pandas/

译者: LeoSun