crumblycloth
crumblycloth

Reputation: 75

How to add a ranking column for this dataset?

My data is as follows:

 df <- data.frame(
  comp_name = c("A","B","C","D","E","F","G","H","J","K","L","M"),
  country = c("US", "UK", "France", "Germany", "US", "UK", "France", "Germany", "US", "UK", "France", "Germany"),
  profit = c(100,125,150,165,150,110,110,125,130,250,95,100)
)

df:

   comp_name country profit
1          A      US    100
2          B      UK    125
3          C  France    150
4          D Germany    165
5          E      US    150
6          F      UK    110
7          G  France    110
8          H Germany    125
9          J      US    130
10         K      UK    250
11         L  France     95
12         M Germany    100

I would like to add a rank column to this data frame which ranks companies by profit by country, like this:

   comp_name country profit rank
1          A      US    100    3
2          B      UK    125    2
3          C  France    150    1
4          D Germany    165    1
5          E      US    150    1
6          F      UK    110    3
7          G  France    110    2
8          H Germany    125    2
9          J      US    130    2
10         K      UK    250    1
11         L  France     95    3
12         M Germany    100    3

I'm relatively new to R and don't know where to start with this. Any help would be greatly appreciated. Thanks!

Upvotes: 1

Views: 130

Answers (4)

ThomasIsCoding
ThomasIsCoding

Reputation: 102920

A base R option using rank + ave

transform(
  df,
  Rank = ave(-profit, country, FUN = rank)
)

gives

   comp_name country profit Rank
1          A      US    100    3
2          B      UK    125    2
3          C  France    150    1
4          D Germany    165    1
5          E      US    150    1
6          F      UK    110    3
7          G  France    110    2
8          H Germany    125    2
9          J      US    130    2
10         K      UK    250    1
11         L  France     95    3
12         M Germany    100    3

Upvotes: 1

akrun
akrun

Reputation: 887991

An option with data.table

library(data.table)
setDT(df)[, Rank := frank(-profit), country]

Upvotes: 1

geom_na
geom_na

Reputation: 268

df %>% 
  dplyr::group_by(country) %>% 
  dplyr::group_map(function(x, y){
    x %>% dplyr::mutate(rank = rank(-profit))
  }) %>% 
  dplyr::bind_rows()

Karthik S provided a cleaner answer. Apparently, group_map here is redundant

Upvotes: 1

Karthik S
Karthik S

Reputation: 11548

Does this work:

library(dplyr)
df %>% group_by(country) %>% mutate(rank = rank(desc(profit)))
# A tibble: 12 x 4
# Groups:   country [4]
   comp_name country profit  rank
   <chr>     <chr>    <dbl> <dbl>
 1 A         US         100     3
 2 B         UK         125     2
 3 C         France     150     1
 4 D         Germany    165     1
 5 E         US         150     1
 6 F         UK         110     3
 7 G         France     110     2
 8 H         Germany    125     2
 9 J         US         130     2
10 K         UK         250     1
11 L         France      95     3
12 M         Germany    100     3

Upvotes: 2

Related Questions