Separate rows with conditions

user_5554585 0
Viewed 306

I have this dataframe separate_on_condition with two columns:

separate_on_condition <- data.frame(first = 'a3,b1,c2', second = '1,2,3,4,5,6')`

#         first      second
#    1 a3,b1,c2 1,2,3,4,5,6

How can I turn it to:

# A tibble: 6 x 2
  first second
  <chr> <chr> 
1  a     1  
2  a     2
3  a     3  
4  b     4    
5  c     5  
6  c     6

where:

  • a3 will be separated into 3 rows
  • b1 into 1 row
  • c2 into 2 rows

Is there a better way on achieving this instead of using rep() on first column and separate_rows() on the second column?

Any help would be much appreciated!

tidyr dplyr dataframe r split
3 Answers

You can the following base R option

with(
  separate_on_condition,
  data.frame(
    first = unlist(sapply(
      unlist(strsplit(first, ",")),
      function(x) rep(gsub("\\d", "", x), as.numeric(gsub("\\D", "", x)))
    ), use.names = FALSE),
    second = eval(str2lang(sprintf("c(%s)", second)))
  )
)

which gives

  first second
1     a      1
2     a      2
3     a      3
4     b      4
5     c      5
6     c      6
user_121587570
  • Create a row number column to account for multiple rows.
  • Split second column on , in separate rows.
  • For each row extract the data to be repeated along with number of times it needs to be repeated.
library(dplyr)
library(tidyr)
library(stringr)

separate_on_condition %>%
  mutate(row = row_number()) %>%
  separate_rows(second, sep = ',') %>%
  group_by(row) %>%
  mutate(first = rep(str_extract_all(first(first), '[a-zA-Z]+')[[1]],
                     str_extract_all(first(first), '\\d+')[[1]])) %>%
  ungroup %>%
  select(-row)
  
# first second
#  <chr> <chr> 
#1 a     1     
#2 a     2     
#3 a     3     
#4 b     4     
#5 c     5     
#6 c     6
user_39629140

Here is an alternative approach:

  1. add NA to first to get same length
  2. use separate_rows to bring each element to a row
  3. use extract by regex digit to split first into first and helper
  4. group and slice by values in helper
  5. do some tweaking
library(tidyr)
library(dplyr)
separate_on_condition %>%  
    mutate(first = str_c(first, ",NA,NA,NA")) %>% 
    separate_rows(first, second, sep = "[^[:alnum:].]+", convert = TRUE) %>% 
    extract(first, into = c("first", "helper"), "(.{1})(.{1})", remove=FALSE) %>% 
    group_by(second) %>% 
    slice(rep(1:n(), each = helper)) %>% 
    ungroup() %>% 
    drop_na() %>% 
    mutate(second = row_number()) %>% 
    select(first, second)

  first second
  <chr>  <int>
1 a          1
2 a          2
3 a          3
4 b          4
5 c          5
6 c          6
user_133216470
Related