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 Longitude = pd.read_csv(locations_csv, sep = ",")['Longitude'].values Unique_ID = pd.read_csv(locations_csv, sep = ",")['ID_ESIS'].values 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)