How can I summarize my dataset so that the aggregate function takes into account values from another only once?

user_18226275 0
Viewed 32

I want to aggregate column a with sum() and column b with mean() but for this mean() same values of ID column should be used only once. So that i get sum(3,4,5,6) and mean(7,8,9). How do i write this condition within a summarize()?

library(dplyr)
data = tibble(ID=c("111", "111", "222", "333"), a=c(3,4,5,6), b=c(7,7,8,9))
Edit:
data = tibble(ID=c("111", "111", "222", "333", "444"), a=c(3,4,5,6,7), b=c(7,7,8,9,7))

Another ID e.g. '444' can also have value 7. For this case I would want mean(7,8,9,7).

dplyr r
1 Answers

You could calculate sum(a) and mean(b) separately and column-bind them.

bind_cols(
  data %>% summarise(a = sum(a)),
  data %>% distinct(ID, b) %>% summarise(b = mean(b))
)

Or you could use tapply + unique:

data %>%
  summarise(a = sum(a), b = mean(unlist(tapply(b, ID, unique))))
Output
# # A tibble: 1 × 2
#       a     b
#   <dbl> <dbl>
# 1    18     8
user_100689850
Related