Is there a way to realize flat table using Mean (SE) instead of count in R?

Question

I am trying to write a publication using Rmarkdown and I am stuck with table formatting. I am looking for a way to realize a flat table with means as value.

Suppose I have a data frame which looks like this:

F1  F2  F3  V1  V2  V3  V4  V5
I   N0  T   1.977546019 137.5   0.83    8.114217417 1.032679447
I   N0  T   2.342365156 139.4   0.85    10.3602728  0.871637237
I   N0  T   2.170706854 141.2   0.82    11.59271819 1.258035755
I   N0  V   1.559072025 114.9   0.87    11.57618562 1.661523112
I   N0  V   1.984240008 118.6   0.88    11.9835584  1.60688624
I   N0  V   1.68756027  116.3   0.88    11.79686026 1.78102523
I   N1  T   2.19858517  139.7   0.85    33.1128997  4.312955185
I   N1  T   3.249054469 136.4   0.86    29.69128121 3.047780521
I   N1  T   2.223041022 142.1   0.85    20.65967924 2.332772924
I   N1  V   1.595849998 118.2   0.89    19.84579734 2.191828463
I   N1  V   1.72860847  114.8   0.86    20.16367213 5.017873836
I   N1  V   2.133891213 115.7   0.84    23.07712358 3.930948522
I   N2  T   3.152019262 131.3   0.89    35.5848969  5.589698563
I   N2  T   3.367223676 138.7   0.87    34.05297654 2.730557232
I   N2  T   3.059409463 137.4   0.83    35.37992694 3.548049932
I   N2  V   1.71633507  112.3   0.93    34.09476427 5.25868398
I   N2  V   2.284833663 116.9   0.84    22.19728478 3.518505779
I   N2  V   1.866355607 113.6   0.86    29.02993798 5.014262016
II  N0  T   1.768065012 127.8   0.83    7.6010075   9.42999993
II  N0  T   3.250876694 129.4   0.83    29.23677503 27.91017246
II  N0  T   2.815832568 133.6   0.83    4.051675097 10.12918774
II  N0  V   3.891509434 109.1   0.88    5.469474969 9.770670085
II  N0  V   2.882145915 111.2   0.87    17.00061485 21.40077399
II  N0  V   4.128069071 113.7   0.88    12.9571096  37.50296115
II  N1  T   3.003514751 126 0.84    39.39306152 7.043527056
II  N1  T   3.134655188 129.2   0.85    11.4866755  21.51749579
II  N1  T   2.785986782 131.5   0.83    19.78519656 2.176659469
II  N1  V   3.089649674 107.5   0.88    17.32529262 12.99396947
II  N1  V   4.466019417 112.6   0.89    12.03083642 20.22446923
II  N1  V   3.1820755   116.1   0.84    12.63619614 12.65798269
II  N2  T   3.428280773 134.2   0.87    16.67590015 14.49664664
II  N2  T   4.430091185 139.8   0.85    36.47033184 12.18635248
II  N2  T   3.362380446 132.4   0.86    67.7182946  11.7089442
II  N2  V   3.672823219 111.6   0.9 24.5684152  13.5849653
II  N2  V   3.031651201 110.1   0.88    19.7549665  15.6015459
II  N2  V   3.198950088 108.7   0.88    20.86135738 14.60295017

Where F columns are factors and V columns are variables

lets named this data frame "df".

First I put the variable names as column using the function pivot_longerfrom tidy verse package:

df %>% pivot_longer(
-c(F1,F2,F3),
names_to = "Variable",
values_to = "Value") -> df2

I would like to obtain a flat table :

ftable(df2, row.vars = c("Variable", "F3"), col.vars = c("F2", "F1"))

which gives the form of the kind of table I want :

                    F2 N0    N1    N2   
                    F1       I II  I II  I II
Variable F3                             
V1     T                      3  3  3  3  3  3
         V                      3  3  3  3  3  3
V2      T                      3  3  3  3  3  3
         V                      3  3  3  3  3  3
V3    T                      3  3  3  3  3  3
         V                      3  3  3  3  3  3
V4      T                      3  3  3  3  3  3
         V                      3  3  3  3  3  3
V5      T                      3  3  3  3  3  3
         V                      3  3  3  3  3  3

My issue here is that I want means instead of counts (the numbers 3 in the table) as values. Does someone know how to do it ?

I have already compute means using this code :

df2_summary <- dplyr::summarise(df2_grouped,
                                     count = dplyr::n(),
                                     mean = round(mean(Value), 2),
                                     SE = round(sd(Value)/sqrt(count),2))

but I don't know how to transform df2_summary into a flat table that looks like as the one above.

score 1 · Answer 1 · answered Aug 04 '20 at 20:37

Does this give you the result you want?

df %>% 
  group_by(F1, F2, F3) %>% 
  summarise(
    across(
      .cols = starts_with("V"),
      .fns  = mean
    )
  )

# A tibble: 12 x 8
# Groups:   F1, F2 [6]
   F1    F2    F3       V1    V2    V3    V4    V5
   <chr> <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
 1 I     N0    T      2.16  139. 0.833  10.0  1.05
 2 I     N0    V      1.74  117. 0.877  11.8  1.68
 3 I     N1    T      2.56  139. 0.853  27.8  3.23
 4 I     N1    V      1.82  116. 0.863  21.0  3.71
 5 I     N2    T      3.19  136. 0.863  35.0  3.96
 6 I     N2    V      1.96  114. 0.877  28.4  4.60
 7 II    N0    T      2.61  130. 0.83   13.6 15.8 
 8 II    N0    V      3.63  111. 0.877  11.8 22.9 
 9 II    N1    T      2.97  129. 0.84   23.6 10.2 
10 II    N1    V      3.58  112. 0.87   14.0 15.3 
11 II    N2    T      3.74  135. 0.86   40.3 12.8 
12 II    N2    V      3.30  110. 0.887  21.7 14.6

Thank you for your very quick answer. This code is similar to the one at the end of my question although it does not involved "pivot_longer" function. However, I am looking for a way to realize a flat table with the means, not a way to compute the means. — LudovicPascal, Aug 04 '20 at 21:35

Is there a way to realize flat table using Mean (SE) instead of count in R?

1 Answers1