Pivot Tables functionality has been around for decades but people often overlook this powerful tools for managing and summarizing tabular data. Pivot tables (also known as Crosstabs) is available in MS Excel or Access, Google Sheets, Filemaker Pro, LibreOffice Calc. It is also found in GIS software such as ArcGIS Pro (Pivot Table toolbox with Advanced license) and QGIS 3 (GroupStats plugin), or Python Pandas (pivot_table)
Key concepts presented in this workshop:
- Granular. Pivot tables work best when summarizing granular data. Acquire individual records, artifact or sample-level data for the pivot table. Already summarized data will not work well.
- Numerical is best. Pivot Tables work well when aggregating numerical data. Generally it's of limited use with nominal data (counts only). Sometimes one must create a fake numerical column in order to use it as "data". This could be an incrementing (Fill Down) column in Excel that's then introduced as the Data attribute in an Excel Pivot Table
- Aggregate by provenience. Pivot Tables can be very useful when linking tabular lab results with spatial data because lab data is often more granular than the spatial resolution. Think of this as a 1:Many relationship (1 spatial point for Many artifacts). For example if your GPS is only accurate to ~2m accuracy but you have artifact surface analysis information on a individual artifacts you can use Pivot Tables to collapse this 1:Many relationship to the associated GPS point. Perhaps generate a pie chart accounting for all the lithic material types in proximity to a GPS point and display the pie on top of the GPS point. In an excavation you may have many flakes from a single excavation provenience unit like a Locus or a quad and Pivot Table and Pivot Chart can be used to summarize by provenience.
Some valuable elements of Pivot tables covered in the workshop:
- how to show multiple levels of rows or columns in a pivot table
- how to show counts and percents side by side
- the relationship between Pivot Charts and Pivot Tables in MS Excel
- how pivot tables can be used to "flatten" a 1:Many relate for mapping purposes. These relationships between spatial data and non-spatial tables can be simplified into a 1:1 and then joined to a spatial dataset.
We will use Pivot Tables to examine some data from archaeological survey data plotted in fake locations near ARF for this workshop.
Attachments:
