CODEWITHSUNDEEP
rdplyr
Ni-Ar
Ni-Ar

Reputation: 144

dplyr:: create new column with order number of another column

first apologise if this question was asked somewhere else but I couldn't find an answer.

In R, I have a 2 columns data.frame with ID and Score values.

library(dplyr)
library(magrittr)

set.seed(1235) # for reproducible example
data.frame(ID = LETTERS[1:16],
           Score = round(rnorm(n=16,mean = 1200, sd = 5 ), 0),
           stringsAsFactors = F) -> tmp

head(tmp)

#    ID Score
# 1  A  1203
# 2  B  1198
# 3  C  1197
# 4  D  1202
# 5  E  1200
# 6  F  1190

I want to create a new column called Position with numbers from 1 to nrow(tmp) corresponding to the decreasing order of the Score column.

I can do that in base R with:

tmp[order(tmp$Score, decreasing = T), "Position"] <- 1:nrow(tmp)
head(tmp[order(tmp$Position), ])

#     ID Score Position
# 1   A  1211        1
# 8   H  1210        2
# 3   C  1209        3
# 4   D  1205        4
# 5   E  1202        5
# 16  P  1202        6

But I was wondering if there's a more elegant way to do it abiding the tidyverse principles? Like I tried this but it doesn't work and I can't understand why...

tmp %>%
  mutate(Position = order(Score, decreasing = T)) %>%
  arrange(Position) %>%
  head()

#    ID Score Position
# 1  A  1211        1
# 2  L  1200        2
# 3  C  1209        3
# 4  D  1205        4
# 5  E  1202        5
# 6  G  1188        6

Here the ordering clearly didn't work.

Thanks!

Upvotes: 0

Views: 1691

Answers (2)

Ronak Shah
Ronak Shah

Reputation: 389235

Similar to your order logic we can arrange the data in decreasing order and create position column which goes from 1 to number of rows in the data.

library(dplyr)

tmp %>%
  arrange(desc(Score)) %>%
  mutate(position = 1:n())

#   ID Score position
#1   F  1208        1
#2   I  1207        2
#3   C  1205        3
#4   L  1205        4
#5   H  1203        5
#6   J  1202        6
#7   P  1202        7
#8   D  1201        8
#9   E  1201        9
#10  G  1200       10
#11  N  1198       11
#12  A  1197       12
#13  M  1196       13
#14  O  1196       14
#15  K  1195       15
#16  B  1194       16

Upvotes: 1

akrun
akrun

Reputation: 887851

We can use row_number

library(dplyr)
tmp %>% 
    mutate(Position2 = row_number(-Score))

-output

#    ID Score Position Position2
#1   A  1197       12        12
#2   B  1194       16        16
#3   C  1205        3         3
#4   D  1201        8         8
#5   E  1201        9         9
#6   F  1208        1         1
#7   G  1200       10        10
#8   H  1203        5         5
#9   I  1207        2         2
#10  J  1202        6         6
#11  K  1195       15        15
#12  L  1205        4         4
#13  M  1196       13        13
#14  N  1198       11        11
#15  O  1196       14        14
#16  P  1202        7         7

where 'Position' is the one created with order based on base R OP's code

Upvotes: 2

Related Questions