CODEWITHSUNDEEP
rjsonlistdataframe
Yogesh Kumar
Yogesh Kumar

Reputation: 659

How to convert JSON (with uneven lists with few values missing) into dataframe in R (example given below)?

This is my JSON file json_data as given below. It has lists with different length of columns(16 & 15). I want to create 1 data frame given lists data in JSON . How do I solve this ?

data2

[1] "{\"2020-01-01\": 
[{\"contactsPerPageview\":0.015706806282722512,\"returningVisits\":30,\"rawViews\":191
,\"standardViews\":191,\"sessionToContactRate\":0.020689655172413793
,\"pageviewsPerSession\":1.3172413793103448,\"visits\":145,\"visitors\":115
,\"submissionsPerPageview\":0.015706806282722512,\"pageviewsMinusExits\":191
,\"submissions\":3,\"leads\":3,\"leadsPerView\":0.015706806282722512,\"contacts\":3
,\"newVisitorSessionRate\":0.7931034482758621}]
,\"2020-01-02\":[{\"contactsPerPageview\":0.007963594994311717
,\"returningVisits\":71,\"rawViews\":879,\"subscribers\":4,\"standardViews\":879
,\"sessionToContactRate\":0.012704174228675136,\"pageviewsPerSession\":1.5952813067150635
,\"visits\":551,\"visitors\":480,\"submissionsPerPageview\":0.007963594994311717
,\"pageviewsMinusExits\":879,\"submissions\":7,\"leads\":3
,\"leadsPerView\":0.0034129692832764505,\"contacts\":7
,\"newVisitorSessionRate\":0.8711433756805808}]}"

This is what I tried
df <- lapply(data2, function(play) 
{ data.frame(matrix(unlist(play), ncol=16, byrow=T))
})
df <- do.call(rbind, df)
colnames(df) <- names(data2[[1]][[1]])

Upvotes: 0

Views: 216

Answers (1)

JacobJacox
JacobJacox

Reputation: 947

You can try rbindlist from data.table with fill parameter set to TRUE.

js <-  "{\"2020-01-01\": 
[{\"contactsPerPageview\":0.015706806282722512,\"returningVisits\":30,\"rawViews\":191
,\"standardViews\":191,\"sessionToContactRate\":0.020689655172413793
,\"pageviewsPerSession\":1.3172413793103448,\"visits\":145,\"visitors\":115
,\"submissionsPerPageview\":0.015706806282722512,\"pageviewsMinusExits\":191
,\"submissions\":3,\"leads\":3,\"leadsPerView\":0.015706806282722512,\"contacts\":3
,\"newVisitorSessionRate\":0.7931034482758621}]
,\"2020-01-02\":[{\"contactsPerPageview\":0.007963594994311717
,\"returningVisits\":71,\"rawViews\":879,\"subscribers\":4,\"standardViews\":879
,\"sessionToContactRate\":0.012704174228675136,\"pageviewsPerSession\":1.5952813067150635
,\"visits\":551,\"visitors\":480,\"submissionsPerPageview\":0.007963594994311717
,\"pageviewsMinusExits\":879,\"submissions\":7,\"leads\":3
,\"leadsPerView\":0.0034129692832764505,\"contacts\":7
,\"newVisitorSessionRate\":0.8711433756805808}]}"

a<- jsonlite::fromJSON(js)
rbindlist(a,fill = T)

Gives:

   contactsPerPageview returningVisits rawViews standardViews sessionToContactRate pageviewsPerSession visits visitors submissionsPerPageview pageviewsMinusExits submissions leads leadsPerView
1:         0.015706806              30      191           191           0.02068966            1.317241    145      115            0.015706806                 191           3     3  0.015706806
2:         0.007963595              71      879           879           0.01270417            1.595281    551      480            0.007963595                 879           7     3  0.003412969
   contacts newVisitorSessionRate subscribers
1:        3             0.7931034          NA
2:        7             0.8711434           4

Upvotes: 2

Related Questions