Тестовое задание.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a4d83cb2",
   "metadata": {},
   "source": [
    "Уже пятый раз заполняю эту форму.\n",
    "Было бы намного удобней, если бы форма сохранялась для дальнейшего редактирования. Иначе внезапное (или плановое) закрытие браузера приводит к тому, что приходится все заполнять с начала.\n",
    "\n",
    "А отвечая на вопрос чем заинтересовала возможность стать ревьюером, если кратко: в моем понимании, это хорошая возможность расширять свой кругозор, тренировать хард и софт скилы."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "5a411997",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8de83e0b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"movie_metadata.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6b585ecf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5043 entries, 0 to 5042\n",
      "Data columns (total 28 columns):\n",
      " #   Column                     Non-Null Count  Dtype  \n",
      "---  ------                     --------------  -----  \n",
      " 0   color                      5024 non-null   object \n",
      " 1   director_name              4939 non-null   object \n",
      " 2   num_critic_for_reviews     4993 non-null   float64\n",
      " 3   duration                   5028 non-null   float64\n",
      " 4   director_facebook_likes    4939 non-null   float64\n",
      " 5   actor_3_facebook_likes     5020 non-null   float64\n",
      " 6   actor_2_name               5030 non-null   object \n",
      " 7   actor_1_facebook_likes     5036 non-null   float64\n",
      " 8   gross                      4159 non-null   float64\n",
      " 9   genres                     5043 non-null   object \n",
      " 10  actor_1_name               5036 non-null   object \n",
      " 11  movie_title                5043 non-null   object \n",
      " 12  num_voted_users            5043 non-null   int64  \n",
      " 13  cast_total_facebook_likes  5043 non-null   int64  \n",
      " 14  actor_3_name               5020 non-null   object \n",
      " 15  facenumber_in_poster       5030 non-null   float64\n",
      " 16  plot_keywords              4890 non-null   object \n",
      " 17  movie_imdb_link            5043 non-null   object \n",
      " 18  num_user_for_reviews       5022 non-null   float64\n",
      " 19  language                   5031 non-null   object \n",
      " 20  country                    5038 non-null   object \n",
      " 21  content_rating             4740 non-null   object \n",
      " 22  budget                     5043 non-null   object \n",
      " 23  title_year                 4935 non-null   float64\n",
      " 24  actor_2_facebook_likes     5030 non-null   float64\n",
      " 25  imdb_score                 5043 non-null   float64\n",
      " 26  aspect_ratio               4714 non-null   float64\n",
      " 27  movie_facebook_likes       5043 non-null   int64  \n",
      "dtypes: float64(12), int64(3), object(13)\n",
      "memory usage: 1.1+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "1a90bd48",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5028"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.duration.value_counts().sort_index().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ade645a",
   "metadata": {},
   "source": [
    "Сколько пропущенных значений содержится в колонке duration?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "567fff19",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "15"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape[0]-df.duration.value_counts().sort_index().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eab58a82",
   "metadata": {},
   "source": [
    "Замените пропущенные значения в колонке duration медианным значением этой колонки.\n",
    "\n",
    "Какое среднее арифметическое по длительности фильма? Ответ дайте числом с плавающей запятой с округлением до 2 знаков после запятой включительно.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "07fd693b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "107.19"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round((df\n",
    " .fillna(df.duration.median())\n",
    " .duration.describe()\n",
    " .get(\"mean\")\n",
    "),2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7dfa26b8",
   "metadata": {},
   "source": [
    "Cоздайте колонку movie_duration_category, которая содержала бы 3 категории в зависимости от длительности фильма:\n",
    "\n",
    "Категория \"1. < 90\", если фильм длится менее 90 минут;\n",
    "Категория \"2. 90-120\", если фильм длится от 90 минут до 2 часов (включительно);\n",
    "Категория \"3. > 120\", если фильм длится больше 2 часов.\n",
    "Постройте сводную таблицу для фильмов с годом выпуска после 2000 (включительно), которая отражала бы количество фильмов:\n",
    "\n",
    "В строках таблицы - год;\n",
    "В столбцах таблицы - категория длительности фильма (\"< 90\", \"90-120\", \"> 120\");\n",
    "Год фильма должен быть представлен в формате YYYY\n",
    "Сколько фильмов с длительностью от 90 минут до 2 часов было выпущено в 2008 году?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7a24310b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "160"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1 = df.assign(movie_duration_category=\"1. < 90\")\n",
    "df_1.loc[df_1.duration >= 90, \"movie_duration_category\"] = \"2. 90-120\"\n",
    "df_1.loc[df_1.duration > 120, \"movie_duration_category\"] = \"3. > 120\"\n",
    "(df_1\n",
    " .loc[df_1.title_year==2008]\n",
    " .movie_duration_category.value_counts()\n",
    " ).loc[\"2. 90-120\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b8ff25e",
   "metadata": {},
   "source": [
    "Колонка plot_keywords содержит ключевые слова, характеризующие сюжет фильма. На основе данных этой колонки создайте колонку movie_plot_category, которая содержала бы 4 категории в зависимости от ключевых слов в колонке:\n",
    "\n",
    "- Категория \"love_and_death\", если ключевые слова содержат и ключевое слово \"love\" и ключевое слово \"death\";\n",
    "- Категория \"love\", если ключевые слова содержат ключевое слово \"love\";\n",
    "- Категория \"death\", если ключевые слова содержат ключевое слово \"death\";\n",
    "- Категория \"other\", если ключевые слова не соответствуют перечисленным выше условиям.\n",
    "Обратите внимание на разделитель ключевых слов. Подумайте о том как правильно определять наличие ключевого слова в списке ключевых слов.\n",
    "\n",
    "В колонке imdb_score указан пользовательский рейтинг фильма. Постройте таблицу, которая отражала бы средний рейтинг фильмов в зависимости от принадлежности к категории movie_plot_category.\n",
    "\n",
    "Какой средний рейтинг у фильмов категории \"love\"? Ответ дайте числом с плавающей запятой с округлением до 2 знаков после запятой включительно."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a1652031",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                    imdb_score               \n",
      "                          mean count      sum\n",
      "movie_plot_category                          \n",
      "death                 6.519886   176   1147.5\n",
      "love                  6.588987   227   1495.7\n",
      "love_and_death        6.672727    11     73.4\n",
      "other                 6.431432  4629  29771.1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "6.59"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def love_criteria(df):\n",
    "    criteria = df[1]\n",
    "    for clmn in df.columns:\n",
    "        criteria = (criteria) | df[clmn]\n",
    "    return criteria\n",
    "\n",
    "new_df = df.assign(movie_plot_category = \"other\")\n",
    "new_df.loc[\n",
    "     (lambda x: x\n",
    "      .plot_keywords.str.lower().str.split(r\"[|\\s]\", expand=True)\n",
    "      .isin([\"love\"])\n",
    "      .pipe(love_criteria)\n",
    "     ),\n",
    "    \"movie_plot_category\" \n",
    " ] = \"love\"\n",
    "new_df.loc[\n",
    "     (lambda x: x\n",
    "      .plot_keywords.str.lower().str.split(r\"[|\\s]\", expand=True)\n",
    "      .isin([\"death\"])\n",
    "      .pipe(love_criteria)\n",
    "     ),\n",
    "    \"movie_plot_category\" \n",
    " ] = \"death\"\n",
    "condition = (new_df\n",
    "      .plot_keywords.str.lower().str.split(r\"[|\\s]\", expand=True)\n",
    "      .isin([\"death\"])\n",
    "      .pipe(love_criteria)\n",
    "     ) & (new_df\n",
    "      .plot_keywords.str.lower().str.split(r\"[|\\s]\", expand=True)\n",
    "      .isin([\"love\"])\n",
    "      .pipe(love_criteria)\n",
    "     )\n",
    "new_df.movie_plot_category.where(~(condition), other=\"love_and_death\", inplace=True)\n",
    "print(new_df.groupby(\"movie_plot_category\").agg({\"imdb_score\": [\"mean\", \"count\", \"sum\"]}))\n",
    "\n",
    "round(new_df.groupby(\"movie_plot_category\").agg({\"imdb_score\": \"mean\"}).loc[\"love\", \"imdb_score\"], 2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1854e4a8",
   "metadata": {},
   "source": [
    "Колонка budget - бюджет фильма. Какой медианный бюджет всех представленных фильмов? Ответ дайте целым числом."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4edfd42d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     5043\n",
       "unique     440\n",
       "top       0.0$\n",
       "freq       492\n",
       "Name: budget, dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.budget.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "f33d46a2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "15000000.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.budget.str.split(\"$\", expand=True)[0].astype(\"float\").sort_values().median()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81735532",
   "metadata": {},
   "source": [
    "## Вторая часть"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "679cb12b",
   "metadata": {},
   "source": [
    "## 1. \n",
    "Загрузите датасет event_data.csv, который содержит данные использования мобильного приложения пользователей, зарегистрировавшихся в период с 29 июля по 1 сентября 2019 года:\n",
    "\n",
    "user_id - идентификатор пользователя;\n",
    "event_date - время совершения события;\n",
    "event_type - тип события: registration - регистрация в приложении; simple_event - событие клика в приложении; purchase - событие покупки внутри приложения; purchase_amount - величина покупки.\n",
    "Выделите когорты пользователей на основе недели регистрации в приложении. Идентификатором когорты должен быть порядковый номер недели (например, неделя с 29 июля по 4 августа должна иметь идентификатор 31)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "96f12dea",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (pd.read_csv(\"event_data.csv\")\n",
    "       .astype({\n",
    "           'event_type': 'category'\n",
    "       })\n",
    "       .assign(event_date = lambda x: pd.to_datetime(x.event_date, dayfirst=True))\n",
    "       .assign(week = lambda x: x.event_date.dt.isocalendar()['week'])\n",
    "      )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "358680bc",
   "metadata": {},
   "source": [
    "### 1. Какое количество уникальных пользователей в когорте с идентификатором 33?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "2388860d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2045"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df2.loc[\n",
    "    (df2.week==33) & (df2.event_type=='registration')\n",
    "].user_id.unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14b29302",
   "metadata": {},
   "source": [
    "## 2.\n",
    "Для каждого события выделите показатель lifetime — понедельное время жизни когорты. Показатель lifetime рассчитывают на основе порядкового номера недели, в которую совершено событие, относительно недели регистрации. Например, событие, совершённое 3 августа пользователем из когорты зарегистрировавшихся в 31 неделю, будет совершено в нулевую неделю lifetime, а событие, совершенное этим же пользователем 5 августа — в первую неделю lifetime).\n",
    "\n",
    "Постройте сводную таблицу изменения показателя Retention Rate по когортам в зависимости от lifetime.\n",
    "\n",
    "Какой Retention Rate на 3 неделю lifetime у когорты с идентификатором 32? Отсчет недель ведется с 0. Ответ дайте в процентах с округлением до 2 знаков после запятой включительно. Retention Rate представляет собой отношение числа активных пользователей когорты на определенную неделю к общему числу пользователей в когорте. Активным пользователем считается любой пользователь, совершивший событие."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "id": "1143733a",
   "metadata": {},
   "outputs": [],
   "source": [
    "registration_events_df = df2.loc[\n",
    "            df2.event_type==\"registration\"\n",
    "        ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "id": "8f071875",
   "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>event_date_se</th>\n",
       "      <th>event_type_se</th>\n",
       "      <th>purchase_amount_se</th>\n",
       "      <th>week_se</th>\n",
       "      <th>event_date_rg</th>\n",
       "      <th>event_type_rg</th>\n",
       "      <th>purchase_amount_rg</th>\n",
       "      <th>week_rg</th>\n",
       "      <th>_merge</th>\n",
       "      <th>lifetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>af679d</td>\n",
       "      <td>2019-07-29 02:25:19</td>\n",
       "      <td>simple_event</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "      <td>2019-07-29 00:30:46</td>\n",
       "      <td>registration</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "      <td>both</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>71ac11</td>\n",
       "      <td>2019-07-29 06:19:19</td>\n",
       "      <td>simple_event</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "      <td>2019-07-29 00:28:51</td>\n",
       "      <td>registration</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "      <td>both</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>85367a</td>\n",
       "      <td>2019-07-29 06:37:26</td>\n",
       "      <td>simple_event</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "      <td>2019-07-29 01:55:28</td>\n",
       "      <td>registration</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "      <td>both</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9b887b</td>\n",
       "      <td>2019-07-29 06:39:29</td>\n",
       "      <td>simple_event</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "      <td>2019-07-29 03:14:53</td>\n",
       "      <td>registration</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "      <td>both</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>b2e16e</td>\n",
       "      <td>2019-07-29 06:51:51</td>\n",
       "      <td>simple_event</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "      <td>2019-07-29 01:14:16</td>\n",
       "      <td>registration</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "      <td>both</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63534</th>\n",
       "      <td>930c23</td>\n",
       "      <td>2019-09-01 23:57:41</td>\n",
       "      <td>simple_event</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35</td>\n",
       "      <td>2019-08-07 15:58:32</td>\n",
       "      <td>registration</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32</td>\n",
       "      <td>both</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63535</th>\n",
       "      <td>a84999</td>\n",
       "      <td>2019-09-01 23:57:50</td>\n",
       "      <td>simple_event</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35</td>\n",
       "      <td>2019-08-14 05:08:15</td>\n",
       "      <td>registration</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33</td>\n",
       "      <td>both</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63536</th>\n",
       "      <td>175e4d</td>\n",
       "      <td>2019-09-01 23:59:40</td>\n",
       "      <td>simple_event</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35</td>\n",
       "      <td>2019-08-10 00:31:21</td>\n",
       "      <td>registration</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32</td>\n",
       "      <td>both</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63537</th>\n",
       "      <td>1c2210</td>\n",
       "      <td>2019-09-01 23:59:51</td>\n",
       "      <td>simple_event</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35</td>\n",
       "      <td>2019-08-17 09:45:33</td>\n",
       "      <td>registration</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33</td>\n",
       "      <td>both</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63538</th>\n",
       "      <td>d799dc</td>\n",
       "      <td>2019-09-01 23:59:54</td>\n",
       "      <td>simple_event</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35</td>\n",
       "      <td>2019-08-29 06:47:30</td>\n",
       "      <td>registration</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35</td>\n",
       "      <td>both</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>63539 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      user_id       event_date_se event_type_se  purchase_amount_se  week_se  \\\n",
       "0      af679d 2019-07-29 02:25:19  simple_event                 NaN       31   \n",
       "1      71ac11 2019-07-29 06:19:19  simple_event                 NaN       31   \n",
       "2      85367a 2019-07-29 06:37:26  simple_event                 NaN       31   \n",
       "3      9b887b 2019-07-29 06:39:29  simple_event                 NaN       31   \n",
       "4      b2e16e 2019-07-29 06:51:51  simple_event                 NaN       31   \n",
       "...       ...                 ...           ...                 ...      ...   \n",
       "63534  930c23 2019-09-01 23:57:41  simple_event                 NaN       35   \n",
       "63535  a84999 2019-09-01 23:57:50  simple_event                 NaN       35   \n",
       "63536  175e4d 2019-09-01 23:59:40  simple_event                 NaN       35   \n",
       "63537  1c2210 2019-09-01 23:59:51  simple_event                 NaN       35   \n",
       "63538  d799dc 2019-09-01 23:59:54  simple_event                 NaN       35   \n",
       "\n",
       "            event_date_rg event_type_rg  purchase_amount_rg  week_rg _merge  \\\n",
       "0     2019-07-29 00:30:46  registration                 NaN       31   both   \n",
       "1     2019-07-29 00:28:51  registration                 NaN       31   both   \n",
       "2     2019-07-29 01:55:28  registration                 NaN       31   both   \n",
       "3     2019-07-29 03:14:53  registration                 NaN       31   both   \n",
       "4     2019-07-29 01:14:16  registration                 NaN       31   both   \n",
       "...                   ...           ...                 ...      ...    ...   \n",
       "63534 2019-08-07 15:58:32  registration                 NaN       32   both   \n",
       "63535 2019-08-14 05:08:15  registration                 NaN       33   both   \n",
       "63536 2019-08-10 00:31:21  registration                 NaN       32   both   \n",
       "63537 2019-08-17 09:45:33  registration                 NaN       33   both   \n",
       "63538 2019-08-29 06:47:30  registration                 NaN       35   both   \n",
       "\n",
       "       lifetime  \n",
       "0             0  \n",
       "1             0  \n",
       "2             0  \n",
       "3             0  \n",
       "4             0  \n",
       "...         ...  \n",
       "63534         3  \n",
       "63535         2  \n",
       "63536         3  \n",
       "63537         2  \n",
       "63538         0  \n",
       "\n",
       "[63539 rows x 11 columns]"
      ]
     },
     "execution_count": 203,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "simple_events_df = (\n",
    "    df2.loc[\n",
    "        df2.event_type==\"simple_event\"\n",
    "    ]\n",
    "    .merge(\n",
    "        registration_events_df\n",
    "        , how='left'\n",
    "        , on=['user_id']\n",
    "        , suffixes=['_se', '_rg']\n",
    "        , indicator=True\n",
    "    )\n",
    "    .assign(lifetime = lambda x: x.week_se - x.week_rg)\n",
    ")\n",
    "simple_events_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "8bc7298a",
   "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>week_rg</th>\n",
       "      <th>lifetime</th>\n",
       "      <th>retention</th>\n",
       "      <th>base</th>\n",
       "      <th>retention_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>3467</td>\n",
       "      <td>17457</td>\n",
       "      <td>0.198602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31</td>\n",
       "      <td>1</td>\n",
       "      <td>6743</td>\n",
       "      <td>17457</td>\n",
       "      <td>0.386263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>31</td>\n",
       "      <td>2</td>\n",
       "      <td>4271</td>\n",
       "      <td>17457</td>\n",
       "      <td>0.244658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>31</td>\n",
       "      <td>3</td>\n",
       "      <td>2204</td>\n",
       "      <td>17457</td>\n",
       "      <td>0.126253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31</td>\n",
       "      <td>4</td>\n",
       "      <td>772</td>\n",
       "      <td>17457</td>\n",
       "      <td>0.044223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>32</td>\n",
       "      <td>0</td>\n",
       "      <td>3395</td>\n",
       "      <td>16678</td>\n",
       "      <td>0.203562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>6745</td>\n",
       "      <td>16678</td>\n",
       "      <td>0.404425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>32</td>\n",
       "      <td>2</td>\n",
       "      <td>4303</td>\n",
       "      <td>16678</td>\n",
       "      <td>0.258005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>32</td>\n",
       "      <td>3</td>\n",
       "      <td>2235</td>\n",
       "      <td>16678</td>\n",
       "      <td>0.134009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>33</td>\n",
       "      <td>0</td>\n",
       "      <td>3657</td>\n",
       "      <td>15420</td>\n",
       "      <td>0.237160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "      <td>7101</td>\n",
       "      <td>15420</td>\n",
       "      <td>0.460506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>33</td>\n",
       "      <td>2</td>\n",
       "      <td>4662</td>\n",
       "      <td>15420</td>\n",
       "      <td>0.302335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>34</td>\n",
       "      <td>0</td>\n",
       "      <td>3477</td>\n",
       "      <td>10350</td>\n",
       "      <td>0.335942</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>34</td>\n",
       "      <td>1</td>\n",
       "      <td>6873</td>\n",
       "      <td>10350</td>\n",
       "      <td>0.664058</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>35</td>\n",
       "      <td>0</td>\n",
       "      <td>3634</td>\n",
       "      <td>3634</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    week_rg  lifetime  retention   base  retention_rate\n",
       "0        31         0       3467  17457        0.198602\n",
       "1        31         1       6743  17457        0.386263\n",
       "2        31         2       4271  17457        0.244658\n",
       "3        31         3       2204  17457        0.126253\n",
       "4        31         4        772  17457        0.044223\n",
       "5        32         0       3395  16678        0.203562\n",
       "6        32         1       6745  16678        0.404425\n",
       "7        32         2       4303  16678        0.258005\n",
       "8        32         3       2235  16678        0.134009\n",
       "9        33         0       3657  15420        0.237160\n",
       "10       33         1       7101  15420        0.460506\n",
       "11       33         2       4662  15420        0.302335\n",
       "12       34         0       3477  10350        0.335942\n",
       "13       34         1       6873  10350        0.664058\n",
       "14       35         0       3634   3634        1.000000"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "retention_df = (simple_events_df\n",
    " .groupby(['week_rg', 'lifetime'])\n",
    " .agg(\n",
    "     retention=pd.NamedAgg(column='user_id', aggfunc='count')\n",
    " )\n",
    " .reset_index()\n",
    ").merge(\n",
    "    (simple_events_df\n",
    "     .groupby(['week_rg'])\n",
    "     .agg(\n",
    "         base=pd.NamedAgg(column='user_id', aggfunc='count'))\n",
    "     .reset_index()\n",
    "    ), how='left', on=['week_rg']\n",
    ").assign(retention_rate=lambda x: x.retention / x.base)\n",
    "retention_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "id": "4248a078",
   "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>lifetime</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>week_rg</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>0.198602</td>\n",
       "      <td>0.386263</td>\n",
       "      <td>0.244658</td>\n",
       "      <td>0.126253</td>\n",
       "      <td>0.044223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>0.203562</td>\n",
       "      <td>0.404425</td>\n",
       "      <td>0.258005</td>\n",
       "      <td>0.134009</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>0.237160</td>\n",
       "      <td>0.460506</td>\n",
       "      <td>0.302335</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>0.335942</td>\n",
       "      <td>0.664058</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "lifetime         0         1         2         3         4\n",
       "week_rg                                                   \n",
       "31        0.198602  0.386263  0.244658  0.126253  0.044223\n",
       "32        0.203562  0.404425  0.258005  0.134009  0.000000\n",
       "33        0.237160  0.460506  0.302335  0.000000  0.000000\n",
       "34        0.335942  0.664058  0.000000  0.000000  0.000000\n",
       "35        1.000000  0.000000  0.000000  0.000000  0.000000"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "retention_df.pivot(\n",
    "    index='week_rg',\n",
    "    columns='lifetime',\n",
    "    values='retention_rate').fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5bc357bf",
   "metadata": {},
   "source": [
    "### 2. Какой Retention Rate на 3 неделю lifetime у когорты с идентификатором 32? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "id": "8d862770",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.13"
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round(retention_df.loc[\n",
    "    lambda x: (x.week_rg==32) & (x.lifetime==3)\n",
    "    , \"retention_rate\"\n",
    "].values[0], 2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c22d886",
   "metadata": {},
   "source": [
    "## Третья часть\n",
    "Постройте сводную таблицу изменения показателя ARPPU (Average Revenue Per Paying User) по когортам в зависимости от lifetime. ARPPU для заданой недели рассчитывается как сумма величин покупок, деленная на количество пользователей, совершивших покупку.\n",
    "\n",
    "Какой ARPPU на 3 неделю lifetime у когорты с идентификатором 31? Отсчет недель ведется с 0. Ответ дайте числом с плавающей запятой с округлением до 2 знаков после запятой включительно."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "a94cb5e1",
   "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>event_date_pc</th>\n",
       "      <th>event_type_pc</th>\n",
       "      <th>purchase_amount_pc</th>\n",
       "      <th>week_pc</th>\n",
       "      <th>event_date_rg</th>\n",
       "      <th>event_type_rg</th>\n",
       "      <th>purchase_amount_rg</th>\n",
       "      <th>week_rg</th>\n",
       "      <th>_merge</th>\n",
       "      <th>lifetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>51dc02</td>\n",
       "      <td>2019-07-29 11:43:11</td>\n",
       "      <td>purchase</td>\n",
       "      <td>10.0</td>\n",
       "      <td>31</td>\n",
       "      <td>2019-07-29 07:16:36</td>\n",
       "      <td>registration</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "      <td>both</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>f1c95f</td>\n",
       "      <td>2019-07-29 11:48:46</td>\n",
       "      <td>purchase</td>\n",
       "      <td>50.0</td>\n",
       "      <td>31</td>\n",
       "      <td>2019-07-29 02:35:11</td>\n",
       "      <td>registration</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "      <td>both</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a13a2b</td>\n",
       "      <td>2019-07-29 14:50:07</td>\n",
       "      <td>purchase</td>\n",
       "      <td>10.0</td>\n",
       "      <td>31</td>\n",
       "      <td>2019-07-29 12:32:03</td>\n",
       "      <td>registration</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "      <td>both</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>deccc0</td>\n",
       "      <td>2019-07-29 23:38:20</td>\n",
       "      <td>purchase</td>\n",
       "      <td>50.0</td>\n",
       "      <td>31</td>\n",
       "      <td>2019-07-29 13:25:00</td>\n",
       "      <td>registration</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "      <td>both</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11e04e</td>\n",
       "      <td>2019-07-30 03:08:15</td>\n",
       "      <td>purchase</td>\n",
       "      <td>50.0</td>\n",
       "      <td>31</td>\n",
       "      <td>2019-07-29 21:56:29</td>\n",
       "      <td>registration</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "      <td>both</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6202</th>\n",
       "      <td>7f27ee</td>\n",
       "      <td>2019-09-01 23:31:37</td>\n",
       "      <td>purchase</td>\n",
       "      <td>10.0</td>\n",
       "      <td>35</td>\n",
       "      <td>2019-08-11 14:44:59</td>\n",
       "      <td>registration</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32</td>\n",
       "      <td>both</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6203</th>\n",
       "      <td>e1d4ac</td>\n",
       "      <td>2019-09-01 23:39:00</td>\n",
       "      <td>purchase</td>\n",
       "      <td>50.0</td>\n",
       "      <td>35</td>\n",
       "      <td>2019-08-13 13:22:58</td>\n",
       "      <td>registration</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33</td>\n",
       "      <td>both</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6204</th>\n",
       "      <td>f1b677</td>\n",
       "      <td>2019-09-01 23:39:49</td>\n",
       "      <td>purchase</td>\n",
       "      <td>50.0</td>\n",
       "      <td>35</td>\n",
       "      <td>2019-08-13 01:46:17</td>\n",
       "      <td>registration</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33</td>\n",
       "      <td>both</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6205</th>\n",
       "      <td>0a5860</td>\n",
       "      <td>2019-09-01 23:54:50</td>\n",
       "      <td>purchase</td>\n",
       "      <td>30.0</td>\n",
       "      <td>35</td>\n",
       "      <td>2019-08-28 01:13:29</td>\n",
       "      <td>registration</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35</td>\n",
       "      <td>both</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6206</th>\n",
       "      <td>27583e</td>\n",
       "      <td>2019-09-01 23:54:55</td>\n",
       "      <td>purchase</td>\n",
       "      <td>30.0</td>\n",
       "      <td>35</td>\n",
       "      <td>2019-08-30 15:33:33</td>\n",
       "      <td>registration</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35</td>\n",
       "      <td>both</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6207 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     user_id       event_date_pc event_type_pc  purchase_amount_pc  week_pc  \\\n",
       "0     51dc02 2019-07-29 11:43:11      purchase                10.0       31   \n",
       "1     f1c95f 2019-07-29 11:48:46      purchase                50.0       31   \n",
       "2     a13a2b 2019-07-29 14:50:07      purchase                10.0       31   \n",
       "3     deccc0 2019-07-29 23:38:20      purchase                50.0       31   \n",
       "4     11e04e 2019-07-30 03:08:15      purchase                50.0       31   \n",
       "...      ...                 ...           ...                 ...      ...   \n",
       "6202  7f27ee 2019-09-01 23:31:37      purchase                10.0       35   \n",
       "6203  e1d4ac 2019-09-01 23:39:00      purchase                50.0       35   \n",
       "6204  f1b677 2019-09-01 23:39:49      purchase                50.0       35   \n",
       "6205  0a5860 2019-09-01 23:54:50      purchase                30.0       35   \n",
       "6206  27583e 2019-09-01 23:54:55      purchase                30.0       35   \n",
       "\n",
       "           event_date_rg event_type_rg  purchase_amount_rg  week_rg _merge  \\\n",
       "0    2019-07-29 07:16:36  registration                 NaN       31   both   \n",
       "1    2019-07-29 02:35:11  registration                 NaN       31   both   \n",
       "2    2019-07-29 12:32:03  registration                 NaN       31   both   \n",
       "3    2019-07-29 13:25:00  registration                 NaN       31   both   \n",
       "4    2019-07-29 21:56:29  registration                 NaN       31   both   \n",
       "...                  ...           ...                 ...      ...    ...   \n",
       "6202 2019-08-11 14:44:59  registration                 NaN       32   both   \n",
       "6203 2019-08-13 13:22:58  registration                 NaN       33   both   \n",
       "6204 2019-08-13 01:46:17  registration                 NaN       33   both   \n",
       "6205 2019-08-28 01:13:29  registration                 NaN       35   both   \n",
       "6206 2019-08-30 15:33:33  registration                 NaN       35   both   \n",
       "\n",
       "      lifetime  \n",
       "0            0  \n",
       "1            0  \n",
       "2            0  \n",
       "3            0  \n",
       "4            0  \n",
       "...        ...  \n",
       "6202         3  \n",
       "6203         2  \n",
       "6204         2  \n",
       "6205         0  \n",
       "6206         0  \n",
       "\n",
       "[6207 rows x 11 columns]"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "purcahse_df = (\n",
    "    df2.loc[\n",
    "        df2.event_type==\"purchase\"\n",
    "    ]\n",
    "    .merge(\n",
    "        df2.loc[\n",
    "            df2.event_type==\"registration\"\n",
    "        ], how='left', on=['user_id'], suffixes=['_pc', '_rg'], indicator=True\n",
    "    )\n",
    "    .assign(lifetime = lambda x: x.week_pc - x.week_rg)\n",
    ")\n",
    "purcahse_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "id": "e9293e31",
   "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>week_rg</th>\n",
       "      <th>lifetime</th>\n",
       "      <th>revenue</th>\n",
       "      <th>users</th>\n",
       "      <th>arppu</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>8890.0</td>\n",
       "      <td>316</td>\n",
       "      <td>28.132911</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31</td>\n",
       "      <td>1</td>\n",
       "      <td>20540.0</td>\n",
       "      <td>696</td>\n",
       "      <td>29.511494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>31</td>\n",
       "      <td>2</td>\n",
       "      <td>12210.0</td>\n",
       "      <td>400</td>\n",
       "      <td>30.525000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>31</td>\n",
       "      <td>3</td>\n",
       "      <td>6120.0</td>\n",
       "      <td>210</td>\n",
       "      <td>29.142857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31</td>\n",
       "      <td>4</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>66</td>\n",
       "      <td>30.454545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>32</td>\n",
       "      <td>0</td>\n",
       "      <td>10850.0</td>\n",
       "      <td>359</td>\n",
       "      <td>30.222841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>21050.0</td>\n",
       "      <td>707</td>\n",
       "      <td>29.773692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>32</td>\n",
       "      <td>2</td>\n",
       "      <td>12600.0</td>\n",
       "      <td>421</td>\n",
       "      <td>29.928741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>32</td>\n",
       "      <td>3</td>\n",
       "      <td>6260.0</td>\n",
       "      <td>212</td>\n",
       "      <td>29.528302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>33</td>\n",
       "      <td>0</td>\n",
       "      <td>9790.0</td>\n",
       "      <td>319</td>\n",
       "      <td>30.689655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "      <td>21730.0</td>\n",
       "      <td>712</td>\n",
       "      <td>30.519663</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>33</td>\n",
       "      <td>2</td>\n",
       "      <td>13740.0</td>\n",
       "      <td>471</td>\n",
       "      <td>29.171975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>34</td>\n",
       "      <td>0</td>\n",
       "      <td>9510.0</td>\n",
       "      <td>309</td>\n",
       "      <td>30.776699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>34</td>\n",
       "      <td>1</td>\n",
       "      <td>19640.0</td>\n",
       "      <td>635</td>\n",
       "      <td>30.929134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>35</td>\n",
       "      <td>0</td>\n",
       "      <td>11490.0</td>\n",
       "      <td>374</td>\n",
       "      <td>30.721925</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    week_rg  lifetime  revenue  users      arppu\n",
       "0        31         0   8890.0    316  28.132911\n",
       "1        31         1  20540.0    696  29.511494\n",
       "2        31         2  12210.0    400  30.525000\n",
       "3        31         3   6120.0    210  29.142857\n",
       "4        31         4   2010.0     66  30.454545\n",
       "5        32         0  10850.0    359  30.222841\n",
       "6        32         1  21050.0    707  29.773692\n",
       "7        32         2  12600.0    421  29.928741\n",
       "8        32         3   6260.0    212  29.528302\n",
       "9        33         0   9790.0    319  30.689655\n",
       "10       33         1  21730.0    712  30.519663\n",
       "11       33         2  13740.0    471  29.171975\n",
       "12       34         0   9510.0    309  30.776699\n",
       "13       34         1  19640.0    635  30.929134\n",
       "14       35         0  11490.0    374  30.721925"
      ]
     },
     "execution_count": 145,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "arppu_df = (purcahse_df\n",
    " .groupby(['week_rg', 'lifetime'])\n",
    " .agg(\n",
    "     revenue=pd.NamedAgg(column='purchase_amount_pc', aggfunc='sum'),\n",
    "     users=pd.NamedAgg(column=\"user_id\", aggfunc=\"count\")    \n",
    " )\n",
    " .reset_index()\n",
    " .assign(arppu=lambda x: x.revenue / x.users)\n",
    ")\n",
    "arppu_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "id": "1a223358",
   "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>lifetime</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>week_rg</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>28.132911</td>\n",
       "      <td>29.511494</td>\n",
       "      <td>30.525000</td>\n",
       "      <td>29.142857</td>\n",
       "      <td>30.454545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>30.222841</td>\n",
       "      <td>29.773692</td>\n",
       "      <td>29.928741</td>\n",
       "      <td>29.528302</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>30.689655</td>\n",
       "      <td>30.519663</td>\n",
       "      <td>29.171975</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>30.776699</td>\n",
       "      <td>30.929134</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>30.721925</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "lifetime          0          1          2          3          4\n",
       "week_rg                                                        \n",
       "31        28.132911  29.511494  30.525000  29.142857  30.454545\n",
       "32        30.222841  29.773692  29.928741  29.528302   0.000000\n",
       "33        30.689655  30.519663  29.171975   0.000000   0.000000\n",
       "34        30.776699  30.929134   0.000000   0.000000   0.000000\n",
       "35        30.721925   0.000000   0.000000   0.000000   0.000000"
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "arppu_df.pivot(\n",
    "    index=\"week_rg\",\n",
    "    columns=\"lifetime\",\n",
    "    values=\"arppu\"\n",
    ").fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d0bde6b6",
   "metadata": {},
   "source": [
    "### 3. Какой ARPPU на 3 неделю lifetime у когорты с идентификатором 31? Отсчет недель ведется с 0. Ответ дайте числом с плавающей запятой с округлением до 2 знаков после запятой включительно."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "id": "a2369508",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "29.14"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round(arppu_df.loc[\n",
    "    lambda x: \n",
    "    (x.week_rg==31) & (x.lifetime==3)\n",
    "    , \"arppu\"\n",
    "].values[0], 2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3eb3f42",
   "metadata": {},
   "source": [
    " ## 4. \n",
    " Временем регистрации будем считать время совершения первого события пользователем. Определите для каждого пользователя время регистрации и время первой покупки. Какое медианное время проходит между регистрацией пользователя и первой покупкой? Ответ дайте в секундах целым числом"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "id": "23bda4b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "first_simple_event_df = (simple_events_df\n",
    " .groupby(['user_id'])\n",
    " .agg(\n",
    "     reg_date=pd.NamedAgg(column='event_date_se', aggfunc='min')\n",
    " )\n",
    " .reset_index()\n",
    ")\n",
    "first_purchase_df = (purcahse_df\n",
    " .groupby(['user_id'])\n",
    " .agg(\n",
    "     purchase_date=pd.NamedAgg(column='event_date_pc', aggfunc='min')\n",
    " )\n",
    " .reset_index()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "id": "e0485295",
   "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>purchase_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00049f</td>\n",
       "      <td>2019-08-18 17:31:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00082c</td>\n",
       "      <td>2019-08-14 12:57:41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>001d5d</td>\n",
       "      <td>2019-08-26 13:03:22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>002195</td>\n",
       "      <td>2019-08-18 22:35:43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0025c1</td>\n",
       "      <td>2019-08-10 11:37:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4278</th>\n",
       "      <td>ffb3f3</td>\n",
       "      <td>2019-08-04 06:47:40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4279</th>\n",
       "      <td>ffbe61</td>\n",
       "      <td>2019-08-21 21:14:07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4280</th>\n",
       "      <td>ffc66a</td>\n",
       "      <td>2019-08-16 06:05:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4281</th>\n",
       "      <td>ffc88b</td>\n",
       "      <td>2019-08-09 15:36:48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4282</th>\n",
       "      <td>fff4aa</td>\n",
       "      <td>2019-08-23 08:26:15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4283 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     user_id       purchase_date\n",
       "0     00049f 2019-08-18 17:31:56\n",
       "1     00082c 2019-08-14 12:57:41\n",
       "2     001d5d 2019-08-26 13:03:22\n",
       "3     002195 2019-08-18 22:35:43\n",
       "4     0025c1 2019-08-10 11:37:00\n",
       "...      ...                 ...\n",
       "4278  ffb3f3 2019-08-04 06:47:40\n",
       "4279  ffbe61 2019-08-21 21:14:07\n",
       "4280  ffc66a 2019-08-16 06:05:05\n",
       "4281  ffc88b 2019-08-09 15:36:48\n",
       "4282  fff4aa 2019-08-23 08:26:15\n",
       "\n",
       "[4283 rows x 2 columns]"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_purchase_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 212,
   "id": "20c0d29a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "43623.0"
      ]
     },
     "execution_count": 212,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(registration_events_df\n",
    " .merge(\n",
    "     first_purchase_df, how='inner', on=['user_id']\n",
    " )\n",
    " .assign(time_to_purchase=lambda x: (x.purchase_date - x.event_date).dt.seconds)\n",
    " ).time_to_purchase.median()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b01e4a7",
   "metadata": {},
   "source": [
    "## 3."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60050ce4",
   "metadata": {},
   "source": [
    "### 3.1 \n",
    "Укрупненно, DataFrame - это таблица. В ней есть строки и столбцы. Причем, у каждой строки есть свой номер (индекс). \n",
    "А Series - это один столбец, для которого по строкам также есть номер строки.\n",
    "\n",
    "Например, ниже - DataFrame:\n",
    "Номер | Имя | Цена | Количество\n",
    "1          | Дуб  | 10     | 12\n",
    "2          | Вяз  | 12     | 18\n",
    "3          | Бук  | 10     | 14\n",
    "\n",
    "В нем 3 строки и 3 столбца (1-й столбец, номер, является специальным столбцом, называемым \"индекс\")\n",
    "\n",
    "А вот это - Series:\n",
    "Номер | Имя \n",
    "1          | Дуб  \n",
    "2          | Вяз  \n",
    "3          | Бук \n",
    "В нем только 1 столбец, в котором содержится 3 значения по строкам."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3050bfe4",
   "metadata": {},
   "source": [
    "### 3.2\n",
    "В целом логика близка к правильной, однако есть несколько важных моментов, которые нужно учесть, чтобы получить верный результат:\n",
    "\n",
    "1. Что произойдет в текущем варианте решения, когда нужный жанр впервые встретится в таблице? \n",
    "\n",
    "В цикле будут перебираться последовательно все имена. Как только имя из таблицы будет совпадать с искомым именем, увеличится переменная сount на единицу. То есть, сount будет равным 1.\n",
    "Здесь пока все корректно.\n",
    "\n",
    "Далее, в этом же условии, стоит инструкция \"return count\". То есть, \"заверши выполнение функции и верни count\". Здесь функция остановится и вернет 1. Все, дальше цикл исполняться не будет. А это не совсем корректно - среди оставшихся значений в списке могут быть нужные нам.\n",
    "\n",
    "2. Что произойдет, если нужного нам имени нет в списке?\n",
    "Допустим, список жанров выглядит вот так: ['pop', 'hip-hop', 'rap' ]. И мы ищем имя 'hip'.\n",
    "- На первом шаге 'pop' равно 'hip'? Нет. Переходим на след.шаг\n",
    "- На втором 'hip-hop' равно 'hip'? Нет. Переходим на след.шаг\n",
    "- На третьем 'rap' равно 'hip'? Нет. Список закончился, выходим из цикла.\n",
    "\n",
    "Далее идет инструкция print(count) - \"распечатай на экран значение в переменной count\". То есть, выведи на экран 0.\n",
    "\n",
    "И здесь же третий важный момент: \n",
    "\n",
    "3. что \"вернет\" функция find_genre в этом случае? До инструкции return мы не дошли. В случае, когда программист не указал явным образом, что возвращать, программа сама вернет из функции специальное значение None - по сути, это \"ничего, ничто, пусто\".\n",
    "\n",
    "Поэтому, чтобы получить нужный результат, надо доработать код:\n",
    "- сделать так, чтобы функция не останавливалась на первом же нахождении имени в списке\n",
    "- возвращать из функции в любом случае результат поиска"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b5a1a7c",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.10.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}