Saving Data in Dictionary and saving in CSV with Python

Question

I'm trying to save information about traffic from an API to excel with python.

Basically I can get the data once at the moment but I want it to go underneath headings and then go down a row every new iteration in the csv output; in this example the headings are traffic travel time, length in meters, departure time and traffic delay in seconds. I am basically looking at traffic data every 10 minutes / whatever time say.

So how would I go about creating headers to separate columns, then having the data go below those columns, for the excel output. I assume it's to do with how I append the information to the variable i've set being data.

It would look like this basically,

Traffic Delay - Length In Meters - Departure Time - etc
Data at 0time - Data at 0time - Data at 0time - etc
Data at10time - Data at10time - Data at10time - etc

And the script i've written so far is below.

from lxml import etree
import urllib.request
import csv

#Pickle is not needed
#append to list next

def handleLeg(leg):
   # print this leg as text, or save it to file maybe...
   text = etree.tostring(leg, pretty_print=True)
   # also process individual elements of interest here if we want
   tagsOfInterest=["noTrafficTravelTimeInSeconds", "lengthInMeters", "departureTime", "trafficDelayInSeconds"]  # whatever
   #list to use for data analysis
   global data
   data = []
   #create header dictionary that includes the data to be appended within it. IE, Header = {TrafficDelay[data(0)]...etc
   for child in leg:
       if 'summary' in child.tag:
          for elem in child:
              for item in tagsOfInterest:
                  if item in elem.tag:
                      data.append(elem.text)


    def parseXML(xmlFile):

  While option
   lastTime = time.time() - 600
   while time.time() >= lastTime + 600:
    lastTime += 600
   #Parse the xml

   #Threading way to run every couple of seconds
   #threading.Timer(5.0, parseXML, ["xmlFile"]).start()

  with urllib.request.urlopen("https://api.tomtom.com/routing/1/calculateRoute/-37.79205923474775,145.03010268799338:-37.798883995180496,145.03040309540322:-37.807106781970354,145.02895470253526:-37.80320743019992,145.01021142594075:-37.7999012967757,144.99318476311566:?routeType=shortest&key=xxxx&computeTravelTimeFor=all") as fobj:
           xml = fobj.read()

   root = etree.fromstring(xml)

   for child in root:
       if 'route' in child.tag:
           handleLeg(child)
           # Write CSV file
           with open('datafile.csv', 'w') as fp:
            writer = csv.writer(fp, delimiter=' ')
            # writer.writerow(["your", "header", "foo"])  # write header
            writer.writerows(data)
           """for elem in child:
               if 'leg' in elem.tag:
                   handleLeg(elem)
"""


if __name__ == "__main__":
   parseXML("xmlFile")

with open('datafile.csv', 'r') as fp:
    reader = csv.reader(fp, quotechar='"')
    # next(reader, None)  # skip the headers
    data_read = [row for row in reader]

print(data_read)

