跨不同报表的模糊左连接实现方案求助（含阶段记录需求）

一、近似名称匹配+自动左连接（Python实现） #

针对Mac无法使用PowerQuery模糊连接的问题，结合你具备的基础Python能力，用pandas+fuzzywuzzy可以快速实现需求，适合小数据集场景。

1. 依赖安装 #

pip install pandas openpyxl fuzzywuzzy python-Levenshtein

python-Levenshtein是可选依赖，能提升模糊匹配的速度。

2. 核心代码实现 #

步骤1：读取并预处理企业名称

先统一名称格式，减少模糊匹配的误差：

import pandas as pd
from fuzzywuzzy import process

def clean_company_name(name):
    if pd.isna(name):
        return ""
    # 统一小写、去除标点、压缩空格
    cleaned = name.lower().strip()
    # 移除常见标点符号
    for p in [',', '.', ';', ':', '(', ')', '-', '_', '&']:
        cleaned = cleaned.replace(p, '')
    # 去掉所有空格
    cleaned = ''.join(cleaned.split())
    return cleaned

# 读取两个Excel表（替换为你的文件路径）
df_a = pd.read_excel("TableA.xlsx")
df_b = pd.read_excel("TableB.xlsx")

# 预处理企业名称列（假设列名为"CompanyName"，按需修改）
df_a['CleanedName'] = df_a['CompanyName'].apply(clean_company_name)
df_b['CleanedName'] = df_b['CompanyName'].apply(clean_company_name)

步骤2：模糊匹配+左连接

def match_b_company(cleaned_name, df_b):
    # 从TableB中匹配最相似的企业
    b_names = df_b['CleanedName'].tolist()
    match, score = process.extractOne(cleaned_name, b_names)
    # 设定匹配阈值（可根据实际效果调整，比如75-90）
    if score >= 80:
        return df_b[df_b['CleanedName'] == match].iloc[0]
    else:
        # 无匹配返回空值
        return pd.Series([None]*len(df_b.columns))

# 为TableA的每条记录匹配TableB数据
matched_b_data = df_a['CleanedName'].apply(lambda x: match_b_company(x, df_b))

# 合并为最终结果（左连接）
merged_result = pd.concat([df_a, matched_b_data], axis=1)

# 保存结果到Excel
merged_result.to_excel("Merged_Result.xlsx", index=False)

3. 自动化执行 #

把上述代码保存为merge_script.py，在Mac上写一个shell脚本（run_merge.sh）一键执行：

#!/bin/bash
python3 /你的脚本路径/merge_script.py

给脚本加执行权限：chmod +x run_merge.sh，每周替换新报表后运行即可。

二、阶段记录留存方案 #

用SQLite轻量数据库存储历史记录，无需额外服务，适合小数据量的阶段留存需求。

1. 创建历史记录表 #

import sqlite3
from datetime import datetime

# 连接数据库（不存在则自动创建）
conn = sqlite3.connect('company_stage_history.db')
cursor = conn.cursor()

# 创建阶段记录表（按需添加TableA/TableB的其他字段）
cursor.execute('''
CREATE TABLE IF NOT EXISTS company_stages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    company_name TEXT,
    cleaned_name TEXT,
    table_a_col1 TEXT,
    table_a_col2 INTEGER,
    table_b_col1 TEXT,
    table_b_col2 FLOAT,
    stage_start_date DATE,
    stage_end_date DATE
)
''')
conn.commit()

2. 同步新报表到历史记录 #

每次运行合并脚本后，执行以下逻辑更新历史：

# 获取当前报表的日期（可从文件名提取，比如"TableA_20240520.xlsx"）
current_stage_date = datetime.now().strftime('%Y-%m-%d')

# 1. 标记旧阶段结束：将未结束的阶段（stage_end_date为NULL）的结束日期设为当前日期前一天
cursor.execute('''
UPDATE company_stages 
SET stage_end_date = date(?, '-1 day') 
WHERE stage_end_date IS NULL
''', (current_stage_date,))

# 2. 插入新阶段记录
for _, row in merged_result.iterrows():
    cursor.execute('''
    INSERT INTO company_stages (
        company_name, cleaned_name, table_a_col1, table_a_col2,
        table_b_col1, table_b_col2, stage_start_date, stage_end_date
    ) VALUES (?, ?, ?, ?, ?, ?, ?, NULL)
    ''', (
        row['CompanyName'], row['CleanedName'],
        row['TableA_Col1'], row['TableA_Col2'],
        row['TableB_Col1'], row['TableB_Col2'],
        current_stage_date
    ))

conn.commit()
conn.close()

这样每次运行脚本，都会自动把旧阶段标记为结束，插入新的阶段记录，完整留存企业的所有阶段信息。

注意事项 #

• 匹配阈值（80分）可根据实际匹配效果调整：匹配错误多就提高阈值，漏匹配多就降低。
• 预处理名称的规则可补充，比如移除"LLC"、"Inc."等通用后缀，进一步提升匹配准确率。
• 若习惯用SQL，可将Excel数据导入SQLite后，用similarity函数（需启用FTS5扩展）实现模糊匹配，阶段留存逻辑和上述一致。

内容的提问来源于stack exchange，提问作者Raymond Cantelmo