A Very Geeky Analysis of 8 Months of Data Collection on Running Trains

I rejoined my model railroad club in 2016. Over those 3 years, I had a feeling I ran certain trains and locomotives more than others. Since I work as an analyst, I decided to do some data collection on this. This post is a geeky analysis of how I collected the data, the results, and the plan for this year.

The Collection Goals

My goals for this exercise were simple: how often did I run specific locomotives and specific trains1. For example, how often did I run my Erie Lackawanna SD45s, and how often did I run my coal train?

Collecting the Data:

In the beginning, I just kept a Numbers file with the following information:

Date Reporting Mark Engines Train Name
4/13/19 EL SD45s Freight
4/13/19 UP WideCabs Freight

It was basic information. The date the train ran, the road name of the engines pulling the train, the specific engines that I ran, and a generic name of the train. The challenge of modeling two eras is I do need to be specific on the locomotives and trains. I can’t run my Erie Lackawanna power on a modern train since the railroad ceased to exist in 1976.

By the end of the year, I realized the information was too basic. In the case of the Union Pacific train, generalizing the locomotives made sense since I only had two UP locomotives. By the end of the year, I had four engines that were era-appropriate. I had to get more specific, but not screw up my count. If I ran the coal train with 4 engines, I wanted to count the train running once, and each of the engines running once, but I didn’t want to count the coal train running four times. So, I couldn’t list each of the locomotives as running a coal train since that would inflate the count.

I also didn’t want to track how many laps around the layout each train ran. I thought about it, but decided there were too many variables. A good example is a train I am making adjustments too at the club. I may make 5 laps with it, but I am adjusting problematic cars. Meanwhile, the train that runs fine may only make one lap. I decided I didn’t care about that data. I also didn’t care often a specific locomotive ran a specific train.

In the end, I ended up with a Numbers spreadsheet with the following data:

Date Reporting Mark Engines Train Name Train Class
12/7/2019 UP SD70ACE UPFRT1 Manifest
12/7/2019 UP ES44AC DPU No count
12/7/2019 CSX SD40-2 DPU No count

This is a single entry for one train running: UPFTR1. I got away from generic names, and now most trains have detailed names. What this entry notes is on 12/7/2019 I ran my UP Freight. Pulling the train were 3 engines (two UP and one CSX). DPU is a railroading term for Distributed Power Unit — typically power that runs mid- or end-of-train. I adopted it to mean any additional power that runs on a train. This means that I can get a single count for running the SD70ACE, the ES44AC, and the SD40, but keeping the count for UPFRT1 as one. Train class is a higher-level designation to see how often I ran a manifest, passenger, etc., train2. A No Count entry just means don’t count the two DPU units as Manifest train. It’s just a value to filter out in the report. As with the train name, I only care about one train class per train name. Now, if I run two different manifest trains with the same power on each, then I want two counts. There is also a count for running locomotives during the club’s operating session, but I didn’t need much specificity on the usage.

There is also a worksheet that lists all of my locomotives and rolling stock. The rolling stock also shows what cars the train is currently assigned to.

Analyzing the Data

I was just using a variety of CountIF statements in Numbers. When the data I collected was basic this worked ok. As I wanted to do a deeper analysis on this the limits of CountIFs became a burden. Adjusting each of the CountIFs as I changed the naming was a pain. Numbers doesn’t really support pivot tables, and even then, I wanted a little more flexibility and ease of use.

Enter Tableau.

I use Tableau at work infrequently as a reporting package. I was rusty and wanted to try and keep sharp on using the tool. Tableau doesn’t support Numbers as a data source, so I moved it into Google Sheets.

Tableau made short work of filtering out the data. I could exclude the No Counts, filter out a few other things. Counting operating sessions became more of an aside.

While I cared more about locomotives and trains, the rolling stock sheet also lets me easily print out a grouped list for when I go to the shows. This way I can reduce the likelihood I buy a duplicate car.

Results of the data

Train Class Run Count
Passenger 25
Manifest 20
Unit 12
Operations 7
Local 5
Steam 2
Grand Total 71

I am not going to get into the specific trains run. Instead, I will just talk about the high-level view: the train class.

That passenger trains were around 30% of the total train types run does not surprise me. There are two reasons for this: they are my favorite type of train to run, and they are the easiest to set up and run. Each of my passenger trains can be stored and transported in one storage bin, locomotives included. If I am only going to be at the club for a short time, or just want something easy to set up, the passenger train is my usual choice.

