123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122 |
- 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 커튼, 전등
|