Transform multiple columns of the same name and different suffixes into a panel structure

user_14380501 0
Viewed 42

I need to put multiple variables of the same name but with different suffixes in a panel structure. For example, transforming:

enter image description here

In this structure:

enter image description here

I tried to combine the pivot_longer and pivot_wider functions from the tidyverse package, but I was not successful, as the variables are distributed between numerics, integers, characters, etc.

I appreciate any help.

Here's the reproducible example:

structure(list(class.x = c(4, 4, 4, 4, 4), class.y = c("a", "a",
"a", "a", "a"), class.x.x = structure(c(9.88131291682493e-324,
9.88131291682493e-324, 9.88131291682493e-324, 9.88131291682493e-324,
9.88131291682493e-324), class = "integer64"), var1.x = c(1, 1,
1, 1, 1), var1.y = c(0, 0, 0, 0, 0), var1.x.x = c("b", "b", "b",
"b", "b"), var2.x = c(9, 9, 9, 9, 9), var2.y = c(5, 5, 5, 5,
5), var2.x.x = c("c", "c", "c", "c", "c")), class = "data.frame", row.names = c(NA,
-5L))
tidyverse r
2 Answers
df %>%
  pivot_longer(everything(), 
               names_to = c('.value','Variable'),
               names_pattern = '([^.]+)[.](.*)',
               values_transform = as.character)

# A tibble: 15 x 4
   Variable class var1  var2 
    <chr>    <chr> <chr> <chr>
 1 x        4     1     9    
 2 y        a     0     5    
 3 x.x      0     b     c    
 4 x        4     1     9    
 5 y        a     0     5    
 6 x.x      0     b     c    
 7 x        4     1     9    
 8 y        a     0     5    
 9 x.x      0     b     c    
10 x        4     1     9    
11 y        a     0     5    
12 x.x      0     b     c    
13 x        4     1     9    
14 y        a     0     5    
15 x.x      0     b     c
user_83802720

Note the provided dput varies from the picture you posted:

First we could create names that are all separated by one .

Then we have to transform all to character: I do it with mutate(across... KU99 did it more elegantly with values_transform!

Now we can apply pivot_longer with names_sep argument.

Finally bring data in shape.

library(tidyverse)

df %>% 
  rename_with(~str_replace_all(., ".x.x", ".z")) %>% 
  mutate(across(everything(), as.character)) %>% 
  pivot_longer(
    everything(),
    names_to = c(".value", "var1_2"),
    names_sep ="\\."
  ) %>% 
  arrange(var1_2) %>% 
  mutate(Variable=ifelse(var1_2 == "z", "x.x", var1_2), .keep="unused")



     class                 var1  var2  Variable
   <chr>                 <chr> <chr> <chr>   
 1 4                     1     9     x       
 2 4                     1     9     x       
 3 4                     1     9     x       
 4 4                     1     9     x       
 5 4                     1     9     x       
 6 a                     0     5     y       
 7 a                     0     5     y       
 8 a                     0     5     y       
 9 a                     0     5     y       
10 a                     0     5     y       
11 9.88131291682493e-324 b     c     x.x     
12 9.88131291682493e-324 b     c     x.x     
13 9.88131291682493e-324 b     c     x.x     
14 9.88131291682493e-324 b     c     x.x     
15 9.88131291682493e-324 b     c     x.x
user_133216470
Related