R:读取多个excel文件，提取第一个工作表名称，并创建新列 [英] R: reading multiple excel files, extract first sheet names, and create new column

问题描述

我有多个 excel 文件，它们具有唯一的工作表名称(在我的情况下是文件创建日期).我批量阅读它们，需要将工作表名称分配给新列id"中的每个文件.我知道如何制作数字 id 或 id = 文件名，但找不到将工作表名称作为 id 的方法.

I have multiple excel files and they have unique sheet names (date of file creation in my case). I read them in bulk and need to assign the sheet name to each file in new column "id". I know how to make numeric id, or id = file name, but cannot find a way to get sheet name as id.

library(readxl)
library(data.table)

file.list <- list.files("C:/Users/.../Studies/",pattern='*.xlsx')
df.list <- lapply(file.list, read_excel)

#id = numeric
df <- rbindlist(df.list, idcol = "id")

#Or by file name:
attr(df.list, "names") <- file.list
df2 = rbindlist(df.list,idcol="id")

#How to get sheet names?

推荐答案

如果您碰巧只处理文件的第一张工作表，那么以下内容应该可以帮助您获取第一张工作表的名称作为 id 用于您的数据框:

If you happen to be working with only the first sheets of your files, then the following should help you grab the first sheets' names as the id for your dataframes:

attr(df.list, "names") <- sapply(file.list, function(x) excel_sheets(x)[1])

但是，如果您正在考虑从所有可用的工作表中导入数据，您将需要做更多的工作，从如何创建数据框列表开始:

However, if you are considering importing the data from all the available sheets you will need to do a bit more work, starting with how you create your list of dataframes:

df.list <- lapply(file.list,function(x) {
  sheets <- excel_sheets(x)
  dfs <- lapply(sheets, function(y) {
    read_excel(x, sheet = y)
  })
  names(dfs) <- sheets
  dfs
})

这应该创建一个列表列表，其中应该包含文件中的所有可用数据.主列表中的列表以工作表名称适当命名.因此，您之后无需更改任何属性.但是要将数据帧绑定在一起，您需要执行以下操作:

This should create a list of lists, which should contain all the available data in your files. The lists inside the main list are appropriately named after the sheet names. So, you will not need to change any attributes afterwards. But to bind the dataframes together, you need to do:

rbindlist(lapply(df.list, rbindlist, id = "id"))

我希望这证明是有用的.

I hope this proves useful.

这篇关于R:读取多个excel文件，提取第一个工作表名称，并创建新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！