实战：小象咖啡店营业数据与个股财务指标的多源数据分析

实战：欧洲职业足球比赛数据的多源数据分析

小象实战讲义 · 量化交易Python入门：从咖啡馆到华尔街

在量化交易中，历史回测数据往往以多种异构本地文件（TXT、CSV、JSON、Excel）和关系数据库形式存在。本章你将编写数据接口，读取小象咖啡的门店营收数据并解析股票行情API响应，打通量化回测的数据通道！

💡 核心导读

本节将带领读者完成欧洲职业足球数据的采集、清洗与分析全流程。通过Python数据科学工具链操作本地存储的SQLite数据库与CSV文件，掌握多源数据合并、特征工程与可视化分析技巧。核心技能包括：1) 使用Pandas进行多表关联查询 2) 构建比赛结果预测特征 3) 制作热力图展示球队表现矩阵。

数据准备与环境配置

数据库连接与探查

import sqlite3
import pandas as pd
from pathlib import Path

# 在内存中创建数据库并填充示例数据
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# 创建示例表结构并插入少量数据
cursor.execute('''
CREATE TABLE Match (
    match_id INTEGER PRIMARY KEY,
    season TEXT,
    home_team_api_id INTEGER,
    away_team_api_id INTEGER,
    home_team_goal INTEGER,
    away_team_goal INTEGER,
    B365H REAL,
    B365D REAL,
    B365A REAL,
    league_id INTEGER
)
''')

matches_data = [
    (1, '2015/2016', 8634, 10281, 2, 1, 1.85, 3.60, 4.20, 1),
    (2, '2015/2016', 9991, 9993, 1, 1, 2.10, 3.30, 3.50, 1),
    (3, '2015/2016', 8635, 10282, 3, 0, 1.50, 4.00, 6.50, 2),
    (4, '2014/2015', 8634, 10281, 1, 2, 2.00, 3.40, 3.80, 1),
]
cursor.executemany('INSERT INTO Match VALUES (?,?,?,?,?,?,?,?,?,?)', matches_data)

cursor.execute('''
CREATE TABLE Team (
    team_api_id INTEGER PRIMARY KEY,
    team_long_name TEXT
)
''')

teams_data = [
    (8634, 'Real Madrid CF'),
    (10281, 'FC Barcelona'),
    (9991, 'Manchester United'),
    (9993, 'Chelsea'),
    (8635, 'FC Bayern Munich'),
    (10282, 'Borussia Dortmund'),
]
cursor.executemany('INSERT INTO Team VALUES (?,?)', teams_data)

conn.commit()

# 查询表名
tables = pd.read_sql("SELECT name FROM sqlite_master WHERE type='table';", conn)
print(tables)

name
0  Match
1   Team

多源数据加载

import pandas as pd
import sqlite3

# 重新建立连接并查询
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# 重建Match表和数据
cursor.execute('''
CREATE TABLE Match (
    match_id INTEGER PRIMARY KEY,
    season TEXT,
    home_team_api_id INTEGER,
    away_team_api_id INTEGER,
    home_team_goal INTEGER,
    away_team_goal INTEGER,
    B365H REAL,
    B365D REAL,
    B365A REAL,
    league_id INTEGER
)
''')
matches_data = [
    (1, '2015/2016', 8634, 10281, 2, 1, 1.85, 3.60, 4.20, 1),
    (2, '2015/2016', 9991, 9993, 1, 1, 2.10, 3.30, 3.50, 1),
    (3, '2015/2016', 8635, 10282, 3, 0, 1.50, 4.00, 6.50, 2),
]
cursor.executemany('INSERT INTO Match VALUES (?,?,?,?,?,?,?,?,?,?)', matches_data)

# 查询比赛数据
matches = pd.read_sql("SELECT * FROM Match WHERE season='2015/2016'", conn)

# 构造球员评分数据（模拟读取CSV）
ratings_data = {
    'player_id': [101, 102, 103],
    'player_name': ['Lionel Messi', 'Cristiano Ronaldo', 'Robert Lewandowski'],
    'overall_rating': [94, 93, 91]
}
ratings = pd.DataFrame(ratings_data)

print("比赛数据字段示例：")
print(matches[['home_team_api_id', 'away_team_api_id', 'home_team_goal']].head(2))
print("\n球员评分数据示例：")
print(ratings.head(2))

比赛数据字段示例：
   home_team_api_id  away_team_api_id  home_team_goal
0              8634             10281               2
1              9991              9993               1

球员评分数据示例：
   player_id        player_name  overall_rating
0        101       Lionel Messi              94
1        102  Cristiano Ronaldo              93

核心分析流程

数据关联与特征工程

import pandas as pd
import sqlite3

