如何在Pandas中将国家对重复行转换为新列（进出口数据场景）

问题背景 #

现有一组国家对的进出口数据集，每对国家在同一年份下对应两行数据（分别代表两个方向的贸易记录），原始数据如下：

原始数据代码 #

import pandas as pd
d = {
    "c1_id": [1,1,1,1,2,2,2,2],
    "c2_id":[2,2,2,2,1,1,1,1],
    "c1_name":["Austria","Austria","Austria","Austria","Denmark","Denmark","Denmark","Denmark"], 
    "c2_name":["Denmark","Denmark","Denmark","Denmark","Austria","Austria","Austria","Austria"],
    "year": [1970,1971,1972,1973,1970,1971,1972,1973],
    "export":[10,12,14,17,19,20,45,70],
    "import":[17,19,49,12,45,34,23,60]
}
df = pd.DataFrame(d)

原始数据输出 #

c1_id  c2_id  c1_name  c2_name  year  export  import
0      1      2  Austria  Denmark  1970      10      17
1      1      2  Austria  Denmark  1971      12      19
2      1      2  Austria  Denmark  1972      14      49
3      1      2  Austria  Denmark  1973      17      12
4      2      1  Denmark  Austria  1970      19      45
5      2      1  Denmark  Austria  1971      20      34
6      2      1  Denmark  Austria  1972      45      23
7      2      1  Denmark  Austria  1973      70      60

期望将数据转换为每年一行的结构，包含export1、export2、import1、import2列，目标结构如下：

目标数据代码 #

d_end = {
    "c1_id": [1,1,1,1],
    "c2_id":[2,2,2,2],
    "c1_name":["Austria","Austria","Austria","Austria"], 
    "c2_name":["Denmark","Denmark","Denmark","Denmark"],
    "year": [1970,1971,1972,1973],
    "export1":[10,12,14,17],
    "export2":[19,20,45,70],
    "import1":[17,19,49,12],
    "import2":[45,34,23,60]
}
df_end = pd.DataFrame(d_end)

目标数据输出 #

c1_id  c2_id  c1_name  c2_name  year  export1  export2  import1  import2
0      1      2  Austria  Denmark  1970       10       19       17       45
1      1      2  Austria  Denmark  1971       12       20       19       34
2      1      2  Austria  Denmark  1972       14       45       49       23
3      1      2  Austria  Denmark  1973       17       70       12       60

解决方案 #

方法一：针对固定国家对的合并（简单直接） #

如果仅处理指定的一对国家，可以通过拆分+合并的方式快速实现：

import pandas as pd

# 加载原始数据
d = {
    "c1_id": [1,1,1,1,2,2,2,2],
    "c2_id":[2,2,2,2,1,1,1,1],
    "c1_name":["Austria","Austria","Austria","Austria","Denmark","Denmark","Denmark","Denmark"], 
    "c2_name":["Denmark","Denmark","Denmark","Denmark","Austria","Austria","Austria","Austria"],
    "year": [1970,1971,1972,1973,1970,1971,1972,1973],
    "export":[10,12,14,17,19,20,45,70],
    "import":[17,19,49,12,45,34,23,60]
}
df = pd.DataFrame(d)

# 拆分出两个方向的贸易记录
df_austria_to_denmark = df[(df['c1_id'] == 1) & (df['c2_id'] == 2)].copy()
df_denmark_to_austria = df[(df['c1_id'] == 2) & (df['c2_id'] == 1)].copy()

# 按年份合并数据，添加后缀区分两个方向的列
merged_df = pd.merge(df_austria_to_denmark, df_denmark_to_austria, on='year', suffixes=('_1', '_2'))

# 筛选并重命名列，匹配目标结构
result = merged_df[[
    'c1_id_1', 'c2_id_1', 'c1_name_1', 'c2_name_1', 'year',
    'export_1', 'export_2', 'import_1', 'import_2'
]]
result.columns = [
    'c1_id', 'c2_id', 'c1_name', 'c2_name', 'year',
    'export1', 'export2', 'import1', 'import2'
]

print(result)

方法二：通用多国家对处理 #

如果数据包含多组国家对，可通过创建标准化的国家对标识，分组后展开数据：

import pandas as pd

# 加载原始数据
d = {
    "c1_id": [1,1,1,1,2,2,2,2],
    "c2_id":[2,2,2,2,1,1,1,1],
    "c1_name":["Austria","Austria","Austria","Austria","Denmark","Denmark","Denmark","Denmark"], 
    "c2_name":["Denmark","Denmark","Denmark","Denmark","Austria","Austria","Austria","Austria"],
    "year": [1970,1971,1972,1973,1970,1971,1972,1973],
    "export":[10,12,14,17,19,20,45,70],
    "import":[17,19,49,12,45,34,23,60]
}
df = pd.DataFrame(d)

# 创建标准化国家对键：将两个国家ID按升序排列，确保同一对国家的记录归为一组
df['pair_key'] = df.apply(lambda x: tuple(sorted([x['c1_id'], x['c2_id']])), axis=1)

# 按国家对和年份分组，聚合出所需字段
result = df.groupby(['pair_key', 'year']).agg(
    c1_id=('c1_id', lambda x: x.min()),
    c2_id=('c2_id', lambda x: x.max()),
    c1_name=('c1_name', lambda x: x[df.loc[x.index, 'c1_id'] == x.min()].iloc[0]),
    c2_name=('c2_name', lambda x: x[df.loc[x.index, 'c2_id'] == x.max()].iloc[0]),
    export1=('export', lambda x: x.iloc[0]),
    export2=('export', lambda x: x.iloc[1]),
    import1=('import', lambda x: x.iloc[0]),
    import2=('import', lambda x: x.iloc[1])
).reset_index(drop=True)

print(result)

两种方法均可输出符合要求的目标数据结构，方法一适合固定单一对国家的场景，方法二更灵活，支持多组国家对的批量处理。

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