simplejointest.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import glob\n",
    "import pandas as pd\n",
    "#%matplotlib inline\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = ''\n",
    "\n",
    "all_files = glob.glob(os.path.join(path, \"*.csv\")) \n",
    "\n",
    "names = [os.path.basename(x) for x in glob.glob(path+'\\*.csv')] \n",
    "\n",
    "df = pd.DataFrame()\n",
    "for file_ in all_files:\n",
    "    file_df = pd.read_csv(file_, sep=',', parse_dates=[\"capture_datetime_utc\"], index_col=\"capture_datetime_utc\")\n",
    "    file_df['file_name'] = file_ \n",
    "    df = df.append(file_df)\n",
    "inplace=True\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>fertilizer_level</th>\n",
       "      <th>light</th>\n",
       "      <th>soil_moisture_percent</th>\n",
       "      <th>air_temperature_celsius</th>\n",
       "      <th>file_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>capture_datetime_utc</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>2018-07-30 17:34:33</th>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.28</td>\n",
       "      <td>12.13</td>\n",
       "      <td>26.42</td>\n",
       "      <td>Flower power 5991.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-30 17:49:33</th>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.26</td>\n",
       "      <td>11.87</td>\n",
       "      <td>26.51</td>\n",
       "      <td>Flower power 5991.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-30 18:04:33</th>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.26</td>\n",
       "      <td>11.47</td>\n",
       "      <td>26.37</td>\n",
       "      <td>Flower power 5991.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-30 18:19:33</th>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.17</td>\n",
       "      <td>12.00</td>\n",
       "      <td>26.28</td>\n",
       "      <td>Flower power 5991.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-30 18:34:33</th>\n",
       "      <td>-1.0</td>\n",
       "      <td>0.94</td>\n",
       "      <td>11.47</td>\n",
       "      <td>25.34</td>\n",
       "      <td>Flower power 5991.csv</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      fertilizer_level  light  soil_moisture_percent  \\\n",
       "capture_datetime_utc                                                   \n",
       "2018-07-30 17:34:33               -1.0   1.28                  12.13   \n",
       "2018-07-30 17:49:33               -1.0   1.26                  11.87   \n",
       "2018-07-30 18:04:33               -1.0   1.26                  11.47   \n",
       "2018-07-30 18:19:33               -1.0   1.17                  12.00   \n",
       "2018-07-30 18:34:33               -1.0   0.94                  11.47   \n",
       "\n",
       "                      air_temperature_celsius              file_name  \n",
       "capture_datetime_utc                                                  \n",
       "2018-07-30 17:34:33                     26.42  Flower power 5991.csv  \n",
       "2018-07-30 17:49:33                     26.51  Flower power 5991.csv  \n",
       "2018-07-30 18:04:33                     26.37  Flower power 5991.csv  \n",
       "2018-07-30 18:19:33                     26.28  Flower power 5991.csv  \n",
       "2018-07-30 18:34:33                     25.34  Flower power 5991.csv  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "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>fertilizer_level</th>\n",
       "      <th>light</th>\n",
       "      <th>soil_moisture_percent</th>\n",
       "      <th>air_temperature_celsius</th>\n",
       "      <th>file_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>capture_datetime_utc</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>2018-08-28 07:50:23</th>\n",
       "      <td>-1.0</td>\n",
       "      <td>40.73</td>\n",
       "      <td>6.53</td>\n",
       "      <td>31.82</td>\n",
       "      <td>Flower power 9417.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-28 08:05:23</th>\n",
       "      <td>-1.0</td>\n",
       "      <td>47.13</td>\n",
       "      <td>6.65</td>\n",
       "      <td>33.65</td>\n",
       "      <td>Flower power 9417.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-28 08:20:23</th>\n",
       "      <td>-1.0</td>\n",
       "      <td>51.94</td>\n",
       "      <td>6.65</td>\n",
       "      <td>35.00</td>\n",
       "      <td>Flower power 9417.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-28 08:35:23</th>\n",
       "      <td>-1.0</td>\n",
       "      <td>57.46</td>\n",
       "      <td>6.65</td>\n",
       "      <td>36.55</td>\n",
       "      <td>Flower power 9417.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-28 08:50:23</th>\n",
       "      <td>-1.0</td>\n",
       "      <td>14.17</td>\n",
       "      <td>6.77</td>\n",
       "      <td>32.98</td>\n",
       "      <td>Flower power 9417.csv</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      fertilizer_level  light  soil_moisture_percent  \\\n",
       "capture_datetime_utc                                                   \n",
       "2018-08-28 07:50:23               -1.0  40.73                   6.53   \n",
       "2018-08-28 08:05:23               -1.0  47.13                   6.65   \n",
       "2018-08-28 08:20:23               -1.0  51.94                   6.65   \n",
       "2018-08-28 08:35:23               -1.0  57.46                   6.65   \n",
       "2018-08-28 08:50:23               -1.0  14.17                   6.77   \n",
       "\n",
       "                      air_temperature_celsius              file_name  \n",
       "capture_datetime_utc                                                  \n",
       "2018-08-28 07:50:23                     31.82  Flower power 9417.csv  \n",
       "2018-08-28 08:05:23                     33.65  Flower power 9417.csv  \n",
       "2018-08-28 08:20:23                     35.00  Flower power 9417.csv  \n",
       "2018-08-28 08:35:23                     36.55  Flower power 9417.csv  \n",
       "2018-08-28 08:50:23                     32.98  Flower power 9417.csv  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8372, 5)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(712, 4)\n"
     ]
    }
   ],
   "source": [
    "# To match weather data periodicity, We downsample df from 15 minute intervals to hourly frequency, aggregated using mean\n",
    "df_h = df.resample('H').mean()\n",
    "print(df_h.shape)"
   ]
  },
  {
   "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>fertilizer_level</th>\n",
       "      <th>light</th>\n",
       "      <th>soil_moisture_percent</th>\n",
       "      <th>air_temperature_celsius</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>capture_datetime_utc</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-07-30 17:00:00</th>\n",
       "      <td>-1.0</td>\n",
       "      <td>27.216667</td>\n",
       "      <td>10.793333</td>\n",
       "      <td>31.073333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-30 18:00:00</th>\n",
       "      <td>-1.0</td>\n",
       "      <td>10.272500</td>\n",
       "      <td>10.735833</td>\n",
       "      <td>26.864167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-30 19:00:00</th>\n",
       "      <td>-1.0</td>\n",
       "      <td>0.769167</td>\n",
       "      <td>10.617500</td>\n",
       "      <td>23.590833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-30 20:00:00</th>\n",
       "      <td>-1.0</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>10.695833</td>\n",
       "      <td>22.535000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-30 21:00:00</th>\n",
       "      <td>-1.0</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>10.612500</td>\n",
       "      <td>22.110000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      fertilizer_level      light  soil_moisture_percent  \\\n",
       "capture_datetime_utc                                                       \n",
       "2018-07-30 17:00:00               -1.0  27.216667              10.793333   \n",
       "2018-07-30 18:00:00               -1.0  10.272500              10.735833   \n",
       "2018-07-30 19:00:00               -1.0   0.769167              10.617500   \n",
       "2018-07-30 20:00:00               -1.0   0.100000              10.695833   \n",
       "2018-07-30 21:00:00               -1.0   0.100000              10.612500   \n",
       "\n",
       "                      air_temperature_celsius  \n",
       "capture_datetime_utc                           \n",
       "2018-07-30 17:00:00                 31.073333  \n",
       "2018-07-30 18:00:00                 26.864167  \n",
       "2018-07-30 19:00:00                 23.590833  \n",
       "2018-07-30 20:00:00                 22.535000  \n",
       "2018-07-30 21:00:00                 22.110000  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_h.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problems; next steps\n",
    "Two surprises here, namely:\n",
    "1. rows: reduced by a factor of, not 4 as i expected, but... 12?\n",
    "2. columns: looks like we lost one (or: the last script took effect on the sensor data model, without filename appendage)\n",
    "\n",
    "Re 1: ah... Makes sense, when you consider that there are now not just 4 timestamps per hour, but 4x3(files)!\n",
    "\n",
    "And re 2:  i've tried inserting \"inplace=True\" everywhere, but it has no effect"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}