JSON data to Pandas DataFrame

Question

Help I have a JSON file and I can't get it to load in to a python DataFrame. First question should probably be is this actually a json file? Was the resulting file when a chart was rendered on a Web page and this was the back end data I pulled from chrome network inspection.

It seems there is code in front that is not JSON or not a table so it messing up the import.

http://pastebin.com/ne4RRrgP

Can you please help

the below loads the file in to python

import json
from pprint import pprint
with open('data2.json') as data_file:
    data = json.load(data_file)

and

pprint(data)

does print the data but I can't then convert to a pandas dataframe

edit

Ok this must be javascript file that I think is JSON.

json validator says that this is not correct json (http://jsonlint.com/) +why you want to convert that huge html-like string and javascript to dataframe? — János Farkas
– János Farkas, Commented May 9, 2016 at 8:43
Sorry not possible as it's a log in site and don't want to share the password out. A chart is generated from the date and price data. I have searched how the chart is generated and came to this table and I'm assuming it's json, it may not be. Thing is there is possibly extra code at the start and end that I need to remove. So this file I found from inspecting the data loaded to chrome when the chart renders — nicktids
– nicktids, Commented May 9, 2016 at 9:20

hd1 · Accepted Answer · 2016-05-09 09:06:26Z

1

Just use pandas.read_json, which is merely a wrapper around the json class, but can take a remote URL as well as a local filename:

import pandas as pd
pandas_dataframe = pd.read_json('data2.json')

Hope that helps.

answered May 9, 2016 at 9:06

hd1

34.9k5 gold badges83 silver badges95 bronze badges

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

3 Comments

jezrael Over a year ago

Unfortunately it doesnt work, check it: print pd.read_json('http://pastebin.com/raw/ne4RRrgP')

hd1 Over a year ago

@jezrael use the "raw" link -- pd.read_json('http://pastebin.com/raw/ne4RRrgP')

jezrael Over a year ago

return for me ValueError: Unrecognized escape sequence when decoding 'string'

nicktids · Accepted Answer · 2016-05-10 03:25:18Z

0

The data was not formatted correctly I had to edit the text and then it loads correctly in to Pandas. By removing the header and the back slashes as it now looks like it's an ajax request with jason data inside.

{"status": "success", "chart": "\n\n\n\n\n\n\n\n\n\n    \n\n\n\n\n\n\n\n\n<div id=\"id-freight-cash-prices-chart\" style=\"min-height: 480px\"></div>\n\n\n    <script>\n        $(function () {\n\n            var series = [{\"type\": \"line\", \"data\": [{\"date\": 1262563200000, \"last\": \"319.00000\"}, {\"date\": 1262649600000, \"last\": \"318.00000\"}, {\"date\": 1262736000000, \"last\": \"320.00000\"}, {\"date\": 1262822400000, \"last\": \"321.00000\"},

Thanks for all your help

answered May 10, 2016 at 3:25

nicktids

1736 bronze badges

Collectives™ on Stack Overflow

JSON data to Pandas DataFrame

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related