Wide to long data in R

Question

I have following data in R:

structure(list(Name = 1:4, Paper1 = c("C1", "C1", "C1", "C1"), 
Marks1 = 1:4, Paper2 = c("D1", "D1", "D1", "D1"), Marks2 = 1:4, 
Paper3 = c("E1", "E1", "E1", "E1"), Marks3 = 12:15), class = "data.frame", row.names = c(NA, -4L))

I want to arrange my data like this:

structure(list(Name = c(1L, 1L, 1L, 2L, 2L, 2L), Paper = c("C1", 
"D1", "E1", "C1", "D1", "E1"), Marks = c(1L, 1L, 12L, 2L, 2L, 
13L)), class = "data.frame", row.names = c(NA, -6L))

I tried, shape, melt, but both are not providing desired output. Please suggests the solution.

What happened to name 3 and 4 in your output data? – s_baldur Aug 01 '22 at 11:33 — s_baldur, Aug 01 '22 at 11:33

s_baldur · Accepted Answer · 2022-08-01T13:33:47.027

One option with melt():

library(data.table)
setDT(df)

melt(
  df, 
  id.vars      = 'Name', 
  measure.vars = patterns('^Paper', '^Marks'),
  value.name   = c('Paper', 'Marks')
)[order(Name), !'variable']

#     Name Paper Marks
#  1:    1    C1     1
#  2:    1    D1     1
#  3:    1    E1    12
#  4:    2    C1     2
#  5:    2    D1     2
#  6:    2    E1    13
#  7:    3    C1     3
#  8:    3    D1     3
#  9:    3    E1    14
# 10:    4    C1     4
# 11:    4    D1     4
# 12:    4    E1    15

harre · Answer 2 · 2022-08-01T12:39:30.163

For a shorter dplyr option, we could use pivot_longer using the names_sep-argument with either regex-look-around to separate before the digit..

library(dplyr)

df |> 
  pivot_longer(-Name, 
               names_to = c(".value", NA), 
               names_sep = "(?=\\d)")

.. or as pointed out by @Allan Cameron splitting at the fifth position: names_sep = 5.

If you want to keep the numbers of the corresponding papers and marks, you can replace NA in the names_to with your desired column name.

Output:

# A tibble: 12 × 3
    Name Paper Marks
   <int> <chr> <int>
 1     1 C1        1
 2     1 D1        1
 3     1 E1       12
 4     2 C1        2
 5     2 D1        2
 6     2 E1       13
 7     3 C1        3
 8     3 D1        3
 9     3 E1       14
10     4 C1        4
11     4 D1        4
12     4 E1       15

score 3 · Answer 3 · answered Aug 01 '22 at 11:43

A tidyverse way.

df1 <-
  structure(list(
    Name = 1:4, 
    Paper1 = c("C1", "C1", "C1", "C1"), 
    Marks1 = 1:4, 
    Paper2 = c("D1", "D1", "D1", "D1"), 
    Marks2 = 1:4, 
    Paper3 = c("E1", "E1", "E1", "E1"), 
    Marks3 = 12:15), 
    class = "data.frame", row.names = c(NA, -4L))

df2 <-
  structure(list(
    Name = c(1L, 1L, 1L, 2L, 2L, 2L), 
    Paper = c("C1", "D1", "E1", "C1", "D1", "E1"), 
    Marks = c(1L, 1L, 12L, 2L, 2L, 13L)), 
    class = "data.frame", row.names = c(NA, -6L))


suppressPackageStartupMessages(library(tidyverse))

df1 %>%
  mutate(across(starts_with("Marks"), as.character)) %>%
  pivot_longer(-Name, names_to = "PM") %>%
  mutate(PM = gsub("\\d+", "", PM)) %>% 
  group_by(PM) %>%
  mutate(id = row_number()) %>% 
  pivot_wider(
    id_cols = c(id, Name),
    names_from = PM,
    values_from = value
  ) %>%
  select(-id)
#> # A tibble: 12 × 3
#>     Name Paper Marks
#>    <int> <chr> <chr>
#>  1     1 C1    1    
#>  2     1 D1    1    
#>  3     1 E1    12   
#>  4     2 C1    2    
#>  5     2 D1    2    
#>  6     2 E1    13   
#>  7     3 C1    3    
#>  8     3 D1    3    
#>  9     3 E1    14   
#> 10     4 C1    4    
#> 11     4 D1    4    
#> 12     4 E1    15

^{Created on 2022-08-01 by the reprex package (v2.0.1)}

score 3 · Answer 4 · answered Aug 01 '22 at 12:03

Here's a somewhat simpler tidyverse solution

library(tidyverse)

