Load and filter data

This function has the option to either load data from a local file, dataSource = "csv", or a database backend, dataSource = "sql". If using a local file, a data frame is returned. If using a database backend, an unevaluated dplyr data frame corresponding to a SQL query is returned.

filterData(dataSource = "sql", conn = NULL, activeFilters = NULL)

Arguments

dataSource	A string specifying whether to use database backend, "sql", or a csv file, "csv".
conn	An object created with dbConnect() specifying a connection to a database management system. Only needed when dataSource = "sql".
activeFilters	A list of elements with names corresponding to variables and values indicating filter condition (see details)

Value

If dataSource="sql", returns an unevaluated dplyr data frame corresponding to a SQL query (class: tbl_dbi,' tbl_sql, tbl_lazy, tbl). If dataSource="csv", returns a standard dplyr data frame.

Details

When dataSource = "csv", the function will return data from the built in dataset data("huntfishappDemo", package = "huntfishapp"). The option to use a local file is provided mainly to demonstrate the app functionality if a database backend is unavailabe. To use a different data file the source code of the function will need to be modified. Using a local data file has the advantage that analysis can occur very quickly because all the data is loaded into memory. The drawbacks to using a local data file are that the amount of data that can be analyzed is limited by the memory available, it may not scale well if the app is deployed to multiple users, and it may be tedious to manage and update local data files.

The suggested use case is dataSource = "sql" to use a database backend. When using a database backend the function will attempt to connect to a view on the server named "huntfishapp" using the user supplied connection object, tbl(conn, "huntfishapp"). The view should contain the following variables:

• itemUID: Unique integer identifier for each item (i.e., row).

• customerUID: Unique integer identifier for customer who purchased item.

• issueDate: Date item was purchased or issued in the format yyyy-mm-dd.

• itemResidency: Residency attribute of item coded as "T" or "F". May or may not correspond to customer residency attribute.

• duration: Duration attribute of item in character format. Any values are allowed.

• residency: Residency attribute of customer coded as "T" or "F". May or may not correspond to item residency attribute. May or may not change over time.

• state: Customer address state component coded as two letter abbreviation.

• county: Customer address county component in title-case.

• age: Customer age attribute.

• itemType: Description of item type (e.g., Deer, Fish, etc.)

• itemYear: Year attribute of item. May or may not correspond to year of purchase or issue date.

• price: Price of item including relevant fees.

Examples

# Demo data: All items for male customers age 0-17 in Lancaster county
filterData(
  dataSource = "csv",
  activeFilters = list(
    gender = "Male",
    county = "lancaster",
    ageGroup = "0-17"
  )
)
#> # A tibble: 208 x 18
#>    itemUID issueDate  itemResidency duration durationValue residency state
#>      <int> <date>     <chr>         <chr>            <dbl> <chr>     <chr>
#>  1   42350 2017-11-11 T             Season               1 T         NE   
#>  2   39266 2017-04-17 T             Season               1 T         NE   
#>  3   43961 2018-04-09 T             Season               1 T         NE   
#>  4    3854 2010-09-27 T             Season               1 T         NE   
#>  5    3855 2010-09-27 T             Season               1 T         NE   
#>  6    7340 2011-05-09 T             Annual               1 T         NE   
#>  7    8729 2011-08-15 T             Annual               1 T         NE   
#>  8    8730 2011-08-15 T             Season               1 T         NE   
#>  9    9294 2011-09-28 T             Season               1 T         NE   
#> 10   11102 2011-12-30 T             Season               1 T         NE   
#> # … with 198 more rows, and 11 more variables: itemType <chr>, itemYear <int>,
#> #   price <dbl>, birthYear <dbl>, gender <chr>, county <chr>,
#> #   customerUID <int>, age <dbl>, ageGroup <chr>, month <dbl>, year <dbl>
if (FALSE) {
# Database connection. Suggest using keyring package to avoid hardcoding
# passwords
myConn <- DBI::dbConnect(odbc::odbc(),
  dsn = "HuntFishApp", # Your datasource name
  uid = keyring::key_get("HuntFishAppUID"), # Your username
  pwd = keyring::key_get("HuntFishAppPWD")
) # Your password

# SQL Backend: Collect 10 resident waterfowl and hunt items for January 2017
wfJan2017 <-
  filterData(
    dataSource = "sql",
    conn = myConn,
    activeFilters = list(
      issueDate = c("2017-01-01", "2017-01-31"),
      itemType = c("Waterfowl", "Hunt"),
      residency = "T"
    )
  )

# Inspect SQL query (useful for debugging)
wfJan2017 %>% show_query()

# Evaluate SQL query (set n = Inf to return all rows)
wfJan2017 %>% collect(n = 10)
}