source-data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders = pd.read_csv('Orders_20211018.csv', sep=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>%Order%</th>\n",
       "      <th>%Debtor%</th>\n",
       "      <th>OrderNumberMain</th>\n",
       "      <th>VOLGNR</th>\n",
       "      <th>%Order.Loading.Date%</th>\n",
       "      <th>%Order.Unloading.Date%</th>\n",
       "      <th>%Order.CreatedDate%</th>\n",
       "      <th>Order.CreatedTime</th>\n",
       "      <th>SoortRit</th>\n",
       "      <th>Order.Kind</th>\n",
       "      <th>%Order.Department%</th>\n",
       "      <th>Order.Size.LM</th>\n",
       "      <th>Order.Size.Weight</th>\n",
       "      <th>Order.Klasse.LM</th>\n",
       "      <th>Order.Klasse.Size</th>\n",
       "      <th>Distance</th>\n",
       "      <th>#Ritten</th>\n",
       "      <th>Uitbesteed%</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3,12831E+11</td>\n",
       "      <td>10045</td>\n",
       "      <td>31283134</td>\n",
       "      <td>1</td>\n",
       "      <td>28-11-2019</td>\n",
       "      <td>29-11-2019</td>\n",
       "      <td>28-11-2019</td>\n",
       "      <td>11:12</td>\n",
       "      <td>Normaal</td>\n",
       "      <td>WICS ORDER</td>\n",
       "      <td>Huif - Internationaal</td>\n",
       "      <td>0,4</td>\n",
       "      <td>495</td>\n",
       "      <td>&lt; 1lm</td>\n",
       "      <td>Klein</td>\n",
       "      <td>84,56016431</td>\n",
       "      <td>1</td>\n",
       "      <td>0,00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1,17076E+11</td>\n",
       "      <td>16498</td>\n",
       "      <td>11707553</td>\n",
       "      <td>1</td>\n",
       "      <td>19-2-2019</td>\n",
       "      <td>20-2-2019</td>\n",
       "      <td>19-2-2019</td>\n",
       "      <td>13:12</td>\n",
       "      <td>Normaal</td>\n",
       "      <td>Normale order</td>\n",
       "      <td>Huif - Benelux</td>\n",
       "      <td>0,4</td>\n",
       "      <td>500</td>\n",
       "      <td>&lt; 1lm</td>\n",
       "      <td>Klein</td>\n",
       "      <td>76,79606352</td>\n",
       "      <td>2</td>\n",
       "      <td>0,00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1,18296E+11</td>\n",
       "      <td>10739</td>\n",
       "      <td>11829648</td>\n",
       "      <td>1</td>\n",
       "      <td>20-2-2020</td>\n",
       "      <td>21-2-2020</td>\n",
       "      <td>20-2-2020</td>\n",
       "      <td>09:12</td>\n",
       "      <td>Normaal</td>\n",
       "      <td>Normale order</td>\n",
       "      <td>Huif - Benelux</td>\n",
       "      <td>1,5</td>\n",
       "      <td>3000</td>\n",
       "      <td>1-2 lm</td>\n",
       "      <td>Klein</td>\n",
       "      <td>116,4987935</td>\n",
       "      <td>2</td>\n",
       "      <td>0,00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1,19517E+11</td>\n",
       "      <td>4046</td>\n",
       "      <td>11951743</td>\n",
       "      <td>1</td>\n",
       "      <td>26-3-2021</td>\n",
       "      <td>29-3-2021</td>\n",
       "      <td>26-3-2021</td>\n",
       "      <td>12:12</td>\n",
       "      <td>Normaal</td>\n",
       "      <td>Normale order</td>\n",
       "      <td>Huif - Benelux</td>\n",
       "      <td>0,4</td>\n",
       "      <td>100</td>\n",
       "      <td>&lt; 1lm</td>\n",
       "      <td>Klein</td>\n",
       "      <td>91,3393582</td>\n",
       "      <td>2</td>\n",
       "      <td>50,00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1,18296E+11</td>\n",
       "      <td>10739</td>\n",
       "      <td>11829648</td>\n",
       "      <td>2</td>\n",
       "      <td>20-2-2020</td>\n",
       "      <td>21-2-2020</td>\n",
       "      <td>20-2-2020</td>\n",
       "      <td>11:12</td>\n",
       "      <td>Normaal</td>\n",
       "      <td>Normale order</td>\n",
       "      <td>Huif - Benelux</td>\n",
       "      <td>0,2</td>\n",
       "      <td>51</td>\n",
       "      <td>&lt; 1lm</td>\n",
       "      <td>Klein</td>\n",
       "      <td>78,43046515</td>\n",
       "      <td>2</td>\n",
       "      <td>0,00%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       %Order%  %Debtor%  OrderNumberMain  VOLGNR %Order.Loading.Date%  \\\n",
       "0  3,12831E+11     10045         31283134       1           28-11-2019   \n",
       "1  1,17076E+11     16498         11707553       1            19-2-2019   \n",
       "2  1,18296E+11     10739         11829648       1            20-2-2020   \n",
       "3  1,19517E+11      4046         11951743       1            26-3-2021   \n",
       "4  1,18296E+11     10739         11829648       2            20-2-2020   \n",
       "\n",
       "  %Order.Unloading.Date% %Order.CreatedDate% Order.CreatedTime SoortRit  \\\n",
       "0             29-11-2019          28-11-2019             11:12  Normaal   \n",
       "1              20-2-2019           19-2-2019             13:12  Normaal   \n",
       "2              21-2-2020           20-2-2020             09:12  Normaal   \n",
       "3              29-3-2021           26-3-2021             12:12  Normaal   \n",
       "4              21-2-2020           20-2-2020             11:12  Normaal   \n",
       "\n",
       "      Order.Kind     %Order.Department% Order.Size.LM Order.Size.Weight  \\\n",
       "0     WICS ORDER  Huif - Internationaal           0,4               495   \n",
       "1  Normale order         Huif - Benelux           0,4               500   \n",
       "2  Normale order         Huif - Benelux           1,5              3000   \n",
       "3  Normale order         Huif - Benelux           0,4               100   \n",
       "4  Normale order         Huif - Benelux           0,2                51   \n",
       "\n",
       "  Order.Klasse.LM Order.Klasse.Size     Distance  #Ritten Uitbesteed%  \n",
       "0           < 1lm             Klein  84,56016431        1       0,00%  \n",
       "1           < 1lm             Klein  76,79606352        2       0,00%  \n",
       "2          1-2 lm             Klein  116,4987935        2       0,00%  \n",
       "3           < 1lm             Klein   91,3393582        2      50,00%  \n",
       "4           < 1lm             Klein  78,43046515        2       0,00%  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders = orders.rename(columns={\"%Debtor%\" : \"debnr\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders = orders.drop(\"%Order%\", axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>debnr</th>\n",
       "      <th>OrderNumberMain</th>\n",
       "      <th>VOLGNR</th>\n",
       "      <th>%Order.Loading.Date%</th>\n",
       "      <th>%Order.Unloading.Date%</th>\n",
       "      <th>%Order.CreatedDate%</th>\n",
       "      <th>Order.CreatedTime</th>\n",
       "      <th>SoortRit</th>\n",
       "      <th>Order.Kind</th>\n",
       "      <th>%Order.Department%</th>\n",
       "      <th>Order.Size.LM</th>\n",
       "      <th>Order.Size.Weight</th>\n",
       "      <th>Order.Klasse.LM</th>\n",
       "      <th>Order.Klasse.Size</th>\n",
       "      <th>Distance</th>\n",
       "      <th>#Ritten</th>\n",
       "      <th>Uitbesteed%</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10045</td>\n",
       "      <td>31283134</td>\n",
       "      <td>1</td>\n",
       "      <td>28-11-2019</td>\n",
       "      <td>29-11-2019</td>\n",
       "      <td>28-11-2019</td>\n",
       "      <td>11:12</td>\n",
       "      <td>Normaal</td>\n",
       "      <td>WICS ORDER</td>\n",
       "      <td>Huif - Internationaal</td>\n",
       "      <td>0,4</td>\n",
       "      <td>495</td>\n",
       "      <td>&lt; 1lm</td>\n",
       "      <td>Klein</td>\n",
       "      <td>84,56016431</td>\n",
       "      <td>1</td>\n",
       "      <td>0,00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>16498</td>\n",
       "      <td>11707553</td>\n",
       "      <td>1</td>\n",
       "      <td>19-2-2019</td>\n",
       "      <td>20-2-2019</td>\n",
       "      <td>19-2-2019</td>\n",
       "      <td>13:12</td>\n",
       "      <td>Normaal</td>\n",
       "      <td>Normale order</td>\n",
       "      <td>Huif - Benelux</td>\n",
       "      <td>0,4</td>\n",
       "      <td>500</td>\n",
       "      <td>&lt; 1lm</td>\n",
       "      <td>Klein</td>\n",
       "      <td>76,79606352</td>\n",
       "      <td>2</td>\n",
       "      <td>0,00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10739</td>\n",
       "      <td>11829648</td>\n",
       "      <td>1</td>\n",
       "      <td>20-2-2020</td>\n",
       "      <td>21-2-2020</td>\n",
       "      <td>20-2-2020</td>\n",
       "      <td>09:12</td>\n",
       "      <td>Normaal</td>\n",
       "      <td>Normale order</td>\n",
       "      <td>Huif - Benelux</td>\n",
       "      <td>1,5</td>\n",
       "      <td>3000</td>\n",
       "      <td>1-2 lm</td>\n",
       "      <td>Klein</td>\n",
       "      <td>116,4987935</td>\n",
       "      <td>2</td>\n",
       "      <td>0,00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4046</td>\n",
       "      <td>11951743</td>\n",
       "      <td>1</td>\n",
       "      <td>26-3-2021</td>\n",
       "      <td>29-3-2021</td>\n",
       "      <td>26-3-2021</td>\n",
       "      <td>12:12</td>\n",
       "      <td>Normaal</td>\n",
       "      <td>Normale order</td>\n",
       "      <td>Huif - Benelux</td>\n",
       "      <td>0,4</td>\n",
       "      <td>100</td>\n",
       "      <td>&lt; 1lm</td>\n",
       "      <td>Klein</td>\n",
       "      <td>91,3393582</td>\n",
       "      <td>2</td>\n",
       "      <td>50,00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10739</td>\n",
       "      <td>11829648</td>\n",
       "      <td>2</td>\n",
       "      <td>20-2-2020</td>\n",
       "      <td>21-2-2020</td>\n",
       "      <td>20-2-2020</td>\n",
       "      <td>11:12</td>\n",
       "      <td>Normaal</td>\n",
       "      <td>Normale order</td>\n",
       "      <td>Huif - Benelux</td>\n",
       "      <td>0,2</td>\n",
       "      <td>51</td>\n",
       "      <td>&lt; 1lm</td>\n",
       "      <td>Klein</td>\n",
       "      <td>78,43046515</td>\n",
       "      <td>2</td>\n",
       "      <td>0,00%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   debnr  OrderNumberMain  VOLGNR %Order.Loading.Date% %Order.Unloading.Date%  \\\n",
       "0  10045         31283134       1           28-11-2019             29-11-2019   \n",
       "1  16498         11707553       1            19-2-2019              20-2-2019   \n",
       "2  10739         11829648       1            20-2-2020              21-2-2020   \n",
       "3   4046         11951743       1            26-3-2021              29-3-2021   \n",
       "4  10739         11829648       2            20-2-2020              21-2-2020   \n",
       "\n",
       "  %Order.CreatedDate% Order.CreatedTime SoortRit     Order.Kind  \\\n",
       "0          28-11-2019             11:12  Normaal     WICS ORDER   \n",
       "1           19-2-2019             13:12  Normaal  Normale order   \n",
       "2           20-2-2020             09:12  Normaal  Normale order   \n",
       "3           26-3-2021             12:12  Normaal  Normale order   \n",
       "4           20-2-2020             11:12  Normaal  Normale order   \n",
       "\n",
       "      %Order.Department% Order.Size.LM Order.Size.Weight Order.Klasse.LM  \\\n",
       "0  Huif - Internationaal           0,4               495           < 1lm   \n",
       "1         Huif - Benelux           0,4               500           < 1lm   \n",
       "2         Huif - Benelux           1,5              3000          1-2 lm   \n",
       "3         Huif - Benelux           0,4               100           < 1lm   \n",
       "4         Huif - Benelux           0,2                51           < 1lm   \n",
       "\n",
       "  Order.Klasse.Size     Distance  #Ritten Uitbesteed%  \n",
       "0             Klein  84,56016431        1       0,00%  \n",
       "1             Klein  76,79606352        2       0,00%  \n",
       "2             Klein  116,4987935        2       0,00%  \n",
       "3             Klein   91,3393582        2      50,00%  \n",
       "4             Klein  78,43046515        2       0,00%  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 548311 entries, 0 to 548310\n",
      "Data columns (total 17 columns):\n",
      " #   Column                  Non-Null Count   Dtype \n",
      "---  ------                  --------------   ----- \n",
      " 0   debnr                   548311 non-null  int64 \n",
      " 1   OrderNumberMain         548311 non-null  int64 \n",
      " 2   VOLGNR                  548311 non-null  int64 \n",
      " 3   %Order.Loading.Date%    548311 non-null  object\n",
      " 4   %Order.Unloading.Date%  548311 non-null  object\n",
      " 5   %Order.CreatedDate%     548311 non-null  object\n",
      " 6   Order.CreatedTime       548311 non-null  object\n",
      " 7   SoortRit                525360 non-null  object\n",
      " 8   Order.Kind              548229 non-null  object\n",
      " 9   %Order.Department%      548311 non-null  object\n",
      " 10  Order.Size.LM           548311 non-null  object\n",
      " 11  Order.Size.Weight       548311 non-null  object\n",
      " 12  Order.Klasse.LM         548311 non-null  object\n",
      " 13  Order.Klasse.Size       548311 non-null  object\n",
      " 14  Distance                548286 non-null  object\n",
      " 15  #Ritten                 548311 non-null  int64 \n",
      " 16  Uitbesteed%             547645 non-null  object\n",
      "dtypes: int64(4), object(13)\n",
      "memory usage: 71.1+ MB\n"
     ]
    }
   ],
   "source": [
    "orders.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers = pd.read_excel('klantenbestand_03112021.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Bedrijf</th>\n",
       "      <th>Branche</th>\n",
       "      <th>Debiteurnummer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kennis Transport &amp; Logistics B.V.</td>\n",
       "      <td>Transport, logistics and expedition</td>\n",
       "      <td>KENNISTR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Wex Holland B.V.</td>\n",
       "      <td>Transport, logistics and expedition</td>\n",
       "      <td>5683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Oldenburger Fritom</td>\n",
       "      <td>Transport, logistics and expedition</td>\n",
       "      <td>OLDENBURG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>G. van Doesburg Internationaal Transport B.V.</td>\n",
       "      <td>Transport, logistics and expedition</td>\n",
       "      <td>727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MOL Logistics B.V.</td>\n",
       "      <td>Transport, logistics and expedition</td>\n",
       "      <td>1438</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         Bedrijf  \\\n",
       "0              Kennis Transport & Logistics B.V.   \n",
       "1                               Wex Holland B.V.   \n",
       "2                             Oldenburger Fritom   \n",
       "3  G. van Doesburg Internationaal Transport B.V.   \n",
       "4                             MOL Logistics B.V.   \n",
       "\n",
       "                               Branche Debiteurnummer  \n",
       "0  Transport, logistics and expedition       KENNISTR  \n",
       "1  Transport, logistics and expedition           5683  \n",
       "2  Transport, logistics and expedition      OLDENBURG  \n",
       "3  Transport, logistics and expedition            727  \n",
       "4  Transport, logistics and expedition           1438  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 550 entries, 0 to 549\n",
      "Data columns (total 3 columns):\n",
      " #   Column          Non-Null Count  Dtype \n",
      "---  ------          --------------  ----- \n",
      " 0   Bedrijf         550 non-null    object\n",
      " 1   Branche         550 non-null    object\n",
      " 2   Debiteurnummer  549 non-null    object\n",
      "dtypes: object(3)\n",
      "memory usage: 13.0+ KB\n"
     ]
    }
   ],
   "source": [
    "customers.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers = customers.rename(columns = {\"Debiteurnummer\" : \"debnr\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>Bedrijf</th>\n",
       "      <th>Branche</th>\n",
       "      <th>debnr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kennis Transport &amp; Logistics B.V.</td>\n",
       "      <td>Transport, logistics and expedition</td>\n",
       "      <td>KENNISTR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Wex Holland B.V.</td>\n",
       "      <td>Transport, logistics and expedition</td>\n",
       "      <td>5683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Oldenburger Fritom</td>\n",
       "      <td>Transport, logistics and expedition</td>\n",
       "      <td>OLDENBURG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>G. van Doesburg Internationaal Transport B.V.</td>\n",
       "      <td>Transport, logistics and expedition</td>\n",
       "      <td>727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MOL Logistics B.V.</td>\n",
       "      <td>Transport, logistics and expedition</td>\n",
       "      <td>1438</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         Bedrijf  \\\n",
       "0              Kennis Transport & Logistics B.V.   \n",
       "1                               Wex Holland B.V.   \n",
       "2                             Oldenburger Fritom   \n",
       "3  G. van Doesburg Internationaal Transport B.V.   \n",
       "4                             MOL Logistics B.V.   \n",
       "\n",
       "                               Branche      debnr  \n",
       "0  Transport, logistics and expedition   KENNISTR  \n",
       "1  Transport, logistics and expedition       5683  \n",
       "2  Transport, logistics and expedition  OLDENBURG  \n",
       "3  Transport, logistics and expedition        727  \n",
       "4  Transport, logistics and expedition       1438  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers['debnr'] = pd.to_numeric(customers['debnr'],errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers['debnr'] = customers['debnr'].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers['debnr'] = customers['debnr'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Bedrijf</th>\n",
       "      <th>Branche</th>\n",
       "      <th>debnr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kennis Transport &amp; Logistics B.V.</td>\n",
       "      <td>Transport, logistics and expedition</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Wex Holland B.V.</td>\n",
       "      <td>Transport, logistics and expedition</td>\n",
       "      <td>5683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Oldenburger Fritom</td>\n",
       "      <td>Transport, logistics and expedition</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>G. van Doesburg Internationaal Transport B.V.</td>\n",
       "      <td>Transport, logistics and expedition</td>\n",
       "      <td>727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MOL Logistics B.V.</td>\n",
       "      <td>Transport, logistics and expedition</td>\n",
       "      <td>1438</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         Bedrijf  \\\n",
       "0              Kennis Transport & Logistics B.V.   \n",
       "1                               Wex Holland B.V.   \n",
       "2                             Oldenburger Fritom   \n",
       "3  G. van Doesburg Internationaal Transport B.V.   \n",
       "4                             MOL Logistics B.V.   \n",
       "\n",
       "                               Branche  debnr  \n",
       "0  Transport, logistics and expedition      0  \n",
       "1  Transport, logistics and expedition   5683  \n",
       "2  Transport, logistics and expedition      0  \n",
       "3  Transport, logistics and expedition    727  \n",
       "4  Transport, logistics and expedition   1438  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2>Joinen van data</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = orders.merge(right=customers, left_on='debnr', right_on='debnr', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>debnr</th>\n",
       "      <th>OrderNumberMain</th>\n",
       "      <th>VOLGNR</th>\n",
       "      <th>%Order.Loading.Date%</th>\n",
       "      <th>%Order.Unloading.Date%</th>\n",
       "      <th>%Order.CreatedDate%</th>\n",
       "      <th>Order.CreatedTime</th>\n",
       "      <th>SoortRit</th>\n",
       "      <th>Order.Kind</th>\n",
       "      <th>%Order.Department%</th>\n",
       "      <th>Order.Size.LM</th>\n",
       "      <th>Order.Size.Weight</th>\n",
       "      <th>Order.Klasse.LM</th>\n",
       "      <th>Order.Klasse.Size</th>\n",
       "      <th>Distance</th>\n",
       "      <th>#Ritten</th>\n",
       "      <th>Uitbesteed%</th>\n",
       "      <th>Bedrijf</th>\n",
       "      <th>Branche</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>11181</td>\n",
       "      <td>11816082</td>\n",
       "      <td>1</td>\n",
       "      <td>8-1-2020</td>\n",
       "      <td>9-1-2020</td>\n",
       "      <td>8-1-2020</td>\n",
       "      <td>10:12</td>\n",
       "      <td>Normaal</td>\n",
       "      <td>Normale order</td>\n",
       "      <td>Huif - Benelux</td>\n",
       "      <td>0,6</td>\n",
       "      <td>103</td>\n",
       "      <td>&lt; 1lm</td>\n",
       "      <td>Klein</td>\n",
       "      <td>51,77323854</td>\n",
       "      <td>2</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>8053</td>\n",
       "      <td>11897479</td>\n",
       "      <td>1</td>\n",
       "      <td>6-10-2020</td>\n",
       "      <td>8-10-2020</td>\n",
       "      <td>6-10-2020</td>\n",
       "      <td>07:12</td>\n",
       "      <td>Normaal</td>\n",
       "      <td>Normale order</td>\n",
       "      <td>Huif - Internationaal</td>\n",
       "      <td>1,2</td>\n",
       "      <td>450</td>\n",
       "      <td>1-2 lm</td>\n",
       "      <td>Klein</td>\n",
       "      <td>552,4077829</td>\n",
       "      <td>2</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>7431</td>\n",
       "      <td>12006008</td>\n",
       "      <td>1</td>\n",
       "      <td>9-9-2021</td>\n",
       "      <td>14-9-2021</td>\n",
       "      <td>8-9-2021</td>\n",
       "      <td>11:12</td>\n",
       "      <td>Normaal</td>\n",
       "      <td>Operationele order</td>\n",
       "      <td>Huif - Internationaal</td>\n",
       "      <td>3,2</td>\n",
       "      <td>5789</td>\n",
       "      <td>3-4 lm</td>\n",
       "      <td>Middel</td>\n",
       "      <td>980,2533579</td>\n",
       "      <td>2</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>7643</td>\n",
       "      <td>71058338</td>\n",
       "      <td>1</td>\n",
       "      <td>3-2-2020</td>\n",
       "      <td>3-2-2020</td>\n",
       "      <td>29-1-2020</td>\n",
       "      <td>09:12</td>\n",
       "      <td>Normaal</td>\n",
       "      <td>Normale order</td>\n",
       "      <td>GDP - Benelux</td>\n",
       "      <td>1,75</td>\n",
       "      <td>0</td>\n",
       "      <td>1-2 lm</td>\n",
       "      <td>Klein</td>\n",
       "      <td>53,74548434</td>\n",
       "      <td>1</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>11181</td>\n",
       "      <td>11856781</td>\n",
       "      <td>1</td>\n",
       "      <td>20-5-2020</td>\n",
       "      <td>25-5-2020</td>\n",
       "      <td>20-5-2020</td>\n",
       "      <td>10:12</td>\n",
       "      <td>Normaal</td>\n",
       "      <td>Normale order</td>\n",
       "      <td>Huif - Benelux</td>\n",
       "      <td>6,6</td>\n",
       "      <td>2400</td>\n",
       "      <td>6-7 lm</td>\n",
       "      <td>MiddelGroot</td>\n",
       "      <td>113,6106609</td>\n",
       "      <td>2</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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>558830</th>\n",
       "      <td>4095</td>\n",
       "      <td>71058337</td>\n",
       "      <td>1</td>\n",
       "      <td>29-1-2020</td>\n",
       "      <td>30-1-2020</td>\n",
       "      <td>29-1-2020</td>\n",
       "      <td>12:12</td>\n",
       "      <td>GDP</td>\n",
       "      <td>WICS ORDER</td>\n",
       "      <td>GDP - Internationaal</td>\n",
       "      <td>0,4</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt; 1lm</td>\n",
       "      <td>Klein</td>\n",
       "      <td>148,0700921</td>\n",
       "      <td>1</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558831</th>\n",
       "      <td>3671</td>\n",
       "      <td>31310266</td>\n",
       "      <td>1</td>\n",
       "      <td>16-6-2020</td>\n",
       "      <td>17-6-2020</td>\n",
       "      <td>16-6-2020</td>\n",
       "      <td>11:12</td>\n",
       "      <td>Normaal</td>\n",
       "      <td>Operationele order</td>\n",
       "      <td>Huif - Internationaal</td>\n",
       "      <td>6</td>\n",
       "      <td>10150</td>\n",
       "      <td>6-7 lm</td>\n",
       "      <td>MiddelGroot</td>\n",
       "      <td>425,0217379</td>\n",
       "      <td>1</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558833</th>\n",
       "      <td>11181</td>\n",
       "      <td>11856780</td>\n",
       "      <td>1</td>\n",
       "      <td>20-5-2020</td>\n",
       "      <td>22-5-2020</td>\n",
       "      <td>20-5-2020</td>\n",
       "      <td>10:12</td>\n",
       "      <td>Normaal</td>\n",
       "      <td>Normale order</td>\n",
       "      <td>Huif - Benelux</td>\n",
       "      <td>0,2</td>\n",
       "      <td>50</td>\n",
       "      <td>&lt; 1lm</td>\n",
       "      <td>Klein</td>\n",
       "      <td>42,39160107</td>\n",
       "      <td>2</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558835</th>\n",
       "      <td>7643</td>\n",
       "      <td>71044771</td>\n",
       "      <td>1</td>\n",
       "      <td>15-1-2019</td>\n",
       "      <td>16-1-2019</td>\n",
       "      <td>15-1-2019</td>\n",
       "      <td>13:12</td>\n",
       "      <td>Normaal</td>\n",
       "      <td>Normale order</td>\n",
       "      <td>GDP - Internationaal</td>\n",
       "      <td>0,4</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt; 1lm</td>\n",
       "      <td>Klein</td>\n",
       "      <td>23,92629241</td>\n",
       "      <td>1</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558840</th>\n",
       "      <td>4708</td>\n",
       "      <td>11843214</td>\n",
       "      <td>1</td>\n",
       "      <td>2-4-2020</td>\n",
       "      <td>7-4-2020</td>\n",
       "      <td>2-4-2020</td>\n",
       "      <td>11:12</td>\n",
       "      <td>Normaal</td>\n",
       "      <td>Normale order</td>\n",
       "      <td>Huif - Internationaal</td>\n",
       "      <td>0,8</td>\n",
       "      <td>3000</td>\n",
       "      <td>&lt; 1lm</td>\n",
       "      <td>Klein</td>\n",
       "      <td>1007,398352</td>\n",
       "      <td>2</td>\n",
       "      <td>50,00%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>83286 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        debnr  OrderNumberMain  VOLGNR %Order.Loading.Date%  \\\n",
       "9       11181         11816082       1             8-1-2020   \n",
       "23       8053         11897479       1            6-10-2020   \n",
       "26       7431         12006008       1             9-9-2021   \n",
       "31       7643         71058338       1             3-2-2020   \n",
       "34      11181         11856781       1            20-5-2020   \n",
       "...       ...              ...     ...                  ...   \n",
       "558830   4095         71058337       1            29-1-2020   \n",
       "558831   3671         31310266       1            16-6-2020   \n",
       "558833  11181         11856780       1            20-5-2020   \n",
       "558835   7643         71044771       1            15-1-2019   \n",
       "558840   4708         11843214       1             2-4-2020   \n",
       "\n",
       "       %Order.Unloading.Date% %Order.CreatedDate% Order.CreatedTime SoortRit  \\\n",
       "9                    9-1-2020            8-1-2020             10:12  Normaal   \n",
       "23                  8-10-2020           6-10-2020             07:12  Normaal   \n",
       "26                  14-9-2021            8-9-2021             11:12  Normaal   \n",
       "31                   3-2-2020           29-1-2020             09:12  Normaal   \n",
       "34                  25-5-2020           20-5-2020             10:12  Normaal   \n",
       "...                       ...                 ...               ...      ...   \n",
       "558830              30-1-2020           29-1-2020             12:12      GDP   \n",
       "558831              17-6-2020           16-6-2020             11:12  Normaal   \n",
       "558833              22-5-2020           20-5-2020             10:12  Normaal   \n",
       "558835              16-1-2019           15-1-2019             13:12  Normaal   \n",
       "558840               7-4-2020            2-4-2020             11:12  Normaal   \n",
       "\n",
       "                Order.Kind     %Order.Department% Order.Size.LM  \\\n",
       "9            Normale order         Huif - Benelux           0,6   \n",
       "23           Normale order  Huif - Internationaal           1,2   \n",
       "26      Operationele order  Huif - Internationaal           3,2   \n",
       "31           Normale order          GDP - Benelux          1,75   \n",
       "34           Normale order         Huif - Benelux           6,6   \n",
       "...                    ...                    ...           ...   \n",
       "558830          WICS ORDER   GDP - Internationaal           0,4   \n",
       "558831  Operationele order  Huif - Internationaal             6   \n",
       "558833       Normale order         Huif - Benelux           0,2   \n",
       "558835       Normale order   GDP - Internationaal           0,4   \n",
       "558840       Normale order  Huif - Internationaal           0,8   \n",
       "\n",
       "       Order.Size.Weight Order.Klasse.LM Order.Klasse.Size     Distance  \\\n",
       "9                    103           < 1lm             Klein  51,77323854   \n",
       "23                   450          1-2 lm             Klein  552,4077829   \n",
       "26                  5789          3-4 lm            Middel  980,2533579   \n",
       "31                     0          1-2 lm             Klein  53,74548434   \n",
       "34                  2400          6-7 lm       MiddelGroot  113,6106609   \n",
       "...                  ...             ...               ...          ...   \n",
       "558830                 0           < 1lm             Klein  148,0700921   \n",
       "558831             10150          6-7 lm       MiddelGroot  425,0217379   \n",
       "558833                50           < 1lm             Klein  42,39160107   \n",
       "558835                 0           < 1lm             Klein  23,92629241   \n",
       "558840              3000           < 1lm             Klein  1007,398352   \n",
       "\n",
       "        #Ritten Uitbesteed% Bedrijf Branche  \n",
       "9             2       0,00%     NaN     NaN  \n",
       "23            2       0,00%     NaN     NaN  \n",
       "26            2       0,00%     NaN     NaN  \n",
       "31            1       0,00%     NaN     NaN  \n",
       "34            2       0,00%     NaN     NaN  \n",
       "...         ...         ...     ...     ...  \n",
       "558830        1       0,00%     NaN     NaN  \n",
       "558831        1       0,00%     NaN     NaN  \n",
       "558833        2       0,00%     NaN     NaN  \n",
       "558835        1       0,00%     NaN     NaN  \n",
       "558840        2      50,00%     NaN     NaN  \n",
       "\n",
       "[83286 rows x 19 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['Bedrijf'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "debnr                         0\n",
       "OrderNumberMain               0\n",
       "VOLGNR                        0\n",
       "%Order.Loading.Date%          0\n",
       "%Order.Unloading.Date%        0\n",
       "%Order.CreatedDate%           0\n",
       "Order.CreatedTime             0\n",
       "SoortRit                  26185\n",
       "Order.Kind                   82\n",
       "%Order.Department%            0\n",
       "Order.Size.LM                 0\n",
       "Order.Size.Weight             0\n",
       "Order.Klasse.LM               0\n",
       "Order.Klasse.Size             0\n",
       "Distance                     25\n",
       "#Ritten                       0\n",
       "Uitbesteed%                 680\n",
       "Bedrijf                   83286\n",
       "Branche                   83286\n",
       "dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "debnr                      int64\n",
       "OrderNumberMain            int64\n",
       "VOLGNR                     int64\n",
       "%Order.Loading.Date%      object\n",
       "%Order.Unloading.Date%    object\n",
       "%Order.CreatedDate%       object\n",
       "Order.CreatedTime         object\n",
       "SoortRit                  object\n",
       "Order.Kind                object\n",
       "%Order.Department%        object\n",
       "Order.Size.LM             object\n",
       "Order.Size.Weight         object\n",
       "Order.Klasse.LM           object\n",
       "Order.Klasse.Size         object\n",
       "Distance                  object\n",
       "#Ritten                    int64\n",
       "Uitbesteed%               object\n",
       "Bedrijf                   object\n",
       "Branche                   object\n",
       "dtype: object"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "40d3a090f54c6569ab1632332b64b2c03c39dcf918b08424e98f38b5ae0af88f"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}