{ "cells": [ { "cell_type": "raw", "metadata": {}, "source": [ "---\n", "title: \"Generate Podcast Chapters\"\n", "icon: \"notebook\"\n", "description: \"[Open in Kaggle](https://kaggle.com/kernels/welcome?src=https://github.com/pixeltable/pixeltable/blob/release/docs/release/howto/cookbooks/audio/audio-podcast-chapters.ipynb) | [Open in Colab](https://colab.research.google.com/github/pixeltable/pixeltable/blob/release/docs/release/howto/cookbooks/audio/audio-podcast-chapters.ipynb) | [View on GitHub](https://github.com/pixeltable/pixeltable/blob/release/docs/release/howto/cookbooks/audio/audio-podcast-chapters.ipynb)\"\n", "---" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Generate YouTube-style chapter timestamps for podcast episodes. WhisperX detects where speech starts and stops, then an LLM summarizes each segment into a chapter title.\n", "\n", "The end result looks like this:\n", "\n", "```\n", "0:00 - Experiencing self vs remembering self\n", "0:10 - Whether memories are the primary source of happiness\n", "0:36 - Controlling how we remember experiences\n", "```\n", "\n", "## Problem\n", "\n", "You have podcast episodes and want to generate chapter markers \u2014 the kind you see in YouTube descriptions or podcast apps. Each chapter needs a timestamp and a short description of what's being discussed.\n", "\n", "## Solution\n", "\n", "**What's in this recipe:**\n", "\n", "1. **WhisperX** transcribes the audio and detects speech boundaries via VAD (Voice Activity Detection)\n", "1. `json.list_iterator` creates a **view** with one row per speech segment\n", "1. **GPT-4o-mini** generates a chapter title for each segment as a computed column\n", "1. The result is formatted as YouTube-style timestamps" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Setup" ] }, { "cell_type": "code", "metadata": { "execution": { "iopub.execute_input": "2026-05-20T23:48:38.947598Z", "iopub.status.busy": "2026-05-20T23:48:38.947473Z", "iopub.status.idle": "2026-05-20T23:48:39.994615Z", "shell.execute_reply": "2026-05-20T23:48:39.993933Z" } }, "source": [ "%pip install -qU pixeltable whisperx openai" ], "execution_count": null, "outputs": [] }, { "cell_type": "code", "metadata": { "execution": { "iopub.execute_input": "2026-05-20T23:48:40.014336Z", "iopub.status.busy": "2026-05-20T23:48:40.014207Z", "iopub.status.idle": "2026-05-20T23:48:40.016651Z", "shell.execute_reply": "2026-05-20T23:48:40.016242Z" }, "tags": [ "skip-execution" ] }, "source": [ "import getpass\n", "import os\n", "\n", "if 'OPENAI_API_KEY' not in os.environ:\n", " os.environ['OPENAI_API_KEY'] = getpass.getpass('OpenAI API Key: ')" ], "execution_count": 1, "outputs": [] }, { "cell_type": "code", "metadata": { "execution": { "iopub.execute_input": "2026-05-20T23:48:40.017941Z", "iopub.status.busy": "2026-05-20T23:48:40.017849Z", "iopub.status.idle": "2026-05-20T23:48:41.082129Z", "shell.execute_reply": "2026-05-20T23:48:41.081687Z" } }, "source": [ "import pixeltable as pxt\n", "import pixeltable.functions as pxtf\n", "\n", "pxt.drop_dir('podcast_demo', force=True, if_not_exists='ignore')\n", "pxt.create_dir('podcast_demo')" ], "execution_count": 2, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Load a podcast episode" ] }, { "cell_type": "code", "metadata": { "execution": { "iopub.execute_input": "2026-05-20T23:48:41.083605Z", "iopub.status.busy": "2026-05-20T23:48:41.083467Z", "iopub.status.idle": "2026-05-20T23:48:41.311615Z", "shell.execute_reply": "2026-05-20T23:48:41.311058Z" } }, "source": [ "episodes = pxt.create_table(\n", " 'podcast_demo/episodes', {'title': pxt.String, 'audio': pxt.Audio}\n", ")" ], "execution_count": 4, "outputs": [ { "output_type": "stream", "text": [ "Created table 'episodes'.\n" ] } ] }, { "cell_type": "code", "metadata": { "execution": { "iopub.execute_input": "2026-05-20T23:48:41.313219Z", "iopub.status.busy": "2026-05-20T23:48:41.313111Z", "iopub.status.idle": "2026-05-20T23:48:41.377942Z", "shell.execute_reply": "2026-05-20T23:48:41.377388Z" } }, "source": [ "episodes.insert(\n", " [\n", " {\n", " 'title': 'Lex Fridman Podcast Excerpt',\n", " 'audio': 'https://raw.githubusercontent.com/pixeltable/pixeltable/release/docs/resources/audio-transcription-demo/Lex-Fridman-Podcast-430-Excerpt-0.mp4',\n", " }\n", " ]\n", ")" ], "execution_count": 5, "outputs": [ { "output_type": "stream", "text": [ "Inserted 1 row with 0 errors in 0.82 s (1.22 rows/s)\n" ] }, { "output_type": "execute_result", "data": { "text/plain": [ "1 row inserted." ] } } ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "View the data:" ] }, { "cell_type": "code", "metadata": {}, "source": [ "episodes.collect()" ], "execution_count": 6, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
titleaudio
Lex Fridman Podcast Excerpt
\n", "            \n", "        
" ], "text/plain": [ " title \\\n", "0 Lex Fridman Podcast Excerpt \n", "\n", " audio \n", "0 /Users/alison-pxt/.pixeltable/file_cache/467ad... " ] } } ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "And the table schema so far:" ] }, { "cell_type": "code", "metadata": {}, "source": [ "episodes" ], "execution_count": 7, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
table 'podcast_demo/episodes'
\n", "\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Column NameTypeSourceComputed WithComment
titleStringepisodes
audioAudioepisodes
\n" ], "text/plain": [ "table 'podcast_demo/episodes'\n", "\n", " Column Name Type Source Computed With Comment\n", "----------------------------------------------------\n", " title String episodes \n", " audio Audio episodes " ] } } ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Step 1: Transcribe with WhisperX\n", "\n", "WhisperX does two things at once: it runs Voice Activity Detection (VAD) to find where speech occurs, then transcribes each speech region. The output is a list of segments \u2014 each with a start time, end time, and the text that was spoken.\n", "\n", "These segments are the raw material for our chapter markers. Each segment boundary corresponds to a natural pause or transition in the conversation.\n", "\n", "> **Note:** You may see verbose warnings from `torchcodec` or `pyannote` when this cell runs \u2014 they're harmless and can be ignored." ] }, { "cell_type": "code", "metadata": { "execution": { "iopub.execute_input": "2026-05-20T23:48:42.207470Z", "iopub.status.busy": "2026-05-20T23:48:42.207379Z", "iopub.status.idle": "2026-05-20T23:48:50.939361Z", "shell.execute_reply": "2026-05-20T23:48:50.938936Z" } }, "source": [ "episodes.add_computed_column(\n", " transcription=pxtf.whisperx.transcribe(\n", " episodes.audio, model='tiny.en'\n", " )\n", ")" ], "execution_count": 8, "outputs": [ { "output_type": "stream", "text": [ "Added 1 column value with 0 errors in 9.81 s (0.10 rows/s)\n" ] }, { "output_type": "execute_result", "data": { "text/plain": [ "1 row updated." ] } } ] }, { "cell_type": "code", "metadata": { "execution": { "iopub.execute_input": "2026-05-20T23:48:50.940693Z", "iopub.status.busy": "2026-05-20T23:48:50.940328Z", "iopub.status.idle": "2026-05-20T23:48:50.957312Z", "shell.execute_reply": "2026-05-20T23:48:50.956876Z" } }, "source": [ "episodes.select(\n", " first_segment=episodes.transcription.segments[0]\n", ").collect()" ], "execution_count": 9, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
first_segment
{"end": 10.443, "text": " of experiencing self versus remembering self. I was hoping you can give a simple answer of how we should live life.", "start": 0.908, "avg_logprob": -0.063}
" ], "text/plain": [ " first_segment\n", "0 {'end': 10.443, 'text': ' of experiencing self..." ] } } ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Each segment is a dict with `start`, `end`, and `text` fields. We can use the `['*']` path expression to extract a field from every segment at once \u2014 this returns a list of values:" ] }, { "cell_type": "code", "metadata": { "execution": { "iopub.execute_input": "2026-05-20T23:48:50.958440Z", "iopub.status.busy": "2026-05-20T23:48:50.958370Z", "iopub.status.idle": "2026-05-20T23:48:50.969303Z", "shell.execute_reply": "2026-05-20T23:48:50.968899Z" } }, "source": [ "episodes.select(\n", " segment_starts=episodes.transcription.segments['*'].start,\n", " segment_ends=episodes.transcription.segments['*'].end,\n", " segment_text=episodes.transcription.segments['*'].text,\n", ").collect()" ], "execution_count": 10, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
segment_startssegment_endssegment_text
[0.908, 10.73, 36.312][10.443, 36.194, 58.891][" of experiencing self versus remembering self. I was hoping you can give a simple answer of how we should live life.", " Based on the fact that our memories could be a source of happiness or could be the primary source of happiness that an event when experienced bea ...... it's remembered over and over and over and over and maybe there is some wisdom and the fact that we can control to some degree how we remember it.", " how we evolve our memory of it, such that it can maximize the long-term happiness of that repeated experience. Okay, well, first, I'll say, I wis ...... an I be your opening actor? Oh, my God. No, I've got to hope it for you, dude. Otherwise, it's like, you know, everybody leaves after you're done."]
" ], "text/plain": [ " segment_starts segment_ends \\\n", "0 [0.908, 10.73, 36.312] [10.443, 36.194, 58.891] \n", "\n", " segment_text \n", "0 [ of experiencing self versus remembering self... " ] } } ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "That's useful for peeking at the data, but the result is still one row with parallel lists \u2014 not one row per segment. Here's what a single segment looks like as a proper row:" ] }, { "cell_type": "code", "metadata": {}, "source": [ "episodes.select(\n", " start=episodes.transcription.segments[0].start,\n", " end=episodes.transcription.segments[0].end,\n", " text=episodes.transcription.segments[0].text,\n", ").collect()" ], "execution_count": 11, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
startendtext
0.90810.443of experiencing self versus remembering self. I was hoping you can give a simple answer of how we should live life.
" ], "text/plain": [ " start end text\n", "0 0.908 10.443 of experiencing self versus remembering self...." ] } } ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We want every segment as its own row like this \u2014 not just the first one. That's what `list_iterator` does." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Step 2: Create a segments view with `list_iterator`\n", "\n", "To work with individual segments as rows, we create a **view** using `pxtf.json.list_iterator`. This iterator takes parallel lists and zips them into one row per element \u2014 like converting columns of arrays into a proper table.\n", "\n", "The keyword argument names (`start`, `end`, `text`) become the column names in the view. Each argument is a Pixeltable expression that evaluates to a JSON list.\n", "\n", "**Why `astype`?** WhisperX returns an untyped `dict`, so Pixeltable doesn't know what types are inside the segments. `list_iterator` requires typed JSON so it can define the view's schema. We use `astype` to declare that `.start` and `.end` are lists of floats, and `.text` is a list of strings:" ] }, { "cell_type": "code", "metadata": { "execution": { "iopub.execute_input": "2026-05-20T23:48:50.970480Z", "iopub.status.busy": "2026-05-20T23:48:50.970398Z", "iopub.status.idle": "2026-05-20T23:48:51.065303Z", "shell.execute_reply": "2026-05-20T23:48:51.064875Z" } }, "source": [ "segments = pxt.create_view(\n", " 'podcast_demo/segments',\n", " episodes,\n", " iterator=pxtf.json.list_iterator(\n", " start=episodes.transcription.segments['*'].start.astype(\n", " pxt.Json[[float]]\n", " ),\n", " end=episodes.transcription.segments['*'].end.astype(\n", " pxt.Json[[float]]\n", " ),\n", " text=episodes.transcription.segments['*'].text.astype(\n", " pxt.Json[[str]]\n", " ),\n", " ),\n", ")" ], "execution_count": 12, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Here is the schema of this view - you can see the four columns added by the `list_iterator`, and the other columns that came from the `episodes` table we started with:" ] }, { "cell_type": "code", "metadata": { "execution": { "iopub.execute_input": "2026-05-20T23:48:51.066942Z", "iopub.status.busy": "2026-05-20T23:48:51.066852Z", "iopub.status.idle": "2026-05-20T23:48:51.081849Z", "shell.execute_reply": "2026-05-20T23:48:51.081426Z" } }, "source": [ "segments" ], "execution_count": 13, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
view 'podcast_demo/segments' (of 'podcast_demo/episodes')
\n", "\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Column NameTypeSourceComputed WithComment
posRequired[Int]segmentslist_iterator
startRequired[Float]segmentslist_iterator
endRequired[Float]segmentslist_iterator
textRequired[String]segmentslist_iterator
titleStringepisodes
audioAudioepisodes
transcriptionJsonepisodestranscribe(audio, model='tiny.en')
\n" ], "text/plain": [ "view 'podcast_demo/segments' (of 'podcast_demo/episodes')\n", "\n", " Column Name Type Source Computed With Comment\n", "---------------------------------------------------------------------------------------\n", " pos Required[Int] segments list_iterator \n", " start Required[Float] segments list_iterator \n", " end Required[Float] segments list_iterator \n", " text Required[String] segments list_iterator \n", ".......................................................................................\n", " title String episodes \n", " audio Audio episodes \n", " transcription Json episodes transcribe(audio, model='tiny.en') " ] } } ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "One row per segment, with typed columns. From here we can add computed columns that operate on individual segments \u2014 no manual JSON wrangling needed." ] }, { "cell_type": "code", "metadata": { "execution": { "iopub.execute_input": "2026-05-20T23:48:51.083177Z", "iopub.status.busy": "2026-05-20T23:48:51.083108Z", "iopub.status.idle": "2026-05-20T23:48:51.097075Z", "shell.execute_reply": "2026-05-20T23:48:51.096646Z" } }, "source": [ "segments.select(segments.start, segments.end, segments.text).collect()" ], "execution_count": 14, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
startendtext
0.90810.443of experiencing self versus remembering self. I was hoping you can give a simple answer of how we should live life.
10.7336.194Based on the fact that our memories could be a source of happiness or could be the primary source of happiness that an event when experienced bears its fruits the most when it's remembered over and over and over and over and maybe there is some wisdom and the fact that we can control to some degree how we remember it.
36.31258.891how we evolve our memory of it, such that it can maximize the long-term happiness of that repeated experience. Okay, well, first, I'll say, I wish I could take you on the road with me. That was such a great description. Can I be your opening actor? Oh, my God. No, I've got to hope it for you, dude. Otherwise, it's like, you know, everybody leaves after you're done.
" ], "text/plain": [ " start end text\n", "0 0.908 10.443 of experiencing self versus remembering self....\n", "1 10.730 36.194 Based on the fact that our memories could be ...\n", "2 36.312 58.891 how we evolve our memory of it, such that it ..." ] } } ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Step 3: Generate chapter titles\n", "\n", "Each segment now has its own row. We add a computed column that sends the segment text to GPT-4o-mini for a short chapter title:" ] }, { "cell_type": "code", "metadata": { "execution": { "iopub.execute_input": "2026-05-20T23:48:51.098275Z", "iopub.status.busy": "2026-05-20T23:48:51.098196Z", "iopub.status.idle": "2026-05-20T23:48:54.629163Z", "shell.execute_reply": "2026-05-20T23:48:54.628205Z" } }, "source": [ "segments.add_computed_column(\n", " title_response=pxtf.openai.chat_completions(\n", " messages=[\n", " {\n", " 'role': 'user',\n", " 'content': pxtf.string.format(\n", " 'Write a short chapter title (5-10 words) for this podcast segment. '\n", " 'Return only the title, no quotes or extra punctuation.\\n\\n{0}',\n", " segments.text,\n", " ),\n", " }\n", " ],\n", " model='gpt-4o-mini',\n", " ),\n", " if_exists='replace',\n", ")\n", "\n", "segments.add_computed_column(\n", " chapter_title=segments.title_response.choices[0].message.content,\n", " if_exists='replace',\n", ")" ], "execution_count": 15, "outputs": [ { "output_type": "stream", "text": [ "Added 3 column values with 0 errors in 2.64 s (1.14 rows/s)\n", "Added 3 column values with 0 errors in 0.02 s (142.43 rows/s)\n" ] }, { "output_type": "execute_result", "data": { "text/plain": [ "3 rows updated." ] } } ] }, { "cell_type": "code", "metadata": {}, "source": [ "segments.select(segments.text, segments.chapter_title).collect()" ], "execution_count": 16, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
textchapter_title
of experiencing self versus remembering self. I was hoping you can give a simple answer of how we should live life.Living in the Moment vs. Reflecting on the Past
Based on the fact that our memories could be a source of happiness or could be the primary source of happiness that an event when experienced bears its fruits the most when it's remembered over and over and over and over and maybe there is some wisdom and the fact that we can control to some degree how we remember it.The Power of Memory in Shaping Happiness
how we evolve our memory of it, such that it can maximize the long-term happiness of that repeated experience. Okay, well, first, I'll say, I wish I could take you on the road with me. That was such a great description. Can I be your opening actor? Oh, my God. No, I've got to hope it for you, dude. Otherwise, it's like, you know, everybody leaves after you're done.Evolving Memories for Lasting Happiness
" ], "text/plain": [ " text \\\n", "0 of experiencing self versus remembering self.... \n", "1 Based on the fact that our memories could be ... \n", "2 how we evolve our memory of it, such that it ... \n", "\n", " chapter_title \n", "0 Living in the Moment vs. Reflecting on the Past \n", "1 The Power of Memory in Shaping Happiness \n", "2 Evolving Memories for Lasting Happiness " ] } } ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Step 4: YouTube-style timestamps\n", "\n", "Format each segment's start time with its LLM-generated title:" ] }, { "cell_type": "code", "metadata": { "execution": { "iopub.execute_input": "2026-05-20T23:48:54.631205Z", "iopub.status.busy": "2026-05-20T23:48:54.631031Z", "iopub.status.idle": "2026-05-20T23:48:54.692344Z", "shell.execute_reply": "2026-05-20T23:48:54.691418Z" } }, "source": [ "@pxt.udf\n", "def format_timestamp(start: float, chapter_title: str) -> str:\n", " mins, secs = divmod(int(start), 60)\n", " title = chapter_title.strip('\"')\n", " return f'{mins}:{secs:02d} - {title}'\n", "\n", "\n", "segments.add_computed_column(\n", " timestamp=format_timestamp(segments.start, segments.chapter_title),\n", " if_exists='replace',\n", ")" ], "execution_count": 17, "outputs": [ { "output_type": "stream", "text": [ "Added 3 column values with 0 errors in 0.04 s (69.92 rows/s)\n" ] }, { "output_type": "execute_result", "data": { "text/plain": [ "3 rows updated." ] } } ] }, { "cell_type": "code", "metadata": { "execution": { "iopub.execute_input": "2026-05-20T23:48:54.694797Z", "iopub.status.busy": "2026-05-20T23:48:54.694625Z", "iopub.status.idle": "2026-05-20T23:48:54.721520Z", "shell.execute_reply": "2026-05-20T23:48:54.720823Z" } }, "source": [ "segments.select(segments.timestamp).order_by(segments.start).collect()" ], "execution_count": 18, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
timestamp
0:00 - Living in the Moment vs. Reflecting on the Past
0:10 - The Power of Memory in Shaping Happiness
0:36 - Evolving Memories for Lasting Happiness
" ], "text/plain": [ " timestamp\n", "0 0:00 - Living in the Moment vs. Reflecting on ...\n", "1 0:10 - The Power of Memory in Shaping Happiness\n", "2 0:36 - Evolving Memories for Lasting Happiness" ] } } ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Here is a formatted version you can copy/paste directly into your YouTube video description field:" ] }, { "cell_type": "code", "metadata": { "execution": { "iopub.execute_input": "2026-05-20T23:48:54.723303Z", "iopub.status.busy": "2026-05-20T23:48:54.723157Z", "iopub.status.idle": "2026-05-20T23:48:54.733739Z", "shell.execute_reply": "2026-05-20T23:48:54.733203Z" } }, "source": [ "print('\\n'.join(segments.order_by(segments.start).collect()['timestamp']))" ], "execution_count": 19, "outputs": [ { "output_type": "stream", "text": [ "0:00 - Living in the Moment vs. Reflecting on the Past\n", "0:10 - The Power of Memory in Shaping Happiness\n", "0:36 - Evolving Memories for Lasting Happiness\n" ] } ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Step 5: Listen to each segment\n", "\n", "The segments view inherits the `audio` column from the base `episodes` table. We can add a computed column that slices out each segment's audio clip using its `start` and `end` times \u2014 useful for spot-checking the transcription.\n", "\n", "In Jupyter, the `audio_clip` column renders as an inline audio player you can click to listen:" ] }, { "cell_type": "code", "metadata": { "execution": { "iopub.execute_input": "2026-05-20T23:48:54.735172Z", "iopub.status.busy": "2026-05-20T23:48:54.735071Z", "iopub.status.idle": "2026-05-20T23:48:54.940743Z", "shell.execute_reply": "2026-05-20T23:48:54.940245Z" } }, "source": [ "import os\n", "import subprocess\n", "import tempfile\n", "\n", "\n", "@pxt.udf\n", "def slice_audio(audio: pxt.Audio, start: float, end: float) -> pxt.Audio:\n", " \"\"\"Extract a time range from an audio file using ffmpeg.\"\"\"\n", " fd, output_path = tempfile.mkstemp(suffix='.mp4')\n", " os.close(fd)\n", " subprocess.run(\n", " [\n", " 'ffmpeg',\n", " '-y',\n", " '-i',\n", " str(audio),\n", " '-ss',\n", " str(start),\n", " '-to',\n", " str(end),\n", " '-c',\n", " 'copy',\n", " output_path,\n", " ],\n", " capture_output=True,\n", " check=True,\n", " )\n", " return output_path\n", "\n", "\n", "segments.add_computed_column(\n", " audio_clip=slice_audio(segments.audio, segments.start, segments.end),\n", " if_exists='replace',\n", ")" ], "execution_count": 20, "outputs": [ { "output_type": "stream", "text": [ "Added 3 column values with 0 errors in 0.28 s (10.85 rows/s)\n" ] }, { "output_type": "execute_result", "data": { "text/plain": [ "3 rows updated." ] } } ] }, { "cell_type": "code", "metadata": { "execution": { "iopub.execute_input": "2026-05-20T23:48:54.941902Z", "iopub.status.busy": "2026-05-20T23:48:54.941828Z", "iopub.status.idle": "2026-05-20T23:48:54.965922Z", "shell.execute_reply": "2026-05-20T23:48:54.965517Z" } }, "source": [ "segments.select(segments.timestamp, segments.audio_clip).order_by(\n", " segments.start\n", ").collect()" ], "execution_count": 21, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
timestampaudio_clip
0:00 - Living in the Moment vs. Reflecting on the Past
\n", "            \n", "        
0:10 - The Power of Memory in Shaping Happiness
\n", "            \n", "        
0:36 - Evolving Memories for Lasting Happiness
\n", "            \n", "        
" ], "text/plain": [ " timestamp \\\n", "0 0:00 - Living in the Moment vs. Reflecting on ... \n", "1 0:10 - The Power of Memory in Shaping Happiness \n", "2 0:36 - Evolving Memories for Lasting Happiness \n", "\n", " audio_clip \n", "0 /var/folders/zz/d4wytf2s44996j3sr4zgthw80000gn... \n", "1 /var/folders/zz/d4wytf2s44996j3sr4zgthw80000gn... \n", "2 /var/folders/zz/d4wytf2s44996j3sr4zgthw80000gn... " ] } } ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Explanation\n", "\n", "**Pipeline:**\n", "\n", "```\n", "Audio \u2192 WhisperX (VAD + transcription) \u2192 list_iterator view (1 row per segment) \u2192 GPT-4o-mini per row \u2192 timestamps\n", "```\n", "\n", "The `episodes` table holds the raw audio and transcription. The `segments` view fans out the transcription's segment list into individual rows using `json.list_iterator`. Each segment row then gets its own LLM call and timestamp \u2014 all as computed columns.\n", "\n", "Insert a new episode and the entire pipeline runs automatically: transcription, segment extraction, chapter titling, and timestamp formatting.\n", "\n", "**How WhisperX finds the chapter boundaries:**\n", "\n", "WhisperX uses PyAnnote VAD to detect where speech occurs in the audio. Pauses, silence, and transitions between speakers create natural segment boundaries. These boundaries become the chapter start times.\n", "\n", "**Why `list_iterator`?**\n", "\n", "Without `list_iterator`, you'd write custom UDFs to extract segments from the JSON, batch them into a single LLM prompt, and parse the response back apart. The view-based approach is more idiomatic \u2014 each segment is its own row, and computed columns operate on one row at a time.\n", "\n", "**Trade-offs:**\n", "\n", "- One LLM call per segment instead of one batched call \u2014 fine for short podcasts, but consider batching for episodes with 50+ segments\n", "- This approach requires running a full transcription model just to find where the pauses are\n", "- For longer episodes, WhisperX's `chunk_size` parameter controls how the audio is batched internally\n", "- The chapter titles depend on LLM quality \u2014 `gpt-4o-mini` is fast and cheap, use `gpt-4o` for higher quality" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## See also\n", "\n", "- [`json.list_iterator`](https://docs.pixeltable.com/sdk/latest/json#iterator-list_iterator) \u2014 Iterator for flattening JSON lists into view rows\n", "- [Transcribe audio](https://docs.pixeltable.com/howto/cookbooks/audio/audio-transcribe) \u2014 Fixed-interval splitting with local Whisper\n", "- [Summarize podcasts](https://docs.pixeltable.com/howto/cookbooks/audio/audio-summarize-podcast) \u2014 Transcription + LLM summarization\n", "- [Video scene detection](https://docs.pixeltable.com/howto/cookbooks/video/video-scene-detection) \u2014 Content-aware detection for video" ] } ], "metadata": { "kernelspec": { "display_name": "pixeltable", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.12" } }, "nbformat": 4, "nbformat_minor": 4 }