24、数据处理与时间序列分析：Pandas的强大应用

数据处理与时间序列分析：Pandas的强大应用

1. 连接SQL数据库

在数据分析师的职业生涯中，学习SQL是必不可少的技能。世界上大部分数据都存储在支持SQL语句的数据库中，其中SQLite是最受欢迎且易于使用的关系型数据库管理系统之一。

准备工作

我们将探索SQLite提供的Chinook示例数据库，该数据库包含一个音乐商店的11个数据表。在深入研究关系型数据库时，研究数据库图（有时称为实体关系图）有助于更好地理解表之间的关系。

为了完成后续操作，需要安装 sqlalchemy Python包。如果你安装了Anaconda发行版，该包应该已经可用。SQLAlchemy是pandas连接数据库的首选工具。

操作步骤

1. 设置SQLAlchemy引擎 ：

from sqlalchemy import create_engine
engine = create_engine('sqlite:///data/chinook.db')

2. 读取数据表 ：使用 read_sql_table 函数读取 tracks 表。

import pandas as pd
tracks = pd.read_sql_table('tracks', engine)
tracks.head()

3. 查询每个流派的平均歌曲长度 ：

genre_track = genres.merge(tracks[['GenreId', 'Milliseconds']],
                           on='GenreId', how='left') \
                    .drop('GenreId', axis='columns')
genre_track.head()

4. 计算并转换时间格式 ：

genre_time = genre_track.groupby('Name')['Milliseconds'].mean()
pd.to_timedelta(genre_time, unit='ms').dt.floor('s').sort_values()

5. 查询每个客户的总消费金额 ：

cust = pd.read_sql_table('customers', engine,
                         columns=['CustomerId', 'FirstName', 'LastName'])
invoice = pd.read_sql_table('invoices', engine,
                            columns=['InvoiceId', 'CustomerId'])
ii = pd.read_sql_table('invoice_items', engine,
                       columns=['InvoiceId', 'UnitPrice', 'Quantity'])
cust_inv = cust.merge(invoice, on='CustomerId').merge(ii, on='InvoiceId')
cust_inv.head()

6. 计算总消费金额并排序 ：

total = cust_inv['Quantity'] * cust_inv['UnitPrice']
cols = ['CustomerId', 'FirstName', 'LastName']
cust_inv.assign(Total=total).groupby(cols)['Total'].sum().sort_values(ascending=False).head()

原理说明

• create_engine 函数需要一个连接字符串才能正常工作。SQLite的连接字符串很简单，只需指定数据库的位置。其他关系型数据库管理系统的连接字符串更复杂，需要提供用户名、密码、主机名、端口等信息。
• 使用 read_sql_table 函数可以轻松地将整个表选择到DataFrame中。每个表都有一个主键，用于唯一标识每一行。
• 在合并表时，可以根据需要选择保留或删除某些列。使用 groupby 操作可以对数据进行分组和聚合。

更多操作

如果你熟悉SQL，可以编写SQL查询字符串并将其传递给 read_sql_query 函数。例如：

sql_string1 = '''
    select
        Name,
        time(avg(Milliseconds) / 1000, 'unixepoch') as avg_time
    from (
            select
                g.Name,
                t.Milliseconds
            from
                genres as g
            join
                tracks as t
                on
                    g.genreid == t.genreid
         )
    group by
        Name
    order by
         avg_time
'''
pd.read_sql_query(sql_string1, engine)

sql_string2 = '''
    select
          c.customerid,
          c.FirstName,
          c.LastName,
          sum(ii.quantity * ii.unitprice) as Total
    from
         customers as c
    join
         invoices as i
              on c.customerid = i.customerid
    join
        invoice_items as ii
              on i.invoiceid = ii.invoiceid
    group by
        c.customerid, c.FirstName, c.LastName
    order by
        Total desc
'''
pd.read_sql_query(sql_string2, engine)

2. 理解Python和pandas日期工具的区别

在处理时间序列数据之前，了解Python和pandas的日期和时间功能是很有帮助的。

Python的日期和时间功能

Python的 datetime 模块提供了三种不同的数据类型： date 、 time 和 datetime 。
- date 表示一个时刻，仅包含年、月、日。
- time 表示时间，包含小时、分钟、秒和微秒，与日期无关。
- datetime 包含日期和时间的元素。

pandas的日期和时间功能

pandas有一个单一的对象 Timestamp 来封装日期和时间，具有纳秒级的精度，派生自NumPy的 datetime64 数据类型。此外，pandas和Python都有 timedelta 对象，用于日期的加减运算。

操作步骤

1. 创建Python的日期、时间和datetime对象 ：

import datetime
date = datetime.date(year=2013, month=6, day=7)
time = datetime.time(hour=12, minute=30, second=19, microsecond=463198)
dt = datetime.datetime(year=2013, month=6, day=7, hour=12, minute=30, second=19, microsecond=463198)
print("date is ", date)
print("time is", time)
print("datetime is", dt)

2. 创建Python的timedelta对象 ：

td = datetime.timedelta(weeks=2, days=5, hours=10, minutes=20, seconds=6.73, milliseconds=99, microseconds=8)
print(td)

