How to keep other columns when using dplyr?

Question

I have a similar problem as described How to aggregate some columns while keeping other columns in R?, but none of the solutions from there which I have tried work.

I have a data frame like this:

df<-data.frame(a=rep(c("a","b"),each=2),b=c(500,400,200,300), 
               c = c(5,10,2,4),stringsAsFactors = FALSE) 
> df
  a   b  c
1 a 500  5
2 a 400 10
3 b 200  2
4 b 300  4

df%>%
  group_by(a)%>%
  summarise('max' = max(c), 'sum'=sum(c))

  a       max   sum
  <chr> <dbl> <dbl>
1 a        10    15  
2 b         4     6

but I need also column b:

1 a        10    15   400
2 b         4     6   300

The value for column b is max(c).

Edit data for specific case:

> df
  a   b  c
1 a 500  5
2 a 400  5

in this case, I need a higher value col b in the summary

#   a       max   sum     b
#   <chr> <dbl> <dbl> <dbl>
# 1 a         5    10   500

Column b has 4 values, and in your output you show 2 values. How do you decide which ones to use in the output? — Karolis Koncevičius, Mar 30 '20 at 11:54
Why not add them explicitly `summarise('max' = max(c), 'sum'=sum(c), 'b'=max(b))` ? — Karolis Koncevičius, Mar 30 '20 at 12:00
It's not good solution because then A=500, but I expected A=400 — Zizou, Mar 30 '20 at 12:05

score 4 · Answer 1 · answered Mar 30 '20 at 11:55

4

I would replace the summarise with a mutate (keeps all rows), and then filter for the rows you want. The tibble is then still grouped, so an ungroup is needed to get rid of the groups.

d f%>%
    group_by(a) %>%
    mutate('max' = max(c), 'sum'=sum(c)) %>% 
    filter(c == max) %>%
    ungroup()

#   a         b     c   max   sum
#   <chr> <dbl> <dbl> <dbl> <dbl>
# 1 a       400    10    10    15
# 2 b       300     4     4     6

answered Mar 30 '20 at 11:55

Bas

4,628
1
14
16

1

Ok, but what if the same c values are for the unique a? I edited the data last. – Zizou Mar 30 '20 at 12:17

Darren Tsai · Accepted Answer · 2020-03-30T12:24:41.713

3

You have to specify how to summariz the variable b:

df %>%
  group_by(a) %>%
  summarise(max = max(c), sum = sum(c), b = max(b[c == max(c)]))

# # A tibble: 2 x 4
#   a       max   sum     b
#   <chr> <dbl> <dbl> <dbl>
# 1 a        10    15   400
# 2 b         4     6   300

edited Mar 30 '20 at 12:24

answered Mar 30 '20 at 12:05

Darren Tsai

32,117
5
21
51

Ok, but what if the same ```c``` values are for the unique ```a```? I edited the data last. – Zizou Mar 30 '20 at 12:17

nurandi · Answer 3 · 2020-03-30T12:17:32.233

1

Updated as the question was edited

df%>%
  group_by(a)%>%
  summarise('max' = max(c), 'sum'=sum(c), b=max(b))

# A tibble: 2 x 4
#   a       max   sum     b
#  <chr>  <dbl>  <dbl> <dbl>
# 1 a        10    15   500
# 2 b         4     6   300

edited Mar 30 '20 at 12:17

answered Mar 30 '20 at 11:54

nurandi

1,588
1
11
20

How to keep other columns when using dplyr?

3 Answers3