Bobby Kozub
EPPS 6354
Spring 2023
A Shiny application utilizing a PostgreSQL database to help
you plan your favorite restaurant and fast food dishes at home
AT HOME!
W E H A V E . . .
Overview
Initial Goal was a grocery store price comparison tool.
Due to website countermeasures, this was not possible.
“We Have…At Home!” is an R Shiny application that
allows users to find the ingredients needed to make
their favorite dishes utilizing allrecipes.com and fast-
food restaurants such as Whataburger and McDonalds.
Uses a PostgreSQL database created in pgAdmin.
Data scraped with ParseHub.
Hosted on an AWS RDS instance.
UI Created with R Shiny.
Data Collection
Data collected using a web scraping application called
ParseHub.
Requires no coding.
Create templates that iterate through web pages and gather
the specified information (pagination).
Data can be exported and downloaded in various formats
such as CSV, Excel, or JSON.
Data exported as CSV.
Can automatically create an API with paid version.
Clean/Format Data
Data cleaned and formatted with R.
Combine data.
Add columns.
Separate data (ingredients from main table).
Remove undesirable words or characters.
Schema
Database Creation
CREATE TABLE public.allrecipes_combined (
unique_id character varying(255),
recipe_name character varying(255),
recipe_url character varying(2048),
recipe_description text,
recipe_ingredients text,
recipe_calories integer,
recipe_fat character varying(255),
recipe_carbs character varying(255),
recipe_protein character varying(255),
scrape_date date,
category character varying(255)
);
CREATE TABLE public.allrecipes_ingredients (
unique_id character varying(10),
ingredient character varying(100)
);
CREATE TABLE public.heb_combined (
item_id character varying(255),
item_name character varying(255),
item_url character varying(2048),
item_price character varying(255),
scrape_date date,
category character varying(255)
);
CREATE TABLE public.mcdonalds_cleaned (
mcd_id character varying(255),
mcd_category character varying(255),
mcd_cat_url character varying(2048),
mcd_name character varying(255),
mcd_url character varying(2048),
mcd_desc text,
mcd_ingredients text,
scrape_date date
);
CREATE TABLE public.mcdonalds_ingredients (
mcd_id character varying(10),
ingredient character varying(100)
);
CREATE TABLE public.whataburger_cleaned (
wb_id character varying(255),
wb_name character varying(255),
wb_ingredients text,
wb_price_cal character varying(255),
wb_calories integer,
scrape_date date
);
CREATE TABLE public.whataburger_ingredients (
wb_id character varying(10),
ingredient character varying(100)
);
Tables/Schema Created using SQL query.
After tables are created, data from CSVs can be copied over
either using a SQL command or by right clicking the table in
pgAdmin and selecting "import/export data".
Shiny Application
Connects to AWS instance.
UI designed to have drop down
menus, tabs, and search
functionality.
Select from the three main tables:
AllRecipes, McDonalds, and
Whataburger.
Choose recipe or item.
Embedded SQL queries inside the
server function for loop to obtain
grocery item information in 2nd tab.
Additional item information stored
in 3rd tab.
Conclusion
Despite initial challenges, an application was successfully launched.
Educational experience that deepened understanding of R Shiny,
Postgres, and cloud environments.
Inspiration for future projects.
