Parse nested json to pandas dataframe-CodePudding

I'm trying to parse nested json to pandas dataframe but can not get a good output either with json_normalize or using a loop

#libraries 
import json
import requests
import pandas as pd
from pprint import pprint as pp

#___[enter image description here][1]___Request url___
#define a function for mcsa url requests
def load_url(url):
    r = requests.get(url)
    if r.status_code != 200:
        return None
    return r.json()

r_dict = load_url('https://www.ebi.ac.uk/thornton-srv/m-csa/api/residues/?format=json')

#inspect the json response file 
list(r_dict)

enter image description here

CodePudding user response：

Try:

df = pd.read_json(
    "https://www.ebi.ac.uk/thornton-srv/m-csa/api/residues/?format=json"
)
df = df.explode("residue_chains")
df = df.explode("residue_sequences")
df = df.explode("roles")

df = pd.concat(
    [df, df.pop("roles").apply(pd.Series).add_prefix("roles_")], axis=1
).drop(columns="roles_0")

df = pd.concat(
    [
        df,
        df.pop("residue_chains").apply(pd.Series).add_prefix("residue_chains_"),
    ],
    axis=1,
).drop(columns="residue_chains_0")

df = pd.concat(
    [
        df,
        df.pop("residue_sequences")
        .apply(pd.Series)
        .add_prefix("residue_sequences_"),
    ],
    axis=1,
)

print(df.head())

Prints:

   mcsa_id                                                                                      roles_summary function_location_abv ptm       roles_group_function roles_function_type            roles_function  roles_emo residue_chains_chain_name residue_chains_pdb_id residue_chains_assembly_chain_name residue_chains_assembly residue_chains_code  residue_chains_resid  residue_chains_auth_resid residue_chains_is_reference residue_chains_domain_name residue_chains_domain_cath_id residue_sequences_uniprot_id residue_sequences_code  residue_sequences_is_reference  residue_sequences_resid
0        1  activator, electrostatic stabiliser, hydrogen bond acceptor, hydrogen bond donor, proton acceptor                                            activator           spectator                 activator  EMO_00038                         A                  1b73                                  A                       1                 Asp                   7.0                        7.0                        True                        A01                  3.40.50.1860                       P56868                    Asp                            True                        7
0        1  activator, electrostatic stabiliser, hydrogen bond acceptor, hydrogen bond donor, proton acceptor                                                              interaction    hydrogen bond acceptor  EMO_00113                         A                  1b73                                  A                       1                 Asp                   7.0                        7.0                        True                        A01                  3.40.50.1860                       P56868                    Asp                            True                        7
0        1  activator, electrostatic stabiliser, hydrogen bond acceptor, hydrogen bond donor, proton acceptor                            electrostatic interaction           spectator  electrostatic stabiliser  EMO_00033                         A                  1b73                                  A                       1                 Asp                   7.0                        7.0                        True                        A01                  3.40.50.1860                       P56868                    Asp                            True                        7
0        1  activator, electrostatic stabiliser, hydrogen bond acceptor, hydrogen bond donor, proton acceptor                                                              interaction       hydrogen bond donor  EMO_00114                         A                  1b73                                  A                       1                 Asp                   7.0                        7.0                        True                        A01                  3.40.50.1860                       P56868                    Asp                            True                        7
0        1  activator, electrostatic stabiliser, hydrogen bond acceptor, hydrogen bond donor, proton acceptor                            electrostatic interaction           spectator  electrostatic stabiliser  EMO_00033                         A                  1b73                                  A                       1                 Asp                   7.0                        7.0                        True                        A01                  3.40.50.1860                       P56868                    Asp                            True                        7