I have a dataframe with a column such as

and I'm trying to get it to look like:

Any advice on this? Have used various methods using stringr but always lose track of the ID column or end up with messy and slow loops. Thanks

CodePudding user response：

A tidyverse approach to achieve your desired result may look like so:

library(dplyr)
library(tidyr)

data.frame(
  ID = c(1L, 2L),
  dictionary_column = c("{1:5, 10:15, 3:9}", "{3:4, 5:3}")
) %>%
  mutate(dictionary_column = gsub("(\\{|\\})", "", dictionary_column)) %>%
  separate_rows(dictionary_column, sep = ", ") %>%
  separate(dictionary_column, into = c("col_a", "col_b"))
#> # A tibble: 5 × 3
#>      ID col_a col_b
#>   <int> <chr> <chr>
#> 1     1 1     5    
#> 2     1 10    15   
#> 3     1 3     9    
#> 4     2 3     4    
#> 5     2 5     3

CodePudding user response：

Not very elegant but it works:

library(tidyr)
library(dplyr)

dat %>% 
  mutate(dictionary_column = gsub("\\{|\\}|\\,", "", dictionary_column)) %>% 
  separate(dictionary_column, into=c("a", "b", "c"), sep=" ") %>% 
  pivot_longer(-ID, values_drop_na=T) %>% 
  select(-name) %>% 
  separate(value, into = c("col_a", "col_b"))

# A tibble: 5 × 3
     ID col_a col_b
  <int> <chr> <chr>
1     1 1     5    
2     1 10    15   
3     1 3     9    
4     2 3     4    
5     2 5     3    

CodePudding user response：

An option with str_extract_all to extract the digits before and after the : into list columns and then unnest the list

library(stringr)
library(dplyr)
library(tidyr)
df1 %>%
    mutate(col_a = str_extract_all(dictionary_column, "\\d (?=:)"),
       col_b = str_extract_all(dictionary_column, "(?<=:)\\d "), 
       .keep = "unused") %>% 
    unnest(c(col_a, col_b))

-output

# A tibble: 5 × 3
     ID col_a col_b
  <int> <chr> <chr>
1     1 1     5    
2     1 10    15   
3     1 3     9    
4     2 3     4    
5     2 5     3   

data

df1 <- structure(list(ID = 1:2, dictionary_column = c("{1:5, 10:15, 3:9}", 
"{3:4, 5:3}")), class = "data.frame", row.names = c(NA, -2L))