如何使用Pandas和SQL进行数据分析

对于所有与数据科学和机器学习相关的任务来说，决定模型性能的最重要因素取决于我们的数据有多好。Python Pandas 和 SQL 是可以帮助高效提取和处理数据的强大工具。通过将这两种工具结合在一起，数据分析师甚至可以对大型数据集进行复杂的分析。在本文中，我们将探讨如何将 Python Pandas 与 SQL 结合起来，以提高数据分析的质量。

Pandas和SQL：概述

在一起使用 Pandas 和 SQL 之前。首先，我们将介绍 Pandas 和 SQL 的功能及其主要特点。

什么是Pandas？

Pandas 是一个用 Python 编程语言编写的软件库，用于数据操作和分析。它提供对表格、数据结构和时间序列数据的操作。

• Pandas DataFrames 允许我们处理结构化数据。
• 它提供排序、分组、合并、重塑和过滤数据等不同功能。
• 它能有效处理缺失数据值。

什么是SQL？

SQL 是结构化查询语言（Structured Query Language）的缩写，用于提取、管理和操作关系数据库。它通过整合实体和变量之间的关系来处理结构化数据。它允许插入、更新、删除和管理表中存储的数据。

• 它为查询大型数据集提供了一种强大的方法。
• 允许创建、修改和删除数据库模式。
• SQL 的语法经过优化，可使用子查询进行高效、复杂的查询操作，如 JOIN、GROUPBY、ORDER BY、HAVING。

为什么要将Pandas与SQL结合使用？

将 Pandas 和 SQL 结合使用可使代码更具可读性，而且在某些情况下更易于实现。对于复杂的工作流程来说，SQL 查询比 Pandas 代码更清晰、更易读。此外，大多数关系数据都来自数据库，而 SQL 是处理关系数据的主要工具之一。这也是数据分析师和数据科学家等专业人士喜欢集成其功能的主要原因之一。

Pandasql如何工作？

要将 SQL 查询与 Pandas 结合起来，需要在两者之间架起一座共同的桥梁，因此为了解决这个问题，“pandasql”应运而生。Pandasql 允许你直接在 Pandas 中运行 SQL 查询。这样，我们就可以无缝地使用 SQL 语法，而无需离开动态的 Pandas 环境。

安装Pandasql

将 Pandas 和 SQL 结合使用的第一步是将 pandasql 安装到我们的环境中。

pip install pandasql

安装完成后，我们就可以将 pandasql 导入代码，并用它在 Pandas DataFrame 上执行 SQL 查询。

在Pandas中运行SQL查询

安装完成后，我们就可以导入 pandasql 并开始探索它了。

import pandas as pd
import pandasql as psql
# Create a sample DataFrame
data = {'Name': ['Alice', 'Bob', 'Charlie'], 'Age': [25, 30, 35]}
df = pd.DataFrame(data)
# SQL query to select all data
query = "SELECT * FROM df"
result = psql.sqldf(query, locals())
result

让我们分解一下代码

• pd.DataFrame 将样本数据转换成表格格式。
• query (SELECT * FROM df) 将以 DataFrame 的形式选择所有数据。
• psql.sqldf(query, locals()) 将使用本地作用域在 DataFrame 上执行 SQL 查询。

使用pandasql进行数据分析

导入所有库后，就可以使用 pandasql 进行数据分析了。下面将举例说明如何通过结合 Pandas 和 SQL 来增强数据分析。要做到这一点

步骤 1：加载数据

# Required libraries
import pandas as pd
import pandasql as ps
import plotly.express as px
import ipywidgets as widgets
# Load the dataset
car_data = pd.read_csv("cars_datasets.csv")
car_data.head()

让我们来分解代码

• 导入必要的库：pandas 用于处理数据，pandasql 用于查询数据帧，plotly 用于制作交互式图表。
• pd.read_csv(“cars_datasets.csv”) 从本地目录加载数据。
• car_data.head() 将显示前 5 行。

步骤 2：探索数据

在本节中，我们将尝试通过探索列名、特征的数据类型以及数据是否存在空值等来熟悉数据。

1. 检查列名。

