{ "cells": [ { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "# from pycaret.classification import *\n", "import librosa as fe\n", "import awswrangler as wr\n", "from datetime import datetime" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "\n", "def preproc(df, id, tb_name):\n", " df['timestamp'] = pd.to_datetime(df.timestamp)\n", " df.set_index('timestamp', inplace=True) \n", " df_proc = df[df.device_id == id]\n", " if tb_name=='temperature_humidity': \n", " df_proc = df_proc.resample(rule='T').median()\n", " return df_proc[['hum', 'temp']].fillna(method='ffill').fillna(method='bfill')\n", " elif tb_name=='illumination':\n", " df_proc.rename(columns={'evt':'illum'}, inplace=True)\n", " df_proc = df_proc.resample(rule='T').median()\n", " return df_proc[['illum']].fillna(method='ffill').fillna(method='bfill')\n", " elif tb_name=='motion_door':\n", " if id == '00158d0002d545b4': \n", " df_proc.rename(columns={'evt':'motion'}, inplace=True)\n", " df_proc = df_proc.resample(rule='T').count()\n", " return df_proc['motion'].fillna(value=0)\n", " elif id == '00158d0005bb96f3': \n", " df_proc.rename(columns={'evt':'door'}, inplace=True)\n", " df_proc = df_proc.resample(rule='T').count()\n", " return df_proc['door'].fillna(value=0)\n", " else: \n", " return None\n", " else :\n", " return None\n", "\n", "def get_db(tb_name, dev_id, db_name=\"ambt_b2c\", date=None):\n", " if date == None :\n", " date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')\n", " print(f'time stamp = {date}' )\n", " query = f\"SELECT * FROM {tb_name} where device_id = '{dev_id}' and timestamp <= '{date}' order by timestamp desc limit 10\"\n", " df = wr.athena.read_sql_query(sql=query, database=db_name)\n", " df = preproc(df, dev_id, tb_name)\n", " return df" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "date='2021-09-09 11:05:00'\n", "# date='2021-09-09 09:59:00'\n", "db_name=\"ambt_b2c\"\n", "df = get_db('temperature_humidity', '00158d00028d93d8', db_name, date) #3회의실온습도센서\n", "df = df.join( get_db('illumination', '00158d0006c9d5ed', db_name, date) ) #3회의실조도센서\n", "df = df.join( get_db('motion_door', '00158d0002d545b4', db_name, date) ) #3회의실모션센서\n", "df = df.join( get_db('motion_door', '00158d0005bb96f3', db_name, date) ) #3회의실문열림센서\n", "df = df.resample(rule='T').max().fillna(value=0)\n", "print(df)\n", "df.to_csv('out.csv')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "tmp = df.iloc[-1:,:].reset_index().T.to_dict()[0]\n", "tmp['dayofweek'] = tmp['timestamp'].dayofweek\n", "tmp['month'] = tmp['timestamp'].month\n", "tmp['day'] = tmp['timestamp'].day\n", "tmp['hour'] = tmp['timestamp'].hour\n", "tmp['minute'] = tmp['timestamp'].minute\n", "# tmp['timestamp'] = tmp['timestamp'].strftime('%Y-%m-%d %I:%M:%S')\n", "\n", "tmp.pop('timestamp')\n", "feature = [f'{x}={tmp[x]}' for x in tmp]\n", "print('&'.join(feature))\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import requests \n", "URL = 'http://localhost:8000/predict?{}'.format('&'.join(feature))\n", "print(URL)\n", "response = requests.get(URL)\n", "print(response.status_code)\n", "print(response.text)\n", "\n", "# todo : requests 커튼, 전등" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from pandas.tseries.offsets import Day, Hour, Minute, Second\n", "sq = pd.date_range('2021-08-30 08:06:00', periods=8, freq=Minute(1))\n", "for x in sq:\n", " print(x)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# def my_read(date=None, fn='data/data_all.csv'):\n", "fn='data/data_all.csv'\n", "df = pd.read_csv(fn)\n", "# df.set_index('timestamp')\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "\n", "tmp = df[df['timestamp'] == '2021-08-30 08:06:00']\n", "tmp['timestamp'] = pd.to_datetime(tmp['timestamp'])\n", "tmp['dayofweek'] = tmp['timestamp'].dt.dayofweek\n", "tmp['month'] = tmp['timestamp'].dt.month\n", "tmp['day'] = tmp['timestamp'].dt.day\n", "tmp['hour'] = tmp['timestamp'].dt.hour\n", "tmp['minute'] = tmp['timestamp'].dt.minute\n", "tmp.dtypes\n", "tmp" ] } ], "metadata": { "interpreter": { "hash": "9ec2ae184083cd6542d38ad63372b759491f365094fe5916f5948cdad9d01140" }, "kernelspec": { "display_name": "Python 3.8.12 ('iot')", "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.12" }, "orig_nbformat": 4 }, "nbformat": 4, "nbformat_minor": 2 }