数据, 术→技巧

Pandas基础教程之替换SQL

钱魏Way · · 13 次浏览

对于很多数据分析的同学来说，最熟悉的莫过于SQL（相关SQL教程见产品&运营分析技能培训：Hive SQL），针对数据处理问题，脑海里的第一反应也往往都是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. SQL 日期/时间处理函数
  2. Jpuyter中使用PySpark连接Hive
  3. Pandas数据处理简明教程
  4. 树形结构数据数据库存储方案
  5. 数据分析应该掌握的SQL技能
pandas SQL
国内高铁票价的计算规则
同步WordPress到微信公众号
Python模板引擎jinja2

发表回复

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