Here is an example of what the API comes out as (it's XML)

<calculateRouteResponse xmlns="http://api.tomtom.com/routing" formatVersion="0.0.12">
<copyright>...</copyright>
<privacy>...</privacy>
<route>
<summary>
<lengthInMeters>5144</lengthInMeters>
<travelTimeInSeconds>687</travelTimeInSeconds>
<trafficDelayInSeconds>0</trafficDelayInSeconds>
<departureTime>2018-01-16T11:16:06+11:00</departureTime>
<arrivalTime>2018-01-16T11:27:33+11:00</arrivalTime>
<noTrafficTravelTimeInSeconds>478</noTrafficTravelTimeInSeconds>
<historicTrafficTravelTimeInSeconds>687</historicTrafficTravelTimeInSeconds>
<liveTrafficIncidentsTravelTimeInSeconds>687</liveTrafficIncidentsTravelTimeInSeconds>
</summary>
<leg>
<summary>
<lengthInMeters>806</lengthInMeters>
<travelTimeInSeconds>68</travelTimeInSeconds>
<trafficDelayInSeconds>0</trafficDelayInSeconds>
<departureTime>2018-01-16T11:16:06+11:00</departureTime>
<arrivalTime>2018-01-16T11:17:14+11:00</arrivalTime>
<noTrafficTravelTimeInSeconds>59</noTrafficTravelTimeInSeconds>
<historicTrafficTravelTimeInSeconds>68</historicTrafficTravelTimeInSeconds>
<liveTrafficIncidentsTravelTimeInSeconds>68</liveTrafficIncidentsTravelTimeInSeconds>
</summary>
<points>...</points>
</leg>
<leg>
<summary>
<lengthInMeters>958</lengthInMeters>
<travelTimeInSeconds>114</travelTimeInSeconds>
<trafficDelayInSeconds>0</trafficDelayInSeconds>
<departureTime>2018-01-16T11:17:14+11:00</departureTime>
<arrivalTime>2018-01-16T11:19:08+11:00</arrivalTime>
<noTrafficTravelTimeInSeconds>77</noTrafficTravelTimeInSeconds>
<historicTrafficTravelTimeInSeconds>114</historicTrafficTravelTimeInSeconds>
<liveTrafficIncidentsTravelTimeInSeconds>114</liveTrafficIncidentsTravelTimeInSeconds>
</summary>
<points>...</points>
</leg>
<leg>
<summary>
<lengthInMeters>1798</lengthInMeters>
<travelTimeInSeconds>224</travelTimeInSeconds>
<trafficDelayInSeconds>0</trafficDelayInSeconds>
<departureTime>2018-01-16T11:19:08+11:00</departureTime>
<arrivalTime>2018-01-16T11:22:53+11:00</arrivalTime>
<noTrafficTravelTimeInSeconds>181</noTrafficTravelTimeInSeconds>
<historicTrafficTravelTimeInSeconds>224</historicTrafficTravelTimeInSeconds>
<liveTrafficIncidentsTravelTimeInSeconds>224</liveTrafficIncidentsTravelTimeInSeconds>
</summary>
<points>...</points>
</leg>
<leg>
<summary>
<lengthInMeters>1582</lengthInMeters>
<travelTimeInSeconds>280</travelTimeInSeconds>
<trafficDelayInSeconds>0</trafficDelayInSeconds>
<departureTime>2018-01-16T11:22:53+11:00</departureTime>
<arrivalTime>2018-01-16T11:27:33+11:00</arrivalTime>
<noTrafficTravelTimeInSeconds>160</noTrafficTravelTimeInSeconds>
<historicTrafficTravelTimeInSeconds>280</historicTrafficTravelTimeInSeconds>
<liveTrafficIncidentsTravelTimeInSeconds>280</liveTrafficIncidentsTravelTimeInSeconds>
</summary>
<points>...</points>
</leg>
<sections>
<section>
<startPointIndex>0</startPointIndex>
<endPointIndex>139</endPointIndex>
<sectionType>TRAVEL_MODE</sectionType>
<travelMode>car</travelMode>
</section>
</sections>
</route>
</calculateRouteResponse>

Really appreciate your help - I'm very confused about how to proceed at the moment.

John R · Accepted Answer · 2018-01-30 04:08:14Z

1

There are various tools in the xml and csv python libraries, and multiple ways to parse xml to csv.

http://blog.appliedinformaticsinc.com/how-to-parse-and-convert-xml-to-csv-using-python/

seems to have an example already written, if a bit verbose...

I would recommend reading the docs on the libraries, and then using them to transform your data in the way you best see fit.

https://docs.python.org/2/library/xml.html

https://docs.python.org/3/library/csv.html

Update in response to OP comment:

Use a while loop.

lastTime = time.time() - 600
while time.time() >= lastTime + 600:
    lastTime += 600
    do whatever here

edited Jan 30, 2018 at 4:08

answered Jan 30, 2018 at 3:23

John R

1,51611 silver badges19 bronze badges

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

7 Comments

LeCoda Over a year ago

These examples are pretty confusing - appreciate the links though.

LeCoda Over a year ago

Brilliant. I tried another way to running it with threading. Is super complicated haha

LeCoda Over a year ago

This only ran the script once

John R Over a year ago

The loop I posted will run every ten minutes. Which IDE are you using? Step through your code in your debugger and inspect it line by line.

LeCoda Over a year ago

KK I'm running it through PythonAnywhere

|

Collectives™ on Stack Overflow

Saving Data in Dictionary and saving in CSV with Python

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