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?

score 1 · Accepted Answer · answered Mar 21 '19 at 06:17

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

How to convert json to dataframe

1 Answers1