simulate_podcast_episode.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "cell_execution_strategy": "setup",
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "source": [
        "pip install pandas google-cloud-bigquery --upgrade openai"
      ],
      "metadata": {
        "id": "6DUWmMq3BTY6"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "from google.cloud import bigquery\n",
        "import re\n",
        "from google.oauth2 import service_account\n",
        "import openai\n",
        "import json\n",
        "import random\n",
        "import html\n",
        "\n",
        "\n",
        "# Setup BigQuery client\n",
        "credentials = service_account.Credentials.from_service_account_file(\n",
        "    '/path/to/credentials/file')\n",
        "project_id = 'your-project-id'\n",
        "client = bigquery.Client(credentials=credentials, project=project_id)\n",
        "\n",
        "# Setup OpenAI client\n",
        "openai_client = openai.OpenAI(api_key='your-openai-api-key')\n",
        "\n",
        "def create_tables_if_not_exist():\n",
        "    dataset_ref = client.dataset('your-dataset-name')\n",
        "\n",
        "    # Create guest_profiles table\n",
        "    guest_profiles_schema = [\n",
        "        bigquery.SchemaField(\"guest_name\", \"STRING\", mode=\"REQUIRED\"),\n",
        "        bigquery.SchemaField(\"profile\", \"STRING\", mode=\"REQUIRED\"),\n",
        "    ]\n",
        "    guest_profiles_table_ref = dataset_ref.table(\"guest_profiles\")\n",
        "    guest_profiles_table = bigquery.Table(guest_profiles_table_ref, schema=guest_profiles_schema)\n",
        "    try:\n",
        "        client.get_table(guest_profiles_table_ref)\n",
        "    except Exception:\n",
        "        client.create_table(guest_profiles_table)\n",
        "        print(\"Created guest_profiles table.\")\n",
        "\n",
        "    # Create simulated_conversations table\n",
        "    simulated_conversations_schema = [\n",
        "        bigquery.SchemaField(\"topic\", \"STRING\", mode=\"REQUIRED\"),\n",
        "        bigquery.SchemaField(\"guests\", \"STRING\", mode=\"REQUIRED\"),\n",
        "        bigquery.SchemaField(\"guest_profiles\", \"STRING\", mode=\"REQUIRED\"),\n",
        "        bigquery.SchemaField(\"conversation\", \"STRING\", mode=\"REQUIRED\"),\n",
        "        bigquery.SchemaField(\"html_conversation\", \"STRING\", mode=\"REQUIRED\"),\n",
        "    ]\n",
        "    simulated_conversations_table_ref = dataset_ref.table(\"simulated_conversations\")\n",
        "    simulated_conversations_table = bigquery.Table(simulated_conversations_table_ref, schema=simulated_conversations_schema)\n",
        "    try:\n",
        "        client.get_table(simulated_conversations_table_ref)\n",
        "    except Exception:\n",
        "        client.create_table(simulated_conversations_table)\n",
        "        print(\"Created simulated_conversations table.\")\n",
        "\n",
        "def get_podcast_data():\n",
        "    query = \"\"\"\n",
        "    SELECT title, transcript, summary_and_insights\n",
        "    FROM `your-project-id.your-dataset-name.podcast_transcriptions`\n",
        "    \"\"\"\n",
        "    df = client.query(query).to_dataframe()\n",
        "    print(f\"Retrieved {len(df)} podcast episodes.\")\n",
        "    return df\n",
        "\n",
        "def extract_guest_from_title(title):\n",
        "    match = re.search(r'with (?:Special Guest|Guests?)\\s+(.*?)(?:\\s+and\\s+|$)', title, re.IGNORECASE)\n",
        "    if match:\n",
        "        return [name.strip() for name in re.split(r'\\s+and\\s+', match.group(1))]\n",
        "    return []\n",
        "\n",
        "def identify_guests(df):\n",
        "    all_guests = []\n",
        "    for title in df['title']:\n",
        "        guests = extract_guest_from_title(title)\n",
        "        all_guests.extend(guests)\n",
        "\n",
        "    unique_guests = sorted(list(set(all_guests)))\n",
        "    print(f\"Identified {len(unique_guests)} unique guests.\")\n",
        "    return unique_guests\n",
        "\n",
        "def get_guest_profile_from_bigquery(guest_name):\n",
        "    query = f\"\"\"\n",
        "    SELECT * FROM `your-project-id.your-dataset-name.guest_profiles`\n",
        "    WHERE guest_name = '{guest_name}'\n",
        "    \"\"\"\n",
        "    df = client.query(query).to_dataframe()\n",
        "    if not df.empty:\n",
        "        return json.loads(df.iloc[0]['profile'])\n",
        "    return None\n",
        "\n",
        "def write_guest_profile_to_bigquery(guest_name, profile):\n",
        "    table_id = 'your-project-id.your-dataset-name.guest_profiles'\n",
        "    rows_to_insert = [\n",
        "        {\n",
        "            'guest_name': guest_name,\n",
        "            'profile': json.dumps(profile)\n",
        "        }\n",
        "    ]\n",
        "    errors = client.insert_rows_json(table_id, rows_to_insert)\n",
        "    if errors == []:\n",
        "        print(f\"Profile for {guest_name} written to BigQuery successfully.\")\n",
        "    else:\n",
        "        print(f\"Errors occurred while writing profile for {guest_name}: {errors}\")\n",
        "\n",
        "def generate_guest_profile(guest_name, df):\n",
        "    profile = get_guest_profile_from_bigquery(guest_name)\n",
        "    if profile:\n",
        "        print(f\"Profile for {guest_name} retrieved from BigQuery.\")\n",
        "        return profile\n",
        "\n",
        "    guest_episodes = df[df['title'].str.contains(guest_name, case=False, regex=False)]\n",
        "\n",
        "    if guest_episodes.empty:\n",
        "        print(f\"No episodes found for guest: {guest_name}\")\n",
        "        return None\n",
        "\n",
        "    all_transcripts = \" \".join(guest_episodes['transcript'].tolist())\n",
        "    all_summaries = \" \".join(guest_episodes['summary_and_insights'].tolist())\n",
        "\n",
        "    prompt = f\"\"\"\n",
        "    Analyze the following podcast transcripts and summaries for guest {guest_name}.\n",
        "    Create a brief profile of the guest, including:\n",
        "    1. Their area of expertise\n",
        "    2. Their communication style (be specific and colorful in your description)\n",
        "    3. Key opinions or viewpoints they express (include controversial or unique perspectives)\n",
        "    4. Any recurring themes or topics they discuss\n",
        "    5. Their stance on current trends in data analytics or business intelligence\n",
        "    6. Any memorable quotes or catchphrases they use\n",
        "\n",
        "    Transcripts: {all_transcripts[:2000]}...\n",
        "\n",
        "    Summaries: {all_summaries[:2000]}...\n",
        "\n",
        "    Provide the profile in JSON format with the following keys:\n",
        "    expertise, communication_style, key_opinions, recurring_themes, trend_stance, memorable_quotes\n",
        "    \"\"\"\n",
        "\n",
        "    response = openai_client.chat.completions.create(\n",
        "        model=\"gpt-4\",\n",
        "        messages=[\n",
        "            {\"role\": \"system\", \"content\": \"You are an AI assistant tasked with creating colorful, opinionated profiles of podcast guests.\"},\n",
        "            {\"role\": \"user\", \"content\": prompt}\n",
        "        ],\n",
        "        max_tokens=800\n",
        "    )\n",
        "\n",
        "    try:\n",
        "        profile = json.loads(response.choices[0].message.content)\n",
        "        print(f\"Generated profile for guest: {guest_name}\")\n",
        "        write_guest_profile_to_bigquery(guest_name, profile)\n",
        "        return profile\n",
        "    except json.JSONDecodeError:\n",
        "        print(f\"Error decoding profile for guest: {guest_name}\")\n",
        "        return None\n",
        "\n",
        "def generate_topic(df):\n",
        "    all_titles = \" \".join(df['title'].tolist())\n",
        "    all_summaries = \" \".join(df['summary_and_insights'].tolist())\n",
        "\n",
        "    prompt = f\"\"\"\n",
        "    Based on the following podcast episode titles and summaries, generate an interesting and potentially controversial topic for a new podcast discussion.\n",
        "    The topic should be related to data analytics, business intelligence, or modern data stack technologies, but should NOT focus on real-time data analytics.\n",
        "    Choose from a diverse range of subjects such as:\n",
        "    1. Data governance and ethics\n",
        "    2. AI and machine learning in business intelligence\n",
        "    3. The role of data scientists vs. business analysts\n",
        "    4. Cloud vs. on-premise data solutions\n",
        "    5. Data visualization techniques and their impact\n",
        "    6. The future analytics consulting and modern data stack vendors\n",
        "    7. Data privacy regulations and their effect on analytics\n",
        "    8. The impact of big data on small businesses\n",
        "    9. Predictive analytics and its real-world applications\n",
        "    10. The evolution of data warehousing\n",
        "\n",
        "    Phrase the topic as a provocative question that would prompt an engaging and opinionated discussion among experts in the field.\n",
        "\n",
        "    Titles: {all_titles[:1000]}...\n",
        "\n",
        "    Summaries: {all_summaries[:2000]}...\n",
        "\n",
        "    Provide the topic as a single question that challenges conventional wisdom or addresses a contentious issue in the field.\n",
        "    \"\"\"\n",
        "\n",
        "    response = openai_client.chat.completions.create(\n",
        "        model=\"gpt-4\",\n",
        "        messages=[\n",
        "            {\"role\": \"system\", \"content\": \"You are an AI assistant tasked with generating diverse and provocative podcast discussion topics about data analytics and business intelligence.\"},\n",
        "            {\"role\": \"user\", \"content\": prompt}\n",
        "        ],\n",
        "        max_tokens=100\n",
        "    )\n",
        "\n",
        "    topic = response.choices[0].message.content.strip()\n",
        "    print(f\"Generated topic: {topic}\")\n",
        "    return topic\n",
        "\n",
        "def select_relevant_guests(topic, guest_profiles, max_guests=5):\n",
        "    relevant_guests = []\n",
        "    for guest, profile in guest_profiles.items():\n",
        "        relevance_score = sum(word.lower() in topic.lower() for word in profile['expertise'].split())\n",
        "        relevance_score += sum(word.lower() in topic.lower() for word in profile['recurring_themes'])\n",
        "        relevant_guests.append((guest, relevance_score))\n",
        "\n",
        "    relevant_guests.sort(key=lambda x: x[1], reverse=True)\n",
        "    return [guest for guest, score in relevant_guests[:max_guests]]\n",
        "\n",
        "def simulate_conversation(topic, guest_profiles, df, num_exchanges=5):\n",
        "    all_transcripts = \" \".join(df['transcript'].tolist())\n",
        "    questions = re.findall(r\"Mark Rittman: (.*?\\?)\", all_transcripts)\n",
        "    relevant_questions = [q for q in questions if any(word in q.lower() for word in topic.lower().split())]\n",
        "\n",
        "    conversation = [f\"Mark Rittman: Welcome to the Simulated Drill to Detail Podcast! Today, we're diving into a hot topic: {topic}\"]\n",
        "\n",
        "    selected_guests = select_relevant_guests(topic, guest_profiles)\n",
        "\n",
        "    for i in range(num_exchanges):\n",
        "        if relevant_questions and random.random() < 0.7:\n",
        "            question = random.choice(relevant_questions)\n",
        "        else:\n",
        "            prompt = f\"\"\"\n",
        "            Based on the provocative topic \"{topic}\", generate a challenging follow-up question that Mark Rittman might ask.\n",
        "            The question should be insightful, potentially controversial, and aimed at sparking an opinionated discussion.\n",
        "            \"\"\"\n",
        "            response = openai_client.chat.completions.create(\n",
        "                model=\"gpt-4\",\n",
        "                messages=[\n",
        "                    {\"role\": \"system\", \"content\": \"You are Mark Rittman, the host of a data analytics podcast known for asking tough, thought-provoking questions.\"},\n",
        "                    {\"role\": \"user\", \"content\": prompt}\n",
        "                ],\n",
        "                max_tokens=100\n",
        "            )\n",
        "            question = response.choices[0].message.content.strip()\n",
        "\n",
        "        conversation.append(f\"Mark Rittman: {question}\")\n",
        "\n",
        "        guest = random.choice(selected_guests)\n",
        "        guest_profile = guest_profiles[guest]\n",
        "\n",
        "        prompt = f\"\"\"\n",
        "        You are {guest}, a guest on a data analytics podcast. Your expertise is in {guest_profile['expertise']}.\n",
        "        Your communication style is {guest_profile['communication_style']}.\n",
        "        Some of your key opinions are: {', '.join(guest_profile['key_opinions'])}.\n",
        "        Your stance on current trends: {guest_profile['trend_stance']}\n",
        "        You often use phrases like: {', '.join(guest_profile['memorable_quotes'])}\n",
        "\n",
        "        Respond to the following question from the host, Mark Rittman:\n",
        "        \"{question}\"\n",
        "\n",
        "        Your response should be:\n",
        "        1. In your unique communication style\n",
        "        2. Reflect your expertise and strong opinions\n",
        "        3. Potentially controversial or challenge conventional wisdom\n",
        "        4. Include a memorable quote or catchphrase if appropriate\n",
        "        5. Engaging and thought-provoking\n",
        "\n",
        "        Aim to provide a response that will spark further discussion or debate.\n",
        "        \"\"\"\n",
        "\n",
        "        response = openai_client.chat.completions.create(\n",
        "            model=\"gpt-4\",\n",
        "            messages=[\n",
        "                {\"role\": \"system\", \"content\": f\"You are {guest}, an opinionated expert in {guest_profile['expertise']}, responding to a podcast question.\"},\n",
        "                {\"role\": \"user\", \"content\": prompt}\n",
        "            ],\n",
        "            max_tokens=300\n",
        "        )\n",
        "\n",
        "        guest_response = response.choices[0].message.content.strip()\n",
        "        conversation.append(f\"{guest}: {guest_response}\")\n",
        "\n",
        "    return conversation, selected_guests\n",
        "\n",
        "def generate_html_conversation(topic, guests, conversation):\n",
        "    html_content = f\"\"\"\n",
        "<!DOCTYPE html>\n",
        "<html lang=\"en\">\n",
        "<head>\n",
        "    <meta charset=\"UTF-8\">\n",
        "    <meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\">\n",
        "    <title>Simulated Podcast Conversation</title>\n",
        "    <style>\n",
        "        body {{ font-family: Arial, sans-serif; line-height: 1.6; max-width: 800px; margin: 0 auto; padding: 20px; }}\n",
        "        h1 {{ color: #333; }}\n",
        "        .host {{ color: #0066cc; }}\n",
        "        .guest {{ color: #006600; }}\n",
        "        .question, .answer {{ margin-bottom: 20px; }}\n",
        "    </style>\n",
        "</head>\n",
        "<body>\n",
        "    <h1>{html.escape(topic)}</h1>\n",
        "    \"\"\"\n",
        "\n",
        "    for i, message in enumerate(conversation):\n",
        "        speaker, content = message.split(': ', 1)\n",
        "        if speaker == \"Mark Rittman\":\n",
        "            html_content += f\"\"\"\n",
        "    <div class=\"question\">\n",
        "        <p class=\"host\"><strong>{html.escape(speaker)}:</strong> {html.escape(content)}</p>\n",
        "    </div>\n",
        "    \"\"\"\n",
        "        else:\n",
        "            html_content += f\"\"\"\n",
        "    <div class=\"answer\">\n",
        "        <p class=\"guest\"><strong>{html.escape(speaker)}:</strong> {html.escape(content)}</p>\n",
        "    </div>\n",
        "    \"\"\"\n",
        "\n",
        "    html_content += \"\"\"\n",
        "</body>\n",
        "</html>\n",
        "    \"\"\"\n",
        "\n",
        "    return html_content\n",
        "\n",
        "def write_conversation_to_bigquery(topic, guests, guest_profiles, conversation):\n",
        "    table_id = 'ra-development.analytics_seed.simulated_conversations'\n",
        "\n",
        "    html_conversation = generate_html_conversation(topic, guests, conversation)\n",
        "\n",
        "    rows_to_insert = [\n",
        "        {\n",
        "            'topic': topic,\n",
        "            'guests': json.dumps(guests),\n",
        "            'guest_profiles': json.dumps({guest: guest_profiles[guest] for guest in guests}),\n",
        "            'conversation': json.dumps(conversation),\n",
        "            'html_conversation': html_conversation\n",
        "        }\n",
        "    ]\n",
        "\n",
        "    errors = client.insert_rows_json(table_id, rows_to_insert)\n",
        "    if errors == []:\n",
        "        print(f\"Conversation on '{topic}' written to BigQuery successfully.\")\n",
        "    else:\n",
        "        print(f\"Errors occurred while writing conversation: {errors}\")\n",
        "\n",
        "# Main execution\n",
        "create_tables_if_not_exist()\n",
        "df = get_podcast_data()\n",
        "guests = identify_guests(df)\n",
        "\n",
        "guest_profiles = {}\n",
        "for guest in guests:\n",
        "    profile = generate_guest_profile(guest, df)\n",
        "    if profile:\n",
        "        guest_profiles[guest] = profile\n",
        "\n",
        "for _ in range(10):\n",
        "    topic = generate_topic(df)\n",
        "    conversation, selected_guests = simulate_conversation(topic, guest_profiles, df)\n",
        "    write_conversation_to_bigquery(topic, selected_guests, guest_profiles, conversation)\n",
        "\n",
        "print(\"Completed generating and storing 10 simulated conversations.\")"
      ],
      "metadata": {
        "id": "FFbQQPnxBVZ6"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}