Adding labels and fields to a nested JSON I have a dataframe:



Name_ID | URL                    | Count | Rating
------------------------------------------------
ABC     | www.example.com/ABC    | 10    | 5
123     | www.example.com/123    | 9     | 4
XYZ     | www.example.com/XYZ    | 5     | 2
ABC111  | www.example.com/ABC111 | 5     | 2
ABC121  | www.example.com/ABC121 | 5     | 2
222     | www.example.com/222    | 5     | 3
abc222  | www.example.com/abc222 | 4     | 2
ABCaaa  | www.example.com/ABCaaa | 4     | 2


I am trying to create a JSON:



{"name": "sampledata",
 "children": [
{
    "name":9,
    "children":[
        {"name":4,
        "children":[
            {"name":"123","size":100}
                    ]
        }
                ]
},
{
    "name":10,
    "children":[
        {"name":5,
        "children":[
            {"name":"ABC","size":100}
                    ]
        }
                ]
},
{
    "name":4,
    "children":[
        {"name":2,
        "children":[
            {"name":"abc222","size":50},
            {"name":"ABCaaa","size":50}
                    ]
        }
                ]
},
{
    "name":5,
    "children":[
        {"name":2,
        "children":[
            {"name":"ABC","size":16},
            {"name":"ABC111","size":16},
            {"name":"ABC121","size":16}
                    ]
        },
        {"name":3,
        "children":[
            {"name":"222","size":50}
                    ]
        }
                ]
}
]
}


In order to do that:


I am trying to add labels such as "name" and "children" to the JSON while creating it. 

I tried something like results = [{"name": i, "children": j} for i,j in results.items()]. But it won't label it properly, I believe.
Add another field with the label "size" which I am planning to calculate based on the formula
(Rating*Count*10000)/number_of_children_to_the_immediate_parent.




import pandas as pd
from collections import defaultdict
import json

data =[('ABC', 'www.example.com/ABC', 10   , 5), ('123', 'www.example.com/123', 9, 4), ('XYZ', 'www.example.com/XYZ', 5, 2), ('ABC111', 'www.example.com/ABC111', 5, 2), ('ABC121', 'www.example.com/ABC121', 5, 2), ('222', 'www.example.com/222', 5, 3), ('abc222', 'www.example.com/abc222', 4, 2), ('ABCaaa', 'www.example.com/ABCaaa', 4, 2)]

df = pd.DataFrame(data, columns=['Name', 'URL', 'Count', 'Rating'])

gp = df.groupby(['Count'])

dict_json = {"name": "flare"}
children = []

for name, group in gp:
    temp = {}
    temp["name"] = name
    temp["children"] = []

    rgp = group.groupby(['Rating'])
    for n, g in rgp:
        temp2 = {}
        temp2["name"] = n
        temp2["children"] = g.reset_index().T.to_dict().values()
        for t in temp2["children"]:
            t["size"] = (t["Rating"] * t["Count"] * 10000) / len(temp2["children"])
            t["name"] = t["Name"]
            del t["Count"]
            del t["Rating"]
            del t["URL"]
            del t["Name"]
            del t["index"]
        temp["children"].append(temp2)
    children.append(temp)

dict_json["children"] = children

print json.dumps(dict_json, indent=4)


Though the code does print what I need, I am looking for a more efficient and cleaner way to do the same, mainly because the actual dataset might be even more nested and complicated.
The rgp loop can be made more compact, and a bit faster, with:

def foo2(rgp):
    alist = []
    for n, g in rgp:
        temp2 = {"name": n}
        values = g.T.to_dict().values()
        n = len(values)
        def size(t): 
            return (t['Rating'] * t['Count'] * 10000) / n
        temp3 = [{'name': t['Name'], 'size': size(t)} for t in values]
        temp2['children'] = temp3
        alist.append(temp2)
    return alist


I don't have enough experience with Pandas to know whether it is possible to improve on the groupby.  For example, it would be possible to perform a 2 level grouping with one call - ie. group on 'Count' and within that 'Rating'?

Considering that json is a string version of a dict, and you have a specific dictionary layout in mind, I don't see how you can organize the code in any other way.  With the exception of update, all dictionary additions are key by key.  So you have to have these 2 loops over groups.