Data visualization of Aberystwyth Shipping Records

In a previous post, I described the shipping records for vessels registered at the port of Aberystwyth, transcribed by volunteers and released in digital form by the National Library of Wales.

I have used openpyxl to read the files (released as Excel spreadsheets) in Python. I have uploaded the code at bitbucket.org/davidtreth/aberystwythships (the data itself should be downloaded from the National Library of Wales site).

I have uploaded results at taklowkernewek.neocities.org/abership and have recently extended this to include plots made using matplotlib, which are presented below (and will be put up in some form at the neocities page soon).

The earliest and latest dates recorded for each vessel.

The joining and leaving dates for every mariner recorded (who has parsable dates)

An example vessel.

Another example vessel

Another example vessel

Can Y Cardi re-released for President Dukat inaugurization

President Dukat is inaugurized today. The Dukat-Brunt team encourage all citizens of the galaxy to celebrate with this new version of Can Y Cardi (The Cardi Song).

Earth's political crisis continues after the planet voted to leave the United Federation of Planets in a recent referendum.

The Prime Minister of Earth, Tresera June still maintains that the planet will not only press ahead with plans to leave the Federation, but leave the UFP single market. The Romulan government is expecting this may give an opportunity to bring an end to trade sanctions imposed by the Federation after the Romulan annexation of two star systems in the Neutral Zone. This could even lead to the legalisation of Romulan Ale on Earth. However this would mean a hard border with Mars and its dependent asteroid colonies which will be remaining in the Federation, and the possibility of another independence referendum on Luna.

Aberystwyth Shipping Records transcribed by the National Library of Wales

The National Library of Wales (Llyfrgell Genedlaethol Cymru) has published data of shipping records consisting of crew lists of vessels registered at the port of Aberystwyth from 1856-1914. Dolen Cymraeg.

These consist of a large number of Excel files, and I have written a Python program to read these in, using openpyxl.

There are over 500 vessels recorded, which can have more than one Excel file of records each, which itself can have more than one worksheet tab. However the format is quite consistent, so it is possible to read them programatically with nested loops.
I have uploaded the code at Bitbucket: bitbucket.org/davidtreth/aberystwythships.

Update 

I have uploaded the full details at my website: taklowkernewek.neocities.org/abership.

Extract of some of the output, listing the ship names.

The program takes the first spreadsheet for each "Series" (1 series for each vessel), and takes the name from that, and then finds if there is any name conflict. Occasionally the name is spelled differently, etc.

$ python abership.py -cv
when -v is selected, it uses verbose mode which includes additional print() lines that here show which files are being read.

-c checks the ship names, and the registry numbers for discrpeancies within each series.

Yn ddarllen y data
Reading Data
Directory: Series 1 - 10
Directory: Series_1_vtls004566957
File: File_1-1_vtls004583057.xlsx
Sheet: 1-1-1
File: File_1-2_vtls004583119.xlsx
Sheet: 1-2-1
File: File_1-3_vtls004583238.xlsx
Sheet: 1-3-1
Sheet: 1-3-2
File: File_1-4_vtls004583279.xlsx
Sheet: 1-4-1
Sheet: 1-4-2
Sheet: 1-4-3
Sheet: 1-4-4
File: File_1-5_vtls004583314.xlsx
Sheet: 1-5-1
Sheet: 1-5-2
Sheet: 1-5-3
File: File_1-6_vtls004583549.xlsx
Sheet: 1-6-1
Sheet: 1-6-2
File: File_1-7_vtls004583627.xlsx
Sheet: 1-7-1
Sheet: 1-7-2
Sheet: 1-7-3
File: File_1-8_vtls004583639.xlsx
Sheet: 1-8-1
Sheet: 1-8-2
Sheet: 1-8-3
File: File_1-9_vtls004583649.xlsx
Sheet: 1-9-1
Sheet: 1-9-2
File: File_1-10_vtls004583662.xlsx
Sheet: 1-10-1
Sheet: 1-10-2
File: File_1-11_vtls004583696.xlsx
Sheet: 1-11-1
Sheet: 1-11-2
File: File_1-12_vtls004583787.xlsx
Sheet: 1-12-1
Sheet: 1-12-2
Sheet: 1-12-3
Sheet: 1-12-4
File: File_1-13_vtls004583837.xlsx
Sheet: 1-13-1
Directory: Series_2_vtls004566962
File: File_2-1_vtls004584143.xlsx
Sheet: 2-1-1
File: File_2-2_vtls004584274.xlsx
Sheet: 2-2-1
Sheet: 2-2-2
Sheet: 2-2-3

