How to convert a json file without the same length of json objects into csv

Question

I have a json file and I want to convert it to csv format.

The problem I face is that every json object in the file has not the same length of the converted columns I have. For example the one object have 49 columnns and the next have 50.

I provide here an example of 2 data from which the first one has not the creator.slug but the next has it is and so there is the problem with data. The problem is that the process create all 50 columns but for the object which don't have the value creator.slug it takes the next price.

{
      "id": 301852363,
      "name": "Song of the Sea",
      "blurb": "One evening, two shows: SIRENS and The Girl From Bare Cove. Building a community. Giving voice to survivors of sexual violence.",
      "goal": 5000,
      "pledged": 671,
      "state": "live",
      "slug": "song-of-the-sea",
      "disable_communication": false,
      "country": "US",
      "currency": "USD",
      "currency_symbol": "$",
      "currency_trailing_code": true,
      "deadline": 1399293386,
      "state_changed_at": 1397133386,
      "created_at": 1396672480,
      "launched_at": 1397133386,
      "backers_count": 20,
      "photo": {
        "full": "https://s3.amazonaws.com/ksr/projects/939387/photo-full.jpg?1397874930",
        "ed": "https://s3.amazonaws.com/ksr/projects/939387/photo-ed.jpg?1397874930",
        "med": "https://s3.amazonaws.com/ksr/projects/939387/photo-med.jpg?1397874930",
        "little": "https://s3.amazonaws.com/ksr/projects/939387/photo-little.jpg?1397874930",
        "small": "https://s3.amazonaws.com/ksr/projects/939387/photo-small.jpg?1397874930",
        "thumb": "https://s3.amazonaws.com/ksr/projects/939387/photo-thumb.jpg?1397874930",
        "1024x768": "https://s3.amazonaws.com/ksr/projects/939387/photo-1024x768.jpg?1397874930",
        "1536x1152": "https://s3.amazonaws.com/ksr/projects/939387/photo-1536x1152.jpg?1397874930"
      },
      "creator": {
        "id": 1714048992,
        "name": "Maridee Slater",
        "slug": "maridee",
        "avatar": {
          "thumb": "https://s3.amazonaws.com/ksr/avatars/996153/DSC_0310.thumb.jpg?1337713264",
          "small": "https://s3.amazonaws.com/ksr/avatars/996153/DSC_0310.small.jpg?1337713264",
          "medium": "https://s3.amazonaws.com/ksr/avatars/996153/DSC_0310.medium.jpg?1337713264"
        },
        "urls": {
          "web": {
            "user": "https://www.kickstarter.com/profile/maridee"
          },
          "api": {
            "user": "https://api.kickstarter.com/v1/users/1714048992?signature=1398256877.e6d63adcca055cd041a5920368b197d40459f748"
          }
        }
      },
      "location": {
        "id": 2459115,
        "name": "New York",
        "slug": "new-york-ny",
        "short_name": "New York, NY",
        "displayable_name": "New York, NY",
        "country": "US",
        "state": "NY",
        "urls": {
          "web": {
            "discover": "https://www.kickstarter.com/discover/places/new-york-ny",
            "location": "https://www.kickstarter.com/locations/new-york-ny"
          },
          "api": {
            "nearby_projects": "https://api.kickstarter.com/v1/discover?signature=1398256786.89b2c4539aeab4ad25982694dd7e659e8c12028f&woe_id=2459115"
          }
        }
      },
      "category": {
        "id": 17,
        "name": "Theater",
        "slug": "theater",
        "position": 14,
        "urls": {
          "web": {
            "discover": "http://www.kickstarter.com/discover/categories/theater"
          }
        }
      },
      "urls": {
        "web": {
          "project": "https://www.kickstarter.com/projects/maridee/song-of-the-sea"
        }
      }
    },
    {
      "id": 967108708,
      "name": "Good Bread Alley",
      "blurb": "A play by April Yvette Thompson. A Gullah Healer Woman and an Afro-Cuban Priest forge a new world of magic & dreams in Jim Crow Miami.",
      "goal": 100000,
      "pledged": 33242,
      "state": "live",
      "slug": "good-bread-alley",
      "disable_communication": false,
      "country": "US",
      "currency": "USD",
      "currency_symbol": "$",
      "currency_trailing_code": true,
      "deadline": 1399271911,
      "state_changed_at": 1396334313,
      "created_at": 1393278556,
      "launched_at": 1396334311,
      "backers_count": 261,
      "photo": {
        "full": "https://s3.amazonaws.com/ksr/projects/883489/photo-full.jpg?1397869394",
        "ed": "https://s3.amazonaws.com/ksr/projects/883489/photo-ed.jpg?1397869394",
        "med": "https://s3.amazonaws.com/ksr/projects/883489/photo-med.jpg?1397869394",
        "little": "https://s3.amazonaws.com/ksr/projects/883489/photo-little.jpg?1397869394",
        "small": "https://s3.amazonaws.com/ksr/projects/883489/photo-small.jpg?1397869394",
        "thumb": "https://s3.amazonaws.com/ksr/projects/883489/photo-thumb.jpg?1397869394",
        "1024x768": "https://s3.amazonaws.com/ksr/projects/883489/photo-1024x768.jpg?1397869394",
        "1536x1152": "https://s3.amazonaws.com/ksr/projects/883489/photo-1536x1152.jpg?1397869394"
      },
      "creator": {
        "id": 749318998,
        "name": "April Yvette Thompson",
        "avatar": {
          "thumb": "https://s3.amazonaws.com/ksr/avatars/9751919/kick_thumb.thumb.jpg?1396128151",
          "small": "https://s3.amazonaws.com/ksr/avatars/9751919/kick_thumb.small.jpg?1396128151",
          "medium": "https://s3.amazonaws.com/ksr/avatars/9751919/kick_thumb.medium.jpg?1396128151"
        },
        "urls": {
          "web": {
            "user": "https://www.kickstarter.com/profile/749318998"
          },
          "api": {
            "user": "https://api.kickstarter.com/v1/users/749318998?signature=1398256877.af4db50c53f93339b05c7813f4534e833eaca270"
          }
        }
      },
      "location": {
        "id": 2459115,
        "name": "New York",
        "slug": "new-york-ny",
        "short_name": "New York, NY",
        "displayable_name": "New York, NY",
        "country": "US",
        "state": "NY",
        "urls": {
          "web": {
            "discover": "https://www.kickstarter.com/discover/places/new-york-ny",
            "location": "https://www.kickstarter.com/locations/new-york-ny"
          },
          "api": {
            "nearby_projects": "https://api.kickstarter.com/v1/discover?signature=1398256786.89b2c4539aeab4ad25982694dd7e659e8c12028f&woe_id=2459115"
          }
        }
      },
      "category": {
        "id": 17,
        "name": "Theater",
        "slug": "theater",
        "position": 14,
        "urls": {
          "web": {
            "discover": "http://www.kickstarter.com/discover/categories/theater"
          }
        }
      },
      "urls": {
        "web": {
          "project": "https://www.kickstarter.com/projects/749318998/good-bread-alley"
        }
      }
    }

