separate (or similar function) with multiple or no occurrences of splitting character

user_8839068 0
Viewed 664

I have a tibble like this

library("tidyverse")
tib <- tibble(x = c("lemon", "yellow, banana", "red, big, apple"))

I would like to create two new columns named description and fruit and extract the last word after the comma using separate (if there is a comma; otherwise, I would like to just copy the word in the cell).

So far, I have

tib %>%
    separate(x, ", ", into = c("description", "fruit"), remove = FALSE)

but this doesn't quite do what I want, yielding:

# A tibble: 3 x 3
  x               description fruit 
  <chr>           <chr>       <chr> 
1 lemon           lemon       NA    
2 yellow, banana  yellow      banana
3 red, big, apple red         big   
Warning messages:
1: Expected 2 pieces. Additional pieces discarded in 1 rows [3]. 
2: Expected 2 pieces. Missing pieces filled with `NA` in 1 rows [1].

The output I want is:

  x               description fruit 
1 lemon           NA          lemon    
2 yellow, banana  yellow      banana
3 red, big, apple red, big    apple

Can someone point me to the part I'm missing?

EDIT

The goal doesn't have to be achieved using separate. mutate would also work, and solutions are equally appreciated!

r regex
3 Answers

It may be better with extract. Here, we can use capture groups to capture the characters as a group. It is better to start at the end ($) and go backwards, i.e. word (\\w+) at the end captured, succeeds a , or space (\\s) and all other characters in the first capture group ((.*?)) 

library(tidyr)
library(dplyr)
tib %>%
   extract(x, into = c("description", "fruit"), remove = FALSE, '(.*?),?\\s?(\\w+$)')

Or using regex lookaround with separate, by specifying the delimiter as either the , followed by space or the start (^) of the string followed by a word (\\w+) at the end ($) of the string

tib %>%
   separate(x, into = c("description", 'fruit'),
       remove = FALSE, '(, |^)(?=\\w+$)') %>%
   mutate(description = na_if(description, ""))

Also, another option with separate would be to insert a new delimiter before the last word and then use that as sep

library(stringr)
tib %>% 
  mutate(x1 = str_replace(x, ',? ?(\\w+)$', ";\\1")) %>% 
  separate(x1, into = c("description", "fruit"), sep=";") %>%
  mutate(description = na_if(description, ""))
# A tibble: 3 x 3
#  x               description fruit 
#  <chr>           <chr>       <chr> 
#1 lemon           <NA>        lemon 
#2 yellow, banana  yellow      banana
#3 red, big, apple red, big    apple
user_37322710

You can use regex to grab description - replace last comma and everything after it. ",[^,]+$" matches comma followed by anything that is not a comma up to the end.

For getting the fruit, use the word function of stringr package to grab the last word.

tib %>%
    mutate(desc = if_else(grepl(",", x), sub(",[^,]+$", "", x), NA_character_),
           fruit = stringr::word(x, -1))
user_71289340

Regex-based solutions, like the other two here, are probably better. But if for whatever reason you'd like to work with lists of words instead, here's another option.

Split the text into a list of strings. The description is everything but the item at position length(words). The fruit is the last item. If having a blank string instead of NA is fine, you can delete the na_if bit.

library(dplyr)

tib <- tibble(x = c("lemon", "yellow, banana", "red, big, apple"))
tib %>%
  mutate(words = strsplit(x, ", "),
         description = purrr::map_chr(words, ~paste(.[-length(.)], collapse = ", ")) %>% na_if(""),
         fruit = purrr::map_chr(words, last))
#> # A tibble: 3 x 4
#>   x               words     description fruit 
#>   <chr>           <list>    <chr>       <chr> 
#> 1 lemon           <chr [1]> <NA>        lemon 
#> 2 yellow, banana  <chr [2]> yellow      banana
#> 3 red, big, apple <chr [3]> red, big    apple

Obviously you can then drop the words column—I left it in just to show its type.

user_53258620
Related