Pivot data into two different columns simultaneously using pivot_longer() in R?

Question

I'm trying to reshape a dataframe from wide to long while creating two columns simultaneously.

My original dataframe looks like this:

# Sample data frame.
id     <- c(1,2)
v1.act <- c(.4, .8)
v2.act <- c(.5, .7)
v1.fix <- c(1, 0)
v2.fix <- c(0, 1)
df     <- data.frame(id, v1.act, v2.act, v1.fix, v2.fix)

  id v1.act v2.act v1.fix v2.fix
1  1    0.4    0.5      1      0
2  2    0.8    0.7      0      1

I would like to place v1 and v2 into a single column called variable. The two values for each variable should be placed into two separate columns: activation and fixation.

The target dataframe should look like this:

  id variable activation fixation
1  1       v1        0.4        1
2  1       v2        0.5        0
3  2       v1        0.8        0
4  2       v2        0.7        1

Any ideas of how to get to this output?

I have tried with the pivot_longer() function, but I can't figure out how to create the activation and fixation columns simultaneously (and I have failed at doing it successively).

Any ideas would be greatly appreciated! I'm pretty new to tidyr.

score 2 · Accepted Answer · answered Oct 29 '21 at 15:28

Edit

Turns out, you can do it in one pivot_longer:

df %>% 
  pivot_longer(-id,
               names_to = c("variable", ".value"),
               names_pattern = "(.*)\\.(.*)")%>% 
  rename(activation = act, fixation = fix)

with the same result.

Don't know how to do it in one go, but you could use

library(tidyr)
library(dplyr)

df %>% 
  pivot_longer(-id,
               names_to = c("variable", "class"),
               names_pattern = "(.*)\\.(.*)") %>% 
  pivot_wider(names_from = "class") %>% 
  rename(activation = act, fixation = fix)

This returns

# A tibble: 4 x 4
     id variable activation fixation
  <dbl> <chr>         <dbl>    <dbl>
1     1 v1              0.4        1
2     1 v2              0.5        0
3     2 v1              0.8        0
4     2 v2              0.7        1

the one line solution works like magic! Thanks a bunch!! – Sol Oct 29 '21 at 16:11 — Sol, Oct 29 '21 at 16:11

score 2 · Answer 2 · answered Oct 29 '21 at 15:32

df %>% 
        pivot_longer(-id) %>% 
        separate(name, into = c("variable", "b")) %>% 
        pivot_wider(names_from="b", values_from="value")

# A tibble: 4 × 4
     id variable   act   fix
  <dbl> <chr>    <dbl> <dbl>
1     1 v1         0.4     1
2     1 v2         0.5     0
3     2 v1         0.8     0
4     2 v2         0.7     1

EDIT: Clearly Martin Gal's one liner is more efficient, but I'll leave this one here.

Pivot data into two different columns simultaneously using pivot_longer() in R?

2 Answers2

Edit

Linked