Json object to CSV in python

Question

I am calling an API and it returns the value to me as a Json object, like below:

{
    "indicator_value": {
        "AFG": {
            "137506": {
                "2002": 0.373,
                "2003": 0.383,
                "2004": 0.398,
                "2005": 0.408,
                "2006": 0.417,
                "2007": 0.429,
                "2008": 0.437,
                "2009": 0.453,
                "2010": 0.463,
                "2011": 0.471,
                "2012": 0.482,
                "2013": 0.487,
                "2014": 0.491,
                "2015": 0.493,
                "2016": 0.494,
                "2017": 0.498
            }
        },
        "AGO": {
            "137506": {
                "1999": 0.374,
                "2000": 0.387,
                "2001": 0.401,
                "2002": 0.418,
                "2003": 0.429,
                "2004": 0.442,
                "2005": 0.455,
                "2006": 0.471,
                "2007": 0.492,
                "2008": 0.502,
                "2009": 0.522,
                "2010": 0.52,
                "2011": 0.534,
                "2012": 0.543,
                "2013": 0.554,
                "2014": 0.564,
                "2015": 0.572,
                "2016": 0.577,
                "2017": 0.581
            }
        },

The file continues like that and then after the indicator_value array there are two other arrays I am not interested in.

Can anyone advise as to how I, using python, transform this to a csv file with the format:

indicator | country | year | value
 HDI      | AFG     | 2017 | 0.498
 HDI      | AFG     | 2016 | 0.494

Answer 1

This might work, depending on what the rest of your data looks like:

import pandas as pd

rows = []
for k1, v1 in data['indicator_value'].items():
    row = ['HDI', k1]
    for k2, v2 in v1.items():
        for k3, v3 in v2.items():
            rows.append(row + [k3, v3])

df = pd.DataFrame(rows, columns=['indicator', 'country', 'year', 'value'])

where df is a pandas DataFrame and df.head() now looks like:

Now you can do df.to_csv(fname).