preprocess.py.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "from numpy import genfromtxt\n",
    "\n",
    "elec_data = genfromtxt('data/elec_final_data_hackathon.csv', delimiter=',', dtype = str)\n",
    "water_data = genfromtxt('data/water_final_data_hackathon.csv', delimiter=',', dtype = str)\n",
    "weather_data = genfromtxt('data/2017-2020_weather_data.csv', delimiter=',', dtype = str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "mueller_weather_data = []\n",
    "for row in weather_data:\n",
    "\tif row[1] == \"30.292432\" and row[2] == \"-97.699662\":\n",
    "\t\tmueller_weather_data.append(row)\n",
    "mueller_weather_data = np.array(mueller_weather_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "35427\n"
     ]
    }
   ],
   "source": [
    "print(len(mueller_weather_data))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "924397\n"
     ]
    }
   ],
   "source": [
    "matches_dict = dict()\n",
    "\n",
    "skip_head = True\n",
    "for row in elec_data:\n",
    "    if skip_head:\n",
    "        skip_head = False\n",
    "        continue\n",
    "    l1 = row[0].split(\" \")\n",
    "    house_id = row[1]\n",
    "    year = l1[0].split(\"-\")[0]\n",
    "    month = l1[0].split(\"-\")[1]\n",
    "    date = l1[0].split(\"-\")[2]\n",
    "    hour = l1[1].split(\":\")[0]\n",
    "    timestamp = (year, month, date, hour, house_id)\n",
    "    val = []\n",
    "    val.append(row[2])\n",
    "    val.append(row[3])\n",
    "    matches_dict[timestamp] = val\n",
    "\n",
    "skip_head = True\n",
    "for row in water_data:\n",
    "    if skip_head:\n",
    "        skip_head = False\n",
    "        continue\n",
    "    l1 = row[0].split(\" \")\n",
    "    house_id = row[1]\n",
    "    year = l1[0].split(\"-\")[0]\n",
    "    month = l1[0].split(\"-\")[1]\n",
    "    date = l1[0].split(\"-\")[2]\n",
    "    hour = l1[1].split(\":\")[0]\n",
    "    timestamp = (year, month, date, hour, house_id)\n",
    "    if timestamp in matches_dict:\n",
    "        matches_dict[timestamp].append(row[2])\n",
    "\n",
    "sum = 0\n",
    "for key in matches_dict:\n",
    "    if len(matches_dict[key]) == 3:\n",
    "        sum += 1\n",
    "print(sum)\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['\"2020-12-31 23:00:00\"' '30.292432' '-97.699662' 'NULL' '\"Overcast\"'\n",
      " 'NULL' 'NULL' '37.37' 'NULL' '34.34' 'NULL' '0.89' 'NULL' '10' 'NULL'\n",
      " '30.42' 'NULL' '1009.3' 'NULL' '9.91' 'NULL' '0.92' 'NULL' '258' '0'\n",
      " 'NULL' '0' 'NULL' 'NULL' 'NULL']\n"
     ]
    }
   ],
   "source": [
    "print(mueller_weather_data[-1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "sample_size = len(mueller_weather_data)\n",
    "time_parsed_data_weather = np.zeros([sample_size,17])\n",
    "time_parsed_data_weather = time_parsed_data_weather.astype('str')\n",
    "for i in range(0,sample_size):\n",
    "    time1 = mueller_weather_data[i][0]\n",
    "    [ymd,hmm] = time1.split()\n",
    "    [y,m,d] = ymd.split('-')\n",
    "    y = y[1:] #hard coding out the \n",
    "    [h,min,sec] = hmm.split(\":\")\n",
    "    all_might_bool_array = np.array([False,True,True,False,True,False,False,False,False,True,False,True,False,True,False,True,False,True,False,True,False,True,False,True,True,False,True,False,False,False])\n",
    "    temp = mueller_weather_data[i][all_might_bool_array]\n",
    "    temp2 = np.concatenate(([y,m,d,h],temp))\n",
    "    time_parsed_data_weather[i,:] = temp2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['2020' '12' '31' '23' '30.292432' '-97.699662' '\"Overcast\"' '34.34'\n",
      " '0.89' '10' '30.42' '1009.3' '9.91' '0.92' '258' '0' '0']\n"
     ]
    }
   ],
   "source": [
    "print(time_parsed_data_weather[-1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "elec_wtr_dict = dict()\n",
    "for key in matches_dict:\n",
    "    if len(matches_dict[key]) == 3:\n",
    "        elec_wtr_dict[key] = matches_dict[key]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "matches_dict = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "timestamp_dict = dict()\n",
    "for key in elec_wtr_dict:\n",
    "    temp = (key[0], key[1], key[2], key[3])\n",
    "    if temp not in timestamp_dict:\n",
    "        secondary_dict = dict()\n",
    "        secondary_dict[key[4]] = elec_wtr_dict[key]\n",
    "        timestamp_dict[temp] = secondary_dict\n",
    "    else:\n",
    "        timestamp_dict[temp][key[4]] = elec_wtr_dict[key]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['30.292432' '-97.699662' '\"Overcast\"' '34.34' '0.89' '10' '30.42'\n",
      " '1009.3' '9.91' '0.92' '258' '0' '0']\n"
     ]
    }
   ],
   "source": [
    "print(time_parsed_data_weather[-1][4:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "33869\n"
     ]
    }
   ],
   "source": [
    "count = 0\n",
    "for row in time_parsed_data_weather:\n",
    "    ts = (row[0], row[1], row[2], row[3])\n",
    "    if ts in timestamp_dict:\n",
    "        count += 1\n",
    "        timestamp_dict[ts][\"weather\"] = row[4:]\n",
    "print(count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'numpy.ndarray'>\n"
     ]
    }
   ],
   "source": [
    "print(type(time_parsed_data_weather[-1][4:]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "922747\n"
     ]
    }
   ],
   "source": [
    "preprocessed_list = []\n",
    "for timestamp in timestamp_dict:\n",
    "    ts_list = list(timestamp)\n",
    "    if \"weather\" in timestamp_dict[timestamp]:\n",
    "        weather_data = timestamp_dict[timestamp][\"weather\"]\n",
    "        for second_key in timestamp_dict[timestamp]:\n",
    "            if second_key != \"weather\":\n",
    "                ts_list.append(second_key)\n",
    "                new_ts_list = ts_list + timestamp_dict[timestamp][second_key]\n",
    "                ts_list.remove(second_key)\n",
    "                preprocessed_list.append(np.concatenate((np.array(new_ts_list), weather_data), axis=None))\n",
    "            \n",
    "print(len(preprocessed_list))\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['2017' '09' '04' '15' '59.0' '2.555' '0.0' '72.832' '30.292432'\n",
      " '-97.699662' '\"Clear\"' '65.18' '0.44' '9.92' '92.09' '1015.44' '4.99'\n",
      " '0.02' '152' '0' '0']\n",
      "['2020' '10' '03' '22' '7989.0' '1.1340000000000001' '-0.008' '0.0'\n",
      " '30.292432' '-97.699662' '\"Clear\"' '60.31' '0.73' '10' '69.22' '1017.7'\n",
      " '5.03' '0' '145' '0' '0']\n",
      "<class 'numpy.str_'>\n"
     ]
    }
   ],
   "source": [
    "print(preprocessed_list[0])\n",
    "print(preprocessed_list[-1])\n",
    "print(type(preprocessed_list[0][0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "\n",
    "with open('data/preprocessed_data.csv', 'w') as f:\n",
    "    csvwriter = csv.writer(f)\n",
    "    csvwriter.writerows(preprocessed_list)"
   ]
  },
  {
   "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.8.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}