Validating StreetLight InSight’s Origin-Destination Findings with Pivot Tables (Tutorial) (August 2021 Revision)

This tutorial will show you how to use pivot tables to validate your Origin-Destination findings from InSight. For this tutorial, we will be conducting an example analysis. We will validate morning commute travel volume for an average day from San Francisco’s Marina neighborhood to five other neighborhoods.

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

Right Click on “SF_Area_od_all.xlsx” to get these options.

Right 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. The fields we will use are columns C, H, L, M, and N. Double click the grey line to the right of these column headers -this will expand the columns for better readability.

The arrows show the fields we are working with. Double click the grey lines circled in red to expand each column.

Let’s look at the fields that we expanded.

1. (C) — Origin Zone Name (OZN): The name of the neighborhood or area where a given trip starts.
2. (H) — Destination Zone Name (DZN): The name of the neighborhood or area where a given trip ends.
3. (L) — Day Type: The 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. (M) — Day Part: The hours of the day, grouped by type of commute. The hours are in parentheses.

• 0: All Day (12am-12am)
• 1: Early AM (4am-6am)
• 2: Peak AM (6am-10am)
• 3: Mid-Day (10am-3pm)
• 4: Peak PM (3pm-7pm)
• 5: Late PM (7pm-12am)

5. (N) — O-D Traffic (StL Index) This field tells us the number of trips started at the origin that will end at the destination 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 and end at the Marina.

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

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 will use a pivot table, which is a tool to aggregate our data. 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 in 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 A1 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.

Your spreadsheet should now look like this, with the PivotTable FIelds sidebar on the right.

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.

Now we will filter the results. We want to look at trips (1) starting at the Marina, (2) on an average day, and (3) for the morning commute. In the Rows box, click, hold, and drag “Origin Zone Name” from Rows above to Filters. Repeat for “Day Type”, and “Day Part”.

Now we need to reorganize that Pivot Table.

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.

Now we can set up our filters using the circled dropdowns.

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

Double Click on box A5 “Row Labels” and rename it to “Destination”. Double click on box B5 “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.