Friday, 23 March 2018

Balintore's Victorian Spreadsheet

Those of you who think the spreadsheet began around 1979 (in the era of VisiCalc) are urged to read on.

Scans of historic Balintore Estate documents have recently fallen into my hands, and these constitute a meticulously-kept and continuous record, in spreadsheet form, of game-bagged at the estate between the years 1896 and 1928.

Monarchs may have come and gone (Victoria, Edward VII, George V); wars may have come and gone (Second Boer War, WWI, Irish War of Independence); and the Downton Abbey supernova may have waxed and wained (1912-1926); but nothing interfered with the shooting season at Balintore or the accurate recording thereof. The copperplate, to my eyes, betrays the same diligent hand at work throughout.

My understanding is that a single shooting tenant would rent out the castle and the estate for the entire season, and the "Remarks" column of the tables suggests this is the case as it presumably contains the name of the tenant for that year. I am particularly fond of the 1922 tenant: a delightfully named Colonel Courage.

I was unfamiliar with the term "Blackgame", but apparently this is the Black Grouse which the numbers indicate is much rarer than the more familiar Red Grouse.

The Woodcock is by far the rarest bird. I know it is shot in Glen Quarity to this day, and that Italians seem to be the keenest to do so. There are assuredly many disappointed Italians. I can only assume that this small bird must be disproportionately delicious.

I once dined in a restaurant called "La B├ęcasse " in Nice. I was unfamiliar with the word so I looked it up.  Not only is B├ęcasse the French word for Woodcock, but it also means "the hunt" so synonymous must the activity and the bird be to the continental mind.

A scientific training rendered it impossible for me not to graph the data. Given the radically different numbers for the different species, the data was best displayed by grouping the game types into three separate graphs: the "most shot"; the "least shot" and the ignominious "middlingly shot".

The spreadsheet may be found here, and the infinitely scalable SVG file for the graphs may be found here. The scanned historic tables are appended below, followed by the three graphs and finally followed by the Python code to produce the graphs.

numbers bagged 1896 to 1906

numbers bagged 1907 to 1917

numbers bagged 1918 to 1928

import pandas
import matplotlib.pyplot as plt
coloured source produced by
pygmentize -O full,style=emacs -f html -l python -o analyse_spreadsheet.html

def safe_integer_conversion(arg):
        return int(arg)
        return 0  # if no integer is in the cell return 0

csv_name = '/home/david/Documents/qt_code/shooting_spreadsheet/Shooting Spreadsheet - Sheet1.csv'

df = pandas.read_csv(csv_name)
x = list(map(safe_integer_conversion, df["Date."]))

colours = [ "blue", "red", "brown" , "green", "yellow", "black", "orange", "cyan", "magenta", "gray"]
omit_columns = ["date", "total"]
colour_index = 0

use_columns= [col for col in list(df) if not any([o in col.lower() for o in omit_columns])]
column_average = {}
for col in use_columns:
    data = list(map(safe_integer_conversion, df[col]))
    column_average[col] = sum(data) / len(data)

number_of_graphs = 3

items = list(column_average.items())
sorted_items = list(reversed(sorted(items,key = lambda a: a[1] )))
items_per_graph = int(1.0 + (len(sorted_items) / number_of_graphs))

plt.suptitle("Game Killed on Balintore Estate")

colour_index = 0
for figure in range(number_of_graphs):
    item_slice = sorted_items[figure * items_per_graph:(figure + 1) * items_per_graph]
    group = [ e[0] for e in item_slice ]
    subplot = 100 * number_of_graphs + 10 + figure + 1
    plt.ylabel("number killed")
    for col in group:
        y = list(map(safe_integer_conversion, df[col]))
        colour = colours[colour_index % len(colours)]
        game = col.replace("-","").replace(".","")
        l1, = plt.plot(x,y, 'o-', label=game, visible=True, color=colour)
        colour_index = colour_index + 1


  1. I'd like to see a bit more documentation in your code please. Otherwise, approved.


    1. What effrontery! :-) What a shame I can't bribe you with cake as per normal practice.

  2. It's surprising isn't it? There are so many pheasants around. Perhaps the population is larger nowadays and more are shot?