python - Group By and Count occurences of values in list of nested dicts

Question

I have a JSON file that looks structurally like this:

{
  "content": [
    {
      "name": "New York",
      "id": "1234",
      "Tags": {
        "hierarchy": "CITY"
      }
    },
    {
      "name": "Los Angeles",
      "id": "1234",
      "Tags": {
        "hierarchy": "CITY"
      }
    },
    {
      "name": "California",
      "id": "1234",
      "Tags": {
        "hierarchy": "STATE"
      }
    }
  ]
}

And as an outcome I would like a table view in CSV like so:

tag.key	tag.value	occurrance
hierarchy	CITY	2
hierarchy	STATE	1

Answer 1

Firstly construct a dictionary object by using ast.literal_eval function, and then split this object to get a key, value tuples in order to create a dataframe by using zip. Apply groupby to newly formed dataframe, and finally create a .csv file through use of df_agg.to_csv such as

import json
import ast
import pandas as pd    
Js= """{
  "content": [
    {
      "name": "New York",
      "id": "1234",
      "Tags": {
        "hierarchy": "CITY"
      }
    },
    ....
    ....
    {
      "name": "California",
      "id": "1234",
      "Tags": {
        "hierarchy": "STATE"
      }
    }
  ]
}"""
data = ast.literal_eval(Js)     
key = []
value=[]
for i in list(range(0,len(data['content']))):
    value.append(data['content'][i]['Tags']['hierarchy'])
    for j in data['content'][i]['Tags']:
        key.append(j)

df = pd.DataFrame(list(zip(key, value)), columns =['tag.key', 'tag.value'])
df_agg=df.groupby(['tag.key', 'tag.value']).size().reset_index(name='occurrance')
df_agg.to_csv(r'ThePath\to\your\file\result.csv',index = False)