Pivot Address-Level Point Data into Polygon Data

By Jack Dougherty, last updated March 30, 2017

Problem: If I begin with address-level point data, how can I transform this into polygon map data?

One solution: In any spreadsheet, split your address data into separate columns (such as Street, City, State), then create a pivot table to aggregate rows into groups (such as the number of addresses in each City or State).

If your location data is combined into one column (example: 300 Summit St, Hartford CT), then see the Spreadsheets: Split Data Columns tutorial in the Clean Your Data chapter of this book.

Here's an example using a long list of US hospitals from the Medicare open data repository, which is already split into separate columns: https://data.medicare.gov/Hospital-Compare/Hospital-General-Information/xubh-q36u

1) Open the link above, see columns of data (Address, City, State, etc.), and click the blue Export button to download in the CSV generic spreadsheet format.

Screenshot: Export US Hospital data into CSV format
Screenshot: Export US Hospital data into CSV format

2) Open the file with any spreadsheet tool, and create a pivot table to count up the number of hospitals in each state. For help, see the Pivot Table tutorial in the Spreadsheet chapter of this book.

Screenshot: Pivot Table of US Hospitals by State
Screenshot: Pivot Table of US Hospitals by State

3) Now you can copy and paste the pivot table raw data of hospitals by US states. See the Normalize Data tutorial and also the Edit and Join Spreadsheet with Polygon Map using Mapshaper tutorial in this book.

Other Solutions

results matching ""

    No results matching ""