how to read a json web response representing a pandas dataframe

Question

I have a json response from a FastAPI REST service that is generated like this:

dfjson = df.to_json(orient='records', date_format='iso', indent=4)

and looks like this:

[
    {
        "Date":"2017-12-01T00:00:00.000Z",
        "RR":0.0,
        "Symbol":"AAPL"
    },
    {
        "Date":"2018-03-12T00:00:00.000Z",
        "RR":-0.0655954215,
        "Symbol":"AAPL"
    },
    {
        "Date":"2018-03-14T00:00:00.000Z",
        "RR":-0.0493162968,
        "Symbol":"AAPL"
    },
    {
        "Date":"2018-03-15T00:00:00.000Z",
        "RR":-0.0539632781,
        "Symbol":"AAPL"
    },
    ...

On the client side, I am trying to convert this json into a pandas dataframe thus:

df = pd.read_json(response.text)
print(df)

But I get an error:

....
raise ValueError("If using all scalar values, you must pass an index")
ValueError: If using all scalar values, you must pass an index

I am not sure what I am doing wrong?

Are you using `requests` or something similar to fetch the data? In that case, you probably want `response.json()` — M.O., Mar 10 '23 at 23:43

Laurent B. · Accepted Answer · 2023-03-11T15:34:11.277

1

I suspected this was due to json strict format. And this is the case.

Please see my question there

pd.read_json(str(response).replace("'", '"'), orient='records')

                       Date        RR Symbol
0 2017-12-01 00:00:00+00:00  0.000000   AAPL
1 2018-03-12 00:00:00+00:00 -0.065595   AAPL
2 2018-03-14 00:00:00+00:00 -0.049316   AAPL
3 2018-03-15 00:00:00+00:00 -0.053963   AAPL

edited Mar 11 '23 at 15:34

answered Mar 11 '23 at 15:23

Laurent B.

1,653
1
7
16

how to read a json web response representing a pandas dataframe

1 Answers1