Mary Rachel's questions

我尝试使用 将三个数字列转换为基于每列总和的百分比mutate(across(.cols = c(...)))。我通常对一列执行此操作的方法非常有效：

mutate(`Percentage`= ((`Count`/sum(.$`Count`))*100))

当我将类似的原理应用于多列 mutate 调用，使用.x或.代替所有调用的值时，它会将每个值除以自身。我哪里做错了？

# Sample Code

test<-data.frame(fruit=c("Apples","Pears","Bananas"),
                 `John`=c(1,13,34),
                 `Jacob`=c(5,9,2))%>%
  group_by(`fruit`)%>%
  mutate(`Total`=sum(`John`,`Jacob`))

# A tibble: 3 × 4
# Groups:   fruit [3]
  fruit    John Jacob Total
  <chr>   <dbl> <dbl> <dbl>
1 Apples      1     5     6
2 Pears      13     9    22
3 Bananas    34     2    36

fruiteaten<-test%>%
  mutate(across(.cols=c(`John`,
                        `Jacob`,
                        `Total`), .fns = ~ ((.x/sum(.x))*100)))

# Output

# A tibble: 3 × 4
# Groups:   fruit [3]
  fruit    John Jacob Total
  <chr>   <dbl> <dbl> <dbl>
1 Apples    100   100   100
2 Pears     100   100   100
3 Bananas   100   100   100

# Desired Output

  fruit    John Jacob Total
1 Apples   0.02  0.31  0.09
2 Pears    0.27  0.56  0.34
3 Bananas  0.70  0.12  0.56

我正在尝试计算数据框中每列的平均值，并将平均值返回到数据框中具有相似名称的多个列中的每一行。我的想法是使用函数mutate(across(starts_with()))来调用我想要操作的列，然后使用~summarize(mean())来计算每列的平均值并改变每列的原始值。但是，我收到一条错误消息，提示summarize()不能与Fruits - Apples列中的我的数据类型一起使用。当我使用检查该列时str()，它确认这些值属于字符类，因此我用转换了所有内容as.numeric()。当我运行代码时，我仍然收到相同的错误。

# Sample Data

test<-structure(list(`Fruits - Apples` = c("1", "4"), `Fruits - Oranges` = c("2", 
"6"), `Fruits - Bananas` = c("5", "3")), row.names = c(NA, -2L
), class = c("tbl_df", "tbl", "data.frame"))

> test
# A tibble: 2 × 3
  `Fruits - Apples` `Fruits - Oranges` `Fruits - Bananas`
  <chr>             <chr>              <chr>             
1 1                 2                  5                 
2 4                 6                  3                 

# Attempted Code

nicetry<-test%>%
 mutate(across(everything(), ~as.numeric(.x)))%>%
 mutate(across(starts_with("Fruits -"), ~ summarize(mean = mean(.x, na.rm = T))))

# Error Code

Error in `mutate()`:
ℹ In argument: `across(starts_with("Fruits -"), ~summarize(mean = mean(.x, na.rm = T)))`.
Caused by error in `across()`:
! Can't compute column `Fruits - Apples`.
Caused by error in `UseMethod()`:
! no applicable method for 'summarise' applied to an object of class "c('double', 'numeric')"
Run `rlang::last_trace()` to see where the error occurred.

# Desired Output


  `Fruits - Apples` `Fruits - Oranges` `Fruits - Bananas`      
   2.5               4                  4                 
   2.5               4                  4                 

我想创建一个表来统计数据集中每列非 NA 值的数量。我正在使用summarize_all()，但我很难获得非 NA 值的数量。

