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

Answer 1

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