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.
CodePudding user response:
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
CodePudding user response:
Here is a dplyr
using janitor
s row_to_names
function:
library(janitor)
library(dplyr)
df %>%
t() %>%
row_to_names(row_number = 1) %>%
type.convert(as.is=TRUE)
lat lon
A 10 20
B 12 18
C 14 17
D 21 12
E 3 23