Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
CSV to Json
#1
As a python beginner, I like to take a CSV file and load and then output json

I have tried a few things - pandas, csvmapper but I'm struggling to see the best way to to read this and create the sample json from the CSV. I think it is a Dictionary with 2 lists embedded but obvious how to approach this

Quote:A,B,source,status,RecordType,xA,xB
q1,,Direct,New, Quote,x200,,
x1,y2,Direct,New, Quote,x200,y300


I would like to create this
[{
    "RecIds": [{
        "type": "A",
        "value": "q1"
    }],
    "source": "Direct",
    "status": "New",
    "recordType": "Quote",
    "xIds": [{
        "type": "B",
        "value": "x200"
    }]
}, {
    "RecIds": [{
        "type": "A",
        "value": "x1"
    }, {
        "type": "B",
        "value": "y2"
    }],
    "source": "Direct",
    "status": "New",
    "recordType": "Quote",
    "xIds": [{
        "type": "A",
        "value": "x200"
    }, {
        "type": "B",
        "value": "y200"
    }]
}]
Best approach that I have found is so thing like this
https://medium.com/coinmonks/parsing-a-s...18f5c70bd3

import csv, json, xlrd
def csvParse(csvfile):
    # Open the CSV
    f = open(csvfile, 'r')
    # Change each fieldname to the appropriate field name.
    reader = csv.DictReader(f, fieldnames=(
    "Protocol", "Source Port", "Destination Port", "Source IP", "Destination IP", "Source MAC", "Destination MAC",
    "segment ID", "Frame name", "Frame ID", "segment name", "packet ID", "Receivers"))
    framenames = []
    store = []
    # Store frame names in a list
    for row in reader:
        frame = {"FrameName": row["Frame name"],
                 "FrameID": row["Frame ID"],
                 "protocol": row["Protocol"],
                 "segments": []}
        if row["Frame name"] not in framenames:
            framenames.append(row["Frame name"])
            store.append(frame)

    # Create Objects for Frames, segments and packets
    segment = {"segmentName": ""}
    for frame in store:
        f = open(csvfile, 'r')
        reader = csv.DictReader(f, fieldnames=(
        "Protocol", "Source Port", "Destination Port", "Source IP", "Destination IP", "Source MAC", "Destination MAC",
        "segment ID", "Frame name", "Frame ID", "segment name", "packet ID", "Receivers"))
        for row in reader:
            if frame["FrameName"] == row["Frame name"]:
                if segment["segmentName"] != row["segment name"]:
                    segment = {
                        "segmentID": row["segment ID"],
                        "segmentName": row["segment name"],
                        "srcPort": row["Source Port"],
                        "destPort": row["Destination Port"],
                        "packets": [{
                            "packetID": row["packet ID"],
                            "Receivers": row["Receivers"],
                            "destIP": row["Destination IP"],
                            "destMAC": row["Destination MAC"],
                            "srcIP": row["Source IP"],
                            "srcMAC": row["Source MAC"]
                        }]
                    }
                    frame["segments"].append(segment)
                else:
                    packet = {
                        "packetID": row["packet ID"],
                        "Receivers": row["Receivers"],
                        "destIP": row["Destination IP"],
                        "destMAC": row["Destination MAC"],
                        "srcIP": row["Source IP"],
                        "srcMAC": row["Source MAC"]
                    }
                    segment["packets"].append(packet)

    # Parse the CSV into JSON
    out = json.dumps(store, indent=4)
    # Save the JSON
    f = open('data.json', 'w')
    f.write(out)
But as I said not sure if this is the right or best approach
Reply
#2
please attach csv file
Reply
#3
(Jul-08-2020, 08:51 PM)Larz60+ Wrote: please attach csv file


My CSV would look like
A,B,source,status,RecordType,xA,xB
q1,,Direct,New, Quote,x200,,
x1,y2,Direct,New, Quote,x200,y300
Reply
#4
I wondered if anyone had any pointers on how to create this json from the simple CSV
Reply
#5
I think this is close if not there:
import os
import csv
import json


def csv_to_json():
    alldata = []
    ddict = {}
    # Excpect input file in same directory as this script, so set cwd
    os.chdir(os.path.abspath(os.path.dirname(__file__)))
    with open('csvfile.csv') as fp, open('jsonfile.json', 'w') as fo:
        crdr = csv.DictReader(fp)
        for row in crdr:
            rnode = ddict = {}
            dnode = rnode['RecIds'] = {}
            dnode['type'] = 'A'
            dnode['value'] = row['A']
            
            rnode['source'] = row['source']
            rnode['status'] = row['status']
            rnode['recordType'] = row['RecordType']

            xnode = rnode['xIds'] = {}
            xnode['type'] = 'B'
            xnode['value'] = row['B']
            alldata.append(ddict)
        json.dump(alldata, fo)

def read_it_back_as_dict():
    with open('jsonfile.json') as fp:
        mydict = json.load(fp)
    print(mydict)


if __name__ == '__main__':
    csv_to_json()
    read_it_back_as_dict()
results:
Output:
[{ "RecIds": { "type": "A", "value": "q1" }, "source": "Direct", "status": "New", "recordType": " Quote", "xIds": { "type": "B", "value": ""} }, { "RecIds": { "type": "A", "value": "x1" }, "source": "Direct", "status": "New", "recordType": " Quote", "xIds": { "type": "B", "value": "y2" } }]
I think this is a way overly complicated json format and makes for extra work in deciphering.
It's always good to kkeep it simple
Reply
#6
(Jul-11-2020, 03:17 AM)Larz60+ Wrote: I think this is a way overly complicated json format and makes for extra work in deciphering.
It's always good to kkeep it simple


Thank you for looking at this - I have really struggled to create this json - unfortunately it is part of an existing api that I want to interact with.


unfortunately does not quite generate expected format

    "RecIds": [{
        "type": "A",
        "value": "x1"
    }, {
        "type": "B",
        "value": "y2"
    }]
RecIds can be one or more identifiers as per sample


Modding your example I can the basic structure but I encounter 3 problems:
  • blank value(I guess and if can take care of this),
  • invalid json due to the ' rather than "
  • and the embedded objects should be [] rather than {}


import os
import csv
import json


def csv_to_json():
    alldata = []
    ddict = {}
    # Excpect input file in same directory as this script, so set cwd
    os.chdir(os.path.abspath(os.path.dirname(__file__)))
    with open('sample.csv') as fp, open('jsonfile.json', 'w') as fo:
        crdr = csv.DictReader(fp)
        for row in crdr:
            rnode = ddict = {}
            dnode = rnode['RecIds'] = {}
            dnode['type'] = 'A'
            dnode['value'] = row['A']
            dnode['type2'] = 'B'
            dnode['value2'] = row['B']
            rnode['source'] = row['source']
            rnode['status'] = row['status']
            rnode['recordType'] = row['RecordType']

            xnode = rnode['xIds'] = {}
            xnode['type'] = 'xA'
            xnode['value'] = row['xA']
            xnode['type2'] = 'xB'
            xnode['value2'] = row['xB']
            alldata.append(ddict)
        out=json.dumps(alldata, indent=4)
        f = open('jsonfile.json', 'w')
        f.write(out)

def read_it_back_as_dict():
    with open('jsonfile.json') as fp:
        mydict = json.load(fp)
    print(mydict)


if __name__ == '__main__':
    csv_to_json()
    read_it_back_as_dict()
Reply
#7
Not sure how to fix the problems
dnode = rnode['RecIds'] = {"RecIds":[]}

segment = {
"type": "A",
"value": row["A"]
}
dnode['RecIds'].append(segment)
Reply


Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020