Golem Grad Tortoise Data

This one comes from a story with the headline “Constant Sexual Aggression Drives Female Tortoises to Walk Off Cliffs” in the New York Times. You really should just go read that.

You back?

I really spun my tires on this one. Two datasets were provided—one for these recaptures with multiple body condition measurements, and one with clutch size of pregnant females. I wanted to join them since there were two tables with (presumably) a shared key for the individual animals, but the clutch size one only had 53 rows (versus over ten thousand for the other one) and there was even less overlap when I actually joined them. So just looking at the larger table, I tried all sorts of things correlating different measurements over time, by sex, in different locations…most things I tried were too busy or didn’t show much of anything. So finally I came back to this. Just a breakdown by sex, from year to year, run separately for each locality.

I was a bit confused by the locality column. Plateau seemed to be the island, and I could confirm with a Maps search that Konjsko is a town on the mainland. There was a third value, Beach. I don’t know what Beach is. Is this another spot on the mainland, or a different part of the island? Males do outnumber females here, but overall the numbers are much smaller than in either of the other locations.

10005000

2008200920102011201220132014201520162017201820192020202120222023

MaleFemale

↓ Download data • ↓ Download image

10005000

20102011201220132014201520162017201820192020202120222023

MaleFemale

↓ Download data • ↓ Download image

10005000

2008200920102011201220132014201520162017201820192020202120222023

MaleFemale

↓ Download data • ↓ Download image

Queries

The main query was a pivot after selecting year, sex, and locality. I also renamed m and f in that select CTE.

create or replace table recaptures_by_year as

with recaptures as (
     select year,
            case sex when 'm' then 'Male'
                     when 'f' then 'Female'
                      end as sex,
            locality

       from tortoise_body_condition
)

      pivot recaptures
         on sex
      using count(*)
   order by year

This effectively gave me three sets of annual data in a single table—one for each locality:

year	locality	Female	Male
2008	Beach	22	36
2008	Plateau	10	538
2009	Plateau	33	731
2009	Beach	34	47
2010	Konjsko	64	59
2010	Beach	16	40
2010	Plateau	25	673
2011	Plateau	22	956
2011	Konjsko	76	66
2011	Beach	24	55
…	…	…	…
2023	Plateau	17	285
2023	Konjsko	52	40
2023	Beach	12	16

All that remained was to extract each of them:

select year, Male, Female
  from recaptures_by_year
 where locality = 'Plateau';

select year, Male, Female
  from recaptures_by_year
 where locality = 'Konjsko';

select year, Male, Female
  from recaptures_by_year
 where locality = 'Beach';