投稿
  1. 首页
  2. 令爷课程
  3. 数据探索分析

Pandas基础教程之替换SQL

曾确令 • 2024年9月8日 pm12:23 • 数据探索分析 • 阅读 31

Pandas基础教程之替换SQL

来源： https://www.biaodianfu.com

对于很多数据分析的同学来说，最熟悉的莫过于SQL，针对数据处理问题，脑海里的第一反应也往往都是SQL，而在日常的工作中往往也需要在Pandas的DataFrame数据上处理和分析数据，今天就一起来学习Pandas。

Pandas基础教程之替换SQL

使用Pandas代替SQL

选择列

## select COL1, COL2 from TABLE;

# df.loc[:, ["COL1", "COL2"]]

pandas中loc与iloc区别

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

iloc（整数位置索引）：

  • iloc 使用整数位置来进行索引，类似于传统的Python列表和NumPy数组的索引方式。

  • 通过整数位置索引，你可以使用行和列的整数位置来访问数据。

  • 语法：iloc[row_index, col_index]，其中row_index和col_index可以是单个整数、整数切片、整数列表或布尔数组。

    data = {'A': [1, 2, 3], 'B': [4, 5, 6], 'C': [7, 8, 9]}

    loc（标签索引）：

  • loc 使用标签来进行索引，使用行和列的标签或布尔数组。

  • loc 适用于基于标签的索引，例如行或列的名称。

  • 语法：loc[row_label, col_label]，其中row_label和col_label可以是单个标签、标签列表、标签切片或布尔数组。

    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']

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

列别名

COL1 as COL1_renamed,

COL2 as COL2_renamed 

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

条件过滤

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

df[(df["COL1"]=="hoge") & (df["COL2"] > 100)]
```python
条件过滤详解
```python
## select * from TABLE where COL1 = 'hoge';

##sql select * from TABLE where 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;

## 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;

# 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;

## select * from TABLE limit 0, 10;

## select * from TABLE limit 10, 10;

聚合函数

## select sum(COL1) from TABLE;

## select avg(COL1) from TABLE;

## select min(COL1) from TABLE;

## select max(COL1) from TABLE;

## select count(COL1) from TABLE

## select count(distinct COL1) from TABLE

## select COL1, count(*) from TABLE group by COL1;

df.groupby("COL1").count()

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

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

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

HAVING子句

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

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

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

条件语句

WHEN col6 >= 50 THEN 1 ELSE 0

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

WHEN col6 < 100 THEN 1

WHEN col6 >= 100 AND col6 < 200 THEN 2

WHEN col6 >= 200 AND col6 < 300 THEN 3

elif x >= 100 and x < 200:

elif x >= 200 and x < 300:

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.drop(df[df == 'MISC'].index)

窗口函数

OVER (PARTITION BY CustomerId ORDER BY Total desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

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])

, 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 

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)

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

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

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

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：

'name': ['Alice', 'Bob', 'Charlie'],

query = "SELECT * FROM df WHERE age > 30"

result = ps.sqldf(query, locals())

在这个示例中，我们首先创建了一个 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 查询语法的用户。

原创文章，作者：曾确令，如若转载，请注明出处：https://www.zengqueling.com/pjcjczths/

赞 (0)
0 微信扫一扫 微信扫一扫
统计学基础之摘要统计
« 上一篇 2024年9月8日 pm12:11
数据探索工具Dataprep.eda
下一篇 » 2024年9月8日 pm12:25

相关推荐

联系我们

15602395067

在线咨询：点击这里给我发消息

邮件：eden7@qq.com

工作时间：周一至周五，9:30-18:30，节假日休息

QR code