creating a json object from pandas dataframe

Question

      Groups sub-groups selections
    0   sg1    csg1       sc1
    1   sg1    csg1       sc2
    2   sg1    csg2       sc3
    3   sg1    csg2       sc4
    4   sg2    csg3       sc5
    5   sg2    csg3       sc6
    6   sg2    csg4       sc7
    7   sg2    csg4       sc8

I have the dataframe mentioned above and I am trying to create a JSON object as follows:

{
  "sg1": {
    "csg1": ['sc1', 'sc2'],
    "csg2": ['sc3', 'sc4']
  },
  "sg2": {
    "csg3": ['sc5', 'sc6'],
    "csg4": ['sc7', 'sc8']
  }
}

I tried using the pandas to_json and to_dict with orient arguments but I am not getting the expected result. I also tried grouping by the columns and then creating the list and converting it into a JSON.

Any help is much appreciated.

Answer 1

You can groupby ['Groups','sub-groups'] and build a dictionary from the multiindex series with a dictionary comprehension:

s = df.groupby(['Groups','sub-groups']).selections.agg(list)
d = {k1:{k2:v} for (k1,k2),v in s.iteritems()}

print(d)
# {'sg1': {'csg2': ['sc3', 'sc4']}, 'sg2': {'csg4': ['sc7', 'sc8']}}