dplyr rowwise by some columns

Question

What is the dplyr way to apply a function rowwise for some columns. For example I want to Grab all the V, columns and turn them into percents based on the row sums. I show how to do it in base. What about in a dplyr chain. It'd nice to see in data.table form as well (though preference would go to a dplyr solution here).

x <- data.frame(A=LETTERS[1:5], as.data.frame(matrix(sample(0:5, 25, T), ncol=5)))

data.frame(x[1], x[-1]/rowSums(x[-1]))


##   A        V1        V2        V3         V4         V5
## 1 A 0.1428571 0.2142857 0.2142857 0.35714286 0.07142857
## 2 B 0.2000000 0.2000000 0.1500000 0.20000000 0.25000000
## 3 C 0.3571429 0.2857143 0.0000000 0.07142857 0.28571429
## 4 D 0.1904762 0.2380952 0.1904762 0.23809524 0.14285714
## 5 E 0.2000000 0.2500000 0.1500000 0.25000000 0.15000000

library(dplyr)

props <- function(x) round(x/sum(x), 2)

# does not work
x %>%
    rowwise()
    mutate(props(matches("^.{2}$")))

Maybe x %>% rowwise() %>% select(matches("^.{2}$")) %>% props %>% cbind(x[1], .)? The second half isn't really dplyrey though — Rich Scriven
– Rich Scriven, Commented Apr 9, 2016 at 21:27
I'm not a dplyr expert, but can't you just use rowSums in dplyr too? Something like props <- function(x, y) round(x/y, 2) ; x %>% mutate(Total = rowSums(.[-1])) %>% mutate_each(funs(./Total), -c(A, Total)). Though both rowSums and rowwise should be inefficient. I would go with Reduce(`+`, .[-1])) instead, if you don't have NAs. — David Arenburg
– David Arenburg, Commented Apr 9, 2016 at 21:30
@DavidArenburg Nice can you throw down as an answer. It works. — Tyler Rinker
– Tyler Rinker, Commented Apr 9, 2016 at 21:39
@Frank true no need. I'll remove, though it sould allow anyone to just run the code with no need to type library etc. to get dplyr. — Tyler Rinker
– Tyler Rinker, Commented Apr 9, 2016 at 21:49
A "known data.table guy" who is near hadley in the SO dplyr answers leaderboard :) stackoverflow.com/tags/dplyr/topusers — Frank
– Frank, Commented Apr 9, 2016 at 22:11

Frank · Accepted Answer · 2016-04-09 21:37:54Z

7

In data.table, you can do

library(data.table)
setDT(x)

x[, grep("^V",names(DT)) := .SD/Reduce(`+`, .SD), .SDcols = V1:V5]

   A         V1        V2        V3         V4         V5
1: A 0.28571429 0.0000000 0.2857143 0.07142857 0.35714286
2: B 0.23076923 0.2307692 0.3076923 0.15384615 0.07692308
3: C 0.44444444 0.0000000 0.4444444 0.00000000 0.11111111
4: D 0.07142857 0.3571429 0.1428571 0.07142857 0.35714286
5: E 0.00000000 0.2222222 0.3333333 0.44444444 0.00000000

To compute the denominator with NA values ignored, I guess rowSums is an option, though it will coerce .SD to a matrix as an intermediate step.

answered Apr 9, 2016 at 21:37

Frank

66.9k8 gold badges104 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

David Arenburg Over a year ago

It''s fine. I didn't invent Reduce(`+`,.... I'm just wondering if this isn't a dupe?

thelatemail Over a year ago

stackoverflow.com/questions/35306500/… is relevant, though not exactly the same.

Tyler Rinker Over a year ago

@DavidArenburg Seems it should be a dupe but I couldn't find one with an obvious title.

David Arenburg Over a year ago

@thelatemail We are doing these Reduce(`+`, .SD) for some time now. Here's akrun in 2014

MichaelChirico Over a year ago

Didn't realize you could pass column numbers on LHS of :=... I would have used value = TRUE

Frank · Accepted Answer · 2016-04-09 22:23:23Z

6

You can combine tidyr's spread and gather with dplyr to get the following single pipeline:

x <- data.frame(A=LETTERS[1:5], as.data.frame(matrix(sample(0:5, 25, T), ncol=5)))

y <- x %>% 
        gather(V, val, -A) %>% 
        group_by(A) %>% 
        mutate(perc = val / sum(val)) %>% 
        select(-val) %>%
        spread(V, perc)

With tidy data it's quite easy to get any group-wise sum (rows, columns or any nested index-level) and compute percentages. The spread and gather will get you to and from your input data format.

edited Apr 9, 2016 at 22:23

Frank

66.9k8 gold badges104 silver badges190 bronze badges

answered Apr 9, 2016 at 22:18

TemplateRex

70.9k21 gold badges176 silver badges316 bronze badges

3 Comments

Tyler Rinker Over a year ago

Ahh makes perfect sense. This is a "why didn't I think of that moment".

eddi Over a year ago

doing a by-row grouping like this, my guess is this will slow down quickly as data grows

TemplateRex Over a year ago

@eddi I haven't tested this on big data. dplyr probably isn't the best way for that any way. I doubt that it would be slower than the data.frame rescaling by rowSums that the OP showed. In the tidy data format, one could always do an arrange(A) before doing the group_by(A), so that the data per group is sequentially processed.

Bryan Shalloway · Accepted Answer · 2019-02-16 17:24:15Z

Another "tidyverse" solution is to use a select within a mutate. E.g.

library(tidyverse)

x <- data.frame(A=LETTERS[1:5], as.data.frame(matrix(sample(0:5, 25, T), ncol=5)))

x %>% 
  mutate(row_counts = select_if(., is.numeric) %>% rowSums()) %>% 
  mutate_at(vars(contains("V")), funs(./row_counts)) %>% 
  select(-row_counts)
#>   A        V1         V2        V3        V4        V5
#> 1 A 0.0000000 0.14285714 0.1428571 0.5714286 0.1428571
#> 2 B 0.0000000 0.62500000 0.1250000 0.1250000 0.1250000
#> 3 C 0.2222222 0.11111111 0.2222222 0.1111111 0.3333333
#> 4 D 0.3000000 0.50000000 0.1000000 0.1000000 0.0000000
#> 5 E 0.3333333 0.06666667 0.1333333 0.3333333 0.1333333

^{Created on 2019-02-16 by the reprex package (v0.2.1)}

Collectives™ on Stack Overflow

dplyr rowwise by some columns

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related