How to split column and keep first splitted value as representative one

Question

I have the following data frame

library(tidyverse)
#> + ggplot2 2.2.1.9000        Date: 2017-10-10
#> + tibble  1.3.4                R: 3.3.2
#> + tidyr   0.7.1               OS: macOS Sierra 10.12.6
#> + readr   1.1.1              GUI: X11
#> + purrr   0.2.3           Locale: en_US.UTF-8
#> + dplyr   0.7.3               TZ: Asia/Tokyo
#> + stringr 1.2.0           
#> + forcats 0.2.0
#> ── Conflicts ────────────────────────────────────────────────────
#> * filter(),  from dplyr, masks stats::filter()
#> * lag(),     from dplyr, masks stats::lag()

DF <- data.frame(V1=c("Place1-Place2-Place2-Place4-Place2-Place3-Place5",
                      "Place7",
                      "Place6-Place6",
                      "Place1-Place2-Place3-Place4"), 
                 V2=c(100,200,500,10)) %>% 

      as.tibble()


DF 
#> # A tibble: 4 x 2
#>                                                 V1    V2
#>                                             <fctr> <dbl>
#> 1 Place1-Place2-Place2-Place4-Place2-Place3-Place5   100
#> 2                                           Place7   200
#> 3                                    Place6-Place6   500
#> 4                      Place1-Place2-Place3-Place4    10

What I want to do is to split V1 by "-" and pick the first use first splitted value as the new value for V1. Resulting in:

   V1       V2                      
   Place1   100
   Place7   200
   Place6   500
   Place1    10

I am stuck with this:

as.data.frame(str_split_fixed(DF$V1, "-",8))
      V1     V2     V3     V4     V5     V6     V7 V8
1 Place1 Place2 Place2 Place4 Place2 Place3 Place5   
2 Place7                                             
3 Place6 Place6                                      
4 Place1 Place2 Place3 Place4

try `DF %>% mutate(index=1:n()) %>% separate_rows(V1, sep="-") %>% group_by(index) %>% slice(1)` — Roman, Oct 10 '17 at 07:37

akrun · Accepted Answer · 2017-10-10T07:33:42.877

3

We can use separate from tidyr with extra="merge" and later keep only the columns of interest

library(tidyr)
separate(DF, V1, into = c("V1", "Extra"), extra = "merge") %>% 
               select(-Extra)

Or with regex

DF %>%
    mutate(V1 =  sub("-.*", "", V1))
# A tibble: 4 x 2
#      V1    V2
#   <chr> <dbl>
#1 Place1   100
#2 Place7   200
#3 Place6   500
#4 Place1    10

edited Oct 10 '17 at 07:33

answered Oct 10 '17 at 07:31

akrun

874,273
37
540
662

Thanks. But there's a warning `Warning message: Too few values at 1 locations: 2 `. That's the reason why I don't use `separate`. – littleworth Oct 10 '17 at 07:32
1

@yaffle It is a friendly warning – akrun Oct 10 '17 at 07:33

score 2 · Answer 2 · answered Oct 10 '17 at 07:37

2

Here is a solution with base R:

DF <- data.frame(V1=c("Place1-Place2-Place2-Place4-Place2-Place3-Place5",
                      "Place7",
                      "Place6-Place6",
                      "Place1-Place2-Place3-Place4"), 
                 V2=c(100,200,500,10))
DF$V1 <- sub("-.*", "", DF$V1)
DF
# > DF
#       V1  V2
# 1 Place1 100
# 2 Place7 200
# 3 Place6 500
# 4 Place1  10

answered Oct 10 '17 at 07:37

jogo

12,469
11
37
42

How is the `sub` part different from my answer? – akrun Oct 10 '17 at 07:39

How to split column and keep first splitted value as representative one

2 Answers2