Convert pandas dataframe with NaN to nested json with depth >=2

Question

I have dataframe as below with NaN value.

Category,Type,Capacity,Efficiency  
Chiller,ChillerA,1000,6.0  
Chiller,ChillerB,2000,5.5  
Cooling Tower,Cooling TowerA,1000,NaN  
Cooling Tower,Cooling TowerB,2000,NaN

I want to convert this pandas dataframe to below json format.
Can anyone tell me how to implement this?

{
    "Chiller":{
        "ChillerA":{
            "Capacity":1000,
            "Efficiency":6.0
        },
        "ChillerB":{
            "Capacity":2000,
            "Efficiency":5.5
        },
    },
    "Cooling Tower":{
        "Cooling TowerA":{
            "Capacity":1000 <=Will not include efficiency because efficiency was NaN for this.

        },
        "Cooling TowerB":{
            "Capacity":2000
        },
    },
}

zipa · Accepted Answer · 2018-09-04 14:22:00Z

2

This is a very robust solution that will get you to desired output using nested dict comprehension:

df = df.set_index(['Category', 'Type'])
{level: {chiller: {name: value for name, value in values.items() if not np.isnan(value)} for chiller, values in df.xs(level).to_dict('index').items()} for level in df.index.levels[0]}
#{'Cooling Tower':
#    {'Cooling TowerA':
#       {'Capacity': 1000.0},
#    'Cooling TowerB':
#        {'Capacity': 2000.0}},
# 'Chiller':
#    {'ChillerA': {'Efficiency': 6.0, 'Capacity': 1000.0},
#     'ChillerB': {'Efficiency': 5.5, 'Capacity': 2000.0}}}

answered Sep 4, 2018 at 14:22

zipa

28k6 gold badges45 silver badges62 bronze badges

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

Collectives™ on Stack Overflow

Convert pandas dataframe with NaN to nested json with depth >=2

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