Converting an API output from a Python Dictionary to a Dataframe

Question

I have extracted some data around how far you can travel from a certain set of coordinates in 15 mins using this code: https://github.com/traveltime-dev/traveltime-python-sdk.

 departure_search1 = {
    'id': "public transport from Trafalgar Square",
    'departure_time':  datetime.utcnow().isoformat(),
    'travel_time': 900,
    'coords': {'lat': 51.507609, 'lng': -0.128315},
    'transportation': {'type': "public_transport"}
}

out = ttpy.time_map(departure_searches=[departure_search1])

This produces a bit of a messy output which I am trying to convert into a dataframe.

{'results': [{'search_id': 'public transport from Trafalgar Square',
   'shapes': [{'shell': [{'lat': 51.51242588613939,
       'lng': -0.1439717545810651},
      {'lat': 51.51152707131364, 'lng': -0.14253203703525447},
      {'lat': 51.507931812010625, 'lng': -0.14253203703525447},
      {'lat': 51.50703299718487, 'lng': -0.1439717545810651},
      {'lat': 51.506134182359126, 'lng': -0.1439717545810651},
      {'lat': 51.505684774946246, 'lng': -0.14325189580815978},
      {'lat': 51.505684774946246, 'lng': -0.14181217826234913},
      {'lat': 51.50523536753337, 'lng': -0.1410923194894438},
      {'lat': 51.50164010823036, 'lng': -0.1410923194894438},
      {'lat': 51.50119070081748, 'lng': -0.14037246071653847},
      {'lat': 51.50119070081748, 'lng': -0.13605330807910654},
      {'lat': 51.499393071165976, 'lng': -0.13317387298748523},
      {'lat': 51.499393071165976, 'lng': -0.13029443789586392},
      {'lat': 51.4989436637531, 'lng': -0.1295745791229586},
      {'lat': 51.4971460341016, 'lng': -0.1295745791229586},
      {'lat': 51.496247219275844, 'lng': -0.12813486157714796},
      {'lat': 51.49534840445009, 'lng': -0.12813486157714796},
      {'lat': 51.49400018221146, 'lng': -0.12597528525843196},
      {'lat': 51.49444958962434, 'lng': -0.1250754617923003},
      {'lat': 51.49478664518399, 'lng': -0.12453556771262132},
      {'lat': 51.49534840445009, 'lng': -0.123815708939716},
      {'lat': 51.496247219275844, 'lng': -0.12363574424648965},
      {'lat': 51.50523536753337, 'lng': -0.12219602670067901},
      {'lat': 51.51006649722179, 'lng': -0.11589726243775741},
      {'lat': 51.510628256487884, 'lng': -0.11499743897162576},
      {'lat': 51.51242588613939, 'lng': -0.11517740366485209},
      {'lat': 51.51377410837802, 'lng': -0.11733697998356807},
      {'lat': 51.51377410837802, 'lng': -0.12021641507518938},
      {'lat': 51.51557173802953, 'lng': -0.12309585016681067},
      {'lat': 51.51557173802953, 'lng': -0.12453556771262132},
      {'lat': 51.5160211454424, 'lng': -0.12525542648552665},
      {'lat': 51.516919960268154, 'lng': -0.12525542648552665},
      {'lat': 51.517369367681034, 'lng': -0.12597528525843196},
      {'lat': 51.517369367681034, 'lng': -0.12741500280424262},
      {'lat': 51.51826818250679, 'lng': -0.12885472035005327},
      {'lat': 51.51826818250679, 'lng': -0.13029443789586392},
      {'lat': 51.51916699733254, 'lng': -0.13173415544167458},
      {'lat': 51.51871758991966, 'lng': -0.1324540142145799},
      {'lat': 51.516919960268154, 'lng': -0.1324540142145799},
      {'lat': 51.51647055285528, 'lng': -0.13317387298748523},
      {'lat': 51.51647055285528, 'lng': -0.13461359053329588},
      {'lat': 51.514672923203776, 'lng': -0.13749302562491716},
      {'lat': 51.514672923203776, 'lng': -0.14037246071653847},
      {'lat': 51.51557173802953, 'lng': -0.14181217826234913},
      {'lat': 51.51557173802953, 'lng': -0.14325189580815978},
      {'lat': 51.51512233061665, 'lng': -0.1439717545810651},
      {'lat': 51.514223515790896, 'lng': -0.1439717545810651},
      {'lat': 51.51332470096514, 'lng': -0.14253203703525447},
      {'lat': 51.51242588613939, 'lng': -0.1439717545810651}],
     'holes': []},
    {'shell': [{'lat': 51.50433655270762, 'lng': -0.11967652099551038},
      {'lat': 51.50074129340461, 'lng': -0.11967652099551038},
      {'lat': 51.50029188599173, 'lng': -0.11877669752937872},
      {'lat': 51.499393071165976, 'lng': -0.11445754489194676},
      {'lat': 51.499842478578856, 'lng': -0.11373768611904143},
      {'lat': 51.502538923056115, 'lng': -0.11805683875647338},
      {'lat': 51.50388714529474, 'lng': -0.11589726243775741},
      {'lat': 51.50388714529474, 'lng': -0.11445754489194676},
      {'lat': 51.50433655270762, 'lng': -0.11373768611904143},
      {'lat': 51.50703299718487, 'lng': -0.11373768611904143},
      {'lat': 51.50748240459775, 'lng': -0.11445754489194676},
      {'lat': 51.50759475645097, 'lng': -0.11589726243775741},
      {'lat': 51.506134182359126, 'lng': -0.11823680344969972},
      {'lat': 51.50433655270762, 'lng': -0.11967652099551038}],
     'holes': []}],
   'properties': {}}]}

