Star Trek meets Mord ist ihr Hobby

direkt link to list

Motivation

Wie bekomme ich möglichst schnell heraus, welche Personen sowohl bei allen Star Trek-Serien als auch bei Murder She Wrote mitgemacht haben? Mir war aufgefallen, dass mir viele Gesichter in den X-Trillionen Folgen Murder She Wrote bekannt vorkamen. Meiner Vermutung war: von Star Trek.

Nur, wie finde ich ohne allzu viel Aufwand heraus, wer sowohl im Star Trek Franchise als auch bei Murder She Wrote mitmachte?

Copilot

KI is your Friend, klar. Also fix Copilot angeworfen und die Aufgabe gestellt. Das Ergebnis war eine Liste von ca. 40 Personen. Schnell wurde klar, dass die Daten des ersten Wurfes Schrott sind. Schauspieler und Rollen wurden vertauscht. Folgennamen waren vertauscht. Die Figur Q trat angeblich bei Murder She Wrote auf. Also nochmal. Ich erklärte copilot schrittweise, was zu tun ist:

• 1.) Liste aller Darsteller aller StarTrek-Serien und Filme erstellen

• 2.) Liste aller Darsteller aller Murder She Wrote-Folgen erstellen

• 3.) Überprüfen, welche Personen in beiden Listen zu finden sind

Ich habe Copilot angewiesen, sehr genau zu sein. Copilot sollte nur die IMDB verwenden. Auch nach sicherlich 30 Zyklen u.a. mit der Bitte um Korrektur und einer anderen Vorgehensweise, der Angabe der fehlerhaften Daten usw. waren die Daten fehlerhaft. M.M.n. hatte ich alles getan, um die KI auf die richtige Bahn zu führen.

Das Ergebnis blieb also massiv fehlerhaft: zuviele Einträge, zuwenige Einträge, falsche Einträge.

Ein anderer Weg musste her.

IMDB Database

