Split variable from comma into an ordered dataframe

Question

I have a dataframe like this, where the values are separated by comma.

# Events
# A,B,C
# C,D
# B,A
# D,B,A,E
# A,E,B

I would like to have the next data frame

# Event1  Event2  Event3  Event4  Event5
# A       B       C       NA      NA
# NA      NA      C       NA      NA
# A       B       NA      NA      NA
# A       B       NA      D       E
# A       B       NA      NA      E

I have tried with cSplit but I don't have the desired df. Is possible?

NOTE: The values doesn't appear in the same possition as the variable Event in the second dataframe.

Does this answer your question? [Split strings by commas into columns](https://stackoverflow.com/questions/70501700/split-strings-by-commas-into-columns) — SamR, Jul 11 '22 at 15:14
Thanks @SamR but it doesn't. The difference is that my dataframe needs to be ordered. — Kevin Santos, Jul 11 '22 at 15:25

G. Grothendieck · Accepted Answer · 2022-07-11T17:29:04.160

1) Here is a base R solution. split each row giving list s and create cols which contains the possible values. Then iterate over s and convert that to a data frame.

Note that this does not hard code the column names and continues to work even if some column names are substrings of other column names.

s <- strsplit(DF$Events, ",")
cols <- unique(sort(unlist(s)))

data.frame(Event = t(sapply(s, function(x) ifelse(cols %in% x, cols, NA))))

giving:

  Event.1 Event.2 Event.3 Event.4 Event.5
1       A       B       C    <NA>    <NA>
2    <NA>    <NA>       C       D    <NA>
3       A       B    <NA>    <NA>    <NA>
4       A       B    <NA>       D       E
5       A       B    <NA>    <NA>       E

2) This base R solution uses strsplit as above and then names the components since stack requires a named list and then invokes stack. Then we expand that into a wide form using tapply and convert it to a data frame and fix up the names.

s <- strsplit(DF$Events, ",")
names(s) <- seq_along(s)
stk <- stack(s)

mat <- t(tapply(stk$values, stk, c))
colnames(mat) <- NULL
data.frame(Event = mat)

giving:

  Event.1 Event.2 Event.3 Event.4 Event.5
1       A       B       C    <NA>    <NA>
2    <NA>    <NA>       C       D    <NA>
3       A       B    <NA>    <NA>    <NA>
4       A       B    <NA>       D       E
5       A       B    <NA>    <NA>       E

This could also be represented as an R 4.2+ pipeline:

DF |>
  with(setNames(Events, seq_along(Events))) |>
  strsplit(",") |>
  stack() |>
  with(tapply(values, data.frame(ind, values), c)) |>
  `colnames<-`(NULL) |>
  data.frame(Event = _)

Note

The input in reproducible form:

Lines <- "Events
A,B,C
C,D
B,A
D,B,A,E
A,E,B"
DF <- read.table(text = Lines, header = TRUE, strip.white = TRUE)

Thanks. But if you see the df, All letters A must be in Event_1, B in Event_2, etc. — Kevin Santos, Jul 11 '22 at 15:22

shafee · Answer 2 · 2022-07-11T15:45:26.920

Another approach using tidyverse:

library(dplyr)
library(purrr)
library(stringr)

Events = c("A,B,C", 'C,D', "B,A", "D,B,A,E", "A,E,B")

letters <- Events %>% str_split(",") %>% unlist() %>% unique()

df <- data.frame(Events)

df %>% 
  map2_dfc(.y = letters, ~ ifelse(str_detect(.x, .y), .y, NA)) %>% 
  set_names(nm = paste0("Events", 1:length(letters)))

#> # A tibble: 5 × 5
#>   Events1 Events2 Events3 Events4 Events5
#>   <chr>   <chr>   <chr>   <chr>   <chr>  
#> 1 A       B       C       <NA>    <NA>   
#> 2 <NA>    <NA>    C       D       <NA>   
#> 3 A       B       <NA>    <NA>    <NA>   
#> 4 A       B       <NA>    D       E      
#> 5 A       B       <NA>    <NA>    E

^{Created on 2022-07-11 by the reprex package (v2.0.1)}

score 0 · Answer 3 · answered Jul 11 '22 at 16:25

This tidyverse solution is easily the most economical in terms of amount of code used:

library(tidyverse)

data.frame(Events) %>%
  # split the strings by the comma:
  mutate(Events = str_split(Events, ",")) %>%
  # unnest splitted values wider into columns:
  unnest_wider(Events, names_sep = "")
# A tibble: 5 × 4
  Events1 Events2 Events3 Events4
  <chr>   <chr>   <chr>   <chr>  
1 A       B       C       NA     
2 C       D       NA      NA     
3 B       A       NA      NA     
4 D       B       A       E      
5 A       E       B       NA

Data:

Events = c("A,B,C", 'C,D', "B,A", "D,B,A,E", "A,E,B")

ThomasIsCoding · Answer 4 · 2022-07-12T05:51:45.723

0

We can try the following base R code

> d <- t(table(stack(setNames(strsplit(df$Events, ","), 1:nrow(df)))))

> as.data.frame.matrix(`dim<-`(colnames(d)[ifelse(d > 0, d * col(d), NA)], dim(d)))
    V1   V2   V3   V4   V5
1    A    B    C <NA> <NA>
2 <NA> <NA>    C    D <NA>
3    A    B <NA> <NA> <NA>
4    A    B <NA>    D    E
5    A    B <NA> <NA>    E

edited Jul 12 '22 at 05:51

answered Jul 11 '22 at 21:52

ThomasIsCoding

96,636
9
24
81

Split variable from comma into an ordered dataframe

4 Answers4

Note