I would like it to look something like this:

I have previously had a similar question answered here: Converting API output from a dictionary to a dataframe (Python) , but I couldn't quite adapt it for this output.

The code I tried is below:

df_list = []
for res in out['results']:
    search_id = res['search_id']
    for loc in res['shapes']:
        temp_df = {}
        temp_df['search_id'] = res['search_id']
        temp_df['lat'] = loc["shell"][0]    
        temp_df['lng'] = loc["shell"][1]  
        
        df_list.append(temp_df)
df = pd.DataFrame(df_list)

But instead of producing separate columns for the latitude and longtitude, it took the first set of full of coordinates and put in the "lat" column and the second full set of coordinates and put it in the "lng" column like below:

The overall aim is have these set of coordinates saved as a dataframe described as above so that I can bring it into Tableau and plot the area which you can travel in 15 mins from Trafalgar Square

So I assume it is something to do with my indexing? But I couldn't work it out. Would appreciate any suggestions :)

Thanks

JonasK · Accepted Answer · 2022-03-21T13:54:49.843

Disclaimer: I'm a dev at TravelTime

A shell consists of many points, to get the coordinates of a single point you would have to go one level deeper:

    temp_df['lat'] = loc["shell"][0]['lat']
    temp_df['lng'] = loc["shell"][0]['lng']

To put all points into a dataframe, you would have to loop over all elements of a shell.

However, I'm not sure this is the best way to get what you want.

I see that Tableau has the ability to import GeoJSON files. The TravelTime API can return a GeoJSON if you set the Accept header to application/geo+json.

Currently the simplest way to get back a GeoJSON in Python is to construct and send a POST request. Here's one way you could do it:

from datetime import datetime

import requests
import json

url = "https://api.traveltimeapp.com/v4/time-map"

payload = json.dumps({
    "departure_searches": [
        {
            "id": "public transport from Trafalgar Square",
            "coords": {"lat": 51.507609, "lng": -0.128315},
            "transportation": {"type": "public_transport"},
            "departure_time": datetime.utcnow().isoformat(),
            "travel_time": 900
        }
    ]
})

headers = {
    'Host': 'api.traveltimeapp.com',
    'Content-Type': 'application/json',
    'Accept': 'application/geo+json',
    'X-Application-Id': '<YOUR_APP_ID>',
    'X-Api-Key': '<YOUR_API_KEY>'
}

response = requests.request("POST", url, headers=headers, data=payload)

with open("shape.geojson", "w") as text_file:
    text_file.write(response.text)

You could then import the shape.geojson file into Tableau directly.

Hi JonasK. Thanks for this, I managed to get it to work. The issue I have now is that I am trying to bring through multiple isochrones into Tableau. I have locations I am trying to draw isochrones around them to see where you can drive from that location in 20 mins. I also need all the isochrones to have ID's so that I can join that to another SQL table to pull back more details about that location. I have created a loop that runs the above code one location at a time however, I couldn't add an ID to the geojson file or include all this data in one file. Are you able to help please? — MICHAELKM22, Apr 11 '22 at 15:03

Converting an API output from a Python Dictionary to a Dataframe

1 Answers1

Linked