I have my data in wide format and I would convert it to long format. Here is a sample of the data
data <- structure(list(lsoa11 = c("E01000001", "E01000002", "E01000003"
), gpp_dist.16 = c(0.702954545454545, 0.929508196721311, 1.13484848484848
), ed_dist.16 = c(2.15590909090909, 2.04475409836066, 2.53454545454545
), gpp_dist.17 = c(0.701333333333333, 0.937966101694915, 1.10735294117647
), ed_dist.17 = c(3.44755555555556, 3.12610169491525, 3.72970588235294
)), row.names = c(NA, -3L), class = c("tbl_df", "tbl", "data.frame"
))
I want the long data to be in the format below. Please, how do I do this in r?
data2 <- structure(list(lsoa11 = c("E01000001", "E01000002", "E01000003",
"E01000001", "E01000002", "E01000003"), Year = c(16, 16, 16,
17, 17, 17), gpp_dist = c(0.702954545454545, 0.929508196721311,
1.13484848484848, 0.701333333333333, 0.937966101694915, 1.10735294117647
), ed_dist = c(2.15590909090909, 2.04475409836066, 2.53454545454545,
3.44755555555556, 3.12610169491525, 3.72970588235294)), row.names = c(NA,
6L), class = "data.frame")
Thank you
Use pivot_longer with names_sep = "\\." to indicate that the separating character in the column names is . (you need to escape it). This will define two groups of columns to separate, before and after the dot. Whatever is before the dot will be assigned a new column name (".value"), and what's after will be reformatted to long (the years).
library(tidyr)
data |>
pivot_longer(-lsoa11, names_sep = "\\.", names_to = c(".value", "Year"))
# # A tibble: 6 × 4
# lsoa11 Year gpp_dist ed_dist
# <chr> <chr> <dbl> <dbl>
# 1 E01000001 16 0.703 2.16
# 2 E01000001 17 0.701 3.45
# 3 E01000002 16 0.930 2.04
# 4 E01000002 17 0.938 3.13
# 5 E01000003 16 1.13 2.53
# 6 E01000003 17 1.11 3.73