02_pre-processing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# read csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# input files\n",
    "filename = \"./sample data/feature.csv\"\n",
    "filename1 = \"./sample data/wq0118_records.csv\"\n",
    "filename3 = \"./sample data/wp0071_records.csv\"\n",
    "filename4 = \"./sample data/R047_levels.csv\"\n",
    "\n",
    "filename2 = \"./sample data/R047_R258_titration_records.csv\"\n",
    "\n",
    "# output directory\n",
    "file_directory = './sample data/output_3_files.csv'\n",
    "output_all_data = './sample data/output_all_data.csv'\n",
    "outpuut_directory_all = './sample data/output_all_4_data.csv'\n",
    "outpuut_directory_open = './sample data/output_open_4_data.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('feature (df) shape: ', (17282, 392))\n",
      "('wq0118 (df1) shape: ', (192747, 5))\n",
      "('wp0071 (df3) shape: ', (930222, 27))\n",
      "('R046 (df4) shape: ', (8640, 5))\n"
     ]
    }
   ],
   "source": [
    "# read csv\n",
    "df_feature = pd.read_csv(filename, parse_dates=['timestamp'])\n",
    "df1_0118 = pd.read_csv(filename1, parse_dates=['measurement_result_date'])\n",
    "df3_0071 = pd.read_csv(filename3,parse_dates=[['Measurement Result Date','Minute 5']])\n",
    "df4_047 = pd.read_csv(filename4, parse_dates=['measurement_result_date'], dayfirst=True)\n",
    "\n",
    "# print shape\n",
    "print (\"feature (df) shape: \", df_feature.shape)\n",
    "print (\"wq0118 (df1) shape: \", df1_0118.shape)\n",
    "print (\"wp0071 (df3) shape: \", df3_0071.shape)\n",
    "print (\"R047 (df4) shape: \", df4_047.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('feature (df): ', False)\n",
      "('wq0118 (df1): ', True)\n",
      "('wp0071 (df3): ', False)\n",
      "('R046 (df4): ', True)\n"
     ]
    }
   ],
   "source": [
    "# is unique\n",
    "print (\"feature (df): \", df_feature['timestamp'].is_unique)\n",
    "print (\"wq0118 (df1): \", df1_0118['measurement_result_date'].is_unique)\n",
    "print (\"wp0071 (df3): \", df3_0071['Measurement Result Date_Minute 5'].is_unique)\n",
    "print (\"R047 (df4): \", df4_047['measurement_result_date'].is_unique)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### filter duplicated data "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### wp0071 - pump"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# tem_0071 = df3_0071[['Measurement Result Date_Minute 5','Average Result Value', 'Asset Code']].sort_values(['Measurement Result Date_Minute 5'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# data = tem_0071[930000:930050]\n",
    "# data.groupby(['Measurement Result Date_Minute 5']).agg({'Average Result Value': 'sum'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sum average result value\n",
    "feature_pmp = df3_0071.groupby(['Measurement Result Date_Minute 5']).agg({'Average Result Value': 'sum'})\n",
    "# feature_pmp['Average Result Value']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(310074, 2)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# change index to specific column\n",
    "feature_pmp['Measurement_Result_Date_Minute_15'] = feature_pmp.index\n",
    "feature_pmp.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### New feature: Average Result Value - indicator for flow speed when pump open "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    310074.000000\n",
       "mean          0.817137\n",
       "std           1.260779\n",
       "min           0.000000\n",
       "25%           0.000000\n",
       "50%           0.000000\n",
       "75%           1.000000\n",
       "max           5.000000\n",
       "Name: Average Result Value, dtype: float64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_pmp['Average Result Value'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_pmp['Measurement_Result_Date_Minute_15'].is_unique"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count                    8641\n",
       "unique                   8641\n",
       "top       2017-01-29 08:15:00\n",
       "freq                        1\n",
       "first     2016-12-01 00:00:00\n",
       "last      2017-03-01 00:00:00\n",
       "Name: timestamp, dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features_32138 = df_feature.loc[df_feature['node_id']==31238]\n",
    "features_32138['timestamp'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features_32138['timestamp'].is_unique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count                    8641\n",
       "unique                   8641\n",
       "top       2017-01-29 08:15:00\n",
       "freq                        1\n",
       "first     2016-12-01 00:00:00\n",
       "last      2017-03-01 00:00:00\n",
       "Name: timestamp, dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features_41908 = df_feature.loc[df_feature['node_id']==41908]\n",
    "features_41908['timestamp'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### New feature: water level\n",
    "- 25/09/2017"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "df4_047 = pd.read_csv('./sample data/R047_levels.csv', parse_dates=['measurement_result_date'],dayfirst=True)\n",
    "df4_047['measurement_result_date'].describe()\n",
    "# drop useless column\n",
    "df4_047 = df4_047.drop(['statistic_type', 'asset_code', 'facility_code'], axis=1)\n",
    "df4_047.columns = ['measurement_result_date', 'water_level']\n",
    "df4_047 = df4_047.sort_values(['measurement_result_date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count                    8640\n",
       "unique                   8640\n",
       "top       2017-01-29 08:15:00\n",
       "freq                        1\n",
       "first     2016-12-01 00:00:00\n",
       "last      2017-02-28 23:45:00\n",
       "Name: measurement_result_date, dtype: object"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4_047['measurement_result_date'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# merge (inner join)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(192618, 7)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge label and pump state\n",
    "df_merge_1 = pd.merge(df1_0118, feature_pmp, left_on='measurement_result_date', right_on='Measurement_Result_Date_Minute_15',how='inner')\n",
    "df_merge_1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge_1['measurement_result_date'].is_unique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8641, 399)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge df with features where node_id = 32138 (same with 41908)\n",
    "df_merge_3 = pd.merge(df_merge_1, features_32138, left_on='measurement_result_date', right_on='timestamp',how='inner')\n",
    "df_merge_3.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    8641.000000\n",
       "mean        0.322301\n",
       "std         0.471330\n",
       "min         0.000000\n",
       "25%         0.000000\n",
       "50%         0.000000\n",
       "75%         1.000000\n",
       "max         2.000000\n",
       "Name: Average Result Value, dtype: float64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge_3['Average Result Value'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2769, 399)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pump_open = df_merge_3.loc[df_merge_3['Average Result Value']>0]\n",
    "df_pump_open.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>measurement_result_date</th>\n",
       "      <th>average_result_value</th>\n",
       "      <th>statistic_type</th>\n",
       "      <th>asset_code</th>\n",
       "      <th>facility_code</th>\n",
       "      <th>Average Result Value</th>\n",
       "      <th>Measurement_Result_Date_Minute_15</th>\n",
       "      <th>zone_flow_hist_0min</th>\n",
       "      <th>zone_flow_hist_15min</th>\n",
       "      <th>zone_flow_hist_30min</th>\n",
       "      <th>...</th>\n",
       "      <th>res_cl_hist_2835min</th>\n",
       "      <th>res_cl_hist_2850min</th>\n",
       "      <th>res_cl_hist_2865min</th>\n",
       "      <th>res_cl_hist_2880min</th>\n",
       "      <th>res_temperature</th>\n",
       "      <th>travel_time_hrs_nom</th>\n",
       "      <th>flow_nom</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>node_id</th>\n",
       "      <th>zone_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-12-01 00:00:00</td>\n",
       "      <td>1.4572</td>\n",
       "      <td>Snap (Current)</td>\n",
       "      <td>WQM01</td>\n",
       "      <td>WQ0118</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-01 00:00:00</td>\n",
       "      <td>0.537967</td>\n",
       "      <td>0.677703</td>\n",
       "      <td>0.838600</td>\n",
       "      <td>...</td>\n",
       "      <td>1.711658</td>\n",
       "      <td>1.709118</td>\n",
       "      <td>1.707396</td>\n",
       "      <td>1.708366</td>\n",
       "      <td>17.4</td>\n",
       "      <td>2.789096</td>\n",
       "      <td>1.931739</td>\n",
       "      <td>2016-12-01 00:00:00</td>\n",
       "      <td>31238.0</td>\n",
       "      <td>engadine_rem_flow_zone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>2016-12-01 00:15:00</td>\n",
       "      <td>1.4560</td>\n",
       "      <td>Snap (Current)</td>\n",
       "      <td>WQM01</td>\n",
       "      <td>WQ0118</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-01 00:15:00</td>\n",
       "      <td>0.374730</td>\n",
       "      <td>0.537967</td>\n",
       "      <td>0.677703</td>\n",
       "      <td>...</td>\n",
       "      <td>1.714862</td>\n",
       "      <td>1.711658</td>\n",
       "      <td>1.709118</td>\n",
       "      <td>1.707396</td>\n",
       "      <td>17.4</td>\n",
       "      <td>2.789096</td>\n",
       "      <td>1.931739</td>\n",
       "      <td>2016-12-01 00:15:00</td>\n",
       "      <td>31238.0</td>\n",
       "      <td>engadine_rem_flow_zone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4088</th>\n",
       "      <td>2016-12-01 00:30:00</td>\n",
       "      <td>1.4540</td>\n",
       "      <td>Snap (Current)</td>\n",
       "      <td>WQM01</td>\n",
       "      <td>WQ0118</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-01 00:30:00</td>\n",
       "      <td>0.228371</td>\n",
       "      <td>0.374730</td>\n",
       "      <td>0.537967</td>\n",
       "      <td>...</td>\n",
       "      <td>1.718593</td>\n",
       "      <td>1.714862</td>\n",
       "      <td>1.711658</td>\n",
       "      <td>1.709118</td>\n",
       "      <td>17.4</td>\n",
       "      <td>2.789096</td>\n",
       "      <td>1.931739</td>\n",
       "      <td>2016-12-01 00:30:00</td>\n",
       "      <td>31238.0</td>\n",
       "      <td>engadine_rem_flow_zone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>2016-12-01 00:45:00</td>\n",
       "      <td>1.4432</td>\n",
       "      <td>Snap (Current)</td>\n",
       "      <td>WQM01</td>\n",
       "      <td>WQ0118</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-01 00:45:00</td>\n",
       "      <td>0.169935</td>\n",
       "      <td>0.228371</td>\n",
       "      <td>0.374730</td>\n",
       "      <td>...</td>\n",
       "      <td>1.720893</td>\n",
       "      <td>1.718593</td>\n",
       "      <td>1.714862</td>\n",
       "      <td>1.711658</td>\n",
       "      <td>17.4</td>\n",
       "      <td>2.789096</td>\n",
       "      <td>1.931739</td>\n",
       "      <td>2016-12-01 00:45:00</td>\n",
       "      <td>31238.0</td>\n",
       "      <td>engadine_rem_flow_zone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>2016-12-01 01:00:00</td>\n",
       "      <td>1.4306</td>\n",
       "      <td>Snap (Current)</td>\n",
       "      <td>WQM01</td>\n",
       "      <td>WQ0118</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-01 01:00:00</td>\n",
       "      <td>0.172979</td>\n",
       "      <td>0.169935</td>\n",
       "      <td>0.228371</td>\n",
       "      <td>...</td>\n",
       "      <td>1.721563</td>\n",
       "      <td>1.720893</td>\n",
       "      <td>1.718593</td>\n",
       "      <td>1.714862</td>\n",
       "      <td>17.4</td>\n",
       "      <td>2.789096</td>\n",
       "      <td>1.931739</td>\n",
       "      <td>2016-12-01 01:00:00</td>\n",
       "      <td>31238.0</td>\n",
       "      <td>engadine_rem_flow_zone</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 399 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     measurement_result_date  average_result_value  statistic_type asset_code  \\\n",
       "0        2016-12-01 00:00:00                1.4572  Snap (Current)      WQM01   \n",
       "42       2016-12-01 00:15:00                1.4560  Snap (Current)      WQM01   \n",
       "4088     2016-12-01 00:30:00                1.4540  Snap (Current)      WQM01   \n",
       "125      2016-12-01 00:45:00                1.4432  Snap (Current)      WQM01   \n",
       "167      2016-12-01 01:00:00                1.4306  Snap (Current)      WQM01   \n",
       "\n",
       "     facility_code  Average Result Value Measurement_Result_Date_Minute_15  \\\n",
       "0           WQ0118                     1               2016-12-01 00:00:00   \n",
       "42          WQ0118                     1               2016-12-01 00:15:00   \n",
       "4088        WQ0118                     1               2016-12-01 00:30:00   \n",
       "125         WQ0118                     1               2016-12-01 00:45:00   \n",
       "167         WQ0118                     1               2016-12-01 01:00:00   \n",
       "\n",
       "      zone_flow_hist_0min  zone_flow_hist_15min  zone_flow_hist_30min  \\\n",
       "0                0.537967              0.677703              0.838600   \n",
       "42               0.374730              0.537967              0.677703   \n",
       "4088             0.228371              0.374730              0.537967   \n",
       "125              0.169935              0.228371              0.374730   \n",
       "167              0.172979              0.169935              0.228371   \n",
       "\n",
       "               ...            res_cl_hist_2835min  res_cl_hist_2850min  \\\n",
       "0              ...                       1.711658             1.709118   \n",
       "42             ...                       1.714862             1.711658   \n",
       "4088           ...                       1.718593             1.714862   \n",
       "125            ...                       1.720893             1.718593   \n",
       "167            ...                       1.721563             1.720893   \n",
       "\n",
       "      res_cl_hist_2865min  res_cl_hist_2880min  res_temperature  \\\n",
       "0                1.707396             1.708366             17.4   \n",
       "42               1.709118             1.707396             17.4   \n",
       "4088             1.711658             1.709118             17.4   \n",
       "125              1.714862             1.711658             17.4   \n",
       "167              1.718593             1.714862             17.4   \n",
       "\n",
       "      travel_time_hrs_nom  flow_nom           timestamp  node_id  \\\n",
       "0                2.789096  1.931739 2016-12-01 00:00:00  31238.0   \n",
       "42               2.789096  1.931739 2016-12-01 00:15:00  31238.0   \n",
       "4088             2.789096  1.931739 2016-12-01 00:30:00  31238.0   \n",
       "125              2.789096  1.931739 2016-12-01 00:45:00  31238.0   \n",
       "167              2.789096  1.931739 2016-12-01 01:00:00  31238.0   \n",
       "\n",
       "                     zone_id  \n",
       "0     engadine_rem_flow_zone  \n",
       "42    engadine_rem_flow_zone  \n",
       "4088  engadine_rem_flow_zone  \n",
       "125   engadine_rem_flow_zone  \n",
       "167   engadine_rem_flow_zone  \n",
       "\n",
       "[5 rows x 399 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pump_open.sort_values(['measurement_result_date']).head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## output data by merging 4 files\n",
    "- 25/09/2018"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "out put of merging all 4 files: (8640, 400)\n"
     ]
    }
   ],
   "source": [
    "# merge 3 files with water level\n",
    "df_merge_4 = pd.merge(df_merge_3, df4_047, left_on='measurement_result_date', right_on='measurement_result_date',how='inner')\n",
    "print \"Output of merging all 4 files:\",df_merge_4.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count                    8640\n",
       "unique                   8640\n",
       "top       2017-01-29 08:15:00\n",
       "freq                        1\n",
       "first     2016-12-01 00:00:00\n",
       "last      2017-02-28 23:45:00\n",
       "Name: measurement_result_date, dtype: object"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge_4['measurement_result_date'].sort_values().describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## output open data by filter all data of 4 files\n",
    "- 25/09/2018"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Output of opening data with water level: (2769, 399)\n"
     ]
    }
   ],
   "source": [
    "df_pump_open_4 = df_merge_3.loc[df_merge_3['Average Result Value']>0]\n",
    "print \"Output of opening data with water level:\", df_pump_open_4.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# save file - 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### sort values by daytime and output\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 8641 - dataset in 3 months\n",
    "df_merge_3.sort_values(['measurement_result_date']).to_csv(output_all_data,index=None)\n",
    "\n",
    "# 2769 - dataset in 3 months only when pump is open\n",
    "df_pump_open.sort_values(['measurement_result_date']).to_csv(file_directory, index=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## save file - 2\n",
    "- 25/09/2018"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 8640 - dataset in 3 months from 4 files\n",
    "df_merge_4.sort_values(['measurement_result_date']).to_csv(outpuut_directory_all, index=None)\n",
    "\n",
    "# 2769 - dataset in 3 months only when pump is open\n",
    "df_pump_open_4.sort_values(['measurement_result_date']).to_csv(outpuut_directory_open, index=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# plot "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "x_axis= df_pump_open['measurement_result_date']\n",
    "y_label = df_pump_open['average_result_value']\n",
    "y_zone_cl = df_pump_open['zone_flow_hist_0min']\n",
    "y_res_cl = df_pump_open['res_cl_hist_0min']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using matplotlib backend: Qt5Agg\n"
     ]
    }
   ],
   "source": [
    "%matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "fig = plt.figure(figsize=(20, 10))\n",
    "\n",
    "ax1 = fig.add_subplot(111)\n",
    "ax1.plot(x_axis, y_label, color='red', label='average_result_value')\n",
    "ax1.plot(x_axis, y_res_cl, color='blue', label='res_cl_hist_0min')\n",
    "ax1.set_ylabel('chlorine')\n",
    "ax1.set_title(\"data visualization\")\n",
    "\n",
    "ax2 = ax1.twinx()  # this is the important function\n",
    "ax2.plot(x_axis, y_zone_cl, 'r', color='green', label='zone_flow_hist_0min')\n",
    "ax2.set_ylabel('folw')\n",
    "ax2.set_xlabel('time')\n",
    "\n",
    "plt.grid(True)\n",
    " \n",
    "plt.legend(bbox_to_anchor=(1.0, 1), loc=1, borderaxespad=0.)\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from pandas import DataFrame\n",
    "df_drop_plot = DataFrame()\n",
    "df_drop_plot['measurement_result_date'] = df_pump_open['measurement_result_date']\n",
    "df_drop_plot['average_result_value'] = df_pump_open['average_result_value']\n",
    "df_drop_plot.plot(x='measurement_result_date', y='average_result_value')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>average_result_value</th>\n",
       "      <th>res_cl_hist_0min</th>\n",
       "      <th>zone_flow_hist_0min</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>measurement_result_date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-01-08 22:00:00</th>\n",
       "      <td>0.7450</td>\n",
       "      <td>1.377217</td>\n",
       "      <td>2.183595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-08 22:15:00</th>\n",
       "      <td>0.7476</td>\n",
       "      <td>1.374898</td>\n",
       "      <td>1.959374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-08 21:30:00</th>\n",
       "      <td>0.7496</td>\n",
       "      <td>1.381701</td>\n",
       "      <td>2.826537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-08 21:45:00</th>\n",
       "      <td>0.7558</td>\n",
       "      <td>1.379573</td>\n",
       "      <td>2.450470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-08 21:15:00</th>\n",
       "      <td>0.7584</td>\n",
       "      <td>1.383780</td>\n",
       "      <td>3.299814</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         average_result_value  res_cl_hist_0min  \\\n",
       "measurement_result_date                                           \n",
       "2017-01-08 22:00:00                    0.7450          1.377217   \n",
       "2017-01-08 22:15:00                    0.7476          1.374898   \n",
       "2017-01-08 21:30:00                    0.7496          1.381701   \n",
       "2017-01-08 21:45:00                    0.7558          1.379573   \n",
       "2017-01-08 21:15:00                    0.7584          1.383780   \n",
       "\n",
       "                         zone_flow_hist_0min  \n",
       "measurement_result_date                       \n",
       "2017-01-08 22:00:00                 2.183595  \n",
       "2017-01-08 22:15:00                 1.959374  \n",
       "2017-01-08 21:30:00                 2.826537  \n",
       "2017-01-08 21:45:00                 2.450470  \n",
       "2017-01-08 21:15:00                 3.299814  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pandas import DataFrame\n",
    "\n",
    "df_plot_1 = DataFrame()\n",
    "df_plot_1= df_pump_open[['average_result_value','res_cl_hist_0min','measurement_result_date','zone_flow_hist_0min']]\n",
    "df_plot_1.index = df_plot_1['measurement_result_date']\n",
    "df_plot = df_plot_1.drop(['measurement_result_date'],axis=1)\n",
    "\n",
    "df_plot.sort_values(['average_result_value']).head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    2769.000000\n",
       "mean        1.276345\n",
       "std         0.139701\n",
       "min         0.745000\n",
       "25%         1.197000\n",
       "50%         1.290200\n",
       "75%         1.369600\n",
       "max         1.617200\n",
       "Name: average_result_value, dtype: float64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_plot['average_result_value'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using matplotlib backend: Qt5Agg\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0xb915278>"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "df_plot.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0xb68be80>"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_plot.plot(secondary_y=['zone_flow_hist_0min'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "a = df_plot.sort_values(['average_result_value'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>average_result_value</th>\n",
       "      <th>res_cl_hist_0min</th>\n",
       "      <th>zone_flow_hist_0min</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>measurement_result_date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-01-26 03:00:00</th>\n",
       "      <td>1.2440</td>\n",
       "      <td>1.441586</td>\n",
       "      <td>0.298297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-17 02:15:00</th>\n",
       "      <td>1.2440</td>\n",
       "      <td>1.478370</td>\n",
       "      <td>0.255367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-02-20 04:15:00</th>\n",
       "      <td>1.2440</td>\n",
       "      <td>1.313414</td>\n",
       "      <td>0.048853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-06 23:45:00</th>\n",
       "      <td>1.2440</td>\n",
       "      <td>1.496672</td>\n",
       "      <td>0.325878</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-22 03:30:00</th>\n",
       "      <td>1.2440</td>\n",
       "      <td>1.488384</td>\n",
       "      <td>0.494244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-05 03:15:00</th>\n",
       "      <td>1.2440</td>\n",
       "      <td>1.421695</td>\n",
       "      <td>0.073985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-16 02:45:00</th>\n",
       "      <td>1.2452</td>\n",
       "      <td>1.448487</td>\n",
       "      <td>0.132165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-16 23:15:00</th>\n",
       "      <td>1.2452</td>\n",
       "      <td>1.447434</td>\n",
       "      <td>1.266805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-23 00:00:00</th>\n",
       "      <td>1.2452</td>\n",
       "      <td>1.476316</td>\n",
       "      <td>0.847696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-06 01:00:00</th>\n",
       "      <td>1.2452</td>\n",
       "      <td>1.421219</td>\n",
       "      <td>0.105343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-26 03:15:00</th>\n",
       "      <td>1.2458</td>\n",
       "      <td>1.442959</td>\n",
       "      <td>0.278486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-22 03:15:00</th>\n",
       "      <td>1.2458</td>\n",
       "      <td>1.474893</td>\n",
       "      <td>0.160065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-21 01:45:00</th>\n",
       "      <td>1.2458</td>\n",
       "      <td>1.458527</td>\n",
       "      <td>0.267834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-02-19 01:15:00</th>\n",
       "      <td>1.2458</td>\n",
       "      <td>1.280944</td>\n",
       "      <td>0.438014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-02 01:45:00</th>\n",
       "      <td>1.2458</td>\n",
       "      <td>1.422632</td>\n",
       "      <td>0.366276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-02-24 01:00:00</th>\n",
       "      <td>1.2458</td>\n",
       "      <td>1.469011</td>\n",
       "      <td>0.341533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-14 23:30:00</th>\n",
       "      <td>1.2458</td>\n",
       "      <td>1.473210</td>\n",
       "      <td>1.202432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-26 02:45:00</th>\n",
       "      <td>1.2458</td>\n",
       "      <td>1.441423</td>\n",
       "      <td>0.318109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-08 00:30:00</th>\n",
       "      <td>1.2458</td>\n",
       "      <td>1.452562</td>\n",
       "      <td>0.308214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-02-21 00:00:00</th>\n",
       "      <td>1.2464</td>\n",
       "      <td>1.421288</td>\n",
       "      <td>0.599528</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-17 00:00:00</th>\n",
       "      <td>1.2464</td>\n",
       "      <td>1.427937</td>\n",
       "      <td>0.649490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-21 04:15:00</th>\n",
       "      <td>1.2464</td>\n",
       "      <td>1.542517</td>\n",
       "      <td>0.055940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-02-14 04:30:00</th>\n",
       "      <td>1.2464</td>\n",
       "      <td>1.372039</td>\n",
       "      <td>0.225539</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-02-20 23:30:00</th>\n",
       "      <td>1.2464</td>\n",
       "      <td>1.402706</td>\n",
       "      <td>0.951957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-13 23:15:00</th>\n",
       "      <td>1.2472</td>\n",
       "      <td>1.439283</td>\n",
       "      <td>1.249714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-15 23:30:00</th>\n",
       "      <td>1.2472</td>\n",
       "      <td>1.425975</td>\n",
       "      <td>0.862713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-02-19 02:00:00</th>\n",
       "      <td>1.2472</td>\n",
       "      <td>1.285755</td>\n",
       "      <td>0.409539</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-14 03:30:00</th>\n",
       "      <td>1.2472</td>\n",
       "      <td>1.497586</td>\n",
       "      <td>0.180111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-01 02:30:00</th>\n",
       "      <td>1.2472</td>\n",
       "      <td>1.437147</td>\n",
       "      <td>0.390184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-02-24 01:15:00</th>\n",
       "      <td>1.2478</td>\n",
       "      <td>1.471848</td>\n",
       "      <td>0.197838</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         average_result_value  res_cl_hist_0min  \\\n",
       "measurement_result_date                                           \n",
       "2017-01-26 03:00:00                    1.2440          1.441586   \n",
       "2017-01-17 02:15:00                    1.2440          1.478370   \n",
       "2017-02-20 04:15:00                    1.2440          1.313414   \n",
       "2016-12-06 23:45:00                    1.2440          1.496672   \n",
       "2017-01-22 03:30:00                    1.2440          1.488384   \n",
       "2017-01-05 03:15:00                    1.2440          1.421695   \n",
       "2017-01-16 02:45:00                    1.2452          1.448487   \n",
       "2017-01-16 23:15:00                    1.2452          1.447434   \n",
       "2017-01-23 00:00:00                    1.2452          1.476316   \n",
       "2017-01-06 01:00:00                    1.2452          1.421219   \n",
       "2017-01-26 03:15:00                    1.2458          1.442959   \n",
       "2016-12-22 03:15:00                    1.2458          1.474893   \n",
       "2017-01-21 01:45:00                    1.2458          1.458527   \n",
       "2017-02-19 01:15:00                    1.2458          1.280944   \n",
       "2017-01-02 01:45:00                    1.2458          1.422632   \n",
       "2017-02-24 01:00:00                    1.2458          1.469011   \n",
       "2017-01-14 23:30:00                    1.2458          1.473210   \n",
       "2017-01-26 02:45:00                    1.2458          1.441423   \n",
       "2016-12-08 00:30:00                    1.2458          1.452562   \n",
       "2017-02-21 00:00:00                    1.2464          1.421288   \n",
       "2016-12-17 00:00:00                    1.2464          1.427937   \n",
       "2016-12-21 04:15:00                    1.2464          1.542517   \n",
       "2017-02-14 04:30:00                    1.2464          1.372039   \n",
       "2017-02-20 23:30:00                    1.2464          1.402706   \n",
       "2017-01-13 23:15:00                    1.2472          1.439283   \n",
       "2017-01-15 23:30:00                    1.2472          1.425975   \n",
       "2017-02-19 02:00:00                    1.2472          1.285755   \n",
       "2017-01-14 03:30:00                    1.2472          1.497586   \n",
       "2017-01-01 02:30:00                    1.2472          1.437147   \n",
       "2017-02-24 01:15:00                    1.2478          1.471848   \n",
       "\n",
       "                         zone_flow_hist_0min  \n",
       "measurement_result_date                       \n",
       "2017-01-26 03:00:00                 0.298297  \n",
       "2017-01-17 02:15:00                 0.255367  \n",
       "2017-02-20 04:15:00                 0.048853  \n",
       "2016-12-06 23:45:00                 0.325878  \n",
       "2017-01-22 03:30:00                 0.494244  \n",
       "2017-01-05 03:15:00                 0.073985  \n",
       "2017-01-16 02:45:00                 0.132165  \n",
       "2017-01-16 23:15:00                 1.266805  \n",
       "2017-01-23 00:00:00                 0.847696  \n",
       "2017-01-06 01:00:00                 0.105343  \n",
       "2017-01-26 03:15:00                 0.278486  \n",
       "2016-12-22 03:15:00                 0.160065  \n",
       "2017-01-21 01:45:00                 0.267834  \n",
       "2017-02-19 01:15:00                 0.438014  \n",
       "2017-01-02 01:45:00                 0.366276  \n",
       "2017-02-24 01:00:00                 0.341533  \n",
       "2017-01-14 23:30:00                 1.202432  \n",
       "2017-01-26 02:45:00                 0.318109  \n",
       "2016-12-08 00:30:00                 0.308214  \n",
       "2017-02-21 00:00:00                 0.599528  \n",
       "2016-12-17 00:00:00                 0.649490  \n",
       "2016-12-21 04:15:00                 0.055940  \n",
       "2017-02-14 04:30:00                 0.225539  \n",
       "2017-02-20 23:30:00                 0.951957  \n",
       "2017-01-13 23:15:00                 1.249714  \n",
       "2017-01-15 23:30:00                 0.862713  \n",
       "2017-02-19 02:00:00                 0.409539  \n",
       "2017-01-14 03:30:00                 0.180111  \n",
       "2017-01-01 02:30:00                 0.390184  \n",
       "2017-02-24 01:15:00                 0.197838  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a[1000:1030]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}