ibis Single Table Verbs

We will be focusing on how to use the ibis package, a successor to the popular pandas package, for manipulating tabular data. We begin by importing the ibis package. (We include two additional imports from the package which are commonly referred to using their short names, the table placeholder _ instead of ibis._, and the selectors methods as s instead of the verbose ibis.selectors. We will see these in action later).

Learning Goals

  • establish a connection with duckdb.connect()

  • use head() and excute() to preview large data

  • use select(), distinct(), filter() to explore data.

Getting started

To use ibis, we must also select a backend. We will always be using the quite new and very powerful duckdb backend for all of our tasks. We select a backend by creating a “connection”. The details here are not important for us, we can treat this first block as “boilerplate” starting code.

import ibis
from ibis import _
import ibis.selectors as s

con = ibis.duckdb.connect()

We are now ready to read in our data. We will begin by reading the metrics table from the direct access link, as indicated in the URL below. con.read_csv() is quite similar to the pandas.read_csv() we saw in module 1, though the optional arguments get some different names and are not quite as flexibile. One important option for our purposes will be the how to indicate missing values. In the past, we’ve seen negative values like -99 be used to indicate missing values. That convention reflects limitations of early software, which had no natural concept of “missing”. More modern conventions indicating missing values as “NULL” or “NA”. We indicate the data has chosen the latter:

metrics_url = "https://huggingface.co/datasets/cboettig/ram_fisheries/resolve/main/v4.65/tsmetrics.csv"
tsmetrics = con.read_csv(metrics_url, nullstr="NA")

Previewing data: head() and execute()

Let’s take a look at our new table:

tsmetrics
DatabaseTable: ibis_read_csv_ienvx6pgrzgavp6a6pjm3si4mu
  tscategory   string
  tsshort      string
  tslong       string
  tsunitsshort string
  tsunitslong  string
  tsunique     string

This doesn’t look like a pretty pandas table! Where are the values? Actually, as we become more familiar with ibis we learn to appreciate the display choice here. ibis is designed for working with very big data. An important part of this is something called lazy evaluation. Even downloading a very large fle might take a long time, and trying to load a large dataset into python all at once can exceed available RAM and crash the kernel. Instead, ibis merely “peeks” at the data over the remote connection – without even downloading it! It tells us the names of each column and the data type (e.g. string, or numeric, etc) that the read_csv method has ‘guessed’ for the data. As we will see, this is often the most useful information anway.

If we we do want to see a few example rows, we can use the method head() on the table, tsmetrics.head(), to say we want only want to see the top of the data frame. Optionally we can specify how many rows we want to preview, e.g. tsmetrics.head(10) to see 10 (the default is 5). Let’s try it:

tsmetrics.head()
r0 := DatabaseTable: ibis_read_csv_ienvx6pgrzgavp6a6pjm3si4mu
  tscategory   string
  tsshort      string
  tslong       string
  tsunitsshort string
  tsunitslong  string
  tsunique     string

Limit[r0, n=5]

That’s not the top of 5 rows! Once again, ibis is being lazy. We see the same definition of the table as before, only this time it has a name r0, and we see a “plan of execution”, that ibis will return the first 5 rows Limit[r0, 5]. We can force it to execute this plan with execute() :

tsmetrics.head().execute()
tscategory tsshort tslong tsunitsshort tsunitslong tsunique
0 OTHER TIME SERIES DATA AQ Aquaculture MT metric tons AQ-MT
1 OTHER TIME SERIES DATA ASP Annual surplus production MT Metric tons ASP-MT
2 TOTAL BIOMASS BdivBmgtpref General biomass time series preferentially rel... dimensionless dimensionless BdivBmgtpref-dimensionless
3 TOTAL BIOMASS BdivBmgttouse General biomass time series relative to manage... dimensionless dimensionless BdivBmgttouse-dimensionless
4 TOTAL BIOMASS BdivBmsypref General biomass time series preferentially rel... dimensionless dimensionless BdivBmsypref-dimensionless

At last, we are starting to see what the data really looks like. Data tables can quickly become much to large to explore by simply trying to eyeball every row. For instance, we notice the first column, tscategory, shows a few different possible categories for the various metrics in the database. So, how many distinct categories are there?

select() and distinct()

