数据, 术→技巧

Pandas基础教程之替换SQL

钱魏Way · · 177 次浏览
!文章内容如有错误或排版问题，请提交反馈，非常感谢！

对于很多数据分析的同学来说，最熟悉的莫过于SQL（相关SQL教程见产品&运营分析技能培训：HiveSQL），针对数据处理问题，脑海里的第一反应也往往都是SQL，而在日常的工作中往往也需要在Pandas的DataFrame数据上处理和分析数据，今天就一起来学习Pandas。

使用Pandas代替SQL

选择列

## select COL1, COL2 from TABLE;
# df.loc[:, ["COL1", "COL2"]]
df[["COL1", "COL2"]]

pandas中loc与iloc区别

在Pandas中，iloc和loc是两种不同的索引方式，用于访问和操作DataFrame中的数据。以下是它们的主要区别：

iloc（整数位置索引）：

  • iloc使用整数位置来进行索引，类似于传统的Python列表和NumPy数组的索引方式。
  • 通过整数位置索引，你可以使用行和列的整数位置来访问数据。
  • 语法：iloc[row_index, col_index]，其中row_index和col_index可以是单个整数、整数切片、整数列表或布尔数组。
import pandas as pd

data = {'A': [1, 2, 3], 'B': [4, 5, 6], 'C': [7, 8, 9]}
df = pd.DataFrame(data)

# 使用iloc获取第一行第二列的值
value = df.iloc[0, 1]
print(value)

loc（标签索引）：

  • loc使用标签来进行索引，使用行和列的标签或布尔数组。
  • loc适用于基于标签的索引，例如行或列的名称。
  • 语法：loc[row_label, col_label]，其中row_label和col_label可以是单个标签、标签列表、标签切片或布尔数组。
import pandas as pd

data = {'A': [1, 2, 3], 'B': [4, 5, 6], 'C': [7, 8, 9]}
df = pd.DataFrame(data, index=['row1', 'row2', 'row3'])

# 使用loc获取'row1'行'B'列的值
value = df.loc['row1', 'B']
print(value)

总体来说，iloc适用于整数位置索引，而loc适用于标签索引。选择使用哪个索引方式取决于你的需求以及DataFrame的索引类型（整数位置或标签）。

列别名

# SQL
select
COL1 as COL1_renamed,
COL2 as COL2_renamed
from TABLE;

# Pandas
df.rename(columns={"COL1": "COL1_renamed", "COL2": "COL2_renamed"})
# df.columns = ["COL1_renamed", "COL2_renamed"]

列追加

# select *, COL2 * 2 as NEW_COL from TABLE;
df["NEW_COL"] = df["COL2"] * 2

条件过滤

# SQL
select COL1, COL2 from TABLE where COL1 = 'hoge' and COL2 > 100;

# Pandas
df[(df["COL1"] == "hoge") & (df["COL2"] > 100)]

条件过滤详解

## select * from TABLE where COL1 = 'hoge';
df[df["COL1"] == "hoge"]

## sql select * from TABLE where COL1 != 'hoge';
df[df["COL1"] != "hoge"]

## select * from TABLE where COL2 not in (1, 2, 3);
df[~(df["COL1"].isin(1, 2, 3))]

## select * from TABLE where COL2 is null;
df[df["COL2"].isnull()]

## select * from TABLE where COL2 >= 1000 and COL2<= 2000;
df[(df["COL2"] >= 1000) & (df["COL2"] <= 2000)]

## sql select * from TABLE where NAME_COL like '标点符%';
df[df["NAME_COL"].str.startswith('标点符')]

## select * from TABLE where NAME_COL like '%标点符%';
df[df["NAME_COL"].str.contains('标点符', regex=True)]

# select * from TABLE where NAME_COL like '%标点符';
df[df["NAME_COL"].str.endswith('标点符')]

## select * from TABLE where NAME_COL like '标_符';
df[df["NAME_COL"].str.contains('标.符', regex=True)]

## SELECT column1, column2 FROM table WHERE column1 = 'apple' OR column2 >= 50;
df[(df['column1'] == 'apple') | (df['column2'] >= 50)]

排序

# select * from TABLE order by COL desc;
df["COL"].sort_values(ascending=False)

# select * from TABLE order by COL asc;
df.sort_values("COL")

# select * from TABLE order by COL1 desc, COL2 asc;
df.sort_values("COL1", ascending=False).sort_values("COL2")
# 如果两个排序字段的顺序相同使用 df.sort_values(["COL1", "COL2"])即可。

