Converting coordinate ASC file (raster data) into a XY coordinate data frame

Question

I have read in an ASCII file with values that correspond to a UK coordinate grid, however, if I want to apply the which.closest() command I need to be able to specify the columns in terms of X and Y coordinates. So right now, below is an random example extract of the type of data frame I have:

           1000 2000 3000 4000 5000
    66000     1    2    3    4    5
    65000     1    2    3    4    5
    64000     1    2    3    4    5
    63000     1    2    3    4    5
    62000     1    2    3    4    5

Here is the script so you can reproduce this extract:

    a=c(1,1,1,1,1)
    b=c(2,2,2,2,2)
    c=c(3,3,3,3,3)
    d=c(4,4,4,4,4)
    e=c(5,5,5,5,5)
    df=data.frame(a,b,c,d,e)
    colnames_df=seq(1000,5000,1000)
    rownames_df=seq(62000,66000,1000)
    rownames_df=rev(rownames_df)
    colnames(df)=colnames_df
    rownames(df)=rownames_df
    df

and this is the data frame I want to be able to continue my analysis:

    X     Y      Z
    1000  62000  1
    1000  63000  1
    3000  64000  1
    1000  65000  1
    1000  66000  1
    2000  62000  2
    2000  63000  2
    2000  64000  2
    2000  65000  2
    2000  66000  2 
    ...(etc.)

Is there a code that can do this easily rather than manually setting up a new data frame, specifying columns and rows, etc. because I have a data frame 1377 x 812 for 20 different air quality chemical measurements, so if there is a code it would make my life a lot easier. Any help is appreciated! Thank you!

Answer 1

If I understood correctly what you need :

library(dplyr)
library(tidyr)
df <- cbind(Y = rownames(df), df)
rownames(df) <- NULL
df <- df %>% gather(X, Z, -Y)

Answer 2

Here are two more traditional approaches (as compared to gather suggested by Thor6):

X <- as.integer(rep(colnames(df), each=nrow(df)))
Y <- as.integer(rep(rownames(df), nrow(df)))
a <- cbind(X, Y, Z = as.vector(as.matrix(df)))

With reshape (rather convoluted)

cn <- colnames(df)
b <- reshape(df, direction='long', varying=cn, v.names = "Z", times=cn, timevar = "X" )
b$Y <- rownames(df)
b <- b[, c('X', 'Y', 'Z')]
rownames(b) <- NULL

However, given that these are raster data, if these are stored in a standard file format, you would not need to make the data.frame in the first place, and you could do something like this:

library(raster)
r <- raster('raster data file')
d <- rasterToPoints(r) 

If you also want the cells with NA

e <- cbind(xyFromCell(r, 1:ncell(r)), Z=values(r))

Working example

f <- system.file("external/test.grd", package="raster")
r <- raster(f)
d <- rasterToPoints(r) 
e <- cbind(xyFromCell(r, 1:ncell(r)), Z=values(r))