Select the nth element in the coefficient data frame

Question

I have a dataframe with a text column nameand factor city. It is sorted alphabetically first with cityand then name. Now I need to get a data frame that contains only the nth element in each city, keeping this order. How can this be done beautifully, without cycles?

I have:

name    city
John    Atlanta
Josh    Atlanta
Matt    Atlanta
Bob     Boston
Kate    Boston
Lily    Boston
Matt    Boston

I need a function that returns the nth element city, i.e. if it is equal to three, then:

name    city
Matt    Atlanta
Lily    Boston

It should return NULLfor nameif it is out of range for the selected one city, i.e. for the 4th:

name    city
NULL    Atlanta
Matt    Boston

Using only the R base, please?

Answer 1

In the R database using by:

, :

test <- read.table(text="name    city
John    Atlanta
Josh    Atlanta
Matt    Atlanta
Bob     Boston
Kate    Boston
Lily    Boston
Matt    Boston
Bob     Seattle
Kate    Seattle",header=TRUE)

:

do.call(rbind,by(test,test$city,function(x) x[3,]))

:

        name    city
Atlanta Matt Atlanta
Boston  Lily  Boston
Seattle <NA>    <NA>

, , :

nthrow <- function(dset,splitvar,n) {
    result <- do.call(rbind,by(dset,dset[splitvar],function(x) x[n,]))
    result[,splitvar][is.na(result[,splitvar])] <- row.names(result)[is.na(result[,splitvar])]
    row.names(result) <- NULL
    return(result)
}

:

nthrow(test,"city",3)

:

  name    city
1 Matt Atlanta
2 Lily  Boston
3 <NA> Seattle

Answer 2

plyr :

dat <- structure(list(name = c("John", "Josh", "Matt", "Bob", "Kate", 

"", "" ), = c ( "" , "" , "" , "", "", "", "" )).Names = c ( "", "" ), class= "data.frame", row.names = c (NA, -7L))

library(plyr)

ddply(dat, .(city), function(x, n) x[n,], n=3)

> ddply(dat, .(city), function(x, n) x[n,], n=3)
  name    city
1 Matt Atlanta
2 Lily  Boston
> ddply(dat, .(city), function(x, n) x[n,], n=4)
  name   city
1 <NA>   <NA>
2 Matt Boston
> 

, R data.table sqldf...

Answer 3

A data.table solution

library(data.table)
DT <- data.table(test)

# return all columns from the subset data.table
n <- 4
DT[,.SD[n,] ,by = city]
##      city name
## 1: Atlanta   NA
## 2:  Boston Matt
## 3: Seattle   NA

# if you just want the nth element of `name` 
# (excluding other columns that might be there)
# any of the following would work

DT[,.SD[n,] ,by = city, .SDcols = 'name']


DT[, .SD[n, list(name)], by = city]


DT[, list(name = name[n]), by = city]