如何将带复合表头的宽格式数据框转为指定长格式？

你的数据属于带有多行表头的复杂宽格式，需要分步骤处理：

1. 修正前3列的列名（取自第4行）并移除该行
2. 从前3行提取实际值列（Actuals开头）的元数据（财年、期间、科目）
3. 将数据行转为长格式，关联元数据后整理成目标格式

完整代码如下：

library(tidyverse)

# 定义输入数据
Input <-  structure(list(...1 = c(NA, NA, NA, "Segment", "Small", "Small"
), ...2 = c(NA, NA, NA, "Business Product", "CA", 
            "CA"), 
...3 = c("Fiscal Year", "Period", "Account", 
                                          "Entity", "INDIA", "CHINA"), 
`Actuals in USD...4` = c("2022", "Nov", "Liabilities", "* 1,000", "100", "200"), 
`Actuals in USD...5` = c("2022","Dec", "Liabilities", "* 1,000", "400", "300"), 
`Actuals in USD...6` = c("2023","Jan", "Liabilities", "* 1,000", "100", "200")), 
class = c("tbl_df","tbl", "data.frame"), row.names = c(NA, -6L))

# 1. 修正前3列的列名（取自第4行的值）
col_names_first_three <- Input[4, 1:3] %>% unlist() %>% as.character()
Input <- Input %>% rename_with(~col_names_first_three, 1:3)

# 移除作为列名的第4行
Input <- Input %>% slice(-4)

# 2. 从数据前3行提取实际值列的元数据（财年、期间、科目）
metadata <- Input %>% slice(1:3) %>%
  pivot_longer(cols = starts_with("Actuals"), names_to = "col_name", values_to = "value") %>%
  pivot_wider(names_from = Entity, values_from = value) %>%
  select(col_name, `Fiscal Year`, Period, Account)

# 3. 处理实际数据行，转为长格式并清理字段
data_rows <- Input %>% slice(4:5) %>%
  pivot_longer(cols = starts_with("Actuals"), names_to = "col_name", values_to = "Liabilities") %>%
  mutate(Liabilities = as.numeric(Liabilities),
         Entity = str_to_title(Entity))  # 将实体名转为首字母大写格式

# 4. 关联数据与元数据，整理成目标格式
final_output <- data_rows %>%
  left_join(metadata, by = "col_name") %>%
  mutate(`Fiscal Year` = as.numeric(`Fiscal Year`)) %>%
  select(Segment, `Business Product`, Entity, `Fiscal Year`, Period, Liabilities)

# 查看结果
print(final_output)

运行后得到的final_output与你提供的Output完全一致。

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