How to combine transmute with grep function?

user_14066927 0
Viewed 85

I'm trying to find a way to create a new table with variables using the rowSums() function from an existing dataframe. For example, my existing dataframe is called 'asn' and I want to sum up the values for each row of all variables which contain "2011" in the variable title. I want a new table consisting of just one column called asn_y2011 which contains the sum of each row using the variables containing "2011"

Data

structure(list(row = 1:3, south_2010 = c(1L, 5L, 7L), south_2011 = c(4L, 
0L, 4L), south_2012 = c(5L, 8L, 6L), north_2010 = c(3L, 4L, 1L
), north_2011 = c(2L, 6L, 0L), north_2012 = c(1L, 1L, 2L)), class = "data.frame", row.names = c(NA, 
-3L))

The existing 'asn' dataframe looks like this

row south_2010 south_2011 south_2012 north_2010 north_2011 north_2012
  1      1           4         5          3          2          1
  2      5           0         8          4          6          1
  3      7           4         6          1          0          2

I'm trying to use the following function:

asn %>%   
   transmute(asn_y2011 = rowSums(, grep("2011")))

to get something like this

row    asn_y2011
 1         6
 2         6
 3         4
dplyr r
5 Answers

Continuing with your code, grep() should work like this:

library(dplyr)

asn %>%
  transmute(row, asn_y2011 = rowSums(.[grep("2011", names(.))]))

#   row asn_y2011
# 1   1         6
# 2   2         6
# 3   3         4

Or you can use tidy selection in c_across():

asn %>%
  rowwise() %>% 
  transmute(row, asn_y2011 = sum(c_across(contains("2011")))) %>%
  ungroup()
user_100689850

Another base R option using rowSums

cbind(asn[1],asn_y2011 = rowSums(asn[grep("2011",names(asn))]))

which gives

  row asn_y2011
1   1         6
2   2         6
3   3         4
user_121587570

An option in base R with Reduce

cbind(df['row'], asn_y2011 = Reduce(`+`, df[endsWith(names(df), '2011')]))
#  row asn_y2011
#1   1         6
#2   2         6
#3   3         4

data

df <- structure(list(row = 1:3, south_2010 = c(1L, 5L, 7L), south_2011 = c(4L, 
0L, 4L), south_2012 = c(5L, 8L, 6L), north_2010 = c(3L, 4L, 1L
), north_2011 = c(2L, 6L, 0L), north_2012 = c(1L, 1L, 2L)), 
class = "data.frame", row.names = c(NA, 
-3L))
user_37322710

I think that this code will do what you want:

library(magrittr)
tibble::tibble(row = 1:3, south_2011 = c(4, 0, 4), north_2011 = c(2, 6, 0)) %>%
  tidyr::gather(- row, key = "key", value = "value") %>%
  dplyr::mutate(year = purrr::map_chr(.x = key, .f = function(x)stringr::str_split(x, pattern = "_")[[1]][2])) %>%
  dplyr::group_by(row, year) %>%
  dplyr::summarise(sum(value))

I first load the package magrittr so that I can use the pipe, %>%. I've explicitly listed the packages from which the functions are exported, but you are welcome to load the packages with library if you like.

I then create a tibble, or data frame, like what you specify.

I use gather to reorganize the data frame before creating a new variable, year. I then summarise the counts by value of row and year.

user_67972520

You can try this approach

library(tidyverse)
df2 <- df %>% 
  select(grep("_2011|row", names(df), value = TRUE)) %>% 
  rowwise() %>% 
  mutate(asn_y2011 = sum(c_across(south_2011:north_2011))) %>% 
  select(row, asn_y2011)
  
#     row asn_y2011
#   <int>     <int>
# 1     1         6
# 2     2         6
# 3     3         4

Data

df <- structure(list(row = 1:3, south_2010 = c(1L, 5L, 7L), south_2011 = c(4L, 0L, 4L), south_2012 = c(5L, 8L, 6L), north_2010 = c(3L, 4L, 1L), north_2011 = c(2L, 6L, 0L), north_2012 = c(1L, 1L, 2L)), class = "data.frame", row.names = c(NA,-3L))
user_123363050
Related