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

import pandas as pd

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

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

# 指定列数据类型
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'])

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
"""

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)

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
"""

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
"""

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

# 提前转换日期类型
df['order_date'] = pd.to_datetime(df['order_date'])

# 创建数据库索引
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)