CODEWITHSUNDEEP
rdataframedplyr
prog
prog

Reputation: 1073

Descending within group without changing the order

I am trying to the sort column C in descending order within the group without changing the existing order of the group. How can i achieve it!

ab <- data.frame(A = c('a1','a1','a2','a2','a3','a3'),
                 B = c(1,2,3,4,5,6),
                 C = c(2,3,5,6,8,9))

Dataframe:

   A B C
1 a1 1 2
2 a1 2 3
3 a2 3 5
4 a2 4 6
5 a3 5 8
6 a3 6 9

Code I tried but changes the order and does sorting:

ab %>% arrange(A,B,desc(C))

Expected Result: Here it will only sort the numbers but the group A, B remains same as dataframe.

   A B C
1 a1 1 3
2 a1 2 2
3 a2 3 6
4 a2 4 5
5 a3 5 9
6 a3 6 8

Upvotes: 1

Views: 697

Answers (2)

M--
M--

Reputation: 28825

library(dplyr)

ab %>% 
  group_by(A) %>% 
  mutate(C = sort(C, decreasing = TRUE))  

#> # A tibble: 6 x 3
#> # Groups:   A [3]
#>   A         B     C
#>   <fct> <dbl> <dbl>
#> 1 a1        1     3
#> 2 a1        2     2
#> 3 a2        3     6
#> 4 a2        4     5
#> 5 a3        5     9
#> 6 a3        6     8

Update:

Addressing OP's follow-up question about grouping by column B for other purposes:

# a new dataset to have two rows with the same A and B value
ab <- data.frame(A = c('a1','a1','a1','a2','a2','a3','a3'),
                 B = c(1,1,2,3,4,5,6),
                 C = c(2,4,3,5,6,8,9)) 

library(dplyr)

ab %>% 
  group_by(A,B) %>% 
  mutate(S = sum(C)) %>% 
  group_by(A) %>% 
  mutate(C = sort(C, decreasing = TRUE))  
#> # A tibble: 7 x 4
#> # Groups:   A [3]
#>   A         B     C     S
#>   <fct> <dbl> <dbl> <dbl>
#> 1 a1        1     4     6
#> 2 a1        1     3     6
#> 3 a1        2     2     3
#> 4 a2        3     6     5
#> 5 a2        4     5     6
#> 6 a3        5     9     8
#> 7 a3        6     8     9

Upvotes: 1

Calum You
Calum You

Reputation: 15062

You are not sorting the data, you are actually changing C to something new since you are breaking up existing rows. You therefore need to mutate like so. Note that I am inferring that you want to sort C within each group of A since that matches your expected output, though this was not spelled out in the question.

library(dplyr)
ab <- data.frame(A = c('a1','a1','a2','a2','a3','a3'),
                 B = c(1,2,3,4,5,6),
                 C = c(2,3,5,6,8,9)) 
ab %>%
  group_by(A) %>%
  mutate(C2 = sort(C, decreasing = TRUE))
#> # A tibble: 6 x 4
#> # Groups:   A [3]
#>   A         B     C    C2
#>   <fct> <dbl> <dbl> <dbl>
#> 1 a1        1     2     3
#> 2 a1        2     3     2
#> 3 a2        3     5     6
#> 4 a2        4     6     5
#> 5 a3        5     8     9
#> 6 a3        6     9     8

Created on 2019-09-17 by the reprex package (v0.3.0)

Upvotes: 1

Related Questions