IMDb Title Basics → Catalog Row

View on GitHub

What

Reshape IMDb's public title.basics.tsv into a CSV catalogue row with normalised null markers, exploded genres, and a boolean adult column.

Why interesting

IMDb's public non-commercial datasets are the canonical reference for film research, and they ship with four idiosyncrasies that silently corrupt every downstream pipeline that assumes "standard CSV": (1) the file is tab-separated, not comma-separated, so cut -d, and auto-detecting tools mis-parse every row; (2) missing values are encoded as the literal two-character string \N instead of an empty cell, so a naive type cast on runtimeMinutes returns NaN half the time; (3) genres is itself a comma-separated list embedded inside one TSV field; (4) the TSV is unquoted yet thousands of titles contain a literal " character ("Giliap", Mujeres ... "nervios"), so any RFC-4180 parser that assumes " opens a quoted field silently swallows every line up to the next " — dropping ~256k of the 12.5M rows with no error.

Edge cases sourced from.

• https://developer.imdb.com/non-commercial-datasets/ — official IMDb non-commercial dataset reference; documents \N and the TSV format
• https://stackoverflow.com/questions/60655099/how-to-process-the-imdb-tsv-files-in-python — community thread on the \N pitfall

Data source. IMDb Non-Commercial Datasets — title.basics.tsv.gz (this slice: 10 real titles hand-picked so that every quirk is visible in one short table — a \N genre, a \N runtime, real endYear values, an adult title, and two titles carrying a literal ").

At full scale

The committed sample.csv is a 10-row teaching slice; the real file is ~12.5M rows. Pull it and run the same template against the whole thing:

bash fetch-full.sh          # downloads + extracts ./full/title.basics.tsv (~1 GB)
bxp-cli --config full.json  # processes all 12.5M rows

Measured on the reference machine (ReleaseFast, 8 cores):

metric	value
input	12,533,197 rows / 1.1 GB TSV
output	12,533,197 rows / 1.1 GB CSV (1:1)
wall time	~21.4 s
peak RSS	~29 MB (flat — does not grow with rows)

The 1:1 row count: csv_text_quote_in:"none" declares the TSV unquoted, so a stray " in a title is plain data. Even with default " quoting left on, bxp's lazy-quote handling now keeps all 12,533,197 rows and emits a warning on the 2 lines carrying an unbalanced " — it no longer silently merges them (older RFC-4180 tools drop ~256k rows here). none is still the right call: same 1:1 result with no spurious warning. full/ is gitignored — the download stays local.

The tricks

(see inline comments in sample.json):

1. TSV not CSV — csv_delimiter_in: "\t" switches the parser to tab delimiting; output stays CSV for downstream tools.
2. Unquoted TSV with literal " — csv_text_quote_in: "none" turns off RFC-4180 quote handling, so a " in a title is plain data. The slice carries two such titles, "Giliap" and L'homme du "Picardie"; the output re-quotes them properly for CSV consumers. BXP defaults the input quote to "; with lazy-quote handling it no longer merges rows even then — every row is kept and the lines with an unbalanced " get a warning (older RFC-4180 tools silently drop ~256k rows). none is preferred for a known-unquoted format: same result, no warning.
3. \N null marker — NULLIF([X], '\N') on startYear, endYear and runtimeMinutes, plus once for the whole genres field. NULLIF is built for sentinels, so each guard names its field once instead of three times.
4. Multi-value genre cell — SPLIT_PART([genres], ',', 1) peels the first genre into its own primary_genre column while all_genres keeps the full list for filtering.

Final result

Every \N becomes a genuine empty cell, the genre list gains a sortable first element, and a " in a title survives into properly quoted CSV:

raw                                          →  converted
Bohemios          genres=\N                  →  primary_genre=(empty)  all_genres=(empty)
The German …      endYear=1945  runtime=\N   →  end_year=1945          runtime_min=(empty)
Kate & Leopold    genres=Comedy,Fantasy,…    →  primary_genre=Comedy   all_genres="Comedy,Fantasy,Romance"
Bacchanales 69    isAdult=1                  →  adult=true
"Giliap"                                     →  title="Giliap"  (re-quoted for CSV)

The \N guard is the one that bites hardest. Without it SPLIT_PART('\N', ',', 1) returns the literal \N as a perfectly plausible "primary genre" — the run still reports errors:0, warnings:0, and a backslash-N sits in the catalogue forever. Bohemios (1905) is that row.

Sample data

Run it with bxp-cli --config ./sample.json --template imdb_titles_to_catalog:

sample.json (config)sample.csvsample.csvx (result)

{
  conversion_templates: {
    imdb_titles_to_catalog: {
      data_dir:           ".",
      file_pattern_in:    ".csv",
      file_pattern_out:   ".csvx",
      // TRICK 0 — IMDb publishes title.basics as TSV, not CSV.
      csv_delimiter_in:   "\t",
      // TRICK 0b — the TSV is UNQUOTED, yet titles contain literal `"`
      // characters (e.g. `"Giliap"`, `Mujeres ... "nervios"`). BXP defaults
      // csv_text_quote_in to `"` (RFC 4180). With lazy-quote handling a stray
      // `"` no longer merges rows — bxp keeps every row and warns per affected
      // line (older RFC-4180 tools silently drop ~256k rows on the full file).
      // `none` declares the TSV unquoted: `"` is plain data, no warning.
      csv_text_quote_in:  "none",
      csv_delimiter_out:  ",",
      csv_text_quote_out: "double",

      input_schema: {
        $id:    "[tconst]",
        $type:  "[titleType]",
        $title: "[primaryTitle]",
        $orig:  "[originalTitle]",
        $adult: "IF([isAdult] = '1', 'true', 'false')",

        // TRICK 1 — \N is IMDb's null marker (two literal chars: backslash + N).
        // Without normalising it, the literal "\N" leaks into the output and
        // breaks every downstream type cast. NULLIF exists for exactly this:
        // "this value means missing" in one call, field named once.
        $year:    "NULLIF([startYear], '\\N')",
        $end:     "NULLIF([endYear], '\\N')",
        $runtime: "NULLIF([runtimeMinutes], '\\N')",

        // TRICK 2 — `genres` is a comma-separated list inside a TSV cell.
        // Pull the first one out as the primary genre while keeping the
        // raw list for filtering. Whole-list nulls (\N for titles with no
        // genre tagging) need the same null normalisation as TRICK 1.
        $primary_genre: "SPLIT_PART(NULLIF([genres], '\\N'), ',', 1)",
        $all_genres:    "NULLIF([genres], '\\N')"
      },

      row_rules: [ { when: "1 = 1", rows: [ {} ] } ],

      output_schema: {
        id:            "$id",
        type:          "$type",
        title:         "$title",
        original:      "$orig",
        adult:         "$adult",
        year:          "$year",
        end_year:      "$end",
        runtime_min:   "$runtime",
        primary_genre: "$primary_genre",
        all_genres:    "$all_genres"
      }
    }
  }
}

tconst  titleType   primaryTitle    originalTitle   isAdult startYear   endYear runtimeMinutes  genres
tt0000001   short   Carmencita  Carmencita  0   1894    \N  1   Documentary,Short
tt0000009   movie   Miss Jerry  Miss Jerry  0   1894    \N  45  Romance
tt0000502   movie   Bohemios    Bohemios    0   1905    \N  100 \N
tt0035423   movie   Kate & Leopold  Kate & Leopold  0   2001    \N  118 Comedy,Fantasy,Romance
tt0035803   tvSeries    The German Weekly Review    Die Deutsche Wochenschau    0   1940    1945    \N  Documentary,News
tt0039120   tvSeries    Americana   Americana   0   1947    1949    30  Family,Game-Show
tt0062727   short   Of Special Merit    Besonders wertvoll  1   1968    \N  11  Adult,Short
tt0064057   movie   Bacchanales 69  Bacchanales 69  1   1969    \N  95  Adult
tt0073045   movie   "Giliap"    "Giliap"    0   1975    \N  137 Crime,Drama
tt0167609   tvSeries    L'homme du "Picardie"   L'homme du "Picardie"   0   1968    \N  13  Drama

id,type,title,original,adult,year,end_year,runtime_min,primary_genre,all_genres
tt0000001,short,Carmencita,Carmencita,false,1894,,1,Documentary,"Documentary,Short"
tt0000009,movie,Miss Jerry,Miss Jerry,false,1894,,45,Romance,Romance
tt0000502,movie,Bohemios,Bohemios,false,1905,,100,,
tt0035423,movie,Kate & Leopold,Kate & Leopold,false,2001,,118,Comedy,"Comedy,Fantasy,Romance"
tt0035803,tvSeries,The German Weekly Review,Die Deutsche Wochenschau,false,1940,1945,,Documentary,"Documentary,News"
tt0039120,tvSeries,Americana,Americana,false,1947,1949,30,Family,"Family,Game-Show"
tt0062727,short,Of Special Merit,Besonders wertvoll,true,1968,,11,Adult,"Adult,Short"
tt0064057,movie,Bacchanales 69,Bacchanales 69,true,1969,,95,Adult,Adult
tt0073045,movie,"Giliap","Giliap",false,1975,,137,Crime,"Crime,Drama"
tt0167609,tvSeries,"L'homme du ""Picardie""","L'homme du ""Picardie""",false,1968,,13,Drama,Drama

Full-scale & binary files (run it on the complete dataset): fetch-full.sh · full.json.