Convert JSON data from Request into Pandas DataFrame

Question

I'm trying to scrape some data from a web page and put it into a pandas dataframe. I tried and read many things but I just cannot get what I want. And I want a dataframe with all the data in separate columns and rows. Below is my code.

import requests
import json
import pandas as pd
from pandas.io.json import json_normalize

r = requests.get('http://www.starcapital.de/test/Res_Stockmarketvaluation_FundamentalKZ_Tbl.php')

a = json.loads(r.text)

res = json_normalize(a)
##print(res)

df = pd.DataFrame(res)
print(df)

##df = pd.read_json(a)
##print(df)

pd.read_json(a) doesn't seem to work in any way. Could someone give it a try?

Thanks for all the help in advance.

Best regards, David

Answer 1

Or, more simply:

import requests
import pandas as pd

r = requests.get('http://www.starcapital.de/test/Res_Stockmarketvaluation_FundamentalKZ_Tbl.php')

j = r.json()

df = pd.DataFrame.from_dict(j)

Answer 2

And one step simpler than Justin's (already helpful) response...by putting .json() at the end of the r = requests.get line

import requests
import pandas as pd

r = requests.get('http://www.starcapital.de/test/Res_Stockmarketvaluation_FundamentalKZ_Tbl.php').json()

df = pd.DataFrame.from_dict(r)

Answer 3

You may also want pd.json_normalize for when your data isn't exactly the way from_dict() expects.

For example:

data = [
    {
        "id": 1,
        "name": "Cole Volk",
        "fitness": {"height": 130, "weight": 60},
    },
    {"name": "Mark Reg", "fitness": {"height": 130, "weight": 60}},
    {
        "id": 2,
        "name": "Faye Raker",
        "fitness": {"height": 130, "weight": 60},
    },
]
pd.json_normalize(data, max_level=1)
    id        name  fitness.height  fitness.weight
0  1.0   Cole Volk             130              60
1  NaN    Mark Reg             130              60
2  2.0  Faye Raker             130              60

Answer 4

This article saved me From API to Pandas—Getting JSON Data With Python