Splitting the data in brackets in R

t.hariprabhath 0
Viewed 34

Hi I have a dataset that looks like this

PTNUM AGE1_2 AGE2_3 AGE3_2
12345 (23,35) NA NA
12346 NA (23,28,34,44) (45,50)
12347 (17,22) NA (38,45)

I would like to have the output looking like this

PTNUM AGE1_1 AGE1_2 AGE2_2 AGE2_3 AGE3_3 AGE3_2
12345 23 35 NA NA NA NA
12346 NA NA 23 28 NA NA
12346 NA NA 34 44 45 50
12347 17 22 NA NA 38 45

I tried this code in R just to try splitting AGE1_2 to AGE1_1 and AGE1_2 but this resulted in all the rows of AGE1_1 and AGE1_2 being NA's.

ZX_1_2 <- extract(ZX, AGE1_2, into = c('AGE1_1', 'AGE1_2'), regex = "(.?) \((.?)\)")

Could someone help me get the expected result?

r
1 Answers

We could use

library(purrr)
library(tidyr)
library(stringr)
map_dfc(names(ZX)[-1], ~ df1 %>%
    select(all_of(.x)) %>% 
   extract(1, into = str_c(names(.), "_", 1:2), 
     "\\((\\d+),(\\d+)\\)", convert = TRUE)) %>%
   bind_cols(ZX['PTNUM'], .)

-output

   PTNUM AGE1_2_1 AGE1_2_2 AGE2_3_1 AGE2_3_2 AGE3_2_1 AGE3_2_2
1 12345       23       35       NA       NA       NA       NA
2 12346       NA       NA       34       40       45       50
3 12347       17       22       NA       NA       38       45

Or another option is

ZX %>% 
  mutate(across(starts_with('AGE'), 
  ~ read.csv(text = str_remove_all(.x, "\\(|\\)"), 
      header = FALSE, fill = TRUE))) %>% 
  unpack(where(is.data.frame), names_sep = "_")

-output

# A tibble: 3 × 7
  PTNUM AGE1_2_V1 AGE1_2_V2 AGE2_3_V1 AGE2_3_V2 AGE3_2_V1 AGE3_2_V2
  <int>     <int>     <int>     <int>     <int>     <int>     <int>
1 12345        23        35        NA        NA        NA        NA
2 12346        NA        NA        34        40        45        50
3 12347        17        22        NA        NA        38        45

For the updated data

library(data.table)
ZX2 %>% 
 pivot_longer(cols = starts_with("AGE")) %>%
 mutate(value = str_remove_all(value, "\\(|\\)")) %>% 
 separate_rows(value, sep = ",") %>% 
 group_by(PTNUM, name) %>% 
 mutate(rn = as.integer(gl(n(), 2, n()))) %>%
 ungroup %>% 
 mutate(rn2 = rowid(PTNUM, name, rn)) %>%
 unite(name, name, rn2) %>%
 pivot_wider(names_from = name, values_from = value) %>% 
 select(-rn) %>% 
 group_by(PTNUM) %>%
  mutate(across(everything(), ~ .x[order(!is.na(.x))])) %>% 
 ungroup

-output

# A tibble: 4 × 7
  PTNUM AGE1_2_1 AGE1_2_2 AGE2_3_1 AGE3_2_1 AGE2_3_2 AGE3_2_2
  <int> <chr>    <chr>    <chr>    <chr>    <chr>    <chr>   
1 12345 23       35       <NA>     <NA>     <NA>     <NA>    
2 12346 <NA>     <NA>     23       <NA>     28       <NA>    
3 12346 <NA>     <NA>     34       45       44       50      
4 12347 17       22       <NA>     38       <NA>     45

data

ZX <- structure(list(PTNUM = 12345:12347, AGE1_2 = c("(23,35)", NA, 
"(17,22)"), AGE2_3 = c(NA, "(34,40)", NA), AGE3_2 = c(NA, "(45,50)", 
"(38,45)")), class = "data.frame", row.names = c(NA, -3L))
ZX2 <- structure(list(PTNUM = 12345:12347, AGE1_2 = c("(23,35)", NA, 
"(17,22)"), AGE2_3 = c(NA, "(23,28,34,44)", NA), AGE3_2 = c(NA, 
"(45,50)", "(38,45)")), class = "data.frame", row.names = c(NA, 
-3L))
akrun0
Related