How to convert json to dataframe

Question

This is what my json file looks like:

d = {
    "Success" : 
     {
       "Schema1.Table1" : [
         file1, file2
       ],
       "Schema1.Table2" : [
         file3, file4, file5
       ]
    },
   "Fail" :
     {
       "Schema1.Table1" : [
         file7, file8
       ],
       "Schema1.Table2" : [
         file10, file11, file12
       ]
     }
   }

I would like to convert it to a dataframe that looks like this:

Success
Schema1.Table1.file1
Schema1.Table1.file2

...

Fail
Schema1.Table1.file7
Schema1.Table1.file8
...

Any advice on how to do so?

jezrael · Accepted Answer · 2019-03-21 06:17:16Z

1

You can create dictionary of Series and pass to DataFrame constructor in nested dictionary comprehension:

import json
with open('file.json') as file:    
    d = json.load(file)  

d1 = {k: pd.Series([f'{k1}.{x}' for k1, v1 in v.items() for x in v1]) for k, v in d.items()}
df = pd.DataFrame(d1)
print (df)
                Success                   Fail
0  Schema1.Table1.file1   Schema1.Table1.file7
1  Schema1.Table1.file2   Schema1.Table1.file8
2  Schema1.Table2.file3  Schema1.Table2.file10
3  Schema1.Table2.file4  Schema1.Table2.file11
4  Schema1.Table2.file5  Schema1.Table2.file12

answered Mar 21, 2019 at 6:17

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

How to convert json to dataframe

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related