Hi, Try this: DF1<-DF[with(DF,rev(order(Company,Salary))),]
DF2<-within(DF1,rank<-ave(Salary,Company,FUN=function(x)rev(order(x)))) > DF2 Company Person Salary rank 15 Toyota O 68278.808 1 5 Toyota E 57487.220 2 11 Toyota K 56090.075 3 12 Toyota L 52602.772 4 14 Toyota N 50764.182 5 18 Toyota R 25816.593 6 2 Toyota B 20754.511 7 6 Toyota F 7706.438 8 13 GM M 98509.522 1 9 GM I 92861.520 2 4 GM D 59571.200 3 1 GM A 51116.978 4 17 GM Q 23886.868 5 3 GM C 22865.814 6 8 Ford H 64279.549 1 16 Ford P 60154.122 2 10 Ford J 59809.242 3 7 Ford G 3554.058 4 A.K. ________________________________ From: Brigid Rotigel <broti...@gmail.com> To: arun <smartpink...@yahoo.com> Cc: R help <r-help@r-project.org> Sent: Friday, June 1, 2012 2:04 PM Subject: Re: [R] Add rank column to data frame as in SQL... Sorry, I meant to put that it didn't work for "GM", not "Toyota". > DF[DF$Company=="GM",] Company Person Salary rank 1 GM A 7067.905 4 3 GM C 31627.171 8 5 GM E 66200.508 7 7 GM G 91287.592 3 10 GM J 33239.467 6 13 GM M 47854.525 5 14 GM N 76631.067 2 16 GM P 87532.133 1 In this data, person "G" earned 91287, but has rank 3 even though this is the highest salary in this partition. The lowest salary earned is the 7067 by person "A", so this should have rank = 8, but in fact this gives rank = 4. Is that more clear? Thanks, Brigid On Fri, Jun 1, 2012 at 1:48 PM, arun <smartpink...@yahoo.com> wrote: Hi, > >Couldn't find any problems if I understand what you mean. > >A.K. > > > >> DF <-within(DF,rank<-ave(Salary,Company,FUN=function(x)rev(order(x)))) >> DF > Company Person Salary rank >1 GM A 7067.905 4 >2 Ford B 9946.616 3 >3 GM C 31627.171 8 >4 Toyota D 51863.426 1 >5 GM E 66200.508 7 >6 Toyota F 40683.019 4 >7 GM G 91287.592 3 >8 Toyota H 29360.337 2 >9 Toyota I 45906.573 7 >10 GM J 33239.467 6 >11 Ford K 65087.047 2 >12 Toyota L 25801.678 3 >13 GM M 47854.525 5 >14 GM N 76631.067 2 >15 Toyota O 8424.691 5 >16 GM P 87532.133 1 >17 Toyota Q 33907.294 6 >18 Ford R 83944.035 1 >> DF[DF$Company=="Ford",] > Company Person Salary rank >2 Ford B 9946.616 3 >11 Ford K 65087.047 2 >18 Ford R 83944.035 1 >> DF[DF$Company=="Toyota",] > Company Person Salary rank >4 Toyota D 51863.426 1 >6 Toyota F 40683.019 4 >8 Toyota H 29360.337 2 >9 Toyota I 45906.573 7 >12 Toyota L 25801.678 3 >15 Toyota O 8424.691 5 >17 Toyota Q 33907.294 6 >> DF[DF$Company=="GM",] > Company Person Salary rank >1 GM A 7067.905 4 >3 GM C 31627.171 8 >5 GM E 66200.508 7 >7 GM G 91287.592 3 >10 GM J 33239.467 6 >13 GM M 47854.525 5 >14 GM N 76631.067 2 >16 GM P 87532.133 1 > > > > >----- Original Message ----- >From: Brigid Mooney <bkmoo...@gmail.com> >To: r-help@r-project.org >Cc: >Sent: Friday, June 1, 2012 11:23 AM >Subject: [R] Add rank column to data frame as in SQL... > >Hopefully this is an easy problem... > >I'm trying to add a partitioned rank column to a data frame where the >rank is calculated separately across a partition by categories, the >way you could easily do in SQL. I found this solution in the archives >that looked like it might work: > >http://tolstoy.newcastle.edu.au/R/e11/help/10/09/8675.html > >The example has a data frame with several car companies, and employee >salaries within them. A column is then added to the data.frame which >should give the descending rank for each employee, partitioned by >company. > > >But when I implemented it, the results weren't the expected rankings. >What am I doing wrong? > >set.seed(1) >DF <- data.frame(Company=sample(c("Ford","Toyota","GM"),size=18,replace=TRUE), >Person=LETTERS[1:18],Salary=runif(18)*1e5) >DF <- within(DF, rank <- ave(Salary, Company, FUN=function(x)rev(order(x)))) > ># Then checking each category manually >DF[DF$Company == "Ford",] >DF[DF$Company == "GM",] >DF[DF$Company == "Toyota",] > ># My results show that it works for Ford and GM, but not Toyota >> DF[DF$Company == "Ford",] > Company Person Salary rank >1 Ford A 38003.52 4 >5 Ford E 65167.38 2 >10 Ford J 38238.80 3 >11 Ford K 86969.08 1 >12 Ford L 34034.90 5 >> DF[DF$Company == "GM",] > Company Person Salary rank >4 GM D 21214.25 6 >6 GM F 12555.51 7 >7 GM G 26722.07 5 >13 GM M 48208.01 4 >15 GM O 49354.13 3 >17 GM Q 82737.33 1 >18 GM R 66846.67 2 >> DF[DF$Company == "Toyota",] > Company Person Salary rank >2 Toyota B 77744.522 2 >3 Toyota C 93470.523 1 >8 Toyota H 38611.409 5 >9 Toyota I 1339.033 3 >14 Toyota N 59956.583 6 >16 Toyota P 18621.760 4 > > >For reference, I'm using R 2.11.1 on a Windows 7 machine. > >Can anyone provide insight into how I am implementing this >incorrectly, or give an alternate way to add such a partitioned rank >column to a data frame? > >Thanks in advance, >Brigid > >______________________________________________ >R-help@r-project.org mailing list >https://stat.ethz.ch/mailman/listinfo/r-help >PLEASE do read the posting guide http://www.R-project.org/posting-guide.html >and provide commented, minimal, self-contained, reproducible code. > > ______________________________________________ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.