Note: this should have been done by participants before the start of the workshop.
To start, let’s orient ourselves in our project workflow. Previously, we used Excel and OpenRefine to go from messy, human created data to cleaned, computer-readable data. Now we’re going to move to the next piece of the data workflow, using the computer to read in our data, and then use it for analysis and visualization.
The data we will be using is a time-series for a small mammal community in southern Arizona. This is part of a project studying the effects of rodents and ants on the plant community that has been running for almost 40 years. The rodents are sampled on a series of 24 plots, with different experimental manipulations controlling which rodents are allowed to access which plots.
This is a real dataset that has been used in over 100 publications. We’ve simplified it just a little bit for the workshop, but you can download the full dataset and work with it using exactly the same tools we’ll learn about today.
First, let’s download and look at some of the cleaned spreadsheets
Portal Project dataset.
We’ll need the following three files:
Open each of these csv files and explore them. What information is contained in each file? Specifically, if I had the following research questions:
- How has the hindfoot length and weight of Dipodomys species changed over time?
- What is the average weight of each species, per year?
- What information can I learn about Dipodomys species in the 2000s, over time?
What would I need to answer these questions? Which files have the data I need? What operations would I need to perform if I were doing these analyses by hand?
In order to answer the questions described above, we’ll need to do the following basic data operations:
In addition, we don’t want to do this manually! Instead of searching for the right pieces of data ourselves, or clicking between spreadsheets, or manually sorting columns, we want to make the computer do the work.
In particular, we want to use a tool where it’s easy to repeat our analysis in case our data changes. We also want to do all this searching without actually modifying our source data.
Putting our data into a relational database and using SQL will help us achieve these goals.
Definition: Relational database
A relational database is a digital database where all data is stored in relations (or tables) containing rows and columns. All tables contain records and all records are identified by a field contiaing a unique value. Every table shares at least one field with another table in one-to-one, one-to-many or many-to-many relationships. This allows the user to access the data in many ways
Using a relational database serves several purposes.
There are a number of different database management systems for working with relational data. We’re going to use SQLite today, but basically everything we teach you will apply to the other database systems as well (e.g., MySQL, PostgreSQL, MS Access, Filemaker Pro). The only things that will differ are the details of exactly how to import and export data and the details of data types.
Let’s download and look at a pre-existing database, the
file. Clicking on the “open file” icon and then that file will open the database.
You can see the tables in the database by looking at the left hand side of the
screen under Tables, where each table corresponds to one of the
we were exploring earlier. To see the contents of any table, click on it, and
then click the “Browse and Search” tab in the right panel. This will
give us a view that we’re used to - just a copy of the table. Hopefully this
helps to show that a
database is, in some sense, just a collection of tables, where there’s some value
in the tables that allows them to be connected to each other (the “related” part
of “relational database”).
The leftmost tab, “Structure”, provides some metadata about each table. It
describes the columns, often called fields. (The rows of a database table
are called records.) If you scroll down in the Structure view, you’ll
see a list of fields, their labels, and their data type. Each field contains
one variety or type of data, often numbers or text. You can see in the
surveys table that most fields contain numbers (integers) while the
table is nearly all text.
The “Execute SQL” tab is blank now - this is where we’ll be typing our queries to retrieve information from the database tables.
Before we get started with writing our own queries, we’ll create our own
database. We’ll be creating this database from the three
we downloaded earlier. Close the currently open database and then
follow these instructions:
sex, etc.) and INT for fields with numbers (
weight, etc.), click *OK
Import the plots and species tables
You can also use this same approach to append new data to an existing table.
|CHARACTER(n)||Character string. Fixed-length n|
|VARCHAR(n) or CHARACTER VARYING(n)||Character string. Variable length. Maximum length n|
|BINARY(n)||Binary string. Fixed-length n|
|BOOLEAN||Stores TRUE or FALSE values|
|VARBINARY(n) or BINARY VARYING(n)||Binary string. Variable length. Maximum length n|
|INTEGER(p)||Integer numerical (no decimal).|
|SMALLINT||Integer numerical (no decimal).|
|INTEGER||Integer numerical (no decimal).|
|BIGINT||Integer numerical (no decimal).|
|DECIMAL(p,s)||Exact numerical, precision p, scale s.|
|NUMERIC(p,s)||Exact numerical, precision p, scale s. (Same as DECIMAL)|
|FLOAT(p)||Approximate numerical, mantissa precision p. A floating number in base 10 exponential notation.|
|DOUBLE PRECISION||Approximate numerical|
|DATE||Stores year, month, and day values|
|TIME||Stores hour, minute, and second values|
|TIMESTAMP||Stores year, month, day, hour, minute, and second values|
|INTERVAL||Composed of a number of integer fields, representing a period of time, depending on the type of interval|
|ARRAY||A set-length and ordered collection of elements|
|MULTISET||A variable-length and unordered collection of elements|
|XML||Stores XML data|
Different databases offer different choices for the data type definition.
The following table shows some of the common names of data types between the various database platforms:
|integer||Number (integer)||Int||Number||Int / Integer||Int / Integer|
|float||Number (single)||Float / Real||Number||Float||Numeric|
|string (variable)||Text (<256) / Memo (65k+)||Varchar||Varchar / Varchar2||Varchar||Varchar|
|binary object OLE Object Memo Binary (fixed up to 8K)||Varbinary (<8K)||Image (<2GB) Long||Raw Blob||Text Binary||Varbinary|