Use dplyr pivot_longer to transpose dataset

I have a dataset where the columns are different sites and the latitude and longitude values are provided in the first two rows. I want to transpose this data, so that each site is now a row with the latitude and longitude values in the columns.

I’m trying to do this with pivot_longer, but have not been successful to date as I’m unclear from the examples how to indicate which fields should be the new rows and columns.

df <- data.frame(
  sites = c("lat", "lon"),
  A = c(10, 20),
  B = c(12, 18),
  C = c(14, 17),
  D = c(21, 12),
  E = c(3, 23)) %>% 
  # transpose with sites in 1st column (A-E on different rows) and lat/lon values in seperate columns
  pivot_longer(cols = c(2:6),
               names_to = c("lat", "lon"), 
               values_to = "sites")

Error in `build_longer_spec()`:
! If you supply multiple names in `names_to` you must also supply one of `names_sep` or `names_pattern`.
Run `rlang::last_error()` to see where the error occurred.

>Solution :

We may need transpose here

library(data.table)
data.table::transpose(setDT(df), make.names = "sites")

-output

     lat   lon
   <num> <num>
1:    10    20
2:    12    18
3:    14    17
4:    21    12
5:     3    23

If we need the column name as identifier for each row

 data.table::transpose(setDT(df), make.names = "sites", keep.names = "grp")
      grp   lat   lon
   <char> <num> <num>
1:      A    10    20
2:      B    12    18
3:      C    14    17
4:      D    21    12
5:      E     3    23

If we want to use tidyverse, then reshape to ‘long’ and then to ‘wide’ with pivot_wider

library(dplyr)
library(tidyr)
df %>% 
  pivot_longer(cols = -sites, names_to = 'grp') %>% 
  pivot_wider(names_from = sites, values_from = value)
# A tibble: 5 × 3
  grp     lat   lon
  <chr> <dbl> <dbl>
1 A        10    20
2 B        12    18
3 C        14    17
4 D        21    12
5 E         3    23