How to change a latitude (rows) x longitude (columns) data frame into a latitude (column 1), longitude (column 2), value (column 3) data frame in R?

Question

I have a data frame where latitude (lat) values are the names of the rows, and longitude (lon) values are the names of the columns, and for each cell of the data frame is a temperature (temp) value of interest to me (or an NA value) such as:

    lon  lon  lon  lon  lon  lon
lat temp temp temp temp temp temp
lat temp temp temp temp temp temp
lat temp temp temp temp temp temp
lat temp temp temp temp temp temp

My question is, how can I reformat this data frame so that it is in a format of:

    C1    C2    C3
R1  lat  lon   temp
R2  lat  lon   temp
R3  lat  lon   temp
R4  lat  lon   temp

Any help, functions, or example code would be much appreciated!

Answer 1

We could use pivot_longer from tidyr. Here is an example with mock data:

df <- structure(list(lon1 = c(1L, 1L, 1L, 1L), lon2 = c(2L, 2L, 2L, 
2L), lon3 = c(3L, 3L, 3L, 3L), lon4 = c(3L, 3L, 3L, 3L), lon5 = c(4L, 
4L, 4L, 4L), lon6 = c(5L, 5L, 5L, 5L)), row.names = c("lat1", 
"lat2", "lat3", "lat4"), class = "data.frame")

library(dplyr)
library(tidyr)
df %>% 
  rownames_to_column("latitude") %>% 
  pivot_longer(
    cols = -latitude,
      names_to="longitude",
      values_to= "temperature"
    )

Output:

   latitude longitude temperature
   <chr>    <chr>           <int>
 1 lat1     lon1                1
 2 lat1     lon2                2
 3 lat1     lon3                3
 4 lat1     lon4                3
 5 lat1     lon5                4
 6 lat1     lon6                5
 7 lat2     lon1                1
 8 lat2     lon2                2
 9 lat2     lon3                3
10 lat2     lon4                3
# ... with 14 more rows