Here is the code I run

#open the json file
require(RJSONIO)
require(rjson)
library("rjson")
filename2 <- "C:/Users/Desktop/in.json"
json_data <- fromJSON(file = filename2)

#unlist the json because it has a problem
unlisted <- unlist(unlist(json_data,recursive=FALSE),recursive=FALSE)

use to fill the NA but as I can understand now it is for already existed nulls http://stackoverflow.com/questions/16947643/getting-imported-json-data-into-a-data-frame-in-r/16948174#16948174
unlisted <- lapply(unlisted, function(x) {
  x[sapply(x, is.null)] <- NA
  unlist(x)
})

json <- do.call("rbind", unlisted)

Here is a full list with the columns of the output csv and after that I provide what I would like to keep from every object of json, less columns

id
name
blurb
goal
pledged
state
slug
disable_communication
country
currency
currency_symbol
currency_trailing_code
deadline
state_changed_at
created_at
launched_at
backers_count
photo.full
photo.ed
photo.med
photo.little
photo.small
photo.thumb
photo.1024x768
photo.1536x1152
creator.id
creator.name
creator.slug
creator.avatar.thumb
creator.avatar.small
creator.avatar.medium
creator.urls.web.user
creator.urls.api.user
location.id
location.name
location.slug
location.short_name
location.displayable_name
location.country
location.state
location.urls.web.discover
location.urls.web.location
location.urls.api.nearby_projects
category.id
category.name
category.slug
category.position
category.urls.web.discover
category.urls.web.project
category.urls.web.rewards

Here it is the list of columns I would try to have in the output csv:

id
name
blurb
goal
pledged
state
slug
disable_communication
country
currency
currency_symbol
currency_trailing_code
deadline
state_changed_at
created_at
launched_at
backers_count
creator.id
creator.name
creator.slug
location.id
location.name
location.slug
location.short_name
location.displayable_name
location.country
location.state
category.id
category.name
category.slug
category.position

You don't need RJSONIO and rjson...you just need one or the other. — Thomas, Mar 29 '15 at 10:52
@Thomas thank you for your comment. yes I use the to test some things that why I left. Sorry — Bil Bal, Mar 29 '15 at 10:56
it's unlikely you really want or need each JSON object fully expanded as a very wide data.frame. which fields do you want/need from it? — hrbrmstr, Mar 29 '15 at 12:04
@hrbrmstr I edit the question with let's say a full list of columns which can extracted related to json format and after that a list with less columns than the initial which I really want to have — Bil Bal, Mar 29 '15 at 12:39

score 4 · Answer 1 · edited May 23 '17 at 12:06

4

Looks like there's a very similar question (with answer, though not pure R) here: convert json to csv format

However, since you do seem to want most, if not all, the JSON in a "wide CSV" format you can use fromJSON from jsonlite, rbindlist from data.table (which gets you the fill=TRUE parameter to handle uneven lists nicely) and unlist:

library(jsonlite)
library(data.table)

# tell fromJSON we want a list back

json_data <- fromJSON("in.json", simplifyDataFrame=FALSE)

# iterate over the list we have so we can "flatten" it then
# covert it back to a data.frame-like object

dat <- rbindlist(lapply(json_data, function(x) {
  as.list(unlist(x))
}), fill=TRUE)

You may need to tweak column names, but I think this gets you what you're looking for.

edited May 23 '17 at 12:06

Community

1
1

answered Mar 29 '15 at 13:23

hrbrmstr

77,368
11
139
205

thank you very much for your answer. The data I try to handle have a strange format at first level. Here I have a snippet https://jsfiddle.net/eraera/ezn2q6n3/ There is a "total_hits" which I don't need it and produce a wrong format. Which the process I mention in the question unlisted <- unlist(unlist(json_data,recursive=FALSE),recursive=FALSE) I can solve the problem (because if I don't use it the columns are around 1000 columns from the initial file I use) however I have the problem I mentioned. – Bil Bal Mar 29 '15 at 14:39

How to convert a json file without the same length of json objects into csv

1 Answers1