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 · Accepted Answer · 2018-01-14 00:51:36Z

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, 2018 at 0:51

answered Jan 14, 2018 at 0:22

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

5 Comments

alpenmilch411 Over a year ago

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.

MaxU - stand with Ukraine Over a year ago

@alpenmilch411, please provide a small reproducible sample data set, so that we could reproduce your issue

alpenmilch411 Over a year ago

Excuse my ignorance but what do you exactly mean with reproducible sample data set? See my edit above. @MaxU

MaxU - stand with Ukraine Over a year ago

@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...

alpenmilch411 Over a year ago

Okay so my edit above solved the problem but I am guessing there is a better way to do this. @MaxU

alpenmilch411 · Accepted Answer · 2018-01-14 01:19:06Z

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, 2018 at 1:19

alpenmilch411

4931 gold badge6 silver badges18 bronze badges

Comments

tmsss · Accepted Answer · 2018-10-07 19:28:47Z

1

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)

answered Oct 7, 2018 at 19:28

tmsss

2,16922 silver badges25 bronze badges

Comments

datalifenyc · Accepted Answer · 2019-08-23 19:12:46Z

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()

Collectives™ on Stack Overflow

Read json output into Pandas Dataframe

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related