{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Performing Grouped Aggregations\n",
    "\n",
    "Let us understand how to perform grouped or by key aggregations using Pandas.\n",
    "* Here are the steps we need to follow:\n",
    "  * Make sure data is read into Data Frame.\n",
    "  * Identify the key on which data should be aggregated. If the data has to be aggregated on derived field which is not available as part of the Data Frame, then first we need to update data frame with the derived field.\n",
    "  * Using the key group the values using `groupby` function on data frame. We can only pass column names from Data Frame as part of `groupby`.\n",
    "  * Apply required aggregate functions to get aggregated results based up on the key.\n",
    "* We can apply multiple aggregate functions at a time after creating grouped data frame.\n",
    "* Pandas Data Frame exposes a function called as `rename` to provide aliases to the aggregated fields."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "tags": [
     "remove-cell"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/GEn5-9D38FM?rel=0&amp;controls=1&amp;showinfo=0\" frameborder=\"0\" allowfullscreen></iframe>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%HTML\n",
    "<iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/GEn5-9D38FM?rel=0&amp;controls=1&amp;showinfo=0\" frameborder=\"0\" allowfullscreen></iframe>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run 06_csv_to_pandas_data_frame.ipynb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Getting number of orders per day"
   ]
  },
  {
   "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>order_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>order_customer_id</th>\n",
       "      <th>order_status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2013-07-25 00:00:00.0</td>\n",
       "      <td>11599</td>\n",
       "      <td>CLOSED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2013-07-25 00:00:00.0</td>\n",
       "      <td>256</td>\n",
       "      <td>PENDING_PAYMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2013-07-25 00:00:00.0</td>\n",
       "      <td>12111</td>\n",
       "      <td>COMPLETE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2013-07-25 00:00:00.0</td>\n",
       "      <td>8827</td>\n",
       "      <td>CLOSED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2013-07-25 00:00:00.0</td>\n",
       "      <td>11318</td>\n",
       "      <td>COMPLETE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68878</th>\n",
       "      <td>68879</td>\n",
       "      <td>2014-07-09 00:00:00.0</td>\n",
       "      <td>778</td>\n",
       "      <td>COMPLETE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68879</th>\n",
       "      <td>68880</td>\n",
       "      <td>2014-07-13 00:00:00.0</td>\n",
       "      <td>1117</td>\n",
       "      <td>COMPLETE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68880</th>\n",
       "      <td>68881</td>\n",
       "      <td>2014-07-19 00:00:00.0</td>\n",
       "      <td>2518</td>\n",
       "      <td>PENDING_PAYMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68881</th>\n",
       "      <td>68882</td>\n",
       "      <td>2014-07-22 00:00:00.0</td>\n",
       "      <td>10000</td>\n",
       "      <td>ON_HOLD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68882</th>\n",
       "      <td>68883</td>\n",
       "      <td>2014-07-23 00:00:00.0</td>\n",
       "      <td>5533</td>\n",
       "      <td>COMPLETE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>68883 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       order_id             order_date  order_customer_id     order_status\n",
       "0             1  2013-07-25 00:00:00.0              11599           CLOSED\n",
       "1             2  2013-07-25 00:00:00.0                256  PENDING_PAYMENT\n",
       "2             3  2013-07-25 00:00:00.0              12111         COMPLETE\n",
       "3             4  2013-07-25 00:00:00.0               8827           CLOSED\n",
       "4             5  2013-07-25 00:00:00.0              11318         COMPLETE\n",
       "...         ...                    ...                ...              ...\n",
       "68878     68879  2014-07-09 00:00:00.0                778         COMPLETE\n",
       "68879     68880  2014-07-13 00:00:00.0               1117         COMPLETE\n",
       "68880     68881  2014-07-19 00:00:00.0               2518  PENDING_PAYMENT\n",
       "68881     68882  2014-07-22 00:00:00.0              10000          ON_HOLD\n",
       "68882     68883  2014-07-23 00:00:00.0               5533         COMPLETE\n",
       "\n",
       "[68883 rows x 4 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.generic.DataFrameGroupBy object at 0x7f1f811bb828>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.groupby(orders['order_date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('2013-07-25 00:00:00.0',\n",
       "  0            1\n",
       "  1            2\n",
       "  2            3\n",
       "  3            4\n",
       "  4            5\n",
       "           ...  \n",
       "  57786    57787\n",
       "  57787    57788\n",
       "  57788    57789\n",
       "  67415    67416\n",
       "  68690    68691\n",
       "  Name: order_id, Length: 143, dtype: int64),\n",
       " ('2013-07-26 00:00:00.0',\n",
       "  104        105\n",
       "  105        106\n",
       "  106        107\n",
       "  107        108\n",
       "  108        109\n",
       "           ...  \n",
       "  67418    67419\n",
       "  67419    67420\n",
       "  67420    67421\n",
       "  67421    67422\n",
       "  68691    68692\n",
       "  Name: order_id, Length: 269, dtype: int64),\n",
       " ('2013-07-27 00:00:00.0',\n",
       "  346        347\n",
       "  347        348\n",
       "  348        349\n",
       "  349        350\n",
       "  350        351\n",
       "           ...  \n",
       "  67422    67423\n",
       "  67423    67424\n",
       "  67424    67425\n",
       "  67425    67426\n",
       "  68692    68693\n",
       "  Name: order_id, Length: 202, dtype: int64)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(orders.groupby(orders['order_date'])['order_id'])[:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "order_date\n",
       "2013-07-25 00:00:00.0    143\n",
       "2013-07-26 00:00:00.0    269\n",
       "2013-07-27 00:00:00.0    202\n",
       "2013-07-28 00:00:00.0    187\n",
       "2013-07-29 00:00:00.0    253\n",
       "                        ... \n",
       "2014-07-20 00:00:00.0    285\n",
       "2014-07-21 00:00:00.0    235\n",
       "2014-07-22 00:00:00.0    138\n",
       "2014-07-23 00:00:00.0    166\n",
       "2014-07-24 00:00:00.0    185\n",
       "Name: order_id, Length: 364, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.groupby(orders['order_date'])['order_id'].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Getting number of orders per status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "order_status\n",
       "CANCELED            1428\n",
       "CLOSED              7556\n",
       "COMPLETE           22899\n",
       "ON_HOLD             3798\n",
       "PAYMENT_REVIEW       729\n",
       "PENDING             7610\n",
       "PENDING_PAYMENT    15030\n",
       "PROCESSING          8275\n",
       "SUSPECTED_FRAUD     1558\n",
       "Name: order_status, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.groupby('order_status')['order_status'].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Computing revenue per order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_item_id</th>\n",
       "      <th>order_item_order_id</th>\n",
       "      <th>order_item_product_id</th>\n",
       "      <th>order_item_quantity</th>\n",
       "      <th>order_item_subtotal</th>\n",
       "      <th>order_item_product_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>957</td>\n",
       "      <td>1</td>\n",
       "      <td>299.98</td>\n",
       "      <td>299.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1073</td>\n",
       "      <td>1</td>\n",
       "      <td>199.99</td>\n",
       "      <td>199.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>502</td>\n",
       "      <td>5</td>\n",
       "      <td>250.00</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>403</td>\n",
       "      <td>1</td>\n",
       "      <td>129.99</td>\n",
       "      <td>129.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>897</td>\n",
       "      <td>2</td>\n",
       "      <td>49.98</td>\n",
       "      <td>24.99</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172193</th>\n",
       "      <td>172194</td>\n",
       "      <td>68881</td>\n",
       "      <td>403</td>\n",
       "      <td>1</td>\n",
       "      <td>129.99</td>\n",
       "      <td>129.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172194</th>\n",
       "      <td>172195</td>\n",
       "      <td>68882</td>\n",
       "      <td>365</td>\n",
       "      <td>1</td>\n",
       "      <td>59.99</td>\n",
       "      <td>59.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172195</th>\n",
       "      <td>172196</td>\n",
       "      <td>68882</td>\n",
       "      <td>502</td>\n",
       "      <td>1</td>\n",
       "      <td>50.00</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172196</th>\n",
       "      <td>172197</td>\n",
       "      <td>68883</td>\n",
       "      <td>208</td>\n",
       "      <td>1</td>\n",
       "      <td>1999.99</td>\n",
       "      <td>1999.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172197</th>\n",
       "      <td>172198</td>\n",
       "      <td>68883</td>\n",
       "      <td>502</td>\n",
       "      <td>3</td>\n",
       "      <td>150.00</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>172198 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        order_item_id  order_item_order_id  order_item_product_id  \\\n",
       "0                   1                    1                    957   \n",
       "1                   2                    2                   1073   \n",
       "2                   3                    2                    502   \n",
       "3                   4                    2                    403   \n",
       "4                   5                    4                    897   \n",
       "...               ...                  ...                    ...   \n",
       "172193         172194                68881                    403   \n",
       "172194         172195                68882                    365   \n",
       "172195         172196                68882                    502   \n",
       "172196         172197                68883                    208   \n",
       "172197         172198                68883                    502   \n",
       "\n",
       "        order_item_quantity  order_item_subtotal  order_item_product_price  \n",
       "0                         1               299.98                    299.98  \n",
       "1                         1               199.99                    199.99  \n",
       "2                         5               250.00                     50.00  \n",
       "3                         1               129.99                    129.99  \n",
       "4                         2                49.98                     24.99  \n",
       "...                     ...                  ...                       ...  \n",
       "172193                    1               129.99                    129.99  \n",
       "172194                    1                59.99                     59.99  \n",
       "172195                    1                50.00                     50.00  \n",
       "172196                    1              1999.99                   1999.99  \n",
       "172197                    3               150.00                     50.00  \n",
       "\n",
       "[172198 rows x 6 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_items"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1,\n",
       "  0    299.98\n",
       "  Name: order_item_subtotal, dtype: float64),\n",
       " (2,\n",
       "  1    199.99\n",
       "  2    250.00\n",
       "  3    129.99\n",
       "  Name: order_item_subtotal, dtype: float64),\n",
       " (4,\n",
       "  4     49.98\n",
       "  5    299.95\n",
       "  6    150.00\n",
       "  7    199.92\n",
       "  Name: order_item_subtotal, dtype: float64),\n",
       " (5,\n",
       "  8     299.98\n",
       "  9     299.95\n",
       "  10     99.96\n",
       "  11    299.98\n",
       "  12    129.99\n",
       "  Name: order_item_subtotal, dtype: float64),\n",
       " (7,\n",
       "  13    199.99\n",
       "  14    299.98\n",
       "  15     79.95\n",
       "  Name: order_item_subtotal, dtype: float64)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(order_items. \\\n",
    "    groupby('order_item_order_id')['order_item_subtotal'])[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "order_item_order_id\n",
       "1         299.98\n",
       "2         579.98\n",
       "4         699.85\n",
       "5        1129.86\n",
       "7         579.92\n",
       "          ...   \n",
       "68879    1259.97\n",
       "68880     999.77\n",
       "68881     129.99\n",
       "68882     109.99\n",
       "68883    2149.99\n",
       "Name: order_item_subtotal, Length: 57431, dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_items. \\\n",
    "    groupby('order_item_order_id')['order_item_subtotal']. \\\n",
    "    sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>sum</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>order_item_order_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>299.98</td>\n",
       "      <td>299.98</td>\n",
       "      <td>299.98</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>579.98</td>\n",
       "      <td>129.99</td>\n",
       "      <td>250.00</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>699.85</td>\n",
       "      <td>49.98</td>\n",
       "      <td>299.95</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1129.86</td>\n",
       "      <td>99.96</td>\n",
       "      <td>299.98</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>579.92</td>\n",
       "      <td>79.95</td>\n",
       "      <td>299.98</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68879</th>\n",
       "      <td>1259.97</td>\n",
       "      <td>129.99</td>\n",
       "      <td>999.99</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68880</th>\n",
       "      <td>999.77</td>\n",
       "      <td>149.94</td>\n",
       "      <td>250.00</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68881</th>\n",
       "      <td>129.99</td>\n",
       "      <td>129.99</td>\n",
       "      <td>129.99</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68882</th>\n",
       "      <td>109.99</td>\n",
       "      <td>50.00</td>\n",
       "      <td>59.99</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68883</th>\n",
       "      <td>2149.99</td>\n",
       "      <td>150.00</td>\n",
       "      <td>1999.99</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>57431 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         sum     min      max  count\n",
       "order_item_order_id                                 \n",
       "1                     299.98  299.98   299.98      1\n",
       "2                     579.98  129.99   250.00      3\n",
       "4                     699.85   49.98   299.95      4\n",
       "5                    1129.86   99.96   299.98      5\n",
       "7                     579.92   79.95   299.98      3\n",
       "...                      ...     ...      ...    ...\n",
       "68879                1259.97  129.99   999.99      3\n",
       "68880                 999.77  149.94   250.00      5\n",
       "68881                 129.99  129.99   129.99      1\n",
       "68882                 109.99   50.00    59.99      2\n",
       "68883                2149.99  150.00  1999.99      2\n",
       "\n",
       "[57431 rows x 4 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_items. \\\n",
    "    groupby('order_item_order_id')['order_item_subtotal']. \\\n",
    "    agg(['sum', 'min', 'max', 'count'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>revenue</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "      <th>item_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>order_item_order_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>299.98</td>\n",
       "      <td>299.98</td>\n",
       "      <td>299.98</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>579.98</td>\n",
       "      <td>129.99</td>\n",
       "      <td>250.00</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>699.85</td>\n",
       "      <td>49.98</td>\n",
       "      <td>299.95</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1129.86</td>\n",
       "      <td>99.96</td>\n",
       "      <td>299.98</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>579.92</td>\n",
       "      <td>79.95</td>\n",
       "      <td>299.98</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68879</th>\n",
       "      <td>1259.97</td>\n",
       "      <td>129.99</td>\n",
       "      <td>999.99</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68880</th>\n",
       "      <td>999.77</td>\n",
       "      <td>149.94</td>\n",
       "      <td>250.00</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68881</th>\n",
       "      <td>129.99</td>\n",
       "      <td>129.99</td>\n",
       "      <td>129.99</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68882</th>\n",
       "      <td>109.99</td>\n",
       "      <td>50.00</td>\n",
       "      <td>59.99</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68883</th>\n",
       "      <td>2149.99</td>\n",
       "      <td>150.00</td>\n",
       "      <td>1999.99</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>57431 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     revenue     min      max  item_count\n",
       "order_item_order_id                                      \n",
       "1                     299.98  299.98   299.98           1\n",
       "2                     579.98  129.99   250.00           3\n",
       "4                     699.85   49.98   299.95           4\n",
       "5                    1129.86   99.96   299.98           5\n",
       "7                     579.92   79.95   299.98           3\n",
       "...                      ...     ...      ...         ...\n",
       "68879                1259.97  129.99   999.99           3\n",
       "68880                 999.77  149.94   250.00           5\n",
       "68881                 129.99  129.99   129.99           1\n",
       "68882                 109.99   50.00    59.99           2\n",
       "68883                2149.99  150.00  1999.99           2\n",
       "\n",
       "[57431 rows x 4 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_items. \\\n",
    "    groupby('order_item_order_id')['order_item_subtotal']. \\\n",
    "    agg(['sum', 'min', 'max', 'count']). \\\n",
    "    rename(columns={'count': 'item_count', 'sum': 'revenue'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>order_item_id</th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_item_product_id</th>\n",
       "      <th>order_item_quantity</th>\n",
       "      <th>order_item_subtotal</th>\n",
       "      <th>order_item_product_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>957</td>\n",
       "      <td>1</td>\n",
       "      <td>299.98</td>\n",
       "      <td>299.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1073</td>\n",
       "      <td>1</td>\n",
       "      <td>199.99</td>\n",
       "      <td>199.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>502</td>\n",
       "      <td>5</td>\n",
       "      <td>250.00</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>403</td>\n",
       "      <td>1</td>\n",
       "      <td>129.99</td>\n",
       "      <td>129.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>897</td>\n",
       "      <td>2</td>\n",
       "      <td>49.98</td>\n",
       "      <td>24.99</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172193</th>\n",
       "      <td>172194</td>\n",
       "      <td>68881</td>\n",
       "      <td>403</td>\n",
       "      <td>1</td>\n",
       "      <td>129.99</td>\n",
       "      <td>129.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172194</th>\n",
       "      <td>172195</td>\n",
       "      <td>68882</td>\n",
       "      <td>365</td>\n",
       "      <td>1</td>\n",
       "      <td>59.99</td>\n",
       "      <td>59.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172195</th>\n",
       "      <td>172196</td>\n",
       "      <td>68882</td>\n",
       "      <td>502</td>\n",
       "      <td>1</td>\n",
       "      <td>50.00</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172196</th>\n",
       "      <td>172197</td>\n",
       "      <td>68883</td>\n",
       "      <td>208</td>\n",
       "      <td>1</td>\n",
       "      <td>1999.99</td>\n",
       "      <td>1999.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172197</th>\n",
       "      <td>172198</td>\n",
       "      <td>68883</td>\n",
       "      <td>502</td>\n",
       "      <td>3</td>\n",
       "      <td>150.00</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>172198 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        order_item_id  order_id  order_item_product_id  order_item_quantity  \\\n",
       "0                   1         1                    957                    1   \n",
       "1                   2         2                   1073                    1   \n",
       "2                   3         2                    502                    5   \n",
       "3                   4         2                    403                    1   \n",
       "4                   5         4                    897                    2   \n",
       "...               ...       ...                    ...                  ...   \n",
       "172193         172194     68881                    403                    1   \n",
       "172194         172195     68882                    365                    1   \n",
       "172195         172196     68882                    502                    1   \n",
       "172196         172197     68883                    208                    1   \n",
       "172197         172198     68883                    502                    3   \n",
       "\n",
       "        order_item_subtotal  order_item_product_price  \n",
       "0                    299.98                    299.98  \n",
       "1                    199.99                    199.99  \n",
       "2                    250.00                     50.00  \n",
       "3                    129.99                    129.99  \n",
       "4                     49.98                     24.99  \n",
       "...                     ...                       ...  \n",
       "172193               129.99                    129.99  \n",
       "172194                59.99                     59.99  \n",
       "172195                50.00                     50.00  \n",
       "172196              1999.99                   1999.99  \n",
       "172197               150.00                     50.00  \n",
       "\n",
       "[172198 rows x 6 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_items.rename(columns={'order_item_order_id': 'order_id'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 1\n",
    "\n",
    "Get order_item_count and order_revenue for each order_id."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_item_id</th>\n",
       "      <th>order_item_order_id</th>\n",
       "      <th>order_item_product_id</th>\n",
       "      <th>order_item_quantity</th>\n",
       "      <th>order_item_subtotal</th>\n",
       "      <th>order_item_product_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>957</td>\n",
       "      <td>1</td>\n",
       "      <td>299.98</td>\n",
       "      <td>299.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1073</td>\n",
       "      <td>1</td>\n",
       "      <td>199.99</td>\n",
       "      <td>199.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>502</td>\n",
       "      <td>5</td>\n",
       "      <td>250.00</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>403</td>\n",
       "      <td>1</td>\n",
       "      <td>129.99</td>\n",
       "      <td>129.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>897</td>\n",
       "      <td>2</td>\n",
       "      <td>49.98</td>\n",
       "      <td>24.99</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172193</th>\n",
       "      <td>172194</td>\n",
       "      <td>68881</td>\n",
       "      <td>403</td>\n",
       "      <td>1</td>\n",
       "      <td>129.99</td>\n",
       "      <td>129.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172194</th>\n",
       "      <td>172195</td>\n",
       "      <td>68882</td>\n",
       "      <td>365</td>\n",
       "      <td>1</td>\n",
       "      <td>59.99</td>\n",
       "      <td>59.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172195</th>\n",
       "      <td>172196</td>\n",
       "      <td>68882</td>\n",
       "      <td>502</td>\n",
       "      <td>1</td>\n",
       "      <td>50.00</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172196</th>\n",
       "      <td>172197</td>\n",
       "      <td>68883</td>\n",
       "      <td>208</td>\n",
       "      <td>1</td>\n",
       "      <td>1999.99</td>\n",
       "      <td>1999.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172197</th>\n",
       "      <td>172198</td>\n",
       "      <td>68883</td>\n",
       "      <td>502</td>\n",
       "      <td>3</td>\n",
       "      <td>150.00</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>172198 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        order_item_id  order_item_order_id  order_item_product_id  \\\n",
       "0                   1                    1                    957   \n",
       "1                   2                    2                   1073   \n",
       "2                   3                    2                    502   \n",
       "3                   4                    2                    403   \n",
       "4                   5                    4                    897   \n",
       "...               ...                  ...                    ...   \n",
       "172193         172194                68881                    403   \n",
       "172194         172195                68882                    365   \n",
       "172195         172196                68882                    502   \n",
       "172196         172197                68883                    208   \n",
       "172197         172198                68883                    502   \n",
       "\n",
       "        order_item_quantity  order_item_subtotal  order_item_product_price  \n",
       "0                         1               299.98                    299.98  \n",
       "1                         1               199.99                    199.99  \n",
       "2                         5               250.00                     50.00  \n",
       "3                         1               129.99                    129.99  \n",
       "4                         2                49.98                     24.99  \n",
       "...                     ...                  ...                       ...  \n",
       "172193                    1               129.99                    129.99  \n",
       "172194                    1                59.99                     59.99  \n",
       "172195                    1                50.00                     50.00  \n",
       "172196                    1              1999.99                   1999.99  \n",
       "172197                    3               150.00                     50.00  \n",
       "\n",
       "[172198 rows x 6 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_items"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>order_item_order_id</th>\n",
       "      <th>order_revenue</th>\n",
       "      <th>order_item_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>299.98</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>579.98</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>699.85</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>1129.86</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>579.92</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57426</th>\n",
       "      <td>68879</td>\n",
       "      <td>1259.97</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57427</th>\n",
       "      <td>68880</td>\n",
       "      <td>999.77</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57428</th>\n",
       "      <td>68881</td>\n",
       "      <td>129.99</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57429</th>\n",
       "      <td>68882</td>\n",
       "      <td>109.99</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57430</th>\n",
       "      <td>68883</td>\n",
       "      <td>2149.99</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>57431 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       order_item_order_id  order_revenue  order_item_count\n",
       "0                        1         299.98                 1\n",
       "1                        2         579.98                 3\n",
       "2                        4         699.85                 4\n",
       "3                        5        1129.86                 5\n",
       "4                        7         579.92                 3\n",
       "...                    ...            ...               ...\n",
       "57426                68879        1259.97                 3\n",
       "57427                68880         999.77                 5\n",
       "57428                68881         129.99                 1\n",
       "57429                68882         109.99                 2\n",
       "57430                68883        2149.99                 2\n",
       "\n",
       "[57431 rows x 3 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_items. \\\n",
    "    groupby('order_item_order_id')['order_item_subtotal']. \\\n",
    "    agg(['sum', 'count']). \\\n",
    "    rename(columns={'sum': 'order_revenue', 'count': 'order_item_count'}). \\\n",
    "    reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 2\n",
    "\n",
    "Get order count by month using orders data for specific order_status."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>order_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>order_customer_id</th>\n",
       "      <th>order_status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2013-07-25 00:00:00.0</td>\n",
       "      <td>11599</td>\n",
       "      <td>CLOSED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2013-07-25 00:00:00.0</td>\n",
       "      <td>256</td>\n",
       "      <td>PENDING_PAYMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2013-07-25 00:00:00.0</td>\n",
       "      <td>12111</td>\n",
       "      <td>COMPLETE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2013-07-25 00:00:00.0</td>\n",
       "      <td>8827</td>\n",
       "      <td>CLOSED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2013-07-25 00:00:00.0</td>\n",
       "      <td>11318</td>\n",
       "      <td>COMPLETE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68878</th>\n",
       "      <td>68879</td>\n",
       "      <td>2014-07-09 00:00:00.0</td>\n",
       "      <td>778</td>\n",
       "      <td>COMPLETE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68879</th>\n",
       "      <td>68880</td>\n",
       "      <td>2014-07-13 00:00:00.0</td>\n",
       "      <td>1117</td>\n",
       "      <td>COMPLETE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68880</th>\n",
       "      <td>68881</td>\n",
       "      <td>2014-07-19 00:00:00.0</td>\n",
       "      <td>2518</td>\n",
       "      <td>PENDING_PAYMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68881</th>\n",
       "      <td>68882</td>\n",
       "      <td>2014-07-22 00:00:00.0</td>\n",
       "      <td>10000</td>\n",
       "      <td>ON_HOLD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68882</th>\n",
       "      <td>68883</td>\n",
       "      <td>2014-07-23 00:00:00.0</td>\n",
       "      <td>5533</td>\n",
       "      <td>COMPLETE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>68883 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       order_id             order_date  order_customer_id     order_status\n",
       "0             1  2013-07-25 00:00:00.0              11599           CLOSED\n",
       "1             2  2013-07-25 00:00:00.0                256  PENDING_PAYMENT\n",
       "2             3  2013-07-25 00:00:00.0              12111         COMPLETE\n",
       "3             4  2013-07-25 00:00:00.0               8827           CLOSED\n",
       "4             5  2013-07-25 00:00:00.0              11318         COMPLETE\n",
       "...         ...                    ...                ...              ...\n",
       "68878     68879  2014-07-09 00:00:00.0                778         COMPLETE\n",
       "68879     68880  2014-07-13 00:00:00.0               1117         COMPLETE\n",
       "68880     68881  2014-07-19 00:00:00.0               2518  PENDING_PAYMENT\n",
       "68881     68882  2014-07-22 00:00:00.0              10000          ON_HOLD\n",
       "68882     68883  2014-07-23 00:00:00.0               5533         COMPLETE\n",
       "\n",
       "[68883 rows x 4 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        2013-07\n",
       "1        2013-07\n",
       "2        2013-07\n",
       "3        2013-07\n",
       "4        2013-07\n",
       "          ...   \n",
       "68878    2014-07\n",
       "68879    2014-07\n",
       "68880    2014-07\n",
       "68881    2014-07\n",
       "68882    2014-07\n",
       "Name: order_date, Length: 68883, dtype: object"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.order_date.str.slice(0, 7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders['order_month'] = orders.order_date.str.slice(0, 7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>order_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>order_customer_id</th>\n",
       "      <th>order_status</th>\n",
       "      <th>order_month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2013-07-25 00:00:00.0</td>\n",
       "      <td>11599</td>\n",
       "      <td>CLOSED</td>\n",
       "      <td>2013-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2013-07-25 00:00:00.0</td>\n",
       "      <td>256</td>\n",
       "      <td>PENDING_PAYMENT</td>\n",
       "      <td>2013-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2013-07-25 00:00:00.0</td>\n",
       "      <td>12111</td>\n",
       "      <td>COMPLETE</td>\n",
       "      <td>2013-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2013-07-25 00:00:00.0</td>\n",
       "      <td>8827</td>\n",
       "      <td>CLOSED</td>\n",
       "      <td>2013-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2013-07-25 00:00:00.0</td>\n",
       "      <td>11318</td>\n",
       "      <td>COMPLETE</td>\n",
       "      <td>2013-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68878</th>\n",
       "      <td>68879</td>\n",
       "      <td>2014-07-09 00:00:00.0</td>\n",
       "      <td>778</td>\n",
       "      <td>COMPLETE</td>\n",
       "      <td>2014-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68879</th>\n",
       "      <td>68880</td>\n",
       "      <td>2014-07-13 00:00:00.0</td>\n",
       "      <td>1117</td>\n",
       "      <td>COMPLETE</td>\n",
       "      <td>2014-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68880</th>\n",
       "      <td>68881</td>\n",
       "      <td>2014-07-19 00:00:00.0</td>\n",
       "      <td>2518</td>\n",
       "      <td>PENDING_PAYMENT</td>\n",
       "      <td>2014-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68881</th>\n",
       "      <td>68882</td>\n",
       "      <td>2014-07-22 00:00:00.0</td>\n",
       "      <td>10000</td>\n",
       "      <td>ON_HOLD</td>\n",
       "      <td>2014-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68882</th>\n",
       "      <td>68883</td>\n",
       "      <td>2014-07-23 00:00:00.0</td>\n",
       "      <td>5533</td>\n",
       "      <td>COMPLETE</td>\n",
       "      <td>2014-07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>68883 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       order_id             order_date  order_customer_id     order_status  \\\n",
       "0             1  2013-07-25 00:00:00.0              11599           CLOSED   \n",
       "1             2  2013-07-25 00:00:00.0                256  PENDING_PAYMENT   \n",
       "2             3  2013-07-25 00:00:00.0              12111         COMPLETE   \n",
       "3             4  2013-07-25 00:00:00.0               8827           CLOSED   \n",
       "4             5  2013-07-25 00:00:00.0              11318         COMPLETE   \n",
       "...         ...                    ...                ...              ...   \n",
       "68878     68879  2014-07-09 00:00:00.0                778         COMPLETE   \n",
       "68879     68880  2014-07-13 00:00:00.0               1117         COMPLETE   \n",
       "68880     68881  2014-07-19 00:00:00.0               2518  PENDING_PAYMENT   \n",
       "68881     68882  2014-07-22 00:00:00.0              10000          ON_HOLD   \n",
       "68882     68883  2014-07-23 00:00:00.0               5533         COMPLETE   \n",
       "\n",
       "      order_month  \n",
       "0         2013-07  \n",
       "1         2013-07  \n",
       "2         2013-07  \n",
       "3         2013-07  \n",
       "4         2013-07  \n",
       "...           ...  \n",
       "68878     2014-07  \n",
       "68879     2014-07  \n",
       "68880     2014-07  \n",
       "68881     2014-07  \n",
       "68882     2014-07  \n",
       "\n",
       "[68883 rows x 5 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "order_month\n",
       "2013-07     515\n",
       "2014-07    1419\n",
       "2013-10    1783\n",
       "2014-06    1797\n",
       "2014-05    1854\n",
       "2014-02    1869\n",
       "2013-08    1880\n",
       "2013-12    1898\n",
       "2014-01    1911\n",
       "2014-04    1932\n",
       "2013-09    1933\n",
       "2014-03    1967\n",
       "2013-11    2141\n",
       "Name: order_id, dtype: int64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.query('order_status == \"COMPLETE\"'). \\\n",
    "    groupby('order_month')['order_id']. \\\n",
    "    count(). \\\n",
    "    sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 3\n",
    "\n",
    "Get order_revenue and order_quantity for each order_id. Add quantity of all items for each order_id to get order_quantity."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "order_metrics = order_items. \\\n",
    "    groupby('order_item_order_id')[['order_item_subtotal', 'order_item_quantity']]. \\\n",
    "    agg(['sum'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "order_metrics.columns = ['order_revenue', 'order_quantity']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>order_revenue</th>\n",
       "      <th>order_quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>order_item_order_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>299.98</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>579.98</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>699.85</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1129.86</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>579.92</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68879</th>\n",
       "      <td>1259.97</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68880</th>\n",
       "      <td>999.77</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68881</th>\n",
       "      <td>129.99</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68882</th>\n",
       "      <td>109.99</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68883</th>\n",
       "      <td>2149.99</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>57431 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     order_revenue  order_quantity\n",
       "order_item_order_id                               \n",
       "1                           299.98               1\n",
       "2                           579.98               7\n",
       "4                           699.85              14\n",
       "5                          1129.86              10\n",
       "7                           579.92               7\n",
       "...                            ...             ...\n",
       "68879                      1259.97               3\n",
       "68880                       999.77              17\n",
       "68881                       129.99               1\n",
       "68882                       109.99               2\n",
       "68883                      2149.99               4\n",
       "\n",
       "[57431 rows x 2 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_metrics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}