Format Json using Pandas DataFrame

Question

I'm trying to parse this JSON using pandas and getting this particular error:

{
    "tables": [
        {
            "name": "PrimaryResult",
            "columns": [
                {
                    "name": "ApiId",
                    "type": "string"
                },
                {
                    "name": "count_",
                    "type": "long"
                }
            ],
            "rows": [
                [
                    "test 1",
                    654321
                ],
                [
                    "test 2",
                    32564
                ],
                [
                    "test 3",
                    185262
                ]
            ]
        }
    ]
}

analytics_data = response_analytics.text
json_dict = json.loads(analytics_data)
df = pd.DataFrame.from_dict(json_dict["tables"])

cols_to_keep = ["ApiId", "count_"]

df_final = df[cols_to_keep]

df_final = df_final.rename(columns={"ApiId": "API Name", "count_": "Total requests"})

print(
    tabulate(
        df_final, showindex=False, headers=df_final, tablefmt="psql", numalign="left"
    )
)

Error:

Traceback (most recent call last):

File "C:\Users\test\te.py", line 93, in df_final = df[cols_to_keep]

File "C:\Users\test\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\pandas\core\frame.py", line 3461, in getitem indexer = self.loc._get_listlike_indexer(key, axis=1)1

File "C:\Users\test\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\pandas\core\indexing.py", line 1314, in _get_listlike_indexer self._validate_read_indexer(keyarr, indexer, axis)

File "C:\Users\test\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\pandas\core\indexing.py", line 1374, in validate_read_indexer raise KeyError(f"None of [{key}] are in the [{axis_name}]") KeyError: "None of [Index(['ApiId', 'count'], dtype='object')] are in the [columns]"

My goal is to create something like this:

API Name                            Total requests
----------------------------------  --------------------- 
test 1                              654321
test 2                              32564
test 3                              185262

How can I convert this JSON schema to a table? The data is exported using a REST API

Print results:

print(tabulate(df, showindex=False, headers=df, tablefmt='psql', numalign="left"))

json_dict = json.loads(text_data) should it be text_data or analytics_data? — Owenn
– Owenn, Commented Aug 23, 2021 at 10:37
It should be analytics_date. Updated the code and the error. Thanks @Owenn — Amir
– Amir, Commented Aug 23, 2021 at 10:55
You could try to use df.columns to see what are the column name that exists in the dataframe, and you could use rename() to rename those columns into the ones you want. — Owenn
– Owenn, Commented Aug 23, 2021 at 11:17
This does not look like a valid JSON. The 3 list items in "rows" key should be wrapped in a parent list. — Divyesh Peshavaria
– Divyesh Peshavaria, Commented Aug 23, 2021 at 11:34
@Amir in that case does creating DataFrame this way work for you - df = pd.DataFrame(data=json_dict["tables"][0]["rows"], columns=[r["name"] for r in json_dict["tables"][0]["columns"]]) ? — Divyesh Peshavaria
– Divyesh Peshavaria, Commented Aug 23, 2021 at 12:08

Divyesh Peshavaria · Accepted Answer · 2021-08-24 06:16:43Z

1

Creating your dataframe this way will work:

df = pd.DataFrame(
    data=json_dict["tables"][0]["rows"], 
    columns=[r["name"] for r in json_dict["tables"][0]["columns"]]
)

answered Aug 24, 2021 at 6:16

Divyesh Peshavaria

5991 gold badge5 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Format Json using Pandas DataFrame

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related