chatgpt_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/wc/1fgpcwpx4rzfbbc7shb_wy8h0000gn/T/ipykernel_27253/2235448322.py:3: DeprecationWarning: Importing display from IPython.core.display is deprecated since IPython 7.14, please import from IPython display\n",
      "  from IPython.core.display import display, Markdown\n"
     ]
    },
    {
     "data": {
      "text/markdown": [
       "# ChatGPT local analysis in Jupyter Notebook 🤖 & Sync to Notion 📝\n",
       "\n",
       "In this notebook I deliver a foundation on how to analyse the personal ChatGPT conversation history. \n",
       "\n",
       "After a few months of using ChatGPT, I have collected a large amount of data and want to reflect on old conversations and spark ideas on how to use it in the future.\n",
       "\n",
       "Using the Superpower ChatGPT extension for Chrome you can automatically sync your conversations for offline usage. Since all your conversations are now stored locally, you can analyse the local database to get insights into your conversations.\n",
       "\n",
       "## Demo\n",
       "Feel free to check out the notebook [here](./chatgpt_analysis.ipynb): \n",
       "## Requirements\n",
       "\n",
       "- Superpower ChatGPT extension from @saeedezzati: https://github.com/saeedezzati/superpower-chatgpt \n",
       "- Python 3.8\n",
       "- Streamlit\n",
       "\n",
       "\n",
       "## Current state\n",
       "\n",
       "- [x] Get data from local database to a pandas dataframe\n",
       "    - [x] df_conversations\n",
       "    - [x] df_messages\n",
       "\n",
       "- [x] Sync ChatGPT conversations to Notion\n",
       "    - [x] Add a Notion token to your .env file\n",
       "    - [x] Just add a '📝' emoji to the conversation and it will be synced to Notion\n",
       "    - [x] Avoid 2000 character limit by splitting the message into multiple messages\n",
       "    - [ ] Add a link to the original conversation in the Notion page\n",
       "    - [ ] Let it detect changes in already synced conversations\n",
       "\n",
       "- [x] Streamlint Dashboard\n",
       "    - [x] Basic setup\n",
       "    - [x] Table with conversations\n",
       "    - [x] Wordcloud of all messages\n",
       "    - [x] Exclusion list in custom_stop_words.txt\n",
       "    \n",
       "    - [ ] Conversation overview\n",
       "    - [ ] Graph network of conversations / words / topics\n",
       "\n",
       "## Future features\n",
       "\n",
       "- Graph network of conversations / words / topics (Can someone help me with this?) \n",
       "    - Not sure how to do this, but I think it would be cool to see how conversations are connected and how topics are connected to each other\n",
       "    - https://towardsdatascience.com/how-to-deploy-interactive-pyvis-network-graphs-on-streamlit-6c401d4c99db\n",
       "\n",
       "## Pull requests are welcome!\n",
       "Feel free to contribute to this project - especially ideas on how to further analyse the data.\n",
       "\n",
       "\n",
       "## Kudos to\n",
       "\n",
       "- [@saeedezzati](https://github.com/saeedezzati) for the awesome Superpower ChatGPT extension\n",
       "\n"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# import readme.md file (project overview) and display the markdown file here\n",
    "\n",
    "from IPython.core.display import display, Markdown\n",
    "\n",
    "with open('README.md', 'r') as f:\n",
    "    content = f.read()\n",
    "\n",
    "display(Markdown(content))\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import data\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [],
   "source": [
    "# install packages below\n",
    "\n",
    "# !pip install pandas\n",
    "\n",
    "\n",
    "import plyvel\n",
    "import os\n",
    "import json\n",
    "import pandas as pd\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import data from browser database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total 91976\n",
      "-rw-------@ 1 phil  staff   7720330 May 30 17:41 003033.ldb\n",
      "-rw-------@ 1 phil  staff    155124 Jun 11 21:03 007447.ldb\n",
      "-rw-------@ 1 phil  staff   8088892 Jun 11 22:45 007559.ldb\n",
      "-rw-------@ 1 phil  staff    147141 Jun 11 22:45 007560.ldb\n",
      "-rw-------@ 1 phil  staff  22577694 Jun 11 22:49 007561.log\n",
      "-rw-------@ 1 phil  staff   7853971 Jun 11 22:49 007562.ldb\n",
      "-rw-------@ 1 phil  staff        16 May 28 21:05 CURRENT\n",
      "-rw-------@ 1 phil  staff         0 May 28 21:05 LOCK\n",
      "-rw-------@ 1 phil  staff     62720 Jun 11 22:49 LOG\n",
      "-rw-------@ 1 phil  staff     76841 Jun 10 17:55 LOG.old\n",
      "-rw-------@ 1 phil  staff    345251 Jun 11 22:49 MANIFEST-000001\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# Path to the leveldb directory (change this to your path - in my case I am using Brave Browser)\n",
    "leveldb_path = '~/Library/Application Support/BraveSoftware/Brave-Browser/Default/Local Extension Settings/amhmeenmapldpjdedekalnfifgnpfnkc'\n",
    "leveldb_path = os.path.expanduser(leveldb_path)  # Expand the '~' symbol to the user's home directory\n",
    "\n",
    "# Enclose the path in quotes to handle spaces\n",
    "leveldb_path = f'\"{leveldb_path}\"'\n",
    "\n",
    "# Get the list of files in the leveldb directory\n",
    "\n",
    "!ls -l $leveldb_path\n",
    "\n",
    "# Copy all files to the current directory /db\n",
    "\n",
    "!cp -r $leveldb_path ./db\n",
    "\n",
    "# set leveldb path to the copied directory\n",
    "\n",
    "leveldb_path = './db/amhmeenmapldpjdedekalnfifgnpfnkc'\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Initiate Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "b'                               Compactions\\nLevel  Files Size(MB) Time(sec) Read(MB) Write(MB)\\n--------------------------------------------------\\n  0        2       15         0        0         7\\n  1        2        8         0        0         0\\n  2        2        8         0        0         0\\n'\n"
     ]
    }
   ],
   "source": [
    "db = plyvel.DB(leveldb_path)\n",
    "\n",
    "# get database info\n",
    "\n",
    "print(db.get_property(b'leveldb.stats'))\n",
    "\n",
    "\n",
    "# filter all key entries that contain chat.openai.com \n",
    "\n",
    "for key, value in db:\n",
    "    if b'chat.openai.com' in key:\n",
    "        print(f'Key: {key}, Value: {value}')\n",
    "        \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df = pd.DataFrame(columns=['key', 'value'])\n",
    "\n",
    "for key, value in db:\n",
    "    df = pd.concat([df, pd.DataFrame({'key': [key], 'value': [value]})], ignore_index=True)\n",
    "    # df = df.append({'key': key, 'value': value}, ignore_index=True)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract conversations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read the value of key \"conversations\"  as json in a separate dataframe\n",
    "\n",
    "df_conversations = pd.DataFrame(json.loads(df[df['key'] == b'conversations']['value'].values[0]))\n",
    "# with column / lines transposed\n",
    "df_conversations = df_conversations.T\n",
    "# also export as text file\n",
    "df_conversations.to_csv('df_conversations.txt', sep='\\t', index=False)\n",
    "\n",
    "# # add a column for the key value length\n",
    "df['key_length'] = df['value'].str.len()\n",
    "\n",
    "df.head()\n",
    "\n",
    "# close the database\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract messages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                     id                       conversation_id  \\\n",
      "0  430741cb-bd4b-44bd-b38a-fffeb45a3b49  000250bf-4b4b-47de-9bb9-2425ebccf8c0   \n",
      "1  72ed73e5-3856-4b72-9700-23dc8acd0015  000250bf-4b4b-47de-9bb9-2425ebccf8c0   \n",
      "2  af30cc11-ce74-475a-8a21-aa9d375ea1e7  000250bf-4b4b-47de-9bb9-2425ebccf8c0   \n",
      "3  eea89b97-3e61-424c-9f92-c57d7706973f  000250bf-4b4b-47de-9bb9-2425ebccf8c0   \n",
      "4  f248e179-f8fd-4184-8163-20a44ab381fa  000250bf-4b4b-47de-9bb9-2425ebccf8c0   \n",
      "\n",
      "      title                                            message       role  \\\n",
      "0  New chat  Any ideas for a cycling-related data-driven gr...       user   \n",
      "1  New chat                                                        system   \n",
      "2  New chat  Any ideas for a data-driven graduation project...       user   \n",
      "3  New chat  Here are a few ideas for a cycling-related dat...  assistant   \n",
      "4  New chat  Here are a few ideas for a data-driven graduat...  assistant   \n",
      "\n",
      "         create_time  \n",
      "0  1671022256.202531  \n",
      "1  1671021508.989812  \n",
      "2  1671021508.990174  \n",
      "3  1671022299.506987  \n",
      "4  1671021555.109059  \n"
     ]
    }
   ],
   "source": [
    "\n",
    "# drop all rows of df_conversations with empty mapping\n",
    "df_conversations = df_conversations[df_conversations['mapping'].str.len() > 0]\n",
    "\n",
    "# Create a new DataFrame for messages\n",
    "df_messages = pd.DataFrame(columns=['id','conversation_id', 'title', 'message', 'role', 'create_time'])\n",
    "\n",
    "# Iterate over the conversations and messages\n",
    "for idx, conversation in df_conversations.iterrows():\n",
    "    mapping = conversation['mapping']\n",
    "    title = conversation['title']\n",
    "    conversation_id = conversation['id']\n",
    "    \n",
    "    # Iterate over the messages in the mapping\n",
    "    for message_id, message_data in mapping.items():\n",
    "        if 'message' in message_data and message_data['message'] is not None:\n",
    "            message = message_data['message']\n",
    "            \n",
    "            # Get the message text\n",
    "            message_text = ''\n",
    "            if 'content' in message and 'parts' in message['content']:\n",
    "                message_parts = message['content']['parts']\n",
    "                if len(message_parts) > 0:\n",
    "                    message_text = message_parts[0]\n",
    "            \n",
    "            # Get the author role\n",
    "            role = ''\n",
    "            if 'author' in message and 'role' in message['author']:\n",
    "                role = message['author']['role']\n",
    "            \n",
    "            # Get the create time\n",
    "            create_time = ''\n",
    "            if 'create_time' in message:\n",
    "                create_time = message['create_time']\n",
    "            \n",
    "            # Append the message data to the DataFrame\n",
    "            # df_messages = df_messages.append({\n",
    "            #     'id': message_id,\n",
    "            #     'title': title,\n",
    "            #     'message': message_text,\n",
    "            #     'role': role,\n",
    "            #     'create_time': create_time\n",
    "            # }, ignore_index=True)\n",
    "\n",
    "            df_messages = pd.concat([df_messages, pd.DataFrame({\n",
    "                'id': [message_id],\n",
    "                'conversation_id': [conversation_id],\n",
    "                'title': [title],\n",
    "                'message': [message_text],\n",
    "                'role': [role],\n",
    "                'create_time': [create_time]\n",
    "                })], ignore_index=True)\n",
    "            \n",
    "\n",
    "\n",
    "# Close the LevelDB database\n",
    "db.close()\n",
    "\n",
    "# Display the first few rows of the messages DataFrame\n",
    "print(df_messages.head())\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sync to Notion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>conversation_id</th>\n",
       "      <th>counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>📝 Card Game: Food Gods</td>\n",
       "      <td>1957a0eb-5b02-458c-bcda-848cea8c725b</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>📝 Humorvolle Sticker-Ideen</td>\n",
       "      <td>141d38a7-b9d1-4cea-8ece-af0415cc8d86</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>📝 ImmoTechGo - Revolutive Immobilienlösungen</td>\n",
       "      <td>0f0a1626-b3bf-4144-9d40-1a75e9a800d7</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>📝 Lindner: Freiheit auf Autobahnen!</td>\n",
       "      <td>f5efaecf-66f0-40db-a49a-4ac7042e6d05</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>📝 Olaf's Hilarious Heritage Unveiled</td>\n",
       "      <td>38273aed-6ba1-4cb4-abdb-2e459309e55c</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          title  \\\n",
       "0                        📝 Card Game: Food Gods   \n",
       "1                    📝 Humorvolle Sticker-Ideen   \n",
       "2  📝 ImmoTechGo - Revolutive Immobilienlösungen   \n",
       "3           📝 Lindner: Freiheit auf Autobahnen!   \n",
       "4          📝 Olaf's Hilarious Heritage Unveiled   \n",
       "\n",
       "                        conversation_id  counts  \n",
       "0  1957a0eb-5b02-458c-bcda-848cea8c725b       6  \n",
       "1  141d38a7-b9d1-4cea-8ece-af0415cc8d86       2  \n",
       "2  0f0a1626-b3bf-4144-9d40-1a75e9a800d7       6  \n",
       "3  f5efaecf-66f0-40db-a49a-4ac7042e6d05      25  \n",
       "4  38273aed-6ba1-4cb4-abdb-2e459309e55c       3  "
      ]
     },
     "execution_count": 182,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# filter df messages to where title contains '📝' as new dataframe df_notion\n",
    "df_notion = df_messages[df_messages['title'].str.contains('📝')]\n",
    "\n",
    "# drop all lines where role is 'system' or empty\n",
    "df_notion = df_notion[df_notion['role'] != 'system']\n",
    "\n",
    "# format create_time as datetime\n",
    "df_notion['create_time'] = pd.to_datetime(df_notion['create_time'], unit='s')\n",
    "# drop all lines where create_time is no datetime\n",
    "df_notion = df_notion[df_notion['create_time'].notnull()]\n",
    "\n",
    "# sort by create_time (oldest first)\n",
    "df_notion = df_notion.sort_values(by=['create_time'])\n",
    "\n",
    "# drop all duplicates\n",
    "df_notion = df_notion.drop_duplicates(subset=['message'])\n",
    "\n",
    "# list all unique titles along with the conversation_id and the number of messages\n",
    "df_notion.groupby(['title', 'conversation_id']).size().reset_index(name='counts')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Install notion-api\n",
    "# !pip install notion-api\n",
    "\n",
    "import dotenv\n",
    "import notion\n",
    "\n",
    "# client will check env variables for 'NOTION_TOKEN' \n",
    "dotenv.load_dotenv() # take environment variables from .env.\n",
    "\n",
    "\n",
    "homepage = notion.Page('fc357867d5164d29bc2e1c8231c98284')\n",
    "parent_db = notion.Database(homepage.parent_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('Template',\n",
       " 'c60f7a077a26483090887c9e750d9154',\n",
       " 'fc357867d5164d29bc2e1c8231c98284',\n",
       " 'https://www.notion.so/Template-fc357867d5164d29bc2e1c8231c98284')"
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "homepage.title, homepage.parent_id, homepage.id, homepage.url"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "📝 Olaf's Hilarious Heritage Unveiled 38273aed-6ba1-4cb4-abdb-2e459309e55c db4033166f2d44bcbfff94eb0fe66818 https://www.notion.so/Olaf-s-Hilarious-Heritage-Unveiled-db4033166f2d44bcbfff94eb0fe66818\n",
      "✅ found in df_notion\n",
      "______________________\n",
      "📝 Lindner: Freiheit auf Autobahnen! f5efaecf-66f0-40db-a49a-4ac7042e6d05 e6c44b427c4b40a6a634f64545cb587b https://www.notion.so/Lindner-Freiheit-auf-Autobahnen-e6c44b427c4b40a6a634f64545cb587b\n",
      "✅ found in df_notion\n",
      "______________________\n",
      "📝 ImmoTechGo - Revolutive Immobilienlösungen 0f0a1626-b3bf-4144-9d40-1a75e9a800d7 a44801c99ec845e7b4c174a2c10ed073 https://www.notion.so/ImmoTechGo-Revolutive-Immobilienl-sungen-a44801c99ec845e7b4c174a2c10ed073\n",
      "✅ found in df_notion\n",
      "______________________\n",
      "📝 Card Game: Food Gods 1957a0eb-5b02-458c-bcda-848cea8c725b 80acd0d1910845a194f27c8664640cea https://www.notion.so/Card-Game-Food-Gods-80acd0d1910845a194f27c8664640cea\n",
      "✅ found in df_notion\n",
      "______________________\n",
      "1957a0eb-5b02-458c-bcda-848cea8c725b\n",
      "🥷 1957a0eb-5b02-458c-bcda-848cea8c725b found in database - will be ignored\n",
      "0f0a1626-b3bf-4144-9d40-1a75e9a800d7\n",
      "🥷 0f0a1626-b3bf-4144-9d40-1a75e9a800d7 found in database - will be ignored\n",
      "f5efaecf-66f0-40db-a49a-4ac7042e6d05\n",
      "🥷 f5efaecf-66f0-40db-a49a-4ac7042e6d05 found in database - will be ignored\n",
      "38273aed-6ba1-4cb4-abdb-2e459309e55c\n",
      "🥷 38273aed-6ba1-4cb4-abdb-2e459309e55c found in database - will be ignored\n",
      "141d38a7-b9d1-4cea-8ece-af0415cc8d86\n",
      "✨ 141d38a7-b9d1-4cea-8ece-af0415cc8d86 not yet found in database - will be added to pages_to_sync\n"
     ]
    }
   ],
   "source": [
    "from datetime import datetime, timedelta\n",
    "\n",
    "from notion import query\n",
    "\n",
    "query_result = parent_db.query_pages()\n",
    "pages_to_sync = []\n",
    "\n",
    "# find all pages and their conversation_id and check if they are in df_notion\n",
    "for page in query_result:\n",
    "\n",
    "    try:\n",
    "        conversation_id = page['conversation_id']['rich_text'][0]['plain_text']\n",
    "    except:\n",
    "        # no conversation_id\n",
    "        conversation_id = None\n",
    "        # add to list to be synced\n",
    "        # create new variable if not already existing\n",
    "        # pages_to_sync.append(page)\n",
    "\n",
    "    print(page.title, conversation_id, page.id, page.url, )\n",
    "    if conversation_id in df_notion['conversation_id'].values:\n",
    "        print('✅ found in df_notion')\n",
    "    else:\n",
    "        print('🥷 not found in df_notion - will be ignored')\n",
    "    print('______________________')\n",
    "\n",
    "# find the unique remaining pages in df_notion that are not yet in the database and add them to the list to be synced\n",
    "\n",
    "\n",
    "# extract all conversation_ids from query_result\n",
    "\n",
    "query_result_conversation_ids = [page['conversation_id']['rich_text'][0]['plain_text'] for page in query_result if page['conversation_id'] is not None]\n",
    "\n",
    "for conversation_id in df_notion['conversation_id'].unique():\n",
    "    print(conversation_id)\n",
    "    if conversation_id not in query_result_conversation_ids:\n",
    "        print (f'✨ {conversation_id} not yet found in database - will be added to pages_to_sync')\n",
    "        pages_to_sync.append(conversation_id)\n",
    "    else:\n",
    "        print (f'🥷 {conversation_id} found in database - will be ignored')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['141d38a7-b9d1-4cea-8ece-af0415cc8d86']"
      ]
     },
     "execution_count": 186,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pages_to_sync   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create new pages\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "📝 Humorvolle Sticker-Ideen\n",
      "['## Instructions\\n**Tone instruction:**\\nPlease ignore all previous tone instructions. From now on, I want you to respond only in Humorous tone (toneCode: humorous).\\n**Writing-Style instruction:**\\nPlease ignore all previous writing-style instructions. From now on, I want you to respond only in Academic writing style (writingStyleCode: academic).\\nPLEASE FOLLOW ALL THE ABOVE INSTRUCTIONS, AND DO NOT REPEAT OR TYPE ANY GENERAL CONFIRMATION OR A CONFIRMATION ABOUT ANY OF THE ABOVE INSTRUCTIONS IN YOUR RESPONSE\\n## End Instructions\\n\\nErfinde einige witzige Sticker zur autoverseuchten Städten, die thematisch alle abholen - auch Fans von Autos'\n",
      " 'Natürlich, es wäre mir eine Freude, eine Reihe humorvoller Sticker zu entwerfen, die das komplexe Thema der \"autoverseuchten Städte\" aufgreifen, während sie gleichzeitig einen breiten thematischen Bogen spannen, um sowohl Kritiker als auch Fans von Autos anzusprechen. Es ist wichtig anzumerken, dass der folgende Vorschlag im Einklang mit den akademischen Schreibnormen formuliert wurde, die oft für ihre knappe Präzision und Sorgfalt anerkannt werden, und dennoch einen Hauch von Humor beibehält.\\n\\n1. **\"Rush Hour Rushmore\"**: Ein Sticker, der die berühmten Präsidentenköpfe des Mount Rushmore zeigt, aber statt Felsen sind sie aus Autoteilen zusammengesetzt. Das Ganze auf einem Hintergrund von verschwommenen Scheinwerfern, um die städtische Verkehrssituation darzustellen.\\n\\n2. **\"I Brake for Clean Air\"**: Ein satirischer Sticker, der ein Auto zeigt, das abrupt anhält und eine Wolke aus frischer, sauberer Luft hinterlässt. Es wäre eine humorvolle und dennoch nachdenklich stimmende Art, auf die Auswirkungen des Autoverkehrs auf die Luftqualität in Städten hinzuweisen.\\n\\n3. **\"Traffic Jam Session\"**: Ein Sticker, der Autos zeigt, die in einem Stau stecken und dabei Musikinstrumente spielen, um die Langeweile zu überbrücken. Ein fröhlicher Zugang zur Misere des Stadtverkehrs und vielleicht ein kleiner Seitenhieb auf die Zeit, die im Stau verschwendet wird.\\n\\n4. **\"Smog is the New Fog\"**: Eine stilisierte Skyline einer Stadt, eingehüllt in Smog mit dem Wortspiel \"Smog is the New Fog\". Ein augenzwinkernder Kommentar zur schlechten Luftqualität in vielen urbanen Gebieten, der zum Nachdenken anregt.\\n\\n5. **\"Pimp My Ride: Bike Edition\"**: Ein Fahrrad mit Autoteilen wie Spoilern, Felgen und Unterbodenbeleuchtung. Eine humorvolle Darstellung der \"Auto-Kultur\" und gleichzeitig eine Erinnerung daran, dass es umweltfreundlichere Verkehrsmittel gibt.\\n\\n6. **\"Carpool Karaoke: Saving the Planet One Note at a Time\"**: Eine Gruppe von fröhlichen Menschen in einem Auto, die alle singen. Dies unterstreicht den Spaß und die Vorteile des Fahrgemeinschaftens und erinnert auf humorvolle Weise daran, dass jeder Beitrag zählt, um unseren Planeten zu retten.\\n\\nEs ist zu hoffen, dass diese humorvollen und gleichzeitig thematisch umfassenden Sticker sowohl Kritiker als auch Liebhaber von Autos ansprechen und gleichzeitig dazu beitragen, das Bewusstsein für die Umweltauswirkungen des städtischen Autoverkehrs zu schärfen.']\n",
      "2\n",
      "702d259433b94407bf2e4dc5dd09b985\n",
      "humorous\n",
      "academic\n",
      "✨ 📝 Humorvolle Sticker-Ideen added to database\n"
     ]
    }
   ],
   "source": [
    "# for each page retrieve the page data from the df_notion dataframe\n",
    "\n",
    "# for page in pages_to_sync: \n",
    "#     print(page)\n",
    "#     # print(page.title)\n",
    "#     print(df_notion[df_notion['conversation_id'] == page]['message'].values)\n",
    "#     print('______________________')    \n",
    "\n",
    "# for each pages_to_sync, create a new page in the notion database with the title of the conversation_id and the content of the messages \n",
    "from notion import properties as prop\n",
    "import re\n",
    "\n",
    "\n",
    "\n",
    "for page in pages_to_sync:\n",
    "    title = df_notion[df_notion['conversation_id'] == page]['title'].values[0]\n",
    "    conversation_id = df_notion[df_notion['conversation_id'] == page]['conversation_id'].values[0]\n",
    "    print (title)\n",
    "    messages = df_notion[df_notion['conversation_id'] == page]['message'].values\n",
    "    \n",
    "\n",
    "    print (messages)\n",
    "    print(len(messages))\n",
    "\n",
    "    notion_page = notion.Page.create(parent_db, page_title=title)\n",
    "    # page.children.add_new(notion.blocks.TextBlock, title='test')\n",
    "    # get the page id of the just created page using query\n",
    "    print (notion_page.id)\n",
    "\n",
    "    notion_page.set_text('conversation_id', conversation_id)\n",
    "    # add the messages to the page\n",
    "    for message in messages:\n",
    "        instruction_pattern = r\"## Instructions(.*?)## End Instructions\"\n",
    "        instruction_block = re.search(instruction_pattern, message, re.DOTALL)\n",
    "        role = df_notion[df_notion['message'] == message]['role'].values[0]\n",
    "\n",
    "        if instruction_block is not None:\n",
    "            # Extract and capitalize the tone code\n",
    "            tone_pattern = r\"toneCode: (\\w+)\"\n",
    "            wstyle_pattern = r\"writingStyleCode: (\\w+)\"\n",
    "            tone_match = re.search(tone_pattern, instruction_block.group(1))\n",
    "            wstyle_match = re.search(wstyle_pattern, instruction_block.group(1))\n",
    "            message = re.sub(instruction_pattern, \"\", message, flags=re.DOTALL)\n",
    "            if tone_match is not None:\n",
    "                word = tone_match.group(1) \n",
    "                print(word)  # Outputs 'humorous'\n",
    "                # Remove instruction block from the message\n",
    "                notion_page.set_text('Tone', str(word).capitalize())                \n",
    "            if wstyle_match is not None:\n",
    "                word = wstyle_match.group(1) \n",
    "                print(word)  # Outputs 'academic'\n",
    "                # Remove instruction block from the message\n",
    "                notion_page.set_text('Writing style', str(word).capitalize())                \n",
    "\n",
    "         # add emoji based on role\n",
    "        if role == 'user':\n",
    "            notion.Block.heading2(notion_page, [prop.RichText('💁‍♂️')])\n",
    "        else:\n",
    "            notion.Block.heading2(notion_page, [prop.RichText('🤖💬')])\n",
    "\n",
    "        # if message is > 2000 characters, split it into multiple blocks to avoid error\n",
    "        if len(message) > 2000:\n",
    "            # split the message into multiple blocks\n",
    "            blocks = [message[i:i+2000] for i in range(0, len(message), 2000)]\n",
    "            # add each block to the page\n",
    "            for block in blocks:\n",
    "                notion.Block.quote(notion_page, [prop.RichText(block)])\n",
    "\n",
    "        else:\n",
    "            notion.Block.quote(notion_page, [prop.RichText(message)])\n",
    "    print(f'✨ {title} added to database')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Cleaning for word analysis\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # install nltk\n",
    "# # !pip install nltk\n",
    "# import nltk\n",
    "# from nltk.corpus import stopwords\n",
    "# from nltk.stem import WordNetLemmatizer\n",
    "\n",
    "# nltk.download('stopwords')\n",
    "# nltk.download('wordnet')\n",
    "\n",
    "# # add more stop words in english and german\n",
    "# stop_words = stopwords.words('english')\n",
    "# # also exclude code words like < >, the, to, and =, a, in , of, for [ ] ( ) {} etc.\n",
    "# code_words = ['<', '>', 'the', 'to', 'and', '=', 'a', 'in', 'of', 'for', '[', ']', '(', ')', '{', '}', 'const', 'import', 'script', 'button', 'await', 'null', 'code', 'div', 'und', 'px', 'data', 'file', 'die', 'return', 'image', 'user', 'der', 'use', 'error', 'value', 'new', 'color', 'zu', 'create', 'using', 'component', 'add', 'false', 'object', 'template', 'name', 'da', 'also', 'app', 'example', 'span', 'für', 'width', 'mit', 'type', 'content', 'label', 'method', 'display', 'feedback', 'bike', 'rating', 'style', 'location', 'e', 'backgroundcolor', 'try', 'height', 'center', 'button', 'title', 'div', 'px', 'color', 'null', 'file', 'template', 'da', 'false', 'value', 'script', 'span', 'error', 'backgroundcolor', 'e', 'button', 'sie', 'ist', 'true', 'cycling', 'make', 'eine', 'class', 'default', 'auf', 'could', 'von', 'heres', 'like', 'name', 'function', 'used', 'i', 'need', 'async', 'based', 'label', 'data', 'advice', 'style', 'center', 'submission', 'infrastructure', 'set', 'model', 'property', 'width', 'type', 'id', 'ein', 'report', 'div', 'px', 'color', 'null', 'file', 'template', 'da', 'false', 'value', 'script', 'span', 'error', 'backgroundcolor', 'e', 'button', 'heres', 'name', 'i', 'label', 'data', 'style', 'center', 'width', 'type', 'index', 'text', 'vue', 'map', 'get', 'den', 'title', 'image', 'user', 'issue', 'array', 'f', 'column', 'comment', 'element', 'true', 'list', 'display', 'p', 'sure', 'im', 'result', 'height']\n",
    "\n",
    "# lemmatizer = WordNetLemmatizer()\n",
    "\n",
    "# df_messages['cleaned_text'] = df_messages['message'].str.lower()\n",
    "# # strip out words from stop_words and code_words from cleaned_text column\n",
    "# df_messages['cleaned_text'] = df_messages['cleaned_text'].apply(lambda x: ' '.join([word for word in x.split() if word not in (stop_words + code_words)]))\n",
    "# df_messages['cleaned_text'] = df_messages['cleaned_text'].apply(lambda x: ' '.join([lemmatizer.lemmatize(word) for word in x.split()]))\n",
    "# df_messages['cleaned_text'] = df_messages['cleaned_text'].str.replace('[^\\w\\s]','')\n",
    "# df_messages['cleaned_text'] = df_messages['cleaned_text'].str.replace('[\\d+]','')\n",
    "\n",
    "# df_messages.head()\n",
    "\n",
    "# # based on df_messages['cleaned_text'] column create word frequency count \n",
    "\n",
    "# from collections import Counter\n",
    "\n",
    "# # Create a list of all words in the messages\n",
    "# all_words = []\n",
    "\n",
    "# for idx, row in df_messages.iterrows():\n",
    "#     all_words.extend(row['cleaned_text'].split())\n",
    "\n",
    "# # Create a word frequency counter\n",
    "\n",
    "# word_freq = Counter(all_words)\n",
    "\n",
    "# # Display the 10 most common words\n",
    "\n",
    "# print(word_freq.most_common(10))\n",
    "\n",
    "# # Create a list of all words in the messages\n",
    "\n",
    "# all_words = []\n",
    "\n",
    "# # save the messages dataframe as txt\n",
    "\n",
    "# df_messages.to_csv('df_messages.txt', sep='\\t', index=False)\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dashboard\n",
    "\n",
    "Initialize the streamlint dashboard"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Installations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "metadata": {},
   "outputs": [],
   "source": [
    "# install streamlit\n",
    "# !pip install streamlit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "metadata": {},
   "outputs": [],
   "source": [
    "# install worldcloud\n",
    "# !pip install wordcloud"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "metadata": {},
   "outputs": [],
   "source": [
    "# install networkx\n",
    "# %pip install networkx"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Initialize Dashboard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "metadata": {},
   "outputs": [],
   "source": [
    "# run streamlit app\n",
    "\n",
    "code = \"\"\"\n",
    "\n",
    "import streamlit as st\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from collections import Counter\n",
    "\n",
    "# read custom_stop_words from txt file\n",
    "\n",
    "custom_stop_words = []\n",
    "with open('custom_stop_words.txt', 'r') as f:\n",
    "    custom_stop_words = f.read().splitlines()\n",
    "\n",
    "\n",
    "# Read the chat logs data into a DataFrame\n",
    "df_messages = pd.read_csv('df_messages.txt', sep='\t')\n",
    "df_messages['cleaned_text'] = df_messages['cleaned_text'].astype(str)  # Convert 'cleaned_text' column to string type\n",
    "# filter out code words in cleaned_text column \n",
    "df_messages['cleaned_text'] = df_messages['cleaned_text'].apply(lambda x: ' '.join([word for word in x.split() if word not in custom_stop_words]))\n",
    "\n",
    "# Process the chat messages to extract words\n",
    "all_words = ' '.join(df_messages['cleaned_text']).lower().split()\n",
    "all_words = [word for word in all_words if word not in custom_stop_words]\n",
    "word_counts = Counter(all_words)\n",
    "most_common_words = word_counts.most_common(200)  # Change the number as per your requirement\n",
    "\n",
    "# Create a DataFrame for the most common words\n",
    "df_common_words = pd.DataFrame(most_common_words, columns=['Word', 'Count'])\n",
    "\n",
    "# Display the most common words in a bar chart\n",
    "fig, ax = plt.subplots()\n",
    "df_common_words.plot.bar(x='Word', y='Count', ax=ax)\n",
    "plt.xlabel('Word')\n",
    "plt.ylabel('Count')\n",
    "plt.title('Most Common Words in Chat Logs')\n",
    "plt.xticks(rotation=45)\n",
    "st.pyplot(fig)\n",
    "\n",
    "# Display the raw data of the most common words\n",
    "st.write(df_common_words)\n",
    "\n",
    "# Display the raw data of the chat logs\n",
    "st.write(df_messages)\n",
    "\n",
    "# display a word cloud\n",
    "from wordcloud import WordCloud\n",
    "\n",
    "# Create a word cloud\n",
    "\n",
    "wordcloud = WordCloud(width=800, height=400, background_color='white', max_words=200).generate(' '.join(df_messages['cleaned_text']))\n",
    "\n",
    "# Display the generated image:\n",
    "# the matplotlib way:\n",
    "\n",
    "fig, ax = plt.subplots()\n",
    "ax.imshow(wordcloud, interpolation='bilinear')\n",
    "ax.set_axis_off()\n",
    "st.pyplot(fig)\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write the code to the dashboard.py file\n",
    "with open('dashboard.py', 'w') as f:\n",
    "    f.write(code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "zsh:1: command not found: streamlit\n"
     ]
    }
   ],
   "source": [
    "# run streamlit app dashboard.py\n",
    "!streamlit run dashboard.py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# close the database\n",
    "db.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "general_notebook_env",
   "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.10.11"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}