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
– Peter Leimbigler, Commented Sep 26, 2018 at 17:21

David · Accepted Answer · 2018-09-26 18:38:27Z

1

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

edited Sep 26, 2018 at 18:38

answered Sep 26, 2018 at 17:27

David

2201 silver badge11 bronze badges

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

2 Comments

clarkus978 Over a year ago

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).

David Over a year ago

You're right. I didn't generate a new key. Let me edit it.

Collectives™ on Stack Overflow

Flatten Nested Dict from pandas Dataframe

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related