01_basic_stats_my_solutions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# A/B Testing Course"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lesson 1. Basic Statistics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Homework"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Import Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "executionInfo": {
     "elapsed": 580,
     "status": "ok",
     "timestamp": 1674542192205,
     "user": {
      "displayName": "Nikolay Nazarov",
      "userId": "18018785782997408667"
     },
     "user_tz": -420
    },
    "id": "agQEC38nE-72",
    "tags": []
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Import Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "executionInfo": {
     "elapsed": 6,
     "status": "ok",
     "timestamp": 1674542192206,
     "user": {
      "displayName": "Nikolay Nazarov",
      "userId": "18018785782997408667"
     },
     "user_tz": -420
    },
    "id": "xxFeCfFuFKng",
    "tags": []
   },
   "outputs": [],
   "source": [
    "URL_BASE = 'https://raw.githubusercontent.com/ab-courses/simulator-ab-datasets/main/2022-04-01/'\n",
    "\n",
    "def read_database(file_name):\n",
    "    return pd.read_csv(os.path.join(URL_BASE, file_name))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "executionInfo": {
     "elapsed": 6399,
     "status": "ok",
     "timestamp": 1674542198600,
     "user": {
      "displayName": "Nikolay Nazarov",
      "userId": "18018785782997408667"
     },
     "user_tz": -420
    },
    "id": "nQGxlqByE-73",
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_sales = read_database('2022-04-01T12_df_sales.csv')\n",
    "df_web_logs = read_database('2022-04-01T12_df_web_logs.csv')\n",
    "\n",
    "df_sales['date'] = pd.to_datetime(df_sales['date'])\n",
    "df_web_logs['date'] = pd.to_datetime(df_web_logs['date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_sales_details = pd.read_csv('df_sales_detail.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Data Description"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "nNRTGF6aBHrZ"
   },
   "source": [
    "df_sales - information about purchases, one row represents one order:\n",
    "- sale_id - purchase identifier;\n",
    "- date - date of purchase;\n",
    "- count_pizza - number of pizzas in the order;\n",
    "- count_drink - number of drinks in the order;\n",
    "- price - order price;\n",
    "- user_id - user identifier.   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "df_sales_details - information about menu:\n",
    "- sale_id - purchase identifier;\n",
    "- good - position on menu;\n",
    "- price - position price;\n",
    "- date - purchase date;\n",
    "- user_id - user identifier.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "df_web_logs - a table with logs of website visits:\n",
    "- user_id - user identifier;\n",
    "- page - which page of the website was opened. Page types:\n",
    "    - m - menu page;\n",
    "    - b - cart and order confirmation page;\n",
    "    - p - payment page;\n",
    "- date - date of the page opening;\n",
    "- load_time - page load time in milliseconds."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Checking Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "executionInfo": {
     "elapsed": 53,
     "status": "ok",
     "timestamp": 1674542198602,
     "user": {
      "displayName": "Nikolay Nazarov",
      "userId": "18018785782997408667"
     },
     "user_tz": -420
    },
    "id": "0dZkzMMTE-73",
    "outputId": "44f7566a-b32d-4479-b963-7c5977351149",
    "tags": []
   },
   "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>sale_id</th>\n",
       "      <th>date</th>\n",
       "      <th>count_pizza</th>\n",
       "      <th>count_drink</th>\n",
       "      <th>price</th>\n",
       "      <th>user_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000001</td>\n",
       "      <td>2022-02-04 10:00:24</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>720</td>\n",
       "      <td>1c1543</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2022-02-04 10:02:28</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>930</td>\n",
       "      <td>a9a6e8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1000003</td>\n",
       "      <td>2022-02-04 10:02:35</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1980</td>\n",
       "      <td>23420a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1000004</td>\n",
       "      <td>2022-02-04 10:03:06</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>750</td>\n",
       "      <td>3e8ed5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1000005</td>\n",
       "      <td>2022-02-04 10:03:23</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>870</td>\n",
       "      <td>cbc468</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sale_id                date  count_pizza  count_drink  price user_id\n",
       "0  1000001 2022-02-04 10:00:24            1            0    720  1c1543\n",
       "1  1000002 2022-02-04 10:02:28            1            1    930  a9a6e8\n",
       "2  1000003 2022-02-04 10:02:35            3            1   1980  23420a\n",
       "3  1000004 2022-02-04 10:03:06            1            1    750  3e8ed5\n",
       "4  1000005 2022-02-04 10:03:23            1            1    870  cbc468"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sales.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 203847 entries, 0 to 203846\n",
      "Data columns (total 6 columns):\n",
      " #   Column       Non-Null Count   Dtype         \n",
      "---  ------       --------------   -----         \n",
      " 0   sale_id      203847 non-null  int64         \n",
      " 1   date         203847 non-null  datetime64[ns]\n",
      " 2   count_pizza  203847 non-null  int64         \n",
      " 3   count_drink  203847 non-null  int64         \n",
      " 4   price        203847 non-null  int64         \n",
      " 5   user_id      203847 non-null  object        \n",
      "dtypes: datetime64[ns](1), int64(4), object(1)\n",
      "memory usage: 9.3+ MB\n"
     ]
    }
   ],
   "source": [
    "df_sales.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "executionInfo": {
     "elapsed": 50,
     "status": "ok",
     "timestamp": 1674542198603,
     "user": {
      "displayName": "Nikolay Nazarov",
      "userId": "18018785782997408667"
     },
     "user_tz": -420
    },
    "id": "wKEaqYGMYSuC",
    "outputId": "4b5cf4bd-7561-40c7-94ae-55ad9ba9a8d8",
    "tags": []
   },
   "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>page</th>\n",
       "      <th>date</th>\n",
       "      <th>load_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>f25239</td>\n",
       "      <td>m</td>\n",
       "      <td>2022-02-03 23:45:37</td>\n",
       "      <td>80.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>06d6df</td>\n",
       "      <td>m</td>\n",
       "      <td>2022-02-03 23:49:56</td>\n",
       "      <td>70.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>06d6df</td>\n",
       "      <td>m</td>\n",
       "      <td>2022-02-03 23:51:16</td>\n",
       "      <td>89.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>f25239</td>\n",
       "      <td>m</td>\n",
       "      <td>2022-02-03 23:51:43</td>\n",
       "      <td>74.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>697870</td>\n",
       "      <td>m</td>\n",
       "      <td>2022-02-03 23:53:12</td>\n",
       "      <td>66.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  user_id page                date  load_time\n",
       "0  f25239    m 2022-02-03 23:45:37       80.8\n",
       "1  06d6df    m 2022-02-03 23:49:56       70.5\n",
       "2  06d6df    m 2022-02-03 23:51:16       89.7\n",
       "3  f25239    m 2022-02-03 23:51:43       74.4\n",
       "4  697870    m 2022-02-03 23:53:12       66.8"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_web_logs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1964507 entries, 0 to 1964506\n",
      "Data columns (total 4 columns):\n",
      " #   Column     Dtype         \n",
      "---  ------     -----         \n",
      " 0   user_id    object        \n",
      " 1   page       object        \n",
      " 2   date       datetime64[ns]\n",
      " 3   load_time  float64       \n",
      "dtypes: datetime64[ns](1), float64(1), object(2)\n",
      "memory usage: 60.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df_web_logs.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "tags": []
   },
   "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>sale_id</th>\n",
       "      <th>good</th>\n",
       "      <th>price</th>\n",
       "      <th>date</th>\n",
       "      <th>user_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000001</td>\n",
       "      <td>mexican pizza</td>\n",
       "      <td>720</td>\n",
       "      <td>2022-02-04 10:00:24</td>\n",
       "      <td>1c1543</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000002</td>\n",
       "      <td>chefs pizza</td>\n",
       "      <td>840</td>\n",
       "      <td>2022-02-04 10:02:28</td>\n",
       "      <td>a9a6e8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1000002</td>\n",
       "      <td>orange juice</td>\n",
       "      <td>90</td>\n",
       "      <td>2022-02-04 10:02:28</td>\n",
       "      <td>a9a6e8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1000003</td>\n",
       "      <td>cheese pizza</td>\n",
       "      <td>600</td>\n",
       "      <td>2022-02-04 10:02:35</td>\n",
       "      <td>23420a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1000003</td>\n",
       "      <td>italian pizza</td>\n",
       "      <td>720</td>\n",
       "      <td>2022-02-04 10:02:35</td>\n",
       "      <td>23420a</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sale_id           good  price                 date user_id\n",
       "0  1000001  mexican pizza    720  2022-02-04 10:00:24  1c1543\n",
       "1  1000002    chefs pizza    840  2022-02-04 10:02:28  a9a6e8\n",
       "2  1000002   orange juice     90  2022-02-04 10:02:28  a9a6e8\n",
       "3  1000003   cheese pizza    600  2022-02-04 10:02:35  23420a\n",
       "4  1000003  italian pizza    720  2022-02-04 10:02:35  23420a"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sales_details.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 416481 entries, 0 to 416480\n",
      "Data columns (total 5 columns):\n",
      " #   Column   Non-Null Count   Dtype \n",
      "---  ------   --------------   ----- \n",
      " 0   sale_id  416481 non-null  int64 \n",
      " 1   good     416481 non-null  object\n",
      " 2   price    416481 non-null  int64 \n",
      " 3   date     416481 non-null  object\n",
      " 4   user_id  416481 non-null  object\n",
      "dtypes: int64(2), object(3)\n",
      "memory usage: 15.9+ MB\n"
     ]
    }
   ],
   "source": [
    "df_sales_details.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Task 1. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Determine the top 3 products by total revenue."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "tags": []
   },
   "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>good</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>chefs pizza</td>\n",
       "      <td>24558240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>double pepperoni pizza</td>\n",
       "      <td>22558380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>chicken bbq pizza</td>\n",
       "      <td>17622540</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     good     price\n",
       "0             chefs pizza  24558240\n",
       "1  double pepperoni pizza  22558380\n",
       "2       chicken bbq pizza  17622540"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sales_details.groupby('good', as_index=False) \\\n",
    "    .agg({'price': 'sum'}) \\\n",
    "    .sort_values('price', ascending=False) \\\n",
    "    .reset_index(drop=True) \\\n",
    "    .head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Task 2. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Estimate the average time it takes from a user's site visit to making a purchase. We will consider a site visit to be a purchase if it occurred no earlier than two hours before the purchase was made. In other words, for each purchase, we need to calculate the time elapsed from the purchase to the first site visit by the same user within two hours before the purchase. Then, calculate the average of these values.  \n",
    "\n",
    "Convert the result to minutes and round to the nearest integer value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10000 users have been processed.\n",
      "20000 users have been processed.\n",
      "30000 users have been processed.\n",
      "40000 users have been processed.\n",
      "50000 users have been processed.\n",
      "60000 users have been processed.\n",
      "70000 users have been processed.\n",
      "80000 users have been processed.\n",
      "90000 users have been processed.\n",
      "A number of purchases processed: 203,847\n",
      "An average time to make one purchase is: 17 minutes\n"
     ]
    }
   ],
   "source": [
    "purch_delta_s = []\n",
    "users_lst = list(set(df_sales.user_id.to_list()))\n",
    "\n",
    "cnt = 0\n",
    "for user in users_lst[0:]:\n",
    "    dates_lst = df_sales[df_sales['user_id'] == user].date.to_list()\n",
    "    df_user = df_web_logs[df_web_logs['user_id'] == user]\n",
    "    for d in dates_lst:\n",
    "        finish = d\n",
    "        start = d - pd.Timedelta(hours=2)\n",
    "        df_temp = df_user[(df_user['date'] <= finish) & (df_user['date'] >= start)]\n",
    "        df_temp = df_temp.sort_values('date')\n",
    "        ans = pd.to_datetime(finish) - pd.to_datetime(df_temp.head(1).date.values)\n",
    "        purch_delta_s.append(ans.seconds[0])\n",
    "    cnt += 1\n",
    "    \n",
    "    if cnt % 10000 == 0:\n",
    "        print(f'{cnt} users have been processed.')\n",
    "    \n",
    "    \n",
    "print(f'A number of purchases processed: {len(purch_delta_s):,.0f}')\n",
    "print(f'An average time to make one purchase is: {round(np.mean(purch_delta_s)/60, 0):.0f} minutes')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Task 3. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many users come back to us month after month. What proportion of users who made a purchase in February also made a purchase in March?\n",
    "\n",
    "Round the answer to 2 decimal places."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Proportion of users who made a purchase both in February and March: 0.66\n"
     ]
    }
   ],
   "source": [
    "df_sales['month'] = df_sales.date.dt.month\n",
    "feb_users = set(df_sales[df_sales['month'] == 2].user_id.to_list())\n",
    "feb_march_users = df_sales.query('user_id in @feb_users')\n",
    "march_users = set(feb_march_users[feb_march_users['month'] == 3].user_id.to_list())\n",
    "\n",
    "print(f'Proportion of users who made a purchase both in February and March: {len(march_users) / len(feb_users):.2f}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Task 4. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Code the method `get_data_subset` of the `DataService` class. The method description is provided in the solution template below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Simple test is passed.\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "from datetime import datetime\n",
    "\n",
    "\n",
    "class DataService:\n",
    "\n",
    "    def __init__(self, table_name_2_table):\n",
    "        \"\"\"A class that provides access to raw data.\n",
    "        \n",
    "        :param table_name_2_table (dict[str, pd.DataFrame]): A dictionary of data tables.\n",
    "            Example, {\n",
    "                'sales': pd.DateFrame({'sale_id': ['123', ...], ...}),\n",
    "                ...\n",
    "            }. \n",
    "        \"\"\"\n",
    "        self.table_name_2_table = table_name_2_table\n",
    "\n",
    "    def get_data_subset(self, table_name, begin_date, end_date, user_ids=None, columns=None):\n",
    "        \"\"\"Returns a subset of data.\n",
    "\n",
    "        :param table_name (str): The name of the data table.\n",
    "        :param begin_date (datetime.datetime): The start date of the data interval.\n",
    "            Example, df[df['date'] >= begin_date].\n",
    "            If None, no filtering is required.\n",
    "        :param end_date (None, datetime.datetime): The end date of the data interval.\n",
    "            Example, df[df['date'] < end_date].\n",
    "            If None, no filtering is required.\n",
    "        :param user_ids (None, list[str]): A list of user_ids for which data needs to be provided.\n",
    "            Example, df[df['user_id'].isin(user_ids)].\n",
    "            If None, no user_id filtering is required.\n",
    "        :param columns (None, list[str]): A list of column names for which data needs to be provided.\n",
    "            Example, df[columns].\n",
    "            If None, no filtering by columns is required.\n",
    "\n",
    "        :return df (pd.DataFrame): A DataFrame with a subset of data.\n",
    "        \"\"\"\n",
    "        # YOUR_CODE_HERE\n",
    "        df = self.table_name_2_table[table_name]\n",
    "        \n",
    "        if end_date == None and begin_date == None:\n",
    "            df_date_filt = df.copy()\n",
    "        elif begin_date == None:\n",
    "            df_date_filt = df[df['date'] < end_date]\n",
    "        elif end_date == None:\n",
    "            df_date_filt = df[df['date'] >= begin_date]\n",
    "        else:\n",
    "            df_date_filt = df[(df['date'] >= begin_date) & (df['date'] < end_date)]\n",
    "        \n",
    "        if user_ids == None:\n",
    "            df_filt_users = df_date_filt\n",
    "        else:\n",
    "            df_filt_users = df_date_filt.query('user_id in @user_ids')\n",
    "        \n",
    "        if columns == None:\n",
    "            df_filt_columns = df_filt_users\n",
    "        else:\n",
    "            df_filt_columns = df_filt_users[columns]\n",
    "        \n",
    "        return df_filt_columns.copy()\n",
    "\n",
    "\n",
    "def _chech_df(df, df_ideal, sort_by):\n",
    "    assert isinstance(df, pd.DataFrame), 'The function did not return a pd.DataFrame.'\n",
    "    assert len(df) == len(df_ideal), 'Incorrect number of rows.'\n",
    "    assert len(df.T) == len(df_ideal.T), 'Incorrect number of columns.'\n",
    "    columns = df_ideal.columns\n",
    "    assert df.columns.isin(columns).sum() == len(df.columns), 'Incorrect column names.'\n",
    "    df = df[columns].sort_values(sort_by)\n",
    "    df_ideal = df_ideal.sort_values(sort_by)\n",
    "    assert df_ideal.equals(df), 'The final DataFrame does not match the correct result.'\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    table = pd.DataFrame({\n",
    "        'date': [datetime(2022, 1, 5, 12,), datetime(2022, 1, 7, 12)],\n",
    "        'user_id': ['1', '2'],\n",
    "    })\n",
    "    ideal_df = pd.DataFrame({\n",
    "        'date': [datetime(2022, 1, 5, 12,)],\n",
    "        'user_id': ['1'],\n",
    "    })\n",
    "\n",
    "    data_service = DataService({'table': table})\n",
    "    res_df = data_service.get_data_subset('table', datetime(2022, 1, 1), datetime(2022, 1, 6))\n",
    "    _chech_df(res_df, ideal_df, 'date')\n",
    "    print('Simple test is passed.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Task 5. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Code methods for calculating the \"revenue (web)\", \"revenue (all)\", and \"response time\" metrics. The method descriptions are provided in the solution template below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Simple test is passed.\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "from datetime import datetime\n",
    "\n",
    "\n",
    "class DataService:\n",
    "\n",
    "    def __init__(self, table_name_2_table):\n",
    "        self.table_name_2_table = table_name_2_table\n",
    "\n",
    "    def get_data_subset(self, table_name, begin_date, end_date, user_ids=None, columns=None):\n",
    "        df = self.table_name_2_table[table_name]\n",
    "        if begin_date:\n",
    "            df = df[df['date'] >= begin_date]\n",
    "        if end_date:\n",
    "            df = df[df['date'] < end_date]\n",
    "        if user_ids:\n",
    "            df = df[df['user_id'].isin(user_ids)]\n",
    "        if columns:\n",
    "            df = df[columns]\n",
    "        return df.copy()\n",
    "\n",
    "\n",
    "class MetricsService:\n",
    "\n",
    "    def __init__(self, data_service):\n",
    "        \"\"\"Class for calculating metrics.\n",
    "\n",
    "        :param data_service (DataService): An object of the class that provides access to the data.\n",
    "        \"\"\"\n",
    "        self.data_service = data_service\n",
    "\n",
    "    def _get_data_subset(self, table_name, begin_date, end_date, user_ids=None, columns=None):\n",
    "        \"\"\"Returns a subset of the data table.\"\"\"\n",
    "        return self.data_service.get_data_subset(table_name, begin_date, end_date, user_ids, columns)\n",
    "\n",
    "    def _calculate_response_time(self, begin_date, end_date, user_ids):\n",
    "        \"\"\"Calculates the values of server response time.\n",
    "        \n",
    "        You need to return the values of user_id and load_time from \n",
    "        the 'web-logs' table filtered by date and user_id. \n",
    "        We assume that each request is independent, so there is no need to group by user_id.\n",
    "\n",
    "        :param begin_date, end_date (datetime): Time period for which the values should be calculated.\n",
    "        :param user_id (None, list[str]): User IDs to filter the obtained values by.\n",
    "        \n",
    "        :return (pd.DataFrame): A dataframe with two columns ['user_id', 'metric']\n",
    "        \"\"\"\n",
    "        # YOUR_CODE_HERE\n",
    "        df = self._get_data_subset('web-logs', begin_date, end_date, user_ids)\n",
    "        \n",
    "        df_result = df[['user_id', 'load_time']]\n",
    "        df_result.rename(columns={'load_time': 'metric'}, inplace=True)\n",
    "        \n",
    "        return df_result.copy()\n",
    "        \n",
    "\n",
    "    def _calculate_revenue_web(self, begin_date, end_date, user_ids):\n",
    "        \"\"\"Calculates the revenue per user for the specified period for those who visited \n",
    "        the website during that period.\n",
    "\n",
    "        These data are needed for experiments on the site, \n",
    "        when only those who visited the site are included in the experiment.\n",
    "\n",
    "        The function should return user_id and revenue (sum(price)) values.\n",
    "        Price data is in the 'sales' table. Data about website visits is in the 'web-logs' table.\n",
    "        If a user visited the site but didn't make any purchases, their total purchase cost should be zero.\n",
    "        There should be exactly one value per user_id.\n",
    "        \n",
    "        :param begin_date, end_date (datetime): The time period for which to calculate the values.\n",
    "            Also, during this period, it is necessary to select users who visited the website.\n",
    "        :param user_id (None, list[str]): User IDs to filter the obtained values.\n",
    "        \n",
    "        :return (pd.DataFrame): A dataframe with two columns ['user_id', 'metric']\n",
    "        \"\"\"\n",
    "        # YOUR_CODE_HERE\n",
    "        df_sales = self._get_data_subset('sales', begin_date, end_date, user_ids)\n",
    "        df_web = self._get_data_subset('web-logs', begin_date, end_date, user_ids)\n",
    "        \n",
    "        df_m = pd.merge(df_sales, df_web, on=['user_id', 'date'], how='inner')\n",
    "        \n",
    "        df_result = df_m.groupby(['user_id'], as_index=False)['price'].sum()\n",
    "        df_result.rename(columns={'price': 'metric'}, inplace=True)\n",
    "        \n",
    "        return df_result.copy()\n",
    "        \n",
    "\n",
    "    def _calculate_revenue_all(self, begin_date, end_date, user_ids):\n",
    "        \"\"\"Calculates the revenue per user for the specified period for users who visited \n",
    "        the website before the end_date.\n",
    "\n",
    "        These data are needed, for example, for experiments with email marketing, \n",
    "        when those who have ever left their data with us participate in the experiment.\n",
    "\n",
    "        The function should return the user_id and the revenue (sum(price)).\n",
    "        Data on prices is in the sales table. Data on website visits is in the web-logs table.\n",
    "        If a user did not make any purchases during the specified period, their total purchase cost is zero.\n",
    "        There should be exactly one value for each user_id.\n",
    "\n",
    "        :param begin_date, end_date (datetime): Period of time for which the values should be calculated.\n",
    "            Users who at least once visited the site before the end_date should be selected.\n",
    "        :param user_id (None, list[str]): User IDs to filter the obtained values.\n",
    "        \n",
    "        :return (pd.DataFrame): A dataframe with two columns ['user_id', 'metric']\n",
    "        \"\"\"\n",
    "        # YOUR_CODE_HERE\n",
    "        begin_date = None\n",
    "        df_sales = self._get_data_subset('sales', begin_date, end_date, user_ids)\n",
    "        df_web = self._get_data_subset('web-logs', begin_date, end_date, user_ids)\n",
    "        \n",
    "        df_m = pd.merge(df_sales, df_web, on=['user_id', 'date'], how='outer')\n",
    "        \n",
    "        df_result = df_m.groupby(['user_id'], as_index=False)['price'].sum()\n",
    "        df_result.rename(columns={'price': 'metric'}, inplace=True)\n",
    "        \n",
    "        return df_result.copy()\n",
    "\n",
    "    \n",
    "    def calculate_metric(self, metric_name, begin_date, end_date, user_ids=None):\n",
    "        \"\"\"Calculates values for computing metrics.\n",
    "\n",
    "        :param metric_name (str): the name of the metric\n",
    "        :param begin_date (datetime): the start date of the period (inclusive)\n",
    "        :param end_date (datetime): the end date of the period (exclusive)\n",
    "        :param user_ids (list[str], None): a list of user ids.\n",
    "            If None, calculates values for all users.\n",
    "        :return df: columns=['user_id', 'metric']\n",
    "        \"\"\"\n",
    "        if metric_name == 'response time':\n",
    "            return self._calculate_response_time(begin_date, end_date, user_ids)\n",
    "        elif metric_name == 'revenue (web)':\n",
    "            return self._calculate_revenue_web(begin_date, end_date, user_ids)\n",
    "        elif metric_name == 'revenue (all)':\n",
    "            return self._calculate_revenue_all(begin_date, end_date, user_ids)\n",
    "        else:\n",
    "            raise ValueError('Wrong metric name')\n",
    "\n",
    "\n",
    "def _chech_df(df, df_ideal, sort_by, reindex=False, set_dtypes=False):\n",
    "    assert isinstance(df, pd.DataFrame), 'The returned value is not a pd.DataFrame.'\n",
    "    assert len(df) == len(df_ideal), 'A wrong numner of rows.'\n",
    "    assert len(df.T) == len(df_ideal.T), 'A wrong number of columns.'\n",
    "    columns = df_ideal.columns\n",
    "    assert df.columns.isin(columns).sum() == len(df.columns), 'A wrong column name.'\n",
    "    df = df[columns].sort_values(sort_by)\n",
    "    df_ideal = df_ideal.sort_values(sort_by)\n",
    "    if reindex:\n",
    "        df_ideal.index = range(len(df_ideal))\n",
    "        df.index = range(len(df))\n",
    "    if set_dtypes:\n",
    "        for column, dtype in df_ideal.dtypes.to_dict().items():\n",
    "            df[column] = df[column].astype(dtype)\n",
    "    assert df_ideal.equals(df), 'The final dataframe does not match the correct result.'\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    df_sales = pd.DataFrame({\n",
    "        'sale_id': [1, 2, 3],\n",
    "        'date': [datetime(2022, 3, day, 11) for day in range(11, 14)],\n",
    "        'price': [1100, 900, 1500],\n",
    "        'user_id': ['1', '2', '1'],\n",
    "    })\n",
    "    df_web_logs = pd.DataFrame({\n",
    "        'date': [datetime(2022, 3, day, 11) for day in range(10, 14)],\n",
    "        'load_time': [80.8, 90.1, 15.8, 19.7],\n",
    "        'user_id': ['3', '1', '2', '1'],\n",
    "    })\n",
    "    begin_date = datetime(2022, 3, 11, 9)\n",
    "    end_date = datetime(2022, 4, 11, 9)\n",
    "\n",
    "    ideal_response_time = pd.DataFrame({'user_id': ['1', '2', '1'], 'metric': [90.1, 15.8, 19.7],})\n",
    "    ideal_revenue_web = pd.DataFrame({'user_id': ['1', '2'], 'metric': [2600., 900.],})\n",
    "    ideal_revenue_all = pd.DataFrame({'user_id': ['1', '2', '3'], 'metric': [2600., 900., 0.],})\n",
    "\n",
    "    data_service = DataService({'sales': df_sales, 'web-logs': df_web_logs})\n",
    "    metrics_service = MetricsService(data_service)\n",
    "\n",
    "    df_response_time = metrics_service.calculate_metric('response time', begin_date, end_date)\n",
    "    df_revenue_web = metrics_service.calculate_metric('revenue (web)', begin_date, end_date)\n",
    "    df_revenue_all = metrics_service.calculate_metric('revenue (all)', begin_date, end_date)\n",
    "\n",
    "    _chech_df(df_response_time, ideal_response_time, ['user_id', 'metric'], True, True)\n",
    "    _chech_df(df_revenue_web, ideal_revenue_web, ['user_id', 'metric'], True, True)\n",
    "    _chech_df(df_revenue_all, ideal_revenue_all, ['user_id', 'metric'], True, True)\n",
    "    print('Simple test is passed.')"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [
    "KRzEGsPUE-7k"
   ],
   "provenance": []
  },
  "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.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}