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

Should each json file be one row in the output? Could you provide one or two short example json files and maybe even a list of all possible keys, that should appear in the csv output? — ClF3
– ClF3, Commented Jan 22, 2021 at 11:36
header Solutions, account_number ,actual_reading_current, actual_reading_previous address, amount_due, values $7.90 72xxx06,,,, 16839,,, 16586 ,,, T B FAIR LAWN BORO NJ 07410 ,,,48.1 — robin
– robin, Commented Jan 22, 2021 at 11:40
This is the header that you want in your csv file? Could you please also give me a dummy json file to test? And please either use a file sharing service or alter your original post, comments don't allow for a lot of formatting. — ClF3
– ClF3, Commented Jan 22, 2021 at 11:43

ClF3 · Accepted Answer · 2021-01-22 11:59:01Z

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.

answered Jan 22, 2021 at 11:59

ClF3

1712 bronze badges

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

5 Comments

robin Over a year ago

this error is comingdat[key].append(json_data[key][0] if key in json_data else None) TypeError: 'float' object is not subscriptable

ClF3 Over a year ago

Interesting. Is it possible that some json files are structured differently, e.g. not all values are inside of a list? If that is the case you can either make all json files use the same format or check inside the main loop if json_data[key] is a list.

robin Over a year ago

okay data i have show above is i edited it into list through my code ,,,,values are not into list inside my json they are normal strings

robin Over a year ago

inside dat after first loop\

{' Solutions': ['$'], 'account_number': [], 'actual_reading_current': [], 'actual_reading_previous': [], 'address': [], 'amount_due': [], 'average_supply': [],  'rate': [], 'total_amount_debt': [], 'total_electric_charge': [], 'total_electric_delivery_charge': [], 'total_electricity_used': []}

ClF3 Over a year ago

I wont change my answer, while your json format stays in the original post. But to work with the different format, change 'json_data[key][0] if key in json_data else None' to 'json_data.get(key, None)'. You no longer need to take the value out of the array, simply get the key and if the key is not there, return None.

Collectives™ on Stack Overflow

multiple json to csv using pandas python

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related