R语言输出时，在列表中的每个数据框上方附加文本行

yhived7q 于 11个月前发布在其他

关注(0)|答案(2)|浏览(111)

假设我有两个数据集（实际上，我有6个）作为一个列表，我对它们运行一系列函数，然后我需要将这些导出为单独的Excel文件，通过文件名的子集命名它们。在导出它们之前，我想添加两行文本，如“Report”和“Quarter”。我已经提供了一个屏幕截图的模板，我想做什么。
我得到的错误是Error in if (file.exists(file) & !overwrite) { : the condition has length > 1，但在此之前还有一个问题，第二个lapply语句lapply(output_data, name_file的输出只是输出一个0的列表。

library(openxlsx)

res <- list(mtcars, iris)
names(res) <- c('mtcars', 'iris')

some_fun = function(dat, vars){
  dat%>%
    group_by('vars')%>%
    summarize(count = n())
}

name_file = function(dat, file_name){
  wb <- createWorkbook()
  addWorksheet(wb, 'raw')
  writeData(wb, 'raw', "CAR SALES REPORT:", startCol = 1, startRow = 1)
  writeData(wb, 'raw', dat, startCol = 1, startRow = 3)
  
  addWorksheet(wb, 'processed')
  writeData(wb, 'processed', "CAR SALES REPORT:")
  writeData(wb, 'processed', dat, startCol = 1, startRow = 3)
  saveWorkbook(wb, file = paste0(file_name, '.xlsx'), overwrite = TRUE)
}

output_data = lapply(res, some_fun, 1)
lapply(output_data, name_file, names(res))

字符串
这超出了我的第一个问题，但是假设我实际上有两个来自some_fun的独立输出，所以它实际上是

some_fun = function(dat, vars){
  dat1 <- dat%>%
    group_by('vars')%>%
    summarize(count = n())
  
  dat2 <- dat%>%
    group_by('vars')%>%
    mutate(count = n())
}

型
然后，我如何将dat1列表的输出插入到原始Excel工作表中，并将dat2列表的输出插入到处理后的Excel工作表中，如下所示：

name_file = function(dat, file_name){
  wb <- createWorkbook()
  addWorksheet(wb, 'raw')
  writeData(wb, 'raw', "CAR SALES REPORT:", startCol = 1, startRow = 1)
  writeData(wb, 'raw', dat1, startCol = 1, startRow = 3)
  
  addWorksheet(wb, 'processed')
  writeData(wb, 'processed', "CAR SALES REPORT:", startCol = 1, startRow = 1)
  writeData(wb, 'processed', dat2, startCol = 1, startRow = 3)
  saveWorkbook(wb, file = paste0(file_name, '.xlsx'), overwrite = TRUE)
}

型

的数据

来源：https://stackoverflow.com/questions/77628651/append-lines-of-text-above-each-data-frame-in-list-when-outputting

2条答案

按热度按时间

roejwanj1#

在上面更新的代码中，有两个列表必须连接，我使用了mapply(SIMPLIFY = FALSE)，但还有其他方法可以做到这一点。

library(openxlsx2)

# don't pollute any folder
tmp <- paste0(tempdir(), "/fun_with_lists")
if (dir.exists(tmp)) unlink(tmp, recursive = TRUE)
dir.create(tmp)
setwd(tmp)

# create lists
res <- list(mtcars, iris)
names(res) <- c("mtcars", "iris")

# aggregate function
some_fun <- function(dat, vars) {
  dat %>%
    dplyr::group_by("vars") %>%
    dplyr::summarize(count = dplyr::n())
}

# xlsx output function
name_file <- function(dat) {
  raw       <- dat[["raw"]]
  processed <- dat[["processed"]]

  wb_workbook()$
    # raw worksheet
    add_worksheet("raw")$
    add_data(dims = "A1", x = "CAR SALES REPORT:")$
    add_data_table(dims = wb_dims(x = raw, from_row = 3), x = raw,
                   with_filter = FALSE)$
    add_named_style(name = "Title")$

    # processed worksheet
    add_worksheet("processed")$
    add_data(dims = "A1", x = "CAR SALES REPORT:")$
    add_data(dims = wb_dims(x = processed, from_row = 3), x = processed)$
    add_named_style(name = "Title")$

    # write file
    save(file = paste0(dat[["name"]], ".xlsx"))
}

output_data <- lapply(res, some_fun)

# assign output list
res2 <- mapply(
  x = res, y = output_data, z = names(res),
  function(x, y, z) {
    list(raw = x, processed = y, name = z)
  },
  SIMPLIFY = FALSE
)

# create output
lapply(res2, name_file) %>%
  invisible()

# check output

if (interactive()) xl_open(dir()[[1]])

字符串

赞(0）回复(0）举报 11个月前

k4emjkb12#

（在详细阐述执行方案后更新）*

您可以将digEmAll's approach与xlsx一起使用：

library(xlsx)

line1 <- data.frame(x = "CAR SALES REPORT")
df_raw <- data.frame(mtcars)
df_processed <- df_raw[1:5, ]

# set up workbook
wb <- xlsx::createWorkbook()
sheet_1 <- xlsx::createSheet(wb, sheetName = "raw")
sheet_2 <- xlsx::createSheet(wb, sheetName = "processed")

# add sheet 1
xlsx::addDataFrame(
    line1,
    sheet = sheet_1,
    startRow = 1,
    col.names = FALSE,
    row.names = FALSE
)

xlsx::addDataFrame(
    df_raw,
    sheet = sheet_1,
    startRow = 3,
    col.names = TRUE,
    row.names = FALSE
)

# add sheet 2
xlsx::addDataFrame(
    line1,
    sheet = sheet_2,
    startRow = 1,
    col.names = FALSE,
    row.names = FALSE
)

xlsx::addDataFrame(
    df_processed,
    sheet = sheet_2,
    startRow = 3,
    col.names = TRUE,
    row.names = FALSE
)

# write file
xlsx::saveWorkbook(wb, file = "mtcars.xlsx")

字符串

的数据

赞(0）回复(0）举报 11个月前

我来回答

R语言输出时，在列表中的每个数据框上方附加文本行

2条答案

相关问题

热门标签

最新问答

R语言 输出时，在列表中的每个数据框上方附加文本行

2条答案

相关问题

热门标签

最新问答

R语言输出时，在列表中的每个数据框上方附加文本行