There are only three trains that I classify as Unit trains (Coal, Grain, and Intermodal), so I was a little surprised to see them count as high as they did. Numbers-wise, it’s a wash on them. I ran the Intermodal 5 times, the coal train 4, and the grain 3. The grain train was the last train I put together, so it being low is unsurprising. After thinking about it, I like the look of passenger trains, and unit trains — where all the cars are the same type — have the same look.

Operations is just a catch-all that I ran units 6 times at the club operating sessions. From a data collection standpoint, it is hard to define. For instance, if I loan out a locomotive, how do I count it? For now, I am just noting a train class of Operations for the entire day, and counting which units I ran at each session.

Collecting Data in 2020.

The main item I am looking forward to in 2020 is getting an entire year’s data. I decided to gather the data in July 2019, and only had verifiable data from February onwards3.

I also decided I am going to collect data on the laps run. I don’t know what it will tell me due to a lot of variables that affect running trains: how long am I at the club; how the layout behaving; and how well my train is running4. I decided at least for 2020 I would collect the data to see if there are trains that make more laps than others. It’s easier to collect that from the start of the year.

Another goal that I am carrying over from 2019 is to not cook the books and run a train just to inflate a run count. I do track the last time I ran a train, so I will use that to run a train I haven’t run in a while. There is one train I haven’t run since July, so that is a candidate for running soon.

I am also tracking car usage. Mostly the date the car ran, the train it ran on, and its current assignment. Since the Google Sheets roster is kept up-to-date, it’s a quick cut and paste when I run a train. I can also capture if a car ran on multiple trains on a single visit.

Admittedly, this was a solution in need of a problem. It started with just ticking off a virtual sheet running a train and a locomotive and ended with a 5-tab Google Sheet fed into a Business Intelligence tool. Two goals were met: I have some data on running trains, and I got better at data collection and analysis.

If you are curious about the data, I posted the Viz here. There is a lot in the Viz I didn’t cover in the post, and the average laps data is obviously only going to be accurate for 2020.


  1. I didn’t really care about how often specific locomotives ran specific trains ↩︎
  2. I made a late-year change to make trains that ran cars of the same type (Coal, Grain, etc.) Unit Trains. ↩︎
  3. I’m not sure I am missing much. Grad School meant I didn’t get to the club much. ↩︎
  4. A poorly running train could require a lot of fixing, and thus more laps for testing. ↩︎

I Am a Model Railroader, Like My Father Before Me

Kids and trains, man, they’re like kids and dinosaurs.

My father was an avid model railroader and built a sprawling empire in the basement of our home. Trains in some form were a constant in my life as long as I can remember. I have memories of standing by train tracks in all sorts of weather waiting to see one; me running his trains too fast on his home layout. “Faster, Daddy, Faster,” I would shout as a five-year old.

Dad grew up on the Erie Lackawanna main line in New York. He modeled that railroad for nostalgia purposes, I imagine. When I got into the hobby as a young adult, I also favored the Erie Lackawanna. I’m not sure why I did. It would be on brand for me to model the more modern railroads with the wide cab locomotives and double-stack container trains, as well as to buck my dad’s influence. Maybe it was as simple as I liked the paint scheme and wanted to be able to borrow his trains to run with mine. We were both members of the North Shore Model Railroad club, and enjoyed running our trains on the massive layout. Life got in the way and we had to resign our membership 20 years ago.

He was diagnosed with pancreatic cancer in 2009. In 2011 he succumbed.

During the years he was fighting the disease he wanted to build a small switching layout in a spare bedroom. He bought some locomotives and rolling stock, replacing what he didn’t want any more from the earlier layout. I also never got rid of my collection. It sat in Rubbermaid bins for almost 20 years following me from apartment to apartment and to my current house. After he died, his Rubbermaid bins sat next to mine. It was a crowded closet.

Three years ago I was feeling creatively restless. I was still in contact with my friend Jeff and we had tried forming a few bands to keep in music. He was still a member of the North Shore train club and I decided to rejoin the club. I was struggling with writing and drawing as creative outlets. Working on trains felt like a way to be creative but engaging a different part of my brain.

This is when I became thankful that I modeled the same railroad as Dad. It was easy to mix-and-match the trains. At first I tried to keep the collections separate and sometimes just run Dad’s trains. About a year ago, I merged the train collections fully. I store complete trains sets in plastic bins to run at the club. One bin is mostly Dad’s trains with a handful of mine mixed in. It wasn’t intentional; it just worked out that a lot of his cars looked good together. I was up at the club Father’s Day weekend. I didn’t give it much thought as I grabbed “Dad’s Bin” on the way out the door. It was more a case of thinking, I haven’t run those cars in a while. I wonder if everything is adjusted properly? During a pass on the layout it hit me: These are Dad’s cars, and it is Father’s Day. I had a tear in my eye, but a smile on my face. I felt his presence strongly, happy that his trains were being run.

