Python Forum
Trying to import JSON data into Python/Pandas DataFrame then edit then write CSV
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Trying to import JSON data into Python/Pandas DataFrame then edit then write CSV
#1
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
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Grouping in pandas/multi-index data frame Aleqsie 3 607 Jan-06-2024, 03:55 PM
Last Post: deanhystad
  HTML Decoder pandas dataframe column mbrown009 3 962 Sep-29-2023, 05:56 PM
Last Post: deanhystad
  Use pandas to obtain cartesian product between a dataframe of int and equations? haihal 0 1,091 Jan-06-2023, 10:53 PM
Last Post: haihal
Smile How to further boost the data read write speed using pandas tjk9501 1 1,230 Nov-14-2022, 01:46 PM
Last Post: jefsummers
  How to insert data in a dataframe? man0s 1 1,313 Apr-26-2022, 11:36 PM
Last Post: jefsummers
  Pandas Dataframe Filtering based on rows mvdlm 0 1,396 Apr-02-2022, 06:39 PM
Last Post: mvdlm
  Pandas dataframe: calculate metrics by year mcva 1 2,269 Mar-02-2022, 08:22 AM
Last Post: mcva
Thumbs Up can't access data from URL in pandas/jupyter notebook aaanoushka 1 1,830 Feb-13-2022, 01:19 PM
Last Post: jefsummers
  Pandas dataframe comparing anto5 0 1,243 Jan-30-2022, 10:21 AM
Last Post: anto5
  PANDAS: DataFrame | Replace and others questions moduki1 2 1,759 Jan-10-2022, 07:19 PM
Last Post: moduki1

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020