How to parsing Json column in dataframe using Rstudio

Question

I am trying to parsing json format in my dataframe's column. The column name is properties. Here's the example:

{"player_version":"vjs-core-6.9.0-hls-5.8.3-ads-5.1.5-ima-2f3a06d-dash-2.9.3","seconds":0.1,"device_model":"Other","levels":4,"referrer":"https://www.bola.net/","os_version":"7","message":"Unable to request ads from server due to network error.", "type":"Video", "autoplay_data":true,"target_bitrate":600,"os_name":"Windows","video":true,"ad_tag":"https://pubads.g.doubleclick.net/gampad/ads?sz=640x480&iu=/95250053/VIDIO_DESKTOP/PREROLL_KLY&impl=s&gdfp_req=1&env=vp&output=vast&unviewed_position_start=1&url=[referrer_url]&description_url=[description_url]&correlator=[timestamp]&cust_params=vidio_embed%3Dtrue%26vidio_user%3Dkapanlagi%26vidio_embed_site%3Dwww.bola.net%26vidio_id%3D1506205%26vidio_categories%3Dentertainment%26vidio_tag%3Dultah-kim-kardashian%2Cthe-kardashians-liburan-di-bali%2Ckapanlagi-hollywoods-buzz", "platform":"web-desktop", "video_id":1506205,"cf":"ccfbfd463b8b703bee7fe656073f6299", "current_time":5.319523,"level":2,"mse_vp9":true,"visitor_id":"b8f98cdb-d7d6-4f63-b3ac-a73c854a0858", "ad_type":"banner","webm_vp8":true,"device_vendor":{},"mse_h264":true,"h264":true,"browser_name":"Firefox","vjs_html5":true,"mse":true,"autoplay":true,"setup_time":0.1,"play_uuid":"716f7fa9-d049-471b-b7de-79df8a2cdccd", "autoplay_supported":true,"vjs_mpegurl":false,"app_name":"vidio","video_duration":84,"browser_version":"63.0","player_name":"videojs","batch_uuid":"7d101615-c1a9-449f-aa96-bb0aa6330d38", "embed":"true","uuid":"f41a01c3-f8f3-4295-b33c-9873e490c59c", "login":false,"batch_full":false,"percentage":10,"ad_uuid":"9eede46b-345e-42e1-89a5-6460df8d8aeb","supported":true,"flash_version":"11,4,402","has_ad":true}

I already used rjson/jsonlite library, and it's not work at all. I have error message like this:
Error in FUN(X[[i]], ...) : STRING_ELT() can only be applied to a 'character vector', not a 'integer'

my code:

library('rjson')
new <- do.call(rbind.data.frame, lapply(all_files$properties, rjson::fromJSON))

Dave2e · Accepted Answer · 2018-12-08 14:50:30Z

1

I prefer the jsonlite package for this type of problem.

library(jsonlite)

#convert the JSON into a list of names/values
values<-fromJSON('{"player_version":"vjs-core-6.9.0-hls-5.8.3-ads-5.1.5-ima-2f3a06d-dash-2.9.3","seconds":0.1,"device_model":"Other","levels":4,"referrer":"https://www.bola.net/","os_version":"7","message":"Unable to request ads from server due to network error.","type":"Video","autoplay_data":true,"target_bitrate":600,"os_name":"Windows","video":true,"ad_tag":"","platform":"web-desktop","video_id":1506205,"cf":"ccfbfd463b8b703bee7fe656073f6299","current_time":5.319523,"level":2,"mse_vp9":true,"visitor_id":"b8f98cdb-d7d6-4f63-b3ac-a73c854a0858","ad_type":"banner","webm_vp8":true,"device_vendor":{},"mse_h264":true,"h264":true,"browser_name":"Firefox","vjs_html5":true,"mse":true,"autoplay":true,"setup_time":0.1,"play_uuid":"716f7fa9-d049-471b-b7de-79df8a2cdccd","autoplay_supported":true,"vjs_mpegurl":false,"app_name":"vidio","video_duration":84,"browser_version":"63.0","player_name":"videojs","batch_uuid":"7d101615-c1a9-449f-aa96-bb0aa6330d38","embed":"true","uuid":"f41a01c3-f8f3-4295-b33c-9873e490c59c","login":false,"batch_full":false,"percentage":10,"ad_uuid":"9eede46b-345e-42e1-89a5-6460df8d8aeb","supported":true,"flash_version":"11,4,402","has_ad":true}')

#convert the list into a data frame where each row is a parameter.
df<-as.data.frame(unlist(values))

answered Dec 8, 2018 at 14:50

Dave2e

24.3k18 gold badges46 silver badges57 bronze badges

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

1 Comment

Tri Imam Wicaksono Over a year ago

appreciate for your answer,but I still get error message when I tried to convert the json string on all_files$properties to dataframe.

Collectives™ on Stack Overflow

How to parsing Json column in dataframe using Rstudio

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related