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
(RatingCount10000)/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', 'ww

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.

StackExchange Code Review Q#150531, answer score: 2