03_FilteringData
April 27, 2018 - Mike McMahon (mike.mcmahon@dfo-mpo.gc.ca)
Some people love to be able to select options from lists, while others hate any attempt at a GUI ("Graphical User Interface"). Which side of this argument you're on is often determined by your level of familiarity with the data and its structure.
Regardless of whether you filter via GUI or script, if at any point you need to return the filtered data to its original, unfiltered state, run get_data(db, data.dir ="/some/folder"), and the filtered data will be replaced with the original versions that you extracted.
(If you hate GUIs, you don't need to use this function -- you can jump to self_filter )
Once the data is extracted via get_data(), you can filter it with data_filter(). This function results in a GUI that presents the user with a series of select boxes through which the user can filter the loaded data. While the function doesn't return anything itself, the data that remains in the global environment will have been altered by the user's selections. For example, if you select that you want data for haddock, then every record that can't be linked to a haddock catch will be discarded. The convenience of having the data constantly filtered is tempered by the slowness of the application. When the databases have millions of records, this process can be very slow. Since the format of the data is almost identical to what is present in the source database, many legacy SQL queries should still work via the sqldf package.
Following is an illustration of how the act of filtering might appear (though available options will change depending on your other selections, especially the value of db).
| initial filters | choosing a survey type | choosing a season | done? |
|---|---|---|---|
 |
 |
 |
 |
Maybe at this point we realize that we still wanted to filter by the species caught - notice that all filters are available again below - we're applying new filters on top of already-filtered data. What you might not notice is that the data available in the filters has been limited to only data for which values exist (e.g. Blue whales won't be available in the species list if no blue whales were ever caught).
| apply another filter | choosing a species |
|---|---|
 |
 |
Here's what our data looks like now - notice that there is just one record for GSXTYPE, one record for GSMISSIONS (we chose 2016, and summer) and 3 records for GSSPECIES. GSINF, GSCAT and GSDET have all been filtered to match our selections.
|
If you use the GUI, you probably won't need to manually touch self_filter(), since it gets run automatically after each selection. For this reason, the GUI can be quite a bit slower than a script.
Everything that can be done with the GUI can also be done without it, providing you are familiar enough with the data to do it properly. Filtering data is simply a matter of overwriting the existing data objects with subsetted versions, and then running self_filter() when you're done. self_filter() is the part of the application that uses the known relationships between the objects and gets rid of those that you've filtered away. For example, if you limit the species table to a single species, running self_filter() is what's going to get rid of catch records that didn't catch that species, detail records for other species, sets that didn't catch that species, and even missions where that species was never seen.
One big advantage of the scripting approach (vs the GUI) is that if you know exactly wat you want, you can do all of your subsetting at once, and then just run the self_filter() at the end ensure that all of the other tables are in sync with your subsets. This is significantly faster, but does make it possible to filter away all of your data.
Additionally, scripting allows you to embed the filtering processes directly into your code. For example, you might want to create a script that loops through a bunch of species or strata or something, and process the data in bulk. Pointing and clicking to accomplish this would be infuriating.
Following is an example of what you might include in a script to extract all of the Cod data for the Summer 2017 survey.
library(Mar.datawrangling)
get_data('rv')
GSSPECIES = GSSPECIES[GSSPECIES$CODE ==10,]
GSXTYPE = GSXTYPE[GSXTYPE$XTYPE ==1,]
GSMISSIONS = GSMISSIONS[GSMISSIONS$YEAR == 2017 & GSMISSIONS$SEASON == 'SUMMER',]
self_filter()