# 重新建立连接并查询
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# 重建Match表和数据
cursor.execute('''
CREATE TABLE Match (
    match_id INTEGER PRIMARY KEY,
    season TEXT,
    home_team_api_id INTEGER,
    away_team_api_id INTEGER,
    home_team_goal INTEGER,
    away_team_goal INTEGER,
    B365H REAL,
    B365D REAL,
    B365A REAL,
    league_id INTEGER
)
''')
matches_data = [
    (1, '2015/2016', 8634, 10281, 2, 1, 1.85, 3.60, 4.20, 1),
    (2, '2015/2016', 9991, 9993, 1, 1, 2.10, 3.30, 3.50, 1),
    (3, '2015/2016', 8635, 10282, 3, 0, 1.50, 4.00, 6.50, 2),
]
cursor.executemany('INSERT INTO Match VALUES (?,?,?,?,?,?,?,?,?,?)', matches_data)

# 重建Team表和数据
cursor.execute('''
CREATE TABLE Team (
    team_api_id INTEGER PRIMARY KEY,
    team_long_name TEXT
)
''')
teams_data = [
    (8634, 'Real Madrid CF'),
    (10281, 'FC Barcelona'),
    (9991, 'Manchester United'),
    (9993, 'Chelsea'),
    (8635, 'FC Bayern Munich'),
    (10282, 'Borussia Dortmund'),
]
cursor.executemany('INSERT INTO Team VALUES (?,?)', teams_data)
conn.commit()

# 查询数据
matches = pd.read_sql("SELECT * FROM Match WHERE season='2015/2016'", conn)
teams = pd.read_sql("SELECT * FROM Team", conn)

# 数据增强
enhanced_matches = matches.merge(
    teams[['team_api_id', 'team_long_name']],
    left_on='home_team_api_id',
    right_on='team_api_id'
).rename(columns={'team_long_name': 'home_team_name'})

enhanced_matches['result'] = enhanced_matches.apply(
    lambda row: 'home_win' if row['home_team_goal'] > row['away_team_goal'] else 
               ('draw' if row['home_team_goal'] == row['away_team_goal'] else 'away_win'),
    axis=1
)

features = enhanced_matches[[
    'home_team_name', 
    'B365H',
    'B365D',
    'B365A',
    'result'
]].dropna()

print("增强后的比赛数据特征：")
print(features)

增强后的比赛数据特征：
      home_team_name  B365H  B365D  B365A    result
0     Real Madrid CF   1.85    3.6    4.2  home_win
1  Manchester United   2.10    3.3    3.5      draw
2   FC Bayern Munich   1.50    4.0    6.5  home_win

可视化分析

import matplotlib
matplotlib.use('Agg')
import matplotlib.pyplot as plt
import seaborn as sns
import pandas as pd

# 构造比赛数据
matches_data = {
    'match_id': [1, 2, 3, 4, 5],
    'league_id': [1, 1, 2, 2, 1],
    'home_team_goal': [2, 1, 3, 0, 2],
    'away_team_goal': [1, 1, 0, 2, 0]
}
matches = pd.DataFrame(matches_data)
matches['total_goals'] = matches['home_team_goal'] + matches['away_team_goal']

plt.figure(figsize=(12,6))
sns.boxplot(
    x='league_id',
    y='total_goals',
    data=matches
)
plt.title('2015/2016赛季各联赛进球数分布')
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()
plt.close()

高级分析技巧

球队对战热力图

import matplotlib
matplotlib.use('Agg')
import matplotlib.pyplot as plt
import seaborn as sns
import pandas as pd
import numpy as np

# 构造增强的比赛数据
data = {
    'home_team_name': ['Real Madrid CF', 'Manchester United', 'FC Bayern Munich', 
                       'Real Madrid CF', 'Manchester United'],
    'away_team_name': ['FC Barcelona', 'Chelsea', 'Borussia Dortmund', 
                       'Chelsea', 'Borussia Dortmund'],
    'home_team_goal': [2, 1, 3, 1, 2],
    'away_team_goal': [1, 1, 0, 0, 1]
}
enhanced_matches = pd.DataFrame(data)

# 计算主队场均进球对阵表现
team_pairs = enhanced_matches.groupby([
    'home_team_name', 
    'away_team_name'
])['home_team_goal'].mean().unstack()

# 填充缺失值为0用于热力图
team_pairs_filled = team_pairs.fillna(0)

plt.figure(figsize=(10, 8))
sns.heatmap(
    team_pairs_filled,
    cmap='YlOrRd',
    annot=True,
    fmt='.1f',
    linewidths=.5,
    cbar_kws={'label': '场均进球'}
)
plt.title('主队场均进球对阵表现')
plt.tight_layout()
plt.show()
plt.close()

比赛结果预测

import pandas as pd
from sklearn.ensemble import RandomForestClassifier
from sklearn.model_selection import train_test_split

# 构造特征数据
features_data = {
    'home_team_name': ['Real Madrid CF', 'Manchester United', 'FC Bayern Munich', 
                       'Real Madrid CF', 'Manchester United'],
    'B365H': [1.85, 2.10, 1.50, 1.90, 2.05],
    'B365D': [3.60, 3.30, 4.00, 3.50, 3.40],
    'B365A': [4.20, 3.50, 6.50, 4.00, 3.60],
    'result': ['home_win', 'draw', 'home_win', 'home_win', 'home_win']
}
features = pd.DataFrame(features_data)

