0

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?

Enlico
  • 23,259
  • 6
  • 48
  • 102
marb_021
  • 65
  • 8
  • if you provide a sample of the data using `dput()` and the code you are working with, I can help you with this – sconfluentus Jan 10 '20 at 17:50
  • 1
    there is a previous post that is similar, and the output that I need is the same. https://stackoverflow.com/questions/29105175/find-neighbouring-elements-of-a-matrix-in-r The problem is that I do not have a matrix. I have a data frame like described above. – marb_021 Jan 10 '20 at 18:43

1 Answers1

3

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"
Allan Cameron
  • 147,086
  • 7
  • 49
  • 87