r dplyr and data.table: aggregate then join back to original table

user_4211609 0
Viewed 1500

I frequently find myself calculating summary statistics of data frames using the following dplyr syntax:

1. Aggregate <- 
2. Original Dataset %>%
3. Group_By %>%
4. Filter %>% 
5. Summarize %>%
6. Left_Join(back to Aggregate)

For example:

Original <- data.frame(A = 1:100,B = sample(LETTERS,100,replace = TRUE),C = rnorm(100))

# Calculate 1st Summary Statistic
Aggregate <- Original %>% group_by(B) %>% 
             filter(A > 50) %>% 
             summarize(meanC = mean(C))

# Calculate 2nd Summary Statistic
Aggregate <- Original %>% group_by(B) %>% 
             summarize(Q = sum(C)) %>%
             left_join(x = Aggregate,y = Original,by = "B")

My question is two fold:

A) Is there a better way of building a table of summary statistics off another table? The left-join feels very clunky.

B) What is the "data.table" way of doing this, i.e., how do I join back to the Aggregate table?

Aggregate[Aggregate[,meanC:=mean(C),by=.(B)]]

Thanks for any advice...

data.table dplyr r
1 Answers

You can avoid the join if you mutate after the group_by instead of summarize. (Caveat: I don't know how to do a filtered summary stat this way. And you'll probably want to ungroup afterwards to avoid unexpected behavior later.)

library(tidyverse)
Original <- data.frame(A = 1:100,B = sample(LETTERS,100,replace = TRUE),C = rnorm(100))

# Calculate unfiltered summary statistic, as in OP
Aggregate_OP <- Original %>% 
  group_by(B) %>% 
  summarize(meanC = mean(C)) %>% 
  right_join(Original) %>% 
  select(A, B, C, meanC) # reorder columns
#> Joining, by = "B"

# Simpler, using mutate
Aggregate_mutate <- Original %>% 
  group_by(B) %>% 
  mutate(meanC = mean(C)) %>% 
  ungroup()

identical(Aggregate_OP, Aggregate_mutate)
#> [1] TRUE
user_68518250
Related