2

I am trying to understand how JSON data which is not parsed/extracted correctly can be converted into a (Pandas) DataFrame.

I am using python (3.7.1) and have tried the usual way of reading the JSON data. Actually, the code works if I use transpose or axis=1 syntax. But using that completely ignores a large number of values or variables in the data and I am 100% sure that the maybe the code is working but is not giving the desired results. 

import pandas as pd
import numpy as np
import csv
import json

sourcefile = open(r"C:\Users\jadil\Downloads\chicago-red-light-and-speed-camera-data\socrata_metadata_red-light-camera-violations.json")
json_data = json.load(sourcefile)
#print(json_data)

type(json_data)
dict

## this code works but is not loading/reading complete data
df = pd.DataFrame.from_dict(json_data, orient="index")
df.head(15)

#This is what I am getting for the first 15 rows
df.head(15)

0
createdAt   1407456580
description This dataset reflects the daily volume of viol...
rights  [read]
flags   [default, restorable, restorePossibleForType]
id  spqx-js37
oid 24980316
owner   {'type': 'interactive', 'profileImageUrlLarge'...
newBackend  False
totalTimesRated 0
attributionLink http://www.cityofchicago.org
hideFromCatalog False
columns [{'description': 'Intersection of the location...
displayType table
indexUpdatedAt  1553164745
rowsUpdatedBy   n9j5-zh
Jay
  • 21
  • 3

1 Answers1

1

As you have seen, Pandas will attempt to create a data frame out of JSON data even if it is not parsed or extracted correctly. If your goal is to understand exactly what Pandas does when presented with a messy JSON file, you can look inside the code for pd.DataFrame.from_dict() to learn more. If your goal is to get the JSON data to convert correctly to a Pandas data frame, you will need to provide more information abut the JSON data, ideally by providing a sample of the data as text in your question. If your data is sufficiently complicated, you might try the json_normalize() function as described here.

Nathaniel
  • 3,230
  • 11
  • 18