R: matching coordinates from one (large) data frame into grid cells from another (large) data frame

data.table, dataframe, r

Solution

You just need two merges with rolling:

grid = data.table(grid, key = 'MINX')
points = data.table(points, key = 'X')

# first merge to find correct MAXX
intermediate = grid[points, roll = Inf][, list(MAXX, X = MINX, Y)]

# now merge by Y
setkey(intermediate, MAXX, Y)
setkey(grid, MAXX, MINY)
grid[intermediate, roll = Inf][, list(X, Y = MINY, GridID)]
#   X Y GridID
#1: 1 4     19
#2: 2 2      8
#3: 3 3     15
#4: 4 1      4
#5: 5 2     11

Problem

I have a large data frame (~200,000 rows) that contains X-Y coordinates, e.g.: ``` points <- data.frame(X = c(1,3,2,5,4), Y = c(4,3,2,2,1)) ``` And another large data frame (~1,000,000 rows) that contains the corner cells of a spatial (rectangular) grid, e.g.: ``` MINX <- rep(0.5:5.5,6) MINY <- rep(0.5:5.5,each=6) grid <- data.frame(GridID = 1:36, MINX, MINY, MAXX = MINX+1, MAXY = MINY+1) ``` I would like to add a column to the "points" data frame that identified the ID of the grid the point is located in: ``` X Y GridID 1 4 19 3 3 15 2 2 8 5 2 11 4 1 4 ``` I can think of several ways to do this, using loops, using combinations of apply and match, even pulling out some big spatial gun from `sp` or `maptools`. But all are prohibitively slow. I have a hunch there's some `data.table()` one liner that could pull this off in reasonable time. Do any gurus have an idea? (For the record, this is how I got the grid cell ID's above: ``` pt.minx <- apply(points,1, function(foo) max(unique(grid)$MINX[unique(grid)$MINX < foo[1]])) pt.miny <- apply(points,1, function(foo) max(unique(grid)$MINY[unique(grid)$MINY < foo[2]])) with(grid, GridID[match(pt.minx+1i*pt.miny, MINX + 1i*MINY)]) ``` I can't tell from here whether it's slick or hideous - either way the apply function is way too slow for the complete data frame.)

Original source