A look into Iowa’s liquor sales for the last 2.5 years. Time spans from January 2018 through June 2020. The data can be found at data.iowa.gov. Click on images below to enlarge.
Distinct Coordinate by Volume Sold (Liters)
Top Bottle Sold by Distinct Location
Top Category Per Distinct Location
C# Console Application was used to parse, aggregate, and output the data into desired structure for Google Charts and Carto. The script below can be used as a reference if you would like to investigate Iowa’s liquor sale data yourself. Note that this particular script will only output the data source of the last map in this post (Top Category Per Distinct Location sized by Bottles Sold).
using CsvHelper; using System; using System.Collections.Generic; using System.Globalization; using System.IO; using System.Linq; using System.Text; using System.Text.RegularExpressions; using System.Threading.Tasks; namespace IowaSpirits2018plus { class Program { static void Main(string[] args) { Console.WriteLine("Iowa Spirits.."); Console.BufferHeight = 4000; Dictionary<string, Dictionary<string, int>> dictCoord_Item_Count = new Dictionary<string, Dictionary<string, int>>(); string lastdate = ""; using (var reader = new StreamReader("C:\\IOWASPIRITS\\iowaspirits2018plus.csv")) using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture)) { csv.Read(); csv.ReadHeader(); while (csv.Read()) { string date = csv.GetField("Date").ToString(); if ( lastdate != date) Console.WriteLine(date); lastdate = date; DateTime fDate = csv.GetField("Date"); string city = csv.GetField("City").ToString(); string categoryName = csv.GetField("Category Name").ToString(); string bottlesSold = csv.GetField("Bottles Sold").ToString(); int iBottlesSold = int.Parse(csv.GetField("Bottles Sold").ToString()); string litersSold = csv.GetField("Volume Sold (Liters)").ToString(); string itemDescription = csv.GetField("Item Description").ToString(); string storelocation = csv.GetField("Store Location").ToString(); storelocation = Regex.Replace(storelocation, "POINT \\(", ""); storelocation = Regex.Replace(storelocation, "\\)", ""); storelocation = Regex.Replace(storelocation, " ", ","); if (dictCoord_Item_Count.ContainsKey(storelocation)) { Dictionary<string, int> existingDict = new Dictionary<string, int>(); existingDict = dictCoord_Item_Count[storelocation]; if (existingDict.ContainsKey(categoryName)) { int existingBottleSold = existingDict[categoryName]; existingBottleSold += iBottlesSold; existingDict[categoryName] = existingBottleSold; } else { existingDict.Add(categoryName, iBottlesSold); } dictCoord_Item_Count[storelocation] = existingDict; } else { Dictionary<string, int> tempDictItemCount = new Dictionary<string, int>(); tempDictItemCount.Add(categoryName, iBottlesSold); dictCoord_Item_Count.Add(storelocation, tempDictItemCount); } } } foreach ( string parent_key in dictCoord_Item_Count.Keys) { Console.Write(parent_key + ","); foreach (var item in dictCoord_Item_Count[parent_key].OrderByDescending(r => r.Value)) { Console.Write("{0}, {1}", item.Key, item.Value); break; } Console.WriteLine(); } } } } |
Is there a reason you have Fireball Cinnamon Whiskey and 5 O Clock something twice on your sideways bar chart?
The aggregates are based on distinct Item Description, and ordered by most bottles sold. “Whiskey” != “Whisky” and “O Clock” != “O clock”. Are they different bottles? Probably not. However, there are thousands of unique item descriptions and consolidating for duplicates would be a daunting task. Send iowa.gov an angry email about this.