Introduction¶
As I commute daily with the train, I thought a simple data project would be to visualize my travel history. This can be used as a template or inspiration for others.
NS is the train company in the netherlands. You can download your data by going to https://www.ns.nl/mijnns#/reishistorie and selecting Periode > CSV > Download.
We will visualize the trajectories in this data quickly in Tabelau. To map the trips geographically we use the methodology outline by Tableau here https://onlinehelp.tableau.com/current/pro/desktop/en-us/maps_howto_origin_destination.htm
To get the data in this format, follow this notebook.
Lastly, to make things simple, I am not going to try and visualize the specific routes I took. However, I have found a usable geographic dataset (shape file) of Netherlands train routes (from here), and therefor we will plot this next to my trajectories simply for reference. For more on how to import spatial files into Tableau see here.
Dependencies¶
import pandas as pd
Import NS Dataset¶
df = pd.read_csv('data/reistransacties-a21a7c18-b5e2-4f67-8a68-1c76c3ebdda8-1555156489-25d6adc3-1680-49a1-bea7-4f7d15eedf35.csv')
df.head()
Create Unique Path ID for each Trip¶
df.loc[:,'path_id'] = df.Vertrek+'-'+df.Bestemming
Split Dataset in Two, one for each Location in Path¶
df1 = df.copy()
df1 = df1[df1.Transactie == 'Reis'][['Datum','Check in','Vertrek','Check uit','path_id']]
df1 = df1.rename(columns={'Vertrek':'Location'})
df1.loc[:,'path_order'] = 1
df1.head()
df2 = df.copy()
df2 = df2[df2.Transactie == 'Reis'][['Datum','Check in','Bestemming','Check uit','path_id']]
df2 = df2.rename(columns={'Bestemming':'Location'})
df2.loc[:,'path_order'] = 2
df2.head()
Create Coordinates of Station Locations¶
list(df.Bestemming.replace(' ',pd.np.nan).dropna().unique())
# create this list in Excel
Import Station Coordinates¶
df_coordinates = pd.read_excel('data/NS_Master.xlsx', sheet_name='Coordinates')
# here I print all coordinates i have already collected for your reference
df_coordinates
JOIN Coordinates and UNION¶
df1 = pd.merge(left=df1, right=df_coordinates, how='left', on='Location')
df2 = pd.merge(left=df2, right=df_coordinates, how='left', on='Location')
df_out = pd.concat([df1,df2], axis=0)
df_out.head()
Export¶
df_out.to_csv("data/ns_tableau.csv", sep=',')
Tableau Dashboard¶
%%html
<div class="d-flex justify-content-center">
<div class='tableauPlaceholder' id='viz1556362972158' style='position: relative'><noscript><a href='#'><img alt=' ' src='https://public.tableau.com/static/images/NS/NSVisualizeMijnReisHistorie/NSReisHistorieDashboard/1_rss.png' style='border: none' /></a></noscript><object class='tableauViz' style='display:none;'><param name='host_url' value='https%3A%2F%2Fpublic.tableau.com%2F' /> <param name='embed_code_version' value='3' /> <param name='site_root' value='' /><param name='name' value='NSVisualizeMijnReisHistorie/NSReisHistorieDashboard' /><param name='tabs' value='no' /><param name='toolbar' value='yes' /><param name='static_image' value='https://public.tableau.com/static/images/NS/NSVisualizeMijnReisHistorie/NSReisHistorieDashboard/1.png' /> <param name='animate_transition' value='yes' /><param name='display_static_image' value='yes' /><param name='display_spinner' value='yes' /><param name='display_overlay' value='yes' /><param name='display_count' value='yes' /><param name='filter' value='publish=yes' /></object></div> <script type='text/javascript'> var divElement = document.getElementById('viz1556362972158'); var vizElement = divElement.getElementsByTagName('object')[0]; if ( divElement.offsetWidth > 800 ) { vizElement.style.width='100%';vizElement.style.height=(divElement.offsetWidth*0.75)+'px';} else if ( divElement.offsetWidth > 500 ) { vizElement.style.width='100%';vizElement.style.height=(divElement.offsetWidth*0.75)+'px';} else { vizElement.style.width='100%';vizElement.style.height='2027px';} var scriptElement = document.createElement('script'); scriptElement.src = 'https://public.tableau.com/javascripts/api/viz_v1.js'; vizElement.parentNode.insertBefore(scriptElement, vizElement); </script>
</div>
