Row sums - filter according to key

Jakub_Komarek · March 28, 2022, 1:43pm

Hi,

I have dataframes 'sample' and 'key'.

sample <-  structure(list(ID_OKRSKY = c("12355", "365", "11325", "1914", 
                                        "10794"), 
HLASY_01 = c("3", "24", "0", "40", "4"), 
HLASY_02 = c("7", "7", "0", "9", "25"), 
HLASY_03 = c("2", "35", "1", "6", "1"), 
HLASY_04 = c("12", "9", "1", "15", "2"), 
HLASY_05 = c("3", "3", "0", "2", "21"), 
HLASY_06 = c("1", "4", "0", "2", "0" ), 
HLASY_07 = c("0", "24", "0", "20", "17"), 
HLASY_08 = c("0",  "8", "0", "1", "3"), 
HLASY_09 = c("2", "3", "0", "3", "13"), 
HLASY_10 = c("2", "17", "0", "14", "1"), 
HLASY_11 = c("0", "17", "0", "0", "6"), 
HLASY_12 = c("0", "12", "0", "16",  "1"), 
HLASY_13 = c("0", "3", "0", "0", "6"), 
HLASY_14 = c("13", "2", "0", "2", "0"), 
HLASY_15 = c("0", "1", "0", "1", "11"), 
HLASY_16 = c("0", "19", "0", "0", "0"), 
HLASY_17 = c("5", "0", "1", "3", "6"), 
HLASY_18 = c("0", "4", "0", "0", "0" ), 
HLASY_19 = c("1", "3", "0", "1", "11"), 
HLASY_20 = c("0", "5", "0", "6", "0"), 
HLASY_21 = c("0", "3", "0", "1", "0" ), 
HLASY_22 = c("0", "4", "0", "2", "0"), 
HLASY_23 = c("0", "1", "0", "0", "0"), 
HLASY_24 = c("0", "1", "0", "1", "0" ), 
HLASY_25 = c("0", "0", "0", "0", "0"), 
HLASY_26 = c("0",    "1", "0", "0", "0"), 
HLASY_27 = c("0", "0", "0", "3", "0"),
HLASY_28 = c("0", "8", "0", "0", "0"), 
HLASY_29 = c("0", "1", "0", "0", "0"), 
HLASY_30 = c("0", "2", "0", "0", "0" ), 
HLASY_31 = c("0", "4", "0", "0", "0"), 
HLASY_32 = c("0",  "0", "0", "0", "0"), 
HLASY_33 = c("0", "2", "0", "0", "0"), 
HLASY_34 = c("0", "1", "0", "1", "0"), 
HLASY_35 = c("0",  "0", "0", "0", "0"), 
HLASY_36 = c("0", "3", "0", "0", "0"), 
kraj = c("CZ072", "CZ010", "CZ053", "CZ021", "CZ053")), 
row.names = c(NA, -5L), 
class = c("tbl_df", "tbl", "data.frame"))

###
key <- list(CZ010 = c("1", "2", "4", "5", "6", "8", "9", "10", "12", "13", "14", "15", "17", "19", "21", "23", "25", "27", "29", "31",  "33", "35"), 
CZ021= c("2", "3", "5", "6", "8", "9", "11", "13", "15", "17", "19", "21", "23", "25", "27", "29", "31", "33"), 
CZ053 = c("1",  "3", "4", "6", "8", "10", "12", "14", "16", "18"), 
CZ072 = c("1", "3", "5", "7", "9", "11", "13", "15", "17", "19", "21"), 
CZ080 = c("1", "2", "3", "5", "6", "7", "9",  "11", "13", "15", "17", "19", "21", "23", "25", "27", "29",  "31", "33", "35"))

for each row identified by 'ID_OKRSKY', I would like to compute the sum of columns HLASY_xx accroding to a list 'key'. If a row has a value of 'kraj' "CZ072", I would look into a list 'key' and find associated numeric vector for which columns of HLASY should be included in the sum. In case of "CZ072", I would get c("1", "3", "5", "7", "9", "11", "13", "15", "17", "19", "21"), so I would like to have sum of

TOTAL = HLASY_01 + HLASY_03 + HLASY_05 + HLASY_07 .... and so on.

Many thanks,

Jakub

FJCC · March 28, 2022, 3:27pm

Is this what you want? At the end I display only two columns to the output is legible.

