CODEWITHSUNDEEP
rtidyverse
SiH
SiH

Reputation: 1546

Subtract one group of values from all groups using group_by

How can I subtract one group of values from all values using group_by in tibble. Below is an example with expected results. I wish to subtract values of category "A" from all values

d <- tibble(categories = c(rep("A", 3), rep("B", 3), rep("C", 3)), 
            values = 1:9)

# expected outcome

d <- tibble(categories = c(rep("A", 3), rep("B", 3), rep("C", 3)), 
            values = c(0, 0, 0, 3, 3, 3, 6, 6, 6))

Upvotes: 0

Views: 41

Answers (2)

akrun
akrun

Reputation: 887501

If the categories size are the same length, we could do

library(dplyr)
 d %>%
   mutate(values = values - d$values[d$categories == "A"])

-output

# A tibble: 9 × 2
  categories values
  <chr>       <int>
1 A               0
2 A               0
3 A               0
4 B               3
5 B               3
6 B               3
7 C               6
8 C               6
9 C               6

Upvotes: 2

deschen
deschen

Reputation: 11006

You can do:

library(tidyverse)
d %>% 
  group_by(categories) %>%
  mutate(id = row_number()) %>%
  ungroup() %>%
  pivot_wider(names_from = 'categories',
              values_from = 'values') %>%
  mutate(across(-id, ~ . - A)) %>%
  pivot_longer(cols = -id,
               names_to = 'categories',
               values_to = 'values',
               cols_vary = 'slowest') %>%
  select(-id)

Alternatively:

d %>% 
  group_by(categories) %>%
  mutate(id = row_number()) %>%
  ungroup() %>%
  mutate(values = values - values[categories == 'A' & id == id]) %>%
  select(-id)

# A tibble: 9 x 2
  categories values
  <chr>       <int>
1 A               0
2 A               0
3 A               0
4 B               3
5 B               3
6 B               3
7 C               6
8 C               6
9 C               6

Upvotes: 0

Related Questions