0

I am trying to make a script that will make grabbing and viewing descriptive information and statistics as easy as possible for all my current and future research projects. Openxlsx is working wonders so far, but I have encountered a critical flaw in my current script: Any tables that I create in R using the aggregate command that have more than one factor level (e.g. Race; contains "White", "Black", "Asian", "Other) only display all the levels in R. When the table is exported to excel using Openxlsx, only the first factor level appears in the table as "x" and the others are missing.

Is there any way to export a table created via the aggregate command into excel, that displays all the factor's levels? I'm not sure why R displays all the levels but excel does not.

Picture of excel output

My script is here (Analysis 2 is the problem):

pubdata=read.csv("pubdatarecoded.csv",header=TRUE)
library(psych)
library(openxlsx)
str(pubdata)
wb <- createWorkbook("doesnotmatter")

Analysis 1: descriptive statistics table of the whole data frame

DescriptivesTable=describe(pubdata)
addWorksheet(wb, "DescriptiveStats")
writeData(wb, "DescriptiveStats", DescriptivesTable, startCol = 1, startRow = 1, rowNames = TRUE)

Analysis 2: checking the demographics across all conditions

addWorksheet(wb,"DemographicLoadings")
DemoLoadingsCheckTable=aggregate(pubdata$Race,by=list(pubdata$Guilt,pubdata$Discount,pubdata$TP),FUN=summary.factor)
writeData(wb, "DemographicLoadings", DemoLoadingsCheckTable, startCol = 1, startRow = 1, rowNames = FALSE)
Longshot408
  • 1
  • 2
  • 1
    Have you tried reading the column `Race` as a vector of character strings before aggregating? you could try `DemoLoadingsCheckTable$Race <- as.character(DemoLoadingsCheckTable$Race)`. Then use the `aggregate` function. – Arturo Sbr Feb 17 '19 at 05:23
  • Not knowing all of the data, I'd consider something like `isfctr <- sapply(x, is.factor); x[isfctr] <- lapply(x[isfctr], as.character)` before exporting to excel. – r2evans Feb 17 '19 at 05:24
  • I just tried transforming my Race variable into a vector of character strings before running aggregate, but that did not help; Excel still only prints level x and ignores the other info. R still recognizes that there are multiple levels to this and prints x1/x2/x3 when I type the name of my table (DemoLoadingsCheckTable) into the console, but it is still getting truncated when exported. – Longshot408 Feb 17 '19 at 12:27

0 Answers0