convert hierarchical data to a specific json format in python

Question

I have a dataframe like below. Each topic has several sub-topics.

pd.DataFrame({'topic': ['A', 'A', 'A', 'B', 'B'],
               'sub-topic': ['A1', 'A2', 'A3', 'B1', 'B3' ],
                'value': [2,12,44,21,1]})

    topic   sub-topic   value
0   A        A1         2
1   A        A2         12
2   A        A3         44
3   B        B1         21
4   B        B3         1

I need to convert it to Json format like below. Within first layer, for example topic A, the value is the sum of all its sub-topics.

{'A': {
    'value': 58,
    'children': {
        'A1': {'value': 2},
        'A2': {'value': 12},
        'A3': {'value': 44}    
       },
    },
 'B': {
     'value': 22,
     'children': {
         'B1': {'value': 21},
         'B3': {'value': 1}
      }
   }
}

Does anyone know how I can convert the data to this specific json? I have no clue how I should approach that. Thanks a lot in advance.

jezrael · Accepted Answer · 2020-02-26 06:05:16Z

1

Use cusom function in GroupBy.apply, last use Series.to_dict or Series.to_json:

def f(x):
    d = {'value': x['value'].sum(),
         'children': x.set_index('sub-topic')[['value']].to_dict('index')}
    return (d)

#for dictonary
out = df.groupby('topic').apply(f).to_dict()

#for json
#out = df.groupby('topic').apply(f).to_json()

print (out)

{
    'A': {
        'value': 58,
        'children': {
            'A1': {
                'value': 2
            },
            'A2': {
                'value': 12
            },
            'A3': {
                'value': 44
            }
        }
    },
    'B': {
        'value': 22,
        'children': {
            'B1': {
                'value': 21
            },
            'B3': {
                'value': 1
            }
        }
    }
}

answered Feb 26, 2020 at 6:05

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

convert hierarchical data to a specific json format in python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related