3. 进行日期和时间的加减运算 ：

print('new date is', date + td)
print('new datetime is', dt + td)

4. 尝试对时间对象进行加减运算（会报错） ：

time + td

5. 创建pandas的Timestamp对象 ：

import pandas as pd
pd.Timestamp(year=2012, month=12, day=21, hour=5, minute=10, second=8, microsecond=99)
pd.Timestamp('2016/1/10')
pd.Timestamp('2014-5/10')
pd.Timestamp('Jan 3, 2019 20:45.56')
pd.Timestamp('2016-01-05T05:34:43.123456789')

6. 使用单个整数或浮点数创建Timestamp对象 ：

pd.Timestamp(500)
pd.Timestamp(5000, unit='D')

7. 使用 to_datetime 函数 ：

pd.to_datetime('2015-5-13')
pd.to_datetime('2015-13-5', dayfirst=True)
pd.to_datetime('Start Date: Sep 30, 2017 Start Time: 1:30 pm',
               format='Start Date: %b %d, %Y Start Time: %I:%M %p')
pd.to_datetime(100, unit='D', origin='2013-1-1')

8. 将列表或Series转换为Timestamps ：

s = pd.Series([10, 100, 1000, 10000])
pd.to_datetime(s, unit='D')
s = pd.Series(['12-5-2015', '14-1-2013', '20/12/2017', '40/23/2017'])
pd.to_datetime(s, dayfirst=True, errors='coerce')
pd.to_datetime(['Aug 3 1999 3:45:56', '10/31/2017'])

9. 创建pandas的Timedelta对象 ：

pd.Timedelta('12 days 5 hours 3 minutes 123456789 nanoseconds')
pd.Timedelta(days=5, minutes=7.34)
pd.Timedelta(100, unit='W')
pd.to_timedelta('67:15:45.454')
s = pd.Series([10, 100])
pd.to_timedelta(s, unit='s')
time_strings = ['2 days 24 minutes 89.67 seconds', '00:45:23.6']
pd.to_timedelta(time_strings)

10. 进行Timedelta的运算 ：

pd.Timedelta('12 days 5 hours 3 minutes') * 2
pd.Timestamp('1/1/2017') + pd.Timedelta('12 days 5 hours 3 minutes') * 2
td1 = pd.to_timedelta([10, 100], unit='s')
td2 = pd.to_timedelta(['3 hours', '4 hours'])
td1 + td2
pd.Timedelta('12 days') / pd.Timedelta('3 days')

11. 使用Timestamp和Timedelta的属性和方法 ：

ts = pd.Timestamp('2016-10-1 4:23:23.9')
ts.ceil('h')
ts.year, ts.month, ts.day, ts.hour, ts.minute, ts.second
ts.dayofweek, ts.dayofyear, ts.daysinmonth
ts.to_pydatetime()
td = pd.Timedelta(125.8723, unit='h')
td
td.round('min')
td.components
td.total_seconds()

原理说明

• datetime 模块是Python标准库的一部分，简单易用，共有六种对象类型。
• pandas的 Timestamp 和 Timedelta 对象具有 datetime 模块对应对象的所有功能，并且功能更强大。
• to_datetime 和 to_timedelta 函数可以将整数或字符串序列转换为所需类型，并且提供了更多参数，如 errors 和 format 。

性能优化

使用 format 参数可以显著提高将大量字符串转换为Timestamps的性能。例如：

date_string_list = ['Sep 30 1984'] * 10000
%timeit pd.to_datetime(date_string_list, format='%b %d %Y')
%timeit pd.to_datetime(date_string_list)

3. 智能切片时间序列

当DataFrame具有 DatetimeIndex 时，选择和切片数据会有更多的机会。

准备工作

使用 hdf5 文件 crimes.h5 中的丹佛犯罪数据集。 hdf5 文件格式可以高效存储大型科学数据，与CSV文本文件不同。

操作步骤

1. 读取数据集并查看数据类型 ：

crime = pd.read_hdf('data/crime.h5', 'crime')
crime.dtypes

2. 设置索引 ：将 REPORTED_DATE 列设置为索引，以便进行智能的Timestamp切片。

crime = crime.set_index('REPORTED_DATE')
crime.head()

3. 选择单个索引值的所有行 ：

crime.loc['2016-05-12 16:45:00']

4. 部分匹配索引值选择所有行 ：

crime.loc['2016-05-12']

5. 选择整个月、年或小时的所有行 ：

crime.loc['2016-05'].shape
crime.loc['2016'].shape
crime.loc['2016-05-12 03'].shape

6. 使用月份名称进行选择 ：

crime.loc['Dec 2015'].sort_index()

7. 使用包含月份名称的其他字符串模式进行选择 ：

crime.loc['2016 Sep, 15'].shape
crime.loc['21st October 2014 05'].shape

8. 使用切片符号选择精确的数据范围 ：

crime.loc['2015-3-4':'2016-1-1'].sort_index()

通过以上操作，我们可以看到pandas在连接数据库、处理日期和时间以及切片时间序列数据方面的强大功能。这些功能可以帮助我们更高效地进行数据处理和分析。

