Use R to make a cross tab count with means and standard dev

Question

I am converting a report to R from excel. My dataframe is like this

Color   Year
Red     2020
Blue    2019
Blue    2020
Green   2020
Green   2019
Green   2020
Red     2019
Blue    2020

And I need a matrix with counts and then the mean and SD columns for each colour at end like this:

      2019      2020      Mean      SD
Red      1      1            1      0
Blue     1      2          1.5      1
Green    1      2          1.5      1

Any help appreciated, This takes me two mins in excel but I need to convert to R. Thanks in advance

Mean of colours, so Mean "red" and SD "Red" – frangopop Jul 15 '20 at 10:17 — frangopop, Jul 15 '20 at 10:17

score 1 · Answer 1 · answered Jul 15 '20 at 10:18

You can use table to get the counts and than applyto get mean and sd.

y <- table(x)
cbind(y, mean=apply(y, 1, mean), sd=apply(y, 1, sd))
#      2019 2020 mean        sd
#Blue     1    2  1.5 0.7071068
#Green    1    2  1.5 0.7071068
#Red      1    1  1.0 0.0000000

Data:

x <- structure(list(Color = c("Red", "Blue", "Blue", "Green", "Green", 
"Green", "Red", "Blue"), Year = c(2020L, 2019L, 2020L, 2020L, 
2019L, 2020L, 2019L, 2020L)), class = "data.frame", row.names = c(NA, 
-8L))

score 0 · Accepted Answer · answered Jul 15 '20 at 10:18

0

Perhaps, you are taking mean and sd of counts :

library(dplyr)

df %>%
 count(Color, Year) %>%
 group_by(Color) %>%
 mutate(mean = mean(n),
        sd = sd(n)) %>%
 tidyr::pivot_wider(names_from = Year, values_from = n)

# Color   mean    sd `2019` `2020`
#  <chr> <dbl> <dbl>  <int>  <int>
#1 Blue    1.5 0.707      1      2
#2 Green   1.5 0.707      1      2
#3 Red     1   0          1      1

answered Jul 15 '20 at 10:18

Ronak Shah

377,200
20
156
213

yes, sorry I wasn't clear – frangopop Jul 15 '20 at 10:33
@frangopop Did the answer work for you? – Ronak Shah Jul 15 '20 at 11:47

Use R to make a cross tab count with means and standard dev

2 Answers2