CODEWITHSUNDEEP
rjsontidyverse
Ruslan
Ruslan

Reputation: 423

How to convert a JSON column in a tibble to new columns

I have a csv file where one of the columns is a JSON. Here's an example of the JSON.

{"sg_template_id":"d-65910500a27a4992bd8ac7","sg_template_name":"trial-end"}
ID Email arrgs
1 [email protected] { "sg_template_id" : "d-65910500a27a4992bd8ac7" , "sg_template_name" : "trial-end" }
2 [email protected] { "sg_template_id" : "d-7878787987877a27a4992b" , "sg_template_name" : "welcome" } 
structure(
  list(
    email = c("[email protected]\t", "[email protected]\t"),
    id = c(1, 2),
    arrgs = c(
      "{ \"sg_template_id\" : \"d-65910500a27a4992bd8ac7\" , \"sg_template_name\" : \"trial-end\" }",
      "{ \"sg_template_id\" : \"d-7878787987877a27a4992b\" , \"sg_template_name\" : \"welcome\" }"
    )
  ),
  row.names = c(NA,-2L),
  class = c("tbl_df", "tbl", "data.frame")
)

I would like to make a tibble with all the columns from the CSV file. And additionally, I want all the keys from JSON to be converted to new columns with values from this JSON.

Is there a way to do it fast and easily?

Here's how the end result should look

ID Email sg_template_id sg_template_name
1 [email protected] d-65910500a27a4992bd8ac7 trial-end
2 [email protected] d-7878787987877a27a4992b welcome

Upvotes: 0

Views: 470

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388982

You can use fromJSON function from jsonlite package to read the json and combine the values in separate column using map_df.

library(dplyr)
library(purrr)
library(jsonlite)

df %>%
  select(-arrgs) %>%
  bind_cols(map_df(df$arrgs, fromJSON))

# A tibble: 2 x 4
#  email                    id sg_template_id           sg_template_name
#  <chr>                 <dbl> <chr>                    <chr>           
#1 "[email protected]\t"     1 d-65910500a27a4992bd8ac7 trial-end       
#2 "[email protected]\t"       2 d-7878787987877a27a4992b welcome

Upvotes: 1

Related Questions