python - libreria - creando df para generar json en el formato dado

leer y escribir json python

Question

Intento generar un df para producir esto debajo de json.

Datos de Json:

{
 "name": "flare",
 "children":  [
    {
     "name": "K1",
     "children": [
      {"name": "Exact", "size": 4},
      {"name": "synonyms", "size": 14}
     ]
    },
    {
     "name": "K2",
     "children": [
      {"name": "Exact", "size": 10},
      {"name": "synonyms", "size": 20}
     ]
    },
     {
     "name": "K3",
     "children": [
      {"name": "Exact", "size": 0},
      {"name": "synonyms", "size": 5}
     ]
    }, 
    {
     "name": "K4",
     "children": [
      {"name": "Exact", "size": 13},
      {"name": "synonyms", "size": 15}
     ]
    },
    {
     "name": "K5",
     "children": [
      {"name": "Exact", "size": 0},
      {"name": "synonyms", "size": 0}
     ]
    }
 ]
}

datos de entrada:

name    Exact   synonyms
K1        4       14
K2        10      20
K3        0       5
K4        13      15
K5        0       0

Intenté crear df con valores en json pero no pude obtener el json deseado en df.to_json, por favor ayuda.

Answer 1

Necesita remodelar los datos mediante set_index + stack y luego use groupby con apply for groupby list of dict :

import json

df = (df.set_index(''name'')
        .stack()
        .reset_index(level=1)
        .rename(columns={''level_1'':''name'', 0:''size''})
        .groupby(level=0).apply(lambda x: x.to_dict(orient=''records''))
        .reset_index(name=''children'')
        )

print (df)
  name                                           children
0   K1  [{''name'': ''Exact'', ''size'': 4}, {''name'': ''synon...
1   K2  [{''name'': ''Exact'', ''size'': 10}, {''name'': ''syno...
2   K3  [{''name'': ''Exact'', ''size'': 0}, {''name'': ''synon...
3   K4  [{''name'': ''Exact'', ''size'': 13}, {''name'': ''syno...
4   K5  [{''name'': ''Exact'', ''size'': 0}, {''name'': ''synon...

#convert output to dict
j = { "name": "flare", "children":  df.to_dict(orient=''records'')}

#for nice output - easier check
import pprint 
pp = pprint.PrettyPrinter(indent=4)
pp.pprint(j)
{   ''children'': [   {   ''children'': [   {''name'': ''Exact'', ''size'': 4},
                                        {''name'': ''synonyms'', ''size'': 14}],
                        ''name'': ''K1''},
                    {   ''children'': [   {''name'': ''Exact'', ''size'': 10},
                                        {''name'': ''synonyms'', ''size'': 20}],
                        ''name'': ''K2''},
                    {   ''children'': [   {''name'': ''Exact'', ''size'': 0},
                                        {''name'': ''synonyms'', ''size'': 5}],
                        ''name'': ''K3''},
                    {   ''children'': [   {''name'': ''Exact'', ''size'': 13},
                                        {''name'': ''synonyms'', ''size'': 15}],
                        ''name'': ''K4''},
                    {   ''children'': [   {''name'': ''Exact'', ''size'': 0},
                                        {''name'': ''synonyms'', ''size'': 0}],
                        ''name'': ''K5''}],
    ''name'': ''flare''}

#convert data to json and write to file
with open(''data.json'', ''w'') as outfile:
    json.dump(j, outfile)