CODEWITHSUNDEEP
rdplyr
zesla
zesla

Reputation: 11843

create a new column which is the sum of specific columns (selected by their names) in dplyr

My question is how to create a new column which is the sum of some specific columns (selected by their names) in dplyr. For example, with iris dataset, I create a new columns called Petal, which is the sum of Petal.Length and Petal.Width.

iris %>% mutate(Petal = Petal.Length+Petal.Width)

Now imagine I have a dataset with 20 columns with 'Petal' in their names. I want to create a column 'Petal' which sum up all those columns. I definitely do not want to type all the columns names in my code. Feel like there should be achievable with one line of code in dplyr. Appreciate if anyone can help.

Upvotes: 15

Views: 17778

Answers (5)

LMc
LMc

Reputation: 18752

I gave a similar answer here and here. rowSums is the best option if your aggregating function is sum:

iris %>% 
  mutate(Petal = rowSums(pick(starts_with("Petal"))))

But you can use c_across and rowwise:

iris %>% 
  rowwise() %>% 
  mutate(Petal = sum(c_across(starts_with("Petal")))) %>% 
  ungroup()

The big advantage is that you can use other functions besides sum. rowSums is a better option because it's faster, but if you want to apply another function other than sum this is a good option.

You can use any of the tidyselect options within c_across and pick to select columns by their name, position, class, a range of consecutive columns, etc.

Output

  Sepal.Length Sepal.Width Petal.Length Petal.Width Species Petal
          <dbl>       <dbl>        <dbl>       <dbl> <fct>   <dbl>
 1          5.1         3.5          1.4         0.2 setosa    1.6
 2          4.9         3            1.4         0.2 setosa    1.6
 3          4.7         3.2          1.3         0.2 setosa    1.5
 4          4.6         3.1          1.5         0.2 setosa    1.7
 5          5           3.6          1.4         0.2 setosa    1.6
 6          5.4         3.9          1.7         0.4 setosa    2.1
 7          4.6         3.4          1.4         0.3 setosa    1.7
 8          5           3.4          1.5         0.2 setosa    1.7
 9          4.4         2.9          1.4         0.2 setosa    1.6
10          4.9         3.1          1.5         0.1 setosa    1.6
# ... with 140 more rows

Upvotes: 2

Evan Hegarty
Evan Hegarty

Reputation: 31

iris %>% mutate(Petal = rowSums(.[grep("Petal", names(.))], na.rm = T))

This should work.

names(.) selects the names from your dataframe, grep searches through these to find ones that match a regex ("Petal"), and rowSums adds the value of each column, assigning them to your new variable Petal. The ".[]" syntax is a work-around for the way that dplyr passes column names.

Upvotes: 2

skd
skd

Reputation: 1977

Using reduce() from purrr is slightly faster than rowSums and definately faster than apply, since you avoid iterating over all the rows and just take advantage of the vectorized operations:

library(purrr)
library(dplyr)
iris %>% mutate(Petal = reduce(select(., starts_with("Petal")), `+`))

See this for timings

Upvotes: 5

ssp3nc3r
ssp3nc3r

Reputation: 3822

More generally, create a key for each observation (e.g., the row number using mutate below), move the columns of interest into two columns, one holds the column name, the other holds the value (using melt below), group_by observation, and do whatever calculations you want. Below, I add a column using mutate that sums all columns containing the word 'Petal' and finally drop whatever variables I don't want (using select).

require(dplyr)
require(reshape2)

iris %>%
  mutate(observation = 1:nrow(iris)) %>% 
  melt(
    measure.vars = grep("Petal", colnames(iris)), 
    variable.name = 'variable', 
    value.name = 'value') %>% 
  group_by(observation) %>%
  mutate(Petal = sum(value)) %>% 
  select(-observation, -variable, -value)

While the above can be shortened, I thought this version would provide some guidance.

Upvotes: 1

Gregor Thomas
Gregor Thomas

Reputation: 146224

I agree with MrFlick that tidying your data is preferable---especially if you want to do anything other than sum these columns---but here is one option:

iris %>% mutate(
   Petal = rowSums(select(., starts_with("Petal")))
)

    Sepal.Length Sepal.Width Petal.Length Petal.Width    Species Petal
1            5.1         3.5          1.4         0.2     setosa   1.6
2            4.9         3.0          1.4         0.2     setosa   1.6
3            4.7         3.2          1.3         0.2     setosa   1.5
4            4.6         3.1          1.5         0.2     setosa   1.7
5            5.0         3.6          1.4         0.2     setosa   1.6
...

See ?select helper options other than starts_with() for selecting columns.

Upvotes: 25

Related Questions