CODEWITHSUNDEEP
r
Richard Michaud Langis
Richard Michaud Langis

Reputation: 133

how to add a mean row, Standard deviation row, sum column

I have tables varying in amounts of rows, but always the same number of columns, but they all look like this:

> head(Summary_POST,10)
        Date Standing Stepping Cycling New_Sitting Counter
1 2022-03-31     1489        8      85         639     132
2 2022-04-01     1823      951     181       29446     481
3 2022-04-04    10159      718   11093       10431    1136
4 2022-04-05     9112        6       8       23275       6
5 2022-04-06    16210      852    7258        8081     601
6 2022-04-07    10997     1607    3554       16243     862
7 2022-04-08    23803       10    2094        6468     238
>dput(head(Summary_POST,10)
structure(list(Date = structure(c(19082, 19083, 19086, 19087, 
19088, 19089, 19090), class = "Date"), Standing = c(1489, 1823, 
10159, 9112, 16210, 10997, 23803), Stepping = c(8, 951, 718, 
6, 852, 1607, 10), Cycling = c(85, 181, 11093, 8, 7258, 3554, 
2094), New_Sitting = c(639, 29446, 10431, 23275, 8081, 16243, 
6468), Counter = c(132L, 481L, 1136L, 6L, 601L, 862L, 238L)), row.names = c(NA, 
7L), class = c("tbl_df", "tbl", "data.frame"))

I would like to be able to add 2 rows at the end and 1 column, 1 row that does the MEAN of each column(not the Date column), 1 row that does the Standard Deviation of each column (not the Date column), and a Column that does the SUM of each Row (ONLY THE DATA IN COLUMNS "Standing:New_Sitting").

End result in this case should look like this:

        Date Standing Stepping Cycling New_Sitting Counter  SUM
1 2022-03-31     1489        8      85         639     132   2221
2 2022-04-01     1823      951     181       29446     481  32401
3 2022-04-04    10159      718   11093       10431    1136  32401
4 2022-04-05     9112        6       8       23275       6  32401
5 2022-04-06    16210      852    7258        8081     601  32401
6 2022-04-07    10997     1607    3554       16243     862  32401
7 2022-04-08    23803       10    2094        6468     238  32375
8 MEAN          10513      593    3467       13511     493
9 STDV           7254      569    3939        9343     377

EDIT

Summary_POST <- workday_POST %>% group_by(Date) %>% mutate_if(is.character,as.numeric) %>% summarise(across(Axis1:Counter,sum))
Summary_POST <- subset (Summary_POST, select = -c(Axis1,Axis2,Axis3,VM))
 Summary_POST  %>% mutate(
        Date = as.character(Date)
    )
Summary_POST %>% 
    bind_rows(
        Summary_POST %>% 
            summarise(across(everything(), mean)) %>% 
            mutate(
                Date = 'mean'
            )
    ) %>% 
    bind_rows(
        Summary_POST %>% 
            summarise(across(everything(), sd)) %>% 
            mutate(
                Date = 'sd'
            )
    ) %>% 
    mutate(
        SUM = rowSums(across(-Date)),
        SUM = if_else(Date %in% c('mean', 'sd'), NA_real_, SUM)
    )

Upvotes: 1

Views: 190

Answers (1)

Baraliuh
Baraliuh

Reputation: 2141

Bit ugly but this is one way:

library(dplyr)
tib <- structure(list(Date = structure(c(19082, 19083, 19086, 19087, 
                                  19088, 19089, 19090), class = "Date"), Standing = c(1489, 1823, 
                                                                                      10159, 9112, 16210, 10997, 23803), Stepping = c(8, 951, 718, 
                                                                                                                                      6, 852, 1607, 10), Cycling = c(85, 181, 11093, 8, 7258, 3554, 
                                                                                                                                                                     2094), New_Sitting = c(639, 29446, 10431, 23275, 8081, 16243, 
                                                                                                                                                                                            6468), Counter = c(132L, 481L, 1136L, 6L, 601L, 862L, 238L)), row.names = c(NA, 
                                                                                                                                                                                                                                                                        7L), class = c("tbl_df", "tbl", "data.frame")) %>% 
    mutate(
        Date = as.character(Date)
    )
tib %>% 
    bind_rows(
        tib %>% 
            summarise(across(everything(), mean)) %>% 
            mutate(
                Date = 'mean'
            )
    ) %>% 
    bind_rows(
        tib %>% 
            summarise(across(everything(), sd)) %>% 
            mutate(
                Date = 'sd'
            )
    ) %>% 
    mutate(
        SUM = rowSums(across(-Date)),
        SUM = if_else(Date %in% c('mean', 'sd'), NA_real_, SUM)
    )
# A tibble: 9 x 7
  Date       Standing Stepping Cycling New_Sitting Counter   SUM
  <chr>         <dbl>    <dbl>   <dbl>       <dbl>   <dbl> <dbl>
1 2022-03-31    1489        8      85         639     132   2353
2 2022-04-01    1823      951     181       29446     481  32882
3 2022-04-04   10159      718   11093       10431    1136  33537
4 2022-04-05    9112        6       8       23275       6  32407
5 2022-04-06   16210      852    7258        8081     601  33002
6 2022-04-07   10997     1607    3554       16243     862  33263
7 2022-04-08   23803       10    2094        6468     238  32613
8 mean         10513.     593.   3468.      13512.    494.    NA
9 sd            7835.     615.   4255.      10091.    407.    NA

Upvotes: 1

Related Questions