如何在R中创建国家二元组的价格平均值（支持面板扩展）

原始数据 #

df <- data.frame(country    = c("US; UK; FI", "CN; IT; US; GR", "UK; US"),
                 product_id = c(1, 2, 3),
                 price      = c(300, 500, 200))

期望输出 #

Ctr_1 Ctr_2 Avg_Price
US    UK    250
US    FI    300
US    CN    500
US    IT    500
UK    FI    300
UK    US    250
CN    IT    500
CN    US    500
CN    GR    500
IT    CN    500
IT    US    500
IT    GR    500
GR    CN    500
GR    IT    500
GR    US    500

解决方案（基于data.table） #

步骤1：拆分国家列并关联价格 #

你之前生成的df1缺少价格信息，先把每个国家对应的商品价格带上：

library(data.table)
setDT(df)

# 拆分国家列，同时保留product_id和对应price
df1 <- df[, .(country = unlist(strsplit(country, "; ")), price = price), by = .(product_id)]

步骤2：生成所有有序国家二元组 #

对每个商品，生成所有不同国家的有序配对（即包含A-B和B-A两种组合）：

# 对每个product_id生成所有国家排列，排除自身配对
df_pairs <- df1[, .(Ctr_1 = country, Ctr_2 = rep(country, each = .N)), by = .(product_id, price)][Ctr_1 != Ctr_2]

步骤3：计算二元组平均价格 #

按国家二元组分组，计算平均价格：

result <- df_pairs[, .(Avg_Price = mean(price)), by = .(Ctr_1, Ctr_2)]
# 按Ctr_1和Ctr_2排序，和期望输出格式对齐
setorder(result, Ctr_1, Ctr_2)

扩展：加入year变量生成面板数据 #

如果原始数据包含year字段，只需在分组时加入year维度即可：

带year的原始数据示例 #

df_with_year <- data.frame(country    = c("US; UK; FI", "CN; IT; US; GR", "UK; US"),
                           product_id = c(1, 2, 3),
                           price      = c(300, 500, 200),
                           year       = c(2020, 2020, 2021))
setDT(df_with_year)

生成年度面板数据 #

# 拆分国家列，加入year分组
df1_year <- df_with_year[, .(country = unlist(strsplit(country, "; ")), price = price), by = .(product_id, year)]

# 生成带year的二元组
df_pairs_year <- df1_year[, .(Ctr_1 = country, Ctr_2 = rep(country, each = .N)), by = .(product_id, year, price)][Ctr_1 != Ctr_2]

# 按国家二元组+year计算平均价格
result_panel <- df_pairs_year[, .(Avg_Price = mean(price)), by = .(Ctr_1, Ctr_2, year)]
setorder(result_panel, year, Ctr_1, Ctr_2)

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