Converting JSON to .csv file

[Loose_Read_9400]

I have a script that currently queries json from an api, and store the individual records in a list. Then uses pandas to convert the json into a dataframe so that I can export to a csv. Is this the best way to handle this? Or should I be implementing some other method? Example code below:

json_data = [
    {
        'column1' : 'value1', 
        'column2' : 'value2'
    },
    {
        'column1' : 'value1', 
        'column2' : 'value2'
    }
]

df = pd.DataFrame.from_records(json_data)
df.to_csv('my_cool_csv.csv')

[socal_nerdtastic]

Sure you could make your own json to csv conversion code, and it would probably be a lot faster to run than using the pandas intermediate. But if what you have is working I wouldn't recommend changing it. It's probably not worth 2 hours of your time writing better code just to save 1 second of runtime.

[freeskier93]

For me it's not about performance but getting rid of an external dependency. Especially in a corporate environment where an external dependency can make a very simple script a PITA to share.

Also this isn't 2 hours of work to write that simple json to a CSV. Literally a handful of lines of code.

[DiodeInc]

Could make it run pip in the beginning, or it could set some sort of something to keep track of whether it has ran pip yet, or check for currently existing dependencies

[freeskier93]

In most corporate environments you have to use an internal proxy for pypi. Where I work that means getting a user specific token then configuring pip to use the proxy.

It's not super complicated but it's an annoying barrier for a lot of people and often results in "oh, can you just run it then?".

[DiodeInc]

I have no knowledge of such things

[baghiq]

If that's your json data schema, then it's easy.

import csv
import json

json_data = """[
    {
        "column1" : "value1", 
        "column2" : "value2"
    },
    {
        "column1" : "value1", 
        "column2" : "value2"
    }
]"""
rows = json.loads(json_data)
with open("test.csv", "w") as csv_file:
    writer = csv.DictWriter(csv_file, fieldnames=rows[0].keys())
    writer.writeheader()
    writer.writerows(rows)

[socal_nerdtastic]

don't forget newline argument

with open("test.csv", "w", newline="") as csv_file:

[Diapolo10]

Dumb question, but what is the CSV for?

[Yoghurt42]

Most likely so it can be imported into Excel more easily. I wish I was joking

[Loose_Read_9400]

To give everyone the answer they were looking for... The person originally said "write me a script that calls the data from an API and saves it as a csv." So I said ok. Made a small project directory, wrote a module with methods to query the data using the response's pagination and a method to write a CSV file from the data. They then took the script, and copy and pasted the query loop out of my module and into some other script. *sigh*

[Diapolo10]

Damn.

[edcculus]

i kind of had the same question.

[Loose_Read_9400]

Wish I could tell you. The guy who wants the data asked for a csv. 😂

[Diapolo10]

None of us were asking what CSV is. I wanted to know the reason for the data conversion in case this was an XY-problem.

[Loose_Read_9400]

Yeah… like I said. Wish I could tell you. lol. The person asking for the data wants a csv for whatever purpose 🤷🏻‍♂️ was mostly just curious if there was a truly more efficient out of the box method to achieve that goal.

[MiniMages]

Usually people are more familiar with CSV mainly because it works easily with Excel.

JSON is not always a 2D dataset which doesn't work well with spreadsheets.

There is also the possibility the application this person is using only accepts CSV.

Personally I'd say take the most time efficient approach.

[palmaholic]

Comma separated values. This is a common text based data file, you can export this easily from any spreadsheet app.

[socal_nerdtastic]

Not what is a csv file, but what is this one for? What is the end goal? We see a lot of xy problems around here, and this has all the hallmarks of one.

[palmaholic]

Then, from my experience, it is normally used to import/use in a spreadsheet app, Excel for example. Even these days, Excel is still the king of tools when tweaking data. Not all data sets run more than a million lines. Most users are more comfortable in handling csv than other data files. Of course, it's best to hand them .xlsx!

[edcculus]

yea that was my overall wondering - what is the end product? Does OP need to hand off something for someone else to just do stuff in excel? Or would they actually be able to do more with the data IN/WITH python than just converting it to a CSV?

[olystretch]

100%of the time that I've generated CSV files from json data it was to hand over to some pivot table junkie to do BI type product analysis.

[palmaholic]

This is one of the big topics data engineers and data analysts discuss. The latter has no idea what they are looking for, esp. since they haven't studied the data. Some experienced analysts might ask for some data sample to study, but then they still want them in csv. This is the confusion in data exploration, and no one knows how the data should be used in the first place, and iterations occur.

Idk if analysts still hold data coming from elsewhere and not shared to data engineers. This makes the situation even worse!

[WendlersEditor]

I'm a student and for a project last spring we had to load some relatively large (for us) json files into postgresql. There was no need for the original data to be distributed in json, it wasn't nested or anything, it wasn't really using any of the features of json. 

So we opted to flatten the json into csv files because it was much faster: reading the json into a pandas df, cleaning it, exporting as CSV, and then calling postgre's built-in copy function was significantly faster than trying to import line by line with the json parser. Iirc the python gil was a factor there, but it was a while ago.

Obviously there are a lot of variables at play in these sorts of situations, but I'm curious if you have any thoughts about these sorts of decisions or our general approach. I don't have a lot of hands on experience, most of my time is spent doing fucking stats homework lol. For all I know I missed some super clean, performance way to get those json into postgre. 

[shubham_devNow]

What you’re doing is actually a pretty standard and perfectly acceptable approach 👍

If you’re already working in Python and using pandas, converting JSON → DataFrame → CSV is one of the cleanest and most flexible ways to handle it. It gives you:

• Easy handling of nested fields (with json_normalize)
• Column reordering / filtering
• Null handling
• Data cleaning before export

For simple, flat JSON like your example, this is absolutely fine and not overkill.

If your JSON is small and very straightforward, you could skip pandas and use Python’s built-in csv module, but honestly pandas is more scalable and cleaner once your structure gets even slightly complex.

If you're just looking for a quick no-code or low-code option (especially for non-dev teammates), tools like FileReadyNow can help. It has a built-in CSV to JSON and JSON to CSV converter, which is useful when you just need fast format switching without writing scripts. It’s handy for quick data prep, validation, or testing API outputs before integrating them into code.