df %>%
group_by(Name) %>%
  summarise(Paper = unlist(across(starts_with('Paper'), c)),
            Marks = unlist(across(starts_with('Marks'), c)))
#> # A tibble: 12 x 3
#> # Groups:   Name [4]
#>     Name Paper Marks
#>    <int> <chr> <int>
#>  1     1 C1        1
#>  2     1 D1        1
#>  3     1 E1       12
#>  4     2 C1        2
#>  5     2 D1        2
#>  6     2 E1       13
#>  7     3 C1        3
#>  8     3 D1        3
#>  9     3 E1       14
#> 10     4 C1        4
#> 11     4 D1        4
#> 12     4 E1       15

score 3 · Answer 5 · answered Aug 01 '22 at 12:18

A base R option using reshape

reshape(
  df,
  direction = "long",
  idvar = "Name",
  varying = -1,
  v.names = c("Paper","Marks")
)

gives

    Name time Paper Marks
1.1    1    1     1    C1
2.1    2    1     2    C1
3.1    3    1     3    C1
4.1    4    1     4    C1
1.2    1    2     1    D1
2.2    2    2     2    D1
3.2    3    2     3    D1
4.2    4    2     4    D1
1.3    1    3    12    E1
2.3    2    3    13    E1
3.3    3    3    14    E1
4.3    4    3    15    E1

PaulS · Answer 6 · 2022-08-01T12:25:49.747

Another possible solution, based on purrr::map2_dfr:

library(tidyverse)

map2_dfr(select(df, starts_with("Paper")), select(df, starts_with("Marks")),
         ~ tibble(Name = df$Name, Paper = .x, Marks = .y))

#> # A tibble: 12 × 3
#>     Name Paper Marks
#>    <int> <chr> <int>
#>  1     1 C1        1
#>  2     2 C1        2
#>  3     3 C1        3
#>  4     4 C1        4
#>  5     1 D1        1
#>  6     2 D1        2
#>  7     3 D1        3
#>  8     4 D1        4
#>  9     1 E1       12
#> 10     2 E1       13
#> 11     3 E1       14
#> 12     4 E1       15

score 2 · Answer 7 · 2022-08-01T14:56:07.150

Another way of doing it is removing the 1 from the names and then stack them with rbind

colnames(df) <- (colnames(df) |> stringr::str_extract("\\D+"))


cbind(df[1], rbind(df[2:3],df[4:5],df[6:7]))

   Name Paper Marks
1     1    C1     1
2     2    C1     2
3     3    C1     3
4     4    C1     4
5     1    D1     1
6     2    D1     2
7     3    D1     3
8     4    D1     4
9     1    E1    12
10    2    E1    13
11    3    E1    14
12    4    E1    15

Less hard coded

cbind(df[1],
      do.call(rbind,
              lapply(seq(0, ncol(df) - 2, 2 ), \(x) {
              colnames(df) <- strsplit(colnames(df), "\\d")
  df[x + 2:3]
})))

score 2 · Answer 8 · answered Aug 01 '22 at 12:42

Another option using to_long from sjmisc:

df <- structure(list(Name = 1:4, Paper1 = c("C1", "C1", "C1", "C1"), 
                     Marks1 = 1:4, Paper2 = c("D1", "D1", "D1", "D1"), Marks2 = 1:4, 
                     Paper3 = c("E1", "E1", "E1", "E1"), Marks3 = 12:15), class = "data.frame", row.names = c(NA, -4L))

library(sjmisc)
output <- to_long(df, 
        key = 'group', 
        values = c('Paper', "Marks"), 
        grep("Paper", names(df)),
        grep("Marks", names(df)),
        recode.key = TRUE)[,-2]
output[order(output$Name),]
#>    Name Paper Marks
#> 1     1    C1     1
#> 5     1    D1     1
#> 9     1    E1    12
#> 2     2    C1     2
#> 6     2    D1     2
#> 10    2    E1    13
#> 3     3    C1     3
#> 7     3    D1     3
#> 11    3    E1    14
#> 4     4    C1     4
#> 8     4    D1     4
#> 12    4    E1    15

^{Created on 2022-08-01 by the reprex package (v2.0.1)}

AlpacaKing · Answer 9 · 2023-02-26T08:07:47.930

0

library(tidyverse)

pivot_longer(
  data = df,
  cols = -Name, # choose all columns except the "Name" column
  names_to = c(".value", ".no"), # new column names according to names_pattern
  names_pattern = "(.*)(\\d)" # separate into two parts, i.e. (string)(number)
)

edited Feb 26 '23 at 08:07

answered Aug 07 '22 at 08:26

AlpacaKing

371
2
10

Wide to long data in R

9 Answers9