Separate a column into multiple variables and making the df long

Question

I am getting a little bit puzzled on reshaping my dataframe in R. Let's say I have a dataframe like this:

SVI     SDI     CVI     CDE
12      10      5        3
11      9       4        2

So every column represents a set of sub-variables which I would like to keep distinct now like this:

Value    First_letter  Second_letter  Third_letter 
12       S              V             I
11       S              V             I
10       S              D             I
9        S              D             I
5        C              V             I
4        C              V             I
3        C              D             E
2        C              D             E

Is there a way to avoid doing this manually? I was thinking of using Reshape2, but I cannot really come up with a way to sort it out. Thanks!

I am not familiar with `Reshape2` but I am very satisfied with `tidyverse` functions. [Here are some more informations about it](https://r4ds.had.co.nz/). And more precisely, `tidyr` has awesome functions such as `pivot_longer()` and `pivot_wider()` (see [this website](https://tidyr.tidyverse.org/) for more info and comparisons with Reshape2) — Paul, Aug 19 '20 at 09:04

score 0 · Accepted Answer · answered Aug 19 '20 at 08:51

Here is a solution using tidyr (part of tidyverse).

library(tidyr)

# your initial dataset
df = data.frame(SVI = c(12,11),
                SDI = c(10,9),
                CVI = c(5,4),
                CDE = c(3,2))

# The output you want
df_pivot = df %>% 
  pivot_longer(1:length(.)) %>% 
  separate(name, into = c("First_letter", "Second_letter", "Third_letter"), sep = c(1,2,3)) # see ?separate for more informations

Output:

df_pivot
# A tibble: 8 x 4
  First_letter Second_letter Third_letter value
  <chr>        <chr>         <chr>        <dbl>
1 S            V             I               12
2 S            D             I               10
3 C            V             I                5
4 C            D             E                3
5 S            V             I               11
6 S            D             I                9
7 C            V             I                4
8 C            D             E                2

Separate a column into multiple variables and making the df long

1 Answers1