Looping Through CSV with Pandas in Python

by Bill Hambone   Last Updated September 11, 2019 21:22 PM

I'm writing a Python script that takes a list of locations output to csv from ArcGIS Pro, grabs the lat/lon coordinates, and plugs those into a transit planner API URL. This returns an XML formatted response which I scrape some data from (route, number of transfers, travel distance, trip duration). What I want to do next is write those four new values to a new csv with all the original columns in tow. I can do this for one row, but I'm hitting a wall with trying to figure out out to loop through each row of the initial csv with Pandas. Do I need to write each new row to an array and then df.to_csv at the end? Here is my script so far.

import requests
import re
import pandas as pd

locations_csv = r'C:\\Temp\\locations.csv'
new_csv = r'C:\\Temp\\travel_times.csv'
Latitude = ''
Longitude = ''
Unique_ID = ''
distance = ''
route = ''
transfers = ''
duration = ''

Latitude = pd.read_csv(locations_csv, sep = ",")['Latitude'].values[0]
Longitude = pd.read_csv(locations_csv, sep = ",")['Longitude'].values[0]
Unique_ID = pd.read_csv(locations_csv, sep = ",")['ID_ESIS'].values[0]
lat = round(Latitude,2)
lon = round(Longitude,2)

##API GET request generates XML formtatted response
requestURL = "https://<localtransitprovider>/ws/V1/trips/tripplanner/fromCoord/" + str(lon) + "," + str(lat) + "/toplace/<landmark>/Mode/A/MaxIntineraries/1/appId/xxxxxxxxxxxxxxxxxxxxxxxx"
r = requests.get(requestURL).text

##use regular expressions to extract values from XML string
dis = re.search('<distance>(.+?)<', r)
dist = dis.group(1)
dur = re.search('duration>(.+?)<', r)
trans = re.search('<numberOfTransfers>(.+?)<', r)
rte = re.search('viaRoute="(.+?)">', r)
transfers = trans.group(1)
duration = dur.group(1)
route = rte.group(1)
distance = round(float(dist),1)

##append new values to new csv 
df = pd.DataFrame()

df = pd.read_csv(locations_csv)

df['TriMetRoute'] = route
df['Transfers'] = transfers
df['Distance Miles'] = distance
df['TravelTime Minutes'] = duration

df.to_csv(new_csv, index=False)
Tags : python pandas

Related Questions

Visualizing GPS points on interactive map

Updated August 05, 2018 15:22 PM

comparing the rows of a column in python

Updated June 19, 2018 15:22 PM