{ "cells": [ { "cell_type": "code", "execution_count": 1, "id": "4b1f1252", "metadata": { "execution": { "iopub.execute_input": "2021-07-29T01:21:45.453765Z", "iopub.status.busy": "2021-07-29T01:21:45.450392Z", "iopub.status.idle": "2021-07-29T01:21:45.455282Z", "shell.execute_reply": "2021-07-29T01:21:45.455814Z" } }, "outputs": [], "source": [ "import sys" ] }, { "cell_type": "code", "execution_count": 2, "id": "f2281041", "metadata": { "execution": { "iopub.execute_input": "2021-07-29T01:21:45.464756Z", "iopub.status.busy": "2021-07-29T01:21:45.464064Z", "iopub.status.idle": "2021-07-29T01:22:06.903982Z", "shell.execute_reply": "2021-07-29T01:22:06.902931Z" } }, "outputs": [], "source": [ "# Install PyAthena\n", "!{sys.executable} -m pip install -q --upgrade pip\n", "!{sys.executable} -m pip install -q PyAthena\n", "!{sys.executable} -m pip install -q awswrangler\n", "!{sys.executable} -m pip install -q pymysql\n" ] }, { "cell_type": "code", "execution_count": 61, "id": "dd3b8f3d", "metadata": { "execution": { "iopub.execute_input": "2021-07-29T01:22:06.910338Z", "iopub.status.busy": "2021-07-29T01:22:06.908127Z", "iopub.status.idle": "2021-07-29T01:23:00.075715Z", "shell.execute_reply": "2021-07-29T01:23:00.076272Z" } }, "outputs": [], "source": [ "#!/usr/bin/env python\n", "# -*- coding: utf-8 -*-\n", "\n", "import pandas as pd\n", "import datetime\n", "import numpy as np\n", "import pymysql\n", "import boto3\n", "import awswrangler as wr\n", "\n", "### connection info\n", "rds_host_write = \"database-ambt.cluster-cvnwxgmngsms.ap-northeast-2.rds.amazonaws.com\"\n", "rds_host_read = \"database-ambt.cluster-ro-cvnwxgmngsms.ap-northeast-2.rds.amazonaws.com\"\n", "name = \"admin\"\n", "password = \"hdci12#$\"\n", "db_name = \"ambt\"\n", "\n", "PORT=3306\n", "\n", "\n", "conn = pymysql.connect(host=rds_host_read, user=name, passwd=password, port=PORT, database=db_name, charset='utf8')\n", "#conn = pymysql.connect(host=rds_host, user=name, passwd=password, db=db_name, use_unicode=True, charset='utf8', connect_timeout=5)\n", "\n", "sql_statement = \"SELECT * FROM `ambt.icos`.facility_type;\"\n", "facilityTypeId = pd.read_sql(sql=sql_statement, con=conn)\n", "\n", "sql_statement = \"SELECT * FROM `ambt.icos`.facility_code;\"\n", "facilityCode = pd.read_sql(sql=sql_statement, con=conn)\n", "\n", "sql_statement = \"SELECT * FROM `ambt.icos`.material_code;\"\n", "materialCode = pd.read_sql(sql=sql_statement, con=conn)\n", "\n", "sql_statement = \"SELECT * FROM `ambt.icos`.control_value;\"\n", "controlValue = pd.read_sql(sql=sql_statement, con=conn)\n", "\n", "sql_statement = \"SELECT * FROM `ambt.icos`.value_type;\"\n", "valueType = pd.read_sql(sql=sql_statement, con=conn)\n", "\n", "sql_statement = \"SELECT * FROM `ambt.icos`.ambt_anoicos_code;\"\n", "mappingTable = pd.read_sql(sql=sql_statement, con=conn)\n", "\n", "\n", "s3_resource = boto3.resource('s3')\n", "bucket = s3_resource.Bucket('hdci-ambt-anoicos-prep')\n", "\n", "now = datetime.datetime.now()\n", "updated_date = []\n", "updated_key = []\n", "for bucket_obj in bucket.objects.all():\n", " calculated_date = bucket_obj.last_modified + datetime.timedelta(hours=9)\n", " key = bucket_obj.key\n", " target_year = int(key.split('/')[2].split('=')[-1])\n", " target_month = int(key.split('/')[3].split('=')[-1])\n", " target_day = int(key.split('/')[4].split('=')[-1])\n", " \n", " appended_data = []\n", " if now.year == calculated_date.year \\\n", " and now.month == calculated_date.month \\\n", " and now.day == calculated_date.day:\n", " \n", " #body = obj.get()['Body'].read()\n", " processed_data = pd.read_csv(bucket_obj.get()['Body'], sep=',')\n", " processed_data = processed_data.drop(['Unnamed: 0'], axis=1)\n", " \n", " \n", " ## ----------------------------------------------------------------------------------------------\n", " ## Service - physics-based anomaly detection for simultaneous valve opening\n", " \n", " # facility_type = 1(ahu)\n", " # facility_code = 4(AHU4_2F)\n", " # facility_code = 5(AHU6_3F)\n", " # facility_code = 6(AHU6_4F)\n", " # facility_code = 7(AHU6_5F)\n", " # facility_code = 8(AHU6_6F)\n", " # facility_code = 9(AHU6_7F)\n", " # facility_code = 10(AHU6_8F)\n", " # facility_code = 11(AHU6_9F)\n", " f_c_ahu4_2F = 4\n", " f_c_ahu6_3F = 5\n", " f_c_ahu6_4F = 6\n", " f_c_ahu6_5F = 7\n", " f_c_ahu6_6F = 8\n", " f_c_ahu6_7F = 9\n", " f_c_ahu6_8F = 10\n", " f_c_ahu6_9F = 11\n", " AHU4_row = mappingTable.loc[(mappingTable['site_id'] == 1) & (mappingTable['collect_status'] == 1) & \\\n", " (mappingTable['facility_code'] == f_c_ahu4_2F)]\n", " AHU3F_row = mappingTable.loc[(mappingTable['site_id'] == 1) & (mappingTable['collect_status'] == 1) & \\\n", " (mappingTable['facility_code'] == f_c_ahu6_3F)]\n", " AHU4F_row = mappingTable.loc[(mappingTable['site_id'] == 1) & (mappingTable['collect_status'] == 1) & \\\n", " (mappingTable['facility_code'] == f_c_ahu6_4F)]\n", " AHU5F_row = mappingTable.loc[(mappingTable['site_id'] == 1) & (mappingTable['collect_status'] == 1) & \\\n", " (mappingTable['facility_code'] == f_c_ahu6_5F)]\n", " AHU6F_row = mappingTable.loc[(mappingTable['site_id'] == 1) & (mappingTable['collect_status'] == 1) & \\\n", " (mappingTable['facility_code'] == f_c_ahu6_6F)]\n", " AHU7F_row = mappingTable.loc[(mappingTable['site_id'] == 1) & (mappingTable['collect_status'] == 1) & \\\n", " (mappingTable['facility_code'] == f_c_ahu6_7F)]\n", " AHU8F_row = mappingTable.loc[(mappingTable['site_id'] == 1) & (mappingTable['collect_status'] == 1) & \\\n", " (mappingTable['facility_code'] == f_c_ahu6_8F)]\n", " AHU9F_row = mappingTable.loc[(mappingTable['site_id'] == 1) & (mappingTable['collect_status'] == 1) & \\\n", " (mappingTable['facility_code'] == f_c_ahu6_9F)]\n", " \n", " # facility_type = 19(밸브)\n", " # control_value = 7(개도율)\n", " # material_code = 10(냉수)\n", " # material_code = 11(온수)\n", " c_v_open_ratio = 7\n", " m_c_cool_water = 10\n", " m_c_hot_water = 11\n", " \n", " ahu4_cool_open_ratio_tag = AHU4_row.loc[(AHU4_row['control_value']==c_v_open_ratio) & \\\n", " (AHU4_row['material_code']==m_c_cool_water)]['raw_tag'].values\n", " ahu4_hot_open_ratio_tag = AHU4_row.loc[(AHU4_row['control_value']==c_v_open_ratio) & \\\n", " (AHU4_row['material_code']==m_c_hot_water)]['raw_tag'].values\n", " \n", " ahu3F_name = facilityCode.loc[(facilityCode['facility_code']==f_c_ahu6_3F)]['facility_code_name']\n", " ahu3F_cool_open_ratio_tag = AHU3F_row.loc[(AHU3F_row['control_value']==c_v_open_ratio) & \\\n", " (AHU3F_row['material_code']==m_c_cool_water)]['raw_tag'].values\n", " ahu3F_hot_open_ratio_tag = AHU3F_row.loc[(AHU3F_row['control_value']==c_v_open_ratio) & \\\n", " (AHU3F_row['material_code']==m_c_hot_water)]['raw_tag'].values\n", " \n", " ahu4F_name = facilityCode.loc[(facilityCode['facility_code']==f_c_ahu6_4F)]['facility_code_name']\n", " ahu4F_cool_open_ratio_tag = AHU4F_row.loc[(AHU4F_row['control_value']==c_v_open_ratio) & \\\n", " (AHU4F_row['material_code']==m_c_cool_water)]['raw_tag'].values\n", " ahu4F_hot_open_ratio_tag = AHU4F_row.loc[(AHU4F_row['control_value']==c_v_open_ratio) & \\\n", " (AHU4F_row['material_code']==m_c_hot_water)]['raw_tag'].values\n", " \n", " ahu5F_name = facilityCode.loc[(facilityCode['facility_code']==f_c_ahu6_5F)]['facility_code_name']\n", " ahu5F_cool_open_ratio_tag = AHU5F_row.loc[(AHU5F_row['control_value']==c_v_open_ratio) & \\\n", " (AHU5F_row['material_code']==m_c_cool_water)]['raw_tag'].values\n", " ahu5F_hot_open_ratio_tag = AHU5F_row.loc[(AHU5F_row['control_value']==c_v_open_ratio) & \\\n", " (AHU5F_row['material_code']==m_c_hot_water)]['raw_tag'].values\n", " \n", " ahu6F_name = facilityCode.loc[(facilityCode['facility_code']==f_c_ahu6_6F)]['facility_code_name']\n", " ahu6F_cool_open_ratio_tag = AHU6F_row.loc[(AHU6F_row['control_value']==c_v_open_ratio) & \\\n", " (AHU6F_row['material_code']==m_c_cool_water)]['raw_tag'].values\n", " ahu6F_hot_open_ratio_tag = AHU6F_row.loc[(AHU6F_row['control_value']==c_v_open_ratio) & \\\n", " (AHU6F_row['material_code']==m_c_hot_water)]['raw_tag'].values\n", " \n", " ahu7F_name = facilityCode.loc[(facilityCode['facility_code']==f_c_ahu6_7F)]['facility_code_name']\n", " ahu7F_cool_open_ratio_tag = AHU7F_row.loc[(AHU7F_row['control_value']==c_v_open_ratio) & \\\n", " (AHU7F_row['material_code']==m_c_cool_water)]['raw_tag'].values\n", " ahu7F_hot_open_ratio_tag = AHU7F_row.loc[(AHU7F_row['control_value']==c_v_open_ratio) & \\\n", " (AHU7F_row['material_code']==m_c_hot_water)]['raw_tag'].values\n", " \n", " ahu8F_name = facilityCode.loc[(facilityCode['facility_code']==f_c_ahu6_8F)]['facility_code_name']\n", " ahu8F_cool_open_ratio_tag = AHU8F_row.loc[(AHU8F_row['control_value']==c_v_open_ratio) & \\\n", " (AHU8F_row['material_code']==m_c_cool_water)]['raw_tag'].values\n", " ahu8F_hot_open_ratio_tag = AHU8F_row.loc[(AHU8F_row['control_value']==c_v_open_ratio) & \\\n", " (AHU8F_row['material_code']==m_c_hot_water)]['raw_tag'].values\n", " \n", " ahu9F_name = facilityCode.loc[(facilityCode['facility_code']==f_c_ahu6_9F)]['facility_code_name']\n", " ahu9F_cool_open_ratio_tag = AHU9F_row.loc[(AHU9F_row['control_value']==c_v_open_ratio) & \\\n", " (AHU9F_row['material_code']==m_c_cool_water)]['raw_tag'].values\n", " ahu9F_hot_open_ratio_tag = AHU9F_row.loc[(AHU9F_row['control_value']==c_v_open_ratio) & \\\n", " (AHU9F_row['material_code']==m_c_hot_water)]['raw_tag'].values\n", " \n", "\n", " cool_valve_tag_list = [ahu4_cool_open_ratio_tag, ahu3F_cool_open_ratio_tag, ahu4F_cool_open_ratio_tag, \\\n", " ahu5F_cool_open_ratio_tag, ahu6F_cool_open_ratio_tag, ahu7F_cool_open_ratio_tag, \\\n", " ahu8F_cool_open_ratio_tag, ahu9F_cool_open_ratio_tag]\n", " hot_valve_tag_list = [ahu4_hot_open_ratio_tag, ahu3F_hot_open_ratio_tag, ahu4F_hot_open_ratio_tag, \\\n", " ahu5F_hot_open_ratio_tag, ahu6F_hot_open_ratio_tag, ahu7F_hot_open_ratio_tag, \\\n", " ahu8F_hot_open_ratio_tag, ahu9F_hot_open_ratio_tag]\n", " if target_month >= 6 or target_month <= 9:\n", " cooling = True\n", " elif target_month <= 3 or target_month == 12:\n", " cooling = False\n", " else:\n", " for list_i in range(len(cool_valve_tag_list)):\n", "\n", " if sum(processed_data[cool_valve_tag_list[list_i]].values.flatten()) > 0:\n", " cooling = True\n", " break\n", " else:\n", " cooling = False\n", " \n", " for list_i in range(len(cool_valve_tag_list)):\n", "\n", " cool_valve_ = processed_data[cool_valve_tag_list[list_i]].values.flatten()\n", " hot_valve_ = processed_data[hot_valve_tag_list[list_i]].values.flatten()\n", " \n", " anormal_event = 0\n", " for i in range(len(cool_valve_)):\n", " \n", " if cooling == False and (cool_valve_[i] > 0 and hot_valve_[i] > 0):\n", " anormal_event = 1\n", " detect_code = 4 # simultaneous opening\n", " description = \"The heating and cooling valves were simultaneously opened in heating period.\"\n", " elif cooling == True and hot_valve_[i] > 0:\n", " anormal_event = 0\n", " detect_code = 1 # simultaneous opening\n", " description = \"Both valves are used in cooling period.\"\n", " else:\n", " anormal_event = 0\n", " detect_code = 1 # normal\n", " description = \"Data is not wrong.\"\n", "\n", " time = datetime.datetime.strptime(processed_data['time'][i], '%Y/%m/%d %H:%M:%S')\n", " time = str(time).replace('/', '-')\n", " facility_type_id = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(cool_valve_tag_list[list_i])[0]]['facility_type_id'].values[0]\n", " facility_code = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(cool_valve_tag_list[list_i])[0]]['facility_code'].values[0]\n", "\n", " material_code_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(cool_valve_tag_list[list_i])[0]]['material_code'].values[0]\n", " control_value_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(cool_valve_tag_list[list_i])[0]]['control_value'].values[0]\n", " point_counts_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(cool_valve_tag_list[list_i])[0]]['point_count'].values[0]\n", " data = [time, facility_type_id, facility_code, anormal_event, detect_code, description, \\\n", " material_code_, control_value_, point_counts_, cool_valve_[i]]\n", " appended_data.append(data)\n", "\n", " material_code_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(hot_valve_tag_list[list_i])[0]]['material_code'].values[0]\n", " control_value_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(hot_valve_tag_list[list_i])[0]]['control_value'].values[0] \n", " point_counts_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(hot_valve_tag_list[list_i])[0]]['point_count'].values[0] \n", " data = [time, facility_type_id, facility_code, anormal_event, detect_code, description, \\\n", " material_code_, control_value_, point_counts_, hot_valve_[i]]\n", " appended_data.append(data)\n", "\n", " data_columns = ['time', 'facility_type_id', 'facility_code', 'anormal_event', 'detect_code', \\\n", " 'description', 'material_code', 'control_value', 'point_count', 'meausred_value']\n", " df=pd.DataFrame(data=appended_data) \n", " df.columns=data_columns\n", " \n", " \n", " ## ----------------------------------------------------------------------------------------------\n", " ## Service - physics-based anomaly detection for inlet/outlet temperature \n", "\n", " # FacilityType = 9(냉온수기)\n", " # FacilityCode = 18(냉온수기1-1)\n", " # FacilityCode = 19(냉온수기1-2)\n", " # FacilityCode = 20(냉온수기2)\n", " # ControlValue = 3(온도)\n", " CH_1_1_row = mappingTable.loc[(mappingTable['site_id'] == 1) & (mappingTable['collect_status'] == 1) \\\n", " & (mappingTable['facility_type_id'] == 9) \\\n", " & (mappingTable['facility_code'] == 18)]\n", " CH_1_2_row = mappingTable.loc[(mappingTable['site_id'] == 1) & (mappingTable['collect_status'] == 1) \\\n", " & (mappingTable['facility_type_id'] == 9) \\\n", " & (mappingTable['facility_code'] == 19)]\n", " CH_2_row = mappingTable.loc[(mappingTable['site_id'] == 1) & (mappingTable['collect_status'] == 1) \\\n", " & (mappingTable['facility_type_id'] == 9) \\\n", " & (mappingTable['facility_code'] == 20)]\n", "\n", " \n", " # control_value = 1(운전상태)\n", " # control_value = 3(온도)\n", " # MaterialCode = 6(급수)\n", " # MaterialCode = 7(환수)\n", " c_v_status = 1\n", " c_v_temperature = 3\n", " m_c_supply_water = 6\n", " m_c_return_water = 7\n", " CH_1_1_name = facilityCode.loc[(facilityCode['facility_code'] == \\\n", " CH_1_1_row['facility_code'].values[0])]['facility_code_name']\n", " CH_1_1_status_tag = CH_1_1_row.loc[(CH_1_1_row['control_value'] == \\\n", " c_v_status).values]['raw_tag'].values\n", " CH_1_1_supply_water_tag = CH_1_1_row.loc[(CH_1_1_row['material_code'] == \\\n", " m_c_supply_water).values]['raw_tag'].values\n", " CH_1_1_return_water_tag = CH_1_1_row.loc[(CH_1_1_row['material_code'] == \\\n", " m_c_return_water).values]['raw_tag'].values\n", "\n", " CH_1_2_name = facilityCode.loc[(facilityCode['facility_code'] == \\\n", " CH_1_2_row['facility_code'].values[0])]['facility_code_name']\n", " CH_1_2_status_tag = CH_1_2_row.loc[(CH_1_2_row['control_value'] == \\\n", " c_v_status).values]['raw_tag'].values\n", " CH_1_2_supply_water_tag = CH_1_2_row.loc[(CH_1_2_row['material_code'] == \\\n", " m_c_supply_water).values]['raw_tag'].values\n", " CH_1_2_return_water_tag = CH_1_2_row.loc[(CH_1_2_row['material_code'] == \\\n", " m_c_return_water).values]['raw_tag'].values\n", "\n", " CH_2_name = facilityCode.loc[(facilityCode['facility_code'] == \\\n", " CH_2_row['facility_code'].values[0])]['facility_code_name']\n", " CH_2_status_tag = CH_2_row.loc[(CH_2_row['control_value'] == \\\n", " c_v_status).values]['raw_tag'].values\n", " CH_2_supply_water_tag = CH_2_row.loc[(CH_2_row['material_code'] == \\\n", " m_c_supply_water).values]['raw_tag'].values\n", " CH_2_return_water_tag = CH_2_row.loc[(CH_2_row['material_code'] == \\\n", " m_c_return_water).values]['raw_tag'].values\n", "\n", "\n", " # FacilityType = 5(냉각탑)\n", " # FacilityCode = 21(냉각탑1-1)\n", " # FacilityCode = 22(냉각탑1-2)\n", " # ControlValue = 3(온도)\n", " CT_1_row = mappingTable.loc[(mappingTable['site_id'] == 1) & (mappingTable['collect_status'] == 1) & \\\n", " (mappingTable['facility_type_id'] == 5) & \\\n", " (mappingTable['facility_code'] == 21)]\n", " CT_2_row = mappingTable.loc[(mappingTable['site_id'] == 1) & (mappingTable['collect_status'] == 1) & \\\n", " (mappingTable['facility_type_id'] == 5) & \\\n", " (mappingTable['facility_code'] == 22)]\n", " \n", " CT_1_name = facilityCode.loc[(facilityCode['facility_code'] == \\\n", " CT_1_row['facility_code'].values[0])]['facility_code_name']\n", " CT_1_status_tag = CT_1_row.loc[(CT_1_row['control_value'] == \\\n", " c_v_status).values]['raw_tag'].values\n", " CT_1_supply_water_tag = CT_1_row.loc[(CT_1_row['material_code'] == \\\n", " m_c_supply_water).values]['raw_tag'].values\n", " CT_1_return_water_tag = CT_1_row.loc[(CT_1_row['material_code'] == \\\n", " m_c_return_water).values]['raw_tag'].values\n", "\n", " CT_2_name = facilityCode.loc[(facilityCode['facility_code'] == \\\n", " CT_2_row['facility_code'].values[0])]['facility_code_name']\n", " CT_2_status_tag = CT_2_row.loc[(CT_2_row['control_value'] == \\\n", " c_v_status).values]['raw_tag'].values\n", " CT_2_supply_water_tag = CT_2_row.loc[(CT_2_row['material_code'] == \\\n", " m_c_supply_water).values]['raw_tag'].values\n", " CT_2_return_water_tag = CT_2_row.loc[(CT_2_row['material_code'] == \\\n", " m_c_return_water).values]['raw_tag'].values\n", "\n", " \n", " name_list = [CH_1_1_name, CH_1_2_name, CH_2_name]\n", " status_tag_list = [CH_1_1_status_tag, CH_1_2_status_tag, CH_2_status_tag]\n", " supply_water_tag_list = [CH_1_1_supply_water_tag, CH_1_2_supply_water_tag, CH_2_supply_water_tag]\n", " return_water_tag_list = [CH_1_1_return_water_tag, CH_1_2_return_water_tag, CH_2_return_water_tag]\n", " \n", " for list_i in range(len(name_list)):\n", " \n", " status_ = processed_data[status_tag_list[list_i]].values.sum(axis=1)\n", " supply_water_ = processed_data[supply_water_tag_list[list_i]].values.flatten()\n", " return_water_ = processed_data[return_water_tag_list[list_i]].values.flatten()\n", "\n", " init_on_cnt = 0\n", " anormal_event = 0\n", " for i in range(len(status_)): \n", " if status_[i] == 1:\n", " init_on_cnt += 1\n", " if init_on_cnt < 5:\n", " anormal_event = 0\n", " detect_code = 1 # normal\n", " description = \"It has been less than an hour since the equipment was put into operation.\" \n", " elif cooling == False and supply_water_[i] < return_water_[i]: \n", " anormal_event = 1\n", " detect_code = 2 # supply < return temp. -> wrong\n", " description = \"supply temperature of heater is not higer than return.\"\n", " elif cooling == True and supply_water_[i] > return_water_[i]: \n", " anormal_event = 1\n", " detect_code = 2 # supply < return temp. -> wrong\n", " description = \"supply temperature of chiller is not lower than return.\"\n", " else:\n", " anormal_event = 0\n", " detect_code = 1 # normal\n", " description = \"Data is not wrong.\"\n", " else:\n", " anormal_event = 0\n", " detect_code = 1 # normal\n", " description = \"Data is not wrong.\"\n", " \n", " \n", " time = datetime.datetime.strptime(processed_data['time'][i], '%Y/%m/%d %H:%M:%S')\n", " time = str(time).replace('/', '-')\n", " facility_type_id = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(status_tag_list[list_i])[0]]['facility_type_id'].values[0]\n", " facility_code = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(status_tag_list[list_i])[0]]['facility_code'].values[0]\n", "\n", " material_code_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(status_tag_list[0])[0]]['material_code'].values[0]\n", " control_value_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(status_tag_list[0])[0]]['control_value'].values[0]\n", " point_counts_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(status_tag_list[0])[0]]['point_count'].values[0]\n", " data = [time, facility_type_id, facility_code, anormal_event, detect_code, description, \\\n", " material_code_, control_value_, point_counts_, status_[i]]\n", " appended_data.append(data)\n", "\n", " material_code_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(supply_water_tag_list[0])[0]]['material_code'].values[0]\n", " control_value_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(supply_water_tag_list[0])[0]]['control_value'].values[0] \n", " point_counts_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(supply_water_tag_list[0])[0]]['point_count'].values[0] \n", " data = [time, facility_type_id, facility_code, anormal_event, detect_code, description, \\\n", " material_code_, control_value_, point_counts_, supply_water_[i]]\n", " appended_data.append(data)\n", "\n", " material_code_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(return_water_tag_list[0])[0]]['material_code'].values[0]\n", " control_value_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(return_water_tag_list[0])[0]]['control_value'].values[0] \n", " point_counts_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(return_water_tag_list[0])[0]]['point_count'].values[0] \n", " data = [time, facility_type_id, facility_code, anormal_event, detect_code, description, \\\n", " material_code_, control_value_, point_counts_, return_water_[i]]\n", " appended_data.append(data)\n", "\n", " data_columns = ['time', 'facility_type_id', 'facility_code', 'anormal_event', 'detect_code', \\\n", " 'description', 'material_code', 'control_value', 'point_count', 'meausred_value']\n", " df=pd.DataFrame(data=appended_data) \n", " df.columns=data_columns\n", " \n", " \n", " ## ----------------------------------------------------------------------------------------------\n", " ## Service - physics-based anomaly detection for set point\n", " \n", " # control_value = 1(운전상태)\n", " # control_value = 3(온도)\n", " # control_value = 5(온도 설정값)\n", " # material_code = 4(환기)\n", " # facility_type_id = 1(공조기)\n", " # facility_type_id = 6(팬)\n", " f_t_ahu = 1\n", " f_t_fan = 6\n", " c_v_status = 1\n", " c_v_temperature = 3\n", " c_v_temperature_SP = 5\n", " m_c_RA = 4\n", " \n", " ahu4_RF_status_tag = AHU4_row.loc[(AHU4_row['facility_type_id']==f_t_fan) & \\\n", " (AHU4_row['control_value']==c_v_status) & \\\n", " (AHU4_row['material_code']==m_c_RA)]['raw_tag'].values\n", " ahu4_RA_tag = AHU4_row.loc[(AHU4_row['control_value']==c_v_temperature) & \\\n", " (AHU4_row['material_code']==m_c_RA)]['raw_tag'].values\n", " ahu4_SP_tag = AHU4_row.loc[(AHU4_row['control_value']==c_v_temperature_SP) & \\\n", " (AHU4_row['material_code']==m_c_RA)]['raw_tag'].values\n", " \n", " \n", " ahu3F_RF_status_tag = AHU3F_row.loc[(AHU3F_row['facility_type_id']==f_t_fan) & \\\n", " (AHU3F_row['control_value']==c_v_status) & \\\n", " (AHU3F_row['material_code']==m_c_RA)]['raw_tag'].values\n", " ahu3F_RA_tag = AHU3F_row.loc[(AHU3F_row['control_value']==c_v_temperature) & \\\n", " (AHU3F_row['material_code']==m_c_RA)]['raw_tag'].values\n", " ahu3F_SP_tag = AHU3F_row.loc[(AHU3F_row['control_value']==c_v_temperature_SP) & \\\n", " (AHU3F_row['material_code']==m_c_RA)]['raw_tag'].values\n", " \n", " ahu4F_RF_status_tag = AHU4F_row.loc[(AHU4F_row['facility_type_id']==f_t_fan) & \\\n", " (AHU4F_row['control_value']==c_v_status) & \\\n", " (AHU4F_row['material_code']==m_c_RA)]['raw_tag'].values\n", " ahu4F_RA_tag = AHU4F_row.loc[(AHU4F_row['control_value']==c_v_temperature) & \\\n", " (AHU4F_row['material_code']==m_c_RA)]['raw_tag'].values\n", " ahu4F_SP_tag = AHU4F_row.loc[(AHU4F_row['control_value']==c_v_temperature_SP) & \\\n", " (AHU4F_row['material_code']==m_c_RA)]['raw_tag'].values\n", " \n", " ahu5F_RF_status_tag = AHU5F_row.loc[(AHU5F_row['facility_type_id']==f_t_fan) & \\\n", " (AHU5F_row['control_value']==c_v_status) & \\\n", " (AHU5F_row['material_code']==m_c_RA)]['raw_tag'].values\n", " ahu5F_RA_tag = AHU5F_row.loc[(AHU5F_row['control_value']==c_v_temperature) & \\\n", " (AHU5F_row['material_code']==m_c_RA)]['raw_tag'].values\n", " ahu5F_SP_tag = AHU5F_row.loc[(AHU5F_row['control_value']==c_v_temperature_SP) & \\\n", " (AHU5F_row['material_code']==m_c_RA)]['raw_tag'].values\n", " \n", " ahu6F_RF_status_tag = AHU6F_row.loc[(AHU6F_row['facility_type_id']==f_t_fan) & \\\n", " (AHU6F_row['control_value']==c_v_status) & \\\n", " (AHU6F_row['material_code']==m_c_RA)]['raw_tag'].values\n", " ahu6F_RA_tag = AHU6F_row.loc[(AHU6F_row['control_value']==c_v_temperature) & \\\n", " (AHU6F_row['material_code']==m_c_RA)]['raw_tag'].values\n", " ahu6F_SP_tag = AHU6F_row.loc[(AHU6F_row['control_value']==c_v_temperature_SP) & \\\n", " (AHU6F_row['material_code']==m_c_RA)]['raw_tag'].values\n", " \n", " ahu7F_RF_status_tag = AHU7F_row.loc[(AHU7F_row['facility_type_id']==f_t_fan) & \\\n", " (AHU7F_row['control_value']==c_v_status) & \\\n", " (AHU7F_row['material_code']==m_c_RA)]['raw_tag'].values\n", " ahu7F_RA_tag = AHU7F_row.loc[(AHU7F_row['control_value']==c_v_temperature) & \\\n", " (AHU7F_row['material_code']==m_c_RA)]['raw_tag'].values\n", " ahu7F_SP_tag = AHU7F_row.loc[(AHU7F_row['control_value']==c_v_temperature_SP) & \\\n", " (AHU7F_row['material_code']==m_c_RA)]['raw_tag'].values\n", " \n", " ahu8F_RF_status_tag = AHU8F_row.loc[(AHU8F_row['facility_type_id']==f_t_fan) & \\\n", " (AHU8F_row['control_value']==c_v_status) & \\\n", " (AHU8F_row['material_code']==m_c_RA)]['raw_tag'].values\n", " ahu8F_RA_tag = AHU8F_row.loc[(AHU8F_row['control_value']==c_v_temperature) & \\\n", " (AHU8F_row['material_code']==m_c_RA)]['raw_tag'].values\n", " ahu8F_SP_tag = AHU8F_row.loc[(AHU8F_row['control_value']==c_v_temperature_SP) & \\\n", " (AHU8F_row['material_code']==m_c_RA)]['raw_tag'].values\n", " \n", " ahu9F_RF_status_tag = AHU9F_row.loc[(AHU9F_row['facility_type_id']==f_t_fan) & \\\n", " (AHU9F_row['control_value']==c_v_status) & \\\n", " (AHU9F_row['material_code']==m_c_RA)]['raw_tag'].values\n", " ahu9F_RA_tag = AHU9F_row.loc[(AHU9F_row['control_value']==c_v_temperature) & \\\n", " (AHU9F_row['material_code']==m_c_RA)]['raw_tag'].values\n", " ahu9F_SP_tag = AHU9F_row.loc[(AHU9F_row['control_value']==c_v_temperature_SP) & \\\n", " (AHU9F_row['material_code']==m_c_RA)]['raw_tag'].values\n", "\n", "\n", " \n", " RF_status_tag_list = [ahu4_RF_status_tag, ahu3F_RF_status_tag, ahu4F_RF_status_tag, ahu5F_RF_status_tag, \\\n", " ahu6F_RF_status_tag, ahu7F_RF_status_tag, ahu8F_RF_status_tag, ahu9F_RF_status_tag]\n", " RA_tag_list = [ahu4_RA_tag, ahu3F_RA_tag, ahu4F_RA_tag, ahu5F_RA_tag, ahu6F_RA_tag, \\\n", " ahu7F_RA_tag, ahu8F_RA_tag, ahu9F_RA_tag]\n", " RA_SP_tag_list = [ahu4_SP_tag, ahu3F_SP_tag, ahu4F_SP_tag, ahu5F_SP_tag, ahu6F_SP_tag, ahu7F_SP_tag, \\\n", " ahu8F_SP_tag, ahu9F_SP_tag]\n", "\n", " for list_i in range(len(RF_status_tag_list)):\n", "\n", " RF_status_ = processed_data[RF_status_tag_list[list_i]].values.flatten()\n", " RA_ = processed_data[RA_tag_list[list_i]].values.flatten()\n", " RA_SP_ = processed_data[RA_SP_tag_list[list_i]].values.flatten()\n", " init_on_cnt = 0\n", " anormal_event = 0\n", " for i in range(len(RA_)):\n", " if RF_status_[i] == 1:\n", " init_on_cnt += 1\n", " if init_on_cnt < 5:\n", " anormal_event = 0 \n", " detect_code = 1 # normal\n", " description = \"It has been less than an hour since the equipment was put into operation.\"\n", " if abs(RA_[i] - RA_SP_[i]) > 5:\n", " anormal_event = 1\n", " detect_code = 5 # Validation of set points\n", " description = \\\n", " \"Data is out of range for the setpoint during equipment operation. (tolerance: abs. 5 Celsius)\"\n", " else:\n", " anormal_event = 0\n", " detect_code = 1 # normal\n", " description = \"Data is not wrong.\"\n", " else:\n", " anormal_event = 0\n", " detect_code = 1 # normal\n", " description = \"Data is not wrong.\"\n", " \n", " \n", " time = datetime.datetime.strptime(processed_data['time'][i], '%Y/%m/%d %H:%M:%S')\n", " time = str(time).replace('/', '-')\n", " facility_code = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(RF_status_tag_list[list_i])[0]]['facility_code'].values[0]\n", "\n", " facility_type_id_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(RF_status_tag_list[list_i])[0]]['facility_type_id'].values[0]\n", " material_code_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(RF_status_tag_list[0])[0]]['material_code'].values[0]\n", " control_value_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(RF_status_tag_list[0])[0]]['control_value'].values[0]\n", " point_counts_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(RF_status_tag_list[0])[0]]['point_count'].values[0]\n", " data = [time, facility_type_id_, facility_code, anormal_event, detect_code, description, \\\n", " material_code_, control_value_, point_counts_, RF_status_[i]]\n", " appended_data.append(data)\n", "\n", " facility_type_id_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(RA_tag_list[list_i])[0]]['facility_type_id'].values[0]\n", " material_code_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(RA_tag_list[0])[0]]['material_code'].values[0]\n", " control_value_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(RA_tag_list[0])[0]]['control_value'].values[0] \n", " point_counts_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(RA_tag_list[0])[0]]['point_count'].values[0] \n", " data = [time, facility_type_id_, facility_code, anormal_event, detect_code, description, \\\n", " material_code_, control_value_, point_counts_, RA_[i]]\n", " appended_data.append(data)\n", "\n", " facility_type_id_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(RA_SP_tag_list[list_i])[0]]['facility_type_id'].values[0]\n", " material_code_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(RA_SP_tag_list[0])[0]]['material_code'].values[0]\n", " control_value_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(RA_SP_tag_list[0])[0]]['control_value'].values[0] \n", " point_counts_ = mappingTable.loc[mappingTable['raw_tag'] == \\\n", " list(RA_SP_tag_list[0])[0]]['point_count'].values[0] \n", " data = [time, facility_type_id_, facility_code, anormal_event, detect_code, description, \\\n", " material_code_, control_value_, point_counts_, RA_SP_[i]]\n", " appended_data.append(data)\n", "\n", " data_columns = ['time', 'facility_type_id', 'facility_code', 'anormal_event', 'detect_code', \\\n", " 'description', 'material_code', 'control_value', 'point_count', 'meausred_value']\n", " df=pd.DataFrame(data=appended_data) \n", " df.columns=data_columns\n", "\n", " \n", " \n", " pred_output_bucket = 'hdci-ambt-anoicos-serv'\n", "\n", " month_str = \"0\" + str(target_month) if target_month < 10 else target_month\n", " day_str = \"0\" + str(target_day) if target_day < 10 else target_day\n", "\n", " s3_db_path=\"s3://{}/dev/site_name=ipark_tower/year={}/month={}/day={}\" \\\n", " .format(pred_output_bucket, target_year, month_str, day_str) \n", "\n", " res=wr.s3.to_parquet(df=df,path=s3_db_path,dataset=True,mode='overwrite')\n", " #print(s3_db_path)\n" ] } ], "metadata": { "kernelspec": { "display_name": "conda_tensorflow2_p36", "language": "python", "name": "conda_tensorflow2_p36" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.13" } }, "nbformat": 4, "nbformat_minor": 5 }