How to use separate() properly?

Question

I have some difficulties to extract an ID in the form:

27da12ce-85fe-3f28-92f9-e5235a5cf6ac

from a data frame:

a<-c("NAME_27da12ce-85fe-3f28-92f9-e5235a5cf6ac_THOMAS_MYR",
        "NAME_94773a8c-b71d-3be6-b57e-db9d8740bb98_THIMO",
         "NAME_1ed571b4-1aef-3fe2-8f85-b757da2436ee_ALEX",
         "NAME_9fbeda37-0e4f-37aa-86ef-11f907812397_JOHN_TYA",
         "NAME_83ef784f-3128-35a1-8ff9-daab1c5f944b_BISHOP",
         "NAME_39de28ca-5eca-3e6c-b5ea-5b82784cc6f4_DUE_TO",
         "NAME_0a52a024-9305-3bf1-a0a6-84b009cc5af4_WIS_MICHAL",
         "NAME_2520ebbb-7900-32c9-9f2d-178cf04f7efc_Sarah_Lu_Van_Gar/Thomas")

Basically its the thing between the first and the second underscore.

Usually I approach that by:

library(tidyr)
df$a<-as.character(df$a)
df<-df[grep("_", df$a), ]
df<- separate(df, a, c("ID","Name") , sep = "_")
df$a<-as.numeric(df$ID)

However this time there a to many underscores...and my approach fails. Is there a way to extract that ID?

Answer 1

I think you should use extract instead of separate. You need to specify the patterns which you want to capture. I'm assuming here that ID is always starts with a number so I'm capturing everything after the first number until the next _ and then everything after it

df <- data.frame(a)
df <- df[grep("_", df$a),, drop = FALSE]
extract(df, a, c("ID", "NAME"), "[A-Za-z].*?(\\d.*?)_(.*)")
#                                     ID                    NAME
# 1 27da12ce-85fe-3f28-92f9-e5235a5cf6ac              THOMAS_MYR
# 2 94773a8c-b71d-3be6-b57e-db9d8740bb98                   THIMO
# 3 1ed571b4-1aef-3fe2-8f85-b757da2436ee                    ALEX
# 4 9fbeda37-0e4f-37aa-86ef-11f907812397                JOHN_TYA
# 5 83ef784f-3128-35a1-8ff9-daab1c5f944b                  BISHOP
# 6 39de28ca-5eca-3e6c-b5ea-5b82784cc6f4                  DUE_TO
# 7 0a52a024-9305-3bf1-a0a6-84b009cc5af4              WIS_MICHAL
# 8 2520ebbb-7900-32c9-9f2d-178cf04f7efc Sarah_Lu_Van_Gar/Thomas

Answer 2

try this (which assumes that the ID is always the part after the first unerscore):

sapply(strsplit(a, "_"), function(x) x[[2]])

which gives you "the middle part" which is your ID:

[1] "27da12ce-85fe-3f28-92f9-e5235a5cf6ac" "94773a8c-b71d-3be6-b57e-db9d8740bb98"
[3] "1ed571b4-1aef-3fe2-8f85-b757da2436ee" "9fbeda37-0e4f-37aa-86ef-11f907812397"
[5] "83ef784f-3128-35a1-8ff9-daab1c5f944b" "39de28ca-5eca-3e6c-b5ea-5b82784cc6f4"
[7] "0a52a024-9305-3bf1-a0a6-84b009cc5af4" "2520ebbb-7900-32c9-9f2d-178cf04f7efc"

if you want to get the Name as well a simple solution would be (which assumes that the Name is always after the second underscore):

Names <- sapply(strsplit(a, "_"), function(x) Reduce(paste, x[-c(1,2)]))

which gives you this:

[1] "THOMAS MYR"              "THIMO"                   "ALEX"                    "JOHN TYA"               
[5] "BISHOP"                  "DUE TO"                  "WIS MICHAL"              "Sarah Lu Van Gar/Thomas"