Add new rows in a R dataframe

Question

This is my dataframe

df <- data.frame(Var=c("Var1", "Var1","Var1","Var1","Var1","Var1"),
             measure=c("M1","M1","M1","M1","M1","M1"),
             grType = c("Sex","Sex","Agegr","Agegr","Agegr","Agegr"),
             gr=c('Male', 'Female', '<20', '20-39', '40-69', '70+'),
             dem=c(39, 42, 11, 19, 22, 29),
             num=c(22, 19, 3, 11, 15, 12))

I want to add a new row as total grType and recode age groups in <40 and 40+. my desired output should look like:

df <- data.frame(Var=c("Var1", "Var1","Var1","Var1","Var1","Var1","Var1","Var1","Var1"),
             measure=c("M1","M1","M1","M1","M1","M1","M1","M1","M1"),
             grType = c("Sex","Sex","Agegr","Agegr","Agegr","Agegr","newAgegr","newAgegr", "Total"),
             gr=c('Male', 'Female', '<20', '20-39', '40-69', '70+', "<40", "40+", "Total"),
             dem=c(39, 42, 11, 19, 22, 29, 30, 49, 81),
             num=c(22, 19, 3, 11, 15, 12,14,27,41))

I tried the following but not working

df <- rbind(df, c("Var1", "M1", "Agegr", "<40", sum(df[df$gr %in% c('<20', '20-39'), "dem"]), sum(df[df$gr %in% c('<20', '20-39'), "num"])))

df <- rbind(df, c("Var1", "M1", "newAgegr", "40+", df[df$grType == "Total", "dem"] - df[df$grType == "Agegr", "dem"], df[df$grType == "Total", "num"] - df[df$grType == "Agegr", "num"]))

df <- rbind(df, c("Var1", "M1", "Total", "Total", sum(df[df$grType %in% c("Sex", "newAgegr"), "dem"]), sum(df[df$grType %in% c("Sex", "newAgegr"), "num"])))

But received some error

Error in sum(df[df$gr %in% c("<20", "20-39"), "dem"]) : invalid 'type' (character) of argument

Answer 1

Perhaps this is a good start, using dplyr:

df2 <- df %>%
  filter(grType == "Agegr") %>%
  group_by(Var, measure, grp = grepl("20", gr)) %>%
  reframe(grType = "newAgegr", gr = if_else(grepl("20", first(gr)), "<40", "40+"), across(c(dem, num), ~ sum(.))) %>%
  select(-grp)
cc()
# (converted)
df2
# # A tibble: 2 × 6
#   Var   measure grType   gr      dem   num
#   <chr> <chr>   <chr>    <chr> <dbl> <dbl>
# 1 Var1  M1      newAgegr 40+      51    27
# 2 Var1  M1      newAgegr <40      30    14

df3 <- df %>%
  group_by(Var, measure) %>%
  summarize(grType = "Total", gr = "Total", across(c(dem, num), ~ sum(.)))
df3
# # A tibble: 1 × 6
# # Groups:   Var [1]
#   Var   measure grType gr      dem   num
#   <chr> <chr>   <chr>  <chr> <dbl> <dbl>
# 1 Var1  M1      Total  Total   162    82

bind_rows(df, df2, df3)
#    Var measure   grType     gr dem num
# 1 Var1      M1      Sex   Male  39  22
# 2 Var1      M1      Sex Female  42  19
# 3 Var1      M1    Agegr    <20  11   3
# 4 Var1      M1    Agegr  20-39  19  11
# 5 Var1      M1    Agegr  40-69  22  15
# 6 Var1      M1    Agegr    70+  29  12
# 7 Var1      M1 newAgegr    40+  51  27
# 8 Var1      M1 newAgegr    <40  30  14
# 9 Var1      M1    Total  Total 162  82