微信号:programmer_club

介绍:程序员第一自媒体,与你探讨码农人生路上遇到的各类泛技术话题,定期为你推荐码农人生思考、感悟以及启迪!

通过 SQL 查询学习 Pandas 数据处理

2017-09-30 20:30 程序员之家

Pandas 是一款广泛使用的数据处理工具。结合 NumPy 和 Matplotlib 类库,我们可以在内存中进行高性能的数据清洗、转换、分析及可视化工作。虽然 Python 本身是一门非常容易学习的语言,但要熟练掌握 Pandas 丰富的 API 接口及正确的使用方式,还是需要投入一定时间的。

对于数据开发工程师或分析师而言,SQL 语言是标准的数据查询工具。本文提供了一系列的示例,如何将常见的 SQL 查询语句使用 Pandas 来实现。

Pandas 的安装和基本概念并不在本文讲述范围内,请读者到官网上阅读相关文档,或者阅读《利用 Python 进行数据分析》一书。我推荐大家使用 Anaconda Python 套件,其中集成了 Spyder 集成开发环境。在运行下文的代码之前,请先引入 Pandas 和 NumPy 包:

 
           
  1. import pandas as pd

  2. import numpy as np

FROM - 读取数据

首先,我们需要将数据加载到工作区(内存)。Pandas 原生支持非常多的数据格式,CSV 是较常见的一种。我们以航班延误时间数据集为例(下载地址):

 
           
  1. date,delay,distance,origin,destination

  2. 02221605,3,358,BUR,SMF

  3. 01022100,-5,239,HOU,DAL

  4. 03210808,6,288,BWI,ALB

我们可以使用 pd.read_csv 函数加载它:

 
           
  1. df = pd.read_csv('flights.csv', dtype={'date': str})

  2. df.head()

这条命令会将 flights.csv 文件读入内存,使用首行作为列名,并自动检测每一列的数据类型。其中,由于 date 一列的日期格式是 %m%d%H%M,自动转换成数字后会失去月份的前异零(02 月的 0),因此我们显式指定了该列的 dtype,告知 Pandas 保留原值。

df.head 用于查看数据集的前 N 行,功能类似于 LIMIT N。如果要实现 LIMIT 10, 100,可以使用 df.iloc[10:100]。此外,IPython 终端默认只显示 60 行数据,我们可以通过以下方法修改设置:

 
           
  1. pd.options.display.max_rows = 100

  2. df.iloc[10:100]

另外一种常见的数据源是关系型数据库,Pandas 也提供了内置支持:

 
           
  1. conn = pymysql.connect(host='localhost', user='root')

  2. df = pd.read_sql("""

  3. select `date`, `delay`, `distance`, `origin`, `destination`

  4. from flights limit 1000

  5. """, conn)

如果要将 DataFrame 保存到文件或数据库中去,可以分别使用 pd.to_csv 和 pd.to_sql 函数。

SELECT - 选择列

SELECT 语句在 SQL 中用于选择需要的列,并对数据做清洗和转换。

 
           
  1. df['date']

  2. df[['date', 'delay']]

  3. df.loc[10:100, ['date', 'delay']]

SQL 提供了诸多函数,大部分都可以用 Pandas 来实现,而且我们也很容易用 Python 编写自定义函数。下面我将列举一些常用的函数。

字符串函数

Pandas 的字符串函数可以通过 DateFrame 和 Series 的 str 属性来调用,如 df['origin'].str.lower()

 
           
  1. df['origin'].str.cat(df['destination'], sep=' to ')

  2. df['origin'].str.strip()

  3. df['origin'].str.len()

  4. df['origin'].str.replace('a', 'b')

  5. df['origin'].str[0:1]

  6. df['domain'].str.split('.').str[:2].str.join('.')

  7. df['domain'].str.extract(r'^([^.]+.[^.]+)')

Pandas 有一个名为广播的特性(broadcast),简单来说就是能够将低维数据(包括单个标量)和高维数据进行结合和处理。例如:

 
           
  1. df['full_date'] = '2001' + df['date']

  2. df['delay'] / 60

  3. df['delay'].div(60)

Pandas 还内置了很多字符串函数,它们的用法和 SQL 有一定区别,但功能更强。完整列表可以参考文档 Working with Text Data。

日期函数

