R parse dataframe column having JSON array and convert to one-hot encoding

user_1194243 0
Viewed 47

I have a dataframe with a column having JSON array in the string form. My goal is to parse the column and convert into one-hot encoding but I'm facing an error while parsing the JSON.

library(jsonlite)
> df <- data_frame(Amenities=c("[\"Parking\", \"Lawn\", \"Garage\", \"Frontyard\"]", "[\"Parking\", \"Lawn\", \"Garage\", \"Backyard\"]", "[\"Parking\", \"Lawn\", \"Garage\"]"))
> df
# A tibble: 3 x 1
  Amenities                                           
  <chr>                                               
1 "[\"Parking\", \"Lawn\", \"Garage\", \"Frontyard\"]"
2 "[\"Parking\", \"Lawn\", \"Garage\", \"Backyard\"]" 
3 "[\"Parking\", \"Lawn\", \"Garage\"]"               
> df <- df %>% mutate(Amenities=fromJSON(Amenities))
Error: parse error: trailing garbage
          awn", "Garage", "Frontyard"] ["Parking", "Lawn", "Garage", "
                     (right here) ------^
>

Expected Output:

Parking  Lawn  Garage  Frontyard  Backyard
      1     1       1          1         0
      1     1       1          0         1
      1     1       1          0         0

Solution: Preserving the existing dataframe as well.

library(qdapTools)
df <- cbind(df, +(mtabulate(str_extract_all(df$amenities, "\\w+( +\\w+)*"))))
r json
2 Answers

We can do this in a single line with mtabulate

library(qdapTools)
library(stringr)
mtabulate(str_extract_all(df$Amenities, "\\w+"))

-output

#  Backyard Frontyard Garage Lawn Parking
#1        0         1      1    1       1
#2        1         0      1    1       1
#3        0         0      1    1       1
user_37322710

You can treat the json as strings, clean them and expand the dataset.

library(dplyr)

df %>%
  mutate(Amenities = gsub('\\[|\\]|"', '', Amenities)) %>%
  splitstackshape::cSplit_e("Amenities", sep = ',\\s*', 
                            type = 'character', fill = 0, fixed = FALSE) %>%
  rename_with(~sub('Amenities_', '', .))

#                         Amenities Backyard Frontyard Garage Lawn Parking
#1 Parking, Lawn, Garage, Frontyard        0         1      1    1       1
#2  Parking, Lawn, Garage, Backyard        1         0      1    1       1
#3            Parking, Lawn, Garage        0         0      1    1       1
user_39629140
Related