library(tidyverse)
library(janitor)Cleaning
Goals of this notebook
The steps we’ll take to prepare our data:
- Download the data
- Import it into our notebook
- Clean up data
- Export the data for the next notebook
Setup
Import data
I am importing the data from the Texas Open Data Portal. The dataset is the Mixed Beverage Gross Receipts, which is collected by the Texas Comptroller of Public Accounts and published on the state data portal, which uses the Socrata service. I filtered the data in Socrata to show 2019 through 2023. I will analyze the last five years of data before 2024. I am not including 2024 because the year is not over, therefore the data would be incomplete and incorrect for this analysis.
# create the object, then fill it with data from the csv
mixedbev_raw <- read_csv("data-raw/Mixed_Beverage_Gross_Receipts_20241112.csv") |> clean_names()Rows: 73721 Columns: 24
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (14): Taxpayer Name, Taxpayer Address, Taxpayer City, Taxpayer State, Ta...
dbl (10): Taxpayer Number, Taxpayer County, Location Number, Location Zip, L...
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.# peak at the data
mixedbev_raw# A tibble: 73,721 × 24
taxpayer_number taxpayer_name taxpayer_address taxpayer_city taxpayer_state
<dbl> <chr> <chr> <chr> <chr>
1 32057827142 ROCKROSE RESTA… 11420 ROCK ROSE… AUSTIN TX
2 32049953949 KLODGE, INC. PO BOX 685084 AUSTIN TX
3 32058372064 EL BORREGO DE … 3900 S CONGRESS… AUSTIN TX
4 32039907699 DESI & FRIENDS… 12700 ENCINO DR MANCHACA TX
5 32039685030 WL STAR BAR, L… 600 W 6TH ST AUSTIN TX
6 17427121425 CASINO EL CAMI… 517 E 6TH ST AUSTIN TX
7 32045024711 SWOON HOSPITAL… 2406 HOMEDALE C… AUSTIN TX
8 32069216045 407 BARLIB LLC PO BOX 685084 AUSTIN TX
9 32055355815 SOUTH FIRST PI… 2901 S 1ST ST S… AUSTIN TX
10 32051844242 EATX COFFEE LLC 22601 STATE HIG… SPICEWOOD TX
# ℹ 73,711 more rows
# ℹ 19 more variables: taxpayer_zip <chr>, taxpayer_county <dbl>,
# location_number <dbl>, location_name <chr>, location_address <chr>,
# location_city <chr>, location_state <chr>, location_zip <dbl>,
# location_county <dbl>, inside_outside_city_limits <chr>,
# tabc_permit_number <chr>, responsibility_begin_date <chr>,
# responsibility_end_date <chr>, obligation_end_date <chr>, …Fix dates
I am using lubridate to create new columns with real dates.
# Fixing dates
mixedbev_date <- mixedbev_raw |>
mutate(
responsibility_begin = mdy(responsibility_begin_date),
responsibility_end = mdy(responsibility_end_date),
obligation_end = mdy(obligation_end_date)
)
# peek at the result
mixedbev_date |> glimpse()Rows: 73,721
Columns: 27
$ taxpayer_number <dbl> 32057827142, 32049953949, 32058372064, 3203…
$ taxpayer_name <chr> "ROCKROSE RESTAURANT L.L.C. DBA MIA ITALIAN…
$ taxpayer_address <chr> "11420 ROCK ROSE AVE STE 120", "PO BOX 6850…
$ taxpayer_city <chr> "AUSTIN", "AUSTIN", "AUSTIN", "MANCHACA", "…
$ taxpayer_state <chr> "TX", "TX", "TX", "TX", "TX", "TX", "TX", "…
$ taxpayer_zip <chr> "78758", "78768", "78704", "78652", "78701"…
$ taxpayer_county <dbl> 227, 227, 227, 227, 227, 227, 227, 227, 227…
$ location_number <dbl> 1, 1, 1, 3, 1, 1, 2, 1, 1, 2, 1, 1, 1, 294,…
$ location_name <chr> "MIA ITALIAN TAPAS & BAR", "THE LODGE", "EL…
$ location_address <chr> "11420 ROCK ROSE AVE UNIT 400", "411 E 6TH …
$ location_city <chr> "AUSTIN", "AUSTIN", "AUSTIN", "AUSTIN", "AU…
$ location_state <chr> "TX", "TX", "TX", "TX", "TX", "TX", "TX", "…
$ location_zip <dbl> 78758, 78701, 78704, 78704, 78701, 78701, 7…
$ location_county <dbl> 227, 227, 227, 227, 227, 227, 227, 227, 227…
$ inside_outside_city_limits <chr> "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y"…
$ tabc_permit_number <chr> "MB927828", "MB848429", "MB630996", "MB1061…
$ responsibility_begin_date <chr> "11/24/2015", "08/08/2013", "10/15/2015", "…
$ responsibility_end_date <chr> NA, NA, NA, "07/12/2023", NA, NA, NA, NA, N…
$ obligation_end_date <chr> "06/30/2021", "06/30/2023", "08/31/2019", "…
$ liquor_receipts <dbl> 22983, 151396, 3324, 12204, 57536, 31245, 5…
$ wine_receipts <dbl> 14304, 128, 0, 3326, 1979, 572, 23573, 9, 3…
$ beer_receipts <dbl> 3953, 24110, 3719, 1610, 36461, 33655, 1084…
$ cover_charge_receipts <dbl> 0, 7219, 0, 0, 0, 0, 0, 7453, 0, 0, 0, 31, …
$ total_receipts <dbl> 41240, 182853, 7043, 17140, 95976, 65472, 9…
$ responsibility_begin <date> 2015-11-24, 2013-08-08, 2015-10-15, 2020-1…
$ responsibility_end <date> NA, NA, NA, 2023-07-12, NA, NA, NA, NA, NA…
$ obligation_end <date> 2021-06-30, 2023-06-30, 2019-08-31, 2022-0…Convert ZIP codes to text
I am converting my two ZIP code columns to text.
# Convert ZIP codes to text
mixedbev_zip <- mixedbev_date |>
mutate(
taxpayer_zip_code = as.character(taxpayer_zip),
location_zip_code = as.character(location_zip)
)
# peek at the result
mixedbev_zip |> glimpse()Rows: 73,721
Columns: 29
$ taxpayer_number <dbl> 32057827142, 32049953949, 32058372064, 3203…
$ taxpayer_name <chr> "ROCKROSE RESTAURANT L.L.C. DBA MIA ITALIAN…
$ taxpayer_address <chr> "11420 ROCK ROSE AVE STE 120", "PO BOX 6850…
$ taxpayer_city <chr> "AUSTIN", "AUSTIN", "AUSTIN", "MANCHACA", "…
$ taxpayer_state <chr> "TX", "TX", "TX", "TX", "TX", "TX", "TX", "…
$ taxpayer_zip <chr> "78758", "78768", "78704", "78652", "78701"…
$ taxpayer_county <dbl> 227, 227, 227, 227, 227, 227, 227, 227, 227…
$ location_number <dbl> 1, 1, 1, 3, 1, 1, 2, 1, 1, 2, 1, 1, 1, 294,…
$ location_name <chr> "MIA ITALIAN TAPAS & BAR", "THE LODGE", "EL…
$ location_address <chr> "11420 ROCK ROSE AVE UNIT 400", "411 E 6TH …
$ location_city <chr> "AUSTIN", "AUSTIN", "AUSTIN", "AUSTIN", "AU…
$ location_state <chr> "TX", "TX", "TX", "TX", "TX", "TX", "TX", "…
$ location_zip <dbl> 78758, 78701, 78704, 78704, 78701, 78701, 7…
$ location_county <dbl> 227, 227, 227, 227, 227, 227, 227, 227, 227…
$ inside_outside_city_limits <chr> "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y"…
$ tabc_permit_number <chr> "MB927828", "MB848429", "MB630996", "MB1061…
$ responsibility_begin_date <chr> "11/24/2015", "08/08/2013", "10/15/2015", "…
$ responsibility_end_date <chr> NA, NA, NA, "07/12/2023", NA, NA, NA, NA, N…
$ obligation_end_date <chr> "06/30/2021", "06/30/2023", "08/31/2019", "…
$ liquor_receipts <dbl> 22983, 151396, 3324, 12204, 57536, 31245, 5…
$ wine_receipts <dbl> 14304, 128, 0, 3326, 1979, 572, 23573, 9, 3…
$ beer_receipts <dbl> 3953, 24110, 3719, 1610, 36461, 33655, 1084…
$ cover_charge_receipts <dbl> 0, 7219, 0, 0, 0, 0, 0, 7453, 0, 0, 0, 31, …
$ total_receipts <dbl> 41240, 182853, 7043, 17140, 95976, 65472, 9…
$ responsibility_begin <date> 2015-11-24, 2013-08-08, 2015-10-15, 2020-1…
$ responsibility_end <date> NA, NA, NA, 2023-07-12, NA, NA, NA, NA, NA…
$ obligation_end <date> 2021-06-30, 2023-06-30, 2019-08-31, 2022-0…
$ taxpayer_zip_code <chr> "78758", "78768", "78704", "78652", "78701"…
$ location_zip_code <chr> "78758", "78701", "78704", "78704", "78701"…Selecting columns
I am now dropping the columns I don’t need.
# Selecting columns
mixedbev_select <- mixedbev_zip |>
select(
taxpayer_number,
taxpayer_name,
taxpayer_address,
taxpayer_city,
taxpayer_state,
taxpayer_county,
location_number,
location_name,
location_address,
location_city,
location_state,
location_county,
inside_outside_city_limits,
tabc_permit_number,
liquor_receipts,
wine_receipts,
beer_receipts,
cover_charge_receipts,
total_receipts,
responsibility_begin,
responsibility_end,
obligation_end,
taxpayer_zip_code,
location_zip_code
)
# peek at the result
mixedbev_select |> glimpse()Rows: 73,721
Columns: 24
$ taxpayer_number <dbl> 32057827142, 32049953949, 32058372064, 3203…
$ taxpayer_name <chr> "ROCKROSE RESTAURANT L.L.C. DBA MIA ITALIAN…
$ taxpayer_address <chr> "11420 ROCK ROSE AVE STE 120", "PO BOX 6850…
$ taxpayer_city <chr> "AUSTIN", "AUSTIN", "AUSTIN", "MANCHACA", "…
$ taxpayer_state <chr> "TX", "TX", "TX", "TX", "TX", "TX", "TX", "…
$ taxpayer_county <dbl> 227, 227, 227, 227, 227, 227, 227, 227, 227…
$ location_number <dbl> 1, 1, 1, 3, 1, 1, 2, 1, 1, 2, 1, 1, 1, 294,…
$ location_name <chr> "MIA ITALIAN TAPAS & BAR", "THE LODGE", "EL…
$ location_address <chr> "11420 ROCK ROSE AVE UNIT 400", "411 E 6TH …
$ location_city <chr> "AUSTIN", "AUSTIN", "AUSTIN", "AUSTIN", "AU…
$ location_state <chr> "TX", "TX", "TX", "TX", "TX", "TX", "TX", "…
$ location_county <dbl> 227, 227, 227, 227, 227, 227, 227, 227, 227…
$ inside_outside_city_limits <chr> "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y"…
$ tabc_permit_number <chr> "MB927828", "MB848429", "MB630996", "MB1061…
$ liquor_receipts <dbl> 22983, 151396, 3324, 12204, 57536, 31245, 5…
$ wine_receipts <dbl> 14304, 128, 0, 3326, 1979, 572, 23573, 9, 3…
$ beer_receipts <dbl> 3953, 24110, 3719, 1610, 36461, 33655, 1084…
$ cover_charge_receipts <dbl> 0, 7219, 0, 0, 0, 0, 0, 7453, 0, 0, 0, 31, …
$ total_receipts <dbl> 41240, 182853, 7043, 17140, 95976, 65472, 9…
$ responsibility_begin <date> 2015-11-24, 2013-08-08, 2015-10-15, 2020-1…
$ responsibility_end <date> NA, NA, NA, 2023-07-12, NA, NA, NA, NA, NA…
$ obligation_end <date> 2021-06-30, 2023-06-30, 2019-08-31, 2022-0…
$ taxpayer_zip_code <chr> "78758", "78768", "78704", "78652", "78701"…
$ location_zip_code <chr> "78758", "78701", "78704", "78704", "78701"…Make reusable date parts
I am making reusable date parts from obligation_end_date. I am also making “year” and “month” with labels like “Jan”.
# Making reusable date parts
mixedbev_clean <- mixedbev_select |>
mutate(
yr = year(obligation_end),
mo = month(obligation_end),
mo_label = month(obligation_end, label = TRUE),
mo_long = month(obligation_end, label = TRUE, abbr = FALSE)
)
# peek at the result
mixedbev_clean# A tibble: 73,721 × 28
taxpayer_number taxpayer_name taxpayer_address taxpayer_city taxpayer_state
<dbl> <chr> <chr> <chr> <chr>
1 32057827142 ROCKROSE RESTA… 11420 ROCK ROSE… AUSTIN TX
2 32049953949 KLODGE, INC. PO BOX 685084 AUSTIN TX
3 32058372064 EL BORREGO DE … 3900 S CONGRESS… AUSTIN TX
4 32039907699 DESI & FRIENDS… 12700 ENCINO DR MANCHACA TX
5 32039685030 WL STAR BAR, L… 600 W 6TH ST AUSTIN TX
6 17427121425 CASINO EL CAMI… 517 E 6TH ST AUSTIN TX
7 32045024711 SWOON HOSPITAL… 2406 HOMEDALE C… AUSTIN TX
8 32069216045 407 BARLIB LLC PO BOX 685084 AUSTIN TX
9 32055355815 SOUTH FIRST PI… 2901 S 1ST ST S… AUSTIN TX
10 32051844242 EATX COFFEE LLC 22601 STATE HIG… SPICEWOOD TX
# ℹ 73,711 more rows
# ℹ 23 more variables: taxpayer_county <dbl>, location_number <dbl>,
# location_name <chr>, location_address <chr>, location_city <chr>,
# location_state <chr>, location_county <dbl>,
# inside_outside_city_limits <chr>, tabc_permit_number <chr>,
# liquor_receipts <dbl>, wine_receipts <dbl>, beer_receipts <dbl>,
# cover_charge_receipts <dbl>, total_receipts <dbl>, …Summary
I am looking at a summary of the data.
# Summary
mixedbev_clean |> summary() taxpayer_number taxpayer_name taxpayer_address taxpayer_city
Min. :1.011e+10 Length:73721 Length:73721 Length:73721
1st Qu.:1.743e+10 Class :character Class :character Class :character
Median :3.205e+10 Mode :character Mode :character Mode :character
Mean :2.711e+10
3rd Qu.:3.206e+10
Max. :3.722e+10
taxpayer_state taxpayer_county location_number location_name
Length:73721 Min. : 0.0 Min. : 1.000 Length:73721
Class :character 1st Qu.:105.0 1st Qu.: 1.000 Class :character
Mode :character Median :227.0 Median : 1.000 Mode :character
Mean :176.5 Mean : 7.255
3rd Qu.:227.0 3rd Qu.: 2.000
Max. :246.0 Max. :608.000
location_address location_city location_state location_county
Length:73721 Length:73721 Length:73721 Min. :105.0
Class :character Class :character Class :character 1st Qu.:227.0
Mode :character Mode :character Mode :character Median :227.0
Mean :226.1
3rd Qu.:227.0
Max. :246.0
inside_outside_city_limits tabc_permit_number liquor_receipts
Length:73721 Length:73721 Min. : 0
Class :character Class :character 1st Qu.: 1956
Mode :character Mode :character Median : 15954
Mean : 37712
3rd Qu.: 45314
Max. :6772055
wine_receipts beer_receipts cover_charge_receipts total_receipts
Min. : 0 Min. : 0 Min. : 0.0 Min. : 0
1st Qu.: 0 1st Qu.: 728 1st Qu.: 0.0 1st Qu.: 4884
Median : 893 Median : 4702 Median : 0.0 Median : 29509
Mean : 8751 Mean : 14048 Mean : 152.9 Mean : 60750
3rd Qu.: 5912 3rd Qu.: 15754 3rd Qu.: 0.0 3rd Qu.: 76488
Max. :987402 Max. :7023836 Max. :147230.0 Max. :14783293
responsibility_begin responsibility_end obligation_end
Min. :1993-11-30 Min. :2019-01-02 Min. :2019-01-31
1st Qu.:2010-02-01 1st Qu.:2021-10-31 1st Qu.:2020-05-31
Median :2015-12-03 Median :2023-02-01 Median :2021-08-31
Mean :2013-08-18 Mean :2022-10-28 Mean :2021-08-17
3rd Qu.:2019-02-01 3rd Qu.:2023-12-18 3rd Qu.:2022-11-30
Max. :2024-11-01 Max. :2024-10-28 Max. :2023-12-31
NA's :58049
taxpayer_zip_code location_zip_code yr mo
Length:73721 Length:73721 Min. :2019 Min. : 1.000
Class :character Class :character 1st Qu.:2020 1st Qu.: 4.000
Mode :character Mode :character Median :2021 Median : 7.000
Mean :2021 Mean : 6.542
3rd Qu.:2022 3rd Qu.:10.000
Max. :2023 Max. :12.000
mo_label mo_long
Dec : 6271 December : 6271
Nov : 6248 November : 6248
Oct : 6209 October : 6209
Sep : 6184 September: 6184
Aug : 6179 August : 6179
Jul : 6152 July : 6152
(Other):36478 (Other) :36478 Export
I am exporting the cleaned data.
# Export
mixedbev_clean |>
write_rds("data-processed/01-mixedbev.rds")