我参考了这个 StackOverflow 线程来获得一些见解，但它似乎没有帮助我获得计数：summarize_all with "n()" function。如果我通过，我将获得不同值的计数，或者如果我通过或如线程所引用，summarize_all(n_distinct)我将获得行数。summarize_all(list(n=~n()))summarize_all(list(n="length")

我的期望输出：

ID Female  Male Non_Binary
 5      5     4          3

我哪里做错了？

# Sample Code

test<-as_tibble(data.frame(`ID` = c("1","2","3","4","5"),
                           `Female` = c("Female","Female","Female","Female","Female"),
                           `Male` = c(NA,"Male","Male","Male","Male"),
                           `Non_Binary`=c("Non-Binary","Non-Binary","Non-Binary",NA,NA)))

## Attempt 1

summary<-test%>%
  summarize_all(list(n=~n()))

# A tibble: 1 × 4
   ID_n Female_n Male_n Non_Binary_n
  <int>    <int>  <int>        <int>
1     5        5      5            5

## Attempt 2

summary<-test%>%
  summarize_all(list(n="length"))

# A tibble: 1 × 4
   ID_n Female_n Male_n Non_Binary_n
  <int>    <int>  <int>        <int>
1     5        5      5            5

## Attempt 3

summary<-test%>%
  summarize_all(n_distinct)

# A tibble: 1 × 4
     ID Female  Male Non_Binary
  <int>  <int> <int>      <int>
1     5      1     2          2

### Desired Output

ID Female  Male Non_Binary
 5      5     4          3

我正在尝试创建一个新列，该列将名称中包含特定字符串的特定列集的所有值用分号分隔符连接起来。我正在工作dplyr，所以我正在寻找tidyverse解决方案。

我尝试grepl()结合使用mutate()、case_when()和paste()来识别名称中包含我想要的字符串的列 ( "Games")，并将它们的内容连接在一起形成一个新列。当失败时，我尝试使用str_detect，但没有成功。

据我所知，我的问题是我无法正确指示代码评估所有列名，然后返回包含我指定模式的字符串的列名。我尝试使用contains("Games")、colnames(.x)以及这些参数的其他变体。我知道如果我明确命名了我想要粘贴在一起的每一列，我就可以做到这一点，但我更喜欢相对解决方案，这样我就不用输入多个名称了。

谢谢你！

# Sample Data

test<-as_tibble(data.frame(`ID` = c("1","2","3"),
                           `Gender` = c("Female","Male","Non-Binary"),
                           `Games_Chess`=c("Chess",NA,"Chess"),
                           `Games_Clue`=c("Clue",NA,NA),
                           `Games_Scrabble`=c("Scrabble",NA,"Scrabble")))
# A tibble: 3 × 5
  ID    Gender     Games_Chess Games_Clue Games_Scrabble
  <chr> <chr>      <chr>       <chr>      <chr>         
1 1     Female     Chess       Clue       Scrabble      
2 2     Male       NA          NA         NA            
3 3     Non-Binary Chess       NA         Scrabble  

# Desired Output

ID    Gender     Games_Chess Games_Clue Games_Scrabble Games    
1     Female     Chess       Clue       Scrabble       Chess; Clue; Scrabble
2     Male       NA          NA         NA             NA
3     Non-Binary Chess       NA         Scrabble       Chess; Scrabble  

# Attempted Code 1

test<-test%>%
  mutate(`Games` = case_when(str_detect(colnames(test),"Games") ~ paste(.x, collapse = ";"), TRUE ~ NA))

# Error Code 1

Error in `mutate()`:
ℹ In argument: `Games = case_when(...)`.
Caused by error in `case_when()`:
! Failed to evaluate the right-hand side of formula 1.
Caused by error:
! object '.x' not found

# Attempted Code 2
test<-test%>%
  mutate(`Games` = case_when(grepl("Games",.) ~ paste(., collapse = ";"), TRUE ~ NA))

# Error Code 2

Error in `mutate()`:
ℹ In argument: `Games = case_when(...)`.
Caused by error:
! `Games` must be size 3 or 1, not 4.
Run `rlang::last_trace()` to see where the error occurred.

我正在尝试将两行数据合并为一行聚合数据。两行中的许多值相同。如果它们不同，我想将数据附加在一起。

我首先使用add_row()创建一个新的空白行，作为聚合数据（包含所有NA值）的目标。然后，我尝试编写一个调用来评估每个字段中两行的内容。如果它们相同，它会将该值填充到添加的空白行中。如果它们不相同，它们会用分隔符将值连接在一起。

我能够让代码评估列中每个值与另一个值的关系并返回我想要的输出，但它会覆盖原始两行中的现有值，并且不会填充到我创建的空白第三行中。这就是我陷入困境的地方，我不确定如何引导代码将聚合值填充到创建的行的空白单元格中。

我不愿意旋转我的数据来创建一个新的列来填充，然后再将其旋转回来，因为我不想丢失我的 ID 字段（并且我也一直不擅长旋转）但这可能是解决方案，除非有更优雅的方法来做到这一点。

任何建议都值得赞赏。谢谢！

编辑：抱歉忘记了，这是我想要的输出：

 ID    Email                Gender Location    Ice_Cream_Flavor      
 1     [email protected]    Female Los Angeles Strawberry
 2     [email protected] Female Los Angeles Rocky Road
 NA    NA                   Female Los Angeles Strawberry; Rocky Road      

# Sample Data

library(tidyverse)

test<-data.frame("ID"=c("1","2"),
                 "Email"=c("[email protected]","[email protected]"),
                 "Gender"=c("Female","Female"),
                 "Location"=c("Los Angeles","Los Angeles"),
                 "Ice_Cream_Flavor"=c("Strawberry","Rocky Road"))
## Original Table

# A tibble: 2 × 5
  ID    Email                Gender Location    Ice_Cream_Flavor
  <chr> <chr>                <chr>  <chr>       <chr>           
1 1     [email protected]    Female Los Angeles Strawberry      
2 2     [email protected] Female Los Angeles Rocky Road 

## Attempted Code

merge<-test%>%
  add_row()%>%                                                                         # creating a new blank row to populate with aggregated data
  mutate(across(.cols = -c(`ID`,
                           `Email`),                                                   # calling up all columns except these specific ones    
                ~ case_when(!is.na(`ID`) & .[1]==.[2] ~ .[1],                          # Checking for equality in the values and returning the value in the first row
                            !is.na(`ID`) & .[1]!=.[2] ~ paste(.[1],.[2], sep = "; "),  # appending unequal values together
                            TRUE~NA)))

## Output

# A tibble: 3 × 5
  ID    Email                Gender Location    Ice_Cream_Flavor      
  <chr> <chr>                <chr>  <chr>       <chr>                 
1 1     [email protected]    Female Los Angeles Strawberry; Rocky Road
2 2     [email protected] Female Los Angeles Strawberry; Rocky Road
3 NA    NA                   NA     NA          NA       

我正在清理公司名称列表，但很难有选择地将大小写转换为标题大小写。我可以使用mutate(str_to_title(...))函数将整个字段转换为标题大小写，这对我的大多数值都很有效，但有少数公司的标题是“ABC Company”或“John Doe Company LLC”，当我应用标题大小写时，它们的正确大小写就会混乱（“Abc Company”和“John Doe Company Llc”）。

我以为我可以使用case_when()特定值的向量来创建一个函数，该函数告诉 R 仅将标题大小写应用于不等于我指定的值向量的值。但是，我要么收到警告“较长的对象长度不是较短的对象长度的倍数”，并且所有值都转换为标题大小写，要么我只是得到字段中的向量值的 NA，并为不在向量中的值更正标题大小写值。我哪里做错了？

# Example Code #

library(tidyverse)

## Reproducible Example ##

test<-structure(list(`Company Name` = c("ABC Company", "John Doe Company LLC", 
"rainbow road company", "yellow brick road incorporated", "XYZ", 
"Mostly Ghostly Company", "hot Leaf juice tea company")), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -7L))

