I have a data frame in which for each grouping variable, there are two types of variables: one set for which I need the mean within each group, the other one for which I need the sum within each group. That is, I want to apply two different summary functions to two different sets of variables in a data frame after applying some chain functions (such as filter and select, because the original problem is more complicated than this).
> head(df, 10)
group.var x1 x2 x3 y1 y2 y3
1 1 460 477 236 65 142 384
2 1 88 336 114 93 378 52
3 1 93 290 353 384 498 43
4 1 394 105 306 172 216 267
5 1 402 145 423 425 125 322
6 2 187 473 466 279 81 484
7 2 465 373 50 422 136 78
8 2 404 455 362 205 315 12
9 2 54 202 242 348 324 275
10 2 340 380 14 442 376 491
Ideally I want to use dplyr's summarize_at function twice in the same chain to apply mean to variable set 1 and sum to set 2 in two different operations, but for obvious reason, the returned grouped df cannot identify the second set of varibales.
> df1 <- df %>%
+ select(group.var, x1:xn, y1:yn) %>% # just for reference
+ filter(x2 != 20) %>% # just for reference
+ group_by(group.var) %>%
+ summarize_at(vars(x1:xn), mean) %>%
+ summarize_at(vars(y1:ym), sum)
Error in is_character(x, encoding = encoding, n = 1L) :
object 'y1' not found
I can write two snippets which do the same grouping, selecting and filtering, but different summarizing using the summarize_all function, and then join the grouped df's using group.var, but I'm looking for a more efficient method.
The end result I want is:
group.var x1 x2 x3 y1 y2 y3
1 1 287.4 270.6 286.4 1139 1359 1068
2 2 290.0 376.6 226.8 1696 1232 1340
Any suggestions, preferably using dplyr or data.table?
You can try this code:
df %>%
group_by(group.var) %>%
do(invoke_map_dfc(list(map_df),
list(list(select(., x1:x3), mean),
list(select(., y1:y3), sum))
)
)
The output will be
# A tibble: 2 x 7
# Groups: group.var [2]
group.var x1 x2 x3 y1 y2 y3
<int> <dbl> <dbl> <dbl> <int> <int> <int>
1 1 287. 271. 286. 1139 1359 1068
2 2 290 377. 227. 1696 1232 1340
Input dataframe:
df <- data.frame(
id = 1:10,
group.var = c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L),
x1 = c(460L, 88L, 93L, 394L, 402L, 187L, 465L, 404L, 54L, 340L),
x2 = c(477L, 336L, 290L, 105L, 145L, 473L, 373L, 455L, 202L, 380L),
x3 = c(236L, 114L, 353L, 306L, 423L, 466L, 50L, 362L, 242L, 14L),
y1 = c(65L, 93L, 384L, 172L, 425L, 279L, 422L, 205L, 348L, 442L),
y2 = c(142L, 378L, 498L, 216L, 125L, 81L, 136L, 315L, 324L, 376L),
y3 = c(384L, 52L, 43L, 267L, 322L, 484L, 78L, 12L, 275L, 491L),
stringsAsFactors = FALSE)