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()
# Temperature, Humidity preprocessing
temphum_df = pd.read_csv('temperature_humidity.csv')
temphum_df['timestamp'] = pd.to_datetime(temphum_df.timestamp)
df_temp_hum = temphum_df[ temphum_df['device_id'].isin(['00158d00028d93d8']) ]
df_temp_hum.set_index('timestamp', inplace=True)
df_temp_hum = df_temp_hum.resample(rule='T').median().fillna(method='ffill').fillna(method='bfill')
df_temp_hum = df_temp_hum[['hum', 'temp']].fillna(method='ffill').fillna(method='bfill')
df_temp_hum
# illumination preprocessing
tmp = pd.read_csv('illumination.csv')
tmp['timestamp'] = pd.to_datetime(tmp.timestamp)
tmp.rename(columns={'evt':'illum'}, inplace=True)
df_illumination = tmp[ tmp['device_id'].isin(['00158d0006c9d5ed']) ]
df_illumination.set_index('timestamp', inplace=True)
df_illumination = df_illumination.resample(rule='T').median()#[['device_id', 'hum', 'temp']]
df_illumination = df_illumination[['illum']].fillna(method='ffill').fillna(method='bfill')
df_illumination = df_illumination.join(df_temp_hum)
df_illumination
# motion, door preprocessing
tmp = pd.read_csv('motion_door.csv')
tmp['timestamp'] = pd.to_datetime(tmp.timestamp)
tmp.set_index('timestamp', inplace=True)
df_motion = tmp[tmp.device_id == '00158d0002d545b4'].copy()
df_motion.rename(columns={'evt':'motion'}, inplace=True)
df_motion = df_motion.resample(rule='T').count()
df_door = tmp[tmp.device_id == '00158d0005bb96f3'].copy()
df_door.rename(columns={'evt':'door'}, inplace=True)
df_door = df_door.resample(rule='T').count()
df_motion_door = pd.concat([df_motion, df_door])[['motion', 'door']].fillna(value=0)
df_merge = df_motion_door.join(df_illumination)
df_merge.to_csv('df_merge.csv')
df_merge
# read and merge label
df_l1 = pd.read_csv('20210830_080618A.csv')
df_l2 = pd.read_csv('20211025_080015A.csv')
df_label = pd.concat([df_l1, df_l2], ignore_index=True)
df_label.rename(columns={df_label.columns[1]:'timestamp', df_label.columns[2]:'label'}, inplace=True)
df_label['timestamp'] = pd.to_datetime(df_label.timestamp)
# filtering
df_label = df_label.loc[ (df_label['timestamp'] > '2021-01-01') ]
df_label.set_index('timestamp', inplace=True)
# resampling
df_label = df_label.resample(rule='T').max()
df_label = df_label[['label']]
df_label = df_merge.join(df_label).resample(rule='T').max()
df_label_8030_0916 = df_label.loc[ '2021-08-30 08:00': '2021-09-16 18:00'].fillna(value=0)
df_label_1025_1103 = df_label.loc[ '2021-10-25 08:00': '2021-11-03 18:00'].fillna(value=0)
df_label.to_csv('df_labeled.csv')
df_label_1025_1103.to_csv('df_label_1025_1103.csv')
df_label_8030_0916.to_csv('df_label_8030_0916.csv')