X = features[['B365H', 'B365D', 'B365A']]
y = features['result']

X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.4, random_state=42)

model = RandomForestClassifier(n_estimators=100, random_state=42)
model.fit(X_train, y_train)
accuracy = model.score(X_test, y_test)
print(f"模型准确率：{accuracy:.2%}")

模型准确率：50.00%

📝 动手练一练

第 1 题 · 概念应用

在量化策略开发中，你正在构建一个多因子模型。其中一个因子是“动量因子”，定义为过去 20 个交易日的累计收益率。你从两个数据源获取数据：price_df（CSV 文件，包含每日收盘价）和 benchmark_df（SQLite 数据库查询结果，包含每日基准指数收益率）。你需要计算个股动量因子与基准动量因子的相关性。

以下关于数据对齐和处理的步骤描述中，哪一项最合理？

• A. 由于两个数据源都包含日期，可以直接使用 pd.concat([price_df, benchmark_df], axis=1) 进行横向拼接，然后计算相关性。
• B. 先把两个 DataFrame 的日期列统一转换为 datetime 类型，再使用 pd.merge(price_df, benchmark_df, on='date', how='inner') 进行内连接对齐，确保分析基于同一组交易日。
• C. 基准在周末没有数据，就用上周五的基准收益率填充周末，并把这些填充出来的周末行当作真实交易日参与 20 日累计收益率的计算。
• D. 计算 20 日累计收益率时，应使用原始价格序列直接计算，即使中间有缺失的交易日（如停牌），也无需进行填充或特殊处理。

第 2 题 · 代码填空

在分析中，你需要将球队信息表 (team_df) 与比赛数据表 (match_df) 进行关联，以获取主队和客队的完整名称。已知两个表的关联键名称不同：match_df 中使用 home_id 和 away_id，而 team_df 中使用 team_id 和 name。

请补全下面代码中 pd.merge 函数的参数，实现正确的关联，生成包含 home_team_name 和 away_team_name 的新 DataFrame enhanced_df。

import pandas as pd

# 模拟比赛数据
match_data = {
    'match_id': [1, 2],
    'home_id': [8634, 9991],
    'away_id': [10281, 9993],
    'home_goals': [2, 1],
    'away_goals': [1, 1]
}
match_df = pd.DataFrame(match_data)

# 模拟球队数据
team_data = {
    'team_id': [8634, 10281, 9991, 9993],
    'name': ['Real Madrid CF', 'FC Barcelona', 'Manchester United', 'Chelsea']
}
team_df = pd.DataFrame(team_data)

# 关联获取主队名称
enhanced_df = pd.merge(match_df, team_df, left_on=______, right_on='team_id')
enhanced_df = enhanced_df.rename(columns={'name': 'home_team_name'})

# 关联获取客队名称
enhanced_df = pd.merge(enhanced_df, team_df, left_on=______, right_on='team_id')
enhanced_df = enhanced_df.rename(columns={'name': 'away_team_name'})

# 选择并展示关键列
result = enhanced_df[['match_id', 'home_team_name', 'away_team_name', 'home_goals', 'away_goals']]
print(result)

import pandas as pd

# 模拟比赛数据
match_data = {
    'match_id': [1, 2],
    'home_id': [8634, 9991],
    'away_id': [10281, 9993],
    'home_goals': [2, 1],
    'away_goals': [1, 1]
}
match_df = pd.DataFrame(match_data)

# 模拟球队数据
team_data = {
    'team_id': [8634, 10281, 9991, 9993],
    'name': ['Real Madrid CF', 'FC Barcelona', 'Manchester United', 'Chelsea']
}
team_df = pd.DataFrame(team_data)

# 关联获取主队名称
enhanced_df = pd.merge(match_df, team_df, left_on='home_id', right_on='team_id')
enhanced_df = enhanced_df.rename(columns={'name': 'home_team_name'})

# 关联获取客队名称
enhanced_df = pd.merge(enhanced_df, team_df, left_on='away_id', right_on='team_id')
enhanced_df = enhanced_df.rename(columns={'name': 'away_team_name'})

# 选择并展示关键列
result = enhanced_df[['match_id', 'home_team_name', 'away_team_name', 'home_goals', 'away_goals']]
print(result)

match_id     home_team_name away_team_name  home_goals  away_goals
0         1     Real Madrid CF   FC Barcelona           2           1
1         2  Manchester United        Chelsea           1           1

本章小结

📋 行动清单

• 在本地SQLite数据库中执行跨表查询，合并Match表与Team表的主客场球队名称
• 使用Seaborn绘制两支特定球队的历史对战进球数折线图
• 修改预测模型，加入球队近期比赛结果作为时序特征

—— 小象教研组