{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "1a7e310f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "bddc2445",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_path = r'..\\..\\data'\n",
    "data='collection_data.txt'\n",
    "file_number=data_path+'\\\\'+data \n",
    "df = pd.read_csv(file_number)\n",
    "df[\"collection_intervall\"]= list(map(lambda z: z*(-1),df[\"last_collection\"]))\n",
    "df[\"number_collections\"]=np.ones(len(df[\"last_collection\"]))\n",
    "df[\"number_collections\"]=df[\"number_collections\"].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "4d9c368e",
   "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>Unnamed: 0</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>container_id</th>\n",
       "      <th>last_collection</th>\n",
       "      <th>pre_height</th>\n",
       "      <th>post_height</th>\n",
       "      <th>sensor_mean_temperature</th>\n",
       "      <th>sensor_max_temperature</th>\n",
       "      <th>sensor_min_temperature</th>\n",
       "      <th>weather_mean_temperature</th>\n",
       "      <th>...</th>\n",
       "      <th>weather_mean_moisture</th>\n",
       "      <th>weather_max_moisture</th>\n",
       "      <th>weather_min_moisture</th>\n",
       "      <th>holiday_percentage</th>\n",
       "      <th>Lockdown</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>weekday</th>\n",
       "      <th>collection_intervall</th>\n",
       "      <th>number_collections</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2020-05-22 18:51:01.742945</td>\n",
       "      <td>70B3D500700016DA</td>\n",
       "      <td>-14</td>\n",
       "      <td>136</td>\n",
       "      <td>16</td>\n",
       "      <td>15.251029</td>\n",
       "      <td>47</td>\n",
       "      <td>0</td>\n",
       "      <td>14.283636</td>\n",
       "      <td>...</td>\n",
       "      <td>58.121212</td>\n",
       "      <td>95.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0.360606</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2020</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2020-06-05 14:49:42.681218</td>\n",
       "      <td>70B3D500700016DA</td>\n",
       "      <td>-14</td>\n",
       "      <td>120</td>\n",
       "      <td>14</td>\n",
       "      <td>16.410714</td>\n",
       "      <td>44</td>\n",
       "      <td>4</td>\n",
       "      <td>16.873193</td>\n",
       "      <td>...</td>\n",
       "      <td>53.888554</td>\n",
       "      <td>93.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>0.361446</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2020</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2020-06-29 13:47:52.050553</td>\n",
       "      <td>70B3D500700016DA</td>\n",
       "      <td>-24</td>\n",
       "      <td>136</td>\n",
       "      <td>14</td>\n",
       "      <td>18.255446</td>\n",
       "      <td>43</td>\n",
       "      <td>4</td>\n",
       "      <td>18.670261</td>\n",
       "      <td>...</td>\n",
       "      <td>65.890435</td>\n",
       "      <td>97.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0.375652</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2020</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2020-07-17 13:46:18.287249</td>\n",
       "      <td>70B3D500700016DA</td>\n",
       "      <td>-18</td>\n",
       "      <td>128</td>\n",
       "      <td>12</td>\n",
       "      <td>19.053476</td>\n",
       "      <td>45</td>\n",
       "      <td>7</td>\n",
       "      <td>19.258796</td>\n",
       "      <td>...</td>\n",
       "      <td>58.773148</td>\n",
       "      <td>96.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0.222222</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2020</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2020-08-07 09:44:36.149679</td>\n",
       "      <td>70B3D500700016DA</td>\n",
       "      <td>-21</td>\n",
       "      <td>118</td>\n",
       "      <td>14</td>\n",
       "      <td>21.981524</td>\n",
       "      <td>47</td>\n",
       "      <td>6</td>\n",
       "      <td>21.973000</td>\n",
       "      <td>...</td>\n",
       "      <td>49.794000</td>\n",
       "      <td>95.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.288000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2020</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>21</td>\n",
       "      <td>1</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4113</th>\n",
       "      <td>4113</td>\n",
       "      <td>2021-04-27 20:26:56.511519</td>\n",
       "      <td>70B3D50070001789</td>\n",
       "      <td>-2</td>\n",
       "      <td>52</td>\n",
       "      <td>28</td>\n",
       "      <td>19.600000</td>\n",
       "      <td>33</td>\n",
       "      <td>10</td>\n",
       "      <td>11.096000</td>\n",
       "      <td>...</td>\n",
       "      <td>37.200000</td>\n",
       "      <td>51.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2021</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4114</th>\n",
       "      <td>4114</td>\n",
       "      <td>2021-05-05 15:26:20.82634</td>\n",
       "      <td>70B3D50070001789</td>\n",
       "      <td>-8</td>\n",
       "      <td>70</td>\n",
       "      <td>4</td>\n",
       "      <td>17.646552</td>\n",
       "      <td>32</td>\n",
       "      <td>10</td>\n",
       "      <td>9.835294</td>\n",
       "      <td>...</td>\n",
       "      <td>61.197861</td>\n",
       "      <td>98.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0.256684</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2021</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4115</th>\n",
       "      <td>4115</td>\n",
       "      <td>2021-05-06 16:26:17.375246</td>\n",
       "      <td>70B3D50070001789</td>\n",
       "      <td>-2</td>\n",
       "      <td>38</td>\n",
       "      <td>6</td>\n",
       "      <td>15.055556</td>\n",
       "      <td>23</td>\n",
       "      <td>10</td>\n",
       "      <td>7.068000</td>\n",
       "      <td>...</td>\n",
       "      <td>75.320000</td>\n",
       "      <td>92.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2021</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4116</th>\n",
       "      <td>4116</td>\n",
       "      <td>2021-05-07 12:26:16.709112</td>\n",
       "      <td>70B3D50070001789</td>\n",
       "      <td>-1</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>14.214286</td>\n",
       "      <td>19</td>\n",
       "      <td>12</td>\n",
       "      <td>7.010000</td>\n",
       "      <td>...</td>\n",
       "      <td>82.950000</td>\n",
       "      <td>95.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2021</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4117</th>\n",
       "      <td>4117</td>\n",
       "      <td>2021-05-07 21:26:16.90103</td>\n",
       "      <td>70B3D50070001789</td>\n",
       "      <td>-1</td>\n",
       "      <td>44</td>\n",
       "      <td>12</td>\n",
       "      <td>19.500000</td>\n",
       "      <td>26</td>\n",
       "      <td>14</td>\n",
       "      <td>9.211111</td>\n",
       "      <td>...</td>\n",
       "      <td>52.333333</td>\n",
       "      <td>76.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2021</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4118 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Unnamed: 0                   timestamp      container_id  \\\n",
       "0              0  2020-05-22 18:51:01.742945  70B3D500700016DA   \n",
       "1              1  2020-06-05 14:49:42.681218  70B3D500700016DA   \n",
       "2              2  2020-06-29 13:47:52.050553  70B3D500700016DA   \n",
       "3              3  2020-07-17 13:46:18.287249  70B3D500700016DA   \n",
       "4              4  2020-08-07 09:44:36.149679  70B3D500700016DA   \n",
       "...          ...                         ...               ...   \n",
       "4113        4113  2021-04-27 20:26:56.511519  70B3D50070001789   \n",
       "4114        4114   2021-05-05 15:26:20.82634  70B3D50070001789   \n",
       "4115        4115  2021-05-06 16:26:17.375246  70B3D50070001789   \n",
       "4116        4116  2021-05-07 12:26:16.709112  70B3D50070001789   \n",
       "4117        4117   2021-05-07 21:26:16.90103  70B3D50070001789   \n",
       "\n",
       "      last_collection  pre_height  post_height  sensor_mean_temperature  \\\n",
       "0                 -14         136           16                15.251029   \n",
       "1                 -14         120           14                16.410714   \n",
       "2                 -24         136           14                18.255446   \n",
       "3                 -18         128           12                19.053476   \n",
       "4                 -21         118           14                21.981524   \n",
       "...               ...         ...          ...                      ...   \n",
       "4113               -2          52           28                19.600000   \n",
       "4114               -8          70            4                17.646552   \n",
       "4115               -2          38            6                15.055556   \n",
       "4116               -1          40            0                14.214286   \n",
       "4117               -1          44           12                19.500000   \n",
       "\n",
       "      sensor_max_temperature  sensor_min_temperature  \\\n",
       "0                         47                       0   \n",
       "1                         44                       4   \n",
       "2                         43                       4   \n",
       "3                         45                       7   \n",
       "4                         47                       6   \n",
       "...                      ...                     ...   \n",
       "4113                      33                      10   \n",
       "4114                      32                      10   \n",
       "4115                      23                      10   \n",
       "4116                      19                      12   \n",
       "4117                      26                      14   \n",
       "\n",
       "      weather_mean_temperature  ...  weather_mean_moisture  \\\n",
       "0                    14.283636  ...              58.121212   \n",
       "1                    16.873193  ...              53.888554   \n",
       "2                    18.670261  ...              65.890435   \n",
       "3                    19.258796  ...              58.773148   \n",
       "4                    21.973000  ...              49.794000   \n",
       "...                        ...  ...                    ...   \n",
       "4113                 11.096000  ...              37.200000   \n",
       "4114                  9.835294  ...              61.197861   \n",
       "4115                  7.068000  ...              75.320000   \n",
       "4116                  7.010000  ...              82.950000   \n",
       "4117                  9.211111  ...              52.333333   \n",
       "\n",
       "      weather_max_moisture  weather_min_moisture  holiday_percentage  \\\n",
       "0                     95.0                  25.0            0.360606   \n",
       "1                     93.0                  19.0            0.361446   \n",
       "2                     97.0                  25.0            0.375652   \n",
       "3                     96.0                  22.0            0.222222   \n",
       "4                     95.0                  20.0            0.288000   \n",
       "...                    ...                   ...                 ...   \n",
       "4113                  51.0                  24.0            0.000000   \n",
       "4114                  98.0                  25.0            0.256684   \n",
       "4115                  92.0                  51.0            0.000000   \n",
       "4116                  95.0                  53.0            0.000000   \n",
       "4117                  76.0                  39.0            0.000000   \n",
       "\n",
       "      Lockdown  year  month  weekday  collection_intervall  number_collections  \n",
       "0          0.0  2020      5        4                    14                   1  \n",
       "1          0.0  2020      6        4                    14                   1  \n",
       "2          0.0  2020      6        0                    24                   1  \n",
       "3          0.0  2020      7        4                    18                   1  \n",
       "4          0.0  2020      8        4                    21                   1  \n",
       "...        ...   ...    ...      ...                   ...                 ...  \n",
       "4113       1.0  2021      4        1                     2                   1  \n",
       "4114       1.0  2021      5        2                     8                   1  \n",
       "4115       1.0  2021      5        3                     2                   1  \n",
       "4116       1.0  2021      5        4                     1                   1  \n",
       "4117       1.0  2021      5        4                     1                   1  \n",
       "\n",
       "[4118 rows x 25 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e02f3a6",
   "metadata": {},
   "source": [
    "Hinzufügen der Information der geschätzten Anzahl der Leerungen jedes Conatainers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "aab3daa4",
   "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>number_collections</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>container_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>70B3D500700016DA</th>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70B3D500700016DF</th>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70B3D500700016E0</th>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70B3D500700016E5</th>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70B3D500700016E6</th>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70B3D50070001782</th>\n",
       "      <td>118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70B3D50070001786</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70B3D50070001787</th>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70B3D50070001788</th>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70B3D50070001789</th>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>72 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  number_collections\n",
       "container_id                        \n",
       "70B3D500700016DA                  22\n",
       "70B3D500700016DF                  44\n",
       "70B3D500700016E0                  43\n",
       "70B3D500700016E5                  24\n",
       "70B3D500700016E6                 104\n",
       "...                              ...\n",
       "70B3D50070001782                 118\n",
       "70B3D50070001786                   5\n",
       "70B3D50070001787                  64\n",
       "70B3D50070001788                  45\n",
       "70B3D50070001789                 112\n",
       "\n",
       "[72 rows x 1 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "number_collections=pd.DataFrame({'container_id':df[\"container_id\"],'number_collections':df['number_collections']})\n",
    "number_collections=number_collections.groupby(['container_id']).sum()\n",
    "number_collections"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "6bba9c37",
   "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>collection_intervall</th>\n",
       "      <th>pre_height</th>\n",
       "      <th>post_height</th>\n",
       "      <th>sensor_mean_temperature</th>\n",
       "      <th>lockdown</th>\n",
       "      <th>number_collections</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>container_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>70B3D500700016DA</th>\n",
       "      <td>-16.818182</td>\n",
       "      <td>114.363636</td>\n",
       "      <td>11.636364</td>\n",
       "      <td>9.286537</td>\n",
       "      <td>0.590909</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70B3D500700016DF</th>\n",
       "      <td>-7.954545</td>\n",
       "      <td>65.136364</td>\n",
       "      <td>14.636364</td>\n",
       "      <td>11.800192</td>\n",
       "      <td>0.681818</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70B3D500700016E0</th>\n",
       "      <td>-8.744186</td>\n",
       "      <td>49.581395</td>\n",
       "      <td>2.511628</td>\n",
       "      <td>18.144672</td>\n",
       "      <td>0.581395</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70B3D500700016E5</th>\n",
       "      <td>-15.541667</td>\n",
       "      <td>51.416667</td>\n",
       "      <td>5.500000</td>\n",
       "      <td>11.360191</td>\n",
       "      <td>0.541667</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70B3D500700016E6</th>\n",
       "      <td>-3.961538</td>\n",
       "      <td>67.711538</td>\n",
       "      <td>17.576923</td>\n",
       "      <td>11.270862</td>\n",
       "      <td>0.653846</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70B3D50070001782</th>\n",
       "      <td>-3.652542</td>\n",
       "      <td>66.203390</td>\n",
       "      <td>18.050847</td>\n",
       "      <td>21.802890</td>\n",
       "      <td>0.330508</td>\n",
       "      <td>118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70B3D50070001786</th>\n",
       "      <td>-26.000000</td>\n",
       "      <td>64.000000</td>\n",
       "      <td>26.400000</td>\n",
       "      <td>25.662432</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70B3D50070001787</th>\n",
       "      <td>-6.109375</td>\n",
       "      <td>77.656250</td>\n",
       "      <td>12.875000</td>\n",
       "      <td>6.496864</td>\n",
       "      <td>0.796875</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70B3D50070001788</th>\n",
       "      <td>-8.311111</td>\n",
       "      <td>67.733333</td>\n",
       "      <td>10.888889</td>\n",
       "      <td>11.653665</td>\n",
       "      <td>0.533333</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70B3D50070001789</th>\n",
       "      <td>-3.776786</td>\n",
       "      <td>69.142857</td>\n",
       "      <td>12.500000</td>\n",
       "      <td>18.658766</td>\n",
       "      <td>0.446429</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>72 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  collection_intervall  pre_height  post_height  \\\n",
       "container_id                                                      \n",
       "70B3D500700016DA            -16.818182  114.363636    11.636364   \n",
       "70B3D500700016DF             -7.954545   65.136364    14.636364   \n",
       "70B3D500700016E0             -8.744186   49.581395     2.511628   \n",
       "70B3D500700016E5            -15.541667   51.416667     5.500000   \n",
       "70B3D500700016E6             -3.961538   67.711538    17.576923   \n",
       "...                                ...         ...          ...   \n",
       "70B3D50070001782             -3.652542   66.203390    18.050847   \n",
       "70B3D50070001786            -26.000000   64.000000    26.400000   \n",
       "70B3D50070001787             -6.109375   77.656250    12.875000   \n",
       "70B3D50070001788             -8.311111   67.733333    10.888889   \n",
       "70B3D50070001789             -3.776786   69.142857    12.500000   \n",
       "\n",
       "                  sensor_mean_temperature  lockdown  number_collections  \n",
       "container_id                                                             \n",
       "70B3D500700016DA                 9.286537  0.590909                  22  \n",
       "70B3D500700016DF                11.800192  0.681818                  44  \n",
       "70B3D500700016E0                18.144672  0.581395                  43  \n",
       "70B3D500700016E5                11.360191  0.541667                  24  \n",
       "70B3D500700016E6                11.270862  0.653846                 104  \n",
       "...                                   ...       ...                 ...  \n",
       "70B3D50070001782                21.802890  0.330508                 118  \n",
       "70B3D50070001786                25.662432  0.000000                   5  \n",
       "70B3D50070001787                 6.496864  0.796875                  64  \n",
       "70B3D50070001788                11.653665  0.533333                  45  \n",
       "70B3D50070001789                18.658766  0.446429                 112  \n",
       "\n",
       "[72 rows x 6 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "relevant_data=pd.DataFrame({'container_id':df[\"container_id\"],'collection_intervall':df[\"last_collection\"],\n",
    "                            'pre_height':df[\"pre_height\"],'post_height':df[\"post_height\"],\n",
    "                            'sensor_mean_temperature':df[\"sensor_mean_temperature\"],'lockdown':df['Lockdown']})\n",
    "data_all=relevant_data.groupby(['container_id']).mean()\n",
    "data_all['number_collections']=number_collections['number_collections']\n",
    "data_all"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63323896",
   "metadata": {},
   "source": [
    "Rausfiltern aller \"Ausreißer Container\", damit Modelling nicht verfälscht wird. <br>\n",
    "\"Ausreißer Container\" sind definiert als alle Contaner, die mehr als 40 Leerungen haben oder im Durchschnitt ein Leerungsintervall von über 75 Tage. Schwellwerte wurden anhand der Visualisierung des Clustering Notebooks ausgewählt.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "4e74d2b7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['70B3D500700016DA',\n",
       " '70B3D500700016E5',\n",
       " '70B3D500700016E7',\n",
       " '70B3D500700016EB',\n",
       " '70B3D500700016F1',\n",
       " '70B3D500700016F2',\n",
       " '70B3D500700016F4',\n",
       " '70B3D500700016FC',\n",
       " '70B3D50070001704',\n",
       " '70B3D50070001706',\n",
       " '70B3D50070001709',\n",
       " '70B3D50070001710',\n",
       " '70B3D50070001712',\n",
       " '70B3D50070001716',\n",
       " '70B3D50070001725',\n",
       " '70B3D50070001726',\n",
       " '70B3D50070001727',\n",
       " '70B3D5007000172B',\n",
       " '70B3D5007000172C',\n",
       " '70B3D5007000172D',\n",
       " '70B3D5007000172E',\n",
       " '70B3D50070001730',\n",
       " '70B3D50070001734',\n",
       " '70B3D50070001738',\n",
       " '70B3D5007000173C',\n",
       " '70B3D50070001740',\n",
       " '70B3D50070001742',\n",
       " '70B3D50070001747',\n",
       " '70B3D5007000174F',\n",
       " '70B3D50070001770',\n",
       " '70B3D50070001779',\n",
       " '70B3D5007000177C',\n",
       " '70B3D50070001786']"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Schwellwerte\n",
    "T_colecction_intervall=75\n",
    "T_number_collections=40\n",
    "\n",
    "data_all=data_all[data_all['collection_intervall']<=T_colecction_intervall]\n",
    "data_all=data_all[data_all['number_collections']<=T_number_collections]\n",
    "data_all=data_all.reset_index()\n",
    "container=list(data_all['container_id'])\n",
    "container"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "01762362",
   "metadata": {},
   "outputs": [],
   "source": [
    "train_data=df[df['container_id']==container[0]]\n",
    "for item in container:\n",
    "    if item == container[0]:\n",
    "        None\n",
    "    else:    \n",
    "        train_data=train_data.append(df[df['container_id']==item])\n",
    "    \n",
    "  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "a1dcbff5",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_path = r'..\\..\\data\\modeling\\train'\n",
    "train_data.to_csv(path_or_buf=data_path+'\\\\train_data.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b271bda",
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}