test<-test%>%
  mutate(`Company Name`= case_when(`Company Name`!= c("ABC Company","John Doe Company LLC","XYZ") ~ str_to_title(`Company Name`)))

# Error #
Warning message:
There was 1 warning in `mutate()`.
ℹ In argument: `Company Name = case_when(...)`.
Caused by warning in `` `Company Name` != c("ABC Company", "John Doe Company LLC", "XYZ") ``:
! longer object length is not a multiple of shorter object length 

我使用包将一些数据框导出到 excel 文档openxlsx，并使用createStyle()和addStyle()函数应用美学。但是，我的“总计”行的格式与其他单元格不同。

我认为这可能是将其格式化为数据框的问题，因为我的格式化会调用数据框的行，而 R 可能不会将总行视为真正的行。我尝试将数据框重新格式化为 tibble，但并没有解决问题。

我不确定是否需要在我的addStyle()行中调整参数，是否需要添加另一addStyle()行并以相对方式调用总行（而不是使用索引值来调用特定数据框的特定行号），或者是否需要将原始表重新格式化为数据框或 tibble 以外的其他内容。

实际产量：

代码：

# Sample Code #

library("tidyverse")
library("openxlsx")
library("openxlsx2")
library("lubridate")
library("zoo")

## Minimum Reproducible Data ##

