dataframe to json with orient index and index equals row value

Question

I have a pandas dataframe that I am trying to convert to a certain json format:

df = pd.DataFrame([['A',1,2,3],['B',2,3,4],['C','C',1,6],['D','D',9,7]], columns=['W','X','Y','Z'])

df.set_index('W', inplace=True, drop=True, append=False)

df
   X  Y  Z
W
A  1  2  3
B  2  3  4
C  C  1  6
D  D  9  7

I am looking to get a json output as follows:

output_json = {'A': {'X':1,'Y':2,'Z':3}, 'B': {'X':2,'Y':3,'Z':4}, 'C':{'Y':1,'Z':6}, 'D': {'Y':9,'Z':7} }

This is what I have tried but I can't get the desired result for 'C' and 'D' keys:

df.to_json(orient='index')
'{"A":{"X":1,"Y":2,"Z":3},"B":{"X":2,"Y":3,"Z":4},"C":{"X":"C","Y":1,"Z":6},"D":{"X":"D","Y":9,"Z":7}}'

How to fix this? Perhaps this is something straightforward that I am missing. Thanks.

jezrael · Accepted Answer · 2017-07-11 04:22:28Z

2

You can first convert to_dict and then use nested dict comprehension for filtering only int values, last for json use dumps:

import json

d = df.to_dict(orient='index')
j = json.dumps({k:{x:y for x,y in v.items() if isinstance(y, int)} for k, v in d.items()})
print (j)
{"A": {"X": 1, "Y": 2, "Z": 3}, 
 "C": {"Y": 1, "Z": 6},
 "D": {"Y": 9, "Z": 7},
 "B": {"X": 2, "Y": 3, "Z": 4}}

answered Jul 11, 2017 at 4:22

jezrael

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

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

1 Comment

jezrael Over a year ago

Glad can help! Good luck!

Collectives™ on Stack Overflow

dataframe to json with orient index and index equals row value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related