rest of this part of the output skipped, and report the total number of Excel files read, and the number of vessels.

Total number of Excel files = 5687
Number of vessels = 544

Vessel names:
Aberystwyth, Acorn, Active, Ada Letitia, Ada & Alice, Adela S. Hills, Adelaide, Adoram, Adrienne, Adroit, Advance, Aeron Belle, Aeron Lass, Aeron Maid, Aeron Queen, Aeron Vale, Affines, Agenoria, Agenoria, Agnes Cairnes, Agnes Fraser, Albatross, Alberta, Albion, Alert, Alert, Alice, Alice & Eliza, Alicia, All Right, Alton Towers, Amity, Ann, Ann, Ann Davies, Ann Eliza, Ann Jones, Ann & Betsy, Ann & Elizabeth, Ann & Margaret, Ann & Mary, Anna Maria, Anne, Anne Catherine, Anne Davies, Anne Elizabeth, Anne Jane, Anne Jenkins, Anne Jones, Anne Warren, Anne & Mary, Annie Maude, Annie Ramsay, Annsboro, Antelope, Arcturus, Aretas, Argo, Arica, Atalanta, Atlantic, Aurora, Azorean, Battus, Beatrice, Bee, Bee, Belem, Berlin, Bertie, Beryl, Bessie, Bezaleel, Birdoswald, Blue Jacket, Blue & White, Bodotria, Breconian, Bristol Packet, Bristol Trader, Britannia, Brothers, Brothers, Burncoose, Cadwgan, Cambria, Cambrian, Cambrian Belle, Camden, Candace, Candor, Capricorn, Caradog, Carn Brea, Caroline, Caroline Spooner, Carthusian, Castle, Castle Eden, Catherine, Catherine, Catherine, Catherine Anne, Catherine Hendry, Catherine Morgan, Catherine & Jane, Catherine & Mary , Cecil Brindley , Ceredig, Ceres, Ceres, Ceylon, Champion, Charlotte, Cheltonian, Clara, Clarissa, Clarita, Clarovine, Claudia, Claudia, Claverdale, Cliftonion, Comet, Confidence, Conovium, Conovium, Convoy, Countess of Lisburne, Countess of Lisburne, Courier, Credo, Crescent, Cricket, Crusader, Cwmsymlog, Cygnet, Dart, Derby, Desdemona, Dewi Lass, Diligence, Dora Ann, Doris, Dove, Dovey Belle, Dovey Packet, Dreadnought, Dreadnought, Drusus, Dudgeon, Eagle, Eagle Eyed, Eaglet, Eastward, Edith Eleanor, Edward John, Egeratea, Eigen, Eleanor, Eleanor, Eleanor Francis, Eleanor Thomas, Eleanor & Jane, Eleanor & Jane, Eliza Francis, Eliza Jane, Eliza Jenkins, Eliza & Hannah, Elizabeth, Elizabeth Ann, Elizabeth Davies, Elizabeth Jane, Elizabeth Lewis, Elizabeth Thomas, Elizabeth & Margaret, Ella, Ellen, Ellen, Ellen, Ellen, Ellen, Ellen Anne, Ellen Beatrice, Ellen Catherine, Ellen Edwards, Ellen Owen, Emily Agnes, Emma Alice, Emrys, Energy, Enterprise, Equity, Erycina, Ethel Anne, Etonian, Eugenia, Eunice, Excellent, Excelsior, Exchange, Express, Fair Hope, Fairlina, Fairy, Faithful Mother, Faithful Sister, Fanny Fothergill, Fanny Taylor, Farmers Lass, Favourite, Favourite, Feliciana, Firefly, Fleetwing, Florrie, Friends, Friendship, Gambia, General Nott, General Nott, General Picton, George, George Evans, George Reynolds, George & Lizzie, Gertrude, Gipsy King, Glad Tidings, Glanyrafon, Glanhafren, Glanrheidol, Glantivy, Glanwern, Glanystwyth, Glanystwyth, Gleaner, Glenara, Glenaros, Glenvech, Glynaeron, Gowerian, Gwalia, Gwladys, Hagar, Hannah, Harperly, Harrovian, Hawendale, Heart of Oak, Heather Bell, Hedessa, Heedful, Helena, Henry E. Taylor, Henry & Dora, Herald, Hero, Hetty Ellen, Hetty Mary, Hind, Honora, Hope, Hope, Hope, Ianthe, Idea, Ifor, Ina Lass, Industry, Industry, Integrity, Ionian Belle, Isabel, Isabella, Isobel, Ivanhoe, J. Llewellyn, J.W., J. W. A., James, Jane, Jane, Jane, Jane, Jane, Jane, Jane Davies, Jane Elizabeth, Jane Ellen, Jane Ellen, Jane Griffiths, Jane Gwynne, Jane Herbert, Jane Jones, Jane Maria, Jane Morgans, Jane Owens, Jane Rowland, Jane Williams, Jane & Mary, Joan Cunllo, Johann, John Elias, John Ewing, John James, John James, John Pierce, John William, John & Ann, John & Margaret, John & Mary, John & Mary, Jubilee, Julianna, Kate, Kate Sophia, Keldhead, Killia Lass, Lady Agnes, Lady of the Forest, Lady Pryse, Laura, Leah, Leander, Lerry, Letitia, Lettice Catherine, Levant, Levant, Lima, Lively Lass, Lizzie Jane, Lois, Lois, Lorne, Louisa Ann, Louisa Jane, Lovely Lass, Lovina, Loyalty, Lydia, Lynwood, M. A. Evans, M.A. Jones, Madona, Maelota, Magdalen Esther, Maggie Cummings, Maggie Phillips, Maglona, Maid of Meirion, Main, Mair, Majestic, Major Nanney, Malvina, Mantura, Margaret Ann, Margaret Evans, Margaret Jane, Margaret Jones, Margaretta, Margaretta, Maria, Maria, Maria Anna, Maria & Ann, Marmion, Martha Jane, Martha Lloyd, Martha Lloyd, Martha & Harriet , Martin, Marwood, Mary, Mary Anna, Mary Anne, Mary Botwood, Mary Davies, MARY & ELLEN, Mary Hughes, MARY JANE, Mary Jane, Mary Jones, Mary Lloyd, Mary Rees, Mary Sarah, Marys, Mediterranean, Mela, Melodia, Meridian, Messenger, Messenger, Miss Hughes, Montego, Morben, Morning Star, Morriston, Mountain Lass, Myra, Nanteos, Naomi, Naomi, Napoleon, Nathaniel, Native, Native Pearl, Nell, Nerissa, New Diligence, Nimrod, Nive, Noah, Norseman, Nymph, Nymph, Nymph, Ocean Belle, Ocean Dart, Omnibus, Oneida, Ontario, Orcadia, Orion, Oronsa, Ospray, Owny Belle, P. M. Willcock, Pacific, Padarn, Pandora, Panthea, Patriot, Pearl, Peerless, Perseverance, Philanthropist, Pilgrim, Pinedene, Pleiades, Plover, Pluvier, Porpoise, Prairie Flower, Prima, Prima Donia, PRIMROSE, Prince Cadwgan, Prince Llewelyn, Prince of Wales, Principality, Priscilla, Progress, Providence, Prudence, Puella, Pyrenee, Queen of the Isles, Queen's Cliff, Rachel Lewis, Raymond, Rebecca, Reform, Resolute, Resolven, Retina, Retriever, Rheidol, Rheidol Queen, Rheidol Vale, Roseina, Rosina, Rosland, Rowland Evans, Ruby, Rugbeian, Santon, Salathiel, Sarah, Sarah Ann, Sarah Bridget, Sarah Davies, Sarah Jane, Sarah Philips, Sarah & Mary, Sea Queen, Severn, Shon Quilt, Sir Robert McClure, Sisters, Solway, Sophia & Emily, Speculation, Speculation, Spread Eagle, Sqirrel, Star of Wales, Strata Florida, Strathisla, Summer Cloud, Susanna, Susanna Gwenllian, Susannah Knapp, Swallow, Swell, Sylph, Syren, Taliesin, Theodore Engels, Thomas, Thomas & Sons, Tirsah, Towy, Triton, Truant, Turtle Dove, Two Brothers, Two sisters, U. Larsing, Union Packet, Urania, Urgent, Utopia, Valentine, Velocity, Velocity, Venus, Victoria, Viscata, Volunteer, Walter J. Cummins, Wasp, Waterloo, Wellington, William Landreth, William & Mary, Xanthippe, Ystwyth, Zenobia, Zingara, Zouave, Zulette,

