split a string at multiple separators in r across multiple rows

Question

I have the following tibble in R:

df <- tibble(desc=c("test1", "test2", "test3", "test4","test1"), code=c("X00.2", "Y10", "X20.234", "Z10", "Q23.2"))

I want to create a new dataframe as:

df <- tibble(desc=c("test1", "test1", "test2", "test3", "test3", "test3", "test3", "test4", "test1", "test1"), code=c("X00", "X00.2", "Y10", "X20", "X20.2", "X20.23", "X20.234", "Z10", "Q23", "Q23.2"))

How would I do this? I think I can do it with separate_rows in dplyr by manipulating the separator but not exactly sure.

Thank you in advance.

Of course I mispecified exactly what I wanted. editing the data frames. — yindalon
– yindalon, Commented Aug 29, 2020 at 15:46

Ronak Shah · Accepted Answer · 2020-08-30 01:15:47Z

2

Here is one way using tidyverse functions.

library(tidyverse)

df %>%
  #n is the number of new rows to add
  mutate(n = nchar(sub('.*\\.', '', code)) + 1, 
         #l is location of "."
         l = str_locate(code, '\\.')[, 1], 
         #replace NA with 1
         n = replace(n, is.na(l), 1),
         l = ifelse(is.na(l), nchar(code), l), 
         r = row_number()) %>%
  #Repeat each row n times
  uncount(n) %>%
  #For each desc
  group_by(r) %>%
  #Create code value incrementing one character at a time
  mutate(code = map_chr(row_number(), ~substr(first(code), 1, l + .x - 1)), 
         #Remove "." which is present at the end of string
         code = sub('\\.$', '', code)) %>%
  ungroup %>%
  select(-l, -r)

This returns

# A tibble: 10 x 2
#   desc  code   
#   <chr> <chr>  
# 1 test1 X00    
# 2 test1 X00.2  
# 3 test2 Y10    
# 4 test3 X20    
# 5 test3 X20.2  
# 6 test3 X20.23 
# 7 test3 X20.234
# 8 test4 Z10    
# 9 test1 Q23    
#10 test1 Q23.2

edited Aug 30, 2020 at 1:15

answered Aug 29, 2020 at 15:09

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

yindalon Over a year ago

Thanks Ronak! i would have never come up with your solution. I tried it on my problem and I didn't specify what I wanted enough. I updated the question if you have time to give a crack at it. I gave it a shot but was getting tripped up with the sub regex.

Ronak Shah Over a year ago

Check updated answer which works for the new data you provided.

yindalon Over a year ago

I'm back. The iteration to build the string gets confused when the desc has repetitions and is not unique. i updated the example. I tried rowwise but that didn't work.

Ronak Shah Over a year ago

@yindalon You can add a row number column. See updated answer.

yindalon Over a year ago

Thank you. I was messing around with the .id in uncount. The row_number is exactly what i needed. Thanks so much.

Collectives™ on Stack Overflow

split a string at multiple separators in r across multiple rows

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related