Aberystwyth Shipping Records transcribed by the National Library of Wales

Tags:

17 Jan 2017 - MawKernewek

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

Back to blog index page