Aberystwyth : 30 worksheets
ship name conflict
Aberystwyth Aberystwyth SS
ship name conflict
Aberystwyth Aberystywyth
ship name conflict
Aberystwyth Aberystwth
Acorn : 92 worksheets
ship name conflict
Acorn None
Active : 38 worksheets
Ada Letitia : 12 worksheets
Ada & Alice : 35 worksheets
Adela S. Hills : 15 worksheets
ship name conflict
Adela S. Hills Adela S Hills
ship name conflict
Adela S. Hills Adela S Hills
ship name conflict
Adela S. Hills Adela S Hills
ship name conflict
Adela S. Hills Adela S Hills
Adelaide : 16 worksheets

etc. - if the name of the ship in some of the worksheets is different from the first one encountered, it will show this. It will also show if the ship registry number is not the same for the whole series.

Sea Queen : 12 worksheets
more than one ship registry number per series
[51148, 51148, 51148, 51148, 51448, 51148, 51148, 51148, 51148, 51148, 51148, 51148]

in this case one of these registry numbers has a typo 51448

Showing the crew lists

Another mode is to output the whole crew lists, which is done by
$ python abership.py -p

As an example:

Series 47. File name File_47-11_vtls004614858.xlsx. Sheet 47-11-1. Ship name: Anne Jane. Ship Registry Number: 42590 Port of registry: Aberystwyth

