addeter Posted 18 February , 2014 Posted 18 February , 2014 Hi allI have been experimenting with the data I have been able to export from the CWGC site. As I have been researching to ancestors in the South Staffs. I decided to focus on that regiment.I exported all of the men listed as with the South Staffs, and imported it into a Google Sheet. Using a Pivot table I have come up with a timeline, which shows casualties by the date they died. The high peaks obviously show the higher death rates, but what is fascinating is that it pinpoints when men of the regiment were engaged in particularly deadly actions.I am not sure what I am going to do with this, but thought I would post it and see what people think. Here is the link to the Google Sheet, if you are interested. Please let me know what you think https://docs.google.com/spreadsheets/d/14sVz4eigRkGvrxzWd_5CpWPm-ySWR2LskfsY2XBNcyU/edit?usp=sharing
stdape2011 Posted 20 February , 2014 Posted 20 February , 2014 I am doing the same with a regiment too. Also adding who had the SWB, where possible date reported as being wounded, any enlistment dates, or any info i can add. Going to take a while, and may fail, but will do my best. Good luck on yours
addeter Posted 20 February , 2014 Author Posted 20 February , 2014 Thanks. I would recommend you opt in for the new Google Sheets, so that it will cope with a larger number of rows in the spreadsheet. Interesting that you are adding information to the sheet. Which regiment are you working on?
addeter Posted 20 February , 2014 Author Posted 20 February , 2014 The file has now been updated, with counts by rank of casualties listed at CWGC, and a pie chart. No surprises that this demonstrates the vast majority of deaths were privates, but ranks all the way up to colonel are included. I am also experimenting with outputting on the data by Memorial or Cemetery. This seems to show the vast majority listed on the Loos, Arras and Thiepval memorials, but the spreadsheet needs some work to make this report properly. Antony
Martin Feledziak Posted 20 February , 2014 Posted 20 February , 2014 That is very impressive work Addeter I have found My Wife's Great Great Grandfather Samuel Walker contained in this mass of data. I have added a screen shot for the benefit of other readers. he appears right down on line 6421 - he was killed very late in the war. I am not a master of Excel - or spreadsheets but there are some cracking features available for analysts. Even I can see that he is amongst friends in the Cement House Cemetery Keep up the good work ! Regards Martin
addeter Posted 20 February , 2014 Author Posted 20 February , 2014 Thanks so much for the kind comments. I think the timeline is the most impressive graphic I have created so far. Keep checking in because I will add other features if i can think of them. Antony That is very impressive work Addeter I have found My Wife's Great Great Grandfather Samuel Walker contained in this mass of data. I have added a screen shot for the benefit of other readers. he appears right down on line 6421 - he was killed very late in the war. Samuel Walker.jpg I am not a master of Excel - or spreadsheets but there are some cracking features available for analysts. Even I can see that he is amongst friends in the Cement House Cemetery Cement.jpg Keep up the good work ! Regards Martin
addeter Posted 20 February , 2014 Author Posted 20 February , 2014 I have just updated the spreadsheet again, and have added a chart which shows distribution of casualties by age at death. Interestingly the mean age was around 26, but by far the most casualties (the mode) were only 19. Please note than only half of the records have an age recorded. Also, looking back at the chart on Cemeteries and Memorials, I think it is striking what a large proportion of them are listed on the various memorials, rather than at individual graves. Antony
Martin Feledziak Posted 20 February , 2014 Posted 20 February , 2014 How long did it take you to present the data for the South Staffs in this format ? Could you do the same for the North staffs ? I think the best column is column 'O' Other Details. This is full of vital info - the best bit is all of these fields are searchable with a CTRL F command.
addeter Posted 21 February , 2014 Author Posted 21 February , 2014 Getting the data together did not take too long in itself. What took time was learning how to design the pivot tables and arrange the data, so that the charts made sense. I do think I could replicate the information for the N. Staffs relatively quickly because I have done the learning. I will give it a try over the weekend. And you are right about the Other Details and CTRL F being so useful. Antony How long did it take you to present the data for the South Staffs in this format ? Could you do the same for the North staffs ? I think the best column is column 'O' Other Details. This is full of vital info - the best bit is all of these fields are searchable with a CTRL F command.
addeter Posted 21 February , 2014 Author Posted 21 February , 2014 I have now completed a new version of the spreadheet for the North Staffordshires. 5937 casualties, so fewer than the Souths. I did however add another chart because the Norths were involved in more theaters, and so you can see what proportion died in France, Belgium, Turkey, Iraq, India etc. Let me now what you think. https://docs.google.com/spreadsheets/d/1XsgmSZLdLweChryr-Eu9bg8CTRmwhuOUip9PGCGvY7k/edit?usp=sharing Antony How long did it take you to present the data for the South Staffs in this format ? Could you do the same for the North staffs ? I think the best column is column 'O' Other Details. This is full of vital info - the best bit is all of these fields are searchable with a CTRL F command.
Martin Feledziak Posted 23 February , 2014 Posted 23 February , 2014 Great Work Alan. You have provided a useable resource for people interested in the lost soldiers of Great War Staffs. Can I just check that my computer is displaying the Pie charts correctly - The "Privates" are not showing up as the biggest chunk of the North Staffs Casualties by rank PIE, is it just my computer ? Column 0 is really great !! Martin
addeter Posted 23 February , 2014 Author Posted 23 February , 2014 Thanks for the kind comments. I will check the pie chart. Antony Great Work Alan. You have provided a useable resource for people interested in the lost soldiers of Great War Staffs. Can I just check that my computer is displaying the Pie charts correctly - The "Privates" are not showing up as the biggest chunk of the North Staffs Casualties by rank PIE, is it just my computer ? Column 0 is really great !! Martin
addeter Posted 24 February , 2014 Author Posted 24 February , 2014 I have now fixed the pie chart. It was skipping the last few lines of data. Anyway, it works now. Antony Great Work Alan. You have provided a useable resource for people interested in the lost soldiers of Great War Staffs. Can I just check that my computer is displaying the Pie charts correctly - The "Privates" are not showing up as the biggest chunk of the North Staffs Casualties by rank PIE, is it just my computer ? Column 0 is really great !! Martin
Martin Feledziak Posted 24 February , 2014 Posted 24 February , 2014 I am very impressed. Do the CWGC - know that you can do this with their data. We all get access to their site but searching is very long winded, ( Basically 1 soldier at a time ) With these spread sheets it become simple and very quick. Good work Alan
addeter Posted 4 March , 2014 Author Posted 4 March , 2014 I have actually been in touch with them to let them know I have done this, in part because I have been having real problems with the formatting of the date of death. It can get messed up when being pulled into the spreadsheet. I will have to wait to see what they say, I think. Antony I am very impressed. Do the CWGC - know that you can do this with their data. We all get access to their site but searching is very long winded, ( Basically 1 soldier at a time ) With these spread sheets it become simple and very quick. Good work Alan
Chris_Baker Posted 4 March , 2014 Posted 4 March , 2014 This is great stuff. I actually did this for the dead of 1st Battalion and based on the data in "Soldiers Died in the Great War". This was about 1998 - and I had to key in every detail by hand! I may still have it somewhere but it will be in something ancient like Lotus 1-2-3 ...
addeter Posted 4 March , 2014 Author Posted 4 March , 2014 Glad you like it. If you do still have that old file, I would be interested in trying to merge the data. 1-2-3 might be a little tricky, but it might be worth a try. Excel should open it, and once in Excel, it can be converted to a google sheet. Let me know, as I think it would be an interesting project. I also meant to be in touch with you because of the rather byzantine way in which the CWGC data listed battalions. For the 16 battalions of the South Staffs, they were listed in 130 different ways. By doing some find and replace I have been able to fix the obvious ones, with extra full stops and the like, but there are listings of battalions, which I don't think existed. Let me know if you are interested and I can show you what I mean. My guess is that the Norths will be the same. Antony This is great stuff. I actually did this for the dead of 1st Battalion and based on the data in "Soldiers Died in the Great War". This was about 1998 - and I had to key in every detail by hand! I may still have it somewhere but it will be in something ancient like Lotus 1-2-3 ...
Martin Feledziak Posted 4 April , 2015 Posted 4 April , 2015 Anthony - has this spreadsheet gone missing ?
addeter Posted 2 July , 2015 Author Posted 2 July , 2015 Anthony - has this spreadsheet gone missing ? Sorry for the delayed response. I am in the process of changing jobs. Yes, sorry I took the spreadsheet down because I was having real problems keeping dates straight because of date formatting in the CGWC data sets. I am going to try to rebuild the spreadsheet from scratch for the South Staffords and I will repost when I make it available again. Antony
addeter Posted 2 July , 2015 Author Posted 2 July , 2015 Anthony - has this spreadsheet gone missing ? Hi again I have decided that when I get the spreadsheet back up and posted, I will use the following blog site to host it. Then there will be a permanent link to it. http://comelassesandlads.blogspot.com Antony
addeter Posted 2 July , 2015 Author Posted 2 July , 2015 Here is the direct link to the new South Staffs spreadsheet. I will be working on adding the charts shortly. https://docs.google.com/spreadsheets/d/1nsXN9Fw-DAw0brHbAIeQc1PD_vv5UV1mYVMVaciSQeU/edit?usp=sharing
Martin Feledziak Posted 3 July , 2015 Posted 3 July , 2015 Morning Anthony, or Perhaps it is Evening where you are. Very pleased to get this great resource back. Many Thanks Martin
addeter Posted 3 July , 2015 Author Posted 3 July , 2015 Morning Anthony, or Perhaps it is Evening where you are. Very pleased to get this great resource back. Many Thanks Martin You're very welcome. I hope you find it helpful. Please let me know if there are specific things you'd like to see in the spreadsheet, and I will see what I can do. I'm going to also add back the North Staffs shortly, and the casualty list of the Battle of Coronel, where another great great uncle died. Antony
addeter Posted 5 July , 2015 Author Posted 5 July , 2015 The South Staffs spreadsheet is now up and running again. I have fixed the date formatting problem, and have started adding in some charts. It can be found at: https://docs.google.com/spreadsheets/d/1nsXN9Fw-DAw0brHbAIeQc1PD_vv5UV1mYVMVaciSQeU/edit?usp=sharing
Martin Feledziak Posted 6 July , 2015 Posted 6 July , 2015 Thanks Anthony. The spread sheet is very good. Now...... Just a quick question specific to the 2nd battalion South Staffs. Do you have the war diary for 25th September 1915.? Forum member JWK has a thread running about a German soldier killed on 25th September 1915. ( Fritz Limbach ) He says that he has downloaded the war diary from the archives but the scan of the 25th South Staffs is not noted. Or not in the scan he was given. we are looking for the account relevant to Captain Kilby VC. Martin
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now