main.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Описание задачи\n",
    "\n",
    "1. Пусть имеется книжный Интернет-магазин с простой структурой:  \n",
    "index.phtml - главная страница  \n",
    "|--catalog.phtml - сценарий просмотра каталога  \n",
    "|--search.phtml - сценарий поиска  \n",
    "|--addbasket.phtml?id_book=NN - сценарий добавления в корзину книги с номером NN  \n",
    "|--order.phtml - сценарий оформления заказа\n",
    "2. Пусть имеется лог-файл web-сервера Apache с данными за месяц работы интернет-магазина access.log.\n",
    "3. Необходимо выполнить следующие задачи:\n",
    "    1. Распарсить данные файла _access.log_ используя python;\n",
    "    2. Создать БД, используя СУБД _Postgresql_;\n",
    "    3. Залить распарсенные данные в БД;\n",
    "    4. Подключиться к БД через Python;\n",
    "    5. Ответить на вопросы из списка ниже:\n",
    "        - Сколько посетителей было на сайте за месяц?\n",
    "        - Сколько в среднем посетителей бывает за час?\n",
    "        - Сколько посетителей сделало заказы?\n",
    "        - Сколько страниц просмотрел посетитель в среднем, максимум, минимум?\n",
    "        - Сколько времени прошло с момента входа на сайт до оформления заказа, в среднем, максимум, минимум?\n",
    "        - Сколько в среднем заказов оформляется за день?\n",
    "        - Имеются ли какие-то взаимозависимые товары, которые покупатель кладет в корзину (например, с уровнем поддержки выше 0.01 и достоверностью выше 0.6)? (Задача поиск ассоциативных правил).\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Подготовка к работе\n",
    "\n",
    "1. Создали новую директорию: <code>mkdir exercise_1</code>;\n",
    "2. Создали виртуальное окружение для python: <code>python -m venv venv</code>;\n",
    "3. Активируем виртульное окружение venv: <code>sourse venv/bin/activate</code>;\n",
    "4. Создали файл _requirements.txt_: <code>touch requirements.txt</code>;\n",
    "5. Добавили необходимые библиотеки в _requirements.txt_:\n",
    "    - <code>vim requirements.txt</code>;\n",
    "    - pandas == 1.5.0  \n",
    "    numpy == 1.23.4  \n",
    "    mlxtend == 0.21.0  \n",
    "    openpyxl == 3.0.10  \n",
    "    psycopg2-binary == 2.9.4  \n",
    "    sqlalchemy == 1.4.42  \n",
    "6. Устанавливаем все библиоки из _requirements.txt_: <code>pip install -r requirements.txt</code>;\n",
    "7. Скачиваем и переносим файл access.log в нашу директорию _exercise_1_;\n",
    "8. Переходим на github и создаем новый репозиторий, копируем SSH-ключ;\n",
    "9. Инициализируем git в нашей директории:<code>git init</code>;\n",
    "10. Создаем файл _.gitignore_: <code>touch .gitignore</code>;\n",
    "10. Добавляем в файл _.gitignore_ файлы и папки, которые не будут передаваться в github:\n",
    "    - <code>vim .gitignore</code>;\n",
    "    - venv/\n",
    "11. Добавляем файлы в коммит: <code>git add .</code>\n",
    "12. Убеждаемся, что все прошло хорошо: <code>git status</code>\n",
    "13. Коммитим указанные изменения и подготавливаем к отправке в удаленный репозиторий: <code>git commit -m \"First commit\"</code>\n",
    "14. Указываем на новый удаленный репозиторий: <code>git remote -v</code>\n",
    "15. Проверяем новый удаленный адрес: <code>git remote add origin ссылка на удаленный репозиторий</code>\n",
    "16. Отправляем изменения локального репозитория в удаленный, помеченный как источник: <code>git push origin master</code>\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Парсинг файла access.log"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Создаем новый файл для парсинга файла _access.log_: <code>touch main.ipynb</code>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "sys.executable # убеждаемся, что используем верное вирутальное окружение\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore') # отключаем warnings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Исходя из структуры файла, очищаем файл access.log\n",
    "\n",
    "full_data = []\n",
    "with open(\"access.log\", \"r\") as f:\n",
    "    for line in f:\n",
    "        line = line.replace(\" - - \", \" \")\\\n",
    "            .replace(\" HTTP 1.1\", \"\")\\\n",
    "            .replace(\"[\", \"\")\\\n",
    "            .replace(\"]\", \"\")\\\n",
    "            .replace('\"', '')\\\n",
    "            .replace(\"ID\", \"\")\\\n",
    "            .split()\n",
    "        full_data.append(line)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# В pandas удобно работать с табличными данными, поэтому превращаем наши данные в DataFrame\n",
    "data = pd.DataFrame(full_data, columns=['ip', 'date', 'utc', 'method', 'query', 'status', 'doc_size', 'id']) \n",
    "\n",
    "# Удаляем ненужные колонки - они неизменяются и не несут никакой полезной информации для нашей задачи\n",
    "data = data.drop(['utc', 'status'], axis=1)\n",
    "\n",
    "# Переводим признак date в формат datetime, так как изначально pandas воспринимал их как string\n",
    "data['date'] = pd.to_datetime(data['date'], format='%d/%b/%Y:%H:%M:%S')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>ip</th>\n",
       "      <th>date</th>\n",
       "      <th>method</th>\n",
       "      <th>query</th>\n",
       "      <th>doc_size</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>69.215.29.74</td>\n",
       "      <td>2015-12-01 00:00:12</td>\n",
       "      <td>POST</td>\n",
       "      <td>/search.phtml</td>\n",
       "      <td>3491</td>\n",
       "      <td>4923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>69.215.29.74</td>\n",
       "      <td>2015-12-01 00:00:29</td>\n",
       "      <td>POST</td>\n",
       "      <td>/catalog.phtml</td>\n",
       "      <td>2143</td>\n",
       "      <td>4923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>151.186.168.158</td>\n",
       "      <td>2015-12-01 00:00:44</td>\n",
       "      <td>POST</td>\n",
       "      <td>/catalog.phtml</td>\n",
       "      <td>2633</td>\n",
       "      <td>4736</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                ip                date method           query doc_size    id\n",
       "0     69.215.29.74 2015-12-01 00:00:12   POST   /search.phtml     3491  4923\n",
       "1     69.215.29.74 2015-12-01 00:00:29   POST  /catalog.phtml     2143  4923\n",
       "2  151.186.168.158 2015-12-01 00:00:44   POST  /catalog.phtml     2633  4736"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.to_csv('clear.csv', header=False) # записываем наши обработанные данные в csv файл"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Работа с БД\n",
    "\n",
    "1. Для работы я буду использовать pgAdmin 4;\n",
    "2. Открываем pgAdmin 4;\n",
    "3. Разворачиваем localhost в левой части экрана;\n",
    "4. Нажимаем правой кнопкой мыши по вкладке localhost/Databases и нажимаем кнопку Create/Database...;\n",
    "5. В открывшемся окне задаю наименование \"University\" и нажимаю кнопку \"Save\";\n",
    "6. Развворачиваем localhost/Databases/University/Schemas/Tables и нажимаем кнопку Create/Table...;\n",
    "7. Во вкладке General в поле name задаем \"shopnew\";\n",
    "\n",
    "<h2>Создание таблицы: первый способ</h2>\n",
    "\n",
    "1. Переходим во вкладку Columns и добавляем следующие колонки:\n",
    "\n",
    "      <table border=\"1\">\n",
    "      <caption>Заполнение вкладки Columns</caption>\n",
    "      <tr>\n",
    "        <th>Name</th>\n",
    "        <th>Data type</th>\n",
    "        <th>Length/Precision</th>\n",
    "        <th>Not NULL?</th>\n",
    "        <th>Primary Key</th>\n",
    "        <th>Constraints/Type</th>\n",
    "      </tr>\n",
    "        <tr><td>id</td><td>integer</td><td>-</td><td>yes</td><td>yes</td><td>IDENTITY</td></tr>\n",
    "        <tr><td>ip</td><td>character varying</td><td>-</td><td>yes</td><td>-</td><td>NONE</td></tr>\n",
    "        <tr><td>date</td><td>timestamp without timezone</td><td>-</td><td>yes</td><td>-</td><td>NONE</td></tr>\n",
    "        <tr><td>method</td><td>character varying</td><td>4</td><td>yes</td><td>-</td><td>NONE</td></tr>\n",
    "        <tr><td>query</td><td>character varying</td><td>-</td><td>yes</td><td>-</td><td>NONE</td></tr>\n",
    "        <tr><td>doc_size</td><td>integer</td><td>-</td><td>yes</td><td>-</td><td>NONE</td></tr>\n",
    "        <tr><td>user_id</td><td>integer</td><td>-</td><td>yes</td><td>-</td><td>NONE</td></tr>\n",
    "      </table>\n",
    "      \n",
    "      Нажимаем кнопку \"Save\";\n",
    "    \n",
    "2. Нажимаем правой кнопкой мыши по localhost/Databases/University/Schemas/Tables/shopnew и выбираем кнопку \"Import/Export Data...\";\n",
    "3. В открывшемся окне в поле \"Import/Export\" перечключаемся на кладку \"Import\" и в поле Filename выбираем наш обработанный файл <i>clear.csv</i>.\n",
    "\n",
    "<h2>Создание таблицы: второй способ</h2>\n",
    "\n",
    "1. Нажимаем правой кнопкой мыши по localhost/Databases/University/Schemas/Tables/shopnew и выбираем кнопку \"Query Tool\";\n",
    "2. В октрывшемся окне прописываем скприт из файла <i>create_table.txt</i> и запускаем его;\n",
    "3. Нажимаем правой кнопкой мыши по localhost/Databases/University/Schemas/Tables/shopnew и выбираем кнопку \"Import/Export Data...\";\n",
    "4. В открывшемся окне в поле \"Import/Export\" перечключаемся на кладку \"Import\" и в поле Filename выбираем наш обработанный файл <i>clear.csv</i>."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Подключение к БД через Python. Запросы - анализ данных."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>id</th>\n",
       "      <th>ip</th>\n",
       "      <th>date</th>\n",
       "      <th>method</th>\n",
       "      <th>query</th>\n",
       "      <th>doc_size</th>\n",
       "      <th>user_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>69.215.29.74</td>\n",
       "      <td>2015-12-01 00:00:12</td>\n",
       "      <td>POST</td>\n",
       "      <td>/search.phtml</td>\n",
       "      <td>3491</td>\n",
       "      <td>4923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>69.215.29.74</td>\n",
       "      <td>2015-12-01 00:00:29</td>\n",
       "      <td>POST</td>\n",
       "      <td>/catalog.phtml</td>\n",
       "      <td>2143</td>\n",
       "      <td>4923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>151.186.168.158</td>\n",
       "      <td>2015-12-01 00:00:44</td>\n",
       "      <td>POST</td>\n",
       "      <td>/catalog.phtml</td>\n",
       "      <td>2633</td>\n",
       "      <td>4736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>69.215.29.74</td>\n",
       "      <td>2015-12-01 00:00:51</td>\n",
       "      <td>POST</td>\n",
       "      <td>/catalog.phtml</td>\n",
       "      <td>2466</td>\n",
       "      <td>4923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>156.197.248.14</td>\n",
       "      <td>2015-12-01 00:01:06</td>\n",
       "      <td>POST</td>\n",
       "      <td>/search.phtml</td>\n",
       "      <td>2243</td>\n",
       "      <td>4920</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id               ip                date method           query  doc_size  \\\n",
       "0   0     69.215.29.74 2015-12-01 00:00:12   POST   /search.phtml      3491   \n",
       "1   1     69.215.29.74 2015-12-01 00:00:29   POST  /catalog.phtml      2143   \n",
       "2   2  151.186.168.158 2015-12-01 00:00:44   POST  /catalog.phtml      2633   \n",
       "3   3     69.215.29.74 2015-12-01 00:00:51   POST  /catalog.phtml      2466   \n",
       "4   4   156.197.248.14 2015-12-01 00:01:06   POST   /search.phtml      2243   \n",
       "\n",
       "   user_id  \n",
       "0     4923  \n",
       "1     4923  \n",
       "2     4736  \n",
       "3     4923  \n",
       "4     4920  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# В библиотеке Pandas имеется функция read_sql, котороая позволяет удобно поодлючиться к БД,\n",
    "# проводить запросы и воводить данные в виде DataFrame\n",
    "\n",
    "# Пример работы\n",
    "data = pd.read_sql(\n",
    "    \"\"\"\n",
    "    SELECT * FROM shopnew;\n",
    "    \"\"\",\n",
    "    con=\"postgresql://postgres:password@localhost:5432/University\"\n",
    ")\n",
    "\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>month</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12.0</td>\n",
       "      <td>239434</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   month   count\n",
       "0   12.0  239434"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Сколько посетителей было на сайте за месяц?\n",
    "data = pd.read_sql(\n",
    "    \"\"\"\n",
    "    SELECT DISTINCT\n",
    "        EXTRACT(month from date) as month, COUNT(*)\n",
    "    FROM shopnew\n",
    "    GROUP BY EXTRACT(month from date);\n",
    "    \"\"\",\n",
    "    con=\"postgresql://postgres:password@localhost:5432/University\"\n",
    ")\n",
    "\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>mean</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9976.42</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      mean\n",
       "0  9976.42"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Сколько в среднем посетителей бывает за час?\n",
    "data = pd.read_sql(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "        ROUND(AVG(number_of_users), 2) as mean\n",
    "    FROM\n",
    "    (SELECT\n",
    "        EXTRACT(hour from date) as hour, count(*) as number_of_users\n",
    "    FROM shopnew\n",
    "    GROUP BY EXTRACT(hour from date)) as t;\n",
    "    \"\"\",\n",
    "    con=\"postgresql://postgres:password@localhost:5432/University\"\n",
    ")\n",
    "\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1786</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count\n",
       "0   1786"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Сколько посетителей сделало заказы?\n",
    "data = pd.read_sql(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "        COUNT(*)\n",
    "    FROM shopnew\n",
    "    WHERE query LIKE '/order.phtml%%';\n",
    "    \"\"\",\n",
    "    con=\"postgresql://postgres:password@localhost:5432/University\"\n",
    ")\n",
    "\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>min</th>\n",
       "      <th>mean</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2.66</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   min  mean  max\n",
       "0    1  2.66   23"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Сколько страниц просмотрел посетитель в среднем, максимум, минимум?\n",
    "data = pd.read_sql(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "        MIN(number_of_views),\n",
    "        ROUND(AVG(number_of_views), 2) as mean,\n",
    "        MAX(number_of_views)\n",
    "    FROM\n",
    "    (SELECT\n",
    "        ip, COUNT(*) as number_of_views\n",
    "    FROM shopnew\n",
    "    WHERE query LIKE '/catalog.phtml%%'\n",
    "    GROUP BY ip) as t;\n",
    "    \"\"\",\n",
    "    con=\"postgresql://postgres:password@localhost:5432/University\"\n",
    ")\n",
    "\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>min</th>\n",
       "      <th>avg</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0 days 00:00:17</td>\n",
       "      <td>0 days 00:26:29.026876</td>\n",
       "      <td>0 days 14:20:11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              min                    avg             max\n",
       "0 0 days 00:00:17 0 days 00:26:29.026876 0 days 14:20:11"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Сколько времени прошло с момента входа на сайт до оформления заказа, в среднем, максимум, минимум?\n",
    "data = pd.read_sql(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "        MIN(time),\n",
    "        AVG(time),\n",
    "        MAX(time)\n",
    "    FROM\n",
    "    (SELECT\n",
    "        ip, MAX(date) - MIN(date) as time\n",
    "    FROM shopnew\n",
    "    WHERE ip in (SELECT ip FROM shopnew WHERE query LIKE '/order.phtml%%')\n",
    "    GROUP BY ip) as t;\n",
    "    \"\"\",\n",
    "    con=\"postgresql://postgres:password@localhost:5432/University\"\n",
    ")\n",
    "\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>mean</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>57.61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    mean\n",
       "0  57.61"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Сколько в среднем заказов оформляется за день?\n",
    "data = pd.read_sql(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "        ROUND(AVG(cnt), 2) as mean\n",
    "    FROM\n",
    "    (SELECT\n",
    "        EXTRACT(day from date) as day, count(*) as cnt\n",
    "    FROM shopnew\n",
    "    WHERE query LIKE '/order.phtml%%'\n",
    "    GROUP BY EXTRACT(day from date)) as t;\n",
    "    \"\"\",\n",
    "    con=\"postgresql://postgres:password@localhost:5432/University\"\n",
    ")\n",
    "\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Априорный алгоритм"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>id</th>\n",
       "      <th>ip</th>\n",
       "      <th>date</th>\n",
       "      <th>method</th>\n",
       "      <th>query</th>\n",
       "      <th>doc_size</th>\n",
       "      <th>user_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "      <td>156.197.248.14</td>\n",
       "      <td>2015-12-01 00:01:54</td>\n",
       "      <td>GET</td>\n",
       "      <td>/addbasket.phtml?id_book=20</td>\n",
       "      <td>4273</td>\n",
       "      <td>4920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18</td>\n",
       "      <td>156.197.248.14</td>\n",
       "      <td>2015-12-01 00:02:44</td>\n",
       "      <td>GET</td>\n",
       "      <td>/addbasket.phtml?id_book=78</td>\n",
       "      <td>3002</td>\n",
       "      <td>4920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22</td>\n",
       "      <td>81.205.211.49</td>\n",
       "      <td>2015-12-01 00:03:13</td>\n",
       "      <td>GET</td>\n",
       "      <td>/addbasket.phtml?id_book=25</td>\n",
       "      <td>2877</td>\n",
       "      <td>4925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>27</td>\n",
       "      <td>110.49.61.147</td>\n",
       "      <td>2015-12-01 00:03:36</td>\n",
       "      <td>GET</td>\n",
       "      <td>/addbasket.phtml?id_book=25</td>\n",
       "      <td>4284</td>\n",
       "      <td>4926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>37</td>\n",
       "      <td>214.40.50.218</td>\n",
       "      <td>2015-12-01 00:06:03</td>\n",
       "      <td>GET</td>\n",
       "      <td>/addbasket.phtml?id_book=8</td>\n",
       "      <td>2752</td>\n",
       "      <td>4930</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id              ip                date method                        query  \\\n",
       "0   9  156.197.248.14 2015-12-01 00:01:54    GET  /addbasket.phtml?id_book=20   \n",
       "1  18  156.197.248.14 2015-12-01 00:02:44    GET  /addbasket.phtml?id_book=78   \n",
       "2  22   81.205.211.49 2015-12-01 00:03:13    GET  /addbasket.phtml?id_book=25   \n",
       "3  27   110.49.61.147 2015-12-01 00:03:36    GET  /addbasket.phtml?id_book=25   \n",
       "4  37   214.40.50.218 2015-12-01 00:06:03    GET   /addbasket.phtml?id_book=8   \n",
       "\n",
       "   doc_size  user_id  \n",
       "0      4273     4920  \n",
       "1      3002     4920  \n",
       "2      2877     4925  \n",
       "3      4284     4926  \n",
       "4      2752     4930  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_sql(\n",
    "    \"\"\"\n",
    "    SELECT * FROM shopnew WHERE query LIKE '/addbasket.phtml%%';\n",
    "    \"\"\",\n",
    "    con=\"postgresql://postgres:password@localhost:5432/University\"\n",
    ")\n",
    "\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "from mlxtend.frequent_patterns import apriori, association_rules\n",
    "pd.set_option('display.max_columns', 500)\n",
    "\n",
    "\n",
    "df = data.copy()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.groupby(['user_id', 'query'])['query'].count().unstack().reset_index().fillna(0).set_index('user_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "def hot_encode(x):\n",
    "    if(x<= 0):\n",
    "        return False\n",
    "    if(x>= 1):\n",
    "        return True\n",
    "  \n",
    "# Encoding the datasets\n",
    "encoded = df.applymap(hot_encode)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>antecedents</th>\n",
       "      <th>consequents</th>\n",
       "      <th>antecedent support</th>\n",
       "      <th>consequent support</th>\n",
       "      <th>support</th>\n",
       "      <th>confidence</th>\n",
       "      <th>lift</th>\n",
       "      <th>leverage</th>\n",
       "      <th>conviction</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>(/addbasket.phtml?id_book=60)</td>\n",
       "      <td>(/addbasket.phtml?id_book=31)</td>\n",
       "      <td>0.020986</td>\n",
       "      <td>0.086479</td>\n",
       "      <td>0.014648</td>\n",
       "      <td>0.697987</td>\n",
       "      <td>8.071180</td>\n",
       "      <td>0.012833</td>\n",
       "      <td>3.024770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>(/addbasket.phtml?id_book=96)</td>\n",
       "      <td>(/addbasket.phtml?id_book=90)</td>\n",
       "      <td>0.020352</td>\n",
       "      <td>0.086690</td>\n",
       "      <td>0.014085</td>\n",
       "      <td>0.692042</td>\n",
       "      <td>7.982932</td>\n",
       "      <td>0.012320</td>\n",
       "      <td>2.965692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>(/addbasket.phtml?id_book=59)</td>\n",
       "      <td>(/addbasket.phtml?id_book=25)</td>\n",
       "      <td>0.021338</td>\n",
       "      <td>0.086549</td>\n",
       "      <td>0.014577</td>\n",
       "      <td>0.683168</td>\n",
       "      <td>7.893401</td>\n",
       "      <td>0.012731</td>\n",
       "      <td>2.883079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>(/addbasket.phtml?id_book=93)</td>\n",
       "      <td>(/addbasket.phtml?id_book=8)</td>\n",
       "      <td>0.019437</td>\n",
       "      <td>0.091338</td>\n",
       "      <td>0.013239</td>\n",
       "      <td>0.681159</td>\n",
       "      <td>7.457567</td>\n",
       "      <td>0.011464</td>\n",
       "      <td>2.849894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>(/addbasket.phtml?id_book=78)</td>\n",
       "      <td>(/addbasket.phtml?id_book=20)</td>\n",
       "      <td>0.020986</td>\n",
       "      <td>0.084437</td>\n",
       "      <td>0.014225</td>\n",
       "      <td>0.677852</td>\n",
       "      <td>8.027943</td>\n",
       "      <td>0.012453</td>\n",
       "      <td>2.842061</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     antecedents                    consequents  \\\n",
       "2  (/addbasket.phtml?id_book=60)  (/addbasket.phtml?id_book=31)   \n",
       "4  (/addbasket.phtml?id_book=96)  (/addbasket.phtml?id_book=90)   \n",
       "1  (/addbasket.phtml?id_book=59)  (/addbasket.phtml?id_book=25)   \n",
       "3  (/addbasket.phtml?id_book=93)   (/addbasket.phtml?id_book=8)   \n",
       "0  (/addbasket.phtml?id_book=78)  (/addbasket.phtml?id_book=20)   \n",
       "\n",
       "   antecedent support  consequent support   support  confidence      lift  \\\n",
       "2            0.020986            0.086479  0.014648    0.697987  8.071180   \n",
       "4            0.020352            0.086690  0.014085    0.692042  7.982932   \n",
       "1            0.021338            0.086549  0.014577    0.683168  7.893401   \n",
       "3            0.019437            0.091338  0.013239    0.681159  7.457567   \n",
       "0            0.020986            0.084437  0.014225    0.677852  8.027943   \n",
       "\n",
       "   leverage  conviction  \n",
       "2  0.012833    3.024770  \n",
       "4  0.012320    2.965692  \n",
       "1  0.012731    2.883079  \n",
       "3  0.011464    2.849894  \n",
       "0  0.012453    2.842061  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frq_items = apriori(encoded, min_support = 0.01, use_colnames = True)\n",
    "  \n",
    "# Collecting the inferred rules in a dataframe\n",
    "rules = association_rules(frq_items, metric =\"confidence\", min_threshold = 0.6)\n",
    "rules = rules.sort_values(['confidence', 'support'], ascending =[False, False])\n",
    "rules"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Результаты показывают, что в наших данных действительно имеются взаимосвязные товары. Например, из первой строчки полученного резульата видим, что если клиент покупает книгу с индексом id_book = 60, то он купит и книгу id_book = 31 с уровнем поддержки 0.015 и достоверностью 0.7."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Выводы\n",
    "\n",
    "В данной работе, мы постарались провести полный путь обрабки анализа Web-логов. В работе вы можете встретить следующее:\n",
    "- Работа с Jupyter Notebook;\n",
    "- Работа с Терминалом;\n",
    "- Создание виртуального окружения для Python и requirements.txt для установки библиотек;\n",
    "- Работа с pgAdmin 4 и СУБД Postgres - создание и заполнение таблиц для БД;\n",
    "- Написание SQL-запросов;\n",
    "- Парсинг средствами Python;\n",
    "- Работа с библиотекой Pandas;\n",
    "- Работа с библиотекой mlxtend для выявления априорных отношений в данных;\n",
    "- Работа с git;"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.7"
  },
  "vscode": {
   "interpreter": {
    "hash": "cd96f0bbaf7dac44834dea80292a8165cda06118c8f30cdb5c7e5544dee369c5"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}