Limit限制

## sql select * from TABLE limit 10;
df.head(10)

## select * from TABLE limit 0, 10;
df.iloc[0:10, :]

## select * from TABLE limit 10, 10;
df.iloc[10:20, :]

聚合函数

## select sum(COL1) from TABLE;
data["COL1"].sum()

## select avg(COL1) from TABLE;
data["COL1"].mean()

## select min(COL1) from TABLE;
data["COL1"].min()

## select max(COL1) from TABLE;
data["COL1"].max()


## select count(COL1) from TABLE
len(df)

## select count(distinct COL1) from TABLE
len(df["COL1"].unique())

## select COL1, count(*) from TABLE group by COL1;
df.groupby("COL1").count()

Pandas中count()与size()的区别

  • size()返回DataFrame或Series中的总元素数，包括缺失值（NaN）。
  • count()返回非空（非缺失值）元素的数量。

在处理数据时，通常使用count来统计有效值的数量，因为它忽略了缺失值，提供了更有意义的信息。

HAVING子句

#SQL
select COL1, count(*)
from TABLE
where ...
group by COL1
having count(*) > 10;

##Pandas
df_grouped = df.groupby("COL1").count()
df_grouped[df_grouped["COL2"] > 10]

字段处理

#字符串拼接
## select concat(COL1, '_', COL2) from TABLE;
df["NEW_COL"] = [f"{COL1}_{COL2}" for COL1, COL2 in zip(df["COL1"], df["COL2"])]

表关联

## select T1.COL1, T1.COL2, T2.COL3 from TABLE1 as T1 inner join TABLE2 as T2 on T1.COL4 = T2.COL5;
df1.merge(df2, how="inner", left_on="lkey1", right_on="rkey1")

how：连接方式，可选值{‘left’, ‘right’, ‘outer’, ‘inner’, ‘cross’}，默认‘inner’

  • left：类似SQL的left join
  • right：类似SQL的right join
  • outer：类似SQL的full join（全连接）
  • inner：类似SQL的inner join
  • cross：类似SQL的cross join(笛卡尔乘积)

如果需要ON多个字段，可以采取的方式是：

result = pd.merge(df1, df2, left_on=['key1', 'key2'], right_on=['other_key1', 'other_key2'], how='inner')

UNION与UNION ALL

##SQL
select COL1, COL2
from TABLE
union
select COL1, COL2
from TABLE

##Pandas
pd.concat([df1[["COL1", "COL2"]], df[["COL1", "COL2"]]]).drop_duplicates()

##SQL
select COL1, COL2
from TABLE
union all
select COL1, COL2
from TABLE

##Pandas
pd.concat([df1[["COL1", "COL2"]], df[["COL1", "COL2"]]])

条件语句

##SQL
SELECT
CASE
WHEN col6 >= 50 THEN 1 ELSE 0
END AS is_50over
FROM sample

##Pandas
sample['is_50over'] = sample['col6'].map(lambda x: 1 if x >= 50 else 0)

##SQL复杂条件语句
SELECT
CASE
WHEN col6< 100 THEN 1
WHEN col6 >= 100 AND col6< 200 THEN 2
WHEN col6 >= 200 AND col6< 300 THEN 3
ELSE 4
END AS cate
FROM sample

##Pandas
def cate(x):
if x< 100:
return 1
elif x >= 100 and x< 200:
return 2
elif x >= 200 and x< 300:
return 3
else:
return 4

sample['cate'] = sample['col6'].map(cate)

UPDATE

## update tabale set col1 = 0 where col2 == 1
df.loc[df['col2'] == '1', 'col1'] = 0

DELETE

## delete from tabel where type = 'MISC'
df = df[df != 'MISC']
df.drop(df[df == 'MISC'].index)

窗口函数

