'hard-nested json and pd.json_normalize

I started using python and I'm a beginner. I have been trying to normalize a very nested Json file (see it later).

In simple terms I've 2 main nests that I want to flatten:

portfolio_item_list (the list contains 2 dictionaries)
supply token list (a dictionary for each of the previous ones)

I succeeded in flattening them separately by using pd.json_normalize but I can't figure out how to flatten everything to get a table like this (in other words I can't understand how to merge them).

     protocol      id   protocol name   chain   tvl asset_usd_value debt_usd_value  net_usd_value   portfolio_item_list.name    optimized_symbol    amount
1   ftm_beefy   Beefy   ftm 239485780,8 512,6956089 0   512,6956089 Yield   WFTM    351,0579442
1   ftm_beefy   Beefy   ftm 239485780,8 512,6956089 0   512,6956089 Yield   TOMB    356,9391582
2   ftm_beefy   Beefy   ftm 239485780,8 106,1679945 0   106,1679945 Yield   SPELL   34050,0303

With this code I succeeded in flattening the first level

pd.json_normalize(first_data, ["portfolio_item_list"])

      update_at   name                                     pool_id detail_types  stats.asset_usd_value  stats.debt_usd_value  stats.net_usd_value                           detail.supply_token_list
0  1.651872e+09  Yield  0x429590a528a86a0da0aca9aa7cd087badc790af8     [common]             501.529883                     0           501.529883  [{'id': '0x21be370d5312f44cb42ce377bc9b8a0cef1...
1  1.651872e+09  Yield  0x5eb148a571b0b3d8b8eb46053545e7df833898dc     [common]             106.541151                     0           106.541151  [{'id': '0x468003b688943977e6130f4f68f23aad939...

With this other code i succeded in flattening the second (nested) level:

pd.json_normalize(first_data, ["portfolio_item_list", 'detail',"supply_token_list"], meta=[['portfolio_item_list','stats'],['portfolio_item_list','pool_id']])

                                           id chain            name symbol display_symbol optimized_symbol  decimals                                           logo_url protocol_id     price  is_verified  is_core  is_wallet       time_at        amount                          portfolio_item_list.stats                 portfolio_item_list.pool_id
0  0x21be370d5312f44cb42ce377bc9b8a0cef1a4c83   ftm  Wrapped Fantom   WFTM           None             WFTM        18  https://static.debank.com/image/ftm_token/logo...              0.721600         True     True       True  1.600243e+09    346.436940  {'asset_usd_value': 501.5298834625261, 'debt_u...  0x429590a528a86a0da0aca9aa7cd087badc790af8
1  0x6c021ae822bea943b2e66552bde1d2696a53fbb7   ftm            TOMB   TOMB           None             TOMB        18  https://static.debank.com/image/ftm_token/logo...              0.694484         True     True       True  1.622414e+09    362.198146  {'asset_usd_value': 501.5298834625261, 'debt_u...  0x429590a528a86a0da0aca9aa7cd087badc790af8
2  0x468003b688943977e6130f4f68f23aad939a1040   ftm     Spell Token  SPELL           None            SPELL        18  https://static.debank.com/image/ftm_token/logo...              0.003128         True     True       True  1.631158e+09  34060.470295  {'asset_usd_value': 106.54115108398622, 'debt_...  0x5eb148a571b0b3d8b8eb46053545e7df833898dc

Here it is my Json:

{
  "id": "ftm_beefy",
  "chain": "ftm",
  "name": "Beefy",
  "site_url": "https://app.beefy.finance",
  "logo_url": "https://static.debank.com/image/project/logo_url/ftm_beefy/98a5cc0f4596cbdeb9abe5125ae7e9e7.png",
  "has_supported_portfolio": true,
  "tvl": 242576268.49032995,
  "portfolio_item_list": [
    {
      "stats": {
        "asset_usd_value": 501.5298834625261,
        "debt_usd_value": 0,
        "net_usd_value": 501.5298834625261
      },
      "update_at": 1651872000.6128848,
      "name": "Yield",
      "pool_id": "0x429590a528a86a0da0aca9aa7cd087badc790af8",
      "detail_types": [
        "common"
      ],
      "detail": {
        "supply_token_list": [
          {
            "id": "0x21be370d5312f44cb42ce377bc9b8a0cef1a4c83",
            "chain": "ftm",
            "name": "Wrapped Fantom",
            "symbol": "WFTM",
            "display_symbol": null,
            "optimized_symbol": "WFTM",
            "decimals": 18,
            "logo_url": "https://static.debank.com/image/ftm_token/logo_url/0x21be370d5312f44cb42ce377bc9b8a0cef1a4c83/2b7d91858f9c62aafc8d7778b9c22f57.png",
            "protocol_id": "",
            "price": 0.7216,
            "is_verified": true,
            "is_core": true,
            "is_wallet": true,
            "time_at": 1600242566.0,
            "amount": 346.436940253138
          },
          {
            "id": "0x6c021ae822bea943b2e66552bde1d2696a53fbb7",
            "chain": "ftm",
            "name": "TOMB",
            "symbol": "TOMB",
            "display_symbol": null,
            "optimized_symbol": "TOMB",
            "decimals": 18,
            "logo_url": "https://static.debank.com/image/ftm_token/logo_url/0x6c021ae822bea943b2e66552bde1d2696a53fbb7/7f7b2e861030a443c5941a3db60d164e.png",
            "protocol_id": "",
            "price": 0.6944844703203712,
            "is_verified": true,
            "is_core": true,
            "is_wallet": true,
            "time_at": 1622413912.0,
            "amount": 362.19814571206155
          }
        ]
      },
      "proxy_detail": {}
    },
    {
      "stats": {
        "asset_usd_value": 106.54115108398622,
        "debt_usd_value": 0,
        "net_usd_value": 106.54115108398622
      },
      "update_at": 1651872000.7800875,
      "name": "Yield",
      "pool_id": "0x5eb148a571b0b3d8b8eb46053545e7df833898dc",
      "detail_types": [
        "common"
      ],
      "detail": {
        "supply_token_list": [
          {
            "id": "0x468003b688943977e6130f4f68f23aad939a1040",
            "chain": "ftm",
            "name": "Spell Token",
            "symbol": "SPELL",
            "display_symbol": null,
            "optimized_symbol": "SPELL",
            "decimals": 18,
            "logo_url": "https://static.debank.com/image/ftm_token/logo_url/0x468003b688943977e6130f4f68f23aad939a1040/a1b1003790d9ee1463cacba03eecc1a6.png",
            "protocol_id": "",
            "price": 0.003128,
            "is_verified": true,
            "is_core": true,
            "is_wallet": true,
            "time_at": 1631157987.0,
            "amount": 34060.47029539201
          }
        ]
      },
      "proxy_detail": {}
    }
  ],
  "protocolname": "Beefy"
}

EDIT 09/05/2022: What I need should be similar to the following code, but I need to normalize ['portfolio_item_list', 'stats'] too and i don't know hot do perform it

pd.json_normalize(first_data, ['portfolio_item_list', 'detail', 'supply_token_list'], ['id',
 'chain',
 'name',
 'site_url',
 'logo_url',
 'has_supported_portfolio',
 'tvl', ['portfolio_item_list', 'pool_id'],['portfolio_item_list', 'stats']], meta_prefix='m_')

Thanks everybody.

Solution 1:^[1]

Is this what you're trying to get?

meta = list(data.keys())
meta.remove('portfolio_item_list')
pd.json_normalize(data, ['portfolio_item_list', 'detail', 'supply_token_list'], meta, meta_prefix='m_')

                                           id chain            name symbol display_symbol optimized_symbol  decimals                                           logo_url protocol_id     price  is_verified  is_core  is_wallet       time_at        amount       m_id m_chain m_name                 m_site_url                                         m_logo_url m_has_supported_portfolio            m_tvl m_protocolname
0  0x21be370d5312f44cb42ce377bc9b8a0cef1a4c83   ftm  Wrapped Fantom   WFTM           None             WFTM        18  https://static.debank.com/image/ftm_token/logo...              0.721600         True     True       True  1.600243e+09    346.436940  ftm_beefy     ftm  Beefy  https://app.beefy.finance  https://static.debank.com/image/project/logo_u...                      True  242576268.49033          Beefy
1  0x6c021ae822bea943b2e66552bde1d2696a53fbb7   ftm            TOMB   TOMB           None             TOMB        18  https://static.debank.com/image/ftm_token/logo...              0.694484         True     True       True  1.622414e+09    362.198146  ftm_beefy     ftm  Beefy  https://app.beefy.finance  https://static.debank.com/image/project/logo_u...                      True  242576268.49033          Beefy
2  0x468003b688943977e6130f4f68f23aad939a1040   ftm     Spell Token  SPELL           None            SPELL        18  https://static.debank.com/image/ftm_token/logo...              0.003128         True     True       True  1.631158e+09  34060.470295  ftm_beefy     ftm  Beefy  https://app.beefy.finance  https://static.debank.com/image/project/logo_u...                      True  242576268.49033          Beefy

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	BeRT2me

'hard-nested json and pd.json_normalize

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]