CODEWITHSUNDEEP
r
mdd
mdd

Reputation: 53

Creating 'Top 10' lists in R

I have a data frame where each row represents a recorded event. As an example, let's say I measured the speed of passing cars, and some cars passed me more than once.

cardata <- data.frame(
  car.ID = c(3,4,1,2,5,4,5),
  speed = c(100,121,56,73,87,111,107)
  )

I can sort the list and pull out the three fastest events...

top3<-head(cardata[order(cardata$speed,decreasing=TRUE),],n=3)
> top3
  car.ID speed
2      4   121
6      4   111
7      5   107

... but you'll notice that car 4 recorded the two fastest times. How do I find the three fastest events without any duplicate car ID's? I realize that may 'Top 3' list will not include the three fastest events in this instance.

Upvotes: 5

Views: 818

Answers (5)

Jonas Tundo
Jonas Tundo

Reputation: 6207

With plyr you can do it as well. To select the top 3 for example:

library(plyr)
top3 <- ddply(ddply(cardata,.(car.ID),summarize, maxspeed=max(speed)),.(-maxspeed))[1:3,-1]

UPDATE

With the dplyr package you can do it faster and in a more clear way.

require(dplyr)

# Select for each car.ID the observation with the highest speed and sort.
top <- cardata  %>% 
    group_by(car.ID) %>% 
    arrange(-speed)%>%
    top_n(1)

# Take the top 3 of the resulting table.
top3 <- top[1:3,]
top3

#   car.ID speed
# 1      4   121
# 2      5   107
# 3      3   100

Upvotes: 3

zx8754
zx8754

Reputation: 56219

I prefer solutions suggested using base R, but for completeness here is another way using sqldf:

library(sqldf)

cardata <- data.frame(
  car.ID = c(3,4,1,2,5,4,5),
  speed = c(100,121,56,73,87,111,107)
)

sqldf("
select car_ID, max(speed) as max_speed
from cardata
group by car_ID
order by max(speed) desc      
limit 3
      ")

Upvotes: 2

Matthew Plourde
Matthew Plourde

Reputation: 44614

This is another base R way:

top.speeds <- unique(transform(cardata, speed=ave(speed, car.ID, FUN=max)))
top3 <- head(top.speeds[order(top.speeds$speed, decreasing=TRUE), ], n=3)
#   car.ID speed
# 2      4   121
# 5      5   107
# 1      3   100

Upvotes: 2

eddi
eddi

Reputation: 49448

Using data.table instead of data.frame:

library(data.table)
dt = data.table(cardata)

# the easier to read way
dt[order(-speed), speed[1], by = car.ID][1:3]
#   car.ID  V1
#1:      4 121
#2:      5 107
#3:      3 100

# (probably) a faster way
setkey(dt, speed) # faster sort by speed
tail(dt[, speed[.N], by = car.ID], 3)
#  car.ID  V1
#1:      5 107
#2:      3 100
#3:      4 121

# and another way for fun (not sure how fast it is)
setkey(dt, car.ID, speed)
tail(dt[J(unique(car.ID)), mult = 'last'], 3)

Upvotes: 3

flodel
flodel

Reputation: 89097

You can use aggregate to first find the top speed per car.ID:

cartop <- aggregate(speed ~ car.ID, data = cardata, FUN = max)
top3 <- head(cartop[order(cartop$speed, decreasing = TRUE), ], n = 3)

 #   car.ID speed
 # 4      4   121
 # 5      5   107
 # 3      3   100

Upvotes: 6

Related Questions