CODEWITHSUNDEEP
rdplyr
machine_apprentice
machine_apprentice

Reputation: 439

Stack Separate Columns with Same Identifier R

Is it possible to merge columns with the same identifier and stack these on top of one another from the same dataframe. Also to then remove NA values as seen in the Desired DataFrame output?

Current DataFrame

id    Col1   Col2   Col3   
1     A      NA     C   
1     NA     B      NA       
2     NA     B      NA  
2     A      NA     C 
3     A      NA     C

Desired DataFrame

id    NewCol       
1     A    
1     B      
1     C   
2     A    
2     B
2     C
3     A
3     C

Upvotes: 1

Views: 93

Answers (2)

TarJae
TarJae

Reputation: 78927

As an alternative we could use splitshakeshape packackge:

library(splitstackshape)
library(dplyr)

df %>% 
  merged.stack(df, id.vars="id", 
             var.stubs="Col", sep = ".") %>% 
  na.omit() %>% 
  select(-.time_1)

   id Col
1:  1   A
2:  1   B
3:  1   C
4:  2   A
5:  2   B
6:  2   C
7:  3   A
8:  3   C

Upvotes: 1

akrun
akrun

Reputation: 887048

Reshape from 'wide' to 'long' with pivot_longer while removing the NAs with values_drop_na and then do the arrange on all the columns

library(tidyr)
library(dplyr)
pivot_longer(df1, cols = starts_with('Col'), values_to = 'NewCol', 
        names_to = NULL, values_drop_na = TRUE) %>%
     arrange(across(everything()))

-output

# A tibble: 8 × 2
     id NewCol
  <int> <chr> 
1     1 A     
2     1 B     
3     1 C     
4     2 A     
5     2 B     
6     2 C     
7     3 A     
8     3 C

data

df1 <- structure(list(id = c(1L, 1L, 2L, 2L, 3L), Col1 = c("A", NA, 
NA, "A", "A"), Col2 = c(NA, "B", "B", NA, NA), Col3 = c("C", 
NA, NA, "C", "C")), class = "data.frame", row.names = c(NA, -5L
))

Upvotes: 2

Related Questions