With the pandas library, this is as easy as using two commands!
df = pd.read_json()
read_json converts a JSON string to a pandas object (either a series or dataframe). Then:
df.to_csv()
Which can either return a string or write directly to a csv-file. See the docs for to_csv.
Based on the verbosity of previous answers, we should all thank pandas for the shortcut.
For unstructured JSON see this answer.
EDIT: Someone asked for a working minimal example:
import pandas as pd
with open('jsonfile.json', encoding='utf-8') as inputfile:
df = pd.read_json(inputfile)
df.to_csv('csvfile.csv', encoding='utf-8', index=False)
Answer from vmg on Stack OverflowWith the pandas library, this is as easy as using two commands!
df = pd.read_json()
read_json converts a JSON string to a pandas object (either a series or dataframe). Then:
df.to_csv()
Which can either return a string or write directly to a csv-file. See the docs for to_csv.
Based on the verbosity of previous answers, we should all thank pandas for the shortcut.
For unstructured JSON see this answer.
EDIT: Someone asked for a working minimal example:
import pandas as pd
with open('jsonfile.json', encoding='utf-8') as inputfile:
df = pd.read_json(inputfile)
df.to_csv('csvfile.csv', encoding='utf-8', index=False)
First, your JSON has nested objects, so it normally cannot be directly converted to CSV. You need to change that to something like this:
{
"pk": 22,
"model": "auth.permission",
"codename": "add_logentry",
"content_type": 8,
"name": "Can add log entry"
},
......]
Here is my code to generate CSV from that:
import csv
import json
x = """[
{
"pk": 22,
"model": "auth.permission",
"fields": {
"codename": "add_logentry",
"name": "Can add log entry",
"content_type": 8
}
},
{
"pk": 23,
"model": "auth.permission",
"fields": {
"codename": "change_logentry",
"name": "Can change log entry",
"content_type": 8
}
},
{
"pk": 24,
"model": "auth.permission",
"fields": {
"codename": "delete_logentry",
"name": "Can delete log entry",
"content_type": 8
}
}
]"""
x = json.loads(x)
f = csv.writer(open("test.csv", "wb+"))
# Write CSV Header, If you dont need that, remove this line
f.writerow(["pk", "model", "codename", "name", "content_type"])
for x in x:
f.writerow([x["pk"],
x["model"],
x["fields"]["codename"],
x["fields"]["name"],
x["fields"]["content_type"]])
You will get output as:
pk,model,codename,name,content_type
22,auth.permission,add_logentry,Can add log entry,8
23,auth.permission,change_logentry,Can change log entry,8
24,auth.permission,delete_logentry,Can delete log entry,8
Converting JSON to CSV
Convert json to csv or XML ?
Converting JSON to .csv file
Best way to automate JSON to CSV/Relational Tables at scale? Anyone have used Flexter?
Videos
I can export my data into json files only.
The problem is, for my database I need CSV or XML format.
Is there a way to convert the json for CVS or XML ?
Any tutorial or way to do this would be appreciated.
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')