How to transform R data frame by using pivot

I have the following data set

df <- data.table(
  id = c(1),
  field_a.x = c(10),
  field_a.y = c(20),
  field_b.x = c(30),
  field_b.y = c(40))

And, I'd like to transform it into

df_result <- data.table(
  id = c(1),
  field_name = c("field_a", "field_b"),
  x = c(10, 30),
  y = c(20, 40))

by using "pivot_longer" function taking into account postfixes ".x" and ".y".

It will be much more fields in my real data. But I would like to see how to process it for 2 for example.

Thanks!

CodePudding user response：

You could set ".value" in names_to and supply one of names_sep or names_pattern to specify how the column names should be split.

library(tidyr)

df %>%
  pivot_longer(-1, names_to = c("field_name", ".value"), names_sep = "\\.")

# # A tibble: 2 × 4
#      id field_name     x     y
#   <dbl> <chr>      <dbl> <dbl>
# 1     1 field_a       10    20
# 2     1 field_b       30    40

names_sep = "\\." can be replaced with names_pattern = "(. )\\.(. )".