Flatten Nested Dict from pandas Dataframe

Question

I have a for loop that is looping over a list for completing an API call. With each loop I have the json response run through pandas and append to REPL_ID:

import requests
import json
import pandas as pd
from pandas.io.json import json_normalize

REPL_ID = []

REPL = ["node01%4000-68D6FB-65377D-4804B8-A7F390%5B1-1-2D%5D",
        "node02%4000-B700F9-869677-4991B3-79CBE2%5B1-1-2E%5D",
        "node03%4000-94CF47-90E188-48728F-0538D8%5B1-1-19%5D"
       ]

def get_id():
    for value in REPL:
        url = func_repl(value)
        r = requests.get(url, headers=HEADERS, verify=False)

        jsonstring = json.dumps(r.json()["replication"])
        load = json.loads(jsonstring)
        df = json_normalize(load)
        df['NodeId'] = pd.Series(df.itemNodeId)
        df['ID'] = pd.Series(df.id).str.replace("{", "").str.replace("}", "")
        col = ['NodeId', 'ID']
        df1 = pd.DataFrame(df, columns=col)
        x = df1.to_dict('index')
        REPL_ID.append(x)
    return

After it completes, I have it print the contents of REPL_ID.

Output from REPL_ID:

[{
  0: {'NodeId': 'node01@00-68D6FB-65377D-4804B8-A7F390[1-1-2D]',
      'ID': '006bdade-49a8-4875-93de-54ba356403c4'},
  ...
  20: {'NodeId': 'node01@00-68D6FB-65377D-4804B8-A7F390[1-1-2D]',
      'ID': 'f8613a7d-30b1-4407-82f0-b92c82c6c422'}
  }, {
  0: {'NodeId': 'node03@00-94CF47-90E188-48728F-0538D8[1-1-19]',
      'ID': '065999f6-a3fe-4b1d-af35-7556efcc530e'},
  ...
  27: {'NodeId': 'node03@00-94CF47-90E188-48728F-0538D8[1-1-19]',
      'ID': 'cf96419c-a188-4b1a-85d6-bde41dbbe3ef'}
}]

Desired Output:

[{
  0: {'NodeId': 'node01@00-68D6FB-65377D-4804B8-A7F390[1-1-2D]',
      'ID': '006bdade-49a8-4875-93de-54ba356403c4'}
  ...
  78: {'NodeId': 'node03@00-94CF47-90E188-48728F-0538D8[1-1-19]',
      'ID': 'cf96419c-a188-4b1a-85d6-bde41dbbe3ef'}
}]

I'm having trouble flattening and reindexing this list. How can this be done?

I'm only collecting this information for completing the next API call which requires both NodeId and ID.

What are the differences between your current and desired outputs? It looks like a nested dict to me, but missing commas between items. — Peter Leimbigler, Sep 26 '18 at 17:21

David · Accepted Answer · 2018-09-26T18:38:27.797

It's hard to implement directly in your code but you can just take an extra step with your output. Simply loop over your dic of dics {{},{},...}, take each dic and add each entry (key/values) to a new dic leading to one unnested dic.

original_output = [{
  1: {'NodeId': 'node01@00-68D6FB-65377D-4804B8-A7F390[1-1-2D]',
      'ID': '006bdade-49a8-4875-93de-54ba356403c4'},
  2: {'NodeId': 'node01@00-68D6FB-65377D-4804B8-A7F390[1-1-2D]',
      'ID': 'f8613a7d-30b1-4407-82f0-b92c82c6c422'}
  }, {
  5: {'NodeId': 'node03@00-94CF47-90E188-48728F-0538D8[1-1-19]',
      'ID': '065999f6-a3fe-4b1d-af35-7556efcc530e'},
  6: {'NodeId': 'node03@00-94CF47-90E188-48728F-0538D8[1-1-19]',
      'ID': 'cf96419c-a188-4b1a-85d6-bde41dbbe3ef'}
}]


new_dict = {}
c = 0 
for dic in original_output:
    for key in dic.keys():
        new_dict[c] = dic[key]
        c += 1
new_dict

Out:
{0: {'NodeId': 'node01@00-68D6FB-65377D-4804B8-A7F390[1-1-2D]',
  'ID': '006bdade-49a8-4875-93de-54ba356403c4'},
 1: {'NodeId': 'node01@00-68D6FB-65377D-4804B8-A7F390[1-1-2D]',
  'ID': 'f8613a7d-30b1-4407-82f0-b92c82c6c422'},
 2: {'NodeId': 'node03@00-94CF47-90E188-48728F-0538D8[1-1-19]',
  'ID': '065999f6-a3fe-4b1d-af35-7556efcc530e'},
 3: {'NodeId': 'node03@00-94CF47-90E188-48728F-0538D8[1-1-19]',
  'ID': 'cf96419c-a188-4b1a-85d6-bde41dbbe3ef'}}

EDIT: added the 'reindexing' via the c variable

Thank you for the answer. I tested your code and it looks like it merges the output based on the index value. For example, the output of each node index starts at 0 and goes to 20 (node01), 31 (node02), and 27 (node03). In the returned output it gave me only node03 (0 through 27) then node02 (28 through 31). — clarkus978, Sep 26 '18 at 18:29

Flatten Nested Dict from pandas Dataframe

1 Answers1

Linked