基于geo匹配的两个数据集列组乘法求和计算需求

步骤1：加载并定义数据集 #

先将你提供的结构转换为R数据框：

# 第一个数据集
first_df <- structure(list(geo = c("Alemanya", "Alemanya", "Espanya", "Espanya"), time = structure(c(1688169600, 1690848000, 1009843200, 1012521600), tzone = "UTC", class = c("POSIXct", "POSIXt")), C10 = c(95.9, 102.6, 84.1, 82.1), C11 = c(114.1, 109.2, 89.8, 88.6), C12 = c(71.6, 69.3, NA, NA), C13 = c(81.7, 81.6, 211.7, 207), C14 = c(90.2, 72.1, 267.9, 284.1), C15 = c(109, 102.9, 274.6, 281.8), C16 = c(85.8, 81.7, 216, 214.9), C17 = c(80.3, 82.1, 99, 94.3), C18 = c(57.1, 63, 134.3, 129.3), C19 = c(86.4, 94, 81.5, 72.4), C20 = c(79.2, 80.3, 90.5, 90.1), C21 = c(119.1, 119.1, 72.4, 71.6), C22 = c(88.9, 88.7, 113.4, 119), C23 = c(90.8, 86.3, 229.5, 231.7), C24 = c(81.3, 79.2, 117.1, 118.2), C25 = c(93.6, 95.7, 143, 151.6), C26 = c(120.9, 127.2, 167, 170.8), C27 = c(103.6, 107.7, 132.4, 131.6), C28 = c(90.9, 87.7, 111.1, 112.7), C29 = c(75.1, 70.5, 112, 114.9), C30 = c(127.3, 128.5, 155.7, 154.7), C31 = c(66.8, 76.5, 256.8, 257.9), C32 = c(108.7, 101.2, 112.5, 115), C33 = c(106.8, 105.9, 105.4, 88), D35 = c(63.5, 57, 115.2, 95.5), E36 = c(NA_real_, NA_real_, NA_real_, NA_real_)), row.names = c(NA, -4L), class = c("tbl_df", "tbl", "data.frame"))

# 第二个数据集
second_df <- structure(list(geo = c("Espanya", "Alemanya"), C10 = c(0.783964803992383, 1.5), C11 = c(0.216035196007617, 2), C12 = c(NA, 0.8), C13 = c(NA, NA), C14 = c(0.495717344753747, 0.03), C15 = c(0.504282655246253, 0.636363636363636), C16 = c(0.195470986004089, 0.74896779521057), C17 = c(0.600537503053995, 0.25103220478943), C18 = c(0.399462496946005, 0.200188708916496), C19 = c(NA, NA), C20 = c(0.06181, 0.06181), C21 = c(0.03649, 0.03649), C22 = c(0.04545, 0.04545), C23 = c(0.03712, 0.495717344753747), C24 = c(0.303462321792261, 0.504282655246253), C25 = c(0.696537678207739, 0.195470986004089), C26 = c(0.27279792746114, 0.600537503053995), C27 = c(0.72720207253886, 0.399462496946005), C28 = c(0.04592, 0.002), C29 = c(0.74896779521057, 0.1), C30 = c(0.25103220478943, 0.4), C31 = c(0.200188708916496, 0.303462321792261), C32 = c(0.173297688315773, 0.696537678207739), C33 = c(0.431042616763642, 0.27279792746114), D35 = c(0.16484, 0.72720207253886), E36 = c(0.02858, 0.06)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, -2L))

步骤2：按geo匹配合并数据集 #

用left_join确保第一个数据集的所有行都保留，并匹配第二个数据集对应geo的系数：

library(dplyr)
library(purrr)

merged_df <- first_df %>%
  left_join(second_df, by = "geo", suffix = c("_first", "_second"))

步骤3：批量计算新列 #

按你要求的分组规则（每两个连续列一组），计算每组的乘积和，生成新列A、B、C...：

# 定义列分组：每两个为一组
col_groups <- list(
  c("C10", "C11"), c("C12", "C13"), c("C14", "C15"),
  c("C16", "C17"), c("C18", "C19"), c("C20", "C21"),
  c("C22", "C23"), c("C24", "C25"), c("C26", "C27"),
  c("C28", "C29"), c("C30", "C31"), c("C32", "C33"),
  c("D35", "E36")
)

# 生成新列名
new_col_names <- LETTERS[1:length(col_groups)]

# 批量计算并生成结果
result_df <- merged_df %>%
  mutate(!!!set_names(
    map2(col_groups, new_col_names, ~ {
      rowSums(
        select(., paste0(.x, "_first")) * select(., paste0(.x, "_second")),
        na.rm = FALSE
      )
    }),
    new_col_names
  )) %>%
  # 保留必要列
  select(geo, time, all_of(new_col_names))

查看最终结果 #

运行print(result_df)即可看到按规则生成的新数据集，其中：

• 新列A对应C10C10 + C11C11，B对应C12C12 + C13C13，以此类推
• 缺失值会保留原始逻辑（任意一个乘积为NA时，结果为NA）

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