Split a large character column with different amount of values into multiple columns

Question

I have a character column with a different amount of values per row. This is just a small example:

GoodForMeal %>% head(5)
# A tibble: 5 x 1
GoodForMeal                                                                                
<chr>
1 dessert': False, 'latenight': False, 'lunch': True, 'dinner': True
2 dessert': False, 'latenight': False, 'lunch': True, 'dinner': True 
3 <NA>
4 dessert': False, 'latenight': False, 'lunch': True, 'dinner': True 
5 dessert': False, 'latenight': False, 'lunch': True, 'dinner': True

Here is a dput() of the first row of the column:

structure(list(GoodForMeal = "dessert': False, 'latenight': False, 'lunch': True, 'dinner': True, 'breakfast': False, 'brunch': False}"), .Names = "GoodForMeal", row.names = c(NA, 
-1L), class = c("tbl_df", "tbl", "data.frame"))

I want to assign the values before the colon as column names and the values after the colon as the values of the respective column.

Example:

   desert latenight lunch diner 
1  False  False     True  True
2  False  False     True  True
3  NA     NA        NA    NA  
4  False  False     True  True
5  False  False     True  True

I tried it with the tidyr packadge and the separate and the spread function:

separate(GoodForMeal, c("key", "value"), sep = ":", extra = "merge") %>% spread(key, value)

The problem is the r is not splitting all the values before the colon but just the first value.

So the result looks like this:

GoodForMeal %>% str()

Classes ‘tbl_df’, ‘tbl’ and 'data.frame':   4464 obs. of  2 variables:
 $ dessert': chr  " False, 'latenight': False, 'lunch': True, 'dinner': False, 'breakfast': False, 'brunch': False}" " False, 'latenight': False, 'lunch': True, 'dinner': True, 'breakfast': False, 'brunch': False}" " False, 'latenight': False, 'lunch': False, 'dinner': False, 'breakfast': False, 'brunch': False}" " False, 'latenight': False, 'lunch': True, 'dinner': True, 'breakfast': False, 'brunch': False}" ...
 $ <NA>    : chr  NA NA NA NA ...

Any Idea how to split the values so that it´s looking like in the example? THX

Can you provide a [reproducible example](http://stackoverflow.com/questions/5963269)? — Sotos, Nov 16 '17 at 12:28

score 1 · Accepted Answer · answered Nov 16 '17 at 13:33

Working with the test data you've provided, I would use mutate first to rid the column of characters such ' and :, along with the meal time keywords. This allows you to split on the comma that separates the various meal times. The following is an illustration:

df <- structure(list(GoodForMeal = "dessert': False, 'latenight': False, 'lunch': True, 'dinner': True, 'breakfast': False, 'brunch': False}"),
                .Names = "GoodForMeal", row.names = c(NA, -1L),
                class = c("tbl_df", "tbl", "data.frame"))

df %>%
  mutate(GoodForMeal = trimws(gsub("[':]|dessert|lunch|dinner|latenight|brunch",
                                   "",
                                   GoodForMeal))) %>%
  separate(GoodForMeal,
           c("dessert", "latenight", "lunch", "dinner"),
           ", ",
           extra="drop")

It should yield:

# A tibble: 1 x 4
# dessert latenight lunch dinner
# * <chr>     <chr> <chr>  <chr>
#   False     False  True   True

I hope this proves useful.

score 0 · Answer 2 · answered Nov 16 '17 at 13:09

This is not an elegant solution (and long) but seems to work. I did change the data to make it more general. Hope this can be a good start.

# i made some changes in the data; remove lunch entry in the 4th element and remove dessert in the 1st
sampleData <- c("'dessert': False, 'latenight': False, 'lunch': True, 'dinner': True",
            "'dessert': False, 'latenight': False, 'lunch': True, 'dinner': True",
            NA,
            "'dessert': False, 'latenight': False, 'dinner': True",
            "'latenight': False, 'lunch': True, 'dinner': True")

# [1] "'dessert': False, 'latenight': False, 'lunch': True, 'dinner': True"
# [2] "'dessert': False, 'latenight': False, 'lunch': True, 'dinner': True"
# [3] NA                                                                   
# [4] "'dessert': False, 'latenight': False, 'dinner': True"               
# [5] "'latenight': False, 'lunch': True, 'dinner': True" 

# not sure if this is necessary, but jsut to clean the data
sampleData <- gsub(x = sampleData, pattern = "'| ", replacement = "")

# i'm a data.table user, so i'll jsut use tstrsplit
# split the pairs within each elements first
x <- data.table::tstrsplit(sampleData, ",")

# split the header and the entry
test <- lapply(x, function(x) data.table::tstrsplit(x, ":", fixed = TRUE))

# get the headers
indexHeader <- do.call("rbind", lapply(test, function(x) x[[1]]))

# get the entries
indexValue <- do.call("rbind",
                      lapply(test, function(x){if(length(x) > 1){ return(x[[2]])}else{ return(x[[1]])} }))

# get unique headers
colNames <- unique(as.vector(indexHeader))

colNames <- colNames[!is.na(colNames)]

# determine the order of the entries using the header matrix
indexUse <- apply(indexHeader, 2, function(x) match(colNames, x))

# index the entry matrix using the above matching
resA <- mapply(FUN = function(x,y) x[y], 
               x = as.data.frame(indexValue), 
               y = as.data.frame(indexUse))

# convert to data frame
final <- as.data.frame(t(resA))

# rename columns
colnames(final) <- colNames

# should give something like this 
final 
# dessert latenight lunch dinner
# False     False  True   True
# False     False  True   True
# <NA>      <NA>  <NA>   <NA>
# False     False  <NA>   True
# <NA>     False  True   True

THX for your solution. It works but it doesn't look like in the example I posted above. — Banjo, Nov 16 '17 at 13:32

Split a large character column with different amount of values into multiple columns

2 Answers2