title: Mapping complex JSON to Pandas Dataframe
body: <p><strong>Background</strong><br>I have a complex nested JSON object, which I am trying to unpack into a pandas <code>df</code> in a very specific way.</p>
  <p><strong>JSON Object</strong><br>this is an extract, containing randomized data of the JSON object, which shows examples of the hierarchy (inc. children) for 1x family (i.e. 'Falconer Family'), however there is 100s of them in total and this extract just has 1x family, however the full JSON object has multiple -</p>
  <pre><code>{
      &quot;meta&quot;: {
          &quot;columns&quot;: [{
                  &quot;key&quot;: &quot;value&quot;,
                  &quot;display_name&quot;: &quot;Adjusted Value (No Div, USD)&quot;,
                  &quot;output_type&quot;: &quot;Number&quot;,
                  &quot;currency&quot;: &quot;USD&quot;
              },
              {
                  &quot;key&quot;: &quot;time_weighted_return&quot;,
                  &quot;display_name&quot;: &quot;Current Quarter TWR (USD)&quot;,
                  &quot;output_type&quot;: &quot;Percent&quot;,
                  &quot;currency&quot;: &quot;USD&quot;
              },
              {
                  &quot;key&quot;: &quot;time_weighted_return_2&quot;,
                  &quot;display_name&quot;: &quot;YTD TWR (USD)&quot;,
                  &quot;output_type&quot;: &quot;Percent&quot;,
                  &quot;currency&quot;: &quot;USD&quot;
              },
              {
                  &quot;key&quot;: &quot;_custom_twr_audit_note_911328&quot;,
                  &quot;display_name&quot;: &quot;TWR Audit Note&quot;,
                  &quot;output_type&quot;: &quot;Word&quot;
              }
          ],
          &quot;groupings&quot;: [{
                  &quot;key&quot;: &quot;_custom_name_747205&quot;,
                  &quot;display_name&quot;: &quot;* Reporting Client Name&quot;
              },
              {
                  &quot;key&quot;: &quot;_custom_new_entity_group_453577&quot;,
                  &quot;display_name&quot;: &quot;NEW Entity Group&quot;
              },
              {
                  &quot;key&quot;: &quot;_custom_level_2_624287&quot;,
                  &quot;display_name&quot;: &quot;* Level 2&quot;
              },
              {
                  &quot;key&quot;: &quot;legal_entity&quot;,
                  &quot;display_name&quot;: &quot;Legal Entity&quot;
              }
          ]
      },
      &quot;data&quot;: {
          &quot;type&quot;: &quot;portfolio_views&quot;,
          &quot;attributes&quot;: {
              &quot;total&quot;: {
                  &quot;name&quot;: &quot;Total&quot;,
                  &quot;columns&quot;: {
                      &quot;time_weighted_return&quot;: -0.046732301295604683,
                      &quot;time_weighted_return_2&quot;: -0.046732301295604683,
                      &quot;_custom_twr_audit_note_911328&quot;: null,
                      &quot;value&quot;: 23132492.905107163
                  },
                  &quot;children&quot;: [{
                      &quot;name&quot;: &quot;Falconer Family&quot;,
                      &quot;grouping&quot;: &quot;_custom_name_747205&quot;,
                      &quot;columns&quot;: {
                          &quot;time_weighted_return&quot;: -0.046732301295604683,
                          &quot;time_weighted_return_2&quot;: -0.046732301295604683,
                          &quot;_custom_twr_audit_note_911328&quot;: null,
                          &quot;value&quot;: 23132492.905107163
                      },
                      &quot;children&quot;: [{
                              &quot;name&quot;: &quot;Wealth Bucket A&quot;,
                              &quot;grouping&quot;: &quot;_custom_new_entity_group_453577&quot;,
                              &quot;columns&quot;: {
                                  &quot;time_weighted_return&quot;: -0.045960317420568164,
                                  &quot;time_weighted_return_2&quot;: -0.045960317420568164,
                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                  &quot;value&quot;: 13264448.506587159
                              },
                              &quot;children&quot;: [{
                                      &quot;name&quot;: &quot;Asset Class A&quot;,
                                      &quot;grouping&quot;: &quot;_custom_level_2_624287&quot;,
                                      &quot;columns&quot;: {
                                          &quot;time_weighted_return&quot;: 0.000003434094574039648,
                                          &quot;time_weighted_return_2&quot;: 0.000003434094574039648,
                                          &quot;_custom_twr_audit_note_911328&quot;: null,
                                          &quot;value&quot;: 3337.99
                                      },
                                      &quot;children&quot;: [{
                                          &quot;entity_id&quot;: 10604454,
                                          &quot;name&quot;: &quot;HUDJ Trust&quot;,
                                          &quot;grouping&quot;: &quot;legal_entity&quot;,
                                          &quot;columns&quot;: {
                                              &quot;time_weighted_return&quot;: 0.000003434094574039648,
                                              &quot;time_weighted_return_2&quot;: 0.000003434094574039648,
                                              &quot;_custom_twr_audit_note_911328&quot;: null,
                                              &quot;value&quot;: 3337.99
                                          },
                                          &quot;children&quot;: []
                                      }]
                                  },
                                  {
                                      &quot;name&quot;: &quot;Asset Class B&quot;,
                                      &quot;grouping&quot;: &quot;_custom_level_2_624287&quot;,
                                      &quot;columns&quot;: {
                                          &quot;time_weighted_return&quot;: -0.025871339096964152,
                                          &quot;time_weighted_return_2&quot;: -0.025871339096964152,
                                          &quot;_custom_twr_audit_note_911328&quot;: null,
                                          &quot;value&quot;: 1017004.7192636987
                                      },
                                      &quot;children&quot;: [{
                                          &quot;entity_id&quot;: 10604454,
                                          &quot;name&quot;: &quot;HUDG Trust&quot;,
                                          &quot;grouping&quot;: &quot;legal_entity&quot;,
                                          &quot;columns&quot;: {
                                              &quot;time_weighted_return&quot;: -0.025871339096964152,
                                              &quot;time_weighted_return_2&quot;: -0.025871339096964152,
                                              &quot;_custom_twr_audit_note_911328&quot;: null,
                                              &quot;value&quot;: 1017004.7192636987
                                          },
                                          &quot;children&quot;: []
                                      }]
                                  },
                                  {
                                      &quot;name&quot;: &quot;Asset Class C&quot;,
                                      &quot;grouping&quot;: &quot;_custom_level_2_624287&quot;,
                                      &quot;columns&quot;: {
                                          &quot;time_weighted_return&quot;: -0.030370376329670656,
                                          &quot;time_weighted_return_2&quot;: -0.030370376329670656,
                                          &quot;_custom_twr_audit_note_911328&quot;: null,
                                          &quot;value&quot;: 231142.67772000004
                                      },
                                      &quot;children&quot;: [{
                                          &quot;entity_id&quot;: 10604454,
                                          &quot;name&quot;: &quot;HKDJ Trust&quot;,
                                          &quot;grouping&quot;: &quot;legal_entity&quot;,
                                          &quot;columns&quot;: {
                                              &quot;time_weighted_return&quot;: -0.030370376329670656,
                                              &quot;time_weighted_return_2&quot;: -0.030370376329670656,
                                              &quot;_custom_twr_audit_note_911328&quot;: null,
                                              &quot;value&quot;: 231142.67772000004
                                          },
                                          &quot;children&quot;: []
                                      }]
                                  },
                                  {
                                      &quot;name&quot;: &quot;Asset Class D&quot;,
                                      &quot;grouping&quot;: &quot;_custom_level_2_624287&quot;,
                                      &quot;columns&quot;: {
                                          &quot;time_weighted_return&quot;: -0.05382756475465478,
                                          &quot;time_weighted_return_2&quot;: -0.05382756475465478,
                                          &quot;_custom_twr_audit_note_911328&quot;: null,
                                          &quot;value&quot;: 9791282.570000006
                                      },
                                      &quot;children&quot;: [{
                                          &quot;entity_id&quot;: 10604454,
                                          &quot;name&quot;: &quot;HUDW Trust&quot;,
                                          &quot;grouping&quot;: &quot;legal_entity&quot;,
                                          &quot;columns&quot;: {
                                              &quot;time_weighted_return&quot;: -0.05382756475465478,
                                              &quot;time_weighted_return_2&quot;: -0.05382756475465478,
                                              &quot;_custom_twr_audit_note_911328&quot;: null,
                                              &quot;value&quot;: 9791282.570000006
                                          },
                                          &quot;children&quot;: []
                                      }]
                                  },
                                  {
                                      &quot;name&quot;: &quot;Asset Class E&quot;,
                                      &quot;grouping&quot;: &quot;_custom_level_2_624287&quot;,
                                      &quot;columns&quot;: {
                                          &quot;time_weighted_return&quot;: -0.01351630404081805,
                                          &quot;time_weighted_return_2&quot;: -0.01351630404081805,
                                          &quot;_custom_twr_audit_note_911328&quot;: null,
                                          &quot;value&quot;: 2153366.6396034593
                                      },
                                      &quot;children&quot;: [{
                                          &quot;entity_id&quot;: 10604454,
                                          &quot;name&quot;: &quot;HJDJ Trust&quot;,
                                          &quot;grouping&quot;: &quot;legal_entity&quot;,
                                          &quot;columns&quot;: {
                                              &quot;time_weighted_return&quot;: -0.01351630404081805,
                                              &quot;time_weighted_return_2&quot;: -0.01351630404081805,
                                              &quot;_custom_twr_audit_note_911328&quot;: null,
                                              &quot;value&quot;: 2153366.6396034593
                                          },
                                          &quot;children&quot;: []
                                      }]
                                  },
                                  {
                                      &quot;name&quot;: &quot;Asset Class F&quot;,
                                      &quot;grouping&quot;: &quot;_custom_level_2_624287&quot;,
                                      &quot;columns&quot;: {
                                          &quot;time_weighted_return&quot;: -0.002298190175237247,
                                          &quot;time_weighted_return_2&quot;: -0.002298190175237247,
                                          &quot;_custom_twr_audit_note_911328&quot;: null,
                                          &quot;value&quot;: 68313.90999999999
                                      },
                                      &quot;children&quot;: [{
                                          &quot;entity_id&quot;: 10604454,
                                          &quot;name&quot;: &quot;HADJ Trust&quot;,
                                          &quot;grouping&quot;: &quot;legal_entity&quot;,
                                          &quot;columns&quot;: {
                                              &quot;time_weighted_return&quot;: -0.002298190175237247,
                                              &quot;time_weighted_return_2&quot;: -0.002298190175237247,
                                              &quot;_custom_twr_audit_note_911328&quot;: null,
                                              &quot;value&quot;: 68313.90999999999
                                          },
                                          &quot;children&quot;: []
                                      }]
                                  }
                              ]
                          },
                          {
                              &quot;name&quot;: &quot;Wealth Bucket B&quot;,
                              &quot;grouping&quot;: &quot;_custom_new_entity_group_453577&quot;,
                              &quot;columns&quot;: {
                                  &quot;time_weighted_return&quot;: -0.04769870075659244,
                                  &quot;time_weighted_return_2&quot;: -0.04769870075659244,
                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                  &quot;value&quot;: 9868044.398519998
                              },
                              &quot;children&quot;: [{
                                      &quot;name&quot;: &quot;Asset Class A&quot;,
                                      &quot;grouping&quot;: &quot;_custom_level_2_624287&quot;,
                                      &quot;columns&quot;: {
                                          &quot;time_weighted_return&quot;: 0.000028632718065191298,
                                          &quot;time_weighted_return_2&quot;: 0.000028632718065191298,
                                          &quot;_custom_twr_audit_note_911328&quot;: null,
                                          &quot;value&quot;: 10234.94
                                      },
                                      &quot;children&quot;: [{
                                              &quot;entity_id&quot;: 10868778,
                                              &quot;name&quot;: &quot;2012 Desc Tr HBO Thalia&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: 0.0000282679297198829,
                                                  &quot;time_weighted_return_2&quot;: 0.0000282679297198829,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 244.28
                                              },
                                              &quot;children&quot;: []
                                          },
                                          {
                                              &quot;entity_id&quot;: 10643052,
                                              &quot;name&quot;: &quot;2013 Irrev Tr HBO Thalia&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: 0.000049373572795108345,
                                                  &quot;time_weighted_return_2&quot;: 0.000049373572795108345,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 5081.08
                                              },
                                              &quot;children&quot;: []
                                          },
                                          {
                                              &quot;entity_id&quot;: 10598341,
                                              &quot;name&quot;: &quot;Cht 11th Tr HBO Shirley&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: 0.000006609603754315074,
                                                  &quot;time_weighted_return_2&quot;: 0.000006609603754315074,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 1523.62
                                              },
                                              &quot;children&quot;: []
                                          },
                                          {
                                              &quot;entity_id&quot;: 10598337,
                                              &quot;name&quot;: &quot;Cht 11th Tr HBO Hannah&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: 0.000010999769004760296,
                                                  &quot;time_weighted_return_2&quot;: 0.000010999769004760296,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 1828.9
                                              },
                                              &quot;children&quot;: []
                                          },
                                          {
                                              &quot;entity_id&quot;: 10598334,
                                              &quot;name&quot;: &quot;Cht 11th Tr HBO Lau&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: 0.000006466673995619843,
                                                  &quot;time_weighted_return_2&quot;: 0.000006466673995619843,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 1557.06
                                              },
                                              &quot;children&quot;: []
                                          }
                                      ]
                                  },
                                  {
                                      &quot;name&quot;: &quot;Asset Class B&quot;,
                                      &quot;grouping&quot;: &quot;_custom_level_2_624287&quot;,
                                      &quot;columns&quot;: {
                                          &quot;time_weighted_return&quot;: -0.024645947842438676,
                                          &quot;time_weighted_return_2&quot;: -0.024645947842438676,
                                          &quot;_custom_twr_audit_note_911328&quot;: null,
                                          &quot;value&quot;: 674052.31962
                                      },
                                      &quot;children&quot;: [{
                                              &quot;entity_id&quot;: 10868778,
                                              &quot;name&quot;: &quot;2012 Desc Tr HBO Thalia&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: -0.043304004172576405,
                                                  &quot;time_weighted_return_2&quot;: -0.043304004172576405,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 52800.96
                                              },
                                              &quot;children&quot;: []
                                          },
                                          {
                                              &quot;entity_id&quot;: 10643052,
                                              &quot;name&quot;: &quot;2013 Irrev Tr HBO Thalia&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: -0.022408434778798836,
                                                  &quot;time_weighted_return_2&quot;: -0.022408434778798836,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 599594.11962
                                              },
                                              &quot;children&quot;: []
                                          },
                                          {
                                              &quot;entity_id&quot;: 10598341,
                                              &quot;name&quot;: &quot;Cht 11th Tr HBO Shirley&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: -0.039799855483646174,
                                                  &quot;time_weighted_return_2&quot;: -0.039799855483646174,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 7219.08
                                              },
                                              &quot;children&quot;: []
                                          },
                                          {
                                              &quot;entity_id&quot;: 10598337,
                                              &quot;name&quot;: &quot;Cht 11th Tr HBO Hannah&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: -0.039799855483646174,
                                                  &quot;time_weighted_return_2&quot;: -0.039799855483646174,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 7219.08
                                              },
                                              &quot;children&quot;: []
                                          },
                                          {
                                              &quot;entity_id&quot;: 10598334,
                                              &quot;name&quot;: &quot;Cht 11th Tr HBO Lau&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: -0.039799855483646174,
                                                  &quot;time_weighted_return_2&quot;: -0.039799855483646174,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 7219.08
                                              },
                                              &quot;children&quot;: []
                                          }
                                      ]
                                  },
                                  {
                                      &quot;name&quot;: &quot;Asset Class C&quot;,
                                      &quot;grouping&quot;: &quot;_custom_level_2_624287&quot;,
                                      &quot;columns&quot;: {
                                          &quot;time_weighted_return&quot;: -0.03037038746301135,
                                          &quot;time_weighted_return_2&quot;: -0.03037038746301135,
                                          &quot;_custom_twr_audit_note_911328&quot;: null,
                                          &quot;value&quot;: 114472.69744
                                      },
                                      &quot;children&quot;: [{
                                              &quot;entity_id&quot;: 10868778,
                                              &quot;name&quot;: &quot;2012 Desc Tr HBO Thalia&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: -0.030370390035505124,
                                                  &quot;time_weighted_return_2&quot;: -0.030370390035505124,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 114472.68744000001
                                              },
                                              &quot;children&quot;: []
                                          },
                                          {
                                              &quot;entity_id&quot;: 10643052,
                                              &quot;name&quot;: &quot;2013 Irrev Tr HBO Thalia&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: 0,
                                                  &quot;time_weighted_return_2&quot;: 0,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 0.01
                                              },
                                              &quot;children&quot;: []
                                          }
                                      ]
                                  },
                                  {
                                      &quot;name&quot;: &quot;Asset Class D&quot;,
                                      &quot;grouping&quot;: &quot;_custom_level_2_624287&quot;,
                                      &quot;columns&quot;: {
                                          &quot;time_weighted_return&quot;: -0.06604362523792162,
                                          &quot;time_weighted_return_2&quot;: -0.06604362523792162,
                                          &quot;_custom_twr_audit_note_911328&quot;: null,
                                          &quot;value&quot;: 5722529.229999997
                                      },
                                      &quot;children&quot;: [{
                                              &quot;entity_id&quot;: 10868778,
                                              &quot;name&quot;: &quot;2012 Desc Tr HBO Thalia&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: -0.06154960593668424,
                                                  &quot;time_weighted_return_2&quot;: -0.06154960593668424,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 1191838.9399999995
                                              },
                                              &quot;children&quot;: []
                                          },
                                          {
                                              &quot;entity_id&quot;: 10643052,
                                              &quot;name&quot;: &quot;2013 Irrev Tr HBO Thalia&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: -0.06750460387418267,
                                                  &quot;time_weighted_return_2&quot;: -0.06750460387418267,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 4416618.520000002
                                              },
                                              &quot;children&quot;: []
                                          },
                                          {
                                              &quot;entity_id&quot;: 10598341,
                                              &quot;name&quot;: &quot;Cht 11th Tr HBO Shirley&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: -0.05604507809250081,
                                                  &quot;time_weighted_return_2&quot;: -0.05604507809250081,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 38190.33
                                              },
                                              &quot;children&quot;: []
                                          },
                                          {
                                              &quot;entity_id&quot;: 10598337,
                                              &quot;name&quot;: &quot;Cht 11th Tr HBO Hannah&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: -0.05604507809250081,
                                                  &quot;time_weighted_return_2&quot;: -0.05604507809250081,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 37940.72
                                              },
                                              &quot;children&quot;: []
                                          },
                                          {
                                              &quot;entity_id&quot;: 10598334,
                                              &quot;name&quot;: &quot;Cht 11th Tr HBO Lau&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: -0.05604507809250081,
                                                  &quot;time_weighted_return_2&quot;: -0.05604507809250081,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 37940.72
                                              },
                                              &quot;children&quot;: []
                                          }
                                      ]
                                  },
                                  {
                                      &quot;name&quot;: &quot;Asset Class E&quot;,
                                      &quot;grouping&quot;: &quot;_custom_level_2_624287&quot;,
                                      &quot;columns&quot;: {
                                          &quot;time_weighted_return&quot;: -0.017118805423322003,
                                          &quot;time_weighted_return_2&quot;: -0.017118805423322003,
                                          &quot;_custom_twr_audit_note_911328&quot;: null,
                                          &quot;value&quot;: 3148495.0914600003
                                      },
                                      &quot;children&quot;: [{
                                              &quot;entity_id&quot;: 10868778,
                                              &quot;name&quot;: &quot;2012 Desc Tr HBO Thalia&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: -0.015251157805867277,
                                                  &quot;time_weighted_return_2&quot;: -0.015251157805867277,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 800493.06146
                                              },
                                              &quot;children&quot;: []
                                          },
                                          {
                                              &quot;entity_id&quot;: 10643052,
                                              &quot;name&quot;: &quot;2013 Irrev Tr HBO Thalia&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: -0.01739609576880241,
                                                  &quot;time_weighted_return_2&quot;: -0.01739609576880241,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 2215511.2700000005
                                              },
                                              &quot;children&quot;: []
                                          },
                                          {
                                              &quot;entity_id&quot;: 10598341,
                                              &quot;name&quot;: &quot;Cht 11th Tr HBO Shirley&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: -0.02085132265594647,
                                                  &quot;time_weighted_return_2&quot;: -0.02085132265594647,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 44031.21
                                              },
                                              &quot;children&quot;: []
                                          },
                                          {
                                              &quot;entity_id&quot;: 10598337,
                                              &quot;name&quot;: &quot;Cht 11th Tr HBO Hannah&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: -0.02089393244695803,
                                                  &quot;time_weighted_return_2&quot;: -0.02089393244695803,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 44394.159999999996
                                              },
                                              &quot;children&quot;: []
                                          },
                                          {
                                              &quot;entity_id&quot;: 10598334,
                                              &quot;name&quot;: &quot;Cht 11th Tr HBO Lau&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: -0.020607507059866248,
                                                  &quot;time_weighted_return_2&quot;: -0.020607507059866248,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 44065.39000000001
                                              },
                                              &quot;children&quot;: []
                                          }
                                      ]
                                  },
                                  {
                                      &quot;name&quot;: &quot;Asset Class F&quot;,
                                      &quot;grouping&quot;: &quot;_custom_level_2_624287&quot;,
                                      &quot;columns&quot;: {
                                          &quot;time_weighted_return&quot;: -0.0014710489231547497,
                                          &quot;time_weighted_return_2&quot;: -0.0014710489231547497,
                                          &quot;_custom_twr_audit_note_911328&quot;: null,
                                          &quot;value&quot;: 198260.12
                                      },
                                      &quot;children&quot;: [{
                                              &quot;entity_id&quot;: 10868778,
                                              &quot;name&quot;: &quot;2012 Desc Tr HBO Thalia&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: -0.0014477244560456848,
                                                  &quot;time_weighted_return_2&quot;: -0.0014477244560456848,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 44612.33
                                              },
                                              &quot;children&quot;: []
                                          },
                                          {
                                              &quot;entity_id&quot;: 10643052,
                                              &quot;name&quot;: &quot;2013 Irrev Tr HBO Thalia&quot;,
                                              &quot;grouping&quot;: &quot;legal_entity&quot;,
                                              &quot;columns&quot;: {
                                                  &quot;time_weighted_return&quot;: -0.001477821083437858,
                                                  &quot;time_weighted_return_2&quot;: -0.001477821083437858,
                                                  &quot;_custom_twr_audit_note_911328&quot;: null,
                                                  &quot;value&quot;: 153647.78999999998
                                              },
                                              &quot;children&quot;: []
                                          }
                                      ]
                                  }
                              ]
                          }
                      ]
                  }]
              }
          },
          &quot;included&quot;: []
      }
  }
  </code></pre>
  <p><strong>Notes on JSON Object extract</strong><br></p>
  <ol>
  <li><code>data</code> - data in here can be ignored, these are aggregated values for underlying children.</li>
  <li><code>meta</code> - <code>columns</code> – contains the column header values I want to use for each applicable <code>children</code> ‘column` key:pair values.</li>
  <li><code>groupings</code> - can be ignored.</li>
  <li><code>children</code> hierarchy – there are 4x levels of <code>children</code> which can be identified by their <code>name</code> as follows –
  <ul>
  <li>Family <code>name</code> (i.e., ‘Falconer Family’)</li>
  <li>Wealth Bucket <code>name</code> (e.g., ‘Wealth Bucket A’)</li>
  <li>Asset Class <code>name</code> (e.g., ‘Asset Class A’)</li>
  <li>Fund <code>name</code> (e.g., ‘HUDJ Trust’)</li>
  </ul>
  </li>
  </ol>
  <p><strong>Target Output</strong><br>this is an extract of target <code>df</code> structure I am trying to achieve -</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th><strong>portfolio</strong></th>
  <th><strong>name</strong></th>
  <th><strong>entity_id</strong></th>
  <th><strong>Adjusted Value (No Div, USD)</strong></th>
  <th><strong>Current Quarter TWR (USD)</strong></th>
  <th><strong>YTD TWR (USD)</strong></th>
  <th><strong>TWR Audit Note</strong></th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Falconer Family</td>
  <td>Falconer Family</td>
  <td></td>
  <td>23132492.90510712</td>
  <td>-0.046732301295604683</td>
  <td>-0.046732301295604683</td>
  <td>None</td>
  </tr>
  <tr>
  <td>Falconer Family</td>
  <td>Wealth Bucket A</td>
  <td></td>
  <td>13264448.506587146</td>
  <td>-0.045960317420568164</td>
  <td>-0.045960317420568164</td>
  <td>None</td>
  </tr>
  <tr>
  <td>Falconer Family</td>
  <td>Asset Class A</td>
  <td></td>
  <td>3337.99</td>
  <td>0.000003434094574039648</td>
  <td>0.000003434094574039648</td>
  <td>None</td>
  </tr>
  <tr>
  <td>Falconer Family</td>
  <td>HUDJ Trust</td>
  <td>10604454</td>
  <td>3337.99</td>
  <td>0.000003434094574039648</td>
  <td>0.000003434094574039648</td>
  <td>None</td>
  </tr>
  <tr>
  <td>Falconer Family</td>
  <td>Asset Class B</td>
  <td></td>
  <td>1017004.7192636987</td>
  <td>-0.025871339096964152</td>
  <td>-0.025871339096964152</td>
  <td>None</td>
  </tr>
  <tr>
  <td>Falconer Family</td>
  <td>HUDG Trust</td>
  <td>10604454</td>
  <td>1017004.7192636987</td>
  <td>-0.025871339096964152</td>
  <td>-0.025871339096964152</td>
  <td>None</td>
  </tr>
  <tr>
  <td>Falconer Family</td>
  <td>Asset Class C</td>
  <td></td>
  <td>231142.67772000004</td>
  <td>-0.030370376329670656</td>
  <td>-0.030370376329670656</td>
  <td>None</td>
  </tr>
  <tr>
  <td>Falconer Family</td>
  <td>HKDJ Trust</td>
  <td>10604454</td>
  <td>231142.67772000004</td>
  <td>-0.030370376329670656</td>
  <td>-0.030370376329670656</td>
  <td>None</td>
  </tr>
  <tr>
  <td>Falconer Family</td>
  <td>Asset Class D</td>
  <td></td>
  <td>9791282.570000006</td>
  <td>-0.05382756475465478</td>
  <td>-0.05382756475465478</td>
  <td>None</td>
  </tr>
  <tr>
  <td>Falconer Family</td>
  <td>HUDW Trust</td>
  <td>10604454</td>
  <td>9791282.570000006</td>
  <td>-0.05382756475465478</td>
  <td>-0.05382756475465478</td>
  <td>None</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>Notes on Target Output</strong><br></p>
  <ol>
  <li>Portfolio header – for every row, I would like to map the top-level <code>children</code> <code>name</code> value [family name]. E.g., ‘Falconer Family.</li>
  <li>Name header – this should simply be the <code>name</code> value from each respective <code>children</code>.</li>
  <li>Entity ID – all 4th level <code>children</code> <code>entity_id</code> value should be mapped to this column.</li>
  <li>Data columns – regardless of level, all <code>children</code> have identical <code>time_weighted_return</code>, <code>time-weighted_return2</code> and <code>value</code> columns which should be mapped respectively.</li>
  <li>TWR Audit Note – these <code>children</code> <code>_custom_twr_audit_note_911318</code> values are currently blank, but will be utilized in the future.</li>
  </ol>
  <p><strong>Current Output</strong><br>My main issue is that you can see that I have only been able to tap into the 1st [Family] and 2nd [Wealth Bucket] <code>children</code> level. This leaves me missing the 3rd [Asset Class] and 4th [Fund] -</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th></th>
  <th><strong>portfolio</strong></th>
  <th><strong>name</strong></th>
  <th><strong>Adjusted Value (No Div, USD)</strong></th>
  <th><strong>Current Quarter TWR (USD)</strong></th>
  <th><strong>YTD TWR (USD)</strong></th>
  <th><strong>TWR Audit Note)</strong></th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>0</td>
  <td>Falconer Family</td>
  <td>Falconer Family</td>
  <td>2.313249e+07</td>
  <td>-0.046732</td>
  <td>-0.046732</td>
  <td>None</td>
  </tr>
  <tr>
  <td>1</td>
  <td>Falconer Family</td>
  <td>Wealth Bucket A</td>
  <td>1.326445e+07</td>
  <td>-0.045960</td>
  <td>-0.045960</td>
  <td>None</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Falconer Family</td>
  <td>Wealth Bucket B</td>
  <td>9.868044e+06</td>
  <td>-0.047699</td>
  <td>-0.047699</td>
  <td>None</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>Current code</strong><br>This is a function which gets me the correct <code>df</code> formatting, however my main issue is that I haven't been able to find a solution to returning all children, but rather only the top-level -</p>
  <pre><code># Function to read API response / JSON Object
  def response_writer():
      with open('api_response_2022-02-13.json') as f:
          api_response = json.load(f)
          return api_response
  
  # Function to unpack JSON response into pandas dataframe.
  def unpack_response():
      while True:
          try:
              api_response = response_writer()
              portfolio_views_children = api_response['data']['attributes']['total']['children']
              portfolios = []
              for portfolio in portfolio_views_children:
                  entity_columns = []
                  # include portfolio itself within an iterable so the total is the header
                  for entity in itertools.chain([portfolio], portfolio[&quot;children&quot;]):
                      entity_data = entity[&quot;columns&quot;].copy()  # don't mutate original response
                      entity_data[&quot;portfolio&quot;] = portfolio[&quot;name&quot;]   # from outer
                      entity_data[&quot;name&quot;]      = entity[&quot;name&quot;]
                      entity_columns.append(entity_data)
  
                  df = pd.DataFrame(entity_columns)
                  portfolios.append(df)
  
              # combine dataframes
              df = pd.concat(portfolios)
              # reorder and rename
              column_ordering = {&quot;portfolio&quot;: &quot;portfolio&quot;, &quot;name&quot;: &quot;name&quot;}
              column_ordering.update({c[&quot;key&quot;]: c[&quot;display_name&quot;] for c in api_response[&quot;meta&quot;][&quot;columns&quot;]})
              df = df[column_ordering.keys()]   # beware: un-named cols will be dropped
              df = df.rename(columns=column_ordering)
              break
          except KeyError:
              print(&quot;-----------------------------------\n&quot;,&quot;API TIMEOUT ERROR: TRY AGAIN...&quot;, &quot;\n-----------------------------------\n&quot;)
      return df
  unpack_response()
  </code></pre>
  <p><strong>Help</strong><br>In short, I am looking for some advice on how I can tap into the remaining <code>children</code> by enhancing the existing code. Whilst I have taken much time to fully explain my problem, please ask if anything isn't clear. Please note that the JSON may have multiple families, so the solution / advice offered must observe this</p>