Background

Exploring the publicly available reporting through the Tax and Trade Bureau about the beverage alcohol industry.

Motivation is in the development of a startup that provides monthly analysis and reporting on this data in order to make it available for business decisions by those in the wine industry.

Primary focus is on data acquisition and conversion into a repeatable process that provides easy to consume data for the development of reporting deliverables.

Data Acquisition and Issues

• Data is published monthly at TTB
• Reports are only available in PDF format
• Only provide data for last month and calendar year to date
• Beer/Spirits/Wine data is all separate
• Attempted to leverage Tabula to scrape PDF but still ran into issues
• Had to manually clean up much of the data into a consistent CSV format

Data Loading

• Kept data in format similar to PDF (easier to validate to source)
• Measure is a column itself
• Leveraged pgAdmin3 to script data loading

DELETE FROM land_wine_ttb;
COPY land_wine_ttb
FROM '/Users/mmoramarco/Projects/cuny/is607_data_acquisition/Final Project/ttb_wine.csv'
WITH DELIMITER ','
CSV HEADER;

Data Validation

• Used RPostgreSQL package to directly query database from within R

library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname="ttb_data")

• Example data

wine_rs <- dbSendQuery(con,"SELECT * from land_wine_ttb LIMIT 3")
db_results <- fetch(wine_rs)
print(db_results)

##     date                                         measure     month
## 1 201210    Still Wine Bulk Removed From Fermentors (WG) 209020808
## 2 201210     Still Wine Bullk Increse by Sweetening (WG)   2422597
## 3 201210 Still Wine Bulk Increase by Adding Spirits (WG)   1822963
##   monthprioryear       ytd ytdprioryear
## 1      211136221 437750041    426968365
## 2        2689228  21485301     22457992
## 3        2050951   8348775      7869998

• Compared results from SQL queries to manually loading data into R

Preliminary Analysis

• After loading/validating data loads into PostgreSQL ran several queries to bring consumption data into R
• Needed to convert data into number of servings (source data in barrels, wine gallons, and proof gallons)
• Leveraged dplyr, reshape2, and ggplot2 to manipulate and graph the data

Result Plot

ggplot(share_melt, aes(x=variable,y=value,fill=variable)) +
  geom_bar(stat="identity") +
  xlab("Category") + ylab("Change in % Share") +
  ggtitle("Change in % Share of Consumption")

• Wine is increasing share of consumption to the detriment of both beer and spirits

Conclusion and Next Steps

• RPostgreSQL is a valuable package for working directly with database tables (Easier than having to reload data in R)
• Can leverage SQL queries to manipulate/select data
• Simplifies new data acquisition as new data sets can be easily appended to database tables
• Can also offload hosting of the database (to AWS perhaps) which would allow multiple people to access a consistent set of data
• Update database tables so that each measure is its own column
• Create standardized reporting deliverable on top of dataset