library(tidyverse)
#> Warning: package 'tibble' was built under R version 4.1.2
sample <-  structure(list(ID_OKRSKY = c("12355", "365", "11325", "1914", 
                                        "10794"), 
                          HLASY_01 = c("3", "24", "0", "40", "4"), 
                          HLASY_02 = c("7", "7", "0", "9", "25"), 
                          HLASY_03 = c("2", "35", "1", "6", "1"), 
                          HLASY_04 = c("12", "9", "1", "15", "2"), 
                          HLASY_05 = c("3", "3", "0", "2", "21"), 
                          HLASY_06 = c("1", "4", "0", "2", "0" ), 
                          HLASY_07 = c("0", "24", "0", "20", "17"), 
                          HLASY_08 = c("0",  "8", "0", "1", "3"), 
                          HLASY_09 = c("2", "3", "0", "3", "13"), 
                          HLASY_10 = c("2", "17", "0", "14", "1"), 
                          HLASY_11 = c("0", "17", "0", "0", "6"), 
                          HLASY_12 = c("0", "12", "0", "16",  "1"), 
                          HLASY_13 = c("0", "3", "0", "0", "6"), 
                          HLASY_14 = c("13", "2", "0", "2", "0"), 
                          HLASY_15 = c("0", "1", "0", "1", "11"), 
                          HLASY_16 = c("0", "19", "0", "0", "0"), 
                          HLASY_17 = c("5", "0", "1", "3", "6"), 
                          HLASY_18 = c("0", "4", "0", "0", "0" ), 
                          HLASY_19 = c("1", "3", "0", "1", "11"), 
                          HLASY_20 = c("0", "5", "0", "6", "0"), 
                          HLASY_21 = c("0", "3", "0", "1", "0" ), 
                          HLASY_22 = c("0", "4", "0", "2", "0"), 
                          HLASY_23 = c("0", "1", "0", "0", "0"), 
                          HLASY_24 = c("0", "1", "0", "1", "0" ), 
                          HLASY_25 = c("0", "0", "0", "0", "0"), 
                          HLASY_26 = c("0",    "1", "0", "0", "0"), 
                          HLASY_27 = c("0", "0", "0", "3", "0"),
                          HLASY_28 = c("0", "8", "0", "0", "0"), 
                          HLASY_29 = c("0", "1", "0", "0", "0"), 
                          HLASY_30 = c("0", "2", "0", "0", "0" ), 
                          HLASY_31 = c("0", "4", "0", "0", "0"), 
                          HLASY_32 = c("0",  "0", "0", "0", "0"), 
                          HLASY_33 = c("0", "2", "0", "0", "0"), 
                          HLASY_34 = c("0", "1", "0", "1", "0"), 
                          HLASY_35 = c("0",  "0", "0", "0", "0"), 
                          HLASY_36 = c("0", "3", "0", "0", "0"), 
                          kraj = c("CZ072", "CZ010", "CZ053", "CZ021", "CZ053")), 
                     row.names = c(NA, -5L), 
                     class = c("tbl_df", "tbl", "data.frame"))

###
key <- list(CZ010 = c("1", "2", "4", "5", "6", "8", "9", "10", "12", "13", "14", "15", "17", "19", "21", "23", "25", "27", "29", "31",  "33", "35"), 
            CZ021= c("2", "3", "5", "6", "8", "9", "11", "13", "15", "17", "19", "21", "23", "25", "27", "29", "31", "33"), 
            CZ053 = c("1",  "3", "4", "6", "8", "10", "12", "14", "16", "18"), 
            CZ072 = c("1", "3", "5", "7", "9", "11", "13", "15", "17", "19", "21"), 
            CZ080 = c("1", "2", "3", "5", "6", "7", "9",  "11", "13", "15", "17", "19", "21", "23", "25", "27", "29",  "31", "33", "35"))

#Make all the values numeric#
sample <- sample |> mutate(across(.cols = -kraj, .fns = as.numeric))
key <- map(key, as.numeric)

MyFunc <- function(Nm) {
  Cols <- key[[Nm]]
  RowSum <- sample |> filter(kraj == Nm) |> 
    rowwise() |> 
    mutate(SUM = sum(c_across(cols = Cols + 1))) #use Cols + 1 to skip over ID_OKRSKY
  return(RowSum)
}
AllKraj <- unique(sample$kraj)

#for each value of kraj, calculate the row sum
SUMS <- map_dfr(AllKraj, MyFunc)
print(select(SUMS,ID_OKRSKY,SUM))
#> # A tibble: 5 x 2
#> # Rowwise: 
#>   ID_OKRSKY   SUM
#>       <dbl> <dbl>
#> 1     12355    16
#> 2       365   107
#> 3     11325     2
#> 4     10794    12
#> 5      1914    32

^{Created on 2022-03-28 by the reprex package (v2.0.1)}

Jakub_Komarek · March 29, 2022, 8:25am

FJCC:

#Make all the values numeric#
sample <- sample |> mutate(across(.cols = -kraj, .fns = as.numeric))
key <- map(key, as.numeric)

MyFunc <- function(Nm) {
  Cols <- key[[Nm]]
  RowSum <- sample |> filter(kraj == Nm) |> 
    rowwise() |> 
    mutate(SUM = sum(c_across(cols = Cols + 1))) #use Cols + 1 to skip over ID_OKRSKY
  return(RowSum)
}
AllKraj <- unique(sample$kraj)

#for each value of kraj, calculate the row sum
SUMS <- map_dfr(AllKraj, MyFunc)
print(select(SUMS,ID_OKRSKY,SUM))
#> # A tibble: 5 x 2
#> # Rowwise: 
#>   ID_OKRSKY   SUM
#>       <dbl> <dbl>
#> 1     12355    16
#> 2       365   107
#> 3     11325     2
#> 4     10794    12
#> 5      1914    32

Many thanks, that's exactly what I wanted even though I have not seen an operator ' |>' before.

FJCC · March 29, 2022, 2:07pm

The |> operator is a relatively new one in base R and it is a "pipe", sending the object on it left into the function on its right. You may have seen the very similar operator %>% from the magrittr package.

system · April 5, 2022, 2:08pm

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.