0

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'])
bullybear17
  • 859
  • 2
  • 13
  • 31
  • 2
    `df = pd.DataFrame(response.json())` – It_is_Chris Jan 06 '21 at 19:45
  • I cannot access the API, as the creds are blocked. But I suspect based on what you have, is that the api is returning a string version of json. Thus you could just use Json.loads(response.text) and pass that to pd.DataFrame and it should work. But Pandas does allow for you to create a DataFrame directly from pd.DataFrame() and a list of dictionaries. – WolVes Jan 06 '21 at 19:48
  • Your first solution worked for me. I'm using pandas version 0.22.0 (use this command to check yours: pd.__version__) on python 3.6.10 – Ricardo Jan 06 '21 at 19:49

2 Answers2

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

Anis R.
  • 6,656
  • 2
  • 15
  • 37
1

The problem is that in df = pd.DataFrame(response) response is not the right type of object you need as an argument. You need to give as argument a list of Dictionaries. You can parse the response.text

Petronella
  • 2,327
  • 1
  • 15
  • 24