Create new column which orders two previous columns

46 views Asked by At

I'm looking to create a new column which is based on the ordering of two other columns, preferably using the Tidyverse functions, but any suggestions are appreciated. I have a table of around 1300 entries and several columns but a sample of my data looks something like:

Number of people TotalOrder TotalQuantile
12 1 1
19 2 1
21 3 2
45 5 2
53 5 3
55 6 3
60 7 4
75 8 4

But I want a fourth column which ranks TotalOrder within TotalQuantile, and to look something like:

Number of people TotalOrder TotalQuantile NewOrder
12 1 1 1
19 2 1 2
21 3 2 1
45 5 2 2
53 5 3 1
55 6 3 2
60 7 4 1
75 8 4 2

I've tried a few things like filtering, arranging, etc but it's not worked out. Thanks for the help.

1

There are 1 answers

0
Vinícius Félix On
library(dplyr)
  

df <-
  structure(list(
    Number.of.people = c(12L, 19L, 21L, 45L, 53L, 55L, 60L, 75L),
    TotalOrder = c(1L, 2L, 3L, 5L, 5L, 6L, 7L, 8L),
    TotalQuantile = c(1L, 1L, 2L, 2L, 3L, 3L, 4L, 4L)),
    row.names = c(NA,-8L), class = c("tbl_df", "tbl", "data.frame"))

df %>% 
  group_by(TotalQuantile) %>% 
  mutate(NewOrder = row_number())

# A tibble: 8 x 4
# Groups:   TotalQuantile [4]
  Number.of.people TotalOrder TotalQuantile NewOrder
             <int>      <int>         <int>    <int>
1               12          1             1        1
2               19          2             1        2
3               21          3             2        1
4               45          5             2        2
5               53          5             3        1
6               55          6             3        2
7               60          7             4        1
8               75          8             4        2