Stata中拆分字符串变量并生成面板数据的技术问询

下面针对不同常用工具，给出具体实现方法：

1. Python Pandas实现 #

Pandas的explode方法可以快速完成拆分展开，步骤如下：

• 先将companies列的分隔符（分号、逗号）统一，再拆分为列表格式
• 使用explode将列表中的每个元素拆成单独一行
• 清理企业名称前后的空格（分隔符后可能带空格）
• 调整列顺序，将企业设为ID，报告ID设为j变量

示例代码：

import pandas as pd

# 模拟原始数据
data = pd.DataFrame({
    "report_id": [1, 2],
    "companies": ["Company A", "Company A; Company B, Company C"],
    "report_date": ["2024-01-01", "2024-02-01"]
})

# 统一分隔符并拆分
data["companies"] = data["companies"].str.replace(",", ";").str.split(";")
# 展开列表并清理空格
data = data.explode("companies").assign(companies=lambda x: x["companies"].str.strip())
# 重命名列适配面板数据要求
data = data.rename(columns={"companies": "firm_id", "report_id": "j"})
# 调整列顺序
data = data[["firm_id", "j", "report_date"]]

print(data)

2. SQL实现（以MySQL为例） #

借助递归CTE拆分多值字符串：

WITH RECURSIVE split_companies AS (
    -- 初始行：提取第一个企业
    SELECT 
        report_id,
        TRIM(SUBSTRING_INDEX(companies, ';', 1)) AS firm_id,
        TRIM(SUBSTRING(companies, LENGTH(SUBSTRING_INDEX(companies, ';', 1)) + 2)) AS remaining_companies,
        report_date
    FROM reports
    WHERE companies IS NOT NULL AND companies != ''
    
    UNION ALL
    
    -- 递归行：拆分剩余企业
    SELECT 
        report_id,
        TRIM(SUBSTRING_INDEX(remaining_companies, ';', 1)) AS firm_id,
        TRIM(SUBSTRING(remaining_companies, LENGTH(SUBSTRING_INDEX(remaining_companies, ';', 1)) + 2)) AS remaining_companies,
        report_date
    FROM split_companies
    WHERE remaining_companies IS NOT NULL AND remaining_companies != ''
)
-- 整理最终结果
SELECT 
    firm_id,
    report_id AS j,
    report_date
FROM split_companies
ORDER BY firm_id, j;

注：原始数据同时存在分号和逗号时，先执行REPLACE(companies, ',', ';')统一分隔符。

3. Stata实现 #

通过split和reshape命令组合完成：

* 模拟原始数据
clear
input str10 report_id str50 companies str10 report_date
1 "Company A" "2024-01-01"
2 "Company A; Company B, Company C" "2024-02-01"
end

* 统一分隔符
replace companies = subinstr(companies, ",", ";", .)
* 拆分企业列生成临时列
split companies, parse(;)
* 获取临时列数量
local n_cols = r(n)
* 宽数据转长数据
reshape long company, i(report_id report_date) j(temp)
* 清理空格并删除空值
replace company = trim(company)
drop if company == ""
* 重命名列并设置面板标识
rename company firm_id
rename report_id j
xtset firm_id j
* 调整列顺序
order firm_id j report_date

内容的提问来源于stack exchange，提问作者Santiago Calvo López