ArcGIS Pro

Pivot rows of long tabular data to columns for joining to features

We’ve all been there, we open up a data table for the first time, only to see that it is “stacked” or “long” – meaning that it has more than one record per geography. Why are so many tabular datasets usually long? Good relational databases are set up this way so that adding new years’ worth of data doesn’t require a schema change of adding a new field. Also, additional rows are often smaller in file size than additional columns in most database programs.

GIS vector data works well when one record in the table corresponds to one point, line, or polygon on the map. We don’t want duplicate features on our map, or duplicate features slowing down our processing time with redundant geographic information. If a dataset has multiple dimensions (multiple years or categories, in addition to multiple locations), GIS requires a “wide” dataset in which those multiple years or categories are different attribute fields.

Transform long data to wide data with the Pivot Table tool

Fortunately, there is a tool called Pivot Table in ArcGIS Pro’s Data Management toolbox that is designed to transform long data to wide data. As the tool’s name implies, it pivots rows into columns to reshape a table so that it is more suitable for joining to features based on a common attribute.

Picture of two small tables, one has multiple rows for the same geography but different years. An arrow from that table to the second, which has years as columns instead of rows.

To start, make sure your table is in a geodatabase (.gdb). You can use the Excel to Table or Table to Table conversion tools to bring in your table to a GDB within your project.

Example 1: data with multiple years

Here I have a table of values of opioid pills purchased by pharmacies from suppliers summarized by state. When sorted by state, you can see that there are seven years of data in this table.

Table with three fields: state, year, and pills. Each state has 7 rows, one for each year (2006 - 2012).

I want to pivot the rows in this table to columns so that each year is an attribute field. The dialog box for this tool asks for five parameters:

  1. Input table
  2. Input field
  3. Pivot field
  4. Value field
  5. Output table

I’ll put in the table I’m currently working with as my input table, and the field that I want to remain as rows as my input field. The field I want to pivot is the year field, which will become new attribute fields, so I’ll put year in for the pivot field. The field I’m most interested in is the value field, in this case, the pills field:

Geoprocessing tool dialog box.

After running this tool, I get a new table that has one row per state, one field for each year, and the value in the pills field is appropriately filled in to the cells. I no longer have a field called “pills.”

A table with one row per state, and multiple fields - one fore each year.

You may notice that the new table has slightly longer field names. The years (or other numbers) are now part of the field name here since year is no longer its own column. Many software programs including ArcGIS do not let you start field names with numbers, which is why the numbers need to be a suffix, e.g. “year2019” instead of “2019.”  If you will be publishing to ArcGIS Online, be sure to put the year in the field aliases too, so that it appears in your legends.

Example 2: data with multiple categories

Some stacked datasets are long because they contain values for multiple categories for each geography. The Pivot Table tool works just as well for string values as it does for numeric values such as years. For example, here I have a table of the opioid pills purchased by pharmacies summarized by state, but instead of values by year, I have values by drug type (hydrocodone vs. oxycodone).

A table with three fields: state, drug type, and pills. Each state has two rows (one fore each drug type).

Here my pivot field is drug_name instead of year:

Geoprocessing tool dialog box.

The unique strings within the pivot field then become my new attribute fields, very similar to the new year fields before:

A table with three rows: state, hydrocodone, and oxycodone. One row for each state.

Next steps: join, analyze, map!

Now that your table has one record for each point, line, or polygon you’d like to join to, run the Join Field tool to jump start your analysis and mapping!

Besides making your data easier to join to features, another advantage to working with data that is wide (many columns rather than many rows) is that it’s easier to calculate change over time or the difference between two categories with a simple field calculation. These new calculated fields can be mapped as well, allowing you to further explore and represent your data!

For more info on the Pivot Table tool, see the tool’s official help page.

Note: Pivot Table is available with the Advanced license of ArcGIS Pro.

About the author

Diana loves working with data! She has over a decade of experience as a practitioner of demography, sociology, economics, policy analysis, and GIS - making her a true social science quantoid. Diana holds a BA in quantitative economics and an MA in applied demography. She has been with Esri as a product engineer on Esri's Living Atlas and Policy Maps teams since 2017. Diana enjoys strong coffee and clean datasets, usually simultaneously.

Next Article

StoryMaps Live! Join us for a new webinar series

Read this article