如何使用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 的侷限性，並探索其他方法。