Glücklicherweise stellt die IMDB neben einer kostenpflichtigen API auch eine Offline-DB zum Download zur Verfügung ( https://developer.imdb.com/non-commercial-datasets/ ). Sie umfasst 7 tsv-Dateien mit insgesamt 8 GB. Das sind tab separated Value-Dateien. Unter https://sqlitebrowser.org findet man den SQLite-Browser, mit dem man einfach SQL-Datenbanken anlegen kann. Also los: Datenbank angelegt und TSV-Dateien importiert. Beim Import kam es zu Fehlern, vermutlich, weil viele die Dateien zu groß sind. Das Problem ließ sich mit einem Powershell-Script umgehen:

write-Output $PSScriptRoot

# Pfad zur ursprünglichen TSV-Datei
$sourceFile = "$PSScriptRoot\title.principals.tsv"

write-Output "SourceFilepath: $sourceFile"

# Verzeichnis für die Aufteilung der Dateien
$outputDir = "$PSScriptRoot\teildateien"

# Zeilenlimit pro Datei
$lineLimit = 6000000

# Initialisierung
$fileIndex = 1
$lineCount = 0
$outputFile = "$outputDir/Teildatei_$fileIndex.tsv"

# Erstellen der ersten Ausgabedatei und Schreiben der Kopfzeile
if (-not (Test-Path $outputDir)) {
    New-Item -Path $outputDir -ItemType Directory
}

$header = $null

# Öffnen der Quell-TSV-Datei für das Lesen zeilenweise
$reader = [System.IO.StreamReader]::new($sourceFile)

# Kopfzeile extrahieren
$header = $reader.ReadLine()
# Erstellen der ersten Ausgabedatei und Schreiben der Kopfzeile
$outputFileStream = [System.IO.StreamWriter]::new($outputFile)
$outputFileStream.WriteLine($header)

# Verarbeiten der Zeilen
while ($reader.Peek() -ge 0) {
    $line = $reader.ReadLine()
    if ($lineCount -ge $lineLimit) {
        # Schließen der aktuellen Ausgabedatei
        $outputFileStream.Close()

        # Neues Dateisegment starten
        $fileIndex++
        $lineCount = 0
        $outputFile = "$outputDir/Teildatei_$fileIndex.tsv"
        $outputFileStream = [System.IO.StreamWriter]::new($outputFile)
        $outputFileStream.WriteLine($header)
    }
    
    # Zeile zur aktuellen Ausgabedatei hinzufügen
    $outputFileStream.WriteLine($line)
    $lineCount++
}

# Schließen der letzten Ausgabedatei
$outputFileStream.Close()

# Schließen der Quell-TSV-Datei
$reader.Close()

Write-Output "Dateien wurden erfolgreich aufgeteilt!"

Das Powershell-Script unterteilt die großen Dateien in mehrere kleinere. Sollte es hier bei euch Probleme geben, bitte mit dem Wert $lineLimit herumspielen.

Nach der Aufteilung der tsv-Dateien in kleinere Häppchen konnte ich über den SQLite-Browser die Dateien importieren. Dieser Vorgang ist der Aufwändigste von allen und dauert abhängig von der Computergeschwindigkeit ca. 45 Minuten. Die erstellte Datenbank stelle ich nicht zur Verfügung, da ich nicht weiß, wie hier der rechtliche Rahmen ist.

Mit folgendem Select werden die entsprechenden Daten ermittelt:

select distinct nconst from (
select tp.nconst, te.tconst Folgen_ID, te.parenttconst Serien_ID, tb2.primaryTitle Folgentitel, tb.primaryTitle Serienname, 'S'||printf('%02d',seasonNumber)||'E'||printf('%02d',episodeNumber) episode
from title_basics tb,
     title_episode te,
	 title_basics tb2,
	 title_principals tp
-- selected series because there is much more than the known series:
where upper( tb.primarytitle ) in ('STAR TREK', 'STAR TREK: DEEP SPACE NINE', 'STAR TREK: DISCOVERY', 'STAR TREK: LOWER DECKS', 'STAR TREK: PICARD', 'STAR TREK: PRODIGY', 'STAR TREK: STRANGE NEW WORLDS', 'STAR TREK: THE NEXT GENERATION', 'STAR TREK: VOYAGER', 'STAR TREK: ENTERPRISE')
and tb.tconst = te.parentTconst
and te.tconst = tb2.tconst
and tb2.tconst = tp.tconst
and tb.titleType ='tvSeries'
and episodeNumber > 0)
union
select distinct nconst
from title_basics tb,
     title_principals tp
where upper( primarytitle) like '%STAR TREK%' -- movies with star trek in title
and tb.tconst = tp.tconst
and genres != 'Documentary'
and titletype = 'movie'
intersect 
select distinct nconst from (
select tp.nconst, te.tconst Folgen_ID, te.parenttconst Serien_ID, tb2.primaryTitle Folgentitel, tb.primaryTitle Serienname, 'S'||printf('%02d',seasonNumber)||'E'||printf('%02d',episodeNumber) episode
from title_basics tb,
     title_episode te,
	 title_basics tb2,
	 title_principals tp
where upper( tb.primarytitle ) in ('MURDER, SHE WROTE')
and tb.tconst = te.parentTconst
and te.tconst = tb2.tconst
and tb2.tconst = tp.tconst
and tb.titleType ='tvSeries'
and episodeNumber > 0)
order by tp.nconst;

Mit diesem Select werden alle Beteiligten in Form der IMDB-ID der Beteiligten zurückgegeben. Die ID (nconst) ist in den Tabellen der Datenbank als Key hinterlegt. Mit dem Key konnte ich das Ergebnis um zusätzlichen Daten mit weiteren Selects anreichern, um zum unten aufgeführten Ergebnis zu kommen.

Und was ist nun das Ergebnis? 202 Personen (Schauspieler und Regisseure, Schreiber etc.) sind bei beiden beteiligt gewesen.

Mit diesem Ansatz ist es sehr leicht, Star Trek auch mit anderen Serien abzugleichen. Alternativ könnte man natürlich auch das Star Trek Franchise durch andere Franchises ersetzen (zB. Marvel, Star Wars usw.).

Ergebnis

Viel Spaß beim Ausprobieren. Und hinterlasst gerne einen Kommentar, wenn euch das gefallen hat.