##SQL
select *
, SUM(Total)
OVER(PARTITION BY CustomerId ORDER BY Total desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
from main.invoices

#Pandas
df_invoices['rt_by_customer'] = df_invoices.sort_values(by=['Total'], ascending=False).groupby('CustomerId')['Total'].cumsum()
df_invoices.sort_values(by=['CustomerId', 'Total'], ascending=[True, False])

##SQL
SELECT *
, LAG(InvoiceId) over(PARTITION BY CustomerId ORDER BY InvoiceDate asc) last_invoice
, LAG(InvoiceDate) over(PARTITION BY CustomerId ORDER BY InvoiceDate asc) last_invoice_date
, LEAD(InvoiceId) over(PARTITION BY CustomerId ORDER BY InvoiceDate asc) next_invoice
, LEAD(InvoiceDate) over(PARTITION BY CustomerId ORDER BY InvoiceDate asc) next_invoice_date
FROM main.invoices
ORDER BY CustomerId ASC;

##Pandas
df_invoices['last_invoice'] = df_invoices.sort_values(by=['InvoiceDate'], ascending=True).groupby('CustomerId')['InvoiceId'].shift(1)
df_invoices['last_invoice_date'] = df_invoices.sort_values(by=['InvoiceDate'], ascending=True).groupby('CustomerId')['InvoiceDate'].shift(1)
df_invoices['next_invoice'] = df_invoices.sort_values(by=['InvoiceDate'], ascending=True).groupby('CustomerId')['InvoiceId'].shift(-1)
df_invoices['next_invoice_date'] = df_invoices.sort_values(by=['InvoiceDate'], ascending=True).groupby('CustomerId')['InvoiceDate'].shift(-1)
df_invoices.sort_values(by=['CustomerId', 'InvoiceDate'], ascending=True)

##SQL
select *, row_number() over(Partition By InvoiceId Order By TrackId asc) rownumber from main.invoice_items

##Pandas
df_invoicesitem['rownumber'] = df_invoicesitem.sort_values(['TrackId'], ascending=True).groupby(['InvoiceId']).cumcount()+1

##SQL
SELECT *, RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS salary_rank FROM employees

##Pandas
employees["salary_rank"] = employees.groupby("department")["salary"].rank(ascending=False)

使用PandasSQL包

pandasql是一个Python包，它允许用户使用SQL语句直接在pandas DataFrame上执行查询。这个包是特别适合那些熟悉SQL但对pandas的查询语法不太熟悉的用户。它基于SQLite数据库引擎，使得用户可以在DataFrame上应用SQL语法进行数据操作。

特点

  • SQL语法: 用户可以使用标准的SQL语法来查询pandas DataFrame，这对于熟悉SQL但不熟悉pandas的人来说非常方便。
  • 集成: 它能够与pandas库无缝集成，允许在DataFrame上直接执行SQL查询。
  • 灵活性: 提供了使用SQL语言处理数据的灵活性，特别是在进行复杂的数据过滤、排序和聚合时。
  • 易用性: 对于那些习惯于数据库查询的用户，它提供了一种更直观、更熟悉的数据查询方式。

使用方法

要使用pandasql，首先需要安装这个包，通常可以通过pip安装：pip install pandasql

安装后，可以在Python代码中导入并使用它。以下是一个简单的示例，展示如何使用pandasql查询DataFrame：

import pandas as pd
import pandasql as ps

# 创建一个简单的DataFrame
df = pd.DataFrame({
'name': ['Alice', 'Bob', 'Charlie'],
'age': [25, 30, 35]
})

# 使用pandasql进行查询
query = "SELECT * FROM df WHERE age > 30"
result = ps.sqldf(query, locals())

print(result)

在这个示例中，我们首先创建了一个pandas DataFrame，然后使用pandasql的sqldf函数执行了一个SQL查询。sqldf函数的第一个参数是SQL查询字符串，第二个参数是定义DataFrame的环境（在这个例子中是locals()）。

df是在局部作用域中定义的一个DataFrame。当我们调用ps.sqldf(query, locals())时，pandasql查看locals()返回的字典，找到df这个DataFrame，然后在这个DataFrame上执行SQL查询。

如果不使用locals()，你需要以其他方式告诉pandasql如何找到和识别DataFrame。例如，你也可以使用globals()，如果你的DataFrame定义在全局作用域中，或者你可以手动创建一个包含DataFrame的字典并传递给sqldf。但在大多数情况下，使用locals()是最方便的方法。

注意事项

pandasql在处理大型数据集时可能不如pandas本身的方法高效。它依赖于SQLite，因此可能不支持某些复杂的SQL功能和优化。

总的来说，pandasql是一个非常有用的工具，尤其是对于那些习惯于SQL而不是pandas查询语法的用户。

相关文章:
  1. Pandas数据处理简明教程
  2. Hive SQL系统化学习
  3. SQL 日期/时间处理函数
  4. Spark SQL 系统化学习
  5. Presto SQL体系化学习
pandas SQL
《乌合之众》读书笔记
出行系统可用化测试指导文档
谈谈数据会说谎

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注