How to transform a class in python into a pandas dataframe?

user_9940344 0
Viewed 2172

I'm trying to create a pandas dataframe from a class object in python.

The class object is the output of postman python script I got from the following tutorial: https://developer.cisco.com/meraki/build/meraki-postman-collection-getting-started/

I wish to take the output of this 

print(response.text)

which gives:

[{"id":578149602163689207,"name":"Axel Network Test"},{"id":578149602163688579,"name":"Your org"},{"id":578149602163688880,"name":"Your org"},{"id":578149602163688885,"name":"Your org"},{"id":578149602163689038,"name":"Tory's Test Lab"},.......

I want to put this into a pandas dataframe with and ID column and name column.

import requests
import pandas as pd

url = "https://api.meraki.com/api/v0/organizations"

headers = {
    'X-Cisco-Meraki-API-Key': "xxxxxxxxxxxxxxxxxxxxxxxxxxx",
    'User-Agent': "PostmanRuntime/7.15.0",
    'Accept': "*/*",
    'Cache-Control': "no-cache",
    'Postman-Token': "7d29cb4e-b022-4954-8fc8-95e5361d15ba,1a3ec8cb-5da8-4983-956d-aab45ed00ca1",
    'accept-encoding': "gzip, deflate",
    'referer': "https://api.meraki.com/api/v0/organizations",
    'Connection': "keep-alive",
    'cache-control': "no-cache"
    }

response = requests.request("GET", url, headers=headers)

I tired to write 

df = pd.DataFrame(response, columns=['id', 'name'])

but this produces many errors.

See error log: https://pastebin.com/4BKFYng1

How can I achieve what I want?

pandas postman python-3.x api python
4 Answers

As the response text is in json, you can:
1. Convert the json to a dict.
2. Feed the dict as a dataframe. 

#load the json as a dict
data = json.loads(response.text)

df = pd.DataFrame.from_dict(data, orient='index')
df.reset_index(level=0, inplace=True)

Then you can change the name of the columns or anything else.

user_74547060

read_json accepts a JSON string or JSON file-like object. 

In [10]: import pandas as pd    

In [11]: df = pd.read_json(response.text)

In [12]: df
Out[12]:
                   id               name
0  578149602163689207  Axel Network Test
1  578149602163688579           Your org
2  578149602163688880           Your org
3  578149602163688885           Your org
4  578149602163689038    Tory's Test Lab
user_82121730

After receiving the response, you are missing deserializing the json response to python object as @ASHu2 mentioned

import pandas as pd
import json 
data = json.loads(response.text)
df= pd.DataFrame(data=data, columns=["id","name"]) 
#Optional define index
df.set_index('id', inplace=True)
user_7856730

Try this:

df = pd.DataFrame.from_dict(response.json())

instead of this:

df = pd.DataFrame(response, columns=['id', 'name'])

user_109326620
Related