Need to extract recordID, date, title, and breakout action_history data. Since the action_history data also has the recordID, it does not all have to be on the same row for the CSV file. Here’s the JSON string:
{
"783": {
"recordID": 783,
"title": "Test1",
"date": 1723572991,
"action_history": [
{
"recordID": 783,
"time": 1723573283,
"actionType": "submit"
},
{
"recordID": 783,
"time": 1723573425,
"actionType": "Save"
},
{
"recordID": 783,
"time": 1723585061,
"actionType": "Complete"
}
]
},
"900": {
"recordID": 900,
"title": "Test2",
"date": 1723572825,
"action_history": [
{
"recordID": 900,
"time": 1723573300,
"actionType": "submit"
},
{
"recordID": 900,
"time": 1723573350,
"actionType": "Save"
},
{
"recordID": 900,
"time": 1723585390,
"actionType": "Complete"
}
]
}
}
Not getting very far with just the basic json.load and csv conversion methods. With this JSON structure, I’m having trouble breaking out the action_history for each recordID. The action_history can be in different rows since the recordID is indicated (I can join the records later).
File output:
recordID,title,date,action_history
783,Test1,1723572991,"[{'recordID': 783, 'time': 1723573283, 'actionType': 'submit'}, {'recordID': 783, 'time': 1723573425, 'actionType': 'Save'}, {'recordID': 783, 'time': 1723585061, 'actionType': 'Complete'}]"
900,Test2,1723572825,"[{'recordID': 900, 'time': 1723573300, 'actionType': 'submit'}, {'recordID': 900, 'time': 1723573350, 'actionType': 'Save'}, {'recordID': 900, 'time': 1723585390, 'actionType': 'Complete'}]"
Script:
import csv
import json
def json_to_csv(json_file, csv_file):
with open(json_file) as f:
data = json.load(f)
f = csv.writer(open(csv_file, "w+", newline=""))
f.writerow(["recordID","title","date","action_history"])
for x in data.values():
f.writerow([x["recordID"],
x["title"],
x["date"],
x["action_history"]])
#src, dest, function call
json_file = 'source.json'
csv_file = 'output.csv'
json_to_csv(json_file, csv_file)
New contributor
Shwfgd is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
you can try:
import csv
import json
def json_to_csv(json_file, csv_file):
with open(json_file) as f:
data = json.load(f)
f = csv.writer(open(csv_file, "w+", newline=""))
header = ["recordID","title","date","action_history",
"action_Submit_time",
"action_Save_time",
"action_Complete_time"
]
f.writerow(header)
for x in data.values():
result = [x["recordID"],
x["title"],
x["date"],
]
result.extend( [ j["time"] for j in x["action_history"] ])
f.writerow(result)
#src, dest, function call
json_file = 'source.json'
csv_file = 'output.csv'
json_to_csv(json_file, csv_file)
short:
result = [ x["recordID"],
x["title"],
x["date"]] + [j["time"] for j in x.get("action_history", [])]