如何用Python实现表格透视重组并合并指定单元格？

原始表格 #

Groups SP1 SP2 SP3 SP4_1 SP4_2 SP5_1 SP5_2
G1     3   4   NA  2     4     2     1
G2     NA  1   NA  3     NA    NA    NA
G3     1   2   NA  NA    NA    8     NA
G4     4   6   NA  NA    NA    NA    NA
G5     8   9   NA  NA    NA    NA    2 

目标表格 #

G1               G2       G3       G4     G5 
SP1    SP1-3            NA       SP1-1    SP1-4  SP1-8
SP2    SP2-4            SP2-1    SP2-2    SP2-6  SP2-9
SP3    NA               NA       NA       NA     NA
SP4    SP4_1-2;SP4_2-4  SP4_1-3  NA       NA     NA
SP5    SP5_1-2;SP5_2-1  NA       SP5_1-8  NA     SP5_2-2

转换规则 #

• 原始表格的Groups列值（G1-G5）作为新表格的列名
• 原始表格中以SP开头的列，按前缀分组（如SP4_1、SP4_2归为SP4组）作为新表格的行名
• 每个分组行与Groups列的交叉位置：
  • 收集所有非NA的原始列数据，格式化为原始列名-值的形式
  • 多个值用分号;连接
  • 无有效数据则填NA

原始数据的dput格式 #

structure(list(Groups = c("G1", "G2", "G3", "G4", "G5"), SP1 = c(3L, 
NA, 1L, 4L, 8L), SP2 = c(4L, 1L, 2L, 6L, 9L), SP3 = c(NA, NA, 
NA, NA, NA), SP4_1 = c(2L, 3L, NA, NA, NA), SP4_2 = c(4L, NA, 
NA, NA, NA), SP5_1 = c(2L, NA, 8L, NA, NA), SP5_2 = c(1L, NA, 
NA, NA, 2L)), class = "data.frame", row.names = c(NA, -5L))

代码实现 #

import pandas as pd
import numpy as np

# 1. 构造原始DataFrame（对应dput数据）
data = {
    'Groups': ['G1', 'G2', 'G3', 'G4', 'G5'],
    'SP1': [3, np.nan, 1, 4, 8],
    'SP2': [4, 1, 2, 6, 9],
    'SP3': [np.nan, np.nan, np.nan, np.nan, np.nan],
    'SP4_1': [2, 3, np.nan, np.nan, np.nan],
    'SP4_2': [4, np.nan, np.nan, np.nan, np.nan],
    'SP5_1': [2, np.nan, 8, np.nan, np.nan],
    'SP5_2': [1, np.nan, np.nan, np.nan, 2]
}
df = pd.DataFrame(data)

# 2. 转为长表，提取SP分组前缀
long_df = df.melt(id_vars='Groups', var_name='SP_col', value_name='Value')
long_df['SP_group'] = long_df['SP_col'].str.extract(r'(SP\d+)')

# 3. 分组处理，生成目标格式字符串
def format_values(group):
    non_na_rows = group.dropna(subset=['Value'])
    if non_na_rows.empty:
        return np.nan
    return ';'.join([f"{row['SP_col']}-{int(row['Value'])}" for _, row in non_na_rows.iterrows()])

result_long = long_df.groupby(['SP_group', 'Groups']).apply(format_values).reset_index(name='Content')

# 4. 转回宽表并调整行顺序
result_wide = result_long.pivot(index='SP_group', columns='Groups', values='Content')
result_wide = result_wide.reindex([f'SP{i}' for i in range(1, 6)])

# 输出结果（与目标格式一致）
print(result_wide.to_string(na_rep='NA'))

运行结果 #

Groups               G1       G2       G3       G4       G5
SP_group                                                   
SP1            SP1-3       NA    SP1-1    SP1-4    SP1-8
SP2            SP2-4    SP2-1    SP2-2    SP2-6    SP2-9
SP3                NA       NA       NA       NA       NA
SP4    SP4_1-2;SP4_2-4  SP4_1-3       NA       NA       NA
SP5    SP5_1-2;SP5_2-1       NA    SP5_1-8       NA  SP5_2-2

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