Break a dataframe which is spread across one row to multiple row with specific column-CodePudding

I have a data set which looks like this example

name  test marks   name.1  test.1  marks.1 name.2 test.2 marks.2  name.3 test.3   marks.3
Jon   math  456     Ria   history  564      Nia   math   456       Tom   physics  567

However, my original dataset is very wide with around 700 columns. I wish to reshape this dataset which is spread across one row to multiple row in R

I want an output which should look like this

name test     marks
Jon  math     456
Ria  history  564
Nia  math     456
Tom  physics  567

I referred Reshaping multiple sets of columns (wide format) into single columns (long format) , Turning one row into multiple rows in r and many more but unable to find a proper solution.

I will really appreciate any help

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(everything(), names_to = c(".value", NA), names_pattern = "([^.] )\\.?(.*)")

# # A tibble: 4 × 3
#   name  test    marks
#   <chr> <chr>   <int>
# 1 Jon   math      456
# 2 Ria   history   564
# 3 Nia   math      456
# 4 Tom   physics   567

Data

df <- structure(list(name = "Jon", test = "math", marks = 456L, name.1 = "Ria",
    test.1 = "history", marks.1 = 564L, name.2 = "Nia", test.2 = "math",
    marks.2 = 456L, name.3 = "Tom", test.3 = "physics", marks.3 = 567L), class = "data.frame", row.names = c(NA, -1L))