processing-json-like-data.R

processing-json-like-data.R

library(tidyverse)

some_data <- tibble(
  messy_col = c('{"id":"1234567","bio":"John Smith, first-year undergraduate in Physics","username":"JSAstronaut"}',
                '{"id":"00303","bio":"Jo Smith, first-year undergraduate in Physics","username":"JSAstronaut"}')
)

some_data$messy_col %>% fromJSON()

# Target each column at once:

some_data %>% 
  separate(messy_col,
           into = c("id", "bio", "username"),
           sep = "\",") %>% 
  mutate(id = str_remove(id, ".*[:]"),
         bio = str_remove(bio, ".*[:]"),
         username = str_remove(username, ".*[:]")) %>% 
  mutate(id = str_remove_all(id, "\"|[}]"),
         bio = str_remove_all(bio, "\"|[}]"),
         username = str_remove_all(username, "\"|[}]"))

# Using across()

some_data %>% 
  separate(messy_col,
           into = c("id", "bio", "username"),
           sep = "\",") %>% 
  mutate(across(c(id, bio, username),
                ~ str_remove(.x, ".*[:]")),
         across(c(id, bio, username),
                ~ str_remove_all(.x, "\"|[}]")))