In this seemingly simple couple of charts, I demonstrate a few nifty tricks that are fun and effective. First, we want to find out how many cats and dogs in a few selected cities (around me) and come with an educated estimate based on data. Then, we will use some cool, free add-in to create charts to show the results. Let’s get started.
So, how would we go around figuring out the numbers of dogs and/or cats in a city?
We can get information such as area and population using Geography data type in Excel. Once a city is entered in a cell, e.g. Seattle, we can change its data type to “GEOGRAPHY”.
This is available from Data menu as shown above. Once that’s done, we can click on the cell and get its freely, easily available statistics such as population and area. Ok, that’s easy but we need some information on pets too. So, heading over to American Veterinary Medical Association (AVMA) site, we can find data such as percentage of households that have dogs, cats (and other ‘companion’ animals)! This is great: now we see (data updated through 2018) that 38.4% of the households have dogs, and 25.4% have cats. (0.7% have horses! But we’ll leave that for another day). They keep track of these things, you know. We’re not talking illegal or stray animals here.
We have city population, we have households numbers owning pets (for dogs and cats…and yes, horses too). Obviously then we need to figure out now is how many are considered Households in a city? Well, for that we roll over to Census data. We find that there 2.6 people in a household in the city. So, we have all the basic information to get started. The table below summarizes all that and calculates per city the required information (note: I added population density and area in square miles from square kilometers, which we won’t be using in this particular example, so you can ignore those columns for now).
To calculate the number of total cats, we derive multipliers to get to total number of dogs and cats, which are 0.614 and 0.457 respectively based on the data from AVMA because 61.4% of total households have dogs and 45.7% of households have cats and 2.6 households make up the total population. We’re done with arranging the data! Now the fun part of charting…
First, let’s do a chart on cats ownership by the city. I could have done a more geeky chart but it’s the age of social media and simple infographics are the rage these days. Very few can comprehend (or willing to…) complex nerdy charts, so here’s a cool chart showing a scalable graphic on cats populations in cities:
Next, let’s do a chart with Bing Map! Showing exact city locations in an interactive map (zoom in/out, change terrain views, scroll to other places, click on specific cities, etc.) that shows the dog and cats ownerships in these cities AND in an in-place pie-chart! How cool is that?
As I said, this is interactive. For example, you could click on any of the pies and see its specific metrics:
Well, I hope it was as educational as it was fun and relaxing for you. The best part of this is that it’s completely scalable to any city, even country, for any time-period.
What else will you do with the pet stats and charts?
Cheers.
