Writing multiple data frames from a list into excel, split 1 factor across different sheets and order by another factor

huangapple go评论61阅读模式
英文:

Writing multiple data frames from a list into excel, split 1 factor across different sheets and order by another factor

问题

这是一些数据。

a <- rnorm(1000, 1, 1)
b <- rep(letters[1:10], each = 100)
d <- rep(c("x", "y", "z", "q"), 250)

abd <- cbind.data.frame(a, b, d)

我想根据一个因子(在这种情况下是 "b")将这个数据框拆分成数据框列表,然后将这个数据框列表以一种方式写入Excel,其中每个因子 "b" 将被放在一个新的工作表上,而因子 "d" 将按指定顺序排序(例如,首先是 x,然后是 y,然后是 z,然后是 q)。

首先,我尝试根据因子拆分:

abdlist <- split(abd, abd$b)
abdlist[1]

这部分目前看起来很好,但我希望在将列表写入Excel时指定因子 "d" 的顺序。

使用以下库来实现这个目标:

library(dplyr)
library(writexl)

abdlist %>% write_xlsx(path = "path.xlsx")

这将把数据写入Excel文件 "path.xlsx" 中,并根据因子 "b" 创建新的工作表,并按指定的顺序排列因子 "d"。

英文:

Here is some data.

a&lt;-rnorm(1000, 1,1)
b&lt;-rep(letters[1:10], each = 100)
d&lt;-rep(c(&quot;x&quot;,&quot;y&quot;,&quot;z&quot;,&quot;q&quot;),250) 

abd&lt;-cbind.data.frame(a,b,d)

I would like to split this data frame into list of data frames based on a factor (b in this case) and than write this list of data frame into excel in a way that each factor "b" will be placed on a new sheet and factor "d" will be ordered in a specified way (e.g. first comes x, than y, than z, than q).
I first tried to split by factor:

abdlist&lt;-split(abd, abd$b)
abdlist[1]

This looks fine for now, I would like to specify the order of factor "d" when I am writing the list to excel.

library(dplyr)
library(writexl)
abdlist %&gt;%:write_xlsx(path = &quot;path.xlsx&quot;)

答案1

得分: 1

以下是翻译后的代码部分:

library(purrr)
library(dplyr)
library(openxlsx)

wb <- createWorkbook()
abdlist %>%
  map(~ .x %>%
        mutate(d = factor(d, levels = c("x", "y", "z", "q")))%>%
        arrange(d)) %>%
  iwalk(function(.data, sheet_name) {
            addWorksheet(wb = wb, sheetName = sheet_name)
            writeData(wb = wb, x = .data, sheet = sheet_name)
          }
  )

saveWorkbook(wb = wb, file = "text.xlsx", overwrite = TRUE)
英文:

You could use purrr for this:

library(purrr)
library(dplyr)
library(openxlsx)

wb &lt;- createWorkbook()
abdlist |&gt; 
  map(~.x |&gt; 
        mutate(d = factor(d, levels = c(&quot;x&quot;,&quot;y&quot;,&quot;z&quot;,&quot;q&quot;))) |&gt; 
        arrange(d)) |&gt; 
  iwalk(function(.data, sheet_name) {
                addWorksheet(wb = wb, sheetName = sheet_name)
                writeData(wb = wb, x = .data, sheet = sheet_name)
              }
  )

saveWorkbook(wb = wb, file = &quot;text.xlsx&quot;, overwrite = TRUE)

huangapple
  • 本文由 发表于 2023年3月31日 17:25:42
  • 转载请务必保留本文链接:https://go.coder-hub.com/75896830.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定