seanie_oc
seanie_oc

Reputation: 341

Python 3 Get JSON value

I am using rest with a python script to extract Name and Start Time from a response.

I can get the information but I can't combine data so that the information is on the same line in a CSV. When I go to export them to CSV they all go on new lines.

There is probably a much better way to extract data from a JSON List.

for item in driverDetails['Query']['Results']:
    for data_item in item['XValues']:
        body.append(data_item)
        for key, value in data_item.items():
            #driver = {}
            #test = {}
            #startTime = {}
            if key == "Name":
                drivers.append(value)
            if key == "StartTime":
                drivers.append(value)

print (drivers)

Code to write to CSV:

with open(logFileName, 'a') as outcsv:   
# configure writer to write standard csv file
writer = csv.writer(outcsv, delimiter=',', quotechar="'",
                    quoting=csv.QUOTE_MINIMAL, lineterminator='\n',skipinitialspace=True)
for driver in drivers:

    writer.writerow(driver)

Here is a sample of the response:

"Query": {
"Results": [
     {
        "XValues": [
            {
                "ReportScopeStartTime": "2018-06-18T23:00:00Z"
            },
            {
                "ReportScopeEndTime": "2018-06-25T22:59:59Z"
            },
            {
                "ID": "1400"
            },
            {
                "Name": " John Doe"
            },
            {
                "StartTime": "2018-06-19T07:16:10Z"
            },
        ],
    },
        "XValues": [
            {
                "ReportScopeStartTime": "2018-06-18T23:00:00Z"
            },
            {
                "ReportScopeEndTime": "2018-06-25T22:59:59Z"
            },
            {
                "ID": "1401"
            },
            {
                "Name": " Jane Smith"
            },
            {
                "StartTime": "2018-06-19T07:16:10Z"
            },
        ],
    },

My ouput in csv:

John Doe 
2018-06-19T07:16:10Z
Jane Smith
2018-06-19T07:16:10Z

Desired Outcome:

John Doe, 2018-06-19T07:16:10Z
Jane Smith, 2018-06-19T07:16:10Z

Upvotes: 0

Views: 101

Answers (1)

Duncan
Duncan

Reputation: 95652

Just use normal dictionary access to get the values:

for item in driverDetails['Query']['Results']:
    for data_item in item['XValues']:
        body.append(data_item)
        if "Name" in data_item:
             drivers.append(data_item["Name"])
        if "StartTime" in data_item:
             drivers.append(data_item["StartTime"])

print (drivers)

If you know the items will already have the required fields then you won't even need the in tests.

writer.writerow() expects a sequence. You are calling it with a single string as a parameter so it will split the string into individual characters. Probably you want to keep the name and start time together so extract them as a tuple:

for item in driverDetails['Query']['Results']:
    name, start_time = "", ""
    for data_item in item['XValues']:
        body.append(data_item)
        if "Name" in data_item:
             name = data_item["Name"]
        if "StartTime" in data_item:
             start_time = data_item["StartTime"]

    drivers.append((name, start_time))

print (drivers)

Now instead of being a list of strings, drivers is a list of tuples: the name for every item that has a name and the start time but if an input item has a name and no start time that field could be empty. Your code to write the csv file should now do the expected thing.

If you want to get all or most of the values try gathering them together into a single dictionary, then you can pull out the fields you want:

for item in driverDetails['Query']['Results']:
    fields = {}
    for data_item in item['XValues']:
        body.append(data_item)
        fields.update(data_item)

    drivers.append((fields["ID"], fields["Name"], fields["StartTime"]))

print (drivers)

Once you have the fields in a single dictionary you could even build the tuple with a loop:

    drivers.append(tuple(fields[f] for f in ("ID", "Name", "StartTime", "ReportScopeStartTime", "ReportScopeEndTime")))

I think you should list the fields you want explicitly just to ensure that new fields don't surprise you.

Upvotes: 1

Related Questions