The LITS project

The long-term individual-based time series (LITS) project ran at Imperial College and the Centre for Population Biology (CPB) at Silwood Park in ~2005–2006. It was funded by the Natural Environment Research Council (NERC) with a grant to Tim Clutton-Brock, Charles Godfray and Tim Coulson.

The broad aim of the project was to catalogue UK-based long-term population biology time-series projects. We collected metadata on these projects including the species, geolocation, data owner(s), time frame, and type of data collected. An important early aim was to digitise data sets deemed to be at risk of loss — for example due to the data owners retiring and/or portions of the data being held only in paper records.

The project was described in Jones et al. (2008).

This repository holds three of the digitised data sets that emerged from the project.

The datasets

Dataset	Species	Study	Span	Scope	Data dictionary
Kestrel	Kestrel (Falco tinnunculus)	Village (1990)	~15 years	Breeding, diet, sightings, distribution and morphometrics of ~1,000 marked birds in England and Scotland (plus supporting vole/prey trapping data)	Kestrel/DATA_DICTIONARY.md
Rook	Rook (Corvus frugilegus)	Patterson, Dunnet & Goodbody (1988)	11 years	Sightings, breeding success, morphometrics and mortality of almost 7,000 birds	Rook/DATA_DICTIONARY.md
Sparrowhawk	Sparrowhawk (Accipiter nisus)	Newton & Rothery (1997)	28 years	Individual identities, breeding data, biometric measurements and egg pesticide levels	Sparrowhawk/DATA_DICTIONARY.md

The databases were constructed by Ian Stevenson (Sunadal Data Solutions) with input from Owen Jones and the data owners.

How the data is organised

Each dataset is a small relational database. Within each dataset folder every table is provided once per format, plus the whole database bundled as a single file:

File	What it is	Use it when
tbl<Name>.csv	One plain-text CSV per table	Start here. Opens anywhere — R, Python, pandas, Excel, a text editor
tbl<Name>.xml	The same table as XML	You need a structured/typed exchange format
<Dataset>.xlsx	All tables as sheets in one workbook	You want to browse everything in Excel
<Dataset>.sqlite	The whole relational database in one SQLite file	You want to run SQL / joins across tables
<Dataset>.sql	SQL dump of the database	You want to load it into another SQL engine
<Dataset>.mde	Compiled Microsoft Access database	Legacy; needs MS Access. The formats above are preferred
datapackage.json	Frictionless descriptor for the CSVs	You want types, keys and relationships loaded automatically

Data package

Each dataset folder contains a datapackage.json — a Frictionless Data Package descriptor that ties the CSV tables together with their field types, titles, primary keys and verified relationships. It lets you load and type a whole dataset in one call, for example:

from frictionless import Package
pkg = Package("Sparrowhawk/datapackage.json")
birds = pkg.get_resource("tblbirdid").to_pandas()   # dates parsed, types applied

library(frictionless)
pkg <- read_package("Sparrowhawk/datapackage.json")
birds <- read_resource(pkg, "tblbirdid")

Only relationships that are actually valid (parent key unique, no orphaned rows) are declared as foreign keys, so the malformed Kestrel relationships noted above are omitted.

Self-describing metadata

Every dataset carries three metadata tables that document itself:

• tblTableList — every table and a plain-language description of it.
• tblTableFieldList — every field, with its description, data type and (where relevant) a CodeRef pointing to the meaning of coded values.
• tblCodeList — the lookup that translates each coded value to a label (e.g. Sex M → Male; Sparrowhawk BreedStatus S → Fully successful).

The DATA_DICTIONARY.md in each folder is generated from these three tables so you can browse the full schema — tables, fields, types and code meanings — on GitHub without opening any database.

Data notes and known issues

A few things to be aware of when working with the data — characteristics of the original digitised databases and how this repository handles them.

Dates and times. In the .csv and .sqlite files dates are stored as ISO 8601 and sort chronologically. Each Date/Time column is one of three kinds:

• date only — YYYY-MM-DD, where no time of day was recorded (e.g. sighting, trapping, mark and mortality dates);
• date and time — YYYY-MM-DD HH:MM:SS, where a real time of day was recorded (only tblRanging.Date in the Kestrel dataset);
• time only — HH:MM:SS, where only the time is meaningful (tblTrap.TrapTime in Rook, tblMeasurements.MeasureTime in Sparrowhawk).

The original Microsoft Access databases stored these as day-first text, e.g. 08/10/1980, 09.05.00 (DD/MM/YYYY, HH.MM.SS) — an ambiguous format that does not sort as text. The .csv and .sqlite files were normalised to ISO 8601 by normalize_dates.py; the legacy .xlsx, .xml, .sql and .mde exports retain the original Access format.

Foreign keys in the Kestrel SQLite file. A few of the relationships carried over from Access into KestrelData1.0.sqlite are malformed — they reference a parent column that is not a unique key, and one (tblKestrelTrapping.KestrelID → tblBreeding.MaleID) is spurious. Because SQLite does not enforce foreign keys unless you set PRAGMA foreign_keys = ON, this does not affect ordinary querying, but PRAGMA foreign_key_check will report errors on that file. The Rook and Sparrowhawk SQLite files have clean foreign keys with no orphaned rows.

Getting started

Load a single table (CSV) — R:

birds <- read.csv("Sparrowhawk/tblBirdID.csv")

Load a single table (CSV) — Python / pandas:

import pandas as pd
birds = pd.read_csv("Sparrowhawk/tblBirdID.csv")

Query across tables with SQL (SQLite):

sqlite3 Sparrowhawk/Sparrowhawks1.03.sqlite \
  "SELECT Sex, COUNT(*) FROM tblBirdID GROUP BY Sex;"

Coded columns store short codes; join to tblCodeList (matching on the field's CodeRef) to get human-readable labels — see each dataset's data dictionary for the CodeRef of every coded field.

Maintenance scripts

Three helper scripts (Python standard library only) keep the derived files in sync with the data. Re-run them after changing the underlying tables:

python3 normalize_dates.py     # convert Access date/time text to ISO 8601 (CSV + SQLite); idempotent
python3 generate_docs.py       # rebuild each DATA_DICTIONARY.md from the metadata tables
python3 build_datapackage.py   # rebuild each datapackage.json (types, keys, verified relationships)

generate_docs.py reads tblTableList, tblTableFieldList and tblCodeList; build_datapackage.py additionally reads the SQLite file for primary and foreign keys.

Reuse and licensing

The contents of this repository are licensed under the Creative Commons Attribution 4.0 International (CC BY 4.0) licence. You are free to share and adapt the material for any purpose, provided you give appropriate credit.

When reusing the data, please attribute the LITS project (Jones et al. 2008) and cite the original study for the dataset(s) you use:

• Kestrel — Village (1990)
• Rook — Patterson, Dunnet & Goodbody (1988)
• Sparrowhawk — Newton & Rothery (1997)

Full references are below.

References

• Jones, O. R., Clutton-Brock, T., Coulson, T., & Godfray, H. C. J. (2008). A web resource for the UK's long-term individual-based time-series (LITS) data. Journal of Animal Ecology, 77(3), 612–615. doi: 10.1111/j.1365-2656.2008.01382.x
• Newton, I. & Rothery, P. (1997) Senescence and reproductive value in Sparrowhawks. Ecology, 78, 1000–1008.
• Patterson, I.J., Dunnet, G.M. & Goodbody, S.R. (1988) Body weight and juvenile mortality in rooks Corvus frugilegus. Journal of Animal Ecology, 57, 1041–1052.
• Village, A. (1990) The Kestrel, 1st edn. T & AD Poyser Ltd, London.