我想从pandas datafram创建一个多嵌套json

Question

我有一个熊猫数据帧，格式如下：

    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        

我想把它转换成一个嵌套的json。在

我尝试了各种不同的方法，包括groupby（）、apply（），但我无法获得所需json格式的输出格式。从下面提到的代码我得到了两个员工的重复custNAme值。在

^{pr2}$

    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":[]
     }

  ]
}

我希望输出是

{
    "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

尝试使用以下代码构造dict索引：

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)

输出：

^{pr2}$

要编写.json文件：

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