Json to csv conversion for nested json in python

Question

Having data in below json format

{"A": {"Show only": ["Buy"], "Apple MacBook Product Line": ["MacBook Pro", "MacBook Air", "MacBook (Original)"], "Color": ["Space Gray", "Silver", "Gold", "Rose Gold", "Gray", "White", "Champagne Gold"], "Seller": ["Back Market", "Best Buy", "eBay"]}, "B": {"Show only": ["Buy"], "Material": ["Human Hair", "Synthetic"], "Features": ["Lace Front"], "Seller": ["arabellahair", "Ergode", "PartyBell.com"]}}

tried using below to convert this to csv format

import pandas as pd
import csv
import glob


for file in glob.glob('file.json'):
    dfjson = pd.read_json(file,encoding='utf-8',lines= False, dtype=str)
dfjson.to_csv("output.csv",index = True)

But the expected output is

A   Show only   Buy
A   Apple MacBook Product Line_001  MacBook Pro
A   Apple MacBook Product Line_002  MacBook Air
A   Apple MacBook Product Line_003  MacBook (Original)
A   Color_001   Space Gray
A   Color_002   Silver
A   Color_003   Gold
A   Color_004   Rose Gold
A   Color_005   Gray
A   Color_006   White
A   Color_007   Champagne Gold
A   Seller_001  Back Market
A   Seller_002   Best Buy
A   Seller_003  eBay
B   Show only   Buy
B   Material_001    Human Hair
B   Material_002    Synthetic
B   Features    Lace Front
B   Seller_001  arabellahair
B   Seller_002  Ergode
B   Seller_003  PartyBell.com

What changes can be made to get this output

@Yefet, how must the dict be to get this output. any suggestion would be helpful — Vj_x49x
– Vj_x49x, Commented Apr 20, 2021 at 11:33

Yefet · Accepted Answer · 2021-04-20 11:45:10Z

1

You got wrong json structure for that , here i tried replace your json with right format

json_file =  {"col1" :["A" , "A" , "A"] ,
              "col2": ["Show only" ,"Apple MacBook Product" ,"Apple MacBook Product"],
              "col3":["Buy","MacBook Pro","MacBook (Original)" ]}

df = pd.DataFrame(json_file)

df

    col1    col2           col3
0   A       Show only      Buy
1   A       Apple MacBook  Product  MacBook Pro
2   A       Apple MacBook  Product  MacBook (Original)

answered Apr 20, 2021 at 11:45

Yefet

2,1162 gold badges12 silver badges21 bronze badges

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

2 Comments

Vj_x49x Over a year ago

is this the only possible dict structure for this output

Yefet Over a year ago

@Vj_x49x this is the most appropriate for your task without realy much extra steps

Collectives™ on Stack Overflow

Json to csv conversion for nested json in python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related