import pandas as pd # from pycaret.classification import * import librosa as fe import awswrangler as wr from datetime import datetime import requests 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): 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) print(df.tail(1)) return df def get_date_colum(df): 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 return tmp def get_feature(db_name="ambt_b2c", date=None): ''' input : table name 과 device id 는 수동으로 조절 해야 함 outpu : 지정한 date의 featrue 를 {컬럼명:컬럼값} 으로 변환하여 리턴 ''' if date == None : date = datetime.now().strftime('%Y-%m-%d %H:%M:%S') print(f'forced setting : time stamp = {date}' ) 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) return get_date_colum(df) def test(date=None): if date==None: return 0 tmp = get_feature(date=date) print(tmp) tmp.pop('timestamp') feature = [f'{x}={tmp[x]}' for x in tmp] URL = 'http://localhost:8000/predict?{}'.format('&'.join(feature)) print(URL) response = requests.get(URL) return response def my_read(date=None, fn='data/data_all.csv'): df = pd.read_csv(fn) return df def test_csv(df, date=None): if date==None: return 0 label = df['label'] tmp = df[df['timestamp'] == date.strftime('%Y-%m-%d %H:%M:%S')] tmp['timestamp'] = pd.to_datetime(tmp['timestamp']) tmp = get_date_colum(tmp) tmp.pop('timestamp') tmp.pop('label') tmp.pop('index') feature = [f'{x}={tmp[x]}' for x in tmp] # print(feature) URL = 'http://localhost:8000/predict?{}'.format('&'.join(feature)) print(URL) response = requests.get(URL) return response from pandas.tseries.offsets import Day, Hour, Minute, Second if __name__ == '__main__': ''' 예제 client code ''' # tmp = get_feature(date='2021-09-09 11:05:00') # tmp = get_feature(date='2021-08-30 08:06:00') # 08:07~08:15 # tmp = get_feature(date='2021-08-30 09:01:00') # 08:07~08:15 # 10:10~10:13 = true sq = pd.date_range('2021-08-30 08:10:00', periods=10, freq=Minute(1)) print(sq) r = [] df = pd.read_csv('data/data_all.csv') for x in sq: # ret = test(x) ret = test_csv(df, x) r.append( [x, ret] ) print(x, ret.text) for x, ret in r: print(x, ret.text) # todo : requests 커튼, 전등