In a previous post, I showed how to use the Uber Movement Travel Times data to create isochrones. In this post, we will explore another use case of this dataset. Say you are concerned about loss of productivity due to long commute times of your employees and wonder if a change in office times might help them get to the office faster. A similar analysis can be done to see if a change in office location will result in better or worse commutes.
Here’s the hypothetical scenario “Given the location of an office and location of homes of employees, determine their current commute times for office timings of 9am-11am and 5pm-6pm. If the office timings were changed to non-peak timings of 7am-8am and 3pm-4pm, what would be the time savings?“
Get the Data
We will use the Travel Times Uber Movement data for the city of Hyderabad. Download the Travel Times by Hour of Day (Weekdays Only) dataset for 2019 Quarter3. This data comes as a CSV file
hyderabad-wards-2019-3-OnlyWeekdays-HourlyAggregate.csv. Also download the Geo Boundaries which has the ward boundaries for Bangalore in GeoJSON format. This will be a file named
Drag and drop the
hyderabad_wards.json into mapshaper.
The travel times data contains times between different wards, so to use that data with the employee location, we need to know which ward is each location. This can be achieved with a spatial join.
join hyderabad_wards fields=MOVEMENT_ID target=employees
Now, drag-and-drop the travel times file
string-fields=sourceid,dstid in the command line options box and click Import.
There are 400k+ records for travel times between each ward and for each hour-of-day (hod).
We assume our hypothetical office is located in zone 39.
Given this information, we can now calculate the current and proposed commute times.
join hyderabad-wards-2019-3-OnlyWeekdays-HourlyAggregate fields=MOVEMENT_ID keys=MOVEMENT_ID,sourceid calc='current_morning = average(mean_travel_time)' where='(hod==9 || hod==10) && dstid==39' target=employees
join hyderabad-wards-2019-3-OnlyWeekdays-HourlyAggregate fields=MOVEMENT_ID keys=MOVEMENT_ID,sourceid calc='new_morning = average(mean_travel_time)' where='(hod==7 || hod==8) && dstid==39' target=employees
join hyderabad-wards-2019-3-OnlyWeekdays-HourlyAggregate fields=MOVEMENT_ID keys=MOVEMENT_ID,dstid calc='current_evening = average(mean_travel_time)' where='(hod==17 || hod==18) && sourceid==39' target=employees
join hyderabad-wards-2019-3-OnlyWeekdays-HourlyAggregate fields=MOVEMENT_ID keys=MOVEMENT_ID,dstid calc='new_evening = average(mean_travel_time)' where='(hod==15 || hod==16) && sourceid==39' target=employees
Each employee point now has 4 attributes with times (in seconds) for their morning and evening commutes. We can calculate total current and total new commute times. We divide by 60 to get the times in minutes and use
Math.floor function to round it to the nearest number.
each 'total_current=Math.floor((current_morning+current_evening)/60), total_new=Math.floor((new_morning+new_evening)/60)'
Now we can calculate the average current and new times for the whole office and estimate the savings. We can use multiple calc statements together to calculate both current and new averages
calc 'average(total_current)' -calc 'average(total_new)' // total_current: 89.68499999999995 // total_new 72.20000000000002 // average savings 17.48 minutes
Given that we have 200 employees, let’s calculate total savings
calc '200*(average(total_current) - average(total_new))/60' // average savings for office 58.28 hours
The analysis says that employees would save 17 minutes on average, resulting in a total of 58 hours of saving for the entire office.