python - Trying to convert list of dictionaries to a pandas dataframe

Question

I am trying to convert a list of dictionaries to a pandas dataframe like in the stackoverflow post here, however for whatever reason I cant get it to work.

Convert list of dictionaries to a pandas DataFrame

When I try to do it with my code below, I get a 1824 rows × 1 columns data object, which is not correct (I want 6 columns).

Here is a snapshot of my data object (returned from response.text) which I want in my pandas DataFrame

[
{
    "base_attack": 118,
    "base_defense": 111,
    "base_stamina": 128,
    "form": "Fall_2019",
    "pokemon_id": 1,
    "pokemon_name": "Bulbasaur"
},
{
    "base_attack": 118,
    "base_defense": 111,
    "base_stamina": 128,
    "form": "Normal",
    "pokemon_id": 1,
    "pokemon_name": "Bulbasaur"
},
]

Here is my code (api key is public and used in examples):

import requests

url = "https://pokemon-go1.p.rapidapi.com/pokemon_stats.json"

headers = {
 'x-rapidapi-key': "4a84fad910msha335a71778de51cp1a79d6jsnafac3cffa115",
 'x-rapidapi-host': "pokemon-go1.p.rapidapi.com"
 }

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

print(response.text)

df = pd.DataFrame(response)

df

I have also tried these 2 approached:

df = pd.DataFrame.from_dict({k: v for d in response.text for k, v in d.items()}, 
                         orient='index', 
                         columns=['pokemon_name', 'form', 'base_attack', 'base_defense', 
                         'base_stamina']).rename_axis('pokemon_id').reset_index()

df = pd.DataFrame(response.text, columns=['base_attack', 'base_defense', 'base_stamina', 
                                'form', 'pokemon_id', 'pokemon_name'])

Answer 1

Your response.text is a string, not a dictionary as Pandas expects. You should use response.json() if that's available.

df = pd.DataFrame(response.json(), columns=['base_attack', 'base_defense', 'base_stamina', 
                                'form', 'pokemon_id', 'pokemon_name'])

---

Alternatively, you can convert response.text to a dictionary using json.loads