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 hyderabad_wards.json

I generated pseudo-random data for employee locations as described in this post. Download the employee.json containing this data.

Procedure

Drag and drop the employee.json and 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 hyderabad-wards-2019-3-OnlyWeekdays-HourlyAggregate.csv. Enter 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.