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
Convert json to csv or XML ?
How to transform a JSON file into a CSV one in Python?
Converting JSON to .csv file
Convert csv in json with python
The problem with your desired output is that it is not valid json document,; it's a stream of json documents!
That's okay, if its what you need, but that means that for each document you want in your output, you'll have to call json.dumps.
Since the newline you want separating your documents is not contained in those documents, you're on the hook for supplying it yourself. So we just need to pull the loop out of the call to json.dump and interpose newlines for each document written.
import csv
import json
csvfile = open('file.csv', 'r')
jsonfile = open('file.json', 'w')
fieldnames = ("FirstName","LastName","IDNumber","Message")
reader = csv.DictReader( csvfile, fieldnames)
for row in reader:
json.dump(row, jsonfile)
jsonfile.write('\n')
You can use Pandas DataFrame to achieve this, with the following Example:
import pandas as pd
csv_file = pd.DataFrame(pd.read_csv("path/to/file.csv", sep = ",", header = 0, index_col = False))
csv_file.to_json("/path/to/new/file.json", orient = "records", date_format = "epoch", double_precision = 10, force_ascii = True, date_unit = "ms", default_handler = None)
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')