Using Pivot Tables to Validate StreetLight InSight’s Origin-Destination Findings (Tutorial)

If you are running Origin-Destination analysis on StreetLight InSight’s platform, but find that the results don’t align with your prior knowledge or hypothesis, you can use Microsoft Excel’s Pivot Tables to validate your findings. This tutorial will show you how to create a Pivot Table to display morning commute travel volume for an average day from San Francisco’s Marina neighborhood to five city neighborhoods.

For this tutorial you will need Microsoft Excel (we will use Excel 2016 in this tutorial), and our example data, which you can download here.

Click on “SF_Area_od_all.xlsx”, and then click the down arrow in the top right to download the spreadsheet.

Step One: Understanding the Data

Open the “SF_area_od_all.xlsx” file in Excel, so we can take a look at the fields and what each represents. I will describe them in layman’s terms for this tutorial. If you want to read the technical definitions, please consult the “README_OD.txt” file here.

The fields we are looking at are columns C, H, L, M, and N.

Let’s look at the fields that will be useful for our Pivot Table.

1. Origin Zone Name (OZN): The name of the neighborhood or area where a given trip starts.
2. Destination Zone Name (DZN): The name of the neighborhood or area where a given trip ends.
3. Day Type: There are three types of days: day, weekday, and weekend.

• “1: Average Weekday (M-Th)” looks at trips taken Monday Through Thursday.
• “2: Average Weekend Day (Sa-Su)” look at trips taken on Saturday and Sunday.
• “0: Average Day (M-Su)” looks at all trips taken.

4. Day Part: There are six types of day parts. They’re pretty self explanatory.

• “0: All Day (12am-12am)” looks at trips started during all 24 hours of the day.
• “1: Early AM (4am-6am)” looks at trips started between 4 am and 6 am.
• “2: Peak AM (6am-10am)” looks at trips started between 6 am and 10 am. This is used for morning commute patterns.
• “3: Mid-Day (10am-3pm)” looks at trips started between 10 am and 3pm.
• “4: Peak PM (3pm-7pm)” looks at trips started between 3 pm and 7 pm. This is used for evening commute patterns.
• “5: Late PM (7pm-12am)” looks at trips started between 7 pm and 12 am.

5. O-D Traffic (StL Index) This field tells us the number of trips started at the OZN that will end at the DZN for the given day type and day part. Let’s look at an example on row 21. Between 4 and 6 am on an average day, 28 cars can be expected to start travel from the Financial District to the Marina.

I have hidden some unneeded fields to make the data more readable.

Other Fields of Note:

• Origin Zone Traffic (StL Index): This field tells us the number of trips started at the OZN for the given day type and day part, without regard to the destination.
• Destination Zone Traffic (StL Index): This field tells us the number of trips ended at the DZN for the given day type and day part, without regard to the origin.

Please keep in mind that the Origin Zone Traffic value has no relation to the Destination Zone Name, and the Destination Zone Traffic value has no relation to the Origin Zone Name.

The other fields will not be used for this tutorial. However, you can read more about them in the “README_OD.txt” file found here.

Step Two: Creating Our Pivot Table

Now that we have taken a look at our data, let’s go back to the analysis we want to perform. We want to look at the morning commute patterns on an average day from the Marina to the five neighborhoods provided in our spreadsheet. Let’s break this down by field.

• Origin Zone Name: “Marina”.
• Destination Zone Name: We will use all five neighborhoods (zones) here.
• Day Type: “0: Average Day (M-Su)”
• Day Part: “2: Peak AM (6am-10am)”
• O-D Traffic: These will provide the values of our analysis.

Let’s create our pivot table. At the bottom of the program towards the left, click on the circled plus (+) button to create a new sheet.

Click that plus button on the bottom left.

A new sheet will appear. Double click “Sheet1” near where you clicked the plus button to rename it to “Marina O-D Pivot Table”.

Click the A-1 Cell in the top left of our new sheet, then go to the menu bar and click Insert, then click PivotTable.

Insert -> PivotTable.

This will open up the Create PivotTable dialog box. Make sure “select a table or range” and “existing worksheet” are selected, then go back to your original sheet by clicking “SF_Area_O_D_od_all” in the bottom left. Click, hold, and drag your cursor from the letter “A” column above your data all the way to the letter “P”. Alternatively, you can type “SF_Area_O_D_3787_od_all!$A:$P” for “Table/Range”. Press OK.

When your dialog box looks like this, press OK to create the Pivot Table.

It should now look like this.

Step Three: Setting Up the Pivot Table

To properly set up our newly created Pivot Table, let’s choose the fields to examine. On the right hand side our our sheet, we see a new area titled “PivotTable Fields”. Mark the checkboxes for the fields “Origin Zone Name”, “Destination Zone Name”, “Day Type”, “Day Part”, and “O-D Traffic (StL Index)”. Don’t worry if the Pivot Table looks strange for now.

The checkboxes are correct; now we need to reorganize that Pivot Table.

As we only want to look at trips (1) starting at the Marina, (2) on an average day, and (3) for the morning commute, we can filter those results. In the PivotTable Fields box, click, hold, and drag the dropdown for “Origin Zone Name”, from Rows above to Filters. Click, hold, and drag “Day Type”, and “Day Part” to filters below “Origin Zone Name”.

Now we can set up our filters.

Click the dropdown arrow in cell B1 and select “Marina”, which will become highlighted in blue. Press OK. Repeat for cells B2 and B3, selecting “0: Average Day (M-Su)” and “2: Peak AM (6am-10am)”, respectively.

Filtering the Origin Zone Name so that we are only looking at trips originating from the Marina.

Double Click on “Row Labels” and rename it to “Destination”. Double click on “Sum of O-D Traffic (StL Index)”, renaming it “Travel Volume” in the “Custom Name” field.

The completed pivot table.

And we are done! We can see that 2,926 trips are conducted on an average day’s morning commute to the five neighborhoods, including 1,119 intra-neighborhood trips within the Marina.

Should you want to look at other findings, such as different neighborhood origins, day types, or day parts, just edit the filters as you wish.

If you want to look at neighborhoods’ origin traffic volume to a certain destination, remove the “Origin Zone Name” from filters by clicking the down arrow and pressing “Remove Field”, and replacing it with the “Destination Zone Name” instead, selecting the neighborhood of your choice.