food_delivery_service_analysis_KPIs.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "d491a8b8-ab52-4a37-b86a-176ff0a69b8f",
   "metadata": {},
   "source": [
    "## KPIs Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6fefea54-126d-4f65-8481-e83a36db2f18",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pandas import read_excel\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed14609e-98dc-4b0f-9168-6dc9883c7936",
   "metadata": {},
   "source": [
    "### Deliverando Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e62da760-3ef1-4f63-b511-d0c510f0f905",
   "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>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>699.0</td>\n",
       "      <td>822.00</td>\n",
       "      <td>deliverando</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>22.5</td>\n",
       "      <td>23.33</td>\n",
       "      <td>deliverando</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td># Negative Comments</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.00</td>\n",
       "      <td>deliverando</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td># Positive Comments</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>deliverando</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>52.0</td>\n",
       "      <td>46.00</td>\n",
       "      <td>deliverando</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name   zip                      kpi  Month 1  Month 2 delivery service\n",
       "0  Mampf  8020  # Commissionable Orders    699.0   822.00      deliverando\n",
       "1  Mampf  8020        Avg Basket Size €     22.5    23.33      deliverando\n",
       "2  Mampf  8020      # Negative Comments      3.0     4.00      deliverando\n",
       "3  Mampf  8020      # Positive Comments      1.0     1.00      deliverando\n",
       "4  Mampf  8020              % Kill Rate     52.0    46.00      deliverando"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1 = pd.read_csv('data_sets/SalesAnalyst_deliverando.csv', delimiter=';')\n",
    "data_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0b28c755-627f-40f1-aa6e-f25e6633d4d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_1_orders= data_1.loc[data_1['kpi']=='# Commissionable Orders']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f5b85d0e-129a-4f17-a829-49b0d167c991",
   "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>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>699.0</td>\n",
       "      <td>822.0</td>\n",
       "      <td>deliverando</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Dolce Vita</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>1178.0</td>\n",
       "      <td>1353.0</td>\n",
       "      <td>deliverando</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>China Restaurant Mond</td>\n",
       "      <td>8010</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>230.0</td>\n",
       "      <td>375.0</td>\n",
       "      <td>deliverando</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Royal</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>692.0</td>\n",
       "      <td>734.0</td>\n",
       "      <td>deliverando</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Xpress</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>365.0</td>\n",
       "      <td>410.0</td>\n",
       "      <td>deliverando</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     name   zip                      kpi  Month 1  Month 2  \\\n",
       "0                   Mampf  8020  # Commissionable Orders    699.0    822.0   \n",
       "6              Dolce Vita  8020  # Commissionable Orders   1178.0   1353.0   \n",
       "12  China Restaurant Mond  8010  # Commissionable Orders    230.0    375.0   \n",
       "18                  Royal  8020  # Commissionable Orders    692.0    734.0   \n",
       "24                 Xpress  8020  # Commissionable Orders    365.0    410.0   \n",
       "\n",
       "   delivery service  \n",
       "0       deliverando  \n",
       "6       deliverando  \n",
       "12      deliverando  \n",
       "18      deliverando  \n",
       "24      deliverando  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_orders.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "83dfaf32-255f-4908-8c0d-d9be6e6a46fc",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/yq/6pm1lcds6ln5qlsjc5y8fly00000gn/T/ipykernel_5904/3894230365.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  data_1_orders['total_orders']= data_1['Month 1'] + data_1['Month 2']\n"
     ]
    }
   ],
   "source": [
    "data_1_orders['total_orders']= data_1['Month 1'] + data_1['Month 2']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5357e6fc-711a-44a2-998f-b5d6c3f5e84a",
   "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>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>699.0</td>\n",
       "      <td>822.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1521.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Dolce Vita</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>1178.0</td>\n",
       "      <td>1353.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2531.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>China Restaurant Mond</td>\n",
       "      <td>8010</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>230.0</td>\n",
       "      <td>375.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>605.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Royal</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>692.0</td>\n",
       "      <td>734.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1426.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Xpress</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>365.0</td>\n",
       "      <td>410.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>775.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     name   zip                      kpi  Month 1  Month 2  \\\n",
       "0                   Mampf  8020  # Commissionable Orders    699.0    822.0   \n",
       "6              Dolce Vita  8020  # Commissionable Orders   1178.0   1353.0   \n",
       "12  China Restaurant Mond  8010  # Commissionable Orders    230.0    375.0   \n",
       "18                  Royal  8020  # Commissionable Orders    692.0    734.0   \n",
       "24                 Xpress  8020  # Commissionable Orders    365.0    410.0   \n",
       "\n",
       "   delivery service  total_orders  \n",
       "0       deliverando        1521.0  \n",
       "6       deliverando        2531.0  \n",
       "12      deliverando         605.0  \n",
       "18      deliverando        1426.0  \n",
       "24      deliverando         775.0  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_orders.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80d8a4c7-f2cf-47ce-84a1-db44d5da9849",
   "metadata": {},
   "source": [
    "### Competition Dataset\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a259273-d526-490e-95f4-266560c5f46a",
   "metadata": {},
   "source": [
    "#### Sheet 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "688afc60-5e8c-4b6a-b0a0-397dd4e0a119",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>orders</th>\n",
       "      <th>month</th>\n",
       "      <th>delivery_service</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chen's Cooking</td>\n",
       "      <td>1120.0</td>\n",
       "      <td>575.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Al Capone</td>\n",
       "      <td>4614.0</td>\n",
       "      <td>283.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Schesch Besch</td>\n",
       "      <td>1010.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Gasthaus Bergwirt</td>\n",
       "      <td>4053.0</td>\n",
       "      <td>1054.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Kebap &amp; Pizza Antalya</td>\n",
       "      <td>9020.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    name     zip  orders  month delivery_service\n",
       "0         Chen's Cooking  1120.0   575.0    1.0      competition\n",
       "1              Al Capone  4614.0   283.0    1.0      competition\n",
       "2          Schesch Besch  1010.0    43.0    1.0      competition\n",
       "3      Gasthaus Bergwirt  4053.0  1054.0    1.0      competition\n",
       "4  Kebap & Pizza Antalya  9020.0   168.0    1.0      competition"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_sheet1 = pd.read_excel('data_sets/SalesAnalyst_Competition.xlsx')\n",
    "data_sheet1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "381d3500-6824-44d5-8b02-db515b93f976",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_2= data_sheet1.astype({'zip': int, 'orders': int, 'month': int },copy=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa76228f-be55-4362-9b18-a0ac49bddbe9",
   "metadata": {},
   "source": [
    "#### Sheet 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "1f75bad4-b793-475d-8f73-30491ed50551",
   "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>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>orders</th>\n",
       "      <th>month</th>\n",
       "      <th>delivery_service</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bio-Pizzeria Vero</td>\n",
       "      <td>1090.0</td>\n",
       "      <td>493.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Nicht Pizza</td>\n",
       "      <td>1100.0</td>\n",
       "      <td>1191.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Fuzo Pizza Kebap &amp; Getränke</td>\n",
       "      <td>4060.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Eat &amp; Meet</td>\n",
       "      <td>4303.0</td>\n",
       "      <td>148.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>La Vostra</td>\n",
       "      <td>1160.0</td>\n",
       "      <td>1540.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          name     zip  orders  month delivery_service\n",
       "0            Bio-Pizzeria Vero  1090.0   493.0    2.0      competition\n",
       "1                  Nicht Pizza  1100.0  1191.0    2.0      competition\n",
       "2  Fuzo Pizza Kebap & Getränke  4060.0    45.0    2.0      competition\n",
       "3                   Eat & Meet  4303.0   148.0    2.0      competition\n",
       "4                    La Vostra  1160.0  1540.0    2.0      competition"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_sheet2 = pd.read_excel('data_sets/SalesAnalyst_Competition.xlsx', sheet_name = 1)\n",
    "data_sheet2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "f899d06b-826a-426c-82a8-097fa178cac3",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_3= data_sheet2.astype({'zip': int, 'orders': int, 'month': int },copy=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52c990e6-1bb6-4547-98d3-0604e5d9ec97",
   "metadata": {},
   "source": [
    "## Data Wrangling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "d1f00fe1-2f1e-4bb5-8b81-e4086c24a678",
   "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>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>699.0</td>\n",
       "      <td>822.00</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1521.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>22.5</td>\n",
       "      <td>23.33</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>45.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td># Negative Comments</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.00</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>7.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td># Positive Comments</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>52.0</td>\n",
       "      <td>46.00</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>98.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name   zip                      kpi  Month 1  Month 2 delivery service  \\\n",
       "0  Mampf  8020  # Commissionable Orders    699.0   822.00      deliverando   \n",
       "1  Mampf  8020        Avg Basket Size €     22.5    23.33      deliverando   \n",
       "2  Mampf  8020      # Negative Comments      3.0     4.00      deliverando   \n",
       "3  Mampf  8020      # Positive Comments      1.0     1.00      deliverando   \n",
       "4  Mampf  8020              % Kill Rate     52.0    46.00      deliverando   \n",
       "\n",
       "   total_orders  \n",
       "0       1521.00  \n",
       "1         45.83  \n",
       "2          7.00  \n",
       "3          2.00  \n",
       "4         98.00  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1['total_orders']= data_1['Month 1'] + data_1['Month 2']\n",
    "data_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "18ab4389-c50c-40af-b33f-642f9b3227c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "competitors_data= data_2.merge(data_3, how= 'inner', on= ['name','zip'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "06698da5-c8d1-4aa6-9d13-ad75e5804201",
   "metadata": {},
   "outputs": [],
   "source": [
    "competitors_data['total_orders']= competitors_data['orders_x'] + competitors_data['orders_y']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "62d686ca-ebe2-4618-bd4f-21b1a243ac1d",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>orders_x</th>\n",
       "      <th>month_x</th>\n",
       "      <th>delivery_service_x</th>\n",
       "      <th>orders_y</th>\n",
       "      <th>month_y</th>\n",
       "      <th>delivery_service_y</th>\n",
       "      <th>total_orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chen's Cooking</td>\n",
       "      <td>1120</td>\n",
       "      <td>575</td>\n",
       "      <td>1</td>\n",
       "      <td>competition</td>\n",
       "      <td>426</td>\n",
       "      <td>2</td>\n",
       "      <td>competition</td>\n",
       "      <td>1001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Al Capone</td>\n",
       "      <td>4614</td>\n",
       "      <td>283</td>\n",
       "      <td>1</td>\n",
       "      <td>competition</td>\n",
       "      <td>151</td>\n",
       "      <td>2</td>\n",
       "      <td>competition</td>\n",
       "      <td>434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Schesch Besch</td>\n",
       "      <td>1010</td>\n",
       "      <td>43</td>\n",
       "      <td>1</td>\n",
       "      <td>competition</td>\n",
       "      <td>79</td>\n",
       "      <td>2</td>\n",
       "      <td>competition</td>\n",
       "      <td>122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Gasthaus Bergwirt</td>\n",
       "      <td>4053</td>\n",
       "      <td>1054</td>\n",
       "      <td>1</td>\n",
       "      <td>competition</td>\n",
       "      <td>1048</td>\n",
       "      <td>2</td>\n",
       "      <td>competition</td>\n",
       "      <td>2102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Kebap &amp; Pizza Antalya</td>\n",
       "      <td>9020</td>\n",
       "      <td>168</td>\n",
       "      <td>1</td>\n",
       "      <td>competition</td>\n",
       "      <td>156</td>\n",
       "      <td>2</td>\n",
       "      <td>competition</td>\n",
       "      <td>324</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    name   zip  orders_x  month_x delivery_service_x  \\\n",
       "0         Chen's Cooking  1120       575        1        competition   \n",
       "1              Al Capone  4614       283        1        competition   \n",
       "2          Schesch Besch  1010        43        1        competition   \n",
       "3      Gasthaus Bergwirt  4053      1054        1        competition   \n",
       "4  Kebap & Pizza Antalya  9020       168        1        competition   \n",
       "\n",
       "   orders_y  month_y delivery_service_y  total_orders  \n",
       "0       426        2        competition          1001  \n",
       "1       151        2        competition           434  \n",
       "2        79        2        competition           122  \n",
       "3      1048        2        competition          2102  \n",
       "4       156        2        competition           324  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "competitors_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99493503-6cc3-4148-91f0-e5ff804dc907",
   "metadata": {},
   "source": [
    "## 4. What conclusions regarding other KPIs can you draw from the performance of the top restaurants on deliverando?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "fe0fd343-f909-401b-934f-d5abbe2d1953",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['# Commissionable Orders', 'Avg Basket Size €',\n",
       "       '# Negative Comments', '# Positive Comments', '% Kill Rate',\n",
       "       'Avg Time to Accept (s)'], dtype=object)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1['kpi'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1506ff87-c9be-4202-9066-563071a4684e",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### kPI 1: % Kill rate - the percentage of customers who did not order again on the platform after placing an order with the restaurant"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "d887e895-1fff-488d-aa06-6cf6ab23d234",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_1_kill_rate= data_1.loc[data_1['kpi']=='% Kill Rate']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "e8c6e7db-9526-4b2b-8624-f0e9c564880c",
   "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>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>52.00</td>\n",
       "      <td>46.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>98.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Dolce Vita</td>\n",
       "      <td>8020</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>0.06</td>\n",
       "      <td>57.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>57.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>China Restaurant Mond</td>\n",
       "      <td>8010</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>54.00</td>\n",
       "      <td>112.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>166.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Royal</td>\n",
       "      <td>8020</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>77.00</td>\n",
       "      <td>73.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>150.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Xpress</td>\n",
       "      <td>8020</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>39.00</td>\n",
       "      <td>44.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>83.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     name   zip          kpi  Month 1  Month 2  \\\n",
       "4                   Mampf  8020  % Kill Rate    52.00     46.0   \n",
       "10             Dolce Vita  8020  % Kill Rate     0.06     57.0   \n",
       "16  China Restaurant Mond  8010  % Kill Rate    54.00    112.0   \n",
       "22                  Royal  8020  % Kill Rate    77.00     73.0   \n",
       "28                 Xpress  8020  % Kill Rate    39.00     44.0   \n",
       "\n",
       "   delivery service  total_orders  \n",
       "4       deliverando         98.00  \n",
       "10      deliverando         57.06  \n",
       "16      deliverando        166.00  \n",
       "22      deliverando        150.00  \n",
       "28      deliverando         83.00  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_kill_rate.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "aeee5973-2e21-4e82-890a-a34b4e11c450",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/yq/6pm1lcds6ln5qlsjc5y8fly00000gn/T/ipykernel_5904/1840767884.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  data_1_kill_rate['aprox. % kill rate avg.']= (data_1_kill_rate['Month 1']+data_1_kill_rate['Month 2'])/2\n"
     ]
    }
   ],
   "source": [
    "data_1_kill_rate['aprox. % kill rate avg.']= (data_1_kill_rate['Month 1']+data_1_kill_rate['Month 2'])/2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "a97f7133-8e4d-48eb-8c39-cab75e916c8f",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "      <th>aprox. % kill rate avg.</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>906</th>\n",
       "      <td>PHO YOU</td>\n",
       "      <td>8010</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>227.0</td>\n",
       "      <td>231.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>458.0</td>\n",
       "      <td>229.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>800</th>\n",
       "      <td>Granola</td>\n",
       "      <td>8054</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>217.0</td>\n",
       "      <td>207.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>424.0</td>\n",
       "      <td>212.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>Milu Milu Taiwanesisches Restaurant</td>\n",
       "      <td>8010</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>184.0</td>\n",
       "      <td>198.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>382.0</td>\n",
       "      <td>191.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>Peppone</td>\n",
       "      <td>8010</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>198.0</td>\n",
       "      <td>173.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>371.0</td>\n",
       "      <td>185.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1223</th>\n",
       "      <td>Pi Pa Po</td>\n",
       "      <td>8055</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>166.0</td>\n",
       "      <td>179.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>345.0</td>\n",
       "      <td>172.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>796</th>\n",
       "      <td>Zu den 3 goldenen Kugeln</td>\n",
       "      <td>8055</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>163.0</td>\n",
       "      <td>178.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>341.0</td>\n",
       "      <td>170.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1373</th>\n",
       "      <td>Momiji</td>\n",
       "      <td>8010</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>161.0</td>\n",
       "      <td>149.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>310.0</td>\n",
       "      <td>155.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>419</th>\n",
       "      <td>Santa Catharina</td>\n",
       "      <td>8010</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>167.0</td>\n",
       "      <td>134.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>301.0</td>\n",
       "      <td>150.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>917</th>\n",
       "      <td>Pyramidana</td>\n",
       "      <td>8054</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>158.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>290.0</td>\n",
       "      <td>145.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>687</th>\n",
       "      <td>Vapiano</td>\n",
       "      <td>8010</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>144.0</td>\n",
       "      <td>142.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>286.0</td>\n",
       "      <td>143.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     name   zip          kpi  Month 1  \\\n",
       "906                               PHO YOU  8010  % Kill Rate    227.0   \n",
       "800                               Granola  8054  % Kill Rate    217.0   \n",
       "195   Milu Milu Taiwanesisches Restaurant  8010  % Kill Rate    184.0   \n",
       "476                               Peppone  8010  % Kill Rate    198.0   \n",
       "1223                             Pi Pa Po  8055  % Kill Rate    166.0   \n",
       "796              Zu den 3 goldenen Kugeln  8055  % Kill Rate    163.0   \n",
       "1373                               Momiji  8010  % Kill Rate    161.0   \n",
       "419                       Santa Catharina  8010  % Kill Rate    167.0   \n",
       "917                            Pyramidana  8054  % Kill Rate    158.0   \n",
       "687                               Vapiano  8010  % Kill Rate    144.0   \n",
       "\n",
       "      Month 2 delivery service  total_orders  aprox. % kill rate avg.  \n",
       "906     231.0      deliverando         458.0                    229.0  \n",
       "800     207.0      deliverando         424.0                    212.0  \n",
       "195     198.0      deliverando         382.0                    191.0  \n",
       "476     173.0      deliverando         371.0                    185.5  \n",
       "1223    179.0      deliverando         345.0                    172.5  \n",
       "796     178.0      deliverando         341.0                    170.5  \n",
       "1373    149.0      deliverando         310.0                    155.0  \n",
       "419     134.0      deliverando         301.0                    150.5  \n",
       "917     132.0      deliverando         290.0                    145.0  \n",
       "687     142.0      deliverando         286.0                    143.0  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_10_kill_rate= data_1_kill_rate.sort_values(by=['aprox. % kill rate avg.'], ascending= False).head(10)\n",
    "top_10_kill_rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "219ba8bf-85dc-41ed-840e-705d341ddcb9",
   "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>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "      <th>aprox. % kill rate avg.</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>52.00</td>\n",
       "      <td>46.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>98.00</td>\n",
       "      <td>49.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Dolce Vita</td>\n",
       "      <td>8020</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>0.06</td>\n",
       "      <td>57.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>57.06</td>\n",
       "      <td>28.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>China Restaurant Mond</td>\n",
       "      <td>8010</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>54.00</td>\n",
       "      <td>112.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>166.00</td>\n",
       "      <td>83.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Royal</td>\n",
       "      <td>8020</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>77.00</td>\n",
       "      <td>73.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>150.00</td>\n",
       "      <td>75.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Xpress</td>\n",
       "      <td>8020</td>\n",
       "      <td>% Kill Rate</td>\n",
       "      <td>39.00</td>\n",
       "      <td>44.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>83.00</td>\n",
       "      <td>41.50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     name   zip          kpi  Month 1  Month 2  \\\n",
       "4                   Mampf  8020  % Kill Rate    52.00     46.0   \n",
       "10             Dolce Vita  8020  % Kill Rate     0.06     57.0   \n",
       "16  China Restaurant Mond  8010  % Kill Rate    54.00    112.0   \n",
       "22                  Royal  8020  % Kill Rate    77.00     73.0   \n",
       "28                 Xpress  8020  % Kill Rate    39.00     44.0   \n",
       "\n",
       "   delivery service  total_orders  aprox. % kill rate avg.  \n",
       "4       deliverando         98.00                    49.00  \n",
       "10      deliverando         57.06                    28.53  \n",
       "16      deliverando        166.00                    83.00  \n",
       "22      deliverando        150.00                    75.00  \n",
       "28      deliverando         83.00                    41.50  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "kip_kill_rate= data_1_kill_rate.sort_values(by=['aprox. % kill rate avg.'], ascending= False)\n",
    "kip_kill_rate= kip_kill_rate.astype({'Month 1': int,'Month 2': int,'aprox. % kill rate avg.': int },copy=False)\n",
    "data_1_kill_rate.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "69c620cb-94f2-4ebc-acd5-8cbb7fb0c31a",
   "metadata": {},
   "source": [
    "#### kPI 2: # Positive comment - number of reviews with positive comments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "b29ce481-73fa-4d9e-908f-c571f88ea43c",
   "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>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td># Positive Comments</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Dolce Vita</td>\n",
       "      <td>8020</td>\n",
       "      <td># Positive Comments</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>China Restaurant Mond</td>\n",
       "      <td>8010</td>\n",
       "      <td># Positive Comments</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Royal</td>\n",
       "      <td>8020</td>\n",
       "      <td># Positive Comments</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Xpress</td>\n",
       "      <td>8020</td>\n",
       "      <td># Positive Comments</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     name   zip                  kpi  Month 1  Month 2  \\\n",
       "3                   Mampf  8020  # Positive Comments      1.0      1.0   \n",
       "9              Dolce Vita  8020  # Positive Comments      1.0      1.0   \n",
       "15  China Restaurant Mond  8010  # Positive Comments      1.0      1.0   \n",
       "21                  Royal  8020  # Positive Comments      1.0      1.0   \n",
       "27                 Xpress  8020  # Positive Comments      1.0      1.0   \n",
       "\n",
       "   delivery service  total_orders  \n",
       "3       deliverando           2.0  \n",
       "9       deliverando           2.0  \n",
       "15      deliverando           2.0  \n",
       "21      deliverando           2.0  \n",
       "27      deliverando           2.0  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_positive_comment= data_1.loc[data_1['kpi']=='# Positive Comments']\n",
    "data_1_positive_comment.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "8495bda0-e6d9-4648-97e3-d524b1955083",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td># Positive Comments</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>758</th>\n",
       "      <td>Venezia Star</td>\n",
       "      <td>8020</td>\n",
       "      <td># Positive Comments</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1372</th>\n",
       "      <td>Momiji</td>\n",
       "      <td>8010</td>\n",
       "      <td># Positive Comments</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1366</th>\n",
       "      <td>Gasthof s'Reindl</td>\n",
       "      <td>8055</td>\n",
       "      <td># Positive Comments</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1360</th>\n",
       "      <td>Come a Casa</td>\n",
       "      <td>8054</td>\n",
       "      <td># Positive Comments</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1354</th>\n",
       "      <td>Pizzeria Bulut</td>\n",
       "      <td>8055</td>\n",
       "      <td># Positive Comments</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>722</th>\n",
       "      <td>Halici - Cafe Kebaphaus</td>\n",
       "      <td>8020</td>\n",
       "      <td># Positive Comments</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>734</th>\n",
       "      <td>Bierbaron</td>\n",
       "      <td>8010</td>\n",
       "      <td># Positive Comments</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>740</th>\n",
       "      <td>Pizzeria Ristorante Zeppelin</td>\n",
       "      <td>8010</td>\n",
       "      <td># Positive Comments</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>746</th>\n",
       "      <td>Continuum</td>\n",
       "      <td>8010</td>\n",
       "      <td># Positive Comments</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              name   zip                  kpi  Month 1  \\\n",
       "3                            Mampf  8020  # Positive Comments      1.0   \n",
       "758                   Venezia Star  8020  # Positive Comments      1.0   \n",
       "1372                        Momiji  8010  # Positive Comments      1.0   \n",
       "1366              Gasthof s'Reindl  8055  # Positive Comments      1.0   \n",
       "1360                   Come a Casa  8054  # Positive Comments      1.0   \n",
       "1354                Pizzeria Bulut  8055  # Positive Comments      1.0   \n",
       "722        Halici - Cafe Kebaphaus  8020  # Positive Comments      1.0   \n",
       "734                      Bierbaron  8010  # Positive Comments      1.0   \n",
       "740   Pizzeria Ristorante Zeppelin  8010  # Positive Comments      1.0   \n",
       "746                      Continuum  8010  # Positive Comments      1.0   \n",
       "\n",
       "      Month 2 delivery service  total_orders  \n",
       "3         1.0      deliverando           2.0  \n",
       "758       1.0      deliverando           2.0  \n",
       "1372      1.0      deliverando           2.0  \n",
       "1366      1.0      deliverando           2.0  \n",
       "1360      1.0      deliverando           2.0  \n",
       "1354      1.0      deliverando           2.0  \n",
       "722       1.0      deliverando           2.0  \n",
       "734       1.0      deliverando           2.0  \n",
       "740       1.0      deliverando           2.0  \n",
       "746       1.0      deliverando           2.0  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_positive_comment.sort_values(by=['total_orders'], ascending= False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "1351ab74-1715-444d-9bb9-36382558e04c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2.0"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_positive_comment['total_orders'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "ed331261-9727-421c-ba6e-77a409c861da",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "data_1_positive_comment = data_1_positive_comment.astype({'Month 1': int,'Month 2': int,'total_orders': int },copy=False)\n",
    "data_1_positive_comment = data_1_positive_comment.rename(columns={\"total_orders\": \"total_positive_comments\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "026378fa-8f7c-49c3-9c1b-0b48949b3886",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2    179\n",
       "0    113\n",
       "1     43\n",
       "Name: total_positive_comments, dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_positive_comment['total_positive_comments'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "1fa3834c-361c-4698-94d3-7f56bf9e1064",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "335"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_positive_comment['total_positive_comments'].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f125b47-69f5-4304-b8b7-9be85160f3bf",
   "metadata": {},
   "source": [
    "#### kPI 3: # Negative comment - number of reviews with negative comments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "fff6582e-ac50-4145-9fa6-781693d733b8",
   "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>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td># Negative Comments</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Dolce Vita</td>\n",
       "      <td>8020</td>\n",
       "      <td># Negative Comments</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>China Restaurant Mond</td>\n",
       "      <td>8010</td>\n",
       "      <td># Negative Comments</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Royal</td>\n",
       "      <td>8020</td>\n",
       "      <td># Negative Comments</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Xpress</td>\n",
       "      <td>8020</td>\n",
       "      <td># Negative Comments</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     name   zip                  kpi  Month 1  Month 2  \\\n",
       "2                   Mampf  8020  # Negative Comments      3.0      4.0   \n",
       "8              Dolce Vita  8020  # Negative Comments      0.0      3.0   \n",
       "14  China Restaurant Mond  8010  # Negative Comments      1.0      0.0   \n",
       "20                  Royal  8020  # Negative Comments      3.0      8.0   \n",
       "26                 Xpress  8020  # Negative Comments      2.0      0.0   \n",
       "\n",
       "   delivery service  total_orders  \n",
       "2       deliverando           7.0  \n",
       "8       deliverando           3.0  \n",
       "14      deliverando           1.0  \n",
       "20      deliverando          11.0  \n",
       "26      deliverando           2.0  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_negative_comment= data_1.loc[data_1['kpi']=='# Negative Comments']\n",
    "data_1_negative_comment.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "53c92e89-9273-418a-bd1a-83902c9bd34a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "data_1_negative_comment= data_1_negative_comment.astype({'Month 1': int,'Month 2': int,'total_orders': int },copy=False)\n",
    "data_1_negative_comment = data_1_negative_comment.rename(columns={\"total_orders\": \"total_negative_comments\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "5e763421-aa4e-4383-a67d-a4614a7e4ab9",
   "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>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_negative_comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>658</th>\n",
       "      <td>KFC</td>\n",
       "      <td>8020</td>\n",
       "      <td># Negative Comments</td>\n",
       "      <td>13</td>\n",
       "      <td>10</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>547</th>\n",
       "      <td>McDonald's®</td>\n",
       "      <td>8010</td>\n",
       "      <td># Negative Comments</td>\n",
       "      <td>12</td>\n",
       "      <td>9</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>973</th>\n",
       "      <td>Mamacita - California Burritos</td>\n",
       "      <td>8010</td>\n",
       "      <td># Negative Comments</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>594</th>\n",
       "      <td>Beste Freunde - Burgergrill</td>\n",
       "      <td>8020</td>\n",
       "      <td># Negative Comments</td>\n",
       "      <td>11</td>\n",
       "      <td>8</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Pizzeria Mama Rosa</td>\n",
       "      <td>8051</td>\n",
       "      <td># Negative Comments</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>457</th>\n",
       "      <td>Tasty Donuts &amp; Coffee</td>\n",
       "      <td>8010</td>\n",
       "      <td># Negative Comments</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>445</th>\n",
       "      <td>Cafe Abol</td>\n",
       "      <td>8010</td>\n",
       "      <td># Negative Comments</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>440</th>\n",
       "      <td>Cevabdzinica Hodzic - Balkan Spezialitäten</td>\n",
       "      <td>8020</td>\n",
       "      <td># Negative Comments</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>435</th>\n",
       "      <td>Noodle House</td>\n",
       "      <td>8010</td>\n",
       "      <td># Negative Comments</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1646</th>\n",
       "      <td>Easy Asia</td>\n",
       "      <td>8020</td>\n",
       "      <td># Negative Comments</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>335 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            name   zip                  kpi  \\\n",
       "658                                          KFC  8020  # Negative Comments   \n",
       "547                                  McDonald's®  8010  # Negative Comments   \n",
       "973               Mamacita - California Burritos  8010  # Negative Comments   \n",
       "594                  Beste Freunde - Burgergrill  8020  # Negative Comments   \n",
       "44                            Pizzeria Mama Rosa  8051  # Negative Comments   \n",
       "...                                          ...   ...                  ...   \n",
       "457                        Tasty Donuts & Coffee  8010  # Negative Comments   \n",
       "445                                    Cafe Abol  8010  # Negative Comments   \n",
       "440   Cevabdzinica Hodzic - Balkan Spezialitäten  8020  # Negative Comments   \n",
       "435                                 Noodle House  8010  # Negative Comments   \n",
       "1646                                   Easy Asia  8020  # Negative Comments   \n",
       "\n",
       "      Month 1  Month 2 delivery service  total_negative_comments  \n",
       "658        13       10      deliverando                       23  \n",
       "547        12        9      deliverando                       21  \n",
       "973        10       10      deliverando                       20  \n",
       "594        11        8      deliverando                       19  \n",
       "44          9        9      deliverando                       18  \n",
       "...       ...      ...              ...                      ...  \n",
       "457         0        0      deliverando                        0  \n",
       "445         0        0      deliverando                        0  \n",
       "440         0        0      deliverando                        0  \n",
       "435         0        0      deliverando                        0  \n",
       "1646        0        0      deliverando                        0  \n",
       "\n",
       "[335 rows x 7 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_negative_comment= data_1_negative_comment.sort_values(by=['total_negative_comments'], ascending= False)\n",
    "data_1_negative_comment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "e834cc43-0f9d-433b-ac2a-540d604b100c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "23"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_negative_comment['total_negative_comments'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "6df498f6-322d-45c0-a8f2-accb335d4a31",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     188\n",
       "1      49\n",
       "2      32\n",
       "3      21\n",
       "4      13\n",
       "5       6\n",
       "11      4\n",
       "7       4\n",
       "9       4\n",
       "12      3\n",
       "18      2\n",
       "21      1\n",
       "8       1\n",
       "14      1\n",
       "6       1\n",
       "15      1\n",
       "17      1\n",
       "19      1\n",
       "20      1\n",
       "23      1\n",
       "Name: total_negative_comments, dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_negative_comment['total_negative_comments'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43c0114e-da9f-4b3c-a683-663b9bd8f5d8",
   "metadata": {},
   "source": [
    "#### kPI 4: Avg time to accept (s) - the average time (in seconds) to accept an order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "3f5d6538-48ae-4cdc-a148-8a32ba8e3a84",
   "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>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>17.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Dolce Vita</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>China Restaurant Mond</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>19.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Royal</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>27.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Xpress</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>20.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>38.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     name   zip                     kpi  Month 1  Month 2  \\\n",
       "5                   Mampf  8020  Avg Time to Accept (s)     17.0     15.0   \n",
       "11             Dolce Vita  8020  Avg Time to Accept (s)      9.0      9.0   \n",
       "17  China Restaurant Mond  8010  Avg Time to Accept (s)     19.0     20.0   \n",
       "23                  Royal  8020  Avg Time to Accept (s)     27.0     28.0   \n",
       "29                 Xpress  8020  Avg Time to Accept (s)     20.0     18.0   \n",
       "\n",
       "   delivery service  total_orders  \n",
       "5       deliverando          32.0  \n",
       "11      deliverando          18.0  \n",
       "17      deliverando          39.0  \n",
       "23      deliverando          55.0  \n",
       "29      deliverando          38.0  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_avg_time_accept= data_1.loc[data_1['kpi']=='Avg Time to Accept (s)']\n",
    "data_1_avg_time_accept.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "8a9d0208-23d3-42f5-8de1-961d881f1aef",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/yq/6pm1lcds6ln5qlsjc5y8fly00000gn/T/ipykernel_5904/503625800.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  data_1_avg_time_accept['avg_time_accept_min']= ((data_1_avg_time_accept['Month 1']+data_1_avg_time_accept['Month 2'])/60).round(2)\n"
     ]
    }
   ],
   "source": [
    "data_1_avg_time_accept['avg_time_accept_min']= ((data_1_avg_time_accept['Month 1']+data_1_avg_time_accept['Month 2'])/60).round(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "1288b59f-dbf2-4f95-95ab-76c7aced180c",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "      <th>avg_time_accept_min</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>789</th>\n",
       "      <td>Zu den 3 goldenen Kugeln</td>\n",
       "      <td>8041</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>1951.0</td>\n",
       "      <td>877.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2828.0</td>\n",
       "      <td>47.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>930</th>\n",
       "      <td>Don Camillo</td>\n",
       "      <td>8041</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>885.0</td>\n",
       "      <td>1051.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1936.0</td>\n",
       "      <td>32.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>846</th>\n",
       "      <td>Cooking Wu</td>\n",
       "      <td>8052</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>1333.0</td>\n",
       "      <td>361.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1694.0</td>\n",
       "      <td>28.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>777</th>\n",
       "      <td>Confiserie Heindl</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>915.0</td>\n",
       "      <td>633.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1548.0</td>\n",
       "      <td>25.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>562</th>\n",
       "      <td>McDonald's®</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>627.0</td>\n",
       "      <td>905.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1532.0</td>\n",
       "      <td>25.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>694</th>\n",
       "      <td>Eiscafe Roma</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>575.0</td>\n",
       "      <td>840.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1415.0</td>\n",
       "      <td>23.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>556</th>\n",
       "      <td>McDonald's®</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>581.0</td>\n",
       "      <td>730.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1311.0</td>\n",
       "      <td>21.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1316</th>\n",
       "      <td>Gasthaus zum weißen Kreuz</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>696.0</td>\n",
       "      <td>584.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1280.0</td>\n",
       "      <td>21.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>715</th>\n",
       "      <td>Mumbai Cuisine</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>669.0</td>\n",
       "      <td>563.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1232.0</td>\n",
       "      <td>20.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>961</th>\n",
       "      <td>Humuhumu Poke Bar</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>562.0</td>\n",
       "      <td>648.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1210.0</td>\n",
       "      <td>20.17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           name   zip                     kpi  Month 1  \\\n",
       "789    Zu den 3 goldenen Kugeln  8041  Avg Time to Accept (s)   1951.0   \n",
       "930                 Don Camillo  8041  Avg Time to Accept (s)    885.0   \n",
       "846                  Cooking Wu  8052  Avg Time to Accept (s)   1333.0   \n",
       "777           Confiserie Heindl  8010  Avg Time to Accept (s)    915.0   \n",
       "562                 McDonald's®  8020  Avg Time to Accept (s)    627.0   \n",
       "694                Eiscafe Roma  8010  Avg Time to Accept (s)    575.0   \n",
       "556                 McDonald's®  8010  Avg Time to Accept (s)    581.0   \n",
       "1316  Gasthaus zum weißen Kreuz  8010  Avg Time to Accept (s)    696.0   \n",
       "715              Mumbai Cuisine  8010  Avg Time to Accept (s)    669.0   \n",
       "961           Humuhumu Poke Bar  8010  Avg Time to Accept (s)    562.0   \n",
       "\n",
       "      Month 2 delivery service  total_orders  avg_time_accept_min  \n",
       "789     877.0      deliverando        2828.0                47.13  \n",
       "930    1051.0      deliverando        1936.0                32.27  \n",
       "846     361.0      deliverando        1694.0                28.23  \n",
       "777     633.0      deliverando        1548.0                25.80  \n",
       "562     905.0      deliverando        1532.0                25.53  \n",
       "694     840.0      deliverando        1415.0                23.58  \n",
       "556     730.0      deliverando        1311.0                21.85  \n",
       "1316    584.0      deliverando        1280.0                21.33  \n",
       "715     563.0      deliverando        1232.0                20.53  \n",
       "961     648.0      deliverando        1210.0                20.17  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_avg_time_accept.sort_values(by=['avg_time_accept_min'], ascending= False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "afcc05be-768f-45b1-81c8-014e7e971a1f",
   "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>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "      <th>avg_time_accept_min</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Dolce Vita</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>332</th>\n",
       "      <td>Euro Pizza Kebap Burger</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>Da Piero</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280</th>\n",
       "      <td>Ristorante Pizzeria La Perla</td>\n",
       "      <td>8055</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>9.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>19.0</td>\n",
       "      <td>0.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>308</th>\n",
       "      <td>Pizzeria Violeta</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>390</th>\n",
       "      <td>Burger King</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250</th>\n",
       "      <td>Big Kebap Pizza &amp; Burger</td>\n",
       "      <td>8053</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>11.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>517</th>\n",
       "      <td>Best Food Grill</td>\n",
       "      <td>8051</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>Pizza Burg</td>\n",
       "      <td>8041</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>Pizza und Kebap Studio</td>\n",
       "      <td>8042</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>11.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             name   zip                     kpi  Month 1  \\\n",
       "11                     Dolce Vita  8020  Avg Time to Accept (s)      9.0   \n",
       "332       Euro Pizza Kebap Burger  8010  Avg Time to Accept (s)      9.0   \n",
       "149                      Da Piero  8020  Avg Time to Accept (s)      9.0   \n",
       "280  Ristorante Pizzeria La Perla  8055  Avg Time to Accept (s)      9.0   \n",
       "308              Pizzeria Violeta  8010  Avg Time to Accept (s)     10.0   \n",
       "390                   Burger King  8010  Avg Time to Accept (s)     10.0   \n",
       "250      Big Kebap Pizza & Burger  8053  Avg Time to Accept (s)     11.0   \n",
       "517               Best Food Grill  8051  Avg Time to Accept (s)     10.0   \n",
       "155                    Pizza Burg  8041  Avg Time to Accept (s)     10.0   \n",
       "143        Pizza und Kebap Studio  8042  Avg Time to Accept (s)     11.0   \n",
       "\n",
       "     Month 2 delivery service  total_orders  avg_time_accept_min  \n",
       "11       9.0      deliverando          18.0                 0.30  \n",
       "332      9.0      deliverando          18.0                 0.30  \n",
       "149      9.0      deliverando          18.0                 0.30  \n",
       "280     10.0      deliverando          19.0                 0.32  \n",
       "308     10.0      deliverando          20.0                 0.33  \n",
       "390     10.0      deliverando          20.0                 0.33  \n",
       "250      9.0      deliverando          20.0                 0.33  \n",
       "517     11.0      deliverando          21.0                 0.35  \n",
       "155     11.0      deliverando          21.0                 0.35  \n",
       "143     10.0      deliverando          21.0                 0.35  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_avg_time_accept.sort_values(by=['avg_time_accept_min'], ascending= True).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "bba4e827-3694-4889-b0d7-49126cc1f1c6",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "data_1_avg_time_accept= data_1_avg_time_accept.astype({'Month 1': int,'Month 2': int,'total_orders': int },copy=False)\n",
    "data_1_avg_time_accept= data_1_avg_time_accept.drop(['total_orders'], axis=1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "0130be98-7ae3-45c1-931c-cc8b078d960c",
   "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>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>avg_time_accept_min</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Dolce Vita</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>332</th>\n",
       "      <td>Euro Pizza Kebap Burger</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>Da Piero</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280</th>\n",
       "      <td>Ristorante Pizzeria La Perla</td>\n",
       "      <td>8055</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>308</th>\n",
       "      <td>Pizzeria Violeta</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>562</th>\n",
       "      <td>McDonald's®</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>627</td>\n",
       "      <td>905</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>25.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>777</th>\n",
       "      <td>Confiserie Heindl</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>915</td>\n",
       "      <td>633</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>25.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>846</th>\n",
       "      <td>Cooking Wu</td>\n",
       "      <td>8052</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>1333</td>\n",
       "      <td>361</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>28.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>930</th>\n",
       "      <td>Don Camillo</td>\n",
       "      <td>8041</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>885</td>\n",
       "      <td>1051</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>32.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>789</th>\n",
       "      <td>Zu den 3 goldenen Kugeln</td>\n",
       "      <td>8041</td>\n",
       "      <td>Avg Time to Accept (s)</td>\n",
       "      <td>1951</td>\n",
       "      <td>877</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>47.13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>214 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             name   zip                     kpi  Month 1  \\\n",
       "11                     Dolce Vita  8020  Avg Time to Accept (s)        9   \n",
       "332       Euro Pizza Kebap Burger  8010  Avg Time to Accept (s)        9   \n",
       "149                      Da Piero  8020  Avg Time to Accept (s)        9   \n",
       "280  Ristorante Pizzeria La Perla  8055  Avg Time to Accept (s)        9   \n",
       "308              Pizzeria Violeta  8010  Avg Time to Accept (s)       10   \n",
       "..                            ...   ...                     ...      ...   \n",
       "562                   McDonald's®  8020  Avg Time to Accept (s)      627   \n",
       "777             Confiserie Heindl  8010  Avg Time to Accept (s)      915   \n",
       "846                    Cooking Wu  8052  Avg Time to Accept (s)     1333   \n",
       "930                   Don Camillo  8041  Avg Time to Accept (s)      885   \n",
       "789      Zu den 3 goldenen Kugeln  8041  Avg Time to Accept (s)     1951   \n",
       "\n",
       "     Month 2 delivery service  avg_time_accept_min  \n",
       "11         9      deliverando                 0.30  \n",
       "332        9      deliverando                 0.30  \n",
       "149        9      deliverando                 0.30  \n",
       "280       10      deliverando                 0.32  \n",
       "308       10      deliverando                 0.33  \n",
       "..       ...              ...                  ...  \n",
       "562      905      deliverando                25.53  \n",
       "777      633      deliverando                25.80  \n",
       "846      361      deliverando                28.23  \n",
       "930     1051      deliverando                32.27  \n",
       "789      877      deliverando                47.13  \n",
       "\n",
       "[214 rows x 7 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_avg_time_accept= data_1_avg_time_accept.sort_values(by=['avg_time_accept_min'], ascending= True)\n",
    "data_1_avg_time_accept"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "2ae206bc-e5f4-43ac-9a6d-0ef28b41461a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.52    7\n",
       "0.73    5\n",
       "0.53    5\n",
       "0.63    4\n",
       "0.35    4\n",
       "Name: avg_time_accept_min, dtype: int64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_avg_time_accept['avg_time_accept_min'].value_counts().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1118d58f-ba75-425c-a70e-93c844855cd7",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### kPI 5: Commissionable Orders - the orders that deliverando earns commissions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "2730aad9-32e5-4432-a263-d12d6581b2a0",
   "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>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>699.0</td>\n",
       "      <td>822.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1521.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Dolce Vita</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>1178.0</td>\n",
       "      <td>1353.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2531.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>China Restaurant Mond</td>\n",
       "      <td>8010</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>230.0</td>\n",
       "      <td>375.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>605.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Royal</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>692.0</td>\n",
       "      <td>734.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1426.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Xpress</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>365.0</td>\n",
       "      <td>410.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>775.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     name   zip                      kpi  Month 1  Month 2  \\\n",
       "0                   Mampf  8020  # Commissionable Orders    699.0    822.0   \n",
       "6              Dolce Vita  8020  # Commissionable Orders   1178.0   1353.0   \n",
       "12  China Restaurant Mond  8010  # Commissionable Orders    230.0    375.0   \n",
       "18                  Royal  8020  # Commissionable Orders    692.0    734.0   \n",
       "24                 Xpress  8020  # Commissionable Orders    365.0    410.0   \n",
       "\n",
       "   delivery service  total_orders  \n",
       "0       deliverando        1521.0  \n",
       "6       deliverando        2531.0  \n",
       "12      deliverando         605.0  \n",
       "18      deliverando        1426.0  \n",
       "24      deliverando         775.0  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_commissionable_orders= data_1.loc[data_1['kpi']=='# Commissionable Orders']\n",
    "data_1_commissionable_orders.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "1eb13114-e0d1-45d1-9edc-cfebafdd9710",
   "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>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>545</th>\n",
       "      <td>McDonald's®</td>\n",
       "      <td>8010</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>2508.0</td>\n",
       "      <td>2038.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>4546.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>297</th>\n",
       "      <td>Pizzeria Ristorante Da Vinci Linz</td>\n",
       "      <td>8010</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>1933.0</td>\n",
       "      <td>2180.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>4113.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>483</th>\n",
       "      <td>Swing Kitchen</td>\n",
       "      <td>8010</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>1982.0</td>\n",
       "      <td>1913.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>3895.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>Restaurant Z10</td>\n",
       "      <td>8010</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>1899.0</td>\n",
       "      <td>1859.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>3758.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>Da Piero</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>1463.0</td>\n",
       "      <td>1634.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>3097.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Dolce Vita</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>1178.0</td>\n",
       "      <td>1353.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2531.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>656</th>\n",
       "      <td>KFC</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>1244.0</td>\n",
       "      <td>1057.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2301.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>563</th>\n",
       "      <td>McDonald's®</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>1226.0</td>\n",
       "      <td>1023.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2249.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>971</th>\n",
       "      <td>Mamacita - California Burritos</td>\n",
       "      <td>8010</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>1010.0</td>\n",
       "      <td>1067.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2077.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>551</th>\n",
       "      <td>McDonald's®</td>\n",
       "      <td>8010</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>1044.0</td>\n",
       "      <td>977.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2021.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  name   zip                      kpi  \\\n",
       "545                        McDonald's®  8010  # Commissionable Orders   \n",
       "297  Pizzeria Ristorante Da Vinci Linz  8010  # Commissionable Orders   \n",
       "483                      Swing Kitchen  8010  # Commissionable Orders   \n",
       "179                     Restaurant Z10  8010  # Commissionable Orders   \n",
       "144                           Da Piero  8020  # Commissionable Orders   \n",
       "6                           Dolce Vita  8020  # Commissionable Orders   \n",
       "656                                KFC  8020  # Commissionable Orders   \n",
       "563                        McDonald's®  8020  # Commissionable Orders   \n",
       "971     Mamacita - California Burritos  8010  # Commissionable Orders   \n",
       "551                        McDonald's®  8010  # Commissionable Orders   \n",
       "\n",
       "     Month 1  Month 2 delivery service  total_orders  \n",
       "545   2508.0   2038.0      deliverando        4546.0  \n",
       "297   1933.0   2180.0      deliverando        4113.0  \n",
       "483   1982.0   1913.0      deliverando        3895.0  \n",
       "179   1899.0   1859.0      deliverando        3758.0  \n",
       "144   1463.0   1634.0      deliverando        3097.0  \n",
       "6     1178.0   1353.0      deliverando        2531.0  \n",
       "656   1244.0   1057.0      deliverando        2301.0  \n",
       "563   1226.0   1023.0      deliverando        2249.0  \n",
       "971   1010.0   1067.0      deliverando        2077.0  \n",
       "551   1044.0    977.0      deliverando        2021.0  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_commissionable_orders.sort_values(by=['total_orders'], ascending= False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "4939a9f2-3be1-443e-b6cd-8616d66a50f4",
   "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>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>699.0</td>\n",
       "      <td>822.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1521.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Dolce Vita</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>1178.0</td>\n",
       "      <td>1353.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2531.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>China Restaurant Mond</td>\n",
       "      <td>8010</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>230.0</td>\n",
       "      <td>375.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>605.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Royal</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>692.0</td>\n",
       "      <td>734.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1426.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Xpress</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>365.0</td>\n",
       "      <td>410.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>775.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1633</th>\n",
       "      <td>Gasthof zum Bayernbrunnen</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1636</th>\n",
       "      <td>Ucco's</td>\n",
       "      <td>8010</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1639</th>\n",
       "      <td>Wir:zhaus</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1642</th>\n",
       "      <td>Namaste Nepal Restaurant</td>\n",
       "      <td>8010</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1645</th>\n",
       "      <td>Easy Asia</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>335 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           name   zip                      kpi  Month 1  \\\n",
       "0                         Mampf  8020  # Commissionable Orders    699.0   \n",
       "6                    Dolce Vita  8020  # Commissionable Orders   1178.0   \n",
       "12        China Restaurant Mond  8010  # Commissionable Orders    230.0   \n",
       "18                        Royal  8020  # Commissionable Orders    692.0   \n",
       "24                       Xpress  8020  # Commissionable Orders    365.0   \n",
       "...                         ...   ...                      ...      ...   \n",
       "1633  Gasthof zum Bayernbrunnen  8020  # Commissionable Orders      0.0   \n",
       "1636                     Ucco's  8010  # Commissionable Orders      0.0   \n",
       "1639                  Wir:zhaus  8020  # Commissionable Orders      0.0   \n",
       "1642   Namaste Nepal Restaurant  8010  # Commissionable Orders      0.0   \n",
       "1645                  Easy Asia  8020  # Commissionable Orders      0.0   \n",
       "\n",
       "      Month 2 delivery service  total_orders  \n",
       "0       822.0      deliverando        1521.0  \n",
       "6      1353.0      deliverando        2531.0  \n",
       "12      375.0      deliverando         605.0  \n",
       "18      734.0      deliverando        1426.0  \n",
       "24      410.0      deliverando         775.0  \n",
       "...       ...              ...           ...  \n",
       "1633      0.0      deliverando           0.0  \n",
       "1636      0.0      deliverando           0.0  \n",
       "1639      0.0      deliverando           0.0  \n",
       "1642      0.0      deliverando           0.0  \n",
       "1645      0.0      deliverando           0.0  \n",
       "\n",
       "[335 rows x 7 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_commissionable_orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "965f5851-4ec7-4191-9732-6cf4d5e9bd96",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_1_commissionable_orders= data_1_commissionable_orders.astype({'Month 1': int,'Month 2': int,'total_orders': int },copy=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "a064d23b-7947-46dd-b0ce-a49f1de9a591",
   "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>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>545</th>\n",
       "      <td>McDonald's®</td>\n",
       "      <td>8010</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>2508</td>\n",
       "      <td>2038</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>4546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>297</th>\n",
       "      <td>Pizzeria Ristorante Da Vinci Linz</td>\n",
       "      <td>8010</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>1933</td>\n",
       "      <td>2180</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>4113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>483</th>\n",
       "      <td>Swing Kitchen</td>\n",
       "      <td>8010</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>1982</td>\n",
       "      <td>1913</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>3895</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>Restaurant Z10</td>\n",
       "      <td>8010</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>1899</td>\n",
       "      <td>1859</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>3758</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>Da Piero</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>1463</td>\n",
       "      <td>1634</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>3097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>366</th>\n",
       "      <td>Casa Transilvania</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>851</th>\n",
       "      <td>Cipollino - Das Original</td>\n",
       "      <td>8010</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1307</th>\n",
       "      <td>Barabar</td>\n",
       "      <td>8041</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1269</th>\n",
       "      <td>Gasthaus Römerstuben</td>\n",
       "      <td>8041</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1645</th>\n",
       "      <td>Easy Asia</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>335 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   name   zip                      kpi  \\\n",
       "545                         McDonald's®  8010  # Commissionable Orders   \n",
       "297   Pizzeria Ristorante Da Vinci Linz  8010  # Commissionable Orders   \n",
       "483                       Swing Kitchen  8010  # Commissionable Orders   \n",
       "179                      Restaurant Z10  8010  # Commissionable Orders   \n",
       "144                            Da Piero  8020  # Commissionable Orders   \n",
       "...                                 ...   ...                      ...   \n",
       "366                   Casa Transilvania  8020  # Commissionable Orders   \n",
       "851            Cipollino - Das Original  8010  # Commissionable Orders   \n",
       "1307                            Barabar  8041  # Commissionable Orders   \n",
       "1269               Gasthaus Römerstuben  8041  # Commissionable Orders   \n",
       "1645                          Easy Asia  8020  # Commissionable Orders   \n",
       "\n",
       "      Month 1  Month 2 delivery service  total_orders  \n",
       "545      2508     2038      deliverando          4546  \n",
       "297      1933     2180      deliverando          4113  \n",
       "483      1982     1913      deliverando          3895  \n",
       "179      1899     1859      deliverando          3758  \n",
       "144      1463     1634      deliverando          3097  \n",
       "...       ...      ...              ...           ...  \n",
       "366         0        0      deliverando             0  \n",
       "851         0        0      deliverando             0  \n",
       "1307        0        0      deliverando             0  \n",
       "1269        0        0      deliverando             0  \n",
       "1645        0        0      deliverando             0  \n",
       "\n",
       "[335 rows x 7 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_commissionable_orders= data_1_commissionable_orders.sort_values(by=['total_orders'], ascending= False)\n",
    "data_1_commissionable_orders"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21ee90c1-b490-4730-ad26-f109650f7a55",
   "metadata": {},
   "source": [
    "#### kPI 6: Avg Basket Size €- the number of products a customer buys per single transaction. This is calculated as the total number of units sold divided by total transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "b0e1b447-de2f-42ea-af5d-d7bc28b9dd79",
   "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>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>22.50</td>\n",
       "      <td>23.33</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>45.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Dolce Vita</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>19.12</td>\n",
       "      <td>19.95</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>39.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>China Restaurant Mond</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>23.82</td>\n",
       "      <td>24.73</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>48.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Royal</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>24.60</td>\n",
       "      <td>25.68</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>50.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Xpress</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>24.13</td>\n",
       "      <td>24.18</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>48.31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     name   zip                kpi  Month 1  Month 2  \\\n",
       "1                   Mampf  8020  Avg Basket Size €    22.50    23.33   \n",
       "7              Dolce Vita  8020  Avg Basket Size €    19.12    19.95   \n",
       "13  China Restaurant Mond  8010  Avg Basket Size €    23.82    24.73   \n",
       "19                  Royal  8020  Avg Basket Size €    24.60    25.68   \n",
       "25                 Xpress  8020  Avg Basket Size €    24.13    24.18   \n",
       "\n",
       "   delivery service  total_orders  \n",
       "1       deliverando         45.83  \n",
       "7       deliverando         39.07  \n",
       "13      deliverando         48.55  \n",
       "19      deliverando         50.28  \n",
       "25      deliverando         48.31  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_avg_basket_size= data_1.loc[data_1['kpi']=='Avg Basket Size €']\n",
    "data_1_avg_basket_size.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "52b859c4-f25b-4914-9da6-5401d29c2f54",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/yq/6pm1lcds6ln5qlsjc5y8fly00000gn/T/ipykernel_5904/53034846.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  data_1_avg_basket_size['aprox. avg_basket_size']= (data_1_avg_basket_size['Month 1']+data_1_avg_basket_size['Month 2'])/2\n"
     ]
    }
   ],
   "source": [
    "data_1_avg_basket_size['aprox. avg_basket_size']= (data_1_avg_basket_size['Month 1']+data_1_avg_basket_size['Month 2'])/2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "9112507d-89c7-45bf-b901-82e39d180ee6",
   "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>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "      <th>aprox. avg_basket_size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1303</th>\n",
       "      <td>Tonkin</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>36.10</td>\n",
       "      <td>43.10</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>79.20</td>\n",
       "      <td>39.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1364</th>\n",
       "      <td>Gasthof s'Reindl</td>\n",
       "      <td>8055</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>32.03</td>\n",
       "      <td>38.48</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>70.51</td>\n",
       "      <td>35.255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>785</th>\n",
       "      <td>Zu den 3 goldenen Kugeln</td>\n",
       "      <td>8041</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>38.30</td>\n",
       "      <td>28.30</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>66.60</td>\n",
       "      <td>33.300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>Aming Dim Sum Profi</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>31.06</td>\n",
       "      <td>32.16</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>63.22</td>\n",
       "      <td>31.610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>951</th>\n",
       "      <td>Masala</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>31.83</td>\n",
       "      <td>31.10</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>62.93</td>\n",
       "      <td>31.465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>613</th>\n",
       "      <td>Akakiko</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>30.43</td>\n",
       "      <td>31.57</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>62.00</td>\n",
       "      <td>31.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>576</th>\n",
       "      <td>Taj Restaurant</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>30.91</td>\n",
       "      <td>30.16</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>61.07</td>\n",
       "      <td>30.535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>534</th>\n",
       "      <td>Granola</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>29.53</td>\n",
       "      <td>30.88</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>60.41</td>\n",
       "      <td>30.205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>Milu Milu Taiwanesisches Restaurant</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>28.38</td>\n",
       "      <td>30.31</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>58.69</td>\n",
       "      <td>29.345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1292</th>\n",
       "      <td>Osaka -Japan Restaurant</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>28.41</td>\n",
       "      <td>29.64</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>58.05</td>\n",
       "      <td>29.025</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     name   zip                kpi  Month 1  \\\n",
       "1303                               Tonkin  8010  Avg Basket Size €    36.10   \n",
       "1364                     Gasthof s'Reindl  8055  Avg Basket Size €    32.03   \n",
       "785              Zu den 3 goldenen Kugeln  8041  Avg Basket Size €    38.30   \n",
       "97                    Aming Dim Sum Profi  8010  Avg Basket Size €    31.06   \n",
       "951                                Masala  8010  Avg Basket Size €    31.83   \n",
       "613                               Akakiko  8010  Avg Basket Size €    30.43   \n",
       "576                        Taj Restaurant  8010  Avg Basket Size €    30.91   \n",
       "534                               Granola  8020  Avg Basket Size €    29.53   \n",
       "192   Milu Milu Taiwanesisches Restaurant  8010  Avg Basket Size €    28.38   \n",
       "1292              Osaka -Japan Restaurant  8020  Avg Basket Size €    28.41   \n",
       "\n",
       "      Month 2 delivery service  total_orders  aprox. avg_basket_size  \n",
       "1303    43.10      deliverando         79.20                  39.600  \n",
       "1364    38.48      deliverando         70.51                  35.255  \n",
       "785     28.30      deliverando         66.60                  33.300  \n",
       "97      32.16      deliverando         63.22                  31.610  \n",
       "951     31.10      deliverando         62.93                  31.465  \n",
       "613     31.57      deliverando         62.00                  31.000  \n",
       "576     30.16      deliverando         61.07                  30.535  \n",
       "534     30.88      deliverando         60.41                  30.205  \n",
       "192     30.31      deliverando         58.69                  29.345  \n",
       "1292    29.64      deliverando         58.05                  29.025  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_avg_basket_size.sort_values(by=['aprox. avg_basket_size'], ascending= False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "846972ae-97e8-4acd-8aa9-5bfcb688b402",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_1_avg_basket_size= data_1_avg_basket_size.rename(columns={\"total_orders\": \"total_basket_size\"})\n",
    "data_1_avg_basket_size= data_1_avg_basket_size.sort_values(by=['aprox. avg_basket_size'], ascending= False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "c5b0dbe6-f958-44a0-96d1-1223985c94eb",
   "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>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_basket_size</th>\n",
       "      <th>aprox. avg_basket_size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1303</th>\n",
       "      <td>Tonkin</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>36.10</td>\n",
       "      <td>43.10</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>79.20</td>\n",
       "      <td>39.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1364</th>\n",
       "      <td>Gasthof s'Reindl</td>\n",
       "      <td>8055</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>32.03</td>\n",
       "      <td>38.48</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>70.51</td>\n",
       "      <td>35.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>785</th>\n",
       "      <td>Zu den 3 goldenen Kugeln</td>\n",
       "      <td>8041</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>38.30</td>\n",
       "      <td>28.30</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>66.60</td>\n",
       "      <td>33.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>Aming Dim Sum Profi</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>31.06</td>\n",
       "      <td>32.16</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>63.22</td>\n",
       "      <td>31.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>951</th>\n",
       "      <td>Masala</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>31.83</td>\n",
       "      <td>31.10</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>62.93</td>\n",
       "      <td>31.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>Pizzeria Nero &amp; Kebap</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>11.98</td>\n",
       "      <td>9.59</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>21.57</td>\n",
       "      <td>10.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>920</th>\n",
       "      <td>Torino Pizza Kebap Burger</td>\n",
       "      <td>8010</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>10.82</td>\n",
       "      <td>10.51</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>21.33</td>\n",
       "      <td>10.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1082</th>\n",
       "      <td>Akdag Kebap</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>10.06</td>\n",
       "      <td>10.62</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>20.68</td>\n",
       "      <td>10.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>720</th>\n",
       "      <td>Halici - Cafe Kebaphaus</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>9.23</td>\n",
       "      <td>10.90</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>20.13</td>\n",
       "      <td>10.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1340</th>\n",
       "      <td>Europa Kebap Pizza</td>\n",
       "      <td>8020</td>\n",
       "      <td>Avg Basket Size €</td>\n",
       "      <td>10.36</td>\n",
       "      <td>9.28</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>19.64</td>\n",
       "      <td>9.82</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>214 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           name   zip                kpi  Month 1  Month 2  \\\n",
       "1303                     Tonkin  8010  Avg Basket Size €    36.10    43.10   \n",
       "1364           Gasthof s'Reindl  8055  Avg Basket Size €    32.03    38.48   \n",
       "785    Zu den 3 goldenen Kugeln  8041  Avg Basket Size €    38.30    28.30   \n",
       "97          Aming Dim Sum Profi  8010  Avg Basket Size €    31.06    32.16   \n",
       "951                      Masala  8010  Avg Basket Size €    31.83    31.10   \n",
       "...                         ...   ...                ...      ...      ...   \n",
       "996       Pizzeria Nero & Kebap  8020  Avg Basket Size €    11.98     9.59   \n",
       "920   Torino Pizza Kebap Burger  8010  Avg Basket Size €    10.82    10.51   \n",
       "1082                Akdag Kebap  8020  Avg Basket Size €    10.06    10.62   \n",
       "720     Halici - Cafe Kebaphaus  8020  Avg Basket Size €     9.23    10.90   \n",
       "1340         Europa Kebap Pizza  8020  Avg Basket Size €    10.36     9.28   \n",
       "\n",
       "     delivery service  total_basket_size  aprox. avg_basket_size  \n",
       "1303      deliverando              79.20                   39.60  \n",
       "1364      deliverando              70.51                   35.25  \n",
       "785       deliverando              66.60                   33.30  \n",
       "97        deliverando              63.22                   31.61  \n",
       "951       deliverando              62.93                   31.46  \n",
       "...               ...                ...                     ...  \n",
       "996       deliverando              21.57                   10.78  \n",
       "920       deliverando              21.33                   10.66  \n",
       "1082      deliverando              20.68                   10.34  \n",
       "720       deliverando              20.13                   10.07  \n",
       "1340      deliverando              19.64                    9.82  \n",
       "\n",
       "[214 rows x 8 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_avg_basket_size['aprox. avg_basket_size']= round(data_1_avg_basket_size['aprox. avg_basket_size'],2)\n",
    "data_1_avg_basket_size"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19f586d5-ef93-422e-b6bc-a382c18839b8",
   "metadata": {},
   "source": [
    "#### kPI 7: Graz Relevant zip codes 8010, 8020, 8036, 8041, 8042, 8043, 8045, 8051, 8052, 8053,8054, 8055, 8063, 8077"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "e06164ac-ca43-4714-939c-99283d3bb853",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([8020, 8010, 8053, 8051, 8042, 8054, 8043, 8052, 8041, 8045, 8055])"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1['zip'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "8c545fa6-25f3-4d6a-a248-c1d710d528f2",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Not present in deliverando= 8036, 8063, 8077"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "c18d9f3f-3b1f-4281-9ce2-d9e68a12e46d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['name', 'zip', 'kpi', 'Month 1', 'Month 2', 'delivery service',\n",
       "       'total_orders'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_orders.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "d444973d-a78b-4d8e-bc80-b14527de2826",
   "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>name</th>\n",
       "      <th>zip</th>\n",
       "      <th>kpi</th>\n",
       "      <th>Month 1</th>\n",
       "      <th>Month 2</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mampf</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>699.0</td>\n",
       "      <td>822.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1521.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Dolce Vita</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>1178.0</td>\n",
       "      <td>1353.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2531.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>China Restaurant Mond</td>\n",
       "      <td>8010</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>230.0</td>\n",
       "      <td>375.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>605.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Royal</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>692.0</td>\n",
       "      <td>734.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1426.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Xpress</td>\n",
       "      <td>8020</td>\n",
       "      <td># Commissionable Orders</td>\n",
       "      <td>365.0</td>\n",
       "      <td>410.0</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>775.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     name   zip                      kpi  Month 1  Month 2  \\\n",
       "0                   Mampf  8020  # Commissionable Orders    699.0    822.0   \n",
       "6              Dolce Vita  8020  # Commissionable Orders   1178.0   1353.0   \n",
       "12  China Restaurant Mond  8010  # Commissionable Orders    230.0    375.0   \n",
       "18                  Royal  8020  # Commissionable Orders    692.0    734.0   \n",
       "24                 Xpress  8020  # Commissionable Orders    365.0    410.0   \n",
       "\n",
       "   delivery service  total_orders  \n",
       "0       deliverando        1521.0  \n",
       "6       deliverando        2531.0  \n",
       "12      deliverando         605.0  \n",
       "18      deliverando        1426.0  \n",
       "24      deliverando         775.0  "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_orders.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "72d01718-9956-4f34-bab4-e4fce88b55d2",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>zip</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders_deliverando</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8020</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>8020</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>8010</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>8020</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>8020</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1633</th>\n",
       "      <td>8020</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1636</th>\n",
       "      <td>8010</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1639</th>\n",
       "      <td>8020</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1642</th>\n",
       "      <td>8010</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1645</th>\n",
       "      <td>8020</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>335 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       zip delivery service  total_orders_deliverando\n",
       "0     8020      deliverando                      1521\n",
       "6     8020      deliverando                      2531\n",
       "12    8010      deliverando                       605\n",
       "18    8020      deliverando                      1426\n",
       "24    8020      deliverando                       775\n",
       "...    ...              ...                       ...\n",
       "1633  8020      deliverando                         0\n",
       "1636  8010      deliverando                         0\n",
       "1639  8020      deliverando                         0\n",
       "1642  8010      deliverando                         0\n",
       "1645  8020      deliverando                         0\n",
       "\n",
       "[335 rows x 3 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_orders_2=  data_1_orders.rename(columns={\"total_orders\": \"total_orders_deliverando\"})\n",
    "data_1_orders_2 = data_1_orders_2.drop(['name', 'kpi','Month 1','Month 2'], axis=1)\n",
    "data_1_orders_2= data_1_orders_2.astype({'total_orders_deliverando': int },copy=False)\n",
    "data_1_orders_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "b26ef6a9-254a-4046-81ae-213341972c27",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "109095.0"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_deliverando_orders= data_1_orders['total_orders'].sum()\n",
    "total_deliverando_orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "cf30a09c-a2ca-4530-b09f-05c84e45d611",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[8020, 8010, 8053, 8051, 8042, 8054, 8043, 8052, 8041, 8045, 8055]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_orders_2_zip_unique= data_1_orders_2['zip'].unique().tolist()\n",
    "data_1_orders_2_zip_unique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "c900d839-8d7a-4964-b852-eb9a046e6af2",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>zip</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8010</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>59857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8020</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>30507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8041</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8042</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>3254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8043</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>8045</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>8051</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8052</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8053</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>3064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>8054</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>3594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>8055</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2276</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     zip delivery service  total_orders\n",
       "0   8010      deliverando         59857\n",
       "1   8020      deliverando         30507\n",
       "2   8041      deliverando          1186\n",
       "3   8042      deliverando          3254\n",
       "4   8043      deliverando           540\n",
       "5   8045      deliverando           957\n",
       "6   8051      deliverando          2486\n",
       "7   8052      deliverando          1374\n",
       "8   8053      deliverando          3064\n",
       "9   8054      deliverando          3594\n",
       "10  8055      deliverando          2276"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_orders_3= data_1_orders_2.groupby(['zip','delivery service']).agg('sum')\n",
    "data_1_orders_3= data_1_orders_3.rename(columns={\"total_orders_deliverando\": \"total_orders\"})\n",
    "data_1_orders_3= data_1_orders_3.reset_index()\n",
    "data_1_orders_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "ff153d94-2ac4-46e9-afd2-fa90ec1363bd",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>zip</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "      <th>%_from_total_orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8010</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>59857</td>\n",
       "      <td>54.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8020</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>30507</td>\n",
       "      <td>27.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8041</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1186</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8042</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>3254</td>\n",
       "      <td>2.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8043</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>540</td>\n",
       "      <td>0.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>8045</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>957</td>\n",
       "      <td>0.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>8051</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2486</td>\n",
       "      <td>2.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8052</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1374</td>\n",
       "      <td>1.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8053</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>3064</td>\n",
       "      <td>2.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>8054</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>3594</td>\n",
       "      <td>3.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>8055</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2276</td>\n",
       "      <td>2.09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     zip delivery service  total_orders  %_from_total_orders\n",
       "0   8010      deliverando         59857                54.87\n",
       "1   8020      deliverando         30507                27.96\n",
       "2   8041      deliverando          1186                 1.09\n",
       "3   8042      deliverando          3254                 2.98\n",
       "4   8043      deliverando           540                 0.49\n",
       "5   8045      deliverando           957                 0.88\n",
       "6   8051      deliverando          2486                 2.28\n",
       "7   8052      deliverando          1374                 1.26\n",
       "8   8053      deliverando          3064                 2.81\n",
       "9   8054      deliverando          3594                 3.29\n",
       "10  8055      deliverando          2276                 2.09"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1_orders_3['%_from_total_orders']= round((data_1_orders_3['total_orders'] / total_deliverando_orders)* 100, 2)\n",
    "data_1_orders_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "8109eb63-d581-49d4-b6a0-80820a737ac5",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>zip</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders_competitors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1120</td>\n",
       "      <td>competition</td>\n",
       "      <td>1001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4614</td>\n",
       "      <td>competition</td>\n",
       "      <td>434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1010</td>\n",
       "      <td>competition</td>\n",
       "      <td>122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4053</td>\n",
       "      <td>competition</td>\n",
       "      <td>2102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9020</td>\n",
       "      <td>competition</td>\n",
       "      <td>324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3301</th>\n",
       "      <td>1060</td>\n",
       "      <td>competition</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3302</th>\n",
       "      <td>1190</td>\n",
       "      <td>competition</td>\n",
       "      <td>343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3303</th>\n",
       "      <td>1150</td>\n",
       "      <td>competition</td>\n",
       "      <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3304</th>\n",
       "      <td>1220</td>\n",
       "      <td>competition</td>\n",
       "      <td>1671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3305</th>\n",
       "      <td>5020</td>\n",
       "      <td>competition</td>\n",
       "      <td>2152</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3306 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       zip delivery service  total_orders_competitors\n",
       "0     1120      competition                      1001\n",
       "1     4614      competition                       434\n",
       "2     1010      competition                       122\n",
       "3     4053      competition                      2102\n",
       "4     9020      competition                       324\n",
       "...    ...              ...                       ...\n",
       "3301  1060      competition                        11\n",
       "3302  1190      competition                       343\n",
       "3303  1150      competition                       183\n",
       "3304  1220      competition                      1671\n",
       "3305  5020      competition                      2152\n",
       "\n",
       "[3306 rows x 3 columns]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "competitors_data_2= competitors_data.rename(columns={\"total_orders\": \"total_orders_competitors\"})\n",
    "competitors_data_2= competitors_data_2.drop(['name','orders_x','month_x','delivery_service_x','orders_y','month_y'], axis=1)\n",
    "competitors_data_2= competitors_data_2.rename(columns={\"delivery_service_y\": \"delivery service\"})\n",
    "competitors_data_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "2486d041-e8b3-40bf-9b7f-bb4c0297aa73",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2192460"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_competitors_orders= competitors_data_2['total_orders_competitors'].sum()\n",
    "total_competitors_orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "0e34c2e3-1448-406a-8bcc-ff278f0d6c5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "competitors_data_2['%_from_total_orders']= round((competitors_data_2['total_orders_competitors'] / total_competitors_orders)* 100, 2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "34526bad-6627-4ea3-adc1-1ef8975eab66",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "99.07000000000001"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "competitors_data_2['%_from_total_orders'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "70b858f6-5c96-4031-bcc6-1d44a11266d7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "448"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(competitors_data_2['zip'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "8a4706da-f523-4e6e-88c9-027db8f68c3f",
   "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>zip</th>\n",
       "      <th>total_orders</th>\n",
       "      <th>%_from_total_orders</th>\n",
       "      <th>delivery service</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1010</td>\n",
       "      <td>37130</td>\n",
       "      <td>1.61</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1020</td>\n",
       "      <td>53937</td>\n",
       "      <td>2.39</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1030</td>\n",
       "      <td>67875</td>\n",
       "      <td>3.05</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1040</td>\n",
       "      <td>14414</td>\n",
       "      <td>0.63</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1050</td>\n",
       "      <td>35562</td>\n",
       "      <td>1.56</td>\n",
       "      <td>competition</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>443</th>\n",
       "      <td>9565</td>\n",
       "      <td>14</td>\n",
       "      <td>0.00</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>444</th>\n",
       "      <td>9620</td>\n",
       "      <td>424</td>\n",
       "      <td>0.02</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>445</th>\n",
       "      <td>9800</td>\n",
       "      <td>147</td>\n",
       "      <td>0.00</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>446</th>\n",
       "      <td>9813</td>\n",
       "      <td>926</td>\n",
       "      <td>0.04</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>447</th>\n",
       "      <td>9900</td>\n",
       "      <td>22</td>\n",
       "      <td>0.00</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>448 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      zip  total_orders  %_from_total_orders delivery service\n",
       "0    1010         37130                 1.61      competition\n",
       "1    1020         53937                 2.39      competition\n",
       "2    1030         67875                 3.05      competition\n",
       "3    1040         14414                 0.63      competition\n",
       "4    1050         35562                 1.56      competition\n",
       "..    ...           ...                  ...              ...\n",
       "443  9565            14                 0.00      competition\n",
       "444  9620           424                 0.02      competition\n",
       "445  9800           147                 0.00      competition\n",
       "446  9813           926                 0.04      competition\n",
       "447  9900            22                 0.00      competition\n",
       "\n",
       "[448 rows x 4 columns]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "competitors_data_3= competitors_data_2[['zip', 'total_orders_competitors','%_from_total_orders']].groupby(['zip']).agg('sum')\n",
    "competitors_data_3= competitors_data_3.rename(columns={\"total_orders_competitors\": \"total_orders\"})\n",
    "competitors_data_3['delivery service']= 'competition'\n",
    "competitors_data_3= competitors_data_3.reset_index()\n",
    "competitors_data_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "55bcb827-f817-4d5e-93b2-b5d1484f67b3",
   "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>zip</th>\n",
       "      <th>total_orders</th>\n",
       "      <th>%_from_total_orders</th>\n",
       "      <th>delivery service</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>238</th>\n",
       "      <td>5020</td>\n",
       "      <td>107730</td>\n",
       "      <td>4.84</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269</th>\n",
       "      <td>6020</td>\n",
       "      <td>91809</td>\n",
       "      <td>4.18</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1100</td>\n",
       "      <td>90085</td>\n",
       "      <td>4.00</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>4020</td>\n",
       "      <td>89107</td>\n",
       "      <td>4.02</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1220</td>\n",
       "      <td>76391</td>\n",
       "      <td>3.53</td>\n",
       "      <td>competition</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>363</th>\n",
       "      <td>8076</td>\n",
       "      <td>15</td>\n",
       "      <td>0.00</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>443</th>\n",
       "      <td>9565</td>\n",
       "      <td>14</td>\n",
       "      <td>0.00</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>2651</td>\n",
       "      <td>10</td>\n",
       "      <td>0.00</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>427</th>\n",
       "      <td>8992</td>\n",
       "      <td>8</td>\n",
       "      <td>0.00</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>314</th>\n",
       "      <td>6719</td>\n",
       "      <td>8</td>\n",
       "      <td>0.00</td>\n",
       "      <td>competition</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>448 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      zip  total_orders  %_from_total_orders delivery service\n",
       "238  5020        107730                 4.84      competition\n",
       "269  6020         91809                 4.18      competition\n",
       "9    1100         90085                 4.00      competition\n",
       "180  4020         89107                 4.02      competition\n",
       "21   1220         76391                 3.53      competition\n",
       "..    ...           ...                  ...              ...\n",
       "363  8076            15                 0.00      competition\n",
       "443  9565            14                 0.00      competition\n",
       "101  2651            10                 0.00      competition\n",
       "427  8992             8                 0.00      competition\n",
       "314  6719             8                 0.00      competition\n",
       "\n",
       "[448 rows x 4 columns]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "competitors_zip_ranked= competitors_data_3.sort_values(by=['total_orders'], ascending= False)\n",
    "competitors_zip_ranked"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "185f97df-cc6b-4c6e-a33e-c7ebd7568577",
   "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>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "      <th>%_from_total_orders</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>zip</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9900</th>\n",
       "      <td>competition</td>\n",
       "      <td>22</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9813</th>\n",
       "      <td>competition</td>\n",
       "      <td>926</td>\n",
       "      <td>0.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9800</th>\n",
       "      <td>competition</td>\n",
       "      <td>147</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9620</th>\n",
       "      <td>competition</td>\n",
       "      <td>424</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9565</th>\n",
       "      <td>competition</td>\n",
       "      <td>14</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1050</th>\n",
       "      <td>competition</td>\n",
       "      <td>35562</td>\n",
       "      <td>1.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1040</th>\n",
       "      <td>competition</td>\n",
       "      <td>14414</td>\n",
       "      <td>0.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1030</th>\n",
       "      <td>competition</td>\n",
       "      <td>67875</td>\n",
       "      <td>3.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1020</th>\n",
       "      <td>competition</td>\n",
       "      <td>53937</td>\n",
       "      <td>2.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1010</th>\n",
       "      <td>competition</td>\n",
       "      <td>37130</td>\n",
       "      <td>1.61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>459 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     delivery service  total_orders  %_from_total_orders\n",
       "zip                                                     \n",
       "9900      competition            22                 0.00\n",
       "9813      competition           926                 0.04\n",
       "9800      competition           147                 0.00\n",
       "9620      competition           424                 0.02\n",
       "9565      competition            14                 0.00\n",
       "...               ...           ...                  ...\n",
       "1050      competition         35562                 1.56\n",
       "1040      competition         14414                 0.63\n",
       "1030      competition         67875                 3.05\n",
       "1020      competition         53937                 2.39\n",
       "1010      competition         37130                 1.61\n",
       "\n",
       "[459 rows x 3 columns]"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zip_data= pd.concat([data_1_orders_3,competitors_data_3])\n",
    "zip_data\n",
    "zip_data= zip_data.sort_values(by=['zip'], ascending= False)\n",
    "zip_data.set_index('zip', inplace=True)\n",
    "zip_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "d12864a6-56b3-4272-a521-62652e8f2df9",
   "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>zip</th>\n",
       "      <th>delivery service</th>\n",
       "      <th>total_orders</th>\n",
       "      <th>%_from_total_orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8020</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>30507</td>\n",
       "      <td>27.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8020</td>\n",
       "      <td>competition</td>\n",
       "      <td>46109</td>\n",
       "      <td>2.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8010</td>\n",
       "      <td>competition</td>\n",
       "      <td>74550</td>\n",
       "      <td>3.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8010</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>59857</td>\n",
       "      <td>54.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8053</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>3064</td>\n",
       "      <td>2.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>8053</td>\n",
       "      <td>competition</td>\n",
       "      <td>3746</td>\n",
       "      <td>0.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>8051</td>\n",
       "      <td>competition</td>\n",
       "      <td>9042</td>\n",
       "      <td>0.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8051</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2486</td>\n",
       "      <td>2.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8042</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>3254</td>\n",
       "      <td>2.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>8042</td>\n",
       "      <td>competition</td>\n",
       "      <td>4839</td>\n",
       "      <td>0.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>8054</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>3594</td>\n",
       "      <td>3.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>8054</td>\n",
       "      <td>competition</td>\n",
       "      <td>9190</td>\n",
       "      <td>0.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>8043</td>\n",
       "      <td>competition</td>\n",
       "      <td>508</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>8043</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>540</td>\n",
       "      <td>0.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>8052</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1374</td>\n",
       "      <td>1.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>8052</td>\n",
       "      <td>competition</td>\n",
       "      <td>3273</td>\n",
       "      <td>0.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>8041</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>1186</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>8041</td>\n",
       "      <td>competition</td>\n",
       "      <td>8159</td>\n",
       "      <td>0.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>8045</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>957</td>\n",
       "      <td>0.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>8045</td>\n",
       "      <td>competition</td>\n",
       "      <td>5083</td>\n",
       "      <td>0.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>8055</td>\n",
       "      <td>competition</td>\n",
       "      <td>10763</td>\n",
       "      <td>0.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>8055</td>\n",
       "      <td>deliverando</td>\n",
       "      <td>2276</td>\n",
       "      <td>2.09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     zip delivery service  total_orders  %_from_total_orders\n",
       "0   8020      deliverando         30507                27.96\n",
       "1   8020      competition         46109                 2.09\n",
       "2   8010      competition         74550                 3.43\n",
       "3   8010      deliverando         59857                54.87\n",
       "4   8053      deliverando          3064                 2.81\n",
       "5   8053      competition          3746                 0.17\n",
       "6   8051      competition          9042                 0.40\n",
       "7   8051      deliverando          2486                 2.28\n",
       "8   8042      deliverando          3254                 2.98\n",
       "9   8042      competition          4839                 0.22\n",
       "10  8054      deliverando          3594                 3.29\n",
       "11  8054      competition          9190                 0.41\n",
       "12  8043      competition           508                 0.02\n",
       "13  8043      deliverando           540                 0.49\n",
       "14  8052      deliverando          1374                 1.26\n",
       "15  8052      competition          3273                 0.15\n",
       "16  8041      deliverando          1186                 1.09\n",
       "17  8041      competition          8159                 0.37\n",
       "18  8045      deliverando           957                 0.88\n",
       "19  8045      competition          5083                 0.22\n",
       "20  8055      competition         10763                 0.49\n",
       "21  8055      deliverando          2276                 2.09"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_zip= zip_data.loc[data_1_orders_2_zip_unique].reset_index()\n",
    "final_zip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f3375ae-fc6d-4ee2-9132-7a408fe67fc4",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}