Importing JSON into Pandas DataFrame

Question

I'm trying to import the table in the following link: https://graphs.coinmarketcap.com/v1/datapoints/bitcoin/

pd.read_json('https://graphs.coinmarketcap.com/v1/datapoints/bitcoin/').head()

Gives me the following:

  market_cap_by_available_supply             price_btc  \
0    [1367174841000, 1500517590]  [1367174841000, 1.0]   
1    [1367261101000, 1575032004]  [1367261101000, 1.0]   
2    [1367347502000, 1501657492]  [1367347502000, 1.0]   
3    [1367433902000, 1298951550]  [1367433902000, 1.0]   
4    [1367522401000, 1148667722]  [1367522401000, 1.0]   

                 price_usd            volume_usd  
0   [1367174841000, 135.3]  [1367174841000, 0.0]  
1  [1367261101000, 141.96]  [1367261101000, 0.0]  
2   [1367347502000, 135.3]  [1367347502000, 0.0]  
3   [1367433902000, 117.0]  [1367433902000, 0.0]  
4  [1367522401000, 103.43]  [1367522401000, 0.0]

The values in the first position of the lists are time stamps that I want to be the index of the DataFrame. e.g. [timestamp, value]

Is there anyway to do this within the pd.read_json command?

Have a look at this question: stackoverflow.com/questions/32866925/… — Pierz
– Pierz, Commented Dec 21, 2018 at 15:03

pansen · Accepted Answer · 2017-04-03 17:14:09Z

I don't think it is possible since the orient parameter of pd.read_json has no option which correctly maps to your required format.

However, you may use requests in conjunction with a tiny dictionary comprehension here:

import requests

url = 'https://graphs.coinmarketcap.com/v1/datapoints/bitcoin/'
json = requests.get(url).json()
df = pd.DataFrame({col: dict(vals) for col, vals in json.items()})

print(df.head())

                market_cap_by_available_supply  price_btc   price_usd   volume_usd
1367174841000   1500517590                      1.0         135.30     0.0
1367261101000   1575032004                      1.0         141.96     0.0
1367347502000   1501657492                      1.0         135.30     0.0
1367433902000   1298951550                      1.0         117.00     0.0
1367522401000   1148667722                      1.0         103.43     0.0

jose_bacoy · Accepted Answer · 2017-04-03 21:30:48Z

0

Try this.

import pandas as pd
df = pd.read_json('https://graphs.coinmarketcap.com/v1/datapoints/bitcoin/')
df = pd.DataFrame([df[df.columns[0]][i][0] for i in range(len(df))]).join(df)
df = df.set_index(df.columns[0])
df.index.rename('timestamp', inplace=True)
df.head()

edited Apr 3, 2017 at 21:30

answered Apr 3, 2017 at 16:53

jose_bacoy

12.7k1 gold badge25 silver badges41 bronze badges

Collectives™ on Stack Overflow

Importing JSON into Pandas DataFrame

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related