How to insert rows in specific indices of dataframe containing sum of few rows above only in R pipe-CodePudding

for dataframe below,

df <- data.frame(id = c(rep(101, 4), rep(202, 3)),
                status = c("a","b","c","d", "a", "b", "c"),
                wt = c(100,200,100,105, 20,22,25),
                ht = c(5.3,5.2,5,5.1, 4.3,4.2,4.1))
    
df
   id status  wt  ht
1 101      a 100 5.3
2 101      b 200 5.2
3 101      c 100 5.0
4 101      d 105 5.1
5 202      a  20 4.3
6 202      b  22 4.2
7 202      c  25 4.1

I want to get output below:

> output
   id status  wt   ht
1 101      a 100  5.3
2 101      b 200  5.2
3 101      c 100  5.0
4 101      d 105  5.1
5 101    sum 505 20.6
6 202      a  20  4.3
7 202      b  22  4.2
8 202      c  25  4.1
9 202    sum  67 12.6

df is coming from a sequence of pipes and I do not want to stop it and do something like

output <- rbind(df[1:4,],
                c(101, "sum", colSums(df[1:4, c(3,4)])),
                df[5:7,],
                c(202, "sum", colSums(df[5:7, c(3,4)])))

I am looking for a neat , good looking! way of achieving this. Any help or idea is so appreciated.

df %>%
   ....

CodePudding user response：

We may use adorn_totals after grouping by 'id'.

library(dplyr)
library(janitor)
df %>% 
  group_by(id) %>%
  group_modify(~ .x %>% 
      adorn_totals(cols = c(wt, ht), name = 'sum')) %>%
  ungroup

-output

# A tibble: 9 × 4
     id status    wt    ht
  <dbl> <chr>  <dbl> <dbl>
1   101 a        100   5.3
2   101 b        200   5.2
3   101 c        100   5  
4   101 d        105   5.1
5   101 sum      505  20.6
6   202 a         20   4.3
7   202 b         22   4.2
8   202 c         25   4.1
9   202 sum       67  12.6

CodePudding user response：

With tibble::add_row, a bit more flexible:

library(tidyverse)
df %>% 
  group_by(id) %>% 
  group_modify(~ .x %>% add_row(status = "sum", wt = sum(.$wt), ht = sum(.$ht)))

# A tibble: 9 × 4
# Groups:   id [2]
     id status    wt    ht
  <dbl> <chr>  <dbl> <dbl>
1   101 a        100   5.3
2   101 b        200   5.2
3   101 c        100   5  
4   101 d        105   5.1
5   101 sum      505  20.6
6   202 a         20   4.3
7   202 b         22   4.2
8   202 c         25   4.1
9   202 sum       67  12.6

CodePudding user response：

Another possible solution, based on colSums and dplyr:

library(dplyr)
              
df %>% 
  group_by(id) %>% 
  group_modify(~ add_row(.x, status = "Sum", !!!colSums(.[2:3]))) %>% 
  ungroup

#> # A tibble: 9 × 4
#>      id status    wt    ht
#>   <dbl> <chr>  <dbl> <dbl>
#> 1   101 a        100   5.3
#> 2   101 b        200   5.2
#> 3   101 c        100   5  
#> 4   101 d        105   5.1
#> 5   101 Sum      505  20.6
#> 6   202 a         20   4.3
#> 7   202 b         22   4.2
#> 8   202 c         25   4.1
#> 9   202 Sum       67  12.6