extract data from a dictionary column to R dataframe

user_11178002 0
Viewed 192

I have a dataframe that looks as follows:

Rest of data frame columnToSeparate
blablarow1 {"info1":"row1string1","info2":"row1string2"}
blablarow2 {"info1":"row2string1","info2":"row2string2"}

What is the best way to extract this information as separate columns in my dataframe to get something like:

Rest of data frame info1 info2
blablarow1 row1string1 row1string2
blablarow2 row2string1 row2string2

Here is code to create the dataframe

df <- data.frame(RestOfDF  = c("blablarow1", "blablarow2"),
                 columnToSeparate = c('{"info1":"row1string1","info2":"row1string2"}',
                                      '{"info1":"row2string1","info2":"row2string2"}'))

Update: My real column has lots of infoN fiels, so I am looking to do something as automatic as possible as the names are actually different. Something like {"mydogsname":"pinky","mycatsfood":"icecream"...}

r json regex
4 Answers

I think this can also help:

library(dplyr)
library(stringr)

df %>%
  magrittr::extract(1) %>%
  bind_cols(str_extract_all(df$columnToSeparate, "(\\w+\\d+)(\\w+\\d+)", simplify = TRUE) %>%
              as_tibble() %>%
              setNames(c("info1", "info2")))

    RestOfDF       info1       info2
1 blablarow1 row1string1 row1string2
2 blablarow2 row2string1 row2string2
user_143145200

Clean the columnToSeparate column removing unwanted text (i.e remove ["{}]), split text into different rows by splitting on comma (,) and in different columns by splitting on colon (:). Use pivot_wider to get data in wide format.

library(dplyr)
library(tidyr)

df %>%
  mutate(columnToSeparate = gsub('["{}]', '', columnToSeparate)) %>%
  separate_rows(columnToSeparate, sep = ',') %>%
  separate(columnToSeparate, c('col1', 'col2'), sep = ':') %>%
  pivot_wider(names_from = col1, values_from = col2)

#  RestOfDF   info1       info2      
#  <chr>      <chr>       <chr>      
#1 blablarow1 row1string1 row1string2
#2 blablarow2 row2string1 row2string2
user_39629140

Here's a dplyr & stringr solution:

library(stringr)
library(dplyr)
df %>% 
  # get rid of metachars:
  mutate(columnToSeparate = gsub('[\\"{}]', '', columnToSeparate)) %>%
  # extract `info1` using lookbehind and lookahead:
  mutate(info1 = str_extract(columnToSeparate, "(?<=:)[^,:]+(?=,)")) %>%
  # extract `info2` using lookbehind and lookahead:
  mutate(info2 = str_extract(columnToSeparate, "(?<=:)[^,:]+(?=$)")) %>%
  # remove obsolete column:
  select(-2)
    RestOfDF       info1       info2
1 blablarow1 row1string1 row1string2
2 blablarow2 row2string1 row2string2
user_80399780

Apparently, columnToSeparate is in JSON format. So, we can

  1. use the fromJSON() function from the jsonlite package to parse the data,
  2. use rbindlist() from the data.table package to create a data.frame with separate columns,
  3. and join this intermediate result with the original df as requested.
setDT(df)[, rbindlist(Map(jsonlite::fromJSON, columnToSeparate), 
                      fill = TRUE, idcol = "columnToSeparate")][
                        df, on = "columnToSeparate"][
                          , columnToSeparate := NULL][]

         info1       info2       info4   RestOfDF
1: row1string1 row1string2        <NA> blablarow1
2: row2string1 row2string2        <NA> blablarow2
3:        <NA>        <NA>        <NA> blablarow3
4:        <NA>        <NA> row4string4 blablarow4

Note that a modified dataset has been used to cover additional edge cases as the OP has mentioned that "the names are actually different".

Data

df <- data.frame(RestOfDF  = paste0("blablarow", 1:4),
                 columnToSeparate = c('{"info1":"row1string1","info2":"row1string2"}',
                                      '{"info1":"row2string1","info2":"row2string2"}',
                                      '{}',
                                      '{"info4":"row4string4"}'))

Rows 1 to 2 are taken from OP's original dataset.
Row 3 has no data.
Row 4 has only one data item and uses a different name.

user_38170040
Related