R Extract specific text from column into multiple columns

user_7824826 0
Viewed 109

I have a dataframe exported from the web with this format

id vals
1  {7,12,58,1}
2  {1,2,5,7}  
3  {15,12}

I would like to extract ONLY the numbers (ignore curlys and commas) into multiple columns like this

id val_1 val_2 val_3 val_4 val_5
1  7     12    58    1
2  1     2     5     7  
3  15    12

Even though the Max of values we got was 4 I want to always go up to value val_5.

Thanks!

tidyverse r
3 Answers

We could use str_extract_all for this:

library(dplyr)
library(stringr)

df %>% 
  mutate(vals = str_extract_all(vals, '\\d+', ''))

or as @akrun suggest in the comments

df %>% 
  mutate(vals = str_extract_all(vals, '\\d+', '')) %>% 
  do.call(data.frame, .)

  id vals.1 vals.2 vals.3 vals.4
1  1      7     12     58      1
2  2      1      2      5      7
3  3     15     12   <NA>   <NA>

data:

df <- structure(list(id = 1:3, vals = c("{7,12,58,1}", "{1,2,5,7}", 
"{15,12}")), class = "data.frame", row.names = c(NA, -3L))
user_133216470

Another possible tidyverse option, where we remove the curly brackets, then separate the rows on the ,, then pivot to wide form. Then, we can create the additional column (using add_column from tibble) based on the max value in the column names (which is 4 in this case), and then can create val_5.

library(tidyverse)

df %>%
  mutate(vals = str_replace_all(vals, "\\{|\\}", "")) %>%
  separate_rows(vals, sep=",") %>%
  group_by(id) %>%
  mutate(ind = row_number()) %>%
  pivot_wider(names_from = ind, values_from = vals, names_prefix = "val_") %>%
  add_column(!!(paste0("val_", parse_number(names(.)[ncol(.)])+1)) := NA)

Output

  id val_1 val_2 val_3 val_4 val_5
1  1     7    12    58     1    NA
2  2     1     2     5     7    NA
3  3    15    12  <NA>  <NA>    NA

Data

df <- read.table(text = "id vals
1  {7,12,58,1}
2  {1,2,5,7}
3  {15,12} ", header = T)
user_152931910

Using data.table

library(data.table)
library(stringi)
result <- setDT(df)[, stri_match_all_regex(vals, '\\d+')[[1]], by=.(id)]
result[, item:=paste('val', 1:.N, sep='_'), by=.(id)]   # defines column names
dcast(result, id~item, value.var = 'V1')                # convert from long to wide
##    id val_1 val_2 val_3 val_4
## 1:  1     7    12    58     1
## 2:  2     1     2     5     7
## 3:  3    15    12  <NA>  <NA>
user_29850070
Related