export excel rows to individual json files in python

My excel file has 500 rows of data. I am trying to get 500 individual JSON files. Each file should have data only from 1 row. Thank you in advance.

import json
import pandas
excel_data_df = pandas.read_excel("F:/2/N.csv.xlsx", 
sheet_name='Sheet1')
json_str = 
excel_data_df.to_json(orient='records')
for idx, row in enumerate(json_str):
fpath = str(idx) + ".json"
with open(fpath, "w+") as f:
json.dump(row, f)

1 answer

  • answered 2022-05-07 02:48 vincentleoooo

    It seems like you are using the pandas .to_json() method to convert the dataframe into JSON. This creates a list of JSON strings. If you are trying to get individual JSON files, may I suggest writing something like below.

    for i, row in enumerate(json_str):
        f = open(f"row_{i}.json", "w")
        f.write(row)
        f.close()
    

    This will iterate over the list you created and write a corresponding JSON file. However, there are other ways to do it as well, possibly doing it faster (if you need to scale it up to way more rows). This includes taking advantage of JSON Lines. This means that there is only one JSON file, but with all 500 JSON objects separated by a line break (\n). This might help in making things simpler for you as you can use the parameter lines=True in the read_json() and to_json() method within pandas. You might also want to separate the files outside of Python, if you choose to do so, through a shell script to split everything by line breaks.

    I hope this helps in some way!

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum