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