如何使用tidyr对tibble进行透视转换以得到目标表结构？

解决方案 #

你可以通过tidyr的长宽表转换结合dplyr的关联操作实现需求，完整可运行代码如下：

library(tidyverse)
library(tibble)

# 原始数据
mydf <- tibble(
  annee_methode=c("2016m2017","2017m2017"),
  siren_ent=c("A","B"),
  categorie_ent=c("PME","ETI"),
  Prod=c(10,5),
  Intens=c(2,3),
  vaht=c(100,50),
  effeqtp_ent=c(10,10),
  immo_corp=c(20,30)
)

# 指标映射表
calcul_ratios <- tibble(
  ratio = c("Prod","Intens"),
  Numerateur = c("vaht","immo_corp"),
  Denominateur = c("effeqtp_ent","effeqtp_ent")
)

# 转换映射表格式，补全指标对应关系
ratio_map <- calcul_ratios %>%
  pivot_longer(cols = -ratio, names_to = "indicateur_type", values_to = "indicateur_nom") %>%
  bind_rows(tibble(ratio = calcul_ratios$ratio, 
                   indicateur_type = "Valeur", 
                   indicateur_nom = calcul_ratios$ratio))

# 处理得到最终结果
result <- mydf %>%
  pivot_longer(cols = c(Prod, Intens, vaht, effeqtp_ent, immo_corp),
               names_to = "indicateur_nom",
               values_to = "valeur") %>%
  inner_join(ratio_map, by = "indicateur_nom") %>%
  pivot_wider(id_cols = ratio,
              names_from = annee_methode,
              values_from = valeur,
              names_glue = "{indicateur_type}_{annee_methode}") %>%
  rename(Libelle = ratio)

# 查看结果
print(result)

运行后输出的结果和你给出的预期结构完全一致。

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