W03.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>Date</th>\n",
       "      <th>SKU</th>\n",
       "      <th>click</th>\n",
       "      <th>add_cart</th>\n",
       "      <th>add_favorites</th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20190728</td>\n",
       "      <td>VF256715</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20190728</td>\n",
       "      <td>VF224834</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20190728</td>\n",
       "      <td>PJ955955</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20190728</td>\n",
       "      <td>SN639704</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20190728</td>\n",
       "      <td>VF237717</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Date       SKU  click  add_cart  add_favorites  sales\n",
       "0  20190728  VF256715      7         0              0      0\n",
       "1  20190728  VF224834     28         0              0      0\n",
       "2  20190728  PJ955955     22         1              0      0\n",
       "3  20190728  SN639704     17         0              0      0\n",
       "4  20190728  VF237717      2         0              0      0"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Import the file \"W03_eCommerce.csv\" as pandas Dataframe. Call it df\n",
    "df = pd.read_csv(\"W03_eCommerce.csv\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "275265"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How many rows does the dataset have? Store this number as r1\n",
    "r1 = df.shape[0]\n",
    "r1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "28\n"
     ]
    }
   ],
   "source": [
    "# How often does SKU \"NH430971\" appear in the data? Store this as r2  \n",
    "r2= df['SKU'].value_counts()[\"NH430971\"]\n",
    "print(r2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "319 The SKU -TR332356- had the most sales on the 26.06.2019 with 319 sales\n"
     ]
    }
   ],
   "source": [
    "# What is the highest sales number for one SKU on a single day in the data set? Store this as r3\n",
    "maxValues= df.max()\n",
    "r3=df.loc[df['sales'] == 319]\n",
    "r3= r3.iloc[0, -1]\n",
    "#print(maxValues)\n",
    "print(r3, \"The SKU -TR332356- had the most sales on the 26.06.2019 with 319 sales\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5749"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# What is the total number of sales on July 25th, 2019? Store this as r4\n",
    "df4= df[df[\"Date\"]==20190725]\n",
    "count=0\n",
    "\n",
    "for i in df4[\"sales\"]:\n",
    "    count=i+count\n",
    "    \n",
    "r4=count\n",
    "r4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2106"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How many clicks did SKU \"KW529031\" have in total in the given period. Store this as r5\n",
    "df5=df[df[\"SKU\"]==\"KW529031\"]\n",
    "r5= df5[\"click\"].sum()\n",
    "r5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.08262108262108261"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate the total number of addings to cart for \"KW529031\" in the given period.\n",
    "# Divide this number by r5. We call this click-to-cart-conversion. Store this as r6 (number between 0 and 1)\n",
    "\n",
    "df6=df[df[\"SKU\"]==\"KW529031\"]\n",
    "count=0\n",
    "for i in df6[\"add_cart\"]:\n",
    "    count=i+count\n",
    "count\n",
    "r6=count/r5\n",
    "r6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.24722991689750692"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Store the cart-to-sales-conversion (i.e. sales/add_cart) for SKU \"VF226119\" as r7\n",
    "d3 = df[df['SKU']==\"VF226119\"]\n",
    "r7 = d3['sales'].sum()/d3['add_cart'].sum()\n",
    "r7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'TR332356'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Which item had the highest sales number on June 25th, 2019? Store the ID of the SKU as r8 (not the sales value)\n",
    "d4 = df[df['Date']==20190625]\n",
    "d4_1 = d4[d4['sales'] == d4['sales'].max()]\n",
    "r8 = d4_1.iloc[0, 1]\n",
    "r8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.11538461538461539"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a new column for 'click-to-sales-conversion' (i.e. sales / click )\n",
    "# Only look at rows with at least 100 clicks.\n",
    "# Among those: What is the highest click-sales-ratio on a single day for a single item?\n",
    "# Store this number as r9  \n",
    "\n",
    "df2 = df.copy()\n",
    "df2['click-to-sales-conversion'] = df2['sales'] / df2['click']\n",
    "df2 = df2[df2['click']>100]\n",
    "#print(df2.head())\n",
    "df2.sort_values('click-to-sales-conversion', ascending=False, inplace=True)\n",
    "df2.reset_index(inplace=True)\n",
    "#print(df2.head())\n",
    "r9 = df2['click-to-sales-conversion'][0]\n",
    "r9"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7382"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How many unique SKUs are included in the dataset? Store this number as r10   \n",
    "r10 = len(df['SKU'].unique())\n",
    "r10"
   ]
  }
 ],
 "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}