Pivot_longer into an already existing column

user_15897371 0
Viewed 127

I want to pivot multiple columns, two by two into an already existing couple, from this

have <- tribble(
  ~egtest,~egorres,  ~egorresu, ~hrorres,~hrorresu,~prorres,~prorresu,~uninteresing,
  "qt", 500,"msec",90,"bpm",100,"msec", "cat",
  "qtc", 370,"msec",NA,"bpm",103,"msec","dog",
  "pra",83,"msec",79,"bpm",97,"msec","cat"
)

To this :

want <- tribble(
  ~egtest,~egorres,  ~egorresu,~uninteresting,
  "qt", 500,"msec","cat",
  "qtc", 370,"msec","dog",
  "pra",83,"msec","cat",
  "hr",90,"bpm","cat",
  "pr",100,"msec","cat",
  "hr",NA,"bpm","dog",
  "pr",103,"msec","dog",
  "hr",79,"bpm","cat",
  "pr",97,"msec","dog"
)

For now my code is

colstopivotEG <- function(table){
  out <-  subset(colnames(table),grepl(pattern = "orres\\b",colnames(table)))
  out <- out[out != "egorres"]
  #print(out)
  return(out)
}
pivot_eg <- function(ndf){
  EG1 <- pivot_longer(ndf,
                      cols = colstopivotEG(ndf),
                      names_pattern = "(.*)orres",
                      names_to="egtest",
                      values_to="egorres")
  
  
  EG2 <- pivot_longer(ndf,
                      cols=ends_with("orresu"),
                      names_pattern = "(.*)orresu",
                      values_to="egorresu")
  ndf <- bind_cols(EG1,EG2 %>% select(EGORRESU_STD))
}

But I can't seem to be able to pivot into an existing column, I'm out of ideas and any help could be great thanks !

PS: There's a lot of column that don't want to be pivoted

tidyr dplyr r
2 Answers

I would split the tibble into two by columns:

  • The columns starting with eg (keep them as they are)
  • The rest (pivot them).

Afterwards (after repairing the second tibble's names) we can bind the two tibbles together again.

library(dplyr)
library(tidyr)

eg <- have %>% 
  select(starts_with("eg"))

rest <- have %>% 
  select(-starts_with("eg")) %>% 
  pivot_longer(everything(),
               names_pattern = "(hr|pr)(.+)",
               names_to = c("egtest", ".value")) %>% 
  rename(egorres = orres,
         egorresu = orresu)

bind_rows(eg, rest)

which gives

  egtest egorres egorresu
  <chr>    <dbl> <chr>   
1 qt         500 msec    
2 qtc        370 msec    
3 pra         83 msec    
4 hr          90 bpm     
5 pr         100 msec    
6 hr          NA bpm     
7 pr         103 msec    
8 hr          79 bpm     
9 pr          97 msec
user_10622250

Another possible solution:

library(tidyverse)

bind_rows(have[c(1:3,8)],
  map(list(c(4:5,8), 6:8),
  ~ bind_cols(egtest = str_sub(names(have[.x])[1], 1, 2), have[.x] %>% 
    set_names(names(have[c(2:3,8)])))))

#> # A tibble: 9 × 4
#>   egtest egorres egorresu uninteresing
#>   <chr>    <dbl> <chr>    <chr>       
#> 1 qt         500 msec     cat         
#> 2 qtc        370 msec     dog         
#> 3 pra         83 msec     cat         
#> 4 hr          90 bpm      cat         
#> 5 hr          NA bpm      dog         
#> 6 hr          79 bpm      cat         
#> 7 pr         100 msec     cat         
#> 8 pr         103 msec     dog         
#> 9 pr          97 msec     cat
user_115644870
Related