How to select variables with numeric suffixes lower than a value

Question

I have a data frame similar to this one.

df <- data.frame(id=c(1,2,3), tot_1=runif(3, 0, 100), tot_2=runif(3, 0, 100), tot_3=runif(3, 0, 100), tot_4=runif(3, 0, 100))

I want to select or make an operation only with those with suffixes lower than 3.

#select
df <- df %>% select(id, tot_1, tot_2)
#or sum
df <- df %>% mutate(sumVar = rowSums(across(c(tot_1, tot_2))))

However, in my real data, there are many more variables and not in order. So how could I select them without doing it manually?

akrun · Accepted Answer · 2021-09-10T19:42:37.913

2

We may use matches

df %>% 
    mutate(sumVar = rowSums(across(matches('tot_[1-2]$'))))

If we need to be more flexible, extract the digit part from the column names that starts with 'tot', subset based on the condition and use that new names

library(stringr)
nm1 <- str_subset(names(df), 'tot')
nm2 <-  nm1[readr::parse_number(nm1) <3]
df %>%
    mutate(sumVar = rowSums(across(all_of(nm2))))

edited Sep 10 '21 at 19:42

answered Sep 10 '21 at 19:22

akrun

874,273
37
540
662

I my real data I have a range greater than 10 so I would need something like matches('tot_[1-112]$'), but it does not work with this range – user2246905 Sep 10 '21 at 19:40

GuedesBF · Answer 2 · 2021-09-11T11:47:32.247

Solution with num_range

This is the rare case for the often forgotten num_range selection helper from dplyr, which extracts the numbers from the names in a single step, then selects a range:

determine the threshold

suffix_threshold <- 3

Select( )

library(dplyr)

df %>% select(id, num_range(prefix='tot_',
                            range=seq_len(suffix_threshold-1)))

  id    tot_1    tot_2
1  1 26.75082 26.89506
2  2 21.86453 18.11683
3  3 51.67968 51.85761

mutate() with rowSums()

library(dplyr)

df %>% mutate(sumVar = across(num_range(prefix='tot_', range=seq_len(suffix_threshold-1)))%>%
                      rowSums)

  id    tot_1    tot_2    tot_3    tot_4    sumVar
1  1 26.75082 26.89506 56.27829 71.79353  53.64588
2  2 21.86453 18.11683 12.91569 96.14099  39.98136
3  3 51.67968 51.85761 25.63676 10.01408 103.53730

@user2246905 , does it work on your dataset? – GuedesBF Sep 10 '21 at 21:43 — GuedesBF, Sep 10 '21 at 21:43

score 0 · Answer 3 · answered Sep 11 '21 at 04:07

Here is a base R way -

cols <- grep('tot_', names(df), value = TRUE)

#Select
df[c('id', cols[as.numeric(sub('tot_', '',cols)) < 3])]

#  id     tot_1    tot_2
#1  1 75.409112 30.59338
#2  2  9.613496 44.96151
#3  3 58.589574 64.90672

#Rowsums
df$sumVar <- rowSums(df[cols[as.numeric(sub('tot_', '',cols)) < 3]])
df

#  id     tot_1    tot_2    tot_3     tot_4    sumVar
#1  1 75.409112 30.59338 59.82815 50.495758 106.00250
#2  2  9.613496 44.96151 84.19916  2.189482  54.57501
#3  3 58.589574 64.90672 18.17310 71.390459 123.49629

How to select variables with numeric suffixes lower than a value

3 Answers3