Question 402968 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Convert JSON dictionary file to CSV

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 dictionary structure (not an array), 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'}]"

Preferred output:
recordID,title,date,actionType,time
783,Test1,1723572991,"submit",1723573283
783,Test1,1723572991,"Save",1723573425
783,Test1,1723572991,"Complete",1723585061
900,Test2,1723572825,"submit",1723573300
900,Test2,1723572825,"Save",1723573350
900,Test2,1723572825,"Complete",1723585390

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)
Tags:

2

Answers


  1. 0

    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_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", [])]
    Login or Signup to reply.
  2. 0

    Here’s a Python code snippet that converts a JSON dictionary file to a CSV file:

    import json
import csv

# Load the JSON file
with open('input.json', 'r') as f:
    data = json.load(f)

# Create a list to store the CSV rows
rows = []

# Iterate over the JSON data and create CSV rows
for key, value in data.items():
    row = [key, value]
    rows.append(row)

# Write the CSV rows to a file
with open('output.csv', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow(["Key", "Value"])  # header row
    writer.writerows(rows)

    Here’s how the code works:

    We load the JSON file using the json.load() function.
    We create an empty list rows to store the CSV rows.
    We iterate over the JSON data using the .items() method, which returns a list of key-value pairs.
    For each key-value pair, we create a CSV row by concatenating the key and value into a list.
    We append the CSV row to the rows list.
    We write the CSV rows to a file using the csv.writer() function. We first write a header row with the column names "Key" and "Value".
    We then write the CSV rows to the file using the writerows() method.
    Assuming your JSON file has the following structure:

    {
    "name": "Name",
    "age": XX,
    "city": "CITY"
    }

    The resulting CSV file would be
    Key,Value
    name,NAME
    age,XX
    city,CITY

    Details are altered for data protection**

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search