Convert JSON embedded in CSV file fields to data frame

Question

I'm pretty new to R. I have done some search but still have trouble with this issue.

This is an example of my data (CSV) out of 100,000 records

{id_outlet_delivery:22015,name:Branch A}6 ,

{id_outlet_delivery:22016,name:Branch B}7 ,

{id_outlet_delivery:22017,name:Branch C}8

What I want is to convert this to data frame so that I can perform some analysis. (I don't want the number 6,7,8 as in the example to be in the data frame).

I guess this could be done using packages like RJSONIO or jsonlite. — akrun
– akrun, Commented Oct 19, 2014 at 4:24
Any of the three most popular JSON drivers (packages) in R can convert a JSON string to R objects. That's not a proper CSV file since the JSON strings aren't quoted in it (and, I can't think of a CSV file library that will read that by default). Your best bet is to use readLines and either convert the whole thing to a proper JSON string/format or parse each line separately and combine everything into data frame rows. — hrbrmstr
– hrbrmstr, Commented Oct 19, 2014 at 4:29

akrun · Accepted Answer · 2014-10-19 04:40:35Z

1

Here is a method using regex

 library(stringr)
  v1 <- '{id_outlet_delivery:22015,name:Branch A}6 ,
    {id_outlet_delivery:22016,name:Branch B}7 ,
    {id_outlet_delivery:22017,name:Branch C}8'

  nm1 <- str_extract_all(v1, perl("[[:alpha:]_]+(?=:)"))[[1]][1:2]
  val <- str_extract_all(v1, perl("(?<=:)[[:alnum:] ]+(?=\\})?"))[[1]]

Or you could use stringi which would be faster

  library(stringi)
  nm1 <- stri_extract_all_regex(v1, "[[:alpha:]_]+(?=:)")[[1]][1:2]
  val <- stri_extract_all_regex(v1, "(?<=:)[[:alnum:] ]+(?=\\}|,)")[[1]]


  indx <- c(TRUE, FALSE)
  dat <- setNames(data.frame(as.numeric(val[indx]), val[!indx],
                                     stringsAsFactors=FALSE), nm1)

  dat
  #  id_outlet_delivery     name
  #1              22015 Branch A
  #2              22016 Branch B
  #3              22017 Branch C

edited Oct 19, 2014 at 4:40

answered Oct 19, 2014 at 4:34

akrun

891k38 gold badges590 silver badges700 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert JSON embedded in CSV file fields to data frame

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related