To answer this, we will introduce a few more methods of data table manipulation. select() selects one or more columns of a given table, while distinct() returns only distinct (unique) rows of the table. Note that both of these methods share a common pattern – they both apply to a table (not some piece of a table, like a row or column or cell), and they both return a new table as well that is some subset of the old table. table in, table out. This design is very intentional – by having methods designed specificially to operate on tables and return tables, we can easily stack or chain these together, (also true of head() and execute()`. So let’s try and see distinct categories:

(tsmetrics
 .select("tscategory")
 .distinct()
 .head(10)
 .execute()
)
 
tscategory
0 CATCH or LANDINGS
1 FISHING MORTALITY
2 OTHER TIME SERIES DATA
3 SPAWNING STOCK BIOMASS or CPUE
4 PRODUCTION
5 TOTAL BIOMASS
6 RECRUITS (NOTE: RECRUITS ARE OFFSET IN TIME SE...
7 TIME UNITS

Note that we have stacked these methods together with each step on it’s own line by wrapping the whole thing inside () parentheses. This can make a long “chain” of commands easier to read. While we have asked for no more that 10 values, we have gotten back only 8 – so we now know there are only 8 categories.

filter()

What are the different unit types within, say, the “CATCH or LANDINGS” category? This requires a subset of rows (a filter), rather than a subset of columns (select()):

(tsmetrics
 .filter(_.tscategory == "CATCH or LANDINGS")
 .distinct()
 .head(14)
 .execute()
)
tscategory tsshort tslong tsunitsshort tsunitslong tsunique
0 CATCH or LANDINGS CdivMSY-conv Catch divided by MSY calculated from converted... ratio ratio CdivMSY-conv-ratio
1 CATCH or LANDINGS CdivMSY Catch divided by MSY ratio ratio CdivMSY-ratio
2 CATCH or LANDINGS TAC-A2 Total allowable catch In subarea MT Metric tons TAC-A2-MT
3 CATCH or LANDINGS TC-3 Total catch. Use only when there is more than ... E03 Thousands TC-3-E03
4 CATCH or LANDINGS TL-1 Total landings. Use only when there is more th... E03MT Thousands of metric tons TL-1-E03MT
5 CATCH or LANDINGS TL-A1 Total landings in subarea MT Metric tons TL-A1-MT
6 CATCH or LANDINGS TL-A3 Total landings in subarea MT Metric tons TL-A3-MT
7 CATCH or LANDINGS CdivMSY-dvmb Catch divided by MSY calculated from reference... ratio ratio CdivMSY-dvmb-ratio
8 CATCH or LANDINGS Cpair-CMB Catch or landings that is paired with TAC comb... MT Metric tons Cpair-CMB-MT
9 CATCH or LANDINGS Cpair Catch or landings that is paired with TAC MT Metric tons Cpair-MT
10 CATCH or LANDINGS RecC Recreational catch E00 Individuals RecC-E00
11 CATCH or LANDINGS TAC-2 Total allowable catch MT Metric tons TAC-2-MT
12 CATCH or LANDINGS TAC-A3 Total allowable catch In subarea MT Metric tons TAC-A3-MT
13 CATCH or LANDINGS TAC-CMB Total allowable catch combined beyond just thi... MT Metric tons TAC-CMB-MT

This syntax to subset rows (filter) is more complicated than columns (select) – to find rows containing “CATCH or LANDINGS” we have to indicate which column to look for.

column selection and .

For python to know that we are looking for the column called “tscategory”, we use the column selection _.tscategory. This is actually a shorthand for the pattern tsmetrics.tscategory – the _ is a placeholder for “the current table” in our chain. Extracting a single column with . is itself something of a shorthand, it is equivalent to using the selector [, as tsmetrics["category"]. When a column name is also the name of a table method, we may need to fall back on the square bracket convention. So why use a dot at all? In addition to taking two less characters to write, the . method allows “tab completion” of the column name, which helps us avoid typos. Note that our select() method recognizes either syntax, you can do: tsmetrics.select(_.tscategory). This looks slightly more cryptic, but benefits from autocomplete and matches the sytnax of other functions.

== not =

Another common mistake is to use a single = sign rather than == in filter. Recall that = is used in variable assignment, a = 1 sets the value of a as 1. Double-equals is a “boolean operator”, that tests if the statement is True or False:

a = 1
a == 1
True

Other boolean operators include >, >=, != (not equal) and so forth. The important thing is to know that we can do boolean comparisons, this syntax is easy to look up.

Next steps

Explore the datasets in this collection using select(), distinct() and filter(). We will return to this list of Catch units after we become more aquainted with the remaining tables.

This syntax is harder than select() – we can’t just filter for “CATCH or LANDINGS” without indicating which column we are looking in. To signal that tscategory is a column name and not just a piece of text, we use the subsetting notation, _.tscategory. This is merely a shorthand for the more verbose: