Python nested dict. return deepest dict to csv

Issue

I’m trying to return some values from a nested dict (based on a json) to a csv without success due to the following structure.

{
  "http_method":"GET",
  "results":{
    "FTKMOB21xxxxD":{
      "serial_number":"FTKMOB21xxxxD",
      "comments":"",
      "q_type":432,
      "license":"EFTM123123123",
      "type":"mobile",
      "user":"pippo",
      "user_type":"user",
      "drift":0,
      "status":{
        "name":"activated"
      }
    },
    "FTKMOB21xxxxF":{
      "serial_number":"FTKMOB21xxxxF",
      "comments":"",
      "q_type":432,
      "license":"EFTM123123123",
      "type":"mobile",
      "drift":0,
      "status":{
        "name":"pending"
      }
    }
  },
  "vdom":"root",
  "path":"user",
  "name":"fortitoken",
  "action":"",
  "status":"success",
  "serial":"FGT_VM",
  "version":"v7.0.5",
  "build":304
}

What I need to return in a csv are fields "serial_number", "user", "status".
The FTKMOB21xxxxD change for each device and I need to consider it as a dynamic value, I suppose that a loop based on its position is needed.

Could you please help me to understood how to do that?

Solution

It’s straight-forward with pandas:

import pandas as pd

df = pd.DataFrame(input_dict['results'])
df.T[["serial_number", "user", "status"]].to_csv('output.csv', index=False)

Your csv will then look like:

serial_number,user,status
FTKMOB21xxxxD,pippo,{'name': 'activated'}
FTKMOB21xxxxF,,{'name': 'pending'}
Edit: if you actually want status/name as status, you have to reassign df['status']:
df = pd.DataFrame.from_dict(input_dict['results'], orient='index', columns=["serial_number", "user", "status"])
df['status'] = pd.DataFrame(df['status'].to_list())['name'].to_list()
df.to_csv('output.csv', index=False)

Answered By – Tranbi

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published