Working with trains still helps me feel close to my Dad. It would be the same even if I modeled a different railroad, or a different era1. I know the reason he wanted to build a model railroad was his way of fighting the disease. He was still active in the Erie Lackawanna Historical Society’s mailing list and would ask questions about the layout he had in mind.

This past weekend, a lot of my feelings about Dad, trains, and his influence on me all came to a head. We held the annual open house for my train club. It is one of the few times a year we are truly open to the public and it’s the model railroader’s version of a fancy dress ball. A lot of members bring out their best trains — the trains that a lot of work and effort went into. I ran a train that had one of Dad’s locomotives and a baggage car he had been working on. My claustrophobia tends to act up during the show when the small aisles of the layout get crowded, so I usually stay in the back working one of the staging yards to help members get trains on, and off, the layout. I was at my post when I saw Dad’s train go by. I mentioned to the gentleman running the train it was mine, and it turns out he had also grown up on the Lackawanna main line. He mentioned the train was getting a lot of nice compliments as he ran it.

On a later pass I mentioned how the engine and the car were Dad’s and how he bought the engine when he was sick hoping he would be able to run it before he died. He never got the chance. My new friend mentioned that he was sure Dad would be smiling right now seeing it run. I told him I knew he was.

On the second day of the show, I brought one of Dad’s favorite engines — a brass Erie Berkshire — with the hopes of running during the open house. Sadly, the steam engine and a key turnout on the layout didn’t get along so I didn’t run it during the event. At the end of the day, when I had the layout to myself, I ran it around the layout with a cut of cars that were all Dad’s. This is the one train I haven’t cross populated with any of my stuff. The label on the bin just says 1950, the name I gave the train, but it’s really Dad’s Train.

I don’t run that train often. The story I was telling myself is the engine is older than I am. That one day it will stop running and why hasten it? But, I can fix it if that happens. Running it this time, I knew why I don’t run that train often: that is the train that Dad should be running. Running his new engine and the baggage car, I can celebrate his spirit and optimism in trying to defeat a terminal illness.

Running the Erie Berkshire, though, I feel his absence.

  1. I also model the modern day Union Pacific, but the bulk of my collection is 1970s-era Erie Lackawanna.

On Minimalism and Model Trains

I am both a minimalist and a model trains enthusiast. The two are sometimes at odds. Model trains are not an interest that embraces minimalism. Sure, I can have one set of locomotives and cars that I run, but much like eating the same thing every day it gets boring fast.

My minimalism theory is pretty much Marie Kondo’s sparks joy approach. When my dad died in 2011, his train collection merged with mine. This was a little untenable. When I rejoined my train club, I did a purge of both collections. Cars that required to much work to repair were thrown away.

To further make things challenging, earlier this year I switched eras I modeled. My dad and I both modeled the Erie Lackawanna railroad which merged with Conrail in 1976. I couldn’t run any of the modern intermodal trains, or the newer wide cab locomotives. So, I switched to modeling the Union Pacific railroad. I didn’t get rid of my Erie Lackawanna-era stuff since it sparks joy to run it.

However, a lot of the older stuff I have isn’t appropriate for the modern era. In the 70s, 40-foot box cars were often used. Most of the 40’ cars were retired around the turn of the century. This necessitated buying some modern rolling stock. I am intentional with it, though. I have a list of items I look for at a good price: 89’ flat cars, 50-60’ box cars; center beam flat cars; and modern tank cars.

I do hyper-organize my collection. I bought a bunch of clear plastic bins and each bin contains a train, sans caboose and locomotives. They are labeled, so when I head to the club I can just grab the bin with the train I want to run. Every now and then I may move cars from one bin to another, but my overall goal isn’t to load up one train with my favorite cars. Instead, each bin has a collection of cars I enjoy. It is also fun coming up with a story for the train. What is it carrying? Where is coming from?

The bins are also labeled with the name of the train. Not a fancy name like The Lake Shore Limited1, but something like Intermodal, or EL Freight, mostly covered hoppers. When I go up to the club, I just scan the labels for what I am in the mood to run.

The one change I did make to my philosophy is to not toss cars beyond repair. I find I need test subjects for weathering techniques so I keep a bin of bad order cars to experiment on.

  1. Although, if I did model that train, the bin would be labeled as such.