0

I have a large data frame that I summarize in multiple ways using describeBy from the psych library as follows:

library(tidyverse)
library(openxlsx)
library(psych)
.
.
.
 # Describe by Region
  lst1 <- describeBy(df[QUESTIONS], df[REGION_DESCRIPTOR])

  # Describe by Doctor
  lst2 <- describeBy(df[QUESTIONS], df[CARE_DESCRIPTOR])

I then create a new workbook

  wb = createWorkbook()

and start trying to write lst1, lst2,.... into into it, one item per worksheet:

  addWorksheet(wb, REGION_DESCRIPTOR)
  writeData(wb, REGION_DESCRIPTOR, lst1)

  addWorksheet(wb, CARE_DESCRIPTOR)
  writeData(wb, CARE_DESCRIPTOR, lst2)

Unfortunately, I get an error message:

Error in as.data.frame.default(x, stringsAsFactors = FALSE) : 
  cannot coerce class ‘c("psych", "describeBy")’ to a data.frame

How can I write each describeBy object to a worksheet using openxlsx? I have tried using writexl, and while it works, I am not happy with the fact that it writes each of the summmaries generated by describeBy to a different worksheet. As I have close to a dozen describeBy's, each with 3-5 categories, this rapidly becomes unwieldy.

Thank you in advance for your help

Thomas Philips

Thomas Philips
  • 935
  • 2
  • 11
  • 22
  • Could you please paste the output of `dput(df)` in the question in order to help you! – Duck Aug 07 '20 at 16:23
  • Can you try with `do.call(rbind, describeBy(mtcars[, 'mpg'], mtcars[, 'vs']) )` – akrun Aug 07 '20 at 16:25
  • Try to change to data structure from "psych" and "describeBy" to something more standard, like a data frame, before you try to write it/save it. – Samuel Aug 07 '20 at 16:26
  • The output of dput(lst1) is too long to print, but it starts and ends like this: `> dput(lst1) structure(list(ASIA_HIGH_INCOME = structure(list(vars = 1:17, n = c(3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3), ..., row.names = c("Q1", "Q2", "Q3", "Q4", "Q5", "Q6", "Q7", "Q8", "Q9", "Q10", "Q11", "Q12", "Q13", "Q14", "Q15", "Q16", "Q17"))), .Dim = 10L, .Dimnames = list( Region = c("ASIA_HIGH_INCOME", "ASIA_MIDDLE_INCOME", ..., "WESTERN_EUROPE")), call = by.data.frame(data = x, INDICES = group, FUN = describe, type = type), class = c("psych", "describeBy"))` – Thomas Philips Aug 07 '20 at 16:32

1 Answers1

1

The object output from describeBy is a list. We could rbind them to a single matrix or data.frame and it should work

do.call(rbind, describeBy(mtcars[, 'mpg'], mtcars[, 'vs']) )
akrun
  • 874,273
  • 37
  • 540
  • 662
  • 'do.call(rbind, describeBy(df[QUESTIONS, REGION_DESCRIPTOR], df[QUESTIONS, CARE_DESCRIPTOR])) Error in do.call(rbind, describeBy(df[QUESTIONS, REGION_DESCRIPTOR], df[QUESTIONS, : second argument must be a list` – Thomas Philips Aug 07 '20 at 16:40
  • @ThomasPhilips The example I showed works for me. Your example is not reproducible to test – akrun Aug 07 '20 at 16:42
  • Your example does indeed work. Each of the describeBy's generates a single column, which can then be glued together with rbind. in my case, I am applying describeBy to 17 questions for each of the descriptors. – Thomas Philips Aug 07 '20 at 16:46
  • @ThomasPhilips You meant mulitple groups ? – akrun Aug 07 '20 at 16:46
  • Perhaps i should start with a simpler question: how can i write a single describeBy applied to 17 questions to a worksheet. I'm having difficulty creating a MWE because the problem is large – Thomas Philips Aug 07 '20 at 16:47
  • Perhaps you meant `do.call(rbind, describeBy(mtcars[c('disp','mpg')], mtcars[, 'vs']))`, it is working for me as well. The 'disp', 'mpg' corresponds to the 'Questions' columns in your case – akrun Aug 07 '20 at 16:48
  • 1
    Works beautifully! Thanks a mill. I haven't ever used do.call, but will read up on it Thanks again! – Thomas Philips Aug 07 '20 at 17:00
  • @ThomasPhilips Thanks, but why you got error earlier – akrun Aug 07 '20 at 17:01
  • @ThomasPhilips if there are more than column, you need to concatenate i.e. `do.call(rbind, describeBy(mtcars[c('disp','mpg')], mtcars[c('vs', 'gear')]))` – akrun Aug 07 '20 at 17:02