Geospatial analysis is becoming more and more prevalent in the field of analytics.  Maps are invaluable for visualization and trend discovery.  Perhaps more importantly they can bring an instant WOW factor to your next report or presentation.

This article provides a brief tutorial on how to join excel data to a map using ArcGIS, then display the results in a stimulating manner.

Ask yourself, what looks better…

Here’s the scenario – Your boss wants to know where to open a new dealership.  You decide to show a count of customers for each zip code within 100 miles of the current dealership to visually represent potentially prosperous new locations.

You will need two files to complete this operation.

Assuming that customer data is stored within an excel spreadsheet, that will be the first.

Then you will need a . SHP (ARCGIS format) file for the area in question that contains zip code map data.  For this example we will use a map of North Carolina, focusing specifically on the area around Raleigh / Durham.

If you don’t have the appropriate .SHP file on hand there are several online resources that provide, map data for download.  I often use the resources listed at North Carolina State’s Library Site to find the map files I need.

Make sure to find a map that contains a field that is also contained in your excel file.  For this example I will join the two tables on the common attribute of zip code.

Once you’ve found your files,  open ArcGIS, start a new project and click the     on the top toolbar to add the .SHP zip code file.

You’ll see the map of your chosen area now appear.

 Pre-join Tasks

ArcGIS requires that the format of the common join field be the same for each table.  Right click on the table name in the layers window then select properties => Fields.  There you can view the the type column to see if the join variable is character, numeric or otherwise.

Now that you know the join variable type in the SHP file, return to your excel sheet and do the appropriate functions and calculations to obtain summary values.  For this example, I used the count function to obtain the frequency of customers for each North Carolina  zip code.

Create an excel sheet that has the appropriate data (join field, summary value).  Be very careful to create the join field as the same type as the equivalent field in your map file.  If you need to convert numeric data to character, you can use this function.

=text(startposition, “string length”)

startposition is the first cell in your excel that will be transformed.

String length is a placeholder for how long your text string will be after conversion.  For our example using  zip codes, the string length would be “00000″ for the five number zip codes.  Make sure to include the quotes in your string length statement.

Please also make sure that your excel has a first column called objectid.  Object id should have ascending numeric values starting at 1.  You should also make sure that none of your column names contain a special character, have a space or start with a number.

Join Execution

Now that the excel is properly munged we want to join it to the map in ArchGIS.  This can be accomplished by  right clicking on the layer name and selecting join.

Now you need to define the join.  In the first field select the common variable (zip code) from the list.  In the next field navigate to your spreadsheet.  Finally, in the last field pick the common field in the spreadsheet.

 Click OK and the excel data should be correctly associated with your map layer.

Time for a housekeeping task.  Right click on the layer name (export_output2)

 Choose open attribute table then right click on the column name of your summary column (in our case customer_frequency).  Select properties from that menu then click the numeric button.

Once selected, define the number of decimal places to be zero.  By default, Arc may define too many decimal places making your report look sloppy.

Color Gradation

Now that we’ve joined the sales data to our map, let’s display it in a visually stimulating and communicative manner.  We’ll shade different regions of our map by value of the excel value so people can understand the distributions of the quantity of interest at a glance.

Close the attribute table and again right click on the layer name.  Choose properties once more and navigate to the symbology tab on the top of the window. Choose the options as show below.  Quantities =>Graduated Colors.  Select the excel attribute from the value drop-down and the color ramp of your choosing.

This should give you a full blown map!  See my other post to add city name labels, an attractive legend and bold map title.