# Display column names
column_names = car_data.columns
column_names
"""
Output:
Index(['Unnamed: 0', 'price', 'brand', 'model', 'year', 'title_status',
'mileage', 'color', 'vin', 'lot', 'state', 'country', 'condition'],
dtype='object')
""”

2. 确定列的数据类型。

# Display dataset info
car_data.info()
"""
Ouput:
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2499 entries, 0 to 2498
Data columns (total 13 columns):
#   Column        Non-Null Count  Dtype 
---  ------        --------------  ----- 
0   Unnamed: 0    2499 non-null   int64 
1   price         2499 non-null   int64 
2   brand         2499 non-null   object
3   model         2499 non-null   object
4   year          2499 non-null   int64 
5   title_status  2499 non-null   object
6   mileage       2499 non-null   float64
7   color         2499 non-null   object
8   vin           2499 non-null   object
9   lot           2499 non-null   int64 
10  state         2499 non-null   object
11  country       2499 non-null   object
12  condition     2499 non-null   object
dtypes: float64(1), int64(4), object(8)
memory usage: 253.9+ KB
"""

3. 检查空值。

# Check for null values
car_data.isnull().sum()
"""Output:
Unnamed: 0      0
price           0
brand           0
model           0
year            0
title_status    0
mileage         0
color           0
vin             0
lot             0
state           0
country         0
condition       0
dtype: int64
"""

步骤 3：分析数据

将数据集加载到工作流程后。现在，我们将开始执行数据分析。

使用Python Pandas和SQL进行数据分析的示例

现在，让我们尝试使用 pandasql 运行一些查询来分析上述数据集。

查询 1：选择10款最昂贵的汽车

首先，让我们从整个数据集中找出前 10 名最昂贵的汽车。

def q(query):
return ps.sqldf(query, {'car_data': car_data})
q("""
SELECT brand, model, year, price
FROM car_data
ORDER BY price DESC
LIMIT 10
""")

让我们分解一下代码

• q(query) 是一个自定义函数，用于在数据帧上执行 SQL 查询。
• 查询会遍历整个数据集，选择品牌、型号、年份、价格等列，然后按价格降序排序。

查询 2：按品牌分列的平均价格

在这里，我们将找到每个品牌汽车的平均价格。

def q(query):
return ps.sqldf(query, {'car_data': car_data})
q("""
SELECT brand, ROUND(AVG(price), 2) AS avg_price
FROM car_data
GROUP BY brand
ORDER BY avg_price DESC""")

让我们分解一下代码

• 在这里，查询使用 AVG(price)计算每个品牌的平均价格，并使用四舍五入将结果舍入两位小数。
• GROUPBY 将按照汽车品牌对数据进行分组，并使用 AVG(price)按降序排序。

查询 3：2015年后生产的汽车

让我们列出 2015 年后生产的汽车。

def q(query):
return ps.sqldf(query, {'car_data': car_data})
q("""
SELECT *
FROM car_data
WHERE year > 2015
ORDER BY year DESC
""")

让我们分解一下代码

• 在这里，查询选择了 2015 年之后的所有汽车制造商，并按降序排列。

查询 4：按上市汽车数量排序的前5个品牌

现在我们来查找每个品牌生产的汽车总数。

def q(query):
return ps.sqldf(query, {'car_data': car_data})
q("""
SELECT brand, COUNT(*) as total_listed
FROM car_data
GROUP BY brand
ORDER BY total_listed DESC
LIMIT 5
""")

让我们分解一下代码

• 在这里，查询使用 GROUP BY 操作统计每个品牌的汽车总数。
• 它以降序排列，并使用 5 的限制只选出前 5 名。

查询 5：按条件计算平均价格

让我们看看如何根据条件对汽车进行分组。在这里，条件列显示了列表添加的时间或剩余时间。据此，我们可以对汽车进行分类，并得到它们的平均价格。

def q(query):
return ps.sqldf(query, {'car_data': car_data})
q("""
SELECT condition, ROUND(AVG(price), 2) AS avg_price, COUNT(*) as listings
FROM car_data
GROUP BY condition
ORDER BY avg_price DESC
""")

让我们分解一下代码

• 在这里，查询按条件（如新车或二手车）对汽车进行分组，并使用 AVG(Price) 计算价格。
• 按降序排列，首先显示最贵的汽车。

查询 6：按品牌分列的平均里程和价格

在这里，我们将找到每个品牌汽车的平均里程数和平均价格。

def q(query):
return ps.sqldf(query, {'car_data': car_data})
q("""
SELECT brand,
ROUND(AVG(mileage), 2) AS avg_mileage,
ROUND(AVG(price), 2) AS avg_price,
COUNT(*) AS total_listings
FROM car_data
GROUP BY brand
ORDER BY avg_price DESC
LIMIT 10
""")

让我们分解一下代码

• 在这里，查询使用品牌对汽车进行分组，计算其平均里程和平均价格，并计算该组中每个品牌的列表总数。
• 按价格降序排列。

查询 7：热门品牌的每英里价格比

现在，让我们根据计算得出的里程比（即各品牌汽车每英里的平均价格）对热门品牌进行排序。

def q(query):
return ps.sqldf(query, {'car_data': car_data})
q("""
SELECT brand,
ROUND(AVG(price/mileage), 4) AS price_per_mile,
COUNT(*) AS total
FROM car_data
WHERE mileage > 0
GROUP BY brand
ORDER BY price_per_mile DESC
LIMIT 10
""")

让我们分解一下代码

在这里，查询会计算每个品牌的每英里价格，然后显示每个品牌具有该特定每英里价格的汽车。按每英里价格降序排列。

查询 8：按地区划分的汽车平均价格

在这里，我们将查找并绘制特定城市中每个品牌的汽车数量。

state_dropdown = widgets.Dropdown(
options=car_data['state'].unique().tolist(),
value=car_data['state'].unique()[0],
description='Select State:',
layout=widgets.Layout(width='50%')
)
def plot_avg_price_state(state_selected):
query = f"""
SELECT brand, AVG(price) AS avg_price
FROM car_data
WHERE state = '{state_selected}'
GROUP BY brand
ORDER BY avg_price DESC
"""
result = q(query)
fig = px.bar(result, x='brand', y='avg_price', color='brand',
title=f"Average Car Price in {state_selected}")
fig.show()
widgets.interact(plot_avg_price_state, state_selected=state_dropdown)

让我们分解一下代码

• State_dropdown 创建一个下拉菜单，从数据中选择美国的不同州，并允许用户选择一个州。
• plot_avg_price_state(state_selected) 执行查询以计算每个品牌的平均价格，并使用 plotly 绘制柱形图。
• widgets.interact() 将下拉菜单与函数连接起来，这样当用户选择不同的状态时，图表可以自行更新。

有关笔记本和此处使用的数据集，请访问此链接。

pandasql的局限性

尽管 pandasql 提供了许多高效的功能和使用 Pandas 运行 SQL 查询的便捷方法，但它也有一些局限性。在本节中，我们将探讨这些局限性，并尝试找出何时该依赖传统的 Pandas 或 SQL，何时该使用 pandasql。

• 与大型数据集不兼容：当我们运行 pandasql 查询时，会在当前查询完全执行之前在内存中创建一个数据副本。这种执行大型数据集查询的方法会导致内存占用率高和执行速度慢。
• 有限的 SQL 功能：pandasql 支持许多基本的 SQL 功能，但无法完全实现所有高级功能，如子查询、复杂连接和窗口函数。
• 与复杂数据的兼容性：pandas 能很好地处理表格数据。在处理嵌套 JSON 或多索引 DataFrames 等复杂数据时，它无法提供所需的结果。

小结

将 Pandas 和 SQL 结合使用可显著改善数据分析工作流程。利用 pandasql，可以在 DataFrames 中无缝运行 SQL 查询。这有助于那些熟悉 SQL 但又想在 Python 环境中工作的人。Pandas 和 SQL 的集成结合了两者的灵活性，为数据处理和分析开辟了新的可能性。有了它，人们可以提高应对各种数据挑战的能力。不过，在处理大型复杂数据集时，也要考虑到 pandasql 的局限性，并探索其他方法。