Python Forum

Full Version: Trying to import JSON data into Python/Pandas DataFrame then edit then write CSV
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Hi, new to Python. Stepping into it for a project I'm trying of processing JSON URL output from Cryptocompare.com prices website.
Wanting to edit data structure (delete some columns, reorder some columns), then write out to CSV file for import into charting program.

An example of the URL return is:

I'm only interested in the Data part, stuff in [sq brackets].

{"Response":"Success","Type":100,"Aggregated":false,"Data":[{"time":1532340000,"close":7680,"high":7714,"low":7665,"open":7707.2,"volumefrom":182.08,"volumeto":1399902.51},{"time":1532343600,"close":7680.3,"high":7710.3,"low":7657.6,"open":7680,"volumefrom":153.42,"volumeto":1179699.16},{"time":1532347200,"close":7706,"high":7718.1,"low":7659.9,"open":7680.3,"volumefrom":107.17,"volumeto":823766.57},{"time":1532350800,"close":7720.3,"high":7724.4,"low":7689,"open":7706,"volumefrom":265.77,"volumeto":2047882.26},{"time":1532354400,"close":7696.8,"high":7720.3,"low":7689,"open":7720.3,"volumefrom":90.95,"volumeto":700561.68},{"time":1532358000,"close":7730,"high":7746.1,"low":7690,"open":7696.8,"volumefrom":124.27,"volumeto":959478.11},{"time":1532361600,"close":7736.9,"high":7745.5,"low":7698.9,"open":7730,"volumefrom":137.99,"volumeto":1065916.57},{"time":1532365200,"close":7733,"high":7747.8,"low":7703.1,"open":7736.9,"volumefrom":143.11,"volumeto":1106167.82},{"time":1532368800,"close":7742,"high":7844.9,"low":7596,"open":7733,"volumefrom":756.65,"volumeto":5846133.3},{"time":1532372400,"close":7746.8,"high":7798,"low":7721.7,"open":7742,"volumefrom":215.41,"volumeto":1671005.17},{"time":1532376000,"close":7728,"high":7764.8,"low":7728,"open":7746.8,"volumefrom":67.77,"volumeto":525240.08}],"TimeTo":1532376000,"TimeFrom":1532340000,"FirstValueInArray":true,"ConversionType":{"type":"force_direct","conversionSymbol":""}}

So far I'm been trawling the Python reference pages to learn how to import JSON, and syntax etc. But I'm getting bogged down.
Thought I'd see if you guys can speed me up?

-------------------------------------------------
import urllib
import pandas as PandaPrices
from pandas.io.json import json_normalize

Crypto_URL = "https://min-api.cryptocompare.com/data/histohour?fsym=BTC&tsym=USD&limit=10&e=Kraken"

CryptoTable = json_normalize(Crypto_URL)

------------------------------------------------
Error I get from the above: Attribute error: str object has no attribute 'values'
I'm assuming its having difficulty in reading the whole JSON structure. The beginning and end is extra info, not prices.
I'm trying to "flatten" the JSON structure into something like the resulting CSV that I get manually. Using json_normalize, but it doesn't seem to be working.

Is the json_normalize function going to try creating data structure for the beginning "header" and ending "footer" as well as the core "data"? I'm happy to dump all exept "data" section before the DataFrame is populated if possible.

I was trying both read_json and json_normalize,

I've been doing the process manually thus far, using free JSON 2 CSV converters, then editing the CSV in Spreadsheet.
An example of a faw CSV that I work with : (it makes more sense in a spreadsheet)

"Response";"Type";"Aggregated";"Data__time";"Data__close";"Data__high";"Data__low";"Data__open";
"Data__volumefrom";"Data__volumeto";"TimeTo";"TimeFrom";"FirstValueInArray";"ConversionType__type";"ConversionType__conversionSymbol"
"Success";"100";"False";"20180719 10:00:00";"86.97";"87.75";"86.97";"87.72";"124.64";"10889.47";"20180723 14:00:00";"20180719 10:00:00";"True";"force_direct";""
"";"";"";"20180719 11:00:00";"87.62";"87.62";"86.76";"86.97";"77.03";"6699.84";"";"";"";"";""
"";"";"";"20180719 12:00:00";"87.9";"87.96";"87.45";"87.62";"777.59";"68299.66";"";"";"";"";""

I can pm or upload to online space entire documents if anyone needs.

Thanks,
Rupert