将面板数据中的就业状态转换合并为字符串序列

Python Pandas 实现 #

核心逻辑是按个体分组，过滤掉连续重复的就业状态，再将剩余状态用连字符拼接成序列。

假设你的数据已经导入为DataFrame df，直接用以下代码处理：

import pandas as pd

# 示例数据构造（实际使用时替换为pd.read_csv/pd.read_excel读取你的数据）
data = {
    'Year': [1990,1991,1992,1993,1990,1991,1990,1991,1992,1993,1994],
    'Person': ['Bob','Bob','Bob','Bob','Peter','Peter','James','James','James','James','James'],
    'Employment_Status': ['High School Teacher','High School Teacher','Freelancer','High School Teacher','Singer','Singer','Actor','Actor','Producer','Producer','Investor']
}
df = pd.DataFrame(data)

# 标记每个个体的前一行就业状态
df['prev_status'] = df.groupby('Person')['Employment_Status'].shift(1)
# 过滤：保留组内第一行，或当前状态与前一行不同的记录
filtered_df = df[(df['Employment_Status'] != df['prev_status']) | df['prev_status'].isna()]

# 分组拼接状态序列
result = filtered_df.groupby('Person')['Employment_Status'].agg(lambda x: '-'.join(x)).reset_index()
result.columns = ['Person', 'Job_Sequence']

print(result)

运行后输出结果：

Person                                      Job_Sequence
0     Bob  High School Teacher-Freelancer-High School Teacher
1   James                          Actor-Producer-Investor
2   Peter                                            Singer

SQL 实现（以PostgreSQL为例） #

利用窗口函数LAG获取前一行的就业状态，过滤连续重复记录后，用STRING_AGG拼接序列：

WITH filtered_records AS (
    SELECT 
        Person,
        Employment_Status,
        Year
    FROM 
        your_table_name
    WHERE 
        Employment_Status != LAG(Employment_Status) OVER (PARTITION BY Person ORDER BY Year)
        OR LAG(Employment_Status) OVER (PARTITION BY Person ORDER BY Year) IS NULL
)
SELECT 
    Person,
    STRING_AGG(Employment_Status, '-' ORDER BY Year) AS Job_Sequence
FROM 
    filtered_records
GROUP BY 
    Person;

注意：如果用MySQL，把STRING_AGG替换为GROUP_CONCAT(Employment_Status ORDER BY Year SEPARATOR '-')即可，记得替换your_table_name为你的实际表名。

核心逻辑说明 #

两种方案的本质都是两步操作：

1. 去重连续重复状态：同一个体连续年份的相同就业状态只保留首次出现的记录
2. 分组拼接：将每个个体去重后的状态按时间顺序用连字符连接成字符串

不管个体的状态转换次数是0次（全程无换工作）还是十几次，都能完美适配需求。

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