• Name Birth Year Age Birthplace Date Joined Port Joined Capacity Date Left Port Left

• Evan Davies blk 28 Aberystwith 1875-09-09 Falmouth Master blk blk
• John Smith blk 44 Stonehouse 1875-09-09 Falmouth Mate 1876-01-15 Gloster
• William Holiday blk 17 Manchester 1875-09-09 Falmouth Cook & Ordinary Seaman 1876-01-15 Gloster
• Patrick Driscoll blk 34 [Queenstown] 1875-09-10 Plymouth Able Seaman 1876-01-15 Gloster
• Jacob Jonsen blk 21 Norway 1875-09-10 Plymouth Able Seaman 1875-09-21 At Sea
• Alexander Johnson blk 22 Jersey 1875-09-11 Falmouth Able Seaman 1876-01-15 Gloster
• William Trewin blk 19 Truro 1875-09-11 Falmouth Ordinary Seaman 1876-01-15 Gloster
• William Golley blk 20 Peel 1875-10-25 Seville Ordinary Seaman 1876-01-15 Gloster

Checking the dates

It is also possible to check the dates, which if they are before 1850 or after 1920 the program will report it.

$ python abership -d

Example output

Series 511. Vessel Name, ID: Theodore Engels 85272
File File_511-1_vtls004969330.xlsx. Sheet 511-1-1
Date 1982-08-29 falls before 1850 or after 1920.
Date 1982-08-29 falls before 1850 or after 1920.
File File_511-1_vtls004969330.xlsx. Sheet 511-1-2
File File_511-2_vtls004969335.xlsx. Sheet 511-2-2
File File_511-2_vtls004969335.xlsx. Sheet 511-2-3
File File_511-2_vtls004969335.xlsx. Sheet 511-2-1
date string that cannot be processed: Continues
date string that cannot be processed: 1896--05-28
date string that cannot be processed: 1896--05-28
File File_511-3_vtls004969400.xlsx. Sheet 511-3-3
File File_511-3_vtls004969400.xlsx. Sheet 511-3-2
File File_511-3_vtls004969400.xlsx. Sheet 511-3-1
Vessel Name, ID, Dates: Theodore Engels, 85272, 1892-06-04, 1898-04-19

