0

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

george g
  • 35
  • 5

1 Answers1

2

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))
Darren Tsai
  • 32,117
  • 5
  • 21
  • 51
  • Thanks @Darren Tsai, I tried the solution provided by you it is stacking all the values from second row for my dataset i.e It displaying a table with column `name` ,`test` ,`marks` with 1st row values and all other values as NA and stacking rest of the rows by creating another columns `name.` , `test.` and `marks.` Can you please suggest why this is happening? My dataset is exactly the example provided – george g Aug 19 '22 at 07:50
  • I am sorry but I am very new to R, how to set regex for `names_pattern` ? Is there any way to make regex patten? – george g Aug 19 '22 at 07:54