如何在R语言中调整双层表头数据框的列顺序？

问题描述 #

需要动态调整数据框的列顺序，将当前结构的dataframe转换为目标结构，数据中存在大量NA值（包括第一行），需实现双层表头的调整。

示例数据 #

当前数据框：

current_dataframe <- data.frame(
  X1 = c(NA, NA, "banana", "strawberry"), 
  X2 = c("Supermarkt", "Turnover", 1, 5),
  X3 = c(NA, "Turnover in EUR", 2, 6),
  X4 = c(NA, "Turnover  absolut", 3, 7),
  X5 = c(NA, "Sales Weeks", 4, 8),
  X6 = c("Bakery", "Turnover", 20, 24),
  X7 = c(NA, "Turnover in EUR", 21, 25),
  X8 = c(NA, "Turnover  absolut", 22, 26),
  X9 = c(NA, "Sales Weeks", 23, 27)
)

目标数据框：

required_dataframe <- data.frame(
  X1 = c(NA, NA, "banana", "strawberry"),
  X2 = c("Turnover", "Supermarkt", 1, 5),
  X3 = c(NA, "Bakery", 20, 24),
  X4 = c("Turnover in EUR", "Supermarkt", 2, 6),
  X5 = c(NA, "Bakery", 21, 25),
  X6 = c("Turnover  absolut", "Supermarkt", 3, 7),
  X7 = c(NA, "Bakery", 22, 26),
  X8 = c("Sales Weeks", "Supermarkt", 4, 8),
  X9 = c(NA, "Bakery", 23, 27)
)

解决方案 #

可以通过提取表头信息、填充缺失的店铺名称、按指标分组重排列顺序来实现，无需额外依赖包：

# 处理当前数据框
current_dataframe <- data.frame(
  X1 = c(NA, NA, "banana", "strawberry"), 
  X2 = c("Supermarkt", "Turnover", 1, 5),
  X3 = c(NA, "Turnover in EUR", 2, 6),
  X4 = c(NA, "Turnover  absolut", 3, 7),
  X5 = c(NA, "Sales Weeks", 4, 8),
  X6 = c("Bakery", "Turnover", 20, 24),
  X7 = c(NA, "Turnover in EUR", 21, 25),
  X8 = c(NA, "Turnover  absolut", 22, 26),
  X9 = c(NA, "Sales Weeks", 23, 27),
  stringsAsFactors = FALSE
)

# 提取两行表头信息
header_shop <- current_dataframe[1, ]
header_indicator <- current_dataframe[2, ]

# 手动填充表头中缺失的店铺名称（继承前一个非NA的店铺名）
filled_shops <- header_shop
current_shop <- NA
for (col_idx in seq_along(filled_shops)) {
  if (!is.na(filled_shops[col_idx])) {
    current_shop <- filled_shops[col_idx]
  } else {
    filled_shops[col_idx] <- current_shop
  }
}

# 整理指标与店铺的对应关系（排除第一列X1）
index_shop_map <- data.frame(
  col_name = colnames(current_dataframe)[-1],
  indicator = header_indicator[-1],
  shop = filled_shops[-1],
  stringsAsFactors = FALSE
)

# 生成新的列顺序：保留X1，然后按指标分组，先放Supermarkt列，再放Bakery列
unique_indicators <- unique(index_shop_map$indicator)
new_cols <- c("X1")
for (ind in unique_indicators) {
  new_cols <- c(new_cols,
                index_shop_map$col_name[index_shop_map$indicator == ind & index_shop_map$shop == "Supermarkt"],
                index_shop_map$col_name[index_shop_map$indicator == ind & index_shop_map$shop == "Bakery"])
}

# 重排列得到中间结果
reordered_df <- current_dataframe[, new_cols]

# 更新表头行：第一行改为指标，第二行改为店铺
new_header1 <- c(NA, rep(unique_indicators, each = 2))
new_header2 <- c(NA, rep(c("Supermarkt", "Bakery"), length(unique_indicators)))

reordered_df[1, ] <- new_header1
reordered_df[2, ] <- new_header2

# 验证结果
print(reordered_df)

关键说明 #

1. 填充缺失店铺名：通过循环继承前一个非NA的店铺名称，解决表头中NA值的问题；
2. 按指标分组重排：将同一指标对应的两个店铺列放在一起，实现目标列顺序；
3. 更新表头内容：调整前两行的内容，匹配目标数据框的双层表头结构。

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