2019-09-11
Python - Problem extracting data from nested json
stackoverflow
Question

I have a problem extracting data from json, I tried n different ways. I was able to extract the ID itself, unfortunately I can't manage to show the details of the field.
Below is my json

{
    "params": {
        "cid": "15482782896",
        "datemax": "20190831",
        "datemin": "20190601",
        "domains": [
            "url.com"
        ],

    },
    "results": {
        "59107": {
            "url.com": {
                "1946592": {
                    "data": {
                        "2019-06-01": {
                            "ENGINE": {
                                "DEVICE": {
                                    "": {
                                        "position": 21,
                                        "url": "url3.com"
                                    }
                                }
                            }
                        },
                        "2019-07-01": {
                            "ENGINE": {
                                "DEVICE": {
                                    "": {
                                        "position": 4,
                                        "url": "url3.com"
                                    }
                                }
                            }
                        },
                        "2019-08-01": {
                            "ENGINE": {
                                "DEVICE": {
                                    "": {
                                        "position": 2,
                                        "url": "url3.com"
                                    }
                                }
                            }
                        }
                    },
                    "keyword": {
                        "title": "python_1",
                        "volume": 10
                    }
                },
                "1946602": {
                    "data": {
                        "2019-06-01": {
                            "ENGINE": {
                                "DEVICE": {
                                    "": {
                                        "position": 5,
                                        "url": "url1.com"
                                    }
                                }
                            }
                        },
                        "2019-07-01": {
                            "ENGINE": {
                                "DEVICE": {
                                    "": {
                                        "position": 12,
                                        "url": "url1.com"
                                    }
                                }
                            }
                        },
                        "2019-08-01": {
                            "ENGINE": {
                                "DEVICE": {
                                    "": {
                                        "position": 10.25,
                                        "url": "url1.com"
                                    }
                                }
                            }
                        }
                    },
                    "keyword": {
                        "title": "python_2",
                        "volume": 20
                    }
                }
            }
        }
    }
}

I tried the following code but I got the result in the form of id itself

import json
import csv

def get_leaves(item, key=None):
    if isinstance(item, dict):
        leaves = {}
        for i in item.keys():
            leaves.update(get_leaves(item[i], i))
        return leaves
    elif isinstance(item, list):
        leaves = {}
        for i in item:
            leaves.update(get_leaves(i, key))
        return leaves
    else:
        return {key : item}


with open('me_filename') as f_input:
    json_data = json.load(f_input)

fieldnames = set()

for entry in json_data:
    fieldnames.update(get_leaves(entry).keys())

with open('output.csv', 'w', newline='') as f_output:
    csv_output = csv.DictWriter(f_output, fieldnames=sorted(fieldnames))
    csv_output.writeheader()
    csv_output.writerows(get_leaves(entry) for entry in json_data)

I also tried to use the pandas but also failed to parse properly

import io
import json
import pandas as pd

with open('me_filename', encoding='utf-8') as f_input:
    df = pd.read_json(f_input , orient='None')

df.to_csv('output.csv', encoding='utf-8')

The result I'd need to get it :

ID      Name         page   volume  url      2019-06-01 2019-07-01  2019-08-01  2019-09-01
1946592 python_1    url.com 10      url3.com    21   4    2 null
1946602 python_2    url.com 20      url1.com    5   12  10,25   null

What could I do wrong?

Answer
1

Hmm this is a bit of a convoluted solution and it looks very messy and no-longer looks like the code provided however I believe it will resolve your issue.

First of all I had a problem with the provided Json (due to the trailing ',' on line 8) however have managed to generate:

Output (temp.csv)

ID,Name,Page,Volume,Url,2019-08-01,2019-07-01,2019-06-01,
1946592,python_1,url.com,10,url3.com,2,4,21,
1946602,python_2,url.com,20,url1.com,10.25,12,5,

using the following:

import json

dates: set = set()

# Collect the data 
def get_breakdown(json):
    collected_data = []
    for result in json['results']:
        for page in json['results'][result]:
            for _id in json['results'][result][page]:
                data_struct = { 
                    'ID': _id, 
                    'Name': json['results'][result][page][_id]['keyword']['title'], 
                    'Page': page, 
                    'Volume': json['results'][result][page][_id]['keyword']['volume'], 
                    'Dates': {}
                }
                for date in dates:
                    if date in json['results'][result][page][_id]['data']:
                        data_struct['URL'] = json['results'][result][page][_id]['data'][date]['ENGINE']['DEVICE']['']['url']
                        data_struct['Dates'][date] = {'Position' : json['results'][result][page][_id]['data'][date]['ENGINE']['DEVICE']['']['position']}
                    else:
                        data_struct['Dates'][date] = {'Position' : 'null'}
                collected_data.append(data_struct)
    return collected_data

# Collect all dates across the whole data 
# structure and save them to a set
def get_dates(json):
    for result in json['results']:
        for page in json['results'][result]:
            for _id in json['results'][result][page]:
                for date in json['results'][result][page][_id]['data']:
                    dates.add(date)


# Write to .csv file
def write_csv(collected_data, file_path):
    f = open(file_path, "w")
    # CSV Title
    date_string = ''
    for date in dates:
        date_string = '{0}{1},'.format(date_string, date)
    f.write('ID,Name,Page,Volume,Url,{0}\n'.format(date_string))

    # Data
    for data in collected_data:
        position_string = ''
        for date in dates:
            position_string = '{0}{1},'.format(position_string, data['Dates'][date]['Position'])
        f.write('{0},{1},{2},{3},{4},{5}\n'.format(
            data['ID'],
            data['Name'],
            data['Page'],
            data['Volume'],
            data['URL'],
            position_string
        ))

# Code Body
with open('me_filename.json') as f_input:
    json_data = json.load(f_input)
    get_dates(json_data)
    write_csv(get_breakdown(json_data), "output.csv")

Hopefully you can follow the code and it does what is expected. I am sure that it can be made much more reliable - however as previously mentioned I couldn't make it work with the base code you provided.

Python - Problem extracting data from nested json
See more ...