Mapping Phone Data in Four Easy Steps

By Zach Gemignani
October 12, 2007
Find more about: googleearth googlemaps google howto mapping

Have you run into this problem: you have a list of phone numbers and associated values which would be best shown geographically to see patterns, but there isn't a clear way to put the data on a map. Maybe you'd like to see a map of customer service calls by call duration or inbound sales by average order size.

I wanted to share how to MacGyver a solution with a piece of twine, bubble gum, Excel, and a free online map tool. To me, this is a nice testament to the simple but powerful data visualizations that can be accomplished without programming skills or expensive applications.

1. Pull out area codes

First I pulled the area codes from my list of phone numbers using the formula below. This simply checks if the phone number starts with 1, then grabs the appropriate three digits for the area code.

=VALUE(IF(LEFT(E7,1)="1",MID(E7,2,3),MID(E7,1,3)))

2. Convert area codes into states

For my purposes, mapping the phone numbers by state was sufficient. Ideally, we would map the phone numbers to precise latitude and longitude coordinates by doing a reverse lookup of addresses then using the Excel geocoding tool.

First I needed a lookup table that could link my list of area codes to states. I wasn't able to track down a good data table, so I grabbed the data from All Area Codes and cleaned it up. Here is a lookup table of area codes by state.

An aside: I have a pet peeve with people who sell data that feels like it should be publicly available. You'll run across these businesses when looking for basic information about ZIP codes, MSAs, or area codes. Here is an example of one of these parasitic businesses.

Zip code product

3. Create your summary data set

I used a pivot table to summarize metrics by state.

4. Create colorized map of the US

Our friend Ducky Sherwood has generously put together a online tool called Mapeteria that will generate a colorized overlay of US states. In Ducky's words: "Want to make a choropleth thematic map (i.e. coloured based on your data) for Canadian provinces, U.S. states, or French départements?" This overlay can be viewed in either Google Maps or Google Earth.

Here's where it gets a little tricky. You will need to provide Mapeteria with a URL to a properly structured CSV file. Posting a CSV file to a web server isn't trivial if you aren't running your own web site. I found one free service called FileDEN that did the job (other suggestions?). Beware all the advertising—and in all likelihood they immediately sold my e-mail address at registration. Nevertheless, you can upload a file here and it will give you a URL which can be used to create your map.

Here's an example of the results:

State Map

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License. All source code is released under a BSD License unless otherwise specified.

5 comments


October 12, 2007
Tony said:

This is somewhat similar to MS MapPoint with the big difference being this one is FREE!

The difference is this one has a bit more eye-appeal. Maybe it's just the pic, but some of the states in white all blend together. It would be nice to see low-light outlines of the states. Also, what if you don't know your geography too well and need to find Nebraska, but aren't sure which one it is? :)

Also, drill-in functionality would be nice to be able to see within a state what the breakdown is.


October 12, 2007
Eric Moritz said:

I'm sure you know this but the census.gov provides a zip code to lat-long file here:
http://www.census.gov/tiger/tms/gazetteer/zips.txt

Other zip code data can be found here:
http://www.sdc.ucsb.edu/holdings/zip_codes.txt


October 16, 2007
Ken said:

Zach,

Nice article. One thing that may have made things quicker for you would be to use the "Get External Data" functionality in Excel on a website like this:

http://www.bennetyee.org/ucsd-pages/area.html

In fact, I used that table to re-create the process in our data browser (screencast here: http://www.kirix.com/blog/2007/10/16/mr-macgyver-meet-strata/).

Keep up the great work!


October 18, 2007
Bob Chatham said:

Good stuff. I've been using the RegEx 5.5 library to extract area codes, country codes etc. (You need to include a reference in your Excel workbook). Here's a sample VBA function:
----
Public Function regExpMatch(s As Variant, Optional p As Variant, Optional n As Variant) As Variant
'Return the nth match to pattern "p" of a regExp; defaults to 1st match if n is omitted
'If pattern p is omitted, defaults to token: "\s*(?:(\d+)|(\w+)|(.))"
'Return #VALUE error if n is greater than the number of matches
'Return NULL string if no match
'Otherwise, return the matched string
'
'------------- Sample patterns
' Country code of phone number: "^\s*\+\s*(\d+)"

Dim myRegExp As RegExp
Dim myMatches As MatchCollection
Dim myMatch As Match

If IsMissing(n) Then n = 1
If IsMissing(p) Then p = "\s*(?:(\d+)|(\w+)|(.))"

Set myRegExp = New RegExp
myRegExp.IgnoreCase = True
myRegExp.Global = True
myRegExp.Pattern = p
Set myMatches = myRegExp.Execute(s)

If (myMatches.Count = 0) Then
regExpMatch = ""
Exit Function
End If

If (myMatches.Count < n) Then
regExpMatch = CVErr(xlErrValue)
Exit Function
End If

regExpMatch = myMatches(n - 1).Value

End Function
-----

Here's a great free tool that works well with Excel data -- just cut and paste date from Excel into their dialogue box. See tutorial for more options.

http://www.gpsvisualizer.com/
http://www.gpsvisualizer.com/tutorials/waypoints.html

Also, useful geolocation/ZIP tools at:

http://zips.sourceforge.net/#dist_calc


October 29, 2007
Chris Kennedy said:

Thanks for the article. Check out http://pages.google.com/ for random data-hosting needs (esp. for Google Maps).

Your name

Email (optional, will not be shared)

Type the word "juice" (required to confuse the spammers)

Your comment


Add a comment






Joining Juice | Contact Us | Privacy Policy
© 2005—2009 Juice Inc.