How to convert data from wide to long format in R with a separating dot

Question

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

Answer 1

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