I have a json file whose rows are in the format as follows:
{"checkin_info": {"11-3": 17, "8-5": 1, "15-0": 2, "15-3": 2, "15-5": 2, "14-4": 1, "14- 5": 3, "14-6": 6, "14-0": 2, "14-1": 2, "14-3": 2, "0-5": 1, "1-6": 1, "11-5": 3, "11-4": 11, "13-1": 1, "11-6": 6, "11-1": 18, "13-6": 5, "13-5": 4, "11-2": 9, "12-6": 5, "12-4": 8, "12-5": 5, "12-2": 12, "12-3": 19, "12-0": 20, "12-1": 14, "13-3": 1, "9-5": 2, "9-4": 1, "13-2": 6, "20-1": 1, "9-6": 4, "16-3": 1, "16-1": 1, "16-5": 1, "10-0": 3, "10-1": 4, "10-2": 4, "10-3": 4, "10-4": 1, "10-5": 2, "10-6": 2, "11-0": 3}, "type": "checkin", "business_id": "KO9CpaSPOoqm0iCWm5scmg"}
and so on....it has 8282 entries like this.
I want to convert it into csv file like this.
business_id "0-0" "1-0" "2-0" "3-0" ….. "23-0" "0-1" ……. "23-1" …….. "0-4" …… "23-4" …… "23-6"
1 KO9CpaSPOoqm0iCWm5scmg 2 1 0 1 NA 1 1 NA NA NA NA NA 6 NA 7
2 oRqBAYtcBYZHXA7G8FlPaA 1 2 2 NA NA 2 NA NA 1 NA 2 NA 2 NA 2
I tried this code:
urlc <- "C:\\Users\\Ayush\\Desktop\\yelp_training_set\\yelp_training_set_checkin.json"
conc = file(urlc, "r")
inputc <- readLines(conc, -1L)
usec <- lapply(X=inputc,fromJSON)
for (i in 1:8282)
{
tt<-usec[[i]]$checkin_info
bb<-toString(tt)
usec[[i]]$checkin_info<-bb
}
dfc <- data.frame(matrix(unlist(usec), nrow=length(usec), byrow=T))
write.csv(dfc,file="checkin_tr.csv")
to convert it into form like this:
X1
business_id
1, 1, 1, 1, 1, 1, 2, 1, 2, 1, 1, 2, 1, 1, 1, 2, 1
D0IB17N66FiyYDCzTlAI4A
1, 1, 2, 1, 1
HLQGo3EaYVvAv22bONGkIw
1, 1, 1, 1
J6OojF0R_1OuwNlrZI-ynQ 2, 1, 2, 1, 2, 1, 1, 1, 1, 4, 1, 1, 1, 1, 1, 1, 2, 1, 2
But I want entries in column "X1" above in separate columns, as shown in the first table.
How can I do this? Please help
Using RJSONIO you can do something like this :
library(RJSONIO)
tt <- fromJSON(tt)
data.frame(business_id =tt$business_id,
do.call(rbind,list(tt$checkin_info)))
business_id X11.3 X8.5 X15.0 X15.3 X15.5 X14.4 X14.5 X14.6 X14.0 X14.1 X14.3 X0.5 X1.6 X11.5 X11.4 X13.1 X11.6 X11.1 X13.6 X13.5 X11.2 X12.6 X12.4
1 KO9CpaSPOoqm0iCWm5scmg 17 1 2 2 2 1 3 6 2 2 2 1 1 3 11 1 6 18 5 4 9 5 8
X12.5 X12.2 X12.3 X12.0 X12.1 X13.3 X9.5 X9.4 X13.2 X20.1 X9.6 X16.3 X16.1 X16.5 X10.0 X10.1 X10.2 X10.3 X10.4 X10.5 X10.6 X11.0
1 5 12 19 20 14 1 2 1 6 1 4 1 1 1 3 4 4 4 1 2 2 3
EDIT
I use a new idea here. It is easier to create a long format data.frame then convert it to a wide format using reshape2 for example.
library(RJSONIO)
## I create 2 shorter lines with different id
tt <- '{"checkin_info": {"11-3": 17, "8-5": 1, "15-0": 2}, "type": "checkin", "business_id": "KO9CpaSPOoqm0iCWm5scmg"}'
tt1 <- '{"checkin_info": {"12-0": 17, "7-5": 1, "15-0": 5}, "type": "checkin", "business_id": "iddd2"}'
## use inputc <- readLines(conc, -1L) in your case
inputc <- list(tt,tt1)
usec <- lapply(X=inputc,function(x){
tt <- fromJSON(x)
data.frame(business_id =tt$business_id,
names = names(tt$checkin_info),
values =unlist(tt$checkin_info))
})
## create a long data frame
dat <- do.call(rbind,usec)
## put in the wide format
library(reshape2)
dcast(business_id~names,data=dat)
business_id 11-3 15-0 8-5 12-0 7-5
1 KO9CpaSPOoqm0iCWm5scmg 17 2 1 NA NA
2 iddd2 NA 5 NA 17 1