I want to create a multi nested json from my pandas dataframe

Question

I have a pandas data frame in the following format :-

    EMPCODE|Indicator|CustNAME
    1        CASA       Raja
    1        CASA       lala
    1        CASA       dada
    1        TL         Nan
    1        l          Nan
    1        p          Nan
    1        q          Nan
    2        CASA       Crick
    2        CASA       Flick
    2        TL         Nan
    2        l          Nan
    2        p          Nan
    2        q          Nan        

I want to convert this into a nested json .

Ive tried various different methods including groupby(),apply() but I Cant get a the output in the required json format.From the code mentioned below I am getting duplicate custNAme values for both employees .

  group = merge_hr_anr.groupby('EMPCODE_y').groups
  group1 = merge_hr_anr.groupby("EMPNAME").groups
    for variable in range(a):
            d = {'EMPCODE_y': list(group.keys())[variable],'EMPNAME': 
            list(group1.keys())[variable] ,'Indicators': [{'IndicatorName': 
            merge_hr_anr.loc[i, 'IndicatorName']} for i in list(group.values()) 
            [variable].unique()]}
            d['Indicators'] = list(map(dict,sorted(set(map(lambda x: 
            tuple(x.items()),d['Indicators'])), key=list(map(lambda x: 
            tuple(x.items()),d['Indicators'])).index)))
            d['Performance'] = [{i['IndicatorName']: 
(merge_hr_anr.loc[merge_hr_anr['IndicatorName'].eq(i['IndicatorName']),"CUSTNAME"]).dropna().tolist()} for i in d['Indicators']]    

    My output is
{
    "EMPCODE": "1",
    "Indicators": [
      {
        "IndicatorName": "CASA"
      },
      {
        "IndicatorName": "TL"
      },
      {
        "IndicatorName": "l"
      },
      {
        "IndicatorName": "p"
      },
      {
        "IndicatorName": "q"
      }
    ]
  "Performance":[
     {
         "CASA":[{"Custname":"Raja"},{"Custname":"lala"},{"Custname":"dada"}]
     },
     {
         "TL":[]
     }
     {
         "l":[]
     }
     {
         "p":[]
     }
     {
         "q":[]
     }

  ]
}
{
    "EMPCODE": "2",
    "Indicators": [
      {
        "IndicatorName": "CASA"
      },
      {
        "IndicatorName": "TL"
      },
      {
        "IndicatorName": "l"
      },
      {
        "IndicatorName": "p"
      },
      {
        "IndicatorName": "q"
      }
    ]
  "Performance":[
     {
         "CASA":[{"Custname":"Raja"},{"Custname":"lala"},{"CustName":"dada"}]
     },
     {
         "TL":[]
     }
     {
         "l":[]
     }
     {
         "p":[]
     }
     {
         "q":[]
     }

  ]
}

i want the output to be

{
    "EMPCODE": "1",
    "Indicators": [
      {
        "IndicatorName": "CASA"
      },
      {
        "IndicatorName": "TL"
      },
      {
        "IndicatorName": "l"
      },
      {
        "IndicatorName": "p"
      },
      {
        "IndicatorName": "q"
      }
    ]
  "Performance":[
     {
         "CASA":[{"Custname":"Raja"},{"Custname":"lala"},{"Custname":"dada"}]
     },
     {
         "TL":[]
     }
     {
         "l":[]
     }
     {
         "p":[]
     }
     {
         "q":[]
     }

  ]
}
{
    "EMPCODE": "2",
    "Indicators": [
      {
        "IndicatorName": "CASA"
      },
      {
        "IndicatorName": "TL"
      },
      {
        "IndicatorName": "l"
      },
      {
        "IndicatorName": "p"
      },
      {
        "IndicatorName": "q"
      }
    ]
  "Performance":[
     {
         "CASA":[{"Custname":"Crick"},{"Custname":"Flick"}]
     },
     {
         "TL":[]
     }
     {
         "l":[]
     }
     {
         "p":[]
     }
     {
         "q":[]
     }

  ]
}

Answer 1

Try the below code with constructing a dictionary:

group = merge_hr_anr.groupby('EMPCODE').groups
d = {'EMPCODE': list(group.keys())[0], 'Indicators': [{'IndicatorName': merge_hr_anr.loc[i, 'Indicator']} for i in list(group.values())[0].unique()]}
d['Indicators'] = list(map(dict,sorted(set(map(lambda x: tuple(x.items()),d['Indicators'])), key=list(map(lambda x: tuple(x.items()),d['Indicators'])).index)))
d['Performance'] = [{i['IndicatorName']: merge_hr_anr.loc[merge_hr_anr['Indicator'].eq(i['IndicatorName']), 'CustNAME'].dropna().tolist()} for i in d['Indicators']]
print(d)

Output:

{'EMPCODE': 1, 'Indicators': [{'IndicatorName': 'CASA'}, {'IndicatorName': 'TL'}, {'IndicatorName': 'l'}, {'IndicatorName': 'p'}, {'IndicatorName': 'q'}], 'Performance': [{'CASA': ['Raja', 'lala', 'dada']}, {'TL': []}, {'l': []}, {'p': []}, {'q': []}]}

To write a .json file:

with open('outvalue7.json', 'w') as f:
    f.write(str(d))