servicetypes_2024<- structure(list(`Service Type` = c("a", "b", 
"c", "d", "e", 
"f", "g", "h", 
"i", "j", "k", "l", 
"m", "n", "o", "Total"),`Percentage of Interactions` = c("5.8%", "5.7%", "2.9%", "1.4%", 
"1.2%", "1%", "0.7%", "0.7%", "0.6%", "0.4%", "0.4%", "0.1%", 
"0.1%", "0.1%", "0.3%", "21.5%")), row.names = c(NA, -16L), class = c("tbl_df", 
"tbl", "data.frame"))

## Aesthetics ##

cellStyle <- createStyle(wrapText = TRUE,
                         halign = "right",
                         border= "TopBottomLeftRight",
                         borderColour = getOption("openxlsx.borderColour", "black"),
                         borderStyle = getOption("openxlsx.borderStyle", "thin")) #creates a custom cell style

notesStyle<-createStyle(wrapText = TRUE, 
                        border= "TopBottomLeftRight",
                        borderColour = getOption("openxlsx.borderColour", "black"),
                        borderStyle = getOption("openxlsx.borderStyle", "thin"),
                        halign = "center",
                        textDecoration = "italic")

rowlabelStyle<-createStyle(wrapText = TRUE,
                           border= "TopBottomLeftRight",
                           borderColour = getOption("openxlsx.borderColour", "black"),
                           borderStyle = getOption("openxlsx.borderStyle", "thin"),
                           halign = "left",
                           textDecoration = "italic")

columnlabelStyle<-createStyle(wrapText = TRUE,
                              border= "TopBottomLeftRight",
                              borderColour = getOption("openxlsx.borderColour", "black"),
                              borderStyle = getOption("openxlsx.borderStyle", "thin"),
                              halign = "center",
                              textDecoration = "bold")

## Creating Worksheet ##
output<-createWorkbook() #opens an active workbook for binding

addWorksheet(output,"2024 Service Type Analysis")
writeData(output, "2024 Service Type Analysis", servicetypes_2024, 
          startCol = 1, 
          startRow = 1, 
          colNames = TRUE, 
          rowNames = FALSE, 
          keepNA = TRUE)
setHeaderFooter(output, 
                sheet = "2024 Service Type Analysis", 
                header = c(NA, "2024 Service Type Analysis", NA), 
                footer = c("Data Window: January 1st, 2024 - December 31st, 2024 unless otherwise stated", NA, NA))
setColWidths(output, "2024 Service Type Analysis", cols = 1:ncol(servicetypes_2024), widths="auto")
pageSetup(output, "2024 Service Type Analysis", orientation = "landscape", scale = 100, fitToWidth = TRUE)
addStyle(output, sheet = "2024 Service Type Analysis", cellStyle, rows = 1:nrow(servicetypes_2024), cols = 1:ncol(servicetypes_2024), gridExpand = T)
addStyle(output, sheet = "2024 Service Type Analysis", rowlabelStyle, rows = 1:nrow(servicetypes_2024), cols = 1, gridExpand = T)
addStyle(output, sheet = "2024 Service Type Analysis", columnlabelStyle, rows = 1, cols = 1:ncol(servicetypes_2024), gridExpand = T)

saveWorkbook(output, "Reporting.xlsx", overwrite= TRUE)

我正在编写一个脚本来使用 呈现 Excel 输出openxlsx，但在单元格内换行文本时遇到了困难。

我的输出中有一张“报告说明”表，其中包含有关文件中分析的常见免责声明，该免责声明由写入变量的文本字符串填充。我必须在单独的工作表中执行此操作，因为所需的免责声明文本太长，无法包含在分析表的页眉或页脚中。问题是文本字符串不会在单元格中换行，这会产生可读性问题。我尝试了各种方法来强制执行此操作，但都没有成功。

