r - 如何处理带有双标题的Excel文件

Question

我的 .xlsx 文件中有数据，这些文件的 header 结构分布在两行中:

rowid   CATA      CATB    CATC
        A1 A2 A3  B1 B2 B3  C1 C2 
1       1  1  2   2  3  5   5  6 
...

此外，第一个 header 中的列数(CATA CATB 等)可以跨文件更改，第二个 header 中的列数(colA1 ... colC2)也可以更改。

在 Excel 中，第一个标题用合并单元格表示，界定第二个标题中的列范围。

我有大约一百个文件，所以我想要一个算法(无需手动工作)来获取数据结构:

Rowid Cat  Col val 
1     CATA A1  1 
1     CATA A2  1 
1     CATA A3  2
1     CATB B1  2 
1     CATB B2  3 
1     CATB B3  5 
1     CATC C1  5 
1     CATC C2  6

在 R 中执行此操作的最佳方法是什么？

Answer 1

这是一个可能的解决方案。我们读取不带 header 的 xlsx 文件，使用 zoo 中的 na.locf 填充第一行中缺失的值，并创建一个存在的新 header 顶部两行的组合，即 CATA---A1、CATA---A2、CATB---B1 等。然后使用 melt 将此数据帧 reshape 为长格式，并使用 split 将自定义 header 拆分回 Cat 和 Col 中。

希望这会有所帮助!

<小时/>

test.xlsx

<小时/>

library(xlsx)
library(zoo)
library(reshape2)
library(tidyr)

read_my_xlsx <- function(xlsx_name,sheet_id)
{
  my_df <- xlsx::read.xlsx(xlsx_name,sheetIndex=sheet_id,header=F,colClasses='character',stringsAsFactors=FALSE)
  my_df[1,] = na.locf(as.character(unlist(my_df[1,])))
  my_df[1,] = c(my_df[1,1] ,sapply(2:ncol(my_df),function(x) paste0(my_df[1,x],'---',my_df[2,x])))
  colnames(my_df) = my_df[1,]
  my_df = my_df[-c(1,2),]
  my_df = melt(my_df, id.vars=c("rowid"))
  my_df = separate(my_df,variable, c("Cat", "Col"), "---")
  return(my_df)
}

read_my_xlsx('test.xlsx',1)

输出:

   rowid  Cat Col value
1      1 CATA  A1     1
2      2 CATA  A1     1
3      3 CATA  A1     3
4      1 CATA  A2     1
5      2 CATA  A2     4
6      3 CATA  A2     3
7      1 CATB  B1     4
8      2 CATB  B1     2
9      3 CATB  B1     1
10     1 CATB  B2     1
11     2 CATB  B2     1
12     3 CATB  B2     4
13     1 CATB  B3     1
14     2 CATB  B3     2
15     3 CATB  B3     3
16     1 CATC  C1     4
17     2 CATC  C1     2
18     3 CATC  C1     1
19     1 CATC  C2     1
20     2 CATC  C2     2
21     3 CATC  C2     3