I have a df with 3 columns, first column is a X coordinate (e.g. values from 9 to 42), second column is a Y coordinate (e.g. values from 13 to 30) and third column is the id of each cell.
I need to get a table with the id of each cell and the names of their surrounding cell. Depending on the cell it can has as maximum eight neighbors, but sometimes are less (for example in the borders).
Another important point is that the df is not full completed, i.e. there are some combination XY that do not has a value.
Do someone know a way to do that with R?
From your description your data looks like this:
head(df)
# id x y
# 1 ID1 36 29
# 2 ID2 21 27
# 3 ID3 35 13
# 4 ID4 35 21
# 5 ID5 29 29
# 6 ID6 34 27
...
As you pointed out, you could do this more easily if your data were in a matrix format. You can easily create one; it just has to be big enough to accommodate your maximum x and y values, and to have an extra row and column so that you can neatly deal with the edges and corners.
We will make the entries all empty strings initially.
mat <- matrix(rep("", (max(df$x) + 1) * (max(df$y) + 1)), ncol = max(df$x + 1))
Now we can write the ids into the appropriate positions in the matrix using the x and y indices:
for(i in 1:nrow(df)) mat[df$y[i], df$x[i]] <- as.character(df$id[i])
Now finding the eight neighbours of each entry in your data frame is as easy as checking the 8 adjacent matrix entries. The tidiest way to represent this is to keep the original data frame and add a new column for each of the 8 relative compass positions:
df$east <- mat[df$y + nrow(mat) * (df$x - 2)]
df$west <- mat[df$y + nrow(mat) * (df$x)]
df$north <- mat[df$y + 1 + nrow(mat) * (df$x - 1)]
df$south <- mat[df$y - 1 + nrow(mat) * (df$x - 1)]
df$southeast <- mat[df$y - 1 + nrow(mat) * (df$x - 2)]
df$southwest <- mat[df$y - 1 + nrow(mat) * (df$x)]
df$northeast <- mat[df$y + 1 + nrow(mat) * (df$x - 2)]
df$northwest <- mat[df$y + 1 + nrow(mat) * (df$x)]
Now we have:
head(df)
# id x y east west north south southeast southwest northeast northwest
# 1 ID1 36 29 ID26 ID317 ID279 ID182
# 2 ID2 21 27 ID178 ID63 ID205 ID97
# 3 ID3 35 13 ID291
# 4 ID4 35 21 ID239 ID338 ID328 ID29
# 5 ID5 29 29 ID268 ID78 ID85
# 6 ID6 34 27 ID20 ID271 ID41 ID154 ID143 ID80 ID72 ID279
Just to prove this works, we can do:
df$north[1]
# [1] "ID26"
df$south[which(df$id == "ID26")]
# [1] "ID1"