与常用SQL语句对应的pandas函数或方法有哪些？

在python中pandas工具包具有基本的数据管理功能，自然就有一些查询pandas中数据结构dataframe的常用函数。下面就看看，与常用的SQL语句对应的pandas函数有哪些？

数据准备

数据从SQL Server数据库中获取，使用符合SQL语法规则的语句查询并获取。在python中如何连接数据库的方法，请点击阅读 这篇文章 。

• 与数据库建立连接

import pyodbcimport pandas as pdimport numpy as npconnection_string = ('Driver={SQL Server Native Client 11.0};' 'Server=Your_Server_Name;' 'Database=My_Database_Name;' 'UID=Your_User_ID;' 'PWD=Your_Password;')connection = pyodbc.connect(connection_string)

• 获取指定条件的数据，例如读取My_Database_Name数据库中State_Population 数据表的前10条记录，

population = pd.read_sql('SELECT TOP(10) * FROM State_Population', connection)

或者写成：

query = 'SELECT * FROM STATE_AREAS WHERE [area (sq. mi)] > 100000'area = pd.read_sql(query, connection)

以上方法中，pd.read_sql 函数的功能与 SQL语句'Select * from ....'相对应，并且以dataframe格式查询输出的结果area， 由此即可在python用pandas函数对area进行更多的操作。

SQL查询语句一般包括以下组成部分：

• SELECT column_name(s)
• FROM table_name
• WHERE condition
• GROUP BY column_name(s)
• HAVING condition
• ORDER BY column_name(s)

各组成部分在pandas中都有对应的函数。从My_Database_Name 数据库中的三个数据表，读取其中两个数据表（State_Population和STATE_AREAS）的部分内容，都是dataframe格式，命名为：population 和 area。下文中将使用population，演示pandas中与SQL查询有相同功能的函数。先看看population包括的数据内容：

population.head()

输出结果如下图：

（一）在pandas中与 SELECT * FROM State_Population;相对应的方法（1-4）

（1） 在python中可读取 dataframe

population

结果如下图：

（2）SELECT year FROM State_Population; 对应的pandas函数：pd.DataFrame(population.year)

pd.DataFrame(population.year)

输出结果如下图：

（3）SELECT population, year FROM State_Population; 对应的pandas方法：population[['population', 'year']]

population[['population', 'year']]

输出结果：

（4）pandas.loc方法

该方法一般格式为：df.loc([row names], [column names])；获取给定列的所有行数据，可以用“：”替代 [row names] ，如：df.loc(: , [column names])。

population.loc[:,['population', 'year']]

输出结果与方法（3）相同：

（二）在python中与SELECT * FROM State_Population WHERE year = 2010;语句相对应的方法（5--8）。

（5）可使用切片方法（slicing）

population[population.year == 2010]

输出结果如下图：

（6） pandas.loc()函数

population.loc[population.year == 2010,:]

输出结果与（5）相同，略。

（7）pandas.query()函数

population.query('year == 2010')

输出结果与（5）相同，略。

（8）lambda函数

population[population.apply(lambda x: x['year'] == 2010, axis=1)]

输出结果与（5）相同，略。

（三）在python中，与SELECT state/region, population, year FROM State_Population WHERE year = 2010 or 2012 and ages = under18; 相对应的方法（9--12）。

（9）索引（indexing）和切片方法（slicing）

population[(population.year.isin([2010, 2012])) & (population.ages == 'under18')][['state/region', 'population', 'year']]

输出结果如下图：

（10）pandas.loc()方法

population.loc[(population.year.isin([2010, 2012])) & (population.ages == 'under18'),['state/region', 'population', 'year']]

输出结果与（9）相同，略。

（11）pandas.query()方法

population.query('(year==2010 | year==2012) & ages == 'under18'')[['state/region', 'population', 'year']]

输出结果与（9）相同，略。

（12）使用lambda函数

population[population.apply(lambda x: (x['year'] in [2010, 2012]) & (x['ages'] == 'under18'), axis=1)]

输出结果与（9）相同，略。

（四）在python中，与SELECT * FROM State_Population WHERE ages = total GROUP BY state/region HAVING AVG(population) > 10000000; 语句相对应的方法。

population.groupby(by = 'state/region')

输出结果如下图：

population.groupby(by = ['state/region', 'year'])

输出结果如下图：

population.groupby(by = ['state/region', 'year']).count()

输出结果如下图：

还可以把分组结果赋给一个变量：grouped

grouped = population.groupby(by = ['state/region', 'year'])

（13）pandas中的groupby和aggregate方法

df = pd.DataFrame(population.loc[population.ages == 'total', :].groupby(by = 'state/region').aggregate('population').mean())df.loc[df.population > 10000000, :]

输出结果如下图：

（14）python中用sort_values()排序

python中与SELECT * FROM State_Population WHERE ages = total GROUP BY state/region HAVING AVG(population) > 10000000 ORDER BY population; 对应的方法

df = pd.DataFrame(population.loc[population.ages == 'total', :].groupby(by = 'state/region').aggregate('population').mean())df.loc[df.population > 10000000, :].sort_values(by = 'population')

输出结果如下图：

使用 “ascending = False” 参数的结果：

（15）pandas提供的多列排序

grouped = population.groupby(by = ['state/region', 'year']).mean()grouped

输出结果如下图：

对分类后的数据表进行排序，

grouped.sort_values(by = ['year', 'population'], ascending=[True, False])