python中如何把excel文件讀取為df并用SQL查詢、分析

# Python中如何把Excel文件讀取為DF并用SQL查詢、分析

## 引言

在數據分析領域，Excel文件是最常見的數據源之一，而Python的pandas庫和SQL查詢的結合能夠提供強大的數據處理能力。本文將詳細介紹如何：
1. 使用pandas讀取Excel文件為DataFrame
2. 通過SQL語法查詢DataFrame數據
3. 進行復雜的數據分析操作

---

## 一、環境準備

### 1.1 安裝必要庫
```bash
pip install pandas openpyxl sqlalchemy

• pandas: 數據處理核心庫
• openpyxl: 處理xlsx格式的Excel文件
• sqlalchemy: 提供SQL查詢接口

1.2 示例數據準備

創建示例Excel文件sales_data.xlsx，包含以下字段：

二、讀取Excel到DataFrame

2.1 基礎讀取方法

import pandas as pd

# 讀取整個Excel文件
df = pd.read_excel('sales_data.xlsx', engine='openpyxl')

# 讀取特定工作表
df = pd.read_excel('sales_data.xlsx', sheet_name='Sheet1')

2.2 高級參數配置

# 指定列數據類型
dtype = {'order_id': str, 'quantity': float}
df = pd.read_excel('sales_data.xlsx', dtype=dtype)

# 處理空值
df = pd.read_excel('sales_data.xlsx', na_values=['NA', 'NULL'])

三、使用SQL查詢DataFrame

3.1 通過pandasql庫

from pandasql import sqldf

# 基本查詢
query = "SELECT product, SUM(quantity) as total_qty FROM df GROUP BY product"
result = sqldf(query, globals())

# 多表連接（需提前加載多個DF）
query = """
SELECT a.product, b.category 
FROM df a 
JOIN product_info b ON a.product = b.product_name
"""

3.2 通過SQLAlchemy（推薦生產環境使用）

from sqlalchemy import create_engine

# 創建內存數據庫
engine = create_engine('sqlite:///:memory:')

# 將DataFrame存入臨時表
df.to_sql('sales', engine, index=False)

# 執行SQL查詢
query = """
SELECT strftime('%Y-%m', order_date) as month, 
       SUM(quantity * unit_price) as revenue
FROM sales 
GROUP BY month
"""
result = pd.read_sql(query, engine)

四、實戰分析案例

4.1 銷售趨勢分析

query = """
SELECT 
    product,
    SUM(quantity) as total_units,
    SUM(quantity * unit_price) as total_revenue,
    AVG(unit_price) as avg_price
FROM sales
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY product
ORDER BY total_revenue DESC
"""

4.2 客戶購買行為分析

query = """
WITH monthly_stats AS (
    SELECT 
        strftime('%Y-%m', order_date) as month,
        COUNT(DISTINCT order_id) as orders_count
    FROM sales
    GROUP BY month
)
SELECT 
    month,
    orders_count,
    LAG(orders_count, 1) OVER (ORDER BY month) as prev_month,
    (orders_count - LAG(orders_count, 1) OVER (ORDER BY month)) / 
        LAG(orders_count, 1) OVER (ORDER BY month) as growth_rate
FROM monthly_stats
"""

五、性能優化技巧

5.1 數據預處理優化

# 讀取時只加載必要列
cols = ['product', 'quantity', 'order_date']
df = pd.read_excel('sales_data.xlsx', usecols=cols)

# 提前轉換日期類型
df['order_date'] = pd.to_datetime(df['order_date'])

5.2 查詢優化

# 創建數據庫索引
with engine.connect() as conn:
    conn.execute("CREATE INDEX idx_product ON sales(product)")
    
# 使用CTE代替子查詢
query = """
WITH high_value_orders AS (
    SELECT order_id 
    FROM sales 
    WHERE quantity * unit_price > 1000
)
SELECT * FROM sales WHERE order_id IN high_value_orders
"""

六、完整代碼示例

import pandas as pd
from sqlalchemy import create_engine

# 讀取數據
df = pd.read_excel('sales_data.xlsx', parse_dates=['order_date'])

# 創建SQL引擎
engine = create_engine('sqlite:///:memory:')
df.to_sql('sales', engine, index=False)

# 執行復雜分析
query = """
SELECT 
    product,
    SUM(quantity) as total_units,
    SUM(quantity * unit_price) as total_revenue,
    ROUND(SUM(quantity * unit_price) / SUM(quantity), 2) as effective_price
FROM sales
GROUP BY product
HAVING total_revenue > 5000
ORDER BY total_revenue DESC
"""

result = pd.read_sql(query, engine)
result.to_excel('analysis_result.xlsx', index=False)

結語

通過結合pandas的Excel處理能力和SQL的查詢靈活性，我們可以： 1. 輕松處理百萬行級別的Excel數據 2. 復用現有的SQL知識進行復雜分析 3. 輸出結果到新的Excel文件或其他格式

這種工作流特別適合需要定期處理Excel報表的數據分析師，既保留了Excel的易用性，又獲得了SQL的強大查詢能力。 “`