Separate column into three columns with grouping

Question

I have a column with Full names that should be separated into three columns just by spaces. The problem is that some full names contains more than three words, and 4-th and other words shouldn't be omitted, but added to third part.
For instance, "Abdullaeva Mehseti Nuraddin Kyzy" should be separated as:

| Abdullaeva | Mehseti | Nuraddin Kyzy |

I tried to split column with (tidyr) package as follow, but in this way 3d part contains only 1 word after second space.

df<-df %>%
    separate('FULL_NAME', c("1st_part","2d_part","3d_part"), sep=" ")

Any help will be appreciated.

Do you have always `name1 name2 fname lname` and you want `| name1 | name2 | fname lname |` ? — shA.t, Aug 15 '17 at 10:41
@shA.t, At almost most situation,yes, but I suppose it is possible situation, where I should group names in other way : `| name1 | name2 name3 | lname |` for another dataset (not in this one) — Евгений М, Aug 15 '17 at 10:50
@Sotos, based on names count. If names number would be more than four. — Евгений М, Aug 15 '17 at 10:55
What is the difference between `A |B C| D` and `A | B | C D` ? They both have 4 elements — Sotos, Aug 15 '17 at 10:56
@ЕвгенийМ please add more example names, and expected output. — zx8754, Aug 15 '17 at 10:57
Polad Bjul' Bjul' Oglý Mamedov splits into `Polad |Bjul Bjul| Ogly Mamedov`. But it's quite rare situation, so it can be done manually. Great thanks for help! — Евгений М, Aug 15 '17 at 11:05
@Sotos 'A |B C| D E' - if full names contains more than 4 elements, 'A | B | C D' - if full names contains 4 elements. — Евгений М, Aug 15 '17 at 11:09

zx8754 · Accepted Answer · 2017-08-15T10:46:39.953

2

Use extra argument:

# dummy data
df1 <- data.frame(x = c(
  "some name1",
  "justOneName",
  "some three name",
  "Abdullaeva Mehseti Nuraddin Kyzy"))

library(tidyr)
library(dplyr)

df1 %>% 
  separate(x, c("a1", "a2", "a3"), extra = "merge")
#            a1      a2            a3
# 1        some   name1          <NA>
# 2 justOneName    <NA>          <NA>
# 3        some   three          name
# 4  Abdullaeva Mehseti Nuraddin Kyzy
# Warning message:
#   Too few values at 2 locations: 1, 2

From manual:

extra

If sep is a character vector, this controls what happens when there are too many pieces. There are three valid options:
- "warn" (the default): emit a warning and drop extra values.
- "drop": drop any extra values without a warning.
- "merge": only splits at most length(into) times

edited Aug 15 '17 at 10:46

answered Aug 15 '17 at 10:40

zx8754

52,746
12
114
209

What's that `extra = merge` business? – Sotos Aug 15 '17 at 10:42
1

@Sotos black magic :D – zx8754 Aug 15 '17 at 10:43
Hihi...that dates back to the dark ages :) Nice argument though. Merges all the remaining cols. I wonder if this can be utilized to address not-known number of cols (kind of like cSplit) – Sotos Aug 15 '17 at 10:46

score 2 · Answer 2 · answered Aug 15 '17 at 11:04

Since for this dataset you said that you only have name1, name2, last name, then you can also use str_split_fixed from stringr, i.e.

setNames(data.frame(stringr::str_split_fixed(df1$x, ' ', 3)), paste0('a', 1:3))

Which gives,

        a1      a2            a3
1        some   name1              
2 justOneName                      
3        some   three          name
4  Abdullaeva Mehseti Nuraddin Kyzy

Note that you can fill the empty slots with NA as per usual

Separate column into three columns with grouping

2 Answers2