2

  • table: Brands_12M as data.table

  • Names(Brands_12M): "Major Category", "Brand", "Sales"

There are 5 major categories and 500 brands.

I am trying to add "Rank" column to based on "Sales" by "Major Category" using:

Brands_12M <- Brands_12M[,Rank := frankv(Brands_12M, "Sales", "Major Category", order=-1L)]

I am getting rank 1-500 for all brands ignoring "Major Category". I need rank by brand for each "Major Category" i.e. 1-100, 1-100 etc

Greatly appreciate any suggestion.

Erik A
  • 31,639
  • 12
  • 42
  • 67
Brian Atakhodjaev
  • 65
  • 6
  • Can you provide an example data set, and your expected output please? – SymbolixAU Apr 09 '18 at 23:05
  • Major Category Brand Sales Rank H Brand 2 13813497.1 1 C Brand 3 8994405.1 2 C Brand 4 8875269.6 3 H Brand 5 8725524.9 4 C Brand 6 8375145.7 5 – Brian Atakhodjaev Apr 09 '18 at 23:44
  • sorry I am not sure how to paste the table – Brian Atakhodjaev Apr 09 '18 at 23:44
  • [this answer on how to make a great reproducible example](https://stackoverflow.com/a/5963610/5977215) tells you everything you need to know. In particular the `dput()` command will give you the code you can used in your question to rebuild your data. It should also only be a few rows of data too. – SymbolixAU Apr 09 '18 at 23:50
  • 1
    And edit your question with the data, rather than in a comment box. – SymbolixAU Apr 09 '18 at 23:52
  • Major Category Brand Sales Rank C Brand 3 8994405.1 1 C Brand 4 8875269.6 2 C Brand 6 8375145.7 3 H Brand 2 13813497.1 1 H Brand 5 8725524.9 2 – Brian Atakhodjaev Apr 09 '18 at 23:54
  • Click the edit link closer to the question and change the body of the question to include your data, preferably in an easily reproducible format. Anyway, if you go through a data.table tutorial you'll see how to use the `by=` argument to do things grouped by one or more columns. – Frank Apr 09 '18 at 23:55
  • thank you for the suggestion. I will try to use by= argument – Brian Atakhodjaev Apr 09 '18 at 23:56

1 Answers1

3

I changed my answer after looking at all the comments. This seems to be closer to what is suggested. The sample data set I use below has 500 observations and that seem to be what the poster wants.

Also the ranks go from 1 to 100 inside each Major Category.

library(data.table)

Brands_12M <- data.table(`Major Category` = rep(1:5,100),
                         Brand = 1:500,
                         Sales = runif(500))

Brands_12M <- Brands_12M[,Rank:=frankv(Sales, order=-1L),by=c("Major Category")][order(`Major Category`,Sales)]
Harlan Nelson
  • 1,394
  • 1
  • 10
  • 22
  • thank you very much! it worked perfectly! I tried to vote it up but for some reason it would not let me since I do not have much yet history... may be I am not doing it right. Thank you.! – Brian Atakhodjaev Apr 10 '18 at 04:02