Using group_by() and prod() for not all rows in each group

user_16605264 0
Viewed 37

I want to transform this df such that the total is only the product of A and B, not including C

df <- data.frame(id = c(1, 1, 1, 1, 2, 2, 2, 2), type = c('A', 'B', 'C', 'total', 'A', 'B', 'C', 'total'), value = c('1', NA, '4', NA, '5', '4', '8', NA))

 id  type value
  1     A     1
  1     B  <NA>
  1     C     4
  1 total  <NA>
  2     A     5
  2     B     4
  2     C     8
  2 total  <NA>

The result should look something like:

df_result <- data.frame(id = c(1, 1, 1, 1, 2, 2, 2, 2), type = c('A', 'B', 'C', 'total', 'A', 'B', 'C', 'total'), value = c('1', NA, '4', '1', '5', '4', '8', '20'))

 id  type value
  1     A     1
  1     B  <NA>
  1     C     4
  1 total     1
  2     A     5
  2     B     4
  2     C     8
  2 total    20

My code so far does the product of all of the types, not just A and B:

df %>%
  group_by(id) %>%
  mutate(
    value = as.numeric(value),
    value = if_else(type == 'total', prod(value, na.rm = T), value)
  )

extra: what if the id does not uniquely identify one 'set' of types? e.g.

df2 <- data.frame(id = c(1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2), type = c('A', 'B', 'C', 'total', 'A', 'B', 'C', 'total', 'A', 'B', 'C', 'total'), value = c('1', NA, '4', NA, '2', NA, '4', NA, '5', '4', '8', NA))

 id  type value
  1     A     1
  1     B  <NA>
  1     C     4
  1 total  <NA>
  1     A     2
  1     B  <NA>
  1     C     4
  1 total  <NA>
  2     A     5
  2     B     4
  2     C     8
  2 total  <NA>

The totals above would be 1, 2 and 20 respectively.

Thanks for all the help!

dplyr dataframe r
2 Answers

Create a column which uniquely identifies every set. We can achieve that with the help of cumsum and subset the value only corresponding to 'A' and 'B'.

library(dplyr)

df2 %>%
  mutate(value = as.numeric(value)) %>%
  group_by(grp = cumsum(lag(type == 'total', default = TRUE))) %>%
  mutate(value = ifelse(type == 'total', prod(value[type %in% c('A', 'B')], na.rm = TRUE), value)) %>%
  ungroup
  

#      id type  value   grp
#   <dbl> <chr> <dbl> <int>
# 1     1 A         1     1
# 2     1 B        NA     1
# 3     1 C         4     1
# 4     1 total     1     1
# 5     1 A         2     2
# 6     1 B        NA     2
# 7     1 C         4     2
# 8     1 total     2     2
# 9     2 A         5     3
#10     2 B         4     3
#11     2 C         8     3
#12     2 total    20     3

You may drop the grp column if it is not needed.

For the first case you can do -

df %>%
  group_by(id) %>%
  mutate(
    value = as.numeric(value),
    value = if_else(type == 'total', prod(value[type %in% c('A', 'B')], na.rm = T), value)
  )
user_39629140

Here is a way using pivot_wider and pivot_longer together:

library(dplyr)
library(tidyr)
df %>%
  mutate(value = as.numeric(value)) %>% 
  pivot_wider(
    names_from = type, values_from = value
  ) %>% 
  rowwise() %>% 
  mutate(total = prod(c(A,B), na.rm =TRUE)) %>% 
  pivot_longer(
    cols = -id,
    names_to = "type",
    values_to = "value"
  )

  id  type value
1  1     A     1
2  1     B  <NA>
3  1     C     4
4  1 total  <NA>
5  2     A     5
6  2     B     4
7  2     C     8
8  2 total  <NA>
user_133216470
Related