Appending a row of sums for each level of a factor

Question

I want to append a row of sums for each Reg like this

   Reg   Res    Pop
1      Total 1000915
2    A Urban 500414
3    A Rural 500501

4     Total  999938
5    B Urban 499922
6    B Rural 500016

7      Total 1000912
8    C Urban 501638
9    C Rural 499274

10     Total  999629
11    D Urban 499804
12    D Rural 499825

13     Total 1000303
14   E Urban 499917
15   E Rural 500386

MWE is below:

Reg <- rep(LETTERS[1:5], each = 2)
Res <- rep(c("Urban", "Rural"), times = 5)
set.seed(12345)
Pop <- rpois(n = 10, lambda = 500000)
df <- data.frame(Reg, Res, Pop)

df
   Reg   Res    Pop
1    A Urban 500414
2    A Rural 500501
3    B Urban 499922
4    B Rural 500016
5    C Urban 501638
6    C Rural 499274
7    D Urban 499804
8    D Rural 499825
9    E Urban 499917
10   E Rural 500386

df %>%
   group_by(Reg) %>%
   summarise(Total = sum(Pop))
# A tibble: 5 x 2
     Reg   Total
  <fctr>   <int>
1      A 1000915
2      B  999938
3      C 1000912
4      D  999629
5      E 1000303

Edited

I would like to have both dplyr and data.table solutions.

Answer 1

You can add an extra Res column to the summary and then bind_rows with the original data frame:

df %>%
    group_by(Reg) %>%
    summarise(Pop = sum(Pop), Res = 'Total') %>%
    bind_rows(df) %>% 
    arrange(Reg)

# A tibble: 15 x 3
#     Reg     Pop   Res
#   <chr>   <int> <chr>
# 1     A 1000915 Total
# 2     A  500414 Urban
# 3     A  500501 Rural
# 4     B  999938 Total
# 5     B  499922 Urban
# 6     B  500016 Rural
# 7     C 1000912 Total
# 8     C  501638 Urban
# 9     C  499274 Rural
#10     D  999629 Total
#11     D  499804 Urban
#12     D  499825 Rural
#13     E 1000303 Total
#14     E  499917 Urban
#15     E  500386 Rural

---

A corresponding data.table solution:

dt <- setDT(df)
rbindlist(list(dt[, .(Pop = sum(Pop), Res = 'Total'), Reg], dt), use.names = TRUE)

Answer 2

lapply(split(df, df$Reg),
       function(a) rbind(data.frame(Reg = a$Reg[1],
                                    Res = "Total",
                                    Pop = sum(a$Pop)),
                         a))
$A
  Reg   Res     Pop
1   A Total 1000915
2   A Urban  500414
3   A Rural  500501

$B
  Reg   Res    Pop
1   B Total 999938
3   B Urban 499922
4   B Rural 500016

$C
  Reg   Res     Pop
1   C Total 1000912
5   C Urban  501638
6   C Rural  499274

$D
  Reg   Res    Pop
1   D Total 999629
7   D Urban 499804
8   D Rural 499825

$E
   Reg   Res     Pop
1    E Total 1000303
9    E Urban  499917
10   E Rural  500386

You could convert the whole thing to data.frame by using do.call(rbind, ...) if you want