如何按分组变量转置数据字符串，实现行转列？

你需要将每个Id对应多个手机号的长格式数据，转换为每个Id一行、手机号分列显示的宽格式数据，以下是几种常用工具的实现方法：

1. SQL（MySQL为例） #

通过分组编号+条件聚合实现：

WITH numbered_phones AS (
    SELECT 
        Id,
        PhoneNumber,
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY PhoneNumber) AS phone_seq
    FROM your_table_name
)
SELECT
    Id,
    MAX(CASE WHEN phone_seq = 1 THEN PhoneNumber ELSE 'NA' END) AS PhoneNumber1,
    MAX(CASE WHEN phone_seq = 2 THEN PhoneNumber ELSE 'NA' END) AS PhoneNumber2,
    MAX(CASE WHEN phone_seq = 3 THEN PhoneNumber ELSE 'NA' END) AS PhoneNumber3
FROM numbered_phones
GROUP BY Id
ORDER BY Id;

2. Python Pandas #

利用分组编号和pivot函数转换：

import pandas as pd

# 构造原始数据（实际使用时可替换为读取数据源）
df = pd.DataFrame({
    'Id': [1,1,1,2,3,3],
    'PhoneNumber': ['598632541','578958458','547817745','417527827','417527745','757517517']
})

# 为每个Id下的手机号生成序号
df['phone_seq'] = df.groupby('Id').cumcount() + 1

# 转宽表并填充缺失值
wide_df = df.pivot(index='Id', columns='phone_seq', values='PhoneNumber').fillna('NA')
# 修改列名格式
wide_df.columns = [f'PhoneNumber{col}' for col in wide_df.columns]
wide_df = wide_df.reset_index()

print(wide_df)

3. R 语言 #

使用tidyr包的pivot_wider函数：

library(tidyr)
library(dplyr)

# 原始数据
df <- data.frame(
    Id = c(1,1,1,2,3,3),
    PhoneNumber = c('598632541','578958458','547817745','417527827','417527745','757517517')
)

# 添加序号并转换为宽表
wide_df <- df %>%
    group_by(Id) %>%
    mutate(phone_seq = paste0("PhoneNumber", row_number())) %>%
    pivot_wider(names_from = phone_seq, values_from = PhoneNumber, values_fill = "NA") %>%
    ungroup()

print(wide_df)

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