Sum multiple rows by columns

I have this df:

test <- structure(list(tipo_prestacion = c("TAC", NA, NA, "RESONANCIA MAGNÉTICA", 
                                           NA, NA, "ECOGRAFÍA", NA, NA, "MAMOGRAFÍA", NA, NA), tipo_cita = c("1 (LE Estructural)", 
                                                                                                             "2 (Aplaz. voluntario)", "3 (Aplaz. clinico)", "1 (LE Estructural)", 
                                                                                                             "2 (Aplaz. voluntario)", "3 (Aplaz. clinico)", "1 (LE Estructural)", 
                                                                                                             "2 (Aplaz. voluntario)", "3 (Aplaz. clinico)", "1 (LE Estructural)", 
                                                                                                             "2 (Aplaz. voluntario)", "3 (Aplaz. clinico)"), h_territ = c(113, 
                                                                                                                                                                             17, 517, 402, 11, 1150, 370, 18, 1041, NA, NA, 35), region = c(3614, 
                                                                                                                                                                                                                                                     2924, 2504, 3724, 2965, 11146, 10369, 3382, 9647, 797, 172, 581
                                                                                                                                                                             )), row.names = c(NA, -12L), class = c("tbl_df", "tbl", "data.frame"
                                                                                                                                                                             ))

where tipo_prestacion, tipo_cita, h_territ and region are four columns with four types of medical tests (TAC, RESONANCIA MAGNETICA, ECOGRAFIA, MAMOGRAFIA) that have each three type of citations (1,2,3 with his respectives names). I want to obtain the sum of each medical test (including all type of his citations) for h_territ and region and add it to a new row, called 'Total'.

Is this close to what you are looking for?

test <- structure(list(tipo_prestacion = c("TAC", NA, NA, "RESONANCIA MAGNÉTICA", 
                                           NA, NA, "ECOGRAFÍA", NA, NA, "MAMOGRAFÍA", NA, NA), 
                       tipo_cita = c("1 (LE Estructural)", "2 (Aplaz. voluntario)", "3 (Aplaz. clinico)", "1 (LE Estructural)", 
                                     "2 (Aplaz. voluntario)", "3 (Aplaz. clinico)", "1 (LE Estructural)", 
                                     "2 (Aplaz. voluntario)", "3 (Aplaz. clinico)", "1 (LE Estructural)", 
                                     "2 (Aplaz. voluntario)", "3 (Aplaz. clinico)"), 
                       h_territ = c(113, 17, 517, 402, 11, 1150, 370, 18, 1041, NA, NA, 35), 
                       region = c(3614, 2924, 2504, 3724, 2965, 11146, 10369, 3382, 9647, 797, 172, 581
                       )), 
                  row.names = c(NA, -12L), class = c("tbl_df", "tbl", "data.frame"))
library(tidyr)
library(dplyr)
test <- test |> fill(tipo_prestacion, .direction = "down")
testSummary <- test  |> 
  summarize(h_territ = sum(h_territ, na.rm = TRUE),
            region = sum(region, na.rm = TRUE),.by = "tipo_prestacion") |> 
  mutate(tipo_cita = "Total")
test <- bind_rows(test, testSummary) |> arrange(tipo_prestacion, tipo_cita)
test
#> # A tibble: 16 × 4
#>    tipo_prestacion      tipo_cita             h_territ region
#>    <chr>                <chr>                    <dbl>  <dbl>
#>  1 ECOGRAFÍA            1 (LE Estructural)         370  10369
#>  2 ECOGRAFÍA            2 (Aplaz. voluntario)       18   3382
#>  3 ECOGRAFÍA            3 (Aplaz. clinico)        1041   9647
#>  4 ECOGRAFÍA            Total                     1429  23398
#>  5 MAMOGRAFÍA           1 (LE Estructural)          NA    797
#>  6 MAMOGRAFÍA           2 (Aplaz. voluntario)       NA    172
#>  7 MAMOGRAFÍA           3 (Aplaz. clinico)          35    581
#>  8 MAMOGRAFÍA           Total                       35   1550
#>  9 RESONANCIA MAGNÉTICA 1 (LE Estructural)         402   3724
#> 10 RESONANCIA MAGNÉTICA 2 (Aplaz. voluntario)       11   2965
#> 11 RESONANCIA MAGNÉTICA 3 (Aplaz. clinico)        1150  11146
#> 12 RESONANCIA MAGNÉTICA Total                     1563  17835
#> 13 TAC                  1 (LE Estructural)         113   3614
#> 14 TAC                  2 (Aplaz. voluntario)       17   2924
#> 15 TAC                  3 (Aplaz. clinico)         517   2504
#> 16 TAC                  Total                      647   9042

Created on 2023-10-17 with reprex v2.0.2

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.