This finds the earliest and latest dates found for that vessel, and reports any strings that do not appear to be a date, or are outside the expected time bounds.

Redirecting to command-line

Using the Linux command line, it is possible to redirect the output of the print() statements, to a text file, e.g.
$ python -p abership.py > aberships_all.txt

After this, the file could be opened in a text editor, or searched for substrings at the command-line using grep.

grep -C 5 Truro aberships_all.txt


finds all rows of the output, either where a mariner was born in Truro, or joined or left it in Truro.

as an example:

Series 389. File name File_389-19_vtls004926403.xlsx. Sheet 398-19-1. Ship name: Marys. Ship Registry Number: 13154 Port of registry: Aberystwyth

• Name Birth Year Age Birthplace Date Joined Port Joined Capacity Date Left Port Left
• George Harvey 1848 blk Padstow 1886-01-01 Truro Master remains blk
• James Pinch 1860 blk Padstow 1886-01-01 Truro Mate 1886-03-12 Waterford
• Joshua Rowe 1860 blk Padstow 1886-01-01 Truro Able Seaman 1886-02-11 Padstow
• Charles Condon 1830 blk St Johns [New Brunswick] 1886-02-11 Padstow Able Seaman 1886-04-22 Pentewan
• Michael Marrison 1862 blk Waterford 1886-03-12 Waterford Able Seaman 1886-05-19 Runcorn
• John Pellow 1853 blk Hayle 1886-05-30 Runcorn Able Seaman 1886-06-10 Hayle
• Daniel Rion 1846 blk Clonakilty 1886-05-30 Runcorn Mate blk blk
• Harry Harvey 1872 blk Padstow 1886-01-01 Truro Boy blk blk

Dead Parrot - Python in Python

Giant dead parrot found in UK. Species 'Psitticus Brexitus'. Scientists insist it's dead & won't fly. Gvmt say "the people" demand it flies. pic.twitter.com/AH5Twgwjvo

— Scientists for EU (@Scientists4EU) 8 January 2017

# -*- coding: utf-8 -*-
import sys, time
import imp
imp.reload(sys)
if sys.version_info[0] < 3:
    sys.setdefaultencoding('utf-8')

class Parrot:
    def __init__(self, name):
        """
        initialise Parrot object    
        """
        print("Initialising parrot")
        self.latin = "psitticus"
        self.name = name
        self.isalive = True
        self.latin = self.latin + " {n}".format(n=name.lower()+"us")
        
    def dispatch(self):
        print("dispatching parrot")
        self.isalive = False
        
    def printName(self):
        print("This parrot's name is {n}.".format(n=self.name))
        print("Latin: {l}".format(l=self.latin))
        
    def checkAlive(self):
        if self.isalive:
            print("This parrot is healthy.")
        else:
            print("This is an ex-parrot, it has ceased to be.")
            
if __name__ == "__main__":
    if sys.version_info[0] < 3:
        pname = raw_input("What is the parrot's name? ")
    else:
        pname = input("What is the parrot's name? ")
    parrot = Parrot(pname)
    parrot.printName()
    parrot.checkAlive()
    time.sleep(3)
    parrot.dispatch()
    time.sleep(1)
    parrot.checkAlive()

For more EU referendum humour see my previous posts:
Gul Dukat wins galactic presidential election and
Federation Membership referendum.