Python 3 Get JSON value

Question

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

Duncan · Accepted Answer · 2018-07-05 16:11:51Z

1

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.

edited Jul 5, 2018 at 16:11

answered Jun 26, 2018 at 14:01

Duncan

96.4k15 gold badges129 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

seanie_oc Over a year ago

Hi Duncan, Thanks for the answer. When I implemented it the code I use for writhing to a CSV file keeps spliting each character into a different cell. i.e J o h n D o e, I have added my lines that I use for file writhing to the question above

seanie_oc Over a year ago

thanks for the update. It is nearly working now. The last little problem is it can't seem to find the value for StartTime. In the outputted details it has the names but StartTime is always blank when it shouldn't be.

Duncan Over a year ago

Oh, your data has Name and StartTime in separate objects. I've updated the code so it may work now.

seanie_oc Over a year ago

Hi Duncan, If I wanted to bring back all the objects not just Name and StartTime, do I need to add them in the same way as you showed me or is there a simpler way? Thanks

Duncan Over a year ago

Collect the values into a single dictionary then it will be easier to manipulate. Answer updated.

|

Collectives™ on Stack Overflow

Python 3 Get JSON value

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related