multiple json to csv using pandas python

Question

trying to convert multiple json files to 1 csv file
tried 2 ways,
first one using pandas , second using json and csv writer
about my json

keys are unordered and some keys are different in every file

code using writer

file_list=os.listdir('output')
count = 0
for file in file_list:
    dict={}
    file_path = "output/" + file
    with open(file_path,'r') as f:
        jsonData=json.load(f)
        datafile=open('data.csv','a')
        csv_writer = csv.writer(datafile)
        if count == 0:
            header = jsonData.keys()
            csv_writer.writerow(header)
            count += 1
            csv_writer.writerow(jsonData.values())
        if count == 1:
            csv_writer.writerow(jsonData.values())

        datafile.close()

problem

bcoz my data is unordered and different keys so in my csv file wrong value is coming under wrong header

code using pandas

for file in file_list:
    dict={}
    file_path = "output/" + file
    with open(file_path,'r') as f:
        jsonData=json.load(f)
        for j in jsonData:


            dict.update({j:[jsonData[j]]})
        df=pd.DataFrame(dict)
        df.to_csv("hello.csv")

problem

i dont know how to append in pandas 
so this is showing only 2 rows bcoz of my last json file i guess

inside my json

question from:https://stackoverflow.com/questions/65844311/multiple-json-to-csv-using-pandas-python

Answer 1

Try this code:

import pandas as pd
import json
import pathlib

data_path = pathlib.Path('.')
keys = ['Solutions', 'account_number', 'actual_reading_current','actual_reading_previous', 'address', 'amount_due']
dat = dict([(k, []) for k in keys])

for jfile in data_path.glob('*.json'):
    with jfile.open('r') as ifile:
        json_data = json.load(ifile)
    for key in keys:
        dat[key].append(json_data[key][0] if key in json_data else None)

result = pd.DataFrame.from_dict(dat)
result.to_csv('result.csv')

I first define a dictionary containing the columns that I want. Then I read in the json files and append them as rows to the dictionary.

Note, that I had to edit your json files, one was missing a ending quote and I had to replace the single quotes by double quotes.