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)
Pandemic Upswing
Top Bottle Sold by Distinct Location
Top Bottle
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();
}
}
}
} |
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();
}
}
}
}
