Similar but different to: Split unique values into separate columns for multiple columns

Question

My question is if I have the next data frame in R.

a<-data.frame(col1=c("a","a","a","d","a"),
       col2=c("b","b","c","e","e"),
       col3=c("c","d","e",NA,NA),
       col4=c("d","e",NA,NA,NA),
       col5=c("e",NA,NA,NA,NA))

print(a)

col1| col2| col3| col4| col5|
 a     b     c     d     e
 a     b     d     e    NA
 a     c     e     NA   NA
 d     e     NA    NA   NA
 a     e     NA    NA   NA

I need other data frame like this:

b<-data.frame(col1=c("a","a","a",NA,"a"),
       col2=c("b","b",NA,NA,NA),
       col3=c("c",NA,"c",NA,NA),
       col4=c("d","d",NA,"d",NA),
       col5=c("e","e","e","e","e"))

print(b)

col1| col2| col3| col4| col5|
 a     b     c     d     e
 a     b     NA    d     e
 a     NA    c     NA    e
 NA    NA    NA    d     e
 a     NA    NA    NA    e

Sorry, I don't have the concepts to explain my problem, is for this reason that I question ask, but I guess that, I want first: separate by columns rows that be distinct to rest of group in a new column, and second, to get the rows that have the same values in a same column.

I think that my problem is similar to this: Split unique values into separate columns for multiple columns

If someone can help me I will be very thankful.

Answer 1

Using some tidyverse libraryies you can do

library(dplyr)
library(tidyr)
a %>% 
  mutate(id=row_number()) %>% 
  pivot_longer(-id) %>% 
  filter(!is.na(value)) %>% 
  pivot_wider(id_cols=id, names_from="value", values_from="value") %>%
  select(-id)

We use the pivot functions to reshape and transform the data. The tricks is just to add the id column to make it easier to rack the data on a per-row basis. This returns

  a     b     c     d     e    
  <chr> <chr> <chr> <chr> <chr>
1 a     b     c     d     e    
2 a     b     NA    d     e    
3 a     NA    c     NA    e    
4 NA    NA    NA    d     e    
5 a     NA    NA    NA    e 

Answer 2

Another base R option:

setNames(data.frame(sapply(sort(na.omit(unique(unlist(a)))), 
       function(x) ifelse(rowSums(a==x, na.rm=TRUE) > 0, x, NA))), colnames(a))

#>   col1 col2 col3 col4 col5
#> 1    a    b    c    d    e
#> 2    a    b <NA>    d    e
#> 3    a <NA>    c <NA>    e
#> 4 <NA> <NA> <NA>    d    e
#> 5    a <NA> <NA> <NA>    e

Answer 3

We can do this in base R

t(apply(a, 1, function(x) {
    v1 <- character(length(x))
    v1[match(x, letters, nomatch = 0)] <- x
   v1}))
#   [,1] [,2] [,3] [,4] [,5]
#[1,] "a"  "b"  "c"  "d"  "e" 
#[2,] "a"  "b"  ""   "d"  "e" 
#[3,] "a"  ""   "c"  ""   "e" 
#[4,] ""   ""   ""   "d"  "e" 
#[5,] "a"  ""   ""   ""   "e" 

---

Or another option is

b <- a
m1 <- t(apply(a, 1, function(x) {table(factor(x, levels = letters[1:5]))})) > 0
b[] <- colnames(m1)[col(m1)* NA^!m1]
b
#  col1 col2 col3 col4 col5
#1    a    b    c    d    e
#2    a    b <NA>    d    e
#3    a <NA>    c <NA>    e
#4 <NA> <NA> <NA>    d    e
#5    a <NA> <NA> <NA>    e

Or a slight variation of the above

t(apply(a, 1, function(x) {
  tbl1 <- table(factor(x, levels = letters[1:5]))
  ifelse(tbl1 >0, names(tbl1), NA)}))