Pivot_longer into an already existing column-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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