数据处理与时间序列分析：Pandas的强大应用（下半部分）

4. 时间序列分析的实际应用示例

为了更深入地理解时间序列分析，下面通过几个实际的应用示例来展示如何使用Pandas进行相关操作。

4.1 统计每周犯罪数量

我们可以利用前面提到的丹佛犯罪数据集，统计每周的犯罪数量。

# 统计每周犯罪数量
weekly_crime_count = crime['IS_CRIME'].resample('W').sum()
print(weekly_crime_count)

在这个示例中， resample('W') 函数将数据按周进行重采样， sum() 函数计算每周的犯罪总数。

4.2 分别聚合每周犯罪和交通事故数据

我们可以分别统计每周的犯罪和交通事故数量。

# 分别聚合每周犯罪和交通事故数据
weekly_crime = crime['IS_CRIME'].resample('W').sum()
weekly_traffic = crime['IS_TRAFFIC'].resample('W').sum()

# 创建一个新的DataFrame来存储结果
weekly_stats = pd.DataFrame({
    'Crime': weekly_crime,
    'Traffic': weekly_traffic
})

print(weekly_stats)

在这个示例中，我们分别对 IS_CRIME 和 IS_TRAFFIC 列进行按周重采样并求和，然后将结果存储在一个新的DataFrame中。

4.3 按工作日和年份统计犯罪数量

我们可以按工作日和年份对犯罪数据进行分组统计。

# 按工作日和年份统计犯罪数量
crime_by_weekday_year = crime.groupby([crime.index.weekday, crime.index.year])['IS_CRIME'].sum()
print(crime_by_weekday_year)

在这个示例中， crime.index.weekday 和 crime.index.year 分别获取日期的工作日和年份信息，然后使用 groupby 函数进行分组，最后对 IS_CRIME 列求和。

5. 时间序列分析的高级技巧

除了前面介绍的基本操作，Pandas还提供了一些高级技巧，帮助我们更灵活地处理时间序列数据。

5.1 使用匿名函数进行分组

我们可以使用匿名函数对时间序列数据进行分组。

# 使用匿名函数进行分组
crime.groupby(lambda x: x.month)['IS_CRIME'].sum()

在这个示例中，我们使用匿名函数 lambda x: x.month 按月份对数据进行分组，然后对 IS_CRIME 列求和。

5.2 按时间戳和其他列进行分组

我们可以同时按时间戳和其他列对数据进行分组。

# 按时间戳和其他列进行分组
crime.groupby([crime.index, 'NEIGHBORHOOD_ID'])['IS_CRIME'].sum()

在这个示例中，我们同时按时间戳和 NEIGHBORHOOD_ID 列对数据进行分组，然后对 IS_CRIME 列求和。

5.3 使用 merge_asof 查找犯罪率下降20%的最后时间

merge_asof 函数可以用于在时间序列数据中查找最接近的匹配项。我们可以使用它来查找犯罪率下降20%的最后时间。

# 创建一个示例DataFrame
crime_rate = crime['IS_CRIME'].resample('M').mean()
crime_rate_pct_change = crime_rate.pct_change()

# 查找犯罪率下降20%的最后时间
threshold = -0.2
last_time = crime_rate_pct_change[crime_rate_pct_change <= threshold].index[-1]
print(last_time)

在这个示例中，我们首先计算每月的犯罪率，然后计算犯罪率的百分比变化。接着，我们找到犯罪率下降20%的所有时间点，并选择最后一个时间点。

6. 总结

通过本文的介绍，我们了解了Pandas在连接SQL数据库、处理日期和时间以及切片时间序列数据方面的强大功能。以下是一些关键要点总结：

功能	要点
连接SQL数据库	使用 sqlalchemy 和 pandas 的 read_sql_table 函数可以轻松连接和读取数据库中的数据。
处理日期和时间	Python的 datetime 模块提供了基本的日期和时间功能，而Pandas的 Timestamp 和 Timedelta 对象功能更强大，且 to_datetime 和 to_timedelta 函数可以处理多种输入格式。
切片时间序列	当DataFrame具有 DatetimeIndex 时，可以使用部分匹配和切片符号进行智能的时间序列选择。
时间序列分析	通过重采样、分组和聚合等操作，可以对时间序列数据进行统计和分析。

通过掌握这些技巧，我们可以更高效地进行数据处理和分析，为进一步的决策提供有力支持。

下面是整个时间序列分析的流程图：

graph TD;
    A[准备数据] --> B[连接数据库读取数据];
    B --> C[设置日期索引];
    C --> D[智能切片时间序列];
    D --> E[统计每周犯罪数量];
    D --> F[分别聚合每周犯罪和交通事故数据];
    D --> G[按工作日和年份统计犯罪数量];
    D --> H[使用匿名函数进行分组];
    D --> I[按时间戳和其他列进行分组];
    D --> J[使用merge_asof查找犯罪率下降20%的最后时间];

希望本文能够帮助你更好地理解和应用Pandas进行时间序列分析。在实际应用中，你可以根据具体需求选择合适的方法和技巧，以达到最佳的分析效果。