MAP Data Visualization

For my 1st bog post I will cover data visualization in Excel and Google Fusion Tables (Heat maps). As a Data Analyst, the person must be acquainted with many software tools. Each project should start with these steps:

  1. What question needs to be answered with the project
  2. What data is available, what data will be needed
  3. What software tools are best for the project
  4. What is the best way to present the project

For the Irish Census project the objective the project is to produce the report showing population by county and product the Irish population heat map.

As a data source I had table with the 2011 Census data:

http://www.cso.ie/en/statistics/population/populationofeachprovincecountyandcity2011/

At first glance this table is fine, but only at 1st glance. As a report it is fine, but as data source it needs to be transformed. Just to name few of the data issues;

  • First column contains multiple data types, City, County, Province,
  • County names are not consistent
  • Males and Females are in the same Row
  • Total persons is static field

I have chosen Excel to transom the data. The final data table looks like this:

County Province Gender QTY
Carlow Leinster Male 27431
Carlow Leinster Female 27181
Cavan Ulster Male 37013
Cavan Ulster Female 36170
Clare Munster Male 58298
Clare Munster Female 58898

The data is broken down, by County, Province, Gender and QTY of persons.

Second data source is Google maps coordinates for the Irish Counties:

http://www.independent.ie/editorial/test/map_lead.kml

To achieve the goal of the project these two data sources need to be merged. To achieve this Google Fusion tables is one of the best tools to use. To create Heat Map few simple steps need to be taken:

  • Upload County boarder data to Google fusion tables
  • Upload Excel file with the County population data
  • Merge these two (common fields are County names)
  • Tweak the report presentation. Add the “buckets” to colour code counties by the population quantity in each county.

 

Heat Map is one the best way for the customer to consume the map data report. The same report can be presented in the table summary. Excel is in the league on its own when it comes to reporting.

County Population % Share Province Population % of Population
Carlow 54,612 1.21% Connacht 542547 12.06%
Cavan 73,183 1.63% Leinster 2504814 55.69%
Clare 117,196 2.61% Munster 1155655 25.69%
Cork 519,032 11.54% Ulster 294803 6.55%
Donegal 161,137 3.58% Grand Total 4497819 100.00%
Dublin 1,273,069 28.30%
Galway 250,653 5.57%
Kerry 145,501 3.23%
Kildare 210,312 4.68%
Kilkenny 95,419 2.12%
Laois 80,559 1.79%
Leitrim 31,798 0.71%
Limerick 191,809 4.26%
Longford 39,000 0.87%
Louth 122,897 2.73%
Mayo 130,638 2.90%
Meath 184,135 4.09%
Monaghan 60,483 1.34%
Offaly 76,687 1.70%
Roscommon 64,065 1.42%
Sligo 65,393 1.45%
Tipperary 70,322 1.56%
Waterford 111,795 2.49%
Westmeath 86,164 1.92%
Wexford 145,320 3.23%
Wicklow 136,640 3.04%
Grand Total 4,497,819 100.00%
Irish population map according to 2012 census
Irish population map according to 2012 census

Other uses of this data

 

With the current data we could calculate the population density in each County. This data can also be used in conjunction with other data sources, for instance graduates in each county, to get comparison of each county comparing the % of third level graduates. Getting number of schools in county calculate student distribution per school and % of recent graduates attending 3rd level.

Get coordinates of international Airports, to find out how far on average people need to travel to the nearest airport.