Price Points with Content Lifecycle - in Progress-NextVersion.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Workbook explores results of the TV Test by price points"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Load core dataset for ASINs \n",
    "df = pd.read_csv(\"Z://00_ETL/TV_test/asin_in_line_wDashboard_4.txt\", sep=\"\\t\", encoding = \"ISO-8859-1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1881683, 19) 2017-11-04 00:00:00 2018-06-09 00:00:00\n"
     ]
    }
   ],
   "source": [
    "print(df.shape,df['week_ending'].min(),df['week_ending'].max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def initial_setup(df,content):\n",
    "    \"\"\"function makes basic data type transformations, \n",
    "    converts object to datetime format and renames lifecycles, gets month\"\"\"\n",
    "    \n",
    "    \n",
    "    df['week_ending'] = pd.to_datetime(df['week_ending'])\n",
    "    df['release_date'] = pd.to_datetime(df['release_date'])\n",
    "    df['month'] = df['week_ending'].dt.month\n",
    "    \n",
    "    #Subset and work only with particular content    \n",
    "    df = df[df['content'] == content]\n",
    "    \n",
    "        \n",
    "    \n",
    "    #Add only last lifecycle in the promo period to be the base, rest get last content age\n",
    "    age = df[(df['week_ending'] >= '2018-03-10')\n",
    "                       & (df['week_ending'] < '2018-05-12')].groupby(\"asin\")['content_age'].first().reset_index()\n",
    "    \n",
    "    age.columns = ['asin','lifecycles_last']\n",
    "    \n",
    "    \n",
    "    merge_age = pd.merge(left = df, right = age, left_on = \"asin\", right_on = \"asin\", how = \"left\")\n",
    "    \n",
    "    add_on = merge_age[merge_age['lifecycles_last'].isnull()].groupby(\"asin\")['content_age'].last().reset_index()\n",
    "    add_on.columns = ['asin','add_on']\n",
    "    \n",
    "    df_next = pd.merge(left = merge_age, right = add_on, left_on = \"asin\", right_on = \"asin\", how = \"left\")\n",
    "    \n",
    "    \n",
    "    df_next['lifecycles_last'] = np.where(df_next['lifecycles_last'].isnull(), df_next['add_on'],df_next['lifecycles_last'])\n",
    "    df_next = df_next.drop(['add_on'], axis = 1)\n",
    "    \n",
    "    df_next['easy_lifecycles'] = np.where(df_next['lifecycles_last'].isin(['New Release','Recent release']),'RR','Catalogue')\n",
    "    \n",
    "    \n",
    "    return df_next"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = initial_setup(df, \"Season\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(244190, 22) 13488\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>week_ending</th>\n",
       "      <th>is_promo</th>\n",
       "      <th>asin</th>\n",
       "      <th>is_promo_asin</th>\n",
       "      <th>content</th>\n",
       "      <th>title_name</th>\n",
       "      <th>studio_name</th>\n",
       "      <th>contract_id</th>\n",
       "      <th>release_date</th>\n",
       "      <th>is_hd</th>\n",
       "      <th>...</th>\n",
       "      <th>content_age</th>\n",
       "      <th>customers</th>\n",
       "      <th>new_customers</th>\n",
       "      <th>units</th>\n",
       "      <th>ops</th>\n",
       "      <th>cogs</th>\n",
       "      <th>ppm</th>\n",
       "      <th>month</th>\n",
       "      <th>lifecycles_last</th>\n",
       "      <th>easy_lifecycles</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-11-04</td>\n",
       "      <td>non_promo_period</td>\n",
       "      <td>B00HXG8PQK</td>\n",
       "      <td>non_promo_asins</td>\n",
       "      <td>Season</td>\n",
       "      <td>Yakari - Staffel 2</td>\n",
       "      <td>Ki.Ka, ZDF, ORF</td>\n",
       "      <td>UXKN4</td>\n",
       "      <td>2010-07-05</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>Deep Catalogue</td>\n",
       "      <td>34</td>\n",
       "      <td>1</td>\n",
       "      <td>34</td>\n",
       "      <td>276.82</td>\n",
       "      <td>185.86</td>\n",
       "      <td>0.3285</td>\n",
       "      <td>11</td>\n",
       "      <td>Deep Catalogue</td>\n",
       "      <td>Catalogue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-11-04</td>\n",
       "      <td>non_promo_period</td>\n",
       "      <td>B00QX1DHFU</td>\n",
       "      <td>non_promo_asins</td>\n",
       "      <td>Season</td>\n",
       "      <td>Phineas und Ferb Staffel 1</td>\n",
       "      <td>Disney Channel</td>\n",
       "      <td>UXDY4</td>\n",
       "      <td>2014-02-26</td>\n",
       "      <td>Y</td>\n",
       "      <td>...</td>\n",
       "      <td>Deep Catalogue</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>60.37</td>\n",
       "      <td>42.34</td>\n",
       "      <td>0.2986</td>\n",
       "      <td>11</td>\n",
       "      <td>Deep Catalogue</td>\n",
       "      <td>Catalogue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-11-04</td>\n",
       "      <td>non_promo_period</td>\n",
       "      <td>B071FN9YNC</td>\n",
       "      <td>Promo_Asins</td>\n",
       "      <td>Season</td>\n",
       "      <td>München 7</td>\n",
       "      <td>ARD</td>\n",
       "      <td>UXTP4</td>\n",
       "      <td>2017-06-02</td>\n",
       "      <td>Y</td>\n",
       "      <td>...</td>\n",
       "      <td>Recent release</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>12.60</td>\n",
       "      <td>8.82</td>\n",
       "      <td>0.3000</td>\n",
       "      <td>11</td>\n",
       "      <td>6mo_1yr_Catalogue</td>\n",
       "      <td>Catalogue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-11-04</td>\n",
       "      <td>non_promo_period</td>\n",
       "      <td>B0757S91QL</td>\n",
       "      <td>non_promo_asins</td>\n",
       "      <td>Season</td>\n",
       "      <td>Looney Tunes: Best Of Tweety und Sylvester, Vo...</td>\n",
       "      <td>Warner Bros.</td>\n",
       "      <td>UXWB4</td>\n",
       "      <td>2017-04-10</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>6mo_1yr_Catalogue</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>37.80</td>\n",
       "      <td>22.92</td>\n",
       "      <td>0.3936</td>\n",
       "      <td>11</td>\n",
       "      <td>1_2yr_Catalogue</td>\n",
       "      <td>Catalogue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-11-04</td>\n",
       "      <td>non_promo_period</td>\n",
       "      <td>B01F2JHD1C</td>\n",
       "      <td>Promo_Asins</td>\n",
       "      <td>Season</td>\n",
       "      <td>Keeping Up With the Kardashians - Season 12 [OV]</td>\n",
       "      <td>E!</td>\n",
       "      <td>UXNB4</td>\n",
       "      <td>2016-05-03</td>\n",
       "      <td>Y</td>\n",
       "      <td>...</td>\n",
       "      <td>1_2yr_Catalogue</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>27.72</td>\n",
       "      <td>19.98</td>\n",
       "      <td>0.2792</td>\n",
       "      <td>11</td>\n",
       "      <td>2_3yr_Catalogue</td>\n",
       "      <td>Catalogue</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  week_ending          is_promo        asin    is_promo_asin content  \\\n",
       "0  2017-11-04  non_promo_period  B00HXG8PQK  non_promo_asins  Season   \n",
       "1  2017-11-04  non_promo_period  B00QX1DHFU  non_promo_asins  Season   \n",
       "2  2017-11-04  non_promo_period  B071FN9YNC      Promo_Asins  Season   \n",
       "3  2017-11-04  non_promo_period  B0757S91QL  non_promo_asins  Season   \n",
       "4  2017-11-04  non_promo_period  B01F2JHD1C      Promo_Asins  Season   \n",
       "\n",
       "                                          title_name      studio_name  \\\n",
       "0                                 Yakari - Staffel 2  Ki.Ka, ZDF, ORF   \n",
       "1                         Phineas und Ferb Staffel 1   Disney Channel   \n",
       "2                                          München 7              ARD   \n",
       "3  Looney Tunes: Best Of Tweety und Sylvester, Vo...     Warner Bros.   \n",
       "4   Keeping Up With the Kardashians - Season 12 [OV]               E!   \n",
       "\n",
       "  contract_id release_date is_hd       ...               content_age  \\\n",
       "0       UXKN4   2010-07-05     N       ...            Deep Catalogue   \n",
       "1       UXDY4   2014-02-26     Y       ...            Deep Catalogue   \n",
       "2       UXTP4   2017-06-02     Y       ...            Recent release   \n",
       "3       UXWB4   2017-04-10     N       ...         6mo_1yr_Catalogue   \n",
       "4       UXNB4   2016-05-03     Y       ...           1_2yr_Catalogue   \n",
       "\n",
       "   customers new_customers  units     ops    cogs     ppm  month  \\\n",
       "0         34             1     34  276.82  185.86  0.3285     11   \n",
       "1          5             1      5   60.37   42.34  0.2986     11   \n",
       "2          1             0      1   12.60    8.82  0.3000     11   \n",
       "3          3             0      3   37.80   22.92  0.3936     11   \n",
       "4          2             0      2   27.72   19.98  0.2792     11   \n",
       "\n",
       "     lifecycles_last  easy_lifecycles  \n",
       "0     Deep Catalogue        Catalogue  \n",
       "1     Deep Catalogue        Catalogue  \n",
       "2  6mo_1yr_Catalogue        Catalogue  \n",
       "3    1_2yr_Catalogue        Catalogue  \n",
       "4    2_3yr_Catalogue        Catalogue  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df.shape, df['asin'].nunique())\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### STEP 1 : Critical part - sanity check to mare sure the same data as in the dashboard!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average OPS in Feb\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "255112.76166666698"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"Average OPS in Feb\")\n",
    "df[(df['week_ending'] >= '2018-01-27')\n",
    "   & (df['week_ending'] < '2018-03-10')\n",
    "   & (df['is_promo_asin'] == \"Promo_Asins\")].groupby('week_ending')['ops'].sum().reset_index()['ops'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2247250.7100000205\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>week_ending</th>\n",
       "      <th>new_customers</th>\n",
       "      <th>units</th>\n",
       "      <th>ops</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-03-10</td>\n",
       "      <td>2077</td>\n",
       "      <td>52742</td>\n",
       "      <td>337491.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-03-17</td>\n",
       "      <td>2106</td>\n",
       "      <td>43532</td>\n",
       "      <td>259809.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-03-24</td>\n",
       "      <td>1895</td>\n",
       "      <td>40603</td>\n",
       "      <td>237363.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-03-31</td>\n",
       "      <td>1842</td>\n",
       "      <td>44182</td>\n",
       "      <td>254590.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-04-07</td>\n",
       "      <td>1634</td>\n",
       "      <td>37775</td>\n",
       "      <td>214874.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018-04-14</td>\n",
       "      <td>1172</td>\n",
       "      <td>32413</td>\n",
       "      <td>187994.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2018-04-21</td>\n",
       "      <td>1001</td>\n",
       "      <td>29440</td>\n",
       "      <td>174938.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2018-04-28</td>\n",
       "      <td>1109</td>\n",
       "      <td>30392</td>\n",
       "      <td>182927.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2018-05-05</td>\n",
       "      <td>1214</td>\n",
       "      <td>34075</td>\n",
       "      <td>205540.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2018-05-12</td>\n",
       "      <td>909</td>\n",
       "      <td>27519</td>\n",
       "      <td>191721.54</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  week_ending  new_customers  units        ops\n",
       "0  2018-03-10           2077  52742  337491.67\n",
       "1  2018-03-17           2106  43532  259809.48\n",
       "2  2018-03-24           1895  40603  237363.10\n",
       "3  2018-03-31           1842  44182  254590.04\n",
       "4  2018-04-07           1634  37775  214874.27\n",
       "5  2018-04-14           1172  32413  187994.60\n",
       "6  2018-04-21           1001  29440  174938.32\n",
       "7  2018-04-28           1109  30392  182927.48\n",
       "8  2018-05-05           1214  34075  205540.21\n",
       "9  2018-05-12            909  27519  191721.54"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df[(df['is_promo'] == 'promo_period')\n",
    "   & (df['is_promo_asin'] == \"Promo_Asins\")].groupby(\"week_ending\")['new_customers',\n",
    "                                                                    'units','ops'].sum().reset_index()['ops'].sum())\n",
    "df[(df['is_promo'] == 'promo_period')\n",
    "   & (df['is_promo_asin'] == \"Promo_Asins\")].groupby(\"week_ending\")['new_customers',\n",
    "                                                                    'units','ops'].sum().reset_index()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Distribution of promo OPS by price points"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def dist_by_revenue(df):\n",
    "    t = pd.DataFrame(df[(df['is_promo'] == 'promo_period')\n",
    "                        & (df['is_promo_asin'] == \"Promo_Asins\")].groupby(\"display_price\")['ops'].sum().reset_index())\n",
    "    t.columns = ['display_price','ops']\n",
    "    t['share'] = t['ops'] / t['ops'].sum()\n",
    "    t['cumsum'] = t['share'].cumsum()\n",
    "    return t"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "rev_check_price_point = dist_by_revenue(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2247250.7099999427\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(rev_check_price_point['ops'].sum())\n",
    "round(rev_check_price_point['ops'].sum(),2) == round(df[(df['is_promo'] == 'promo_period')\n",
    "   & (df['is_promo_asin'] == \"Promo_Asins\")].groupby(\"week_ending\")['new_customers',\n",
    "                                                                    'units','ops'].sum().reset_index()['ops'].sum(),2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Share of the price points, ca. : 0.929\n",
      "Revenue from three price points with non cleaned ASINs:  2087161.9999999418\n"
     ]
    }
   ],
   "source": [
    "#Check the share of OPS covered by those 3 price points\n",
    "checker = rev_check_price_point[rev_check_price_point['display_price'].isin([4.99, 9.99, 14.99])]['ops'].sum() / rev_check_price_point['ops'].sum()\n",
    "print(\"Share of the price points, ca. :\" , round(checker,3) )\n",
    "print(\"Revenue from three price points with non cleaned ASINs: \", rev_check_price_point[rev_check_price_point['display_price'].isin([4.99, 9.99, 14.99])]['ops'].sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Make a list of seasonal coefficiens"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "new_data_daily = pd.read_csv(\"../TV_test/data/forecast_DE_lifecycles_daily.txt\", sep=\"\\t\",\n",
    "                       names = ['promo_period','week_ending','transaction_date','promo_status','content','lifecycle',\n",
    "                        'total_customers','new_customers','units','ops','cogs'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def make_prep_for_lifecycles(df, dimension1, dimension2):\n",
    "    \"\"\"function makes key preparations\"\"\"\n",
    "    \n",
    "    df = df.dropna()\n",
    "    df.is_copy = False #to kill warning message about setting a copy\n",
    "    \n",
    "    df[dimension1] = pd.to_datetime(df[dimension1])\n",
    "    df[dimension2] = pd.to_datetime(df[dimension2])\n",
    "    df['easy_lifecycles'] = np.where(df['lifecycle'].isin(['New Release','Recent release']),'RR','Catalogue')\n",
    "    \n",
    "    df = df[df['content'].isin(['Season'])]\n",
    "    df = df[df[dimension1] < '2018-06-01']\n",
    "    \n",
    "    summed = pd.DataFrame(df.groupby([dimension1,'easy_lifecycles'])['ops'].sum().reset_index())\n",
    "    pivoted = pd.DataFrame(pd.pivot_table(summed, values='ops', columns='easy_lifecycles', index=dimension1).reset_index())\n",
    "    pivoted = pivoted.fillna(0)\n",
    "    return pivoted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>promo_period</th>\n",
       "      <th>week_ending</th>\n",
       "      <th>transaction_date</th>\n",
       "      <th>promo_status</th>\n",
       "      <th>content</th>\n",
       "      <th>lifecycle</th>\n",
       "      <th>total_customers</th>\n",
       "      <th>new_customers</th>\n",
       "      <th>units</th>\n",
       "      <th>ops</th>\n",
       "      <th>cogs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>non_promo_period</td>\n",
       "      <td>2013-09-07 00:00:00</td>\n",
       "      <td>2013-09-05</td>\n",
       "      <td>non_promo_asins</td>\n",
       "      <td>Movie</td>\n",
       "      <td>New Release</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7.99</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>non_promo_period</td>\n",
       "      <td>2013-09-07 00:00:00</td>\n",
       "      <td>2013-09-06</td>\n",
       "      <td>non_promo_asins</td>\n",
       "      <td>Movie</td>\n",
       "      <td>6mo_1yr_Catalogue</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>11.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>non_promo_period</td>\n",
       "      <td>2013-09-14 00:00:00</td>\n",
       "      <td>2013-09-09</td>\n",
       "      <td>non_promo_asins</td>\n",
       "      <td>Movie</td>\n",
       "      <td>6mo_1yr_Catalogue</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>11.99</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>non_promo_period</td>\n",
       "      <td>2013-09-28 00:00:00</td>\n",
       "      <td>2013-09-24</td>\n",
       "      <td>non_promo_asins</td>\n",
       "      <td>Movie</td>\n",
       "      <td>Deep Catalogue</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>11.99</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>non_promo_period</td>\n",
       "      <td>2013-10-26 00:00:00</td>\n",
       "      <td>2013-10-22</td>\n",
       "      <td>non_promo_asins</td>\n",
       "      <td>Movie</td>\n",
       "      <td>6mo_1yr_Catalogue</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>15.12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       promo_period          week_ending transaction_date     promo_status  \\\n",
       "0  non_promo_period  2013-09-07 00:00:00       2013-09-05  non_promo_asins   \n",
       "1  non_promo_period  2013-09-07 00:00:00       2013-09-06  non_promo_asins   \n",
       "2  non_promo_period  2013-09-14 00:00:00       2013-09-09  non_promo_asins   \n",
       "3  non_promo_period  2013-09-28 00:00:00       2013-09-24  non_promo_asins   \n",
       "4  non_promo_period  2013-10-26 00:00:00       2013-10-22  non_promo_asins   \n",
       "\n",
       "  content          lifecycle  total_customers  new_customers  units    ops  \\\n",
       "0   Movie        New Release                1              1      1   7.99   \n",
       "1   Movie  6mo_1yr_Catalogue                1              0      1  11.00   \n",
       "2   Movie  6mo_1yr_Catalogue                1              1      1  11.99   \n",
       "3   Movie     Deep Catalogue                1              0      1  11.99   \n",
       "4   Movie  6mo_1yr_Catalogue                1              1      2  15.12   \n",
       "\n",
       "   cogs  \n",
       "0   NaN  \n",
       "1   NaN  \n",
       "2   NaN  \n",
       "3   NaN  \n",
       "4   NaN  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_data_daily.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def prep_seasonality_monthly(df):\n",
    "    \"\"\"prepares seasonality adjustments and calls another function\"\"\"\n",
    "    \n",
    "    prep = make_prep_for_lifecycles(df,\"transaction_date\",\"transaction_date\")\n",
    "    \n",
    "    prep['total'] = prep['Catalogue'] + prep['RR']\n",
    "    prep['month'] = prep['transaction_date'].dt.month\n",
    "    prep['year']  = prep['transaction_date'].dt.year\n",
    "    #prep['mnth_yr'] = prep['transaction_date'].apply(lambda x: x.strftime('%B-%Y'))   \n",
    "    \n",
    "    t = pd.DataFrame(prep.groupby(['month','year'])['Catalogue','RR','total'].sum().reset_index())\n",
    "    t = t[t['year'] >= 2015]\n",
    "    \n",
    "    t = t.sort_values(['year','month'], ascending = [True,True]).reset_index()\n",
    "    t = t.drop('index', axis = 1)\n",
    "    \n",
    "    t['total_new'] = np.where((t['year'] == 2017) & (t['month'] ==7), t[(t['year'] == 2017)\n",
    "                                        & (t['month'] < 7 )]['total'].mean(), t['total'])\n",
    "    t['RR_new'] = np.where((t['year'] == 2017) & (t['month'] ==7), t[(t['year'] == 2017)\n",
    "                                        & (t['month'] < 7)]['RR'].mean(), t['RR'])\n",
    "    t['Catalogue_new'] = np.where((t['year'] == 2017) & (t['month'] ==7), t[(t['year'] == 2017)\n",
    "                                        & (t['month'] < 7)]['Catalogue'].mean(), t['Catalogue'])\n",
    "\n",
    "    \n",
    "    return t"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def combine_seasonality():\n",
    "    \"\"\"function provides for every month a seasonality factor\"\"\"\n",
    "    \n",
    "    def get_shape(df,dimension):  \n",
    "        monthly_mean = df[df['year'] >= 2017].groupby(\"month\")[dimension].mean()\n",
    "        all_mean = df[df['year'] >= 2017][dimension].mean()\n",
    "        l = pd.DataFrame({\"monthly_mean\":monthly_mean,\"all_mean\":all_mean}).reset_index()\n",
    "        l['factor'] = l['monthly_mean'] / l['all_mean']\n",
    "        l = l.drop(['all_mean',\"monthly_mean\"], axis = 1)\n",
    "        l.columns = ['month', dimension]\n",
    "        return l\n",
    "    \n",
    "    l1 = get_shape(prep_seasonality_monthly(new_data_daily),\"total_new\")\n",
    "    l2 = get_shape(prep_seasonality_monthly(new_data_daily),\"RR_new\")\n",
    "    l3 = get_shape(prep_seasonality_monthly(new_data_daily),\"Catalogue_new\")\n",
    "    \n",
    "    g1 = pd.merge(left=l1,right=l2,left_on=\"month\",right_on=\"month\",how='left')\n",
    "    g2 = pd.merge(left=g1,right=l3,left_on=\"month\",right_on=\"month\", how = \"left\")\n",
    "    \n",
    "    g2.columns = ['month','total','RR','Catalogue']\n",
    "    return g2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sindex = combine_seasonality()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>month</th>\n",
       "      <th>total</th>\n",
       "      <th>RR</th>\n",
       "      <th>Catalogue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.129071</td>\n",
       "      <td>1.068263</td>\n",
       "      <td>1.165034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>0.967600</td>\n",
       "      <td>1.006449</td>\n",
       "      <td>0.944624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>0.954687</td>\n",
       "      <td>0.898981</td>\n",
       "      <td>0.987632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>0.882711</td>\n",
       "      <td>0.828018</td>\n",
       "      <td>0.915057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>0.894475</td>\n",
       "      <td>0.825964</td>\n",
       "      <td>0.934993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>0.799144</td>\n",
       "      <td>0.879082</td>\n",
       "      <td>0.751869</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>0.796890</td>\n",
       "      <td>0.777546</td>\n",
       "      <td>0.808330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>1.265205</td>\n",
       "      <td>1.408715</td>\n",
       "      <td>1.180332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>1.155634</td>\n",
       "      <td>1.227559</td>\n",
       "      <td>1.113097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>1.162811</td>\n",
       "      <td>1.258844</td>\n",
       "      <td>1.106017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>1.013382</td>\n",
       "      <td>1.015930</td>\n",
       "      <td>1.011876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>1.149844</td>\n",
       "      <td>1.176974</td>\n",
       "      <td>1.133800</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    month     total        RR  Catalogue\n",
       "0       1  1.129071  1.068263   1.165034\n",
       "1       2  0.967600  1.006449   0.944624\n",
       "2       3  0.954687  0.898981   0.987632\n",
       "3       4  0.882711  0.828018   0.915057\n",
       "4       5  0.894475  0.825964   0.934993\n",
       "5       6  0.799144  0.879082   0.751869\n",
       "6       7  0.796890  0.777546   0.808330\n",
       "7       8  1.265205  1.408715   1.180332\n",
       "8       9  1.155634  1.227559   1.113097\n",
       "9      10  1.162811  1.258844   1.106017\n",
       "10     11  1.013382  1.015930   1.011876\n",
       "11     12  1.149844  1.176974   1.133800"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sindex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_melt(df):\n",
    "    \"\"\"function transforms the format from wide to long\"\"\"\n",
    "    t = pd.melt(sindex, id_vars=['month'])\n",
    "    t.columns = ['month','easy_lifecycles','factor']\n",
    "    return t"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sindex_melt = get_melt(sindex)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>month</th>\n",
       "      <th>easy_lifecycles</th>\n",
       "      <th>factor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>total</td>\n",
       "      <td>1.129071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>total</td>\n",
       "      <td>0.967600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>total</td>\n",
       "      <td>0.954687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>total</td>\n",
       "      <td>0.882711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>total</td>\n",
       "      <td>0.894475</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   month easy_lifecycles    factor\n",
       "0      1           total  1.129071\n",
       "1      2           total  0.967600\n",
       "2      3           total  0.954687\n",
       "3      4           total  0.882711\n",
       "4      5           total  0.894475"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sindex_melt.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Get ASINs in promo at these price points in the non promo period"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_asin_at_price_points(df):\n",
    "    \"\"\"function selects asins that had certain price points\n",
    "    and selects from pre period the same ones to make proper comparison\"\"\"\n",
    "    asins_in_the_list = df[(df['is_promo'] == 'promo_period')\n",
    "                       & (df['is_promo_asin'] == 'Promo_Asins')\n",
    "                       & df['display_price'].isin([4.99,9.99,14.99])][['asin','display_price']]\n",
    "    \n",
    "    \n",
    "    asins_in_shape = asins_in_the_list.groupby(\"asin\")['display_price'].min().reset_index()\n",
    "    \n",
    "    #To make proper comparison get an array of those above in the non promo period\n",
    "    asins_in_non_promo = pd.DataFrame(df[(df['is_promo'] == 'non_promo_period')\n",
    "                            & (df['asin'].isin(asins_in_shape['asin']))]['asin'].unique(), columns = ['asin'])\n",
    "    \n",
    "    \n",
    "    #Subset from all only those that are also in non promo\n",
    "    final_list = pd.merge(left = asins_in_non_promo, right=asins_in_shape,left_on=\"asin\",right_on=\"asin\",how=\"left\")\n",
    "    \n",
    "    \n",
    "    new_df = df[df['asin'].isin(final_list['asin'])]\n",
    "    \n",
    "    new_new = pd.merge(left=new_df,right=final_list,left_on=\"asin\",right_on=\"asin\",how='left')\n",
    "    new_new.rename(columns = {\"display_price_x\":\"original_price\",\"display_price_y\":\"price_in_promo\"}, inplace=True)\n",
    "    return new_new\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(56729, 23)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df = get_asin_at_price_points(df)\n",
    "new_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 2 - Critical - get same number of asins in promo and non promo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2474\n",
      "2474\n"
     ]
    }
   ],
   "source": [
    "print(new_df[(new_df['is_promo'] == \"non_promo_period\")]['asin'].nunique())\n",
    "print(new_df[(new_df['is_promo'] == \"promo_period\")]['asin'].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Revenue from promo period : 2240613.049999834\n",
      "Period contained: ['non_promo_period' 'promo_period']\n",
      "Price points: [  4.99  14.99   9.99]\n"
     ]
    }
   ],
   "source": [
    "print(\"Revenue from promo period :\", new_df[new_df['is_promo'] == 'promo_period']['ops'].sum())\n",
    "print(\"Period contained:\", new_df['is_promo'].unique())\n",
    "print(\"Price points:\", new_df['price_in_promo'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Apply seasonality factors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def seasonality_factors(df1,df2):\n",
    "    \"\"\"function merges seasonality factors and the output from get_asin_at_price_points\"\"\"\n",
    "    output = pd.merge(left=df1,right=df2,\n",
    "                      left_on=['month','easy_lifecycles'],\n",
    "                      right_on=['month','easy_lifecycles'],how='left')\n",
    "    \n",
    "    return output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(56729, 24)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df_seas = seasonality_factors(new_df,sindex_melt)\n",
    "new_df_seas.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check the share of cumulative price points in the total Promo OPS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def prep_dataset_dimension(df, dimension = None):\n",
    "    \"\"\"adds complexity to the analysis in the dimension using optional argument\"\"\"\n",
    "    \n",
    "    #Notice: df will be updated here so export after the function will contain both of these columns\n",
    "    df['ops_deseas'] = df['ops'] / df['factor']\n",
    "    df['units_deseas'] = df['units'] / df['factor']\n",
    "    df['new_customers_deseas'] = df['new_customers'] / df['factor']\n",
    "        \n",
    "    #use optional agrument\n",
    "    \n",
    "#     if dimension is None:\n",
    "#         a = pd.DataFrame(df.groupby([\"is_promo\",\"asin\"])['week_ending'].size().reset_index())\n",
    "#     else: \n",
    "#         a = pd.DataFrame(df.groupby([\"is_promo\",\"asin\", dimension])['week_ending'].size().reset_index())\n",
    "#     key_metrics = pd.DataFrame(df.groupby([\"is_promo\",\"asin\"])['ops','ops_deseas',\n",
    "#                                                                'units','units_deseas'].sum().reset_index())\n",
    "    \n",
    "#     m = pd.merge(left=key_metrics, right=a, left_on=['is_promo','asin'], right_on=['is_promo','asin'], how = 'left')\n",
    "#     m['ops_per_week'] = m['ops'] / m['week_ending']\n",
    "#     m['ops_deseas_per_week'] = m['ops_deseas'] / m['week_ending']\n",
    "    \n",
    "#     m['units_per_week'] = m['units'] / m['week_ending']\n",
    "#     m['units_deseas_per_week'] = m['units_deseas'] / m['week_ending']\n",
    "    \n",
    "    \n",
    "    \n",
    "    \n",
    "    before = pd.DataFrame(df[df['is_promo']=='non_promo_period'].\n",
    "                          groupby([\"asin\"])[\"original_price\"].mean().reset_index())\n",
    "    \n",
    "       \n",
    "    key_out = pd.merge(left=df,right=before,left_on=['asin'],\n",
    "                       right_on=['asin'],how=\"left\")\n",
    "    \n",
    "    key_out.rename(columns={\"original_price_x\":\"original_price\",\"original_price_y\":\"mean_non_promo_price\"}, inplace = True)\n",
    "\n",
    "    return key_out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(56729, 28)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df_seas_wbefore = prep_dataset_dimension(new_df_seas)\n",
    "new_df_seas_wbefore.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['week_ending', 'is_promo', 'asin', 'is_promo_asin', 'content',\n",
       "       'title_name', 'studio_name', 'contract_id', 'release_date', 'is_hd',\n",
       "       'original_price', 'season_number', 'content_age', 'customers',\n",
       "       'new_customers', 'units', 'ops', 'cogs', 'ppm', 'month',\n",
       "       'lifecycles_last', 'easy_lifecycles', 'price_in_promo', 'factor',\n",
       "       'ops_deseas', 'units_deseas', 'new_customers_deseas',\n",
       "       'mean_non_promo_price'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df_seas_wbefore.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2541510.016666679"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df_seas_wbefore[(new_df_seas_wbefore['week_ending'] >= '2018-01-27')\n",
    "                    & (new_df_seas_wbefore['week_ending'] <= '2018-03-03')]['ops'].sum()/6*10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2240613.049999834"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df_seas_wbefore[(new_df_seas_wbefore['is_promo'] == 'promo_period')]['ops'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def glue_together(df):\n",
    "    df1 = df[(df['week_ending'] >= '2018-01-27')\n",
    "                          & (df['week_ending'] <= '2018-03-03')]\n",
    "    df2 = df[(df['is_promo'] == 'promo_period')]\n",
    "\n",
    "    frames = [df1,df2]\n",
    "    gluon = pd.concat(frames)\n",
    "    return gluon"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "gluon = glue_together(new_df_seas_wbefore)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['week_ending', 'is_promo', 'asin', 'is_promo_asin', 'content',\n",
       "       'title_name', 'studio_name', 'contract_id', 'release_date', 'is_hd',\n",
       "       'original_price', 'season_number', 'content_age', 'customers',\n",
       "       'new_customers', 'units', 'ops', 'cogs', 'ppm', 'month',\n",
       "       'lifecycles_last', 'easy_lifecycles', 'price_in_promo', 'factor',\n",
       "       'ops_deseas', 'units_deseas', 'new_customers_deseas',\n",
       "       'mean_non_promo_price'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gluon.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "factor = 5/3\n",
    "\n",
    "def get_output_dimension(df,dimension1 = None, dimension2 = None):\n",
    "    \"\"\"make first output with 2 optional arguments\"\"\"\n",
    "    if dimension1 is None and dimension2 is None :\n",
    "        get = df.groupby([\"is_promo\"])['ops','ops_deseas','units','units_deseas',\n",
    "                                       'new_customers','new_customers_deseas'].sum().reset_index()\n",
    "    \n",
    "    elif dimension1 is not None and dimension2 is None:\n",
    "        get = df.groupby([\"is_promo\", dimension1])['ops','ops_deseas',\n",
    "                                                  'units','units_deseas','new_customers',\n",
    "                                                  'new_customers_deseas'].sum().reset_index()\n",
    "    \n",
    "    else:\n",
    "        get = df.groupby([\"is_promo\", dimension1, dimension2])['ops','ops_deseas',\n",
    "                                                  'units','units_deseas','new_customers','new_customers_deseas'].sum().reset_index()\n",
    "  \n",
    "    \n",
    "    \n",
    "    get.set_index(\"is_promo\", inplace=True)\n",
    "       \n",
    "    get.loc[\"non_promo_period\",['ops',\"ops_deseas\",\"units\",\"units_deseas\",\"new_customers\",'new_customers_deseas']] *= factor \n",
    "    \n",
    "    get = get.reset_index()\n",
    "    return get        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "get = get_output_dimension(gluon)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>is_promo</th>\n",
       "      <th>ops</th>\n",
       "      <th>ops_deseas</th>\n",
       "      <th>units</th>\n",
       "      <th>units_deseas</th>\n",
       "      <th>new_customers</th>\n",
       "      <th>new_customers_deseas</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>non_promo_period</td>\n",
       "      <td>2.541510e+06</td>\n",
       "      <td>2.582707e+06</td>\n",
       "      <td>177848.333333</td>\n",
       "      <td>180290.878673</td>\n",
       "      <td>8536.666667</td>\n",
       "      <td>8633.154470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>promo_period</td>\n",
       "      <td>2.240613e+06</td>\n",
       "      <td>2.372924e+06</td>\n",
       "      <td>371864.000000</td>\n",
       "      <td>393151.050018</td>\n",
       "      <td>14919.000000</td>\n",
       "      <td>15753.686036</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           is_promo           ops    ops_deseas          units   units_deseas  \\\n",
       "0  non_promo_period  2.541510e+06  2.582707e+06  177848.333333  180290.878673   \n",
       "1      promo_period  2.240613e+06  2.372924e+06  371864.000000  393151.050018   \n",
       "\n",
       "   new_customers  new_customers_deseas  \n",
       "0    8536.666667           8633.154470  \n",
       "1   14919.000000          15753.686036  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2474"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gluon['asin'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "price_in_promo\n",
       "4.99     1730\n",
       "9.99      605\n",
       "14.99     139\n",
       "Name: asin, dtype: int64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gluon.groupby(\"price_in_promo\")['asin'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ggg = gluon.groupby(['price_in_promo','season_number'])['asin'].nunique().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>price_in_promo</th>\n",
       "      <th>season_number</th>\n",
       "      <th>asin</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4.99</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4.99</td>\n",
       "      <td>1.0</td>\n",
       "      <td>573</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.99</td>\n",
       "      <td>2.0</td>\n",
       "      <td>295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.99</td>\n",
       "      <td>3.0</td>\n",
       "      <td>203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4.99</td>\n",
       "      <td>4.0</td>\n",
       "      <td>135</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   price_in_promo  season_number  asin\n",
       "0            4.99            0.0     2\n",
       "1            4.99            1.0   573\n",
       "2            4.99            2.0   295\n",
       "3            4.99            3.0   203\n",
       "4            4.99            4.0   135"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ggg.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_spread(df, price_point, dimension=None, subdimension=None):\n",
    "    spread = df[df[\"is_promo\"] == \"promo_period\"][['asin','ops_deseas',\n",
    "                                                     'price_in_promo','lifecycles_last',\"season_number\"]]\n",
    "    \n",
    "    if dimension is not None and subdimension is not None:\n",
    "        c = spread[(spread['price_in_promo'] == price_point)\n",
    "               & (spread[dimension] > subdimension)].groupby(['asin',\n",
    "                                    dimension])['ops_deseas'].sum().reset_index()  \n",
    "        c = c.sort_values(by=\"ops_deseas\", ascending = False)\n",
    "        c['share']  = c['ops_deseas'] / c['ops_deseas'].sum()\n",
    "        c['cumsum'] = c['share'].cumsum()\n",
    "    else:\n",
    "        return spread\n",
    "    return c\n",
    "                \n",
    "        \n",
    "   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total shape:  (34, 5)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(4, 5)"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d = get_spread(gluon, 14.99, \"season_number\",5)\n",
    "print(\"total shape: \", d.shape)\n",
    "d[d['cumsum'] < 0.8].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(34, 5)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>asin</th>\n",
       "      <th>season_number</th>\n",
       "      <th>ops_deseas</th>\n",
       "      <th>share</th>\n",
       "      <th>cumsum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>B06XWMQ6KH</td>\n",
       "      <td>8.0</td>\n",
       "      <td>48890.012604</td>\n",
       "      <td>0.449696</td>\n",
       "      <td>0.449696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>B01MYDU1F2</td>\n",
       "      <td>6.0</td>\n",
       "      <td>24604.206026</td>\n",
       "      <td>0.226312</td>\n",
       "      <td>0.676008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>B071NNSZJT</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5653.575551</td>\n",
       "      <td>0.052002</td>\n",
       "      <td>0.728010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>B06XWMF555</td>\n",
       "      <td>8.0</td>\n",
       "      <td>5211.436830</td>\n",
       "      <td>0.047935</td>\n",
       "      <td>0.775946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>B01MUG8C4G</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4007.835598</td>\n",
       "      <td>0.036865</td>\n",
       "      <td>0.812810</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          asin  season_number    ops_deseas     share    cumsum\n",
       "22  B06XWMQ6KH            8.0  48890.012604  0.449696  0.449696\n",
       "18  B01MYDU1F2            6.0  24604.206026  0.226312  0.676008\n",
       "23  B071NNSZJT            6.0   5653.575551  0.052002  0.728010\n",
       "21  B06XWMF555            8.0   5211.436830  0.047935  0.775946\n",
       "17  B01MUG8C4G            6.0   4007.835598  0.036865  0.812810"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(d.shape)\n",
    "d.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Look at Non Promo ASINs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(244190, 22)\n"
     ]
    }
   ],
   "source": [
    "print(df.shape)\n",
    "overall_df = seasonality_factors(df[df['is_promo_asin'] == 'non_promo_asins'],sindex_melt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(186476, 23)"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "overall_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>week_ending</th>\n",
       "      <th>is_promo</th>\n",
       "      <th>asin</th>\n",
       "      <th>is_promo_asin</th>\n",
       "      <th>content</th>\n",
       "      <th>title_name</th>\n",
       "      <th>studio_name</th>\n",
       "      <th>contract_id</th>\n",
       "      <th>release_date</th>\n",
       "      <th>is_hd</th>\n",
       "      <th>...</th>\n",
       "      <th>customers</th>\n",
       "      <th>new_customers</th>\n",
       "      <th>units</th>\n",
       "      <th>ops</th>\n",
       "      <th>cogs</th>\n",
       "      <th>ppm</th>\n",
       "      <th>month</th>\n",
       "      <th>lifecycles_last</th>\n",
       "      <th>easy_lifecycles</th>\n",
       "      <th>factor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-11-04</td>\n",
       "      <td>non_promo_period</td>\n",
       "      <td>B00HXG8PQK</td>\n",
       "      <td>non_promo_asins</td>\n",
       "      <td>Season</td>\n",
       "      <td>Yakari - Staffel 2</td>\n",
       "      <td>Ki.Ka, ZDF, ORF</td>\n",
       "      <td>UXKN4</td>\n",
       "      <td>2010-07-05</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>34</td>\n",
       "      <td>1</td>\n",
       "      <td>34</td>\n",
       "      <td>276.82</td>\n",
       "      <td>185.86</td>\n",
       "      <td>0.3285</td>\n",
       "      <td>11</td>\n",
       "      <td>Deep Catalogue</td>\n",
       "      <td>Catalogue</td>\n",
       "      <td>1.011876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-11-04</td>\n",
       "      <td>non_promo_period</td>\n",
       "      <td>B00QX1DHFU</td>\n",
       "      <td>non_promo_asins</td>\n",
       "      <td>Season</td>\n",
       "      <td>Phineas und Ferb Staffel 1</td>\n",
       "      <td>Disney Channel</td>\n",
       "      <td>UXDY4</td>\n",
       "      <td>2014-02-26</td>\n",
       "      <td>Y</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>60.37</td>\n",
       "      <td>42.34</td>\n",
       "      <td>0.2986</td>\n",
       "      <td>11</td>\n",
       "      <td>Deep Catalogue</td>\n",
       "      <td>Catalogue</td>\n",
       "      <td>1.011876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-11-04</td>\n",
       "      <td>non_promo_period</td>\n",
       "      <td>B0757S91QL</td>\n",
       "      <td>non_promo_asins</td>\n",
       "      <td>Season</td>\n",
       "      <td>Looney Tunes: Best Of Tweety und Sylvester, Vo...</td>\n",
       "      <td>Warner Bros.</td>\n",
       "      <td>UXWB4</td>\n",
       "      <td>2017-04-10</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>37.80</td>\n",
       "      <td>22.92</td>\n",
       "      <td>0.3936</td>\n",
       "      <td>11</td>\n",
       "      <td>1_2yr_Catalogue</td>\n",
       "      <td>Catalogue</td>\n",
       "      <td>1.011876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-11-04</td>\n",
       "      <td>non_promo_period</td>\n",
       "      <td>B00SQRNGO6</td>\n",
       "      <td>non_promo_asins</td>\n",
       "      <td>Season</td>\n",
       "      <td>Melissa and Joey - Staffel 4</td>\n",
       "      <td>ABC Family</td>\n",
       "      <td>UXDY4</td>\n",
       "      <td>2015-11-01</td>\n",
       "      <td>Y</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>77.96</td>\n",
       "      <td>54.68</td>\n",
       "      <td>0.2986</td>\n",
       "      <td>11</td>\n",
       "      <td>2_3yr_Catalogue</td>\n",
       "      <td>Catalogue</td>\n",
       "      <td>1.011876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-11-04</td>\n",
       "      <td>non_promo_period</td>\n",
       "      <td>B01DE1ZHRM</td>\n",
       "      <td>non_promo_asins</td>\n",
       "      <td>Season</td>\n",
       "      <td>Die Ostküste der USA</td>\n",
       "      <td>VIDICOM Media</td>\n",
       "      <td>UXSD4</td>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>Y</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>8.36</td>\n",
       "      <td>4.16</td>\n",
       "      <td>0.5023</td>\n",
       "      <td>11</td>\n",
       "      <td>2_3yr_Catalogue</td>\n",
       "      <td>Catalogue</td>\n",
       "      <td>1.011876</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  week_ending          is_promo        asin    is_promo_asin content  \\\n",
       "0  2017-11-04  non_promo_period  B00HXG8PQK  non_promo_asins  Season   \n",
       "1  2017-11-04  non_promo_period  B00QX1DHFU  non_promo_asins  Season   \n",
       "2  2017-11-04  non_promo_period  B0757S91QL  non_promo_asins  Season   \n",
       "3  2017-11-04  non_promo_period  B00SQRNGO6  non_promo_asins  Season   \n",
       "4  2017-11-04  non_promo_period  B01DE1ZHRM  non_promo_asins  Season   \n",
       "\n",
       "                                          title_name      studio_name  \\\n",
       "0                                 Yakari - Staffel 2  Ki.Ka, ZDF, ORF   \n",
       "1                         Phineas und Ferb Staffel 1   Disney Channel   \n",
       "2  Looney Tunes: Best Of Tweety und Sylvester, Vo...     Warner Bros.   \n",
       "3                       Melissa and Joey - Staffel 4       ABC Family   \n",
       "4                               Die Ostküste der USA    VIDICOM Media   \n",
       "\n",
       "  contract_id release_date is_hd    ...     customers  new_customers units  \\\n",
       "0       UXKN4   2010-07-05     N    ...            34              1    34   \n",
       "1       UXDY4   2014-02-26     Y    ...             5              1     5   \n",
       "2       UXWB4   2017-04-10     N    ...             3              0     3   \n",
       "3       UXDY4   2015-11-01     Y    ...             4              0     4   \n",
       "4       UXSD4   2016-03-01     Y    ...             1              0     1   \n",
       "\n",
       "      ops    cogs     ppm  month  lifecycles_last  easy_lifecycles    factor  \n",
       "0  276.82  185.86  0.3285     11   Deep Catalogue        Catalogue  1.011876  \n",
       "1   60.37   42.34  0.2986     11   Deep Catalogue        Catalogue  1.011876  \n",
       "2   37.80   22.92  0.3936     11  1_2yr_Catalogue        Catalogue  1.011876  \n",
       "3   77.96   54.68  0.2986     11  2_3yr_Catalogue        Catalogue  1.011876  \n",
       "4    8.36    4.16  0.5023     11  2_3yr_Catalogue        Catalogue  1.011876  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "overall_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9015262.909999695"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "overall_df[(overall_df['is_promo_asin'] == \"non_promo_asins\") & (overall_df['is_promo'] == \"promo_period\")]['ops'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def deseasonalize(df):\n",
    "    \n",
    "    df['ops_deseas'] = df['ops'] / df['factor']\n",
    "    df['units_deseas'] = df['units'] / df['factor']\n",
    "    df['new_customers_deseas'] = df['new_customers'] / df['factor']\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "overall_df = deseasonalize(overall_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "get_non_promo = glue_together(overall_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>is_promo</th>\n",
       "      <th>ops</th>\n",
       "      <th>ops_deseas</th>\n",
       "      <th>units</th>\n",
       "      <th>units_deseas</th>\n",
       "      <th>new_customers</th>\n",
       "      <th>new_customers_deseas</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>non_promo_period</td>\n",
       "      <td>10226861.85</td>\n",
       "      <td>1.037568e+07</td>\n",
       "      <td>693183.333333</td>\n",
       "      <td>703202.365075</td>\n",
       "      <td>29403.333333</td>\n",
       "      <td>29798.540281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>promo_period</td>\n",
       "      <td>9015262.91</td>\n",
       "      <td>9.947884e+06</td>\n",
       "      <td>619315.000000</td>\n",
       "      <td>676206.234255</td>\n",
       "      <td>24452.000000</td>\n",
       "      <td>26487.285963</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           is_promo          ops    ops_deseas          units   units_deseas  \\\n",
       "0  non_promo_period  10226861.85  1.037568e+07  693183.333333  703202.365075   \n",
       "1      promo_period   9015262.91  9.947884e+06  619315.000000  676206.234255   \n",
       "\n",
       "   new_customers  new_customers_deseas  \n",
       "0   29403.333333          29798.540281  \n",
       "1   24452.000000          26487.285963  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get2 = get_output_dimension(get_non_promo)\n",
    "get2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}