pd.to_datetime 用于将各种日期字符串转换成标准的 datetime64 类型。日期类型的 Series 都会有一个 dt 属性,从中可以获取到有关日期时间的信息,具体请参考文档 Time Series / Date functionality。

 
           
  1. df['datetime'] = pd.to_datetime(df['full_date'], format='%Y%m%d%H%M%S')

  2. df['datetime'].dt.strftime('%Y-%m-%d')

  3. df['datetime'].dt.month

  4. df['datetime'].dt.hour

  5. df['datetime'].view('int64') // pd.Timedelta(1, unit='s').value

  6. pd.to_datetime(df['timestamp'], unit='s')

  7. df['datetime'] + pd.Timedelta(1, unit='D')

WHERE - 选择行

在 Pandas 中使用逻辑表达式后,会返回一个布尔型的 Series,通过它可以对数据集进行过滤:

 
           
  1. (df['delay'] > 0).head()

  2. df[df['delay'] > 0]

我们可以用位运算符来组合多个查询条件:

 
           
  1. df[(df['delay'] > 0) & (df['distance'] <= 500)]

  2. df[(df['delay'] > 0) | (df['origin'] == 'BUR')]

  3. df[~(df['delay'] > 0)]

对于 IS NULL 和 IS NOT NULL,也提供了相应的内置函数:

 
           
  1. df[df['delay'].isnull()]

  2. df[df['delay'].notnull()]

此外,Pandas 还提供了 df.query 方法,可以使用字符串表达式来编写过滤条件:

 
           
  1. df.query('delay > 0 and distaince <= 500')

  2. df.query('(delay > 0) | (origin == "BUR")')

其实,Pandas 提供了功能强大的数据选取工具,很多是无法用 SQL 表达出来的,建议详细阅读 Indexing and Selecting Data 文档,其中包含了丰富的示例。

GROUP BY - 汇总

 
           
  1. df.groupby('origin').size()

聚合运算包含了两个部分,一是分组字段,二是聚合函数。我们可以传递多个分组字段给 df.groupby,也能够指定多个聚合函数:

 
           
  1. df.groupby(['origin', 'destination']).agg({

  2.    'delay': np.sum,

  3.    'distance': np.mean

  4. })

  5. df.groupby('origin')['delay'].agg(['min', 'max'])

我们还可以将函数的运行结果作为分组条件。更多示例请见 Group By: split-apply-combine。

 
           
  1. df.set_index('origin').groupby(len).size()

ORDER BY - 排序

Pandas 中有两类排序,按索引和按数值。如果不了解 Pandas 的索引,还请自行查阅相关教程。

 
           
  1. df.set_index('origin').sort_index()

  2. df.sort_values(by='origin')

  3. df.sort_values(by=['origin', 'destination'], ascending=[True, False])

JOIN - 关联查询

 
           
  1. pd.merge(df_product, df_category, left_on='cid', right_on='id', how='left')

如果联合查询的键是同名的,可以直接使用 on=['k1', 'k2']。默认的关联方式是 INNER JOIN( how='inner'),其它还有左外连接( left)、右外连接( right)、以及 FULL OUTER JOIN( outer)。

pd.concat 可用于实现 UNION 查询。 更多关联查询的示例请参考 Merge, join, and concatenate。

 
           
  1. pd.concat([df_a, df_b]).drop_duplicates()

最后,我们经常会需要在分组中按某种规则排序,并获得前几位的记录。MySQL 中需要通过变量来实现,Pandas 中则可以使用 rank 函数:

 
           
  1. rnk = df.groupby('origin')['delay'].rank(method='first', ascending=False)

  2. df.assign(rnk=rnk).query('rnk <= 3').sort_values(['origin', 'rnk'])

作者:张吉

来源:http://shzhangji.com/cnblogs/2017/07/23/learn-pandas-from-a-sql-perspective/


编辑 | 码哥

图片源于网络,版权归原作者所有

 
程序员之家 更多文章 马云又双叒叕搞事情啦!淘宝怎么活! 性奴、割器官,每年至少250万人旅游失踪?他们都去了哪? 程序员爱吃树莓派?好吃么? 6年来微信首次换图,换中国的! 顶级程序员有一个共同点,那就是......
猜您喜欢 极客帮创投蒋涛:人机围棋大战之后的人工智能走向何方(PPT详解版) 【独家】谷歌翻译真那么神?真人约会体验给你答案 提高开发生产力的利器:宜信开源基于Docker的PaaS平台LAIN 解读Android官方MVP项目单元测试 《第三方网络广告平台陷入窘境》