data-exploration.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "cd06f86c-b5ae-4e0e-946f-acf3cb0223f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "96210a7e-dabc-4a5a-ad51-335dcef90411",
   "metadata": {},
   "outputs": [],
   "source": [
    "pharma_df = pd.read_csv(\"pharma-data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "917b1ea3-eef4-44b4-96fb-10d8b34afa4e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(254082, 18)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pharma_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a3585d51-4af4-45d4-9eeb-977c3a9b3762",
   "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>Distributor</th>\n",
       "      <th>Customer Name</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Channel</th>\n",
       "      <th>Sub-channel</th>\n",
       "      <th>Product Name</th>\n",
       "      <th>Product Class</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Price</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Month</th>\n",
       "      <th>Year</th>\n",
       "      <th>Name of Sales Rep</th>\n",
       "      <th>Manager</th>\n",
       "      <th>Sales Team</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Gottlieb-Cruickshank</td>\n",
       "      <td>Zieme, Doyle and Kunze</td>\n",
       "      <td>Lublin</td>\n",
       "      <td>Poland</td>\n",
       "      <td>51.2333</td>\n",
       "      <td>22.5667</td>\n",
       "      <td>Hospital</td>\n",
       "      <td>Private</td>\n",
       "      <td>Topipizole</td>\n",
       "      <td>Mood Stabilizers</td>\n",
       "      <td>4.0</td>\n",
       "      <td>368</td>\n",
       "      <td>1472.0</td>\n",
       "      <td>January</td>\n",
       "      <td>2018</td>\n",
       "      <td>Mary Gerrard</td>\n",
       "      <td>Britanny Bold</td>\n",
       "      <td>Delta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Gottlieb-Cruickshank</td>\n",
       "      <td>Feest PLC</td>\n",
       "      <td>Świecie</td>\n",
       "      <td>Poland</td>\n",
       "      <td>53.4167</td>\n",
       "      <td>18.4333</td>\n",
       "      <td>Pharmacy</td>\n",
       "      <td>Retail</td>\n",
       "      <td>Choriotrisin</td>\n",
       "      <td>Antibiotics</td>\n",
       "      <td>7.0</td>\n",
       "      <td>591</td>\n",
       "      <td>4137.0</td>\n",
       "      <td>January</td>\n",
       "      <td>2018</td>\n",
       "      <td>Jessica Smith</td>\n",
       "      <td>Britanny Bold</td>\n",
       "      <td>Delta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Gottlieb-Cruickshank</td>\n",
       "      <td>Medhurst-Beer Pharmaceutical Limited</td>\n",
       "      <td>Rybnik</td>\n",
       "      <td>Poland</td>\n",
       "      <td>50.0833</td>\n",
       "      <td>18.5000</td>\n",
       "      <td>Pharmacy</td>\n",
       "      <td>Institution</td>\n",
       "      <td>Acantaine</td>\n",
       "      <td>Antibiotics</td>\n",
       "      <td>30.0</td>\n",
       "      <td>66</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>January</td>\n",
       "      <td>2018</td>\n",
       "      <td>Steve Pepple</td>\n",
       "      <td>Tracy Banks</td>\n",
       "      <td>Bravo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Gottlieb-Cruickshank</td>\n",
       "      <td>Barton Ltd Pharma Plc</td>\n",
       "      <td>Czeladź</td>\n",
       "      <td>Poland</td>\n",
       "      <td>50.3333</td>\n",
       "      <td>19.0833</td>\n",
       "      <td>Hospital</td>\n",
       "      <td>Private</td>\n",
       "      <td>Lioletine Refliruvax</td>\n",
       "      <td>Analgesics</td>\n",
       "      <td>6.0</td>\n",
       "      <td>435</td>\n",
       "      <td>2610.0</td>\n",
       "      <td>January</td>\n",
       "      <td>2018</td>\n",
       "      <td>Mary Gerrard</td>\n",
       "      <td>Britanny Bold</td>\n",
       "      <td>Delta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Gottlieb-Cruickshank</td>\n",
       "      <td>Keeling LLC Pharmacy</td>\n",
       "      <td>Olsztyn</td>\n",
       "      <td>Poland</td>\n",
       "      <td>53.7800</td>\n",
       "      <td>20.4942</td>\n",
       "      <td>Pharmacy</td>\n",
       "      <td>Retail</td>\n",
       "      <td>Oxymotroban Fexoformin</td>\n",
       "      <td>Analgesics</td>\n",
       "      <td>20.0</td>\n",
       "      <td>458</td>\n",
       "      <td>9160.0</td>\n",
       "      <td>January</td>\n",
       "      <td>2018</td>\n",
       "      <td>Anne Wu</td>\n",
       "      <td>Britanny Bold</td>\n",
       "      <td>Delta</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Distributor                         Customer Name     City  \\\n",
       "0  Gottlieb-Cruickshank                 Zieme, Doyle and Kunze    Lublin   \n",
       "1  Gottlieb-Cruickshank                              Feest PLC   Świecie   \n",
       "2  Gottlieb-Cruickshank    Medhurst-Beer Pharmaceutical Limited   Rybnik   \n",
       "3  Gottlieb-Cruickshank                   Barton Ltd Pharma Plc  Czeladź   \n",
       "4  Gottlieb-Cruickshank                    Keeling LLC Pharmacy  Olsztyn   \n",
       "\n",
       "  Country  Latitude  Longitude   Channel  Sub-channel            Product Name  \\\n",
       "0  Poland   51.2333    22.5667  Hospital      Private              Topipizole   \n",
       "1  Poland   53.4167    18.4333  Pharmacy       Retail            Choriotrisin   \n",
       "2  Poland   50.0833    18.5000  Pharmacy  Institution               Acantaine   \n",
       "3  Poland   50.3333    19.0833  Hospital      Private    Lioletine Refliruvax   \n",
       "4  Poland   53.7800    20.4942  Pharmacy       Retail  Oxymotroban Fexoformin   \n",
       "\n",
       "      Product Class  Quantity  Price   Sales    Month  Year Name of Sales Rep  \\\n",
       "0  Mood Stabilizers       4.0    368  1472.0  January  2018      Mary Gerrard   \n",
       "1       Antibiotics       7.0    591  4137.0  January  2018     Jessica Smith   \n",
       "2       Antibiotics      30.0     66  1980.0  January  2018      Steve Pepple   \n",
       "3        Analgesics       6.0    435  2610.0  January  2018      Mary Gerrard   \n",
       "4        Analgesics      20.0    458  9160.0  January  2018           Anne Wu   \n",
       "\n",
       "         Manager Sales Team  \n",
       "0  Britanny Bold      Delta  \n",
       "1  Britanny Bold      Delta  \n",
       "2    Tracy Banks      Bravo  \n",
       "3  Britanny Bold      Delta  \n",
       "4  Britanny Bold      Delta  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pharma_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a409aaf3-d761-4951-8343-720152064c1c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 254082 entries, 0 to 254081\n",
      "Data columns (total 18 columns):\n",
      " #   Column             Non-Null Count   Dtype  \n",
      "---  ------             --------------   -----  \n",
      " 0   Distributor        254082 non-null  object \n",
      " 1   Customer Name      254082 non-null  object \n",
      " 2   City               254082 non-null  object \n",
      " 3   Country            254082 non-null  object \n",
      " 4   Latitude           254082 non-null  float64\n",
      " 5   Longitude          254082 non-null  float64\n",
      " 6   Channel            254082 non-null  object \n",
      " 7   Sub-channel        254082 non-null  object \n",
      " 8   Product Name       254082 non-null  object \n",
      " 9   Product Class      254082 non-null  object \n",
      " 10  Quantity           254082 non-null  float64\n",
      " 11  Price              254082 non-null  int64  \n",
      " 12  Sales              254082 non-null  float64\n",
      " 13  Month              254082 non-null  object \n",
      " 14  Year               254082 non-null  int64  \n",
      " 15  Name of Sales Rep  254082 non-null  object \n",
      " 16  Manager            254082 non-null  object \n",
      " 17  Sales Team         254082 non-null  object \n",
      "dtypes: float64(4), int64(2), object(12)\n",
      "memory usage: 34.9+ MB\n"
     ]
    }
   ],
   "source": [
    "pharma_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "e7dc5971-cb07-43f3-a58e-e501cf9bf7bf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pharma_df.isna().sum().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "7024bd9e-b61b-44bd-898f-21713dc91456",
   "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>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Price</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>254082.000000</td>\n",
       "      <td>254082.000000</td>\n",
       "      <td>254082.000000</td>\n",
       "      <td>254082.000000</td>\n",
       "      <td>2.540820e+05</td>\n",
       "      <td>254082.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>50.962222</td>\n",
       "      <td>10.803212</td>\n",
       "      <td>112.872139</td>\n",
       "      <td>412.207720</td>\n",
       "      <td>4.643772e+04</td>\n",
       "      <td>2018.385187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1.625526</td>\n",
       "      <td>4.143311</td>\n",
       "      <td>744.310385</td>\n",
       "      <td>224.963687</td>\n",
       "      <td>3.491918e+05</td>\n",
       "      <td>1.041352</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>47.514200</td>\n",
       "      <td>6.083800</td>\n",
       "      <td>-7200.000000</td>\n",
       "      <td>22.000000</td>\n",
       "      <td>-4.161600e+06</td>\n",
       "      <td>2017.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>49.805600</td>\n",
       "      <td>7.891100</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>195.000000</td>\n",
       "      <td>1.704000e+03</td>\n",
       "      <td>2018.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>51.133300</td>\n",
       "      <td>9.397800</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>430.000000</td>\n",
       "      <td>5.850000e+03</td>\n",
       "      <td>2018.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>52.083300</td>\n",
       "      <td>12.133300</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>605.000000</td>\n",
       "      <td>2.156525e+04</td>\n",
       "      <td>2019.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>54.781900</td>\n",
       "      <td>23.566700</td>\n",
       "      <td>117600.000000</td>\n",
       "      <td>794.000000</td>\n",
       "      <td>7.420560e+07</td>\n",
       "      <td>2020.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Latitude      Longitude       Quantity          Price  \\\n",
       "count  254082.000000  254082.000000  254082.000000  254082.000000   \n",
       "mean       50.962222      10.803212     112.872139     412.207720   \n",
       "std         1.625526       4.143311     744.310385     224.963687   \n",
       "min        47.514200       6.083800   -7200.000000      22.000000   \n",
       "25%        49.805600       7.891100       5.000000     195.000000   \n",
       "50%        51.133300       9.397800      20.000000     430.000000   \n",
       "75%        52.083300      12.133300      50.000000     605.000000   \n",
       "max        54.781900      23.566700  117600.000000     794.000000   \n",
       "\n",
       "              Sales           Year  \n",
       "count  2.540820e+05  254082.000000  \n",
       "mean   4.643772e+04    2018.385187  \n",
       "std    3.491918e+05       1.041352  \n",
       "min   -4.161600e+06    2017.000000  \n",
       "25%    1.704000e+03    2018.000000  \n",
       "50%    5.850000e+03    2018.000000  \n",
       "75%    2.156525e+04    2019.000000  \n",
       "max    7.420560e+07    2020.000000  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Numeric cols - measures (PowerBI uses measures nomenclature bit differently)\n",
    "pharma_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "796b4811-d7e3-4773-9e0e-bc68a182a9d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "pharma_df['calc_amount'] = pharma_df[\"Quantity\"] * pharma_df[\"Price\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "c6da7025-0f09-420c-ae00-e54105f016bb",
   "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>Distributor</th>\n",
       "      <th>Customer Name</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Channel</th>\n",
       "      <th>Sub-channel</th>\n",
       "      <th>Product Name</th>\n",
       "      <th>Product Class</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Price</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Month</th>\n",
       "      <th>Year</th>\n",
       "      <th>Name of Sales Rep</th>\n",
       "      <th>Manager</th>\n",
       "      <th>Sales Team</th>\n",
       "      <th>calc_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3761</th>\n",
       "      <td>Rohan</td>\n",
       "      <td>Zieme, Doyle and Kunze</td>\n",
       "      <td>Lublin</td>\n",
       "      <td>Poland</td>\n",
       "      <td>51.2333</td>\n",
       "      <td>22.5667</td>\n",
       "      <td>Hospital</td>\n",
       "      <td>Private</td>\n",
       "      <td>Exotropin Empizine</td>\n",
       "      <td>Mood Stabilizers</td>\n",
       "      <td>-19.0</td>\n",
       "      <td>785</td>\n",
       "      <td>-14915.0</td>\n",
       "      <td>February</td>\n",
       "      <td>2018</td>\n",
       "      <td>Anne Wu</td>\n",
       "      <td>Britanny Bold</td>\n",
       "      <td>Delta</td>\n",
       "      <td>-14915.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3762</th>\n",
       "      <td>Rohan</td>\n",
       "      <td>Nader-Gaylord Pharmaceutical Limited</td>\n",
       "      <td>Sobótka</td>\n",
       "      <td>Poland</td>\n",
       "      <td>50.8986</td>\n",
       "      <td>16.7444</td>\n",
       "      <td>Hospital</td>\n",
       "      <td>Private</td>\n",
       "      <td>Epzipitant</td>\n",
       "      <td>Analgesics</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>778</td>\n",
       "      <td>-9336.0</td>\n",
       "      <td>February</td>\n",
       "      <td>2018</td>\n",
       "      <td>Thompson Crawford</td>\n",
       "      <td>James Goodwill</td>\n",
       "      <td>Alfa</td>\n",
       "      <td>-9336.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18526</th>\n",
       "      <td>Rohan</td>\n",
       "      <td>Buckridge, Dach and Carroll Pharmaceutical Lim...</td>\n",
       "      <td>Luboń</td>\n",
       "      <td>Poland</td>\n",
       "      <td>52.3333</td>\n",
       "      <td>16.8833</td>\n",
       "      <td>Hospital</td>\n",
       "      <td>Private</td>\n",
       "      <td>Belavarix Benzabicin</td>\n",
       "      <td>Antibiotics</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>131</td>\n",
       "      <td>-131.0</td>\n",
       "      <td>November</td>\n",
       "      <td>2018</td>\n",
       "      <td>Morris Garcia</td>\n",
       "      <td>Tracy Banks</td>\n",
       "      <td>Bravo</td>\n",
       "      <td>-131.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22613</th>\n",
       "      <td>Prohaska-Kuhic</td>\n",
       "      <td>Wiegand, Jast and Yost Pharma Plc</td>\n",
       "      <td>Płock</td>\n",
       "      <td>Poland</td>\n",
       "      <td>52.5500</td>\n",
       "      <td>19.7000</td>\n",
       "      <td>Hospital</td>\n",
       "      <td>Private</td>\n",
       "      <td>Afinitasol</td>\n",
       "      <td>Antipiretics</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>286</td>\n",
       "      <td>-1430.0</td>\n",
       "      <td>February</td>\n",
       "      <td>2018</td>\n",
       "      <td>Jessica Smith</td>\n",
       "      <td>Britanny Bold</td>\n",
       "      <td>Delta</td>\n",
       "      <td>-1430.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22751</th>\n",
       "      <td>Prohaska-Kuhic</td>\n",
       "      <td>Pfeffer-Hodkiewicz Pharmaceutical Ltd</td>\n",
       "      <td>Szczytno</td>\n",
       "      <td>Poland</td>\n",
       "      <td>53.5628</td>\n",
       "      <td>20.9853</td>\n",
       "      <td>Hospital</td>\n",
       "      <td>Government</td>\n",
       "      <td>Sucprine Specbalamin</td>\n",
       "      <td>Antiseptics</td>\n",
       "      <td>-50.0</td>\n",
       "      <td>182</td>\n",
       "      <td>-9100.0</td>\n",
       "      <td>March</td>\n",
       "      <td>2018</td>\n",
       "      <td>Jimmy Grey</td>\n",
       "      <td>Alisha Cordwell</td>\n",
       "      <td>Charlie</td>\n",
       "      <td>-9100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <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>253760</th>\n",
       "      <td>Koss</td>\n",
       "      <td>Murray PLC Pharma Plc</td>\n",
       "      <td>Reinbek</td>\n",
       "      <td>Germany</td>\n",
       "      <td>53.5089</td>\n",
       "      <td>10.2483</td>\n",
       "      <td>Hospital</td>\n",
       "      <td>Private</td>\n",
       "      <td>Symbiroban</td>\n",
       "      <td>Antibiotics</td>\n",
       "      <td>-237.0</td>\n",
       "      <td>122</td>\n",
       "      <td>-28914.0</td>\n",
       "      <td>December</td>\n",
       "      <td>2020</td>\n",
       "      <td>Morris Garcia</td>\n",
       "      <td>Tracy Banks</td>\n",
       "      <td>Bravo</td>\n",
       "      <td>-28914.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253761</th>\n",
       "      <td>Koss</td>\n",
       "      <td>Kreiger Inc</td>\n",
       "      <td>Burgdorf</td>\n",
       "      <td>Germany</td>\n",
       "      <td>52.4500</td>\n",
       "      <td>10.0083</td>\n",
       "      <td>Pharmacy</td>\n",
       "      <td>Retail</td>\n",
       "      <td>Docstryl Rivacin</td>\n",
       "      <td>Antiseptics</td>\n",
       "      <td>-660.0</td>\n",
       "      <td>789</td>\n",
       "      <td>-520740.0</td>\n",
       "      <td>December</td>\n",
       "      <td>2020</td>\n",
       "      <td>Daniel Gates</td>\n",
       "      <td>Alisha Cordwell</td>\n",
       "      <td>Charlie</td>\n",
       "      <td>-520740.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253762</th>\n",
       "      <td>Koss</td>\n",
       "      <td>Gerhold, Mills and Effertz Pharma Plc</td>\n",
       "      <td>Stadthagen</td>\n",
       "      <td>Germany</td>\n",
       "      <td>52.3247</td>\n",
       "      <td>9.2069</td>\n",
       "      <td>Hospital</td>\n",
       "      <td>Private</td>\n",
       "      <td>Pulmofine</td>\n",
       "      <td>Antibiotics</td>\n",
       "      <td>-30.0</td>\n",
       "      <td>688</td>\n",
       "      <td>-20640.0</td>\n",
       "      <td>December</td>\n",
       "      <td>2020</td>\n",
       "      <td>Stella Given</td>\n",
       "      <td>Alisha Cordwell</td>\n",
       "      <td>Charlie</td>\n",
       "      <td>-20640.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253763</th>\n",
       "      <td>Koss</td>\n",
       "      <td>Pfeffer-Kirlin Pharmacy</td>\n",
       "      <td>Ahlen</td>\n",
       "      <td>Germany</td>\n",
       "      <td>51.7633</td>\n",
       "      <td>7.8911</td>\n",
       "      <td>Pharmacy</td>\n",
       "      <td>Institution</td>\n",
       "      <td>Abatatriptan</td>\n",
       "      <td>Antibiotics</td>\n",
       "      <td>-1100.0</td>\n",
       "      <td>742</td>\n",
       "      <td>-816200.0</td>\n",
       "      <td>December</td>\n",
       "      <td>2020</td>\n",
       "      <td>Stella Given</td>\n",
       "      <td>Alisha Cordwell</td>\n",
       "      <td>Charlie</td>\n",
       "      <td>-816200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253998</th>\n",
       "      <td>Koss</td>\n",
       "      <td>Corkery-Kovacek Pharm</td>\n",
       "      <td>Kehl</td>\n",
       "      <td>Germany</td>\n",
       "      <td>48.5711</td>\n",
       "      <td>7.8089</td>\n",
       "      <td>Hospital</td>\n",
       "      <td>Private</td>\n",
       "      <td>Agalsiline</td>\n",
       "      <td>Mood Stabilizers</td>\n",
       "      <td>-1200.0</td>\n",
       "      <td>64</td>\n",
       "      <td>-76800.0</td>\n",
       "      <td>December</td>\n",
       "      <td>2020</td>\n",
       "      <td>Morris Garcia</td>\n",
       "      <td>Tracy Banks</td>\n",
       "      <td>Bravo</td>\n",
       "      <td>-76800.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2633 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Distributor                                      Customer Name  \\\n",
       "3761           Rohan                               Zieme, Doyle and Kunze    \n",
       "3762           Rohan                  Nader-Gaylord Pharmaceutical Limited   \n",
       "18526          Rohan     Buckridge, Dach and Carroll Pharmaceutical Lim...   \n",
       "22613   Prohaska-Kuhic                   Wiegand, Jast and Yost Pharma Plc   \n",
       "22751   Prohaska-Kuhic               Pfeffer-Hodkiewicz Pharmaceutical Ltd   \n",
       "...                 ...                                                ...   \n",
       "253760          Koss                                 Murray PLC Pharma Plc   \n",
       "253761          Koss                                          Kreiger Inc    \n",
       "253762          Koss                 Gerhold, Mills and Effertz Pharma Plc   \n",
       "253763          Koss                               Pfeffer-Kirlin Pharmacy   \n",
       "253998          Koss                                 Corkery-Kovacek Pharm   \n",
       "\n",
       "              City  Country  Latitude  Longitude   Channel  Sub-channel  \\\n",
       "3761        Lublin   Poland   51.2333    22.5667  Hospital      Private   \n",
       "3762       Sobótka   Poland   50.8986    16.7444  Hospital      Private   \n",
       "18526        Luboń   Poland   52.3333    16.8833  Hospital      Private   \n",
       "22613        Płock   Poland   52.5500    19.7000  Hospital      Private   \n",
       "22751     Szczytno   Poland   53.5628    20.9853  Hospital   Government   \n",
       "...            ...      ...       ...        ...       ...          ...   \n",
       "253760     Reinbek  Germany   53.5089    10.2483  Hospital      Private   \n",
       "253761    Burgdorf  Germany   52.4500    10.0083  Pharmacy       Retail   \n",
       "253762  Stadthagen  Germany   52.3247     9.2069  Hospital      Private   \n",
       "253763       Ahlen  Germany   51.7633     7.8911  Pharmacy  Institution   \n",
       "253998        Kehl  Germany   48.5711     7.8089  Hospital      Private   \n",
       "\n",
       "                Product Name     Product Class  Quantity  Price     Sales  \\\n",
       "3761      Exotropin Empizine  Mood Stabilizers     -19.0    785  -14915.0   \n",
       "3762              Epzipitant        Analgesics     -12.0    778   -9336.0   \n",
       "18526   Belavarix Benzabicin       Antibiotics      -1.0    131    -131.0   \n",
       "22613             Afinitasol      Antipiretics      -5.0    286   -1430.0   \n",
       "22751   Sucprine Specbalamin       Antiseptics     -50.0    182   -9100.0   \n",
       "...                      ...               ...       ...    ...       ...   \n",
       "253760            Symbiroban       Antibiotics    -237.0    122  -28914.0   \n",
       "253761      Docstryl Rivacin       Antiseptics    -660.0    789 -520740.0   \n",
       "253762             Pulmofine       Antibiotics     -30.0    688  -20640.0   \n",
       "253763          Abatatriptan       Antibiotics   -1100.0    742 -816200.0   \n",
       "253998            Agalsiline  Mood Stabilizers   -1200.0     64  -76800.0   \n",
       "\n",
       "           Month  Year  Name of Sales Rep          Manager Sales Team  \\\n",
       "3761    February  2018            Anne Wu    Britanny Bold      Delta   \n",
       "3762    February  2018  Thompson Crawford   James Goodwill       Alfa   \n",
       "18526   November  2018      Morris Garcia      Tracy Banks      Bravo   \n",
       "22613   February  2018      Jessica Smith    Britanny Bold      Delta   \n",
       "22751      March  2018         Jimmy Grey  Alisha Cordwell    Charlie   \n",
       "...          ...   ...                ...              ...        ...   \n",
       "253760  December  2020      Morris Garcia      Tracy Banks      Bravo   \n",
       "253761  December  2020       Daniel Gates  Alisha Cordwell    Charlie   \n",
       "253762  December  2020       Stella Given  Alisha Cordwell    Charlie   \n",
       "253763  December  2020       Stella Given  Alisha Cordwell    Charlie   \n",
       "253998  December  2020      Morris Garcia      Tracy Banks      Bravo   \n",
       "\n",
       "        calc_amount  \n",
       "3761       -14915.0  \n",
       "3762        -9336.0  \n",
       "18526        -131.0  \n",
       "22613       -1430.0  \n",
       "22751       -9100.0  \n",
       "...             ...  \n",
       "253760     -28914.0  \n",
       "253761    -520740.0  \n",
       "253762     -20640.0  \n",
       "253763    -816200.0  \n",
       "253998     -76800.0  \n",
       "\n",
       "[2633 rows x 19 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# analyse -ve qty and sales\n",
    "pharma_df[pharma_df['Quantity'] < 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "28d259a5-e188-4785-8ea9-99bfefc6cc7a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Britanny Bold      792\n",
       "James Goodwill     627\n",
       "Tracy Banks        623\n",
       "Alisha Cordwell    591\n",
       "Name: Manager, dtype: int64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check managers with -ve qty and sales\n",
    "pharma_df[pharma_df['Quantity'] < 0][\"Manager\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "75ad9d45-004a-4f01-b453-d8cbd8813fb9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "****** Distributor ******:\n",
      "Gerlach LLC               111364\n",
      "Koss                       58360\n",
      "Erdman                     26238\n",
      "Stehr-Champlin             11942\n",
      "Smith Inc                   6802\n",
      "Gottlieb-Cruickshank        6427\n",
      "Prohaska-Kuhic              6109\n",
      "Kozey-Emmerich              4305\n",
      "Beier                       3311\n",
      "Welch-Langworth             3125\n",
      "Cassin                      3007\n",
      "Daugherty-Rempel            1960\n",
      "Rohan and Sons              1575\n",
      "Romaguera-Fay               1356\n",
      "Rohan                       1185\n",
      "Crist Inc                   1157\n",
      "Lockman                     1013\n",
      "Lesch                        974\n",
      "Gleason                      779\n",
      "Lindgren-Simonis Pharm       759\n",
      "Schaefer LLC                 572\n",
      "Nader-Gaylord                324\n",
      "Hansen Group Pharm           318\n",
      "Schuppe Inc                  269\n",
      "Rogahn-Klein                 227\n",
      "Graham and Sons              213\n",
      "Carter-Conn                  181\n",
      "Bashirian-Kassulke           178\n",
      "Kris LLC                      52\n",
      "Name: Distributor, dtype: int64\n",
      "\n",
      "\n",
      "****** Customer Name ******:\n",
      "Spinka, Schowalter and Conn                   440\n",
      "Fritsch, Hodkiewicz and Sipes Pharma Plc      437\n",
      "Weber Ltd Pharmaceutical Ltd                  436\n",
      "Corkery-Kovacek Pharm                         436\n",
      "Nolan, DuBuque and Schultz                    434\n",
      "                                             ... \n",
      "Fadel-West                                    172\n",
      "Wiegand, Jast and Yost Pharma Plc             164\n",
      "Bernier, Murphy and Rau Pharmaceutical Ltd    164\n",
      "Schaefer-Rolfson                              162\n",
      "Medhurst-Beer Pharmaceutical Limited          157\n",
      "Name: Customer Name, Length: 751, dtype: int64\n",
      "\n",
      "\n",
      "****** City ******:\n",
      "Friedberg      796\n",
      "Neustadt       760\n",
      "Bergheim       440\n",
      "Ettlingen      437\n",
      "Kehl           436\n",
      "              ... \n",
      "Złotoryja      172\n",
      "Ruda Śląska    164\n",
      "Płock          164\n",
      "Niepołomice    162\n",
      "Rybnik         157\n",
      "Name: City, Length: 749, dtype: int64\n",
      "\n",
      "\n",
      "****** Country ******:\n",
      "Germany    213598\n",
      "Poland      40484\n",
      "Name: Country, dtype: int64\n",
      "\n",
      "\n",
      "****** Channel ******:\n",
      "Pharmacy    129971\n",
      "Hospital    124111\n",
      "Name: Channel, dtype: int64\n",
      "\n",
      "\n",
      "****** Sub-channel ******:\n",
      "Retail         68351\n",
      "Government     65605\n",
      "Institution    61620\n",
      "Private        58506\n",
      "Name: Sub-channel, dtype: int64\n",
      "\n",
      "\n",
      "****** Product Name ******:\n",
      "Diprotaine               1143\n",
      "Nevanide Actozide        1137\n",
      "Solasteride              1136\n",
      "Acantaine                1133\n",
      "Menogine                 1132\n",
      "                         ... \n",
      "Adrecetam Barazoxane     1000\n",
      "Vibratosine Alemtrace    1000\n",
      "Immurenol Temarate        999\n",
      "Secrelazine Insonamic     994\n",
      "Adideine                  986\n",
      "Name: Product Name, Length: 240, dtype: int64\n",
      "\n",
      "\n",
      "****** Product Class ******:\n",
      "Antiseptics         52037\n",
      "Mood Stabilizers    46415\n",
      "Analgesics          44751\n",
      "Antibiotics         36979\n",
      "Antipiretics        36955\n",
      "Antimalarial        36945\n",
      "Name: Product Class, dtype: int64\n",
      "\n",
      "\n",
      "****** Month ******:\n",
      "November     23323\n",
      "July         23017\n",
      "March        22956\n",
      "October      22116\n",
      "June         21802\n",
      "August       21764\n",
      "May          21302\n",
      "September    21256\n",
      "February     21094\n",
      "January      18687\n",
      "April        18564\n",
      "December     18201\n",
      "Name: Month, dtype: int64\n",
      "\n",
      "\n",
      "****** Name of Sales Rep ******:\n",
      "Sheila Stones        19655\n",
      "Jessica Smith        19623\n",
      "Thompson Crawford    19622\n",
      "Steve Pepple         19620\n",
      "Alan Ray             19612\n",
      "Abigail Thompson     19571\n",
      "Anne Wu              19563\n",
      "Stella Given         19539\n",
      "Jimmy Grey           19503\n",
      "Erica Jones          19471\n",
      "Morris Garcia        19461\n",
      "Daniel Gates         19439\n",
      "Mary Gerrard         19403\n",
      "Name: Name of Sales Rep, dtype: int64\n",
      "\n",
      "\n",
      "****** Manager ******:\n",
      "Britanny Bold      78244\n",
      "James Goodwill     58705\n",
      "Tracy Banks        58652\n",
      "Alisha Cordwell    58481\n",
      "Name: Manager, dtype: int64\n",
      "\n",
      "\n",
      "****** Sales Team ******:\n",
      "Delta      78244\n",
      "Alfa       58705\n",
      "Bravo      58652\n",
      "Charlie    58481\n",
      "Name: Sales Team, dtype: int64\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Analyse Dimentions\n",
    "categorical_cols = pharma_df.select_dtypes(include='object').columns\n",
    "for col in pharma_df[categorical_cols]:\n",
    "    print(\"****** \"+ col+ \" ******:\")\n",
    "    print(pharma_df[col].value_counts())\n",
    "    print()\n",
    "    print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "7d0818be-15dc-4241-96b2-8230562892d5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.generic.DataFrameGroupBy object at 0x000001E6DAFFA500>"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check if each distinct has distinct manager\n",
    "g = pharma_df.groupby([\"Sales Team\", \"Manager\"])\n",
    "g\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "37aa45d2-db6e-468a-abbf-33157cf94c83",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys([('Alfa', 'James Goodwill'), ('Bravo', 'Tracy Banks'), ('Charlie', 'Alisha Cordwell'), ('Delta', 'Britanny Bold')])"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# One manager one team\n",
    "g.groups.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a6073f1-3c91-487f-912b-ca7f19afde5f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "71e6bf13-afaa-45e7-ac18-85087387aaa2",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}