在 R 中重新格式化 Excel 工作表

Question

我有一个包含多张工作表的 excel 文件。每张纸看起来像这样，底部有一些多余的数据

    A      B     C    D.... 
1   time  USA   USA   USA   
2          MD     CA   PX   
3         pork  peas  nuts 
4   jan-11  4      2      2
5   feb-11  4      9      3
6   mar-11  8      8      3

.
.
工作簿1|工作簿2.....

该文件是 11 mb，但是当我尝试使用

sheet<-readWorksheetFromFile("excelfile.xlsx", sheet = 1)

我明白了

Error: OutOfMemoryError (Java): Java heap space

对于每个工作表，数据占用不同的行数和列数，我想写一些东西来为每张工作表产生这个。

我正在尝试将每一列转换为

country state product unit time 
USA     MD     pork    3    jan-11
USA     MD     pork    3    feb-11
USA     MD     pork    3    mar-11
...
..
.

有没有办法在 R 中做到这一点？

Answer 1

如果您的电子表格中充满了公式，您可能需要将它们转换为值，以便轻松读取它们。否则，我建议使用 tool like this one (除其他外)将工作簿中的所有工作表转换为 CSV 文件并从那里开始工作。

如果您已经做到了这一点，那么可以尝试以下方法来解决您问题的“ reshape ”部分。在这里，我们假设“A”实际上代表一个 CSV 文件，其内容是在您的问题中显示为示例数据的六行:

## Create some sample data
A <- tempfile()
writeLines(sep="\n", con = A,
           text = c("time,  USA,   USA,   USA",
                    ",   MD,    CA,    PX",
                    ", pork,  peas,  nuts",
                    "jan-11,    4,     2,     2",
                    "feb-11,    4,     9,     3",
                    "mar-11,    8,     8,     3"))

我要做的第一件事是分别读取标题和数据。要单独阅读标题，请使用 nrows指定包含标题信息的行数。要单独读取数据，请指定 skip跳过标题行。

B <- read.csv(A, header = FALSE, skip = 3, strip.white = TRUE)
Bnames <- read.csv(A, header = FALSE, nrows = 3, strip.white = TRUE)

使用 apply至 paste标题行一起形成结果 data.frame 的名称:

names(B) <- apply(Bnames, 2, function(x) paste(x[x != ""], collapse = "_"))
B
#     time USA_MD_pork USA_CA_peas USA_PX_nuts
# 1 jan-11           4           2           2
# 2 feb-11           4           9           3
# 3 mar-11           8           8           3

现在是将数据从“宽”格式转换为“长”格式的部分。有很多方法可以做到这一点，有些也使用base R，但最直接的是使用 melt和 colsplit来自“reshape2”包:

library(reshape2)
BL <- melt(B, id.vars="time")
cbind(BL[c("time", "value")], 
      colsplit(BL$variable, "_", 
               c("country", "state", "product")))
#     time value country state product
# 1 jan-11     4     USA    MD    pork
# 2 feb-11     4     USA    MD    pork
# 3 mar-11     8     USA    MD    pork
# 4 jan-11     2     USA    CA    peas
# 5 feb-11     9     USA    CA    peas
# 6 mar-11     8     USA    CA    peas
# 7 jan-11     2     USA    PX    nuts
# 8 feb-11     3     USA    PX    nuts
# 9 mar-11     3     USA    PX    nuts