way to create summary table in R with two grouping factors

Question

I am trying to create a summary table in R with two grouping factors based on the following data

   species        plot   type  `mean(C)`
   <fct>        <fct> <fct>     <dbl>
 1 CA          MI  A         -35.7
 2 CA          MI  B         -35.6
 3 CA          MI  C         -35.9
 4 FO          MI  A         -35.7
 5 FO          MI  B         -34.9
 6 FO          MI  C         -35.3
 7 HE          MI  A         -35.4
 8 HE          MI  B         -35.6
 9 HE          MI  C         -35.6
10 LA          MI  A         -36.5

mean(C) is the response variable I am looking to show, and I'd like for it to be split up based on type and species; i.e. type as columns and species as rows

Every package I've tried to use (xtable, stargazer, gtsummary) doesn't seem to have the ability to do this. Of course, I can just plug and chug myself, but it'd be nice to know if there is a package. Anyone have any ideas?

Thanks so much

Answer 1

This is one solution using the tidyr:: package.

library(tidyr)
# define your data as dataframe df
df <- data.frame("species" = c("CA","CA","CA","FO","FO", "FO", "HE", "HE", "HE", "LA"),
                         "plot" = c("MI"),
                         "type" = c("A", "B", "C", "A", "B", "C", "A", "B", "C", "A"),
                         "mean" = c(-35.7, -35.6, -35.9, -35.7, -34.9, -35.3, -35.4, -35.6, -35.6, -36.5))
# pivot df around 'type', using 'mean'   
df %>%
  pivot_wider(names_from = type, values_from = mean)

This returns:

> df %>%
+   pivot_wider(names_from = type, values_from = mean)
# A tibble: 4 x 5
species plot      A     B     C   
<fct>   <fct> <dbl> <dbl> <dbl> 
1 CA      MI    -35.7 -35.6 -35.9 
2 FO      MI    -35.7 -34.9 -35.3 
3 HE      MI    -35.4 -35.6 -35.6 
4 LA      MI    -36.5  NA    NA

Answer 2

If you just want the data frame you presented with the type variable values as columns, you're just looking for the spread function from the tidyverse.

library(tidyr)

df <- data.frame(species = c("CA", "CA", "CA", "FO", "FO", "FO", "HE", "HE", "HE", "LA"),
                 plot = "MI", 
                 type = c("A", "B", "C", "A", "B", "C", "A", "B", "C", "A"),
                 mean_C = c(-35.7,-35.6,-35.9, -35.7,-34.9,-35.3, -35.4,-35.6, -35.6,-36.5))


new_df <- df %>%
  spread(type, mean_C)

print(new_df)

Answer 3

You can add any number of stratifying variables with tbl_strata() in {gtsummary}. Example below!

library(gtsummary)
packageVersion("gtsummary")
#> [1] '1.5.0'

tbl <-
  trial %>%
  mutate(grade = paste("Grade", grade)) %>%
  tbl_strata(
    strata = grade,
    ~ .x %>%
      tbl_summary(
        by = trt, 
        include = c(age, response),
        missing = "no"
      )
  )

^{Created on 2022-01-07 by the reprex package (v2.0.1)}