#### Example Data ####

library(tidyverse)
library(openxlsx)
library(openxlsx2)

reportnotes<- "THIS IS AN EXAMPLE TEXT STRING THAT I WANT TO WRITE TO AN EXCEL DOCUMENT. HOPEFULLY IT'S LONG ENOUGH TO MAKE MY POINT."

output<-createWorkbook()

addWorksheet(output, "Report Notes")

writeData(output, "Report Notes", reportnotes,
          startCol = 1,
          startRow = 1,
          colNames = TRUE,
          rowNames = FALSE,
          keepNA = TRUE)

pageSetup(output, "Report Notes", orientation = "landscape", scale = 100, fitToWidth = TRUE)

我尝试了以下解决方案：

#### Attempted Solutions ####

# Solution 1) Using str_wrap() and setColWidths() and setRowHeights() to create the wrap. 
# Resulted in an adjusted column and row height but no wrapped text:

reportnotes<-reportnotes%>%
   str_wrap(width = 80, indent = 0, whitespace_only = FALSE) 

setColWidths(output, "Report Notes", cols = 1, widths=70)

setRowHeights(output, "Report Notes", rows = 1, heights = 150)



# Solution 2) Defining a cell style. This solution produced an error:

wb_add_cell_style(output, sheet="Report Notes", dims = "A1", apply_border = TRUE, wrap_text=TRUE)

# Error: wb must be class wbWorkbook or R6

我的控制台是RStudio 2023.06.1+524 "Mountain Hydrangea" Release (547dcf861cac0253a8abb52c135e44e02ba407a1, 2023-07-07) for windows，我的 R 版本是R version 4.3.0 (2023-04-21 ucrt) -- "Already Tomorrow"。由于组织政策，我对更新版本的访问受到限制。

鉴于这些限制，我该怎么做才能强制文本在 Excel 输出中换行？

我正在使用 openxlsx 包准备一个 Excel 工作簿，我想用它setColWidths()来自动调整每个工作表中的列大小。由于我有多个工作表，并且我希望我的代码是相对的，所以我需要编写cols=参数来调用工作表中的所有列以进行自动调整大小。但是，似乎我只能使用特定索引来填充该参数，并且该参数似乎是该函数所必需的。

现在，我当然可以计算出一张表中有多少列并调用该特定索引值，但由于这个项目涉及许多张表，这很快就会变得繁琐，并且代码无法适应不同的表。有没有一种相对的方法来调用一张表中的所有列setColWidths()？

下面是我的虚拟数据以供参考（本例中只有一张表）。此虚拟数据将产生所需的输出，但cols=参数不是如此处所写的相对值。有没有相对的方式来调用表中的所有列？

library(tidyverse)
library(readxl)
library(writexl)
library(openxlsx)

#### Sample Data ####

month_2024<-structure(list(`CAL MONTH` = c("Jan 2024", "Feb 2024", "Mar 2024", 
"Apr 2024", "May 2024", "Jun 2024", "Jul 2024", "Aug 2024", "Sep 2024", 
"Oct 2024", "Total"), `Total Number of Interactions` = c(17L, 
11L, 18L, 9L, 15L, 29L, 28L, 16L, 10L, 13L, 166L)), row.names = c(NA, 
-11L), class = "data.frame")

####
                       
output<-createWorkbook()

addWorksheet(output,"2024 Analysis")

writeData(output, "2024 Analysis", `month_2024`, 
          startCol = 1, 
          startRow = 1, 
          colNames = TRUE, 
          rowNames = FALSE, 
          keepNA = TRUE)

setHeaderFooter(output, 
                sheet = "2024 Analysis", 
                header = c(NA, "2024 Analysis", NA), 
                footer = c("Data Window: January 1st, 2024 - &[Date] unless otherwise stated", NA, NA))

setColWidths(output, "2024 Analysis", cols = 1:2, widths="auto")

saveWorkbook(output, "Monthly Reporting.xlsx", overwrite = TRUE)