# import awswrangler as wr
import pandas as pd
import numpy as np
# import boto3
# from sagemaker import get_execution_role
import datetime
import string
import random
# role = get_execution_role()
def preproc_common(fn, id):
tmp = pd.read_csv(fn)
tmp['timestamp'] = pd.to_datetime(tmp.timestamp)
tmp.set_index('timestamp', inplace=True)
return tmp[tmp.device_id == id].copy()
def preproc_th(fn='temperature_humidity.csv', id='00158d00028d93d8'):
# Temperature, Humidity preprocessing
df_temp_hum = preproc_common(fn, id)
df_temp_hum = df_temp_hum.resample(rule='T').median()
return df_temp_hum[['hum', 'temp']].fillna(method='ffill').fillna(method='bfill')
def preproc_il(fn='illumination.csv', id='00158d0006c9d5ed'):
# illumination preprocessing
df_illumination = preproc_common(fn, id)
df_illumination.rename(columns={'evt':'illum'}, inplace=True)
df_illumination = df_illumination.resample(rule='T').median()
return df_illumination[['illum']].fillna(method='ffill').fillna(method='bfill')
def preproc_motion(fn='motion_door.csv', id='00158d0002d545b4'):
df_motion = preproc_common(fn, id)
df_motion.rename(columns={'evt':'motion'}, inplace=True)
df_motion = df_motion.resample(rule='T').count()
return df_motion['motion'].fillna(value=0)
def preproc_door(fn='motion_door.csv', id='00158d0005bb96f3'):
df_door = preproc_common(fn, id)
df_door.rename(columns={'evt':'door'}, inplace=True)
df_door = df_door.resample(rule='T').count()
return df_door['door'].fillna(value=0)
df = preproc_th('data/mr_temperature_humidity_0104.csv', '00158d0006c9d545')
df = df.join(preproc_door('data/mr_door_0104.csv', '00158d0005bb968a'))
df = df.join(preproc_motion('data/mr_motion_0104.csv', '00158d0002cab225'))
df = df.join(preproc_il('data/mr_illumination_0104.csv', '00158d0005a9998b'))
df_rnd = df.copy()
df_rnd.to_csv('data/mr_0104.csv')
df = preproc_th('data/m3_temperature_humidity_0104.csv', '00158d00028d93d8')
df = df.join(preproc_door('data/m3_door_0104.csv', '00158d0005bb96f3'))
df = df.join(preproc_motion('data/m3_motion_0104.csv', '00158d0002d545b4'))
df = df.join(preproc_il('data/m3_illumination_0104.csv', '00158d0006c9d5ed'))
df_m3 = df.copy()
df_m3.to_csv('data/m3_0104.csv')
# read and merge label
# "20211105_193120A.mp4" 동영상 부터 Time Sink 조절 필요
# time stamp 가 실제 시간보다 약 36시간 22분 빠름
import glob
def my_read(fn):
print(fn)
df = pd.read_csv(fn)
df.rename(columns={df.columns[1]:'timestamp', df.columns[2]:'label'}, inplace=True)
# 타임스템프에 문제가 있는 행 NaT 로 대체
df['timestamp'] = pd.to_datetime(df.timestamp, errors='coerce')
return df
dfs = []
for elm in glob.glob('data/video_label_m3/*.csv'):
dfs.append(my_read(elm))
df_label = pd.concat(dfs, ignore_index=True)
# 3개 구간으로 나누어 처리
df_label.set_index('timestamp', inplace=True)
df_label_a = df_label.loc['2021-08-30 08:00': '2021-09-16 18:00'].resample(rule='T').max().fillna(value=0).copy()
df_label_b = df_label.loc['2021-10-25 08:00': '2021-11-04 04:00'].resample(rule='T').max().fillna(value=0).copy()
# 마지막 구간은 36시간 22분이 밀려있음
df_label_c = df_label.loc['2021-11-05 19:00': '2021-12-02 00:00'].resample(rule='T').max().fillna(value=0).copy()
df_label_c.reset_index(inplace=True)
# 36H 22M= 60*36 + 22 (minute)
df_label_c['timestamp'] = df_label_c['timestamp'] - pd.DateOffset(minutes=2182)
df_label_c.set_index('timestamp', inplace=True)
df_label = pd.concat([df_label_a, df_label_b, df_label_c])
df_label = df_label['label']
print(df_label)
df_data = df_m3.join(df_label)
# df_data.to_csv('data/df_data.csv')
data/video_label_m3\20210830_080618A.csv data/video_label_m3\20211025_080015A.csv data/video_label_m3\20211103_095316A.csv data/video_label_m3\20211105_193120A.csv data/video_label_m3\20211109_203305A.csv data/video_label_m3\20211117_003420A.csv data/video_label_m3\20211124_001105A.csv timestamp 2021-08-30 08:06:00 0.0 2021-08-30 08:07:00 1.0 2021-08-30 08:08:00 1.0 2021-08-30 08:09:00 1.0 2021-08-30 08:10:00 1.0 ... 2021-11-30 11:34:00 0.0 2021-11-30 11:35:00 0.0 2021-11-30 11:36:00 0.0 2021-11-30 11:37:00 0.0 2021-11-30 11:38:00 0.0 Name: label, Length: 76909, dtype: float64 <ipython-input-4-008aa259fb8c>:21: FutureWarning: Value based partial slicing on non-monotonic DatetimeIndexes with non-existing keys is deprecated and will raise a KeyError in a future Version. df_label_a = df_label.loc['2021-08-30 08:00': '2021-09-16 18:00'].resample(rule='T').max().fillna(value=0).copy() <ipython-input-4-008aa259fb8c>:22: FutureWarning: Value based partial slicing on non-monotonic DatetimeIndexes with non-existing keys is deprecated and will raise a KeyError in a future Version. df_label_b = df_label.loc['2021-10-25 08:00': '2021-11-04 04:00'].resample(rule='T').max().fillna(value=0).copy() <ipython-input-4-008aa259fb8c>:24: FutureWarning: Value based partial slicing on non-monotonic DatetimeIndexes with non-existing keys is deprecated and will raise a KeyError in a future Version. df_label_c = df_label.loc['2021-11-05 19:00': '2021-12-02 00:00'].resample(rule='T').max().fillna(value=0).copy()
from sklearn.preprocessing import MinMaxScaler
min_max_scaler = MinMaxScaler()
def postproc(df):
scaler = min_max_scaler.fit(df[['hum', 'temp']])
# df[['hum_p', 'temp_p']] = (df[['hum', 'temp']] - scaler.data_min_) / scaler.data_max_
df.reset_index(inplace=True)
df['dayofweek'] = df['timestamp'].dt.dayofweek
df.set_index('timestamp', inplace=True)
return df
a = postproc( df_data.loc['2021-08-30':'2021-09-13'].fillna(value=0).copy() )
a.to_csv('data/data_0831_0910.csv')
b = postproc( df_data.loc['2021-10-28':'2021-11-26'].fillna(value=0).copy() )
b.to_csv('data/data_1028_1126.csv')
pd.concat([a, b]).to_csv('data/data_all.csv')