cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_json('data/collected_data.json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "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>url</th>\n",
       "      <th>votes</th>\n",
       "      <th>answers</th>\n",
       "      <th>views</th>\n",
       "      <th>tags</th>\n",
       "      <th>datetime</th>\n",
       "      <th>user</th>\n",
       "      <th>user_url</th>\n",
       "      <th>user_reputation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Electron uncaught error: Error in native callb...</td>\n",
       "      <td>/questions/76713998/electron-uncaught-error-er...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>[javascript, node.js, electron, ffi]</td>\n",
       "      <td>2023-07-18 14:44:25</td>\n",
       "      <td>Raul</td>\n",
       "      <td>/users/20093880/raul</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>tomcat installed in remote Ubuntu server doesn...</td>\n",
       "      <td>/questions/76713997/tomcat-installed-in-remote...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>[apache, tcp, firewall, ubuntu-22.04, tomcat10]</td>\n",
       "      <td>2023-07-18 14:44:17</td>\n",
       "      <td>ayada</td>\n",
       "      <td>/users/20757134/ayada</td>\n",
       "      <td>390.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Email Tracking using Google Analytics 4?</td>\n",
       "      <td>/questions/76713996/email-tracking-using-googl...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>[html, google-analytics, html-email, google-an...</td>\n",
       "      <td>2023-07-18 14:44:15</td>\n",
       "      <td>witchysofia</td>\n",
       "      <td>/users/22247037/witchysofia</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fatal error: Uncaught TypeError: call_user_fun...</td>\n",
       "      <td>/questions/76713995/fatal-error-uncaught-typee...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>[wordpress]</td>\n",
       "      <td>2023-07-18 14:44:13</td>\n",
       "      <td>Odene</td>\n",
       "      <td>/users/22247119/odene</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Need a good javascript stats package; or at le...</td>\n",
       "      <td>/questions/76713992/need-a-good-javascript-sta...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>[javascript, statistics, documentation, jstat]</td>\n",
       "      <td>2023-07-18 14:44:01</td>\n",
       "      <td>Tim Erickson</td>\n",
       "      <td>/users/2782564/tim-erickson</td>\n",
       "      <td>582.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               title  \\\n",
       "0  Electron uncaught error: Error in native callb...   \n",
       "1  tomcat installed in remote Ubuntu server doesn...   \n",
       "2           Email Tracking using Google Analytics 4?   \n",
       "3  Fatal error: Uncaught TypeError: call_user_fun...   \n",
       "4  Need a good javascript stats package; or at le...   \n",
       "\n",
       "                                                 url  votes  answers  views  \\\n",
       "0  /questions/76713998/electron-uncaught-error-er...      0        0      2   \n",
       "1  /questions/76713997/tomcat-installed-in-remote...      0        0      2   \n",
       "2  /questions/76713996/email-tracking-using-googl...      0        0      2   \n",
       "3  /questions/76713995/fatal-error-uncaught-typee...      0        0      2   \n",
       "4  /questions/76713992/need-a-good-javascript-sta...      0        0      2   \n",
       "\n",
       "                                                tags            datetime  \\\n",
       "0               [javascript, node.js, electron, ffi] 2023-07-18 14:44:25   \n",
       "1    [apache, tcp, firewall, ubuntu-22.04, tomcat10] 2023-07-18 14:44:17   \n",
       "2  [html, google-analytics, html-email, google-an... 2023-07-18 14:44:15   \n",
       "3                                        [wordpress] 2023-07-18 14:44:13   \n",
       "4     [javascript, statistics, documentation, jstat] 2023-07-18 14:44:01   \n",
       "\n",
       "           user                     user_url  user_reputation  \n",
       "0          Raul         /users/20093880/raul             29.0  \n",
       "1         ayada        /users/20757134/ayada            390.0  \n",
       "2   witchysofia  /users/22247037/witchysofia              1.0  \n",
       "3         Odene        /users/22247119/odene              1.0  \n",
       "4  Tim Erickson  /users/2782564/tim-erickson            582.0  "
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 49999 entries, 0 to 49998\n",
      "Data columns (total 10 columns):\n",
      " #   Column           Non-Null Count  Dtype         \n",
      "---  ------           --------------  -----         \n",
      " 0   title            49999 non-null  object        \n",
      " 1   url              49999 non-null  object        \n",
      " 2   votes            49999 non-null  int64         \n",
      " 3   answers          49999 non-null  int64         \n",
      " 4   views            49999 non-null  int64         \n",
      " 5   tags             49999 non-null  object        \n",
      " 6   datetime         49999 non-null  datetime64[ns]\n",
      " 7   user             49950 non-null  object        \n",
      " 8   user_url         49950 non-null  object        \n",
      " 9   user_reputation  49950 non-null  float64       \n",
      "dtypes: datetime64[ns](1), float64(1), int64(3), object(5)\n",
      "memory usage: 3.8+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "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>votes</th>\n",
       "      <th>answers</th>\n",
       "      <th>views</th>\n",
       "      <th>datetime</th>\n",
       "      <th>user_reputation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>49999.000000</td>\n",
       "      <td>49999.000000</td>\n",
       "      <td>49999.000000</td>\n",
       "      <td>49999</td>\n",
       "      <td>49950.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>-0.085162</td>\n",
       "      <td>0.559971</td>\n",
       "      <td>28.756595</td>\n",
       "      <td>2023-07-11 16:24:44.884897792</td>\n",
       "      <td>1096.769109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>-11.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2023-07-04 14:14:23</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>2023-07-08 04:05:11.500000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>2023-07-11 19:07:50</td>\n",
       "      <td>21.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>35.000000</td>\n",
       "      <td>2023-07-14 13:34:58</td>\n",
       "      <td>185.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>50.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>20000.000000</td>\n",
       "      <td>2023-07-18 14:44:25</td>\n",
       "      <td>513000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1.060015</td>\n",
       "      <td>0.758855</td>\n",
       "      <td>112.329822</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7639.895788</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              votes       answers         views  \\\n",
       "count  49999.000000  49999.000000  49999.000000   \n",
       "mean      -0.085162      0.559971     28.756595   \n",
       "min      -11.000000      0.000000      2.000000   \n",
       "25%        0.000000      0.000000     15.000000   \n",
       "50%        0.000000      0.000000     23.000000   \n",
       "75%        0.000000      1.000000     35.000000   \n",
       "max       50.000000     12.000000  20000.000000   \n",
       "std        1.060015      0.758855    112.329822   \n",
       "\n",
       "                            datetime  user_reputation  \n",
       "count                          49999     49950.000000  \n",
       "mean   2023-07-11 16:24:44.884897792      1096.769109  \n",
       "min              2023-07-04 14:14:23         1.000000  \n",
       "25%       2023-07-08 04:05:11.500000         1.000000  \n",
       "50%              2023-07-11 19:07:50        21.000000  \n",
       "75%              2023-07-14 13:34:58       185.000000  \n",
       "max              2023-07-18 14:44:25    513000.000000  \n",
       "std                              NaN      7639.895788  "
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of non existing users: 49\n"
     ]
    }
   ],
   "source": [
    "print('Number of non existing users:', sum(df['user'].isna()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dropna(subset=['user'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of questions after deleting non existing users: 49950\n"
     ]
    }
   ],
   "source": [
    "print('Number of questions after deleting non existing users:', len(df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove [duplicate] and [close] from titles\n",
    "df['title'] = df['title'].apply(lambda x: x.replace('[duplicate]', '').replace('[closed]', '').strip())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of unique tags: 15298\n",
      "Number of unique users: 37610\n"
     ]
    }
   ],
   "source": [
    "print('Number of unique tags:', len(df['tags'].explode().unique()))\n",
    "print('Number of unique users:', len(df['user'].unique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Min datetime: 2023-07-04 14:14:23\n",
      "Max datetime: 2023-07-18 14:44:25\n"
     ]
    }
   ],
   "source": [
    "print('Min datetime:', df['datetime'].min())\n",
    "print('Max datetime:', df['datetime'].max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tags\n",
       "python        6919\n",
       "javascript    4452\n",
       "reactjs       2746\n",
       "java          2523\n",
       "c#            2220\n",
       "html          1717\n",
       "android       1657\n",
       "node.js       1424\n",
       "css           1365\n",
       "flutter       1338\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['tags'].explode().value_counts().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['question_id'] = df['url'].apply(lambda x: x.split('/')[2])\n",
    "df['user_id'] = df['user_url'].apply(lambda x: x.split('/')[2])\n",
    "\n",
    "# Create the 'questions' DataFrame\n",
    "questions = df[['question_id', 'title', 'url', 'votes', 'answers', 'views', 'datetime']]\n",
    "\n",
    "# Create the 'users' DataFrame\n",
    "users = df[['user_id', 'user', 'user_url', 'user_reputation']].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "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>question_id</th>\n",
       "      <th>title</th>\n",
       "      <th>url</th>\n",
       "      <th>votes</th>\n",
       "      <th>answers</th>\n",
       "      <th>views</th>\n",
       "      <th>datetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76713998</td>\n",
       "      <td>Electron uncaught error: Error in native callb...</td>\n",
       "      <td>/questions/76713998/electron-uncaught-error-er...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2023-07-18 14:44:25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>76713997</td>\n",
       "      <td>tomcat installed in remote Ubuntu server doesn...</td>\n",
       "      <td>/questions/76713997/tomcat-installed-in-remote...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2023-07-18 14:44:17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>76713996</td>\n",
       "      <td>Email Tracking using Google Analytics 4?</td>\n",
       "      <td>/questions/76713996/email-tracking-using-googl...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2023-07-18 14:44:15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>76713995</td>\n",
       "      <td>Fatal error: Uncaught TypeError: call_user_fun...</td>\n",
       "      <td>/questions/76713995/fatal-error-uncaught-typee...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2023-07-18 14:44:13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>76713992</td>\n",
       "      <td>Need a good javascript stats package; or at le...</td>\n",
       "      <td>/questions/76713992/need-a-good-javascript-sta...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2023-07-18 14:44:01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  question_id                                              title  \\\n",
       "0    76713998  Electron uncaught error: Error in native callb...   \n",
       "1    76713997  tomcat installed in remote Ubuntu server doesn...   \n",
       "2    76713996           Email Tracking using Google Analytics 4?   \n",
       "3    76713995  Fatal error: Uncaught TypeError: call_user_fun...   \n",
       "4    76713992  Need a good javascript stats package; or at le...   \n",
       "\n",
       "                                                 url  votes  answers  views  \\\n",
       "0  /questions/76713998/electron-uncaught-error-er...      0        0      2   \n",
       "1  /questions/76713997/tomcat-installed-in-remote...      0        0      2   \n",
       "2  /questions/76713996/email-tracking-using-googl...      0        0      2   \n",
       "3  /questions/76713995/fatal-error-uncaught-typee...      0        0      2   \n",
       "4  /questions/76713992/need-a-good-javascript-sta...      0        0      2   \n",
       "\n",
       "             datetime  \n",
       "0 2023-07-18 14:44:25  \n",
       "1 2023-07-18 14:44:17  \n",
       "2 2023-07-18 14:44:15  \n",
       "3 2023-07-18 14:44:13  \n",
       "4 2023-07-18 14:44:01  "
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "questions.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "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>user_id</th>\n",
       "      <th>user</th>\n",
       "      <th>user_url</th>\n",
       "      <th>user_reputation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20093880</td>\n",
       "      <td>Raul</td>\n",
       "      <td>/users/20093880/raul</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20757134</td>\n",
       "      <td>ayada</td>\n",
       "      <td>/users/20757134/ayada</td>\n",
       "      <td>390.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22247037</td>\n",
       "      <td>witchysofia</td>\n",
       "      <td>/users/22247037/witchysofia</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22247119</td>\n",
       "      <td>Odene</td>\n",
       "      <td>/users/22247119/odene</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2782564</td>\n",
       "      <td>Tim Erickson</td>\n",
       "      <td>/users/2782564/tim-erickson</td>\n",
       "      <td>582.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    user_id          user                     user_url  user_reputation\n",
       "0  20093880          Raul         /users/20093880/raul             29.0\n",
       "1  20757134         ayada        /users/20757134/ayada            390.0\n",
       "2  22247037   witchysofia  /users/22247037/witchysofia              1.0\n",
       "3  22247119         Odene        /users/22247119/odene              1.0\n",
       "4   2782564  Tim Erickson  /users/2782564/tim-erickson            582.0"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "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>question_id</th>\n",
       "      <th>tag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76713998</td>\n",
       "      <td>javascript</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76713998</td>\n",
       "      <td>node.js</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76713998</td>\n",
       "      <td>electron</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76713998</td>\n",
       "      <td>ffi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>76713997</td>\n",
       "      <td>apache</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  question_id         tag\n",
       "0    76713998  javascript\n",
       "0    76713998     node.js\n",
       "0    76713998    electron\n",
       "0    76713998         ffi\n",
       "1    76713997      apache"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create the 'tags' DataFrame by exploding the 'tags' column\n",
    "tags = df[['question_id', 'tags']].explode('tags')\n",
    "\n",
    "# Rename the 'tags' column to 'tag'\n",
    "tags.rename(columns={'tags': 'tag'}, inplace=True)\n",
    "\n",
    "# Display the first few rows of the 'tags' DataFrame\n",
    "tags.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "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>user_id</th>\n",
       "      <th>question_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20093880</td>\n",
       "      <td>76713998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20757134</td>\n",
       "      <td>76713997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22247037</td>\n",
       "      <td>76713996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22247119</td>\n",
       "      <td>76713995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2782564</td>\n",
       "      <td>76713992</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    user_id question_id\n",
       "0  20093880    76713998\n",
       "1  20757134    76713997\n",
       "2  22247037    76713996\n",
       "3  22247119    76713995\n",
       "4   2782564    76713992"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create the 'user_question' DataFrame\n",
    "user_question = df[['user_id', 'question_id']]\n",
    "\n",
    "# Display the first few rows of the 'user_question' DataFrame\n",
    "user_question.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "# Create a connection to the SQLite database\n",
    "# Note: this will create the database file if it doesn't exist already\n",
    "conn = sqlite3.connect('data/stackoverflow_data.db')\n",
    "\n",
    "# Write the DataFrames to the database\n",
    "questions.to_sql('questions', conn, if_exists='replace', index=False)\n",
    "users.to_sql('users', conn, if_exists='replace', index=False)\n",
    "tags.to_sql('tags', conn, if_exists='replace', index=False)\n",
    "user_question.to_sql('user_question', conn, if_exists='replace', index=False)\n",
    "\n",
    "# Close the connection to the database\n",
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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.11.0"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}