Read json output into Pandas Dataframe

Question

How do I put the following json output into a pandas dataframe?

[{'currency': '1ST', 'available': '0', 'reserved': '0'}, {'currency': '8BT', 'available': '0', 'reserved': '0'}, {'currency': 'ADX', 'available': '0', 'reserved': '0'}, {'currency': 'AE', 'available': '0', 'reserved': '0'}, {'currency': 'AEON', 'available': '0', 'reserved': '0'}, {'currency': 'AIR', 'available': '0', 'reserved': '0'}, {'currency': 'AMB', 'available': '0', 'reserved': '0'}, {'currency': 'AMM', 'available': '0', 'reserved': '0'}, {'currency': 'AMP', 'available': '0', 'reserved': '0'}]

I've tried the following but only receive the following error:

Code

balances = pd.read_json(data)

Error

ValueError: Invalid file path or buffer object type: <class 'method'>

Edit - How I get the data:

def get_account_balance(self):
    """Get main balance."""
    return self.session.get("https://api.hitbtc.com/api/2/account/balance").json()

MaxU - stand with Ukraine · Answer 1 · 2018-01-14T00:51:36.937

4

IIUC assuming you have a dictionary:

In [231]: d = [{'currency': 'ZRX', 'available': '0', 'reserved': '0'}, {'currency': 'ZSC', 'available': '0', 'reserved': '0'}]

In [235]: pd.DataFrame(d)
Out[235]:
  available currency reserved
0         0      ZRX        0
1         0      ZSC        0

If it's a string (broken JSON, as JSON must have double quotes instead of single quotes):

import json

In [238]: s = """
     ...: [{'currency': 'ZRX', 'available': '0', 'reserved': '0'}, {'currency': 'ZSC', 'available': '0', 'reserved': '0'}]
     ...: """

In [239]: d = json.loads(s.replace("'", '"'))

In [240]: pd.DataFrame(d)
Out[240]:
  available currency reserved
0         0      ZRX        0
1         0      ZSC        0

edited Jan 14 '18 at 00:51

answered Jan 14 '18 at 00:22

MaxU - stand with Ukraine

205,989
36
386
419

I get the following error using your string approach: AttributeError: 'list' object has no attribute 'replace'. See my edit above on how I get the data. – alpenmilch411 Jan 14 '18 at 00:38
@alpenmilch411, please provide a small __reproducible__ sample data set, so that we could reproduce your issue – MaxU - stand with Ukraine Jan 14 '18 at 00:39
Excuse my ignorance but what do you exactly mean with reproducible sample data set? See my edit above. @MaxU – alpenmilch411 Jan 14 '18 at 00:47
@alpenmilch411, i mean that we need a data set which would help us to reproduce your error. When I try the data set from your question - both my solutions are working properly - whether it's a string or a dictionary... – MaxU - stand with Ukraine Jan 14 '18 at 00:50
Okay so my edit above solved the problem but I am guessing there is a better way to do this. @MaxU – alpenmilch411 Jan 14 '18 at 01:03

score 1 · Accepted Answer · answered Jan 14 '18 at 01:19

1

The following worked for me:

pd.DataFrame.from_dict(data, orient='columns')

Source: https://github.com/vi3k6i5/pandas_basics/blob/master/1_a_create_a_dataframe_from_dictonary.ipynb

answered Jan 14 '18 at 01:19

alpenmilch411

483
1
5
18

score 1 · Answer 3 · answered Oct 07 '18 at 19:28

I had the same problem while importing a json file to a pandas dataframe. I've solved it by flattening the dictionaries and then importing them normally:

def get_values(lVals):
    res = []
    for val in lVals:
        if type(val) not in [list, set, tuple]:
            res.append(val)
        else:
            res.extend(get_values(val))
    return res

data = get_values(data)

df = pd.DataFrame(data)

score 0 · Answer 4 · answered Aug 23 '19 at 19:12

Reference:

Python how convert single quotes to double quotes to format as json string | user3850

import json
import pandas as pd

json_data = [{'currency': '1ST', 'available': '0', 'reserved': '0'}, {'currency': '8BT', 'available': '0', 'reserved': '0'}, {'currency': 'ADX', 'available': '0', 'reserved': '0'}, {'currency': 'AE', 'available': '0', 'reserved': '0'}, {'currency': 'AEON', 'available': '0', 'reserved': '0'}, {'currency': 'AIR', 'available': '0', 'reserved': '0'}, {'currency': 'AMB', 'available': '0', 'reserved': '0'}, {'currency': 'AMM', 'available': '0', 'reserved': '0'}, {'currency': 'AMP', 'available': '0', 'reserved': '0'}]

json_format = json.dumps(json_data)

json_df = pd.read_json(json_format)

json_df.head()

Read json output into Pandas Dataframe

4 Answers4