I. A Curious Coincidence
Our story begins on a lazy weekend sometime in August, 2023, in that timeless city, San Francisco. I was out for a walk with my girlfriend on the wharf, in hopes of catching sight of some of our favorite local dogs in the park there. Meanwhile, unbeknownst to us and not too far away, a certain Star Trek actor (one of her favorites!) was planning a weekend in much the same neighborhood.
As luck would have it, our paths crossed and he graciously took a picture with us (the one headlining this story). As you can see, he is even wearing the same hat! It was a truly happy little coincidence and we still like to tell the story.
II. Another Coincidence
Of course, by far the easier way to come across Robert Picardo is to watch a movie wherein he makes an unexpected cameo. Such was our good fortune last week, while watching “Confess, Fletch!”, a light hearted art heist movie that brings in Robert Picardo for a nice scene at the end. Especially now that we have a tiny personal connection with the actor, this really made the movie for us.
Of course, one can’t help but ask… can we make our own luck? Let us say that we want movies and TV shows satisfying the following criteria:
- They are *not* Star Trek movies.
- But they star as many actors as possible who have each appeared in a large number of Star Trek shows,
- acting as other characters (i.e. not just showing up as themselves for some re-union or parody etc.)
- They are reasonably highly rated, fun in their own right.
In essence, we’d like to be overwhelmed by cameos of Star Trek actors in other settings. Can we do it?
III. Trapped on a Plane with the IMDB Database
On Monday I found myself on a long cross-country flight and set about answering this important question. I downloaded the IMDB data sets in the airport and settled in. Since I couldn’t really figure out the wifi in the airplane and find the internet incredibly distracting anyway, I decided to officially challenge myself to find Robert Picardo WITHOUT wifi. This turned out to be surprisingly difficult in its own right, so I’ll regale you with the story here.
My first plan of attack was to load all the data, which I had in TSV format, into memory and analyze it with Node.js. Unfortunately the files are large – so large that Node refused to allocate enough memory to store the first file (~5 GiB) as a string. However, it was able to load the file into memory as a raw buffer of bytes. “No problem!”, I said — “I’ll parse the buffer byte-by-byte, recursive-descent style. That way I only need to make a bunch of small strings, one for each row!” Sadly, here Node ran out of memory while I was building data structures out of the buffer.
I looked through the other files and found that one of them was as large as 7 GiB. Unfortunately, I particularly needed the information in these large files. I thought grimly of trying write some streaming parser in Python that would join all the files in some clever way, but we all know I’m not really smart enough to do that. Not without wifi.
We are now an hour into the plane ride. “I should probably just do this in a database,” I said to myself. Unfortunately, while I have postgres running on my computer, I really don’t know what the password to any of the users is. The local server kindly let me try as many passwords as I could think of, but NONE of them was the right one. The man pages for psql and postgres did not suggest any bypasses, helpfully informing me that my network administrator should be able to tell me the password. Wait a minute … what if I start a brand new execution of postgres on a different port? Then I’M the network administrator, and maybe it’ll let me log in from scratch!
It turns out that a new postgres instance needs a data directory strewn with a lot of configs and VERSIONS and so forth. postgres kept telling me that it was missing thus-and-such file, so I would helpfully make it a blank one, until one of the error messages asked me to please run initdb. That created a lot of files — I was really quite grateful – and shortly I was able to log in to the database.
We are now two hours in. Now I needed to load these giant TSVs into the database. Of course the way you do THAT — everyone knows this — is to run a \copy command. Anyway, the psql documentation helpfully tells you that the syntax of this command is just like postgres’s COPY command. I feverishly check my backpack for a reference: only the Nand2Tetris book, which is unlikely to explain the syntax for this (or any) SQL command. I try a few plausible parameters but get stuck on setting a tab (\t) as a delimiter, for the program wants delimiters of only a single byte. (The tab, in fact, IS a single byte, despite its escaped representation, in every encoding scheme where a comma is also a single byte — but I’m not sure how to tell psql this…)
I am struck by a flash of blazing insight and type \copy into the search bar of my mac. Who knows what this hail mary might yield. Stunningly, it yields a match, which turns out to be part of the postgres source code I downloaded ages ago and was unable to compile. I am thrown headfirst into the file that tests COPY itself. I am now a rich man in the currency of copy syntax. Sadly, there is no evidence that TSVs are supported in any way, but CSVs look very easy indeed. Perhaps I should write a program to convert my TSVs to CSVs.
I try writing another Javascript buffer-parsing program to convert the files. It works on the smaller data files but stubbornly crashes on the big ones. Stumped for a moment, I recall the old unix utility tr. Off to the man pages I go. It is much better at managing memory than my stupid JavaScript programs. Unfortunately, it can apparently only translate single-character patterns; it took care of my delimiters for me, but I also wanted to get rid of the \N null symbol in the TSVs, and that was beyond tr‘s powers. However, it seems that \copy CAN handle multiple characters when it comes to matching null symbols, unlike delimiters, and so by a mix of tr and incantations stolen from the postgresql unit tests, I’m able to import all the data to the database. Three hours have elapsed.
IV. Get to the point, Jacob!
Well, after that I wrote a lot of SQL to actually answer the question. Suffice it to say that I found myself making a lot of materialized views to checkpoint little pieces of the analysis, and wound up with the following database schema:
The tables come from the IMDB dataset, and the materialized views are my little investigations, built up in a fairly boring deterministic way from the original question (determine the non-Star Trek shows that contain a lot of actors who ARE in Star Trek.) I made the executive decision to limit the Star Trek canon to 1438 episodes of 6 shows (Strange New Worlds, Deep Space Nine, a few others), due to the ASTONISHINGLY VAST corpus of Star Trek-branded content on IMDB.
From here, I assigned each actor a score, proportional to the number of episodes of canon they acted in, and assigned each non-Star Trek movie a score — the number of actors in the movie who had participated in at least a certain number of episodes of Star Trek, too. I also filtered the movies for a reasonable rating on IMDB to flush out a lot of after shows and other off-topic material. I finished just as the plane landed.
The results – the top 50 episodes of non-Star Trek material:
As you can see, a few of these series are frequent contenders – Boston Legal and Hell on Wheels are particularly interesting, but there are scattered episodes of other shows worth watching, too. For example, if we take that “Alienated” episode near the top, here’s its Star Trek representation:
- Wil Wheaton (165 episodes of my selected canon as Wesley Crusher)
- George Takei (100 episodes as Hikaru Sulu)
- Majel Barrett (91 episodes as Nurse Christine Chapel and many other characters, including the computer systems)
- Walter Koenig (60 episodes as Pavel Chekov)
- Grace Lee Whitney (18 episodes as Janice Rand)
PLUS the writers of this episode, J. Larry Carroll and David Carren, both have Star Trek credits as well.
To me, this is pretty stunning and probably the winning episode overall for this post — a huge representation of famous Star Trek actors, across various series, acting as other characters in another show (not a Star Trek parody or anything like that).
Finally, we must of course find the best show of this type that includes Robert Picardo. My vote is for the episode “The Storm” from the show “The Dead Zone”, which besides Picardo includes Nicole de Boer (“Ezri Dax” from Deep Space Nine), and was created by Michael Piller, who made vast writing contributions to Star Trek. (There’s a few other minor Star Trek alumni in the credits, too.)
… Of course, one could just watch Star Trek too.
Jacob Brazeal 