Cannot get pandas tabular data from json to csv and back from dataframe

Admin 1
Viewed 60

I hope I do this right. I'm a first time poster. I have a pandas json-csv data-frame problem, and have been looking for solutions for a few days now. It may be small and obvious error, but I have tried may examples. Here is my code:

print('__Trades__' '\n')
    since = int(time.time()*1000)-24*60*60*1000
    xbtzartrades = c.list_trades(pair='XBTZAR', since=since)
    exctrades = (xbtzartrades)
    json_object = json.dumps(exctrades, indent=4)  # Serializing json
    with open("exdata/json/xbtzartrades.json", "w") as outfile:    #  Open a file in exdata/json/
        outfile.write(json_object)  # Writing to json
    df = pd.read_json("exdata/json/xbtzartrades.json")   # read the json file
    df.to_csv(r'exdata/csv/xbtzartrades.csv', index=None)
    #  print(df)   #  Print the contents of df
    #  print(xbtzartrades)    #  Print the recent trades.

Output to exdata/json/xbtzartrades.json:

{
    "trades": [
        {
            "sequence": 7933159,
            "timestamp": 1586191458641,
            "price": "139449.00",
            "volume": "0.001444",
            "is_buy": false
        },
        {
            "sequence": 7933158,
            "timestamp": 1586191450810,
            "price": "139449.00",
            "volume": "0.001122",
            "is_buy": false
        },  ------------> ECT.

Instead of a tabular format. The CSV file ends up like this: 

trades
"{'sequence': 7933159, 'timestamp': 1586191458641, 'price': '139449.00', 'volume': '0.001444', 'is_buy': False}"
"{'sequence': 7933158, 'timestamp': 1586191450810, 'price': '139449.00', 'volume': '0.001122', 'is_buy': False}"
"{'sequence': 7933157, 'timestamp': 1586191450695, 'price': '139449.00', 'volume': '0.003657', 'is_buy': False}"
"{'sequence': 7933156, 'timestamp': 1586191446474, 'price': '139450.00', 'volume': '0.004181', 'is_buy': True}"
"{'sequence': 7933155, 'timestamp': 1586191438571, 'price': '139449.00', 'volume': '0.005716', 'is_buy': False}"
"{'sequence': 7933154, 'timestamp': 1586191421319, 'price': '139449.00', 'volume': '0.001918', 'is_buy': False}"
"{'sequence': 7933153, 'timestamp': 1586191420238, 'price': '139449.00', 'volume': '0.007831', 'is_buy': False}"
"{'sequence': 7933152, 'timestamp': 1586191420180, 'price': '139450.00', 'volume': '0.000954', 'is_buy': True}"
pandas dataframe python json
2 Answers

It looks like xbtzartrades has the data you need. It doesn't seem like you need to go through all of the intermediate steps of converting to JSON and then reading a file into pandas.

xbtzartrades = c.list_trades(pair='XBTZAR', since=since)
df = pd.DataFrame(xbtzartrades['trades'])
df.to_csv('exdata/csv/xbtzartrades.csv', index=None)
user_5156630

So my output is correct now:

sequence,timestamp,price,volume,is_buy
7933537,1586195229592,139689.00,0.071588,False
7933536,1586195229533,139689.00,0.064388,False
7933535,1586195229515,139689.00,0.0072,False
7933534,1586195228302,139689.00,0.164651,False
Admin1
Related