b2c_sensor_data.ipynb 83 KB

import awswrangler as wr
df = wr.athena.read_sql_query(
    sql="SELECT * FROM illumination where device_id = '00158d0006c9d5ed' order by timestamp desc limit 10 /*3회의실조도센서*/;",
    database="ambt_b2c"
)
df
device_id sk timestamp model id e_type battery status evt year month day hour
0 00158d0006c9d5ed 1638142531000 2021-11-29 08:35:31 a2_sen_cds_01 c5a28dfd reported 6 1 6 2021 11 29 8
1 00158d0006c9d5ed 1638138628000 2021-11-29 07:30:28 a2_sen_cds_01 339ffde9 reported 6 1 1 2021 11 29 7
2 00158d0006c9d5ed 1638134714000 2021-11-29 06:25:14 a2_sen_cds_01 2dfd69c4 reported 6 1 1 2021 11 29 6
3 00158d0006c9d5ed 1638130206000 2021-11-29 05:10:06 a2_sen_cds_01 55fcca49 reported 6 1 1 2021 11 29 5
4 00158d0006c9d5ed 1637987718000 2021-11-27 13:35:18 a2_sen_cds_01 07cd0ed4 reported 6 1 1 2021 11 27 13
5 00158d0006c9d5ed 1637970265000 2021-11-27 08:44:25 a2_sen_cds_01 0134f2c3 reported 6 1 1 2021 11 27 8
6 00158d0006c9d5ed 1637965751000 2021-11-27 07:29:11 a2_sen_cds_01 83daa627 reported 6 1 1 2021 11 27 7
7 00158d0006c9d5ed 1637939559000 2021-11-27 00:12:39 a2_sen_cds_01 4278c321 reported 6 1 1 2021 11 27 0
8 00158d0006c9d5ed 1637917262000 2021-11-26 18:01:02 a2_sen_cds_01 7a458c00 reported 6 1 1 2021 11 26 18
9 00158d0006c9d5ed 1637915754000 2021-11-26 17:35:54 a2_sen_cds_01 278d125a reported 6 1 6 2021 11 26 17
df = wr.athena.read_sql_query(
    sql="SELECT * FROM temperature_humidity where device_id = '00158d00028d93d8' order by timestamp desc limit 10 /*3회의실온습도센서*/;",
    database="ambt_b2c"
)
df
device_id sk timestamp model id e_type hum temp battery status year month day hour
0 00158d00028d93d8 1638146954000 2021-11-29 09:49:14 a2_sen_th_01 fdc93e9c reported 26 249 0 1 2021 11 29 9
1 00158d00028d93d8 1638146954000 2021-11-29 09:49:14 a2_sen_th_01 a6fa9718 reported 25 249 0 1 2021 11 29 9
2 00158d00028d93d8 1638146653000 2021-11-29 09:44:13 a2_sen_th_01 19295a0d reported 26 244 0 1 2021 11 29 9
3 00158d00028d93d8 1638146362000 2021-11-29 09:39:22 a2_sen_th_01 5fc92dc4 reported 27 240 0 1 2021 11 29 9
4 00158d00028d93d8 1638146057000 2021-11-29 09:34:17 a2_sen_th_01 6393c2bc reported 27 234 0 1 2021 11 29 9
5 00158d00028d93d8 1638146056000 2021-11-29 09:34:16 a2_sen_th_01 5f518e6c reported 28 234 0 1 2021 11 29 9
6 00158d00028d93d8 1638145756000 2021-11-29 09:29:16 a2_sen_th_01 87312dd5 reported 28 227 0 1 2021 11 29 9
7 00158d00028d93d8 1638145751000 2021-11-29 09:29:11 a2_sen_th_01 78ccec5f reported 29 227 0 1 2021 11 29 9
8 00158d00028d93d8 1638145451000 2021-11-29 09:24:11 a2_sen_th_01 2967d0b3 reported 29 219 0 1 2021 11 29 9
9 00158d00028d93d8 1638145150000 2021-11-29 09:19:10 a2_sen_th_01 7d5f4627 reported 29 211 0 1 2021 11 29 9
df = wr.athena.read_sql_query(
    sql="SELECT * FROM motion_door where device_id = '00158d0002d545b4' order by timestamp desc limit 10 /*3회의실모션센서*/;",
    database="ambt_b2c"
)
df
device_id sk timestamp model id e_type battery status evt year month day hour
0 00158d0002d545b4 1638250408000 2021-11-30 14:33:28 a2_sen_mt_01 72e2d749 reported 0 1 1 2021 11 30 14
1 00158d0002d545b4 1638248702000 2021-11-30 14:05:02 a2_sen_mt_01 7f6500ba reported 0 1 1 2021 11 30 14
2 00158d0002d545b4 1637913814000 2021-11-26 17:03:34 a2_sen_mt_01 7374a99b reported 0 1 1 2021 11 26 17
3 00158d0002d545b4 1637906976000 2021-11-26 15:09:36 a2_sen_mt_01 fc6ef1ed reported 0 1 1 2021 11 26 15
4 00158d0002d545b4 1637905314000 2021-11-26 14:41:54 a2_sen_mt_01 3fff6d7f reported 0 1 1 2021 11 26 14
5 00158d0002d545b4 1637904052000 2021-11-26 14:20:52 a2_sen_mt_01 df21c92d reported 0 1 1 2021 11 26 14
6 00158d0002d545b4 1637885207000 2021-11-26 09:06:47 a2_sen_mt_01 54b9e9b2 reported 0 1 1 2021 11 26 9
7 00158d0002d545b4 1637882804000 2021-11-26 08:26:44 a2_sen_mt_01 f156880a reported 0 1 1 2021 11 26 8
8 00158d0002d545b4 1637882445000 2021-11-26 08:20:45 a2_sen_mt_01 622e4ab9 reported 0 1 1 2021 11 26 8
9 00158d0002d545b4 1637881650000 2021-11-26 08:07:30 a2_sen_mt_01 b5c40bab reported 0 1 1 2021 11 26 8
df = wr.athena.read_sql_query(
    sql="SELECT * FROM motion_door where device_id = '00158d0005bb96f3' order by timestamp desc limit 10 /*3회의실문열림센서*/;",
    database="ambt_b2c"
)
df
device_id sk timestamp model id e_type battery status evt year month day hour
0 00158d0005bb96f3 1638250388000 2021-11-30 14:33:08 a2_sen_dor_01 5eb49bb8 reported 0 1 0 2021 11 30 14
1 00158d0005bb96f3 1638146963000 2021-11-29 09:49:23 a2_sen_dor_01 0ee044e5 reported 0 1 1 2021 11 29 9
2 00158d0005bb96f3 1638145524000 2021-11-29 09:25:24 a2_sen_dor_01 bced6030 reported 0 1 0 2021 11 29 9
3 00158d0005bb96f3 1638145260000 2021-11-29 09:21:00 a2_sen_dor_01 dcc754bc reported 0 1 1 2021 11 29 9
4 00158d0005bb96f3 1638145039000 2021-11-29 09:17:19 a2_sen_dor_01 5ee6843e reported 0 1 0 2021 11 29 9
5 00158d0005bb96f3 1638145035000 2021-11-29 09:17:15 a2_sen_dor_01 274ec676 reported 0 1 1 2021 11 29 9
6 00158d0005bb96f3 1638144390000 2021-11-29 09:06:30 a2_sen_dor_01 ffb89511 reported 0 1 0 2021 11 29 9
7 00158d0005bb96f3 1638142790000 2021-11-29 08:39:50 a2_sen_dor_01 cc1f5fdb reported 0 1 1 2021 11 29 8
8 00158d0005bb96f3 1638142526000 2021-11-29 08:35:26 a2_sen_dor_01 16e525f4 reported 0 1 0 2021 11 29 8
9 00158d0005bb96f3 1637913816000 2021-11-26 17:03:36 a2_sen_dor_01 7c29c17d reported 0 1 1 2021 11 26 17
df = wr.athena.read_sql_query(
    sql="SELECT * FROM illumination where device_id = '00158d0005a9998b' order by timestamp desc limit 10 /*연구소조도센서*/;",
    database="ambt_b2c"
)
df
device_id sk timestamp model id e_type battery status evt year month day hour
0 00158d0005a9998b 1638246272000 2021-11-30 13:24:32 a2_sen_cds_01 93f9369c reported 0 1 9 2021 11 30 13
1 00158d0005a9998b 1638244831000 2021-11-30 13:00:31 a2_sen_cds_01 601c4eea reported 0 1 10 2021 11 30 13
2 00158d0005a9998b 1638244822000 2021-11-30 13:00:22 a2_sen_cds_01 7abd863e reported 0 1 9 2021 11 30 13
3 00158d0005a9998b 1638244791000 2021-11-30 12:59:51 a2_sen_cds_01 ae4e0806 reported 0 1 4 2021 11 30 12
4 00158d0005a9998b 1638244781000 2021-11-30 12:59:41 a2_sen_cds_01 129de4ae reported 0 1 5 2021 11 30 12
5 00158d0005a9998b 1638244499000 2021-11-30 12:54:59 a2_sen_cds_01 cc8ff83e reported 0 1 4 2021 11 30 12
6 00158d0005a9998b 1638244489000 2021-11-30 12:54:49 a2_sen_cds_01 e821a0ed reported 0 1 3 2021 11 30 12
7 00158d0005a9998b 1638244479000 2021-11-30 12:54:39 a2_sen_cds_01 147ee512 reported 0 1 4 2021 11 30 12
8 00158d0005a9998b 1638244419000 2021-11-30 12:53:39 a2_sen_cds_01 b9431dfe reported 0 1 3 2021 11 30 12
9 00158d0005a9998b 1638244358000 2021-11-30 12:52:38 a2_sen_cds_01 7d9eb350 reported 0 1 4 2021 11 30 12
df = wr.athena.read_sql_query(
    sql="SELECT * FROM motion_door where device_id = '00158d0002cab225' order by timestamp desc limit 10 /*연구소모션센서*/;",
    database="ambt_b2c"
)
df
device_id sk timestamp model id e_type battery status evt year month day hour
0 00158d0002cab225 1638248019000 2021-11-30 13:53:39 a2_sen_mt_01 1040e7c6 reported 0 1 1 2021 11 30 13
1 00158d0002cab225 1638247475000 2021-11-30 13:44:35 a2_sen_mt_01 4af86acf reported 0 1 1 2021 11 30 13
2 00158d0002cab225 1638246438000 2021-11-30 13:27:18 a2_sen_mt_01 32b6aa68 reported 0 1 1 2021 11 30 13
3 00158d0002cab225 1638233537000 2021-11-30 09:52:17 a2_sen_mt_01 da53ea05 reported 0 1 1 2021 11 30 9
4 00158d0002cab225 1638231066000 2021-11-30 09:11:06 a2_sen_mt_01 472686d4 reported 0 1 1 2021 11 30 9
5 00158d0002cab225 1638230419000 2021-11-30 09:00:19 a2_sen_mt_01 9018cbfd reported 0 1 1 2021 11 30 9
6 00158d0002cab225 1638227099000 2021-11-30 08:04:59 a2_sen_mt_01 9b7710a8 reported 0 1 1 2021 11 30 8
7 00158d0002cab225 1638217727000 2021-11-30 05:28:47 a2_sen_mt_01 4805e5b3 reported 0 1 1 2021 11 30 5
8 00158d0002cab225 1638207705000 2021-11-30 02:41:45 a2_sen_mt_01 de5f7e04 reported 0 1 1 2021 11 30 2
9 00158d0002cab225 1638177874000 2021-11-29 18:24:34 a2_sen_mt_01 fa93d91d reported 0 1 1 2021 11 29 18
df = wr.athena.read_sql_query(
    sql="SELECT * FROM motion_door where device_id = '00158d0005bb968a' order by timestamp desc limit 10 /*연구소문열림센서*/;",
    database="ambt_b2c"
)
df
device_id sk timestamp model id e_type battery status evt year month day hour
0 00158d0005bb968a 1638247519000 2021-11-30 13:45:19 a2_sen_dor_01 c4f0d490 reported 0 1 0 2021 11 30 13
1 00158d0005bb968a 1638247514000 2021-11-30 13:45:14 a2_sen_dor_01 24500391 reported 0 1 1 2021 11 30 13
2 00158d0005bb968a 1638247483000 2021-11-30 13:44:43 a2_sen_dor_01 a1f435ab reported 0 1 0 2021 11 30 13
3 00158d0005bb968a 1638247479000 2021-11-30 13:44:39 a2_sen_dor_01 26db99c3 reported 0 1 1 2021 11 30 13
4 00158d0005bb968a 1638246665000 2021-11-30 13:31:05 a2_sen_dor_01 df9ef5b4 reported 0 1 0 2021 11 30 13
5 00158d0005bb968a 1638246662000 2021-11-30 13:31:02 a2_sen_dor_01 2fa18fc4 reported 0 1 1 2021 11 30 13
6 00158d0005bb968a 1638246449000 2021-11-30 13:27:29 a2_sen_dor_01 c8ad680b reported 0 1 0 2021 11 30 13
7 00158d0005bb968a 1638246447000 2021-11-30 13:27:27 a2_sen_dor_01 92f56584 reported 0 1 1 2021 11 30 13
8 00158d0005bb968a 1638246130000 2021-11-30 13:22:10 a2_sen_dor_01 ee0271a4 reported 0 1 0 2021 11 30 13
9 00158d0005bb968a 1638246126000 2021-11-30 13:22:06 a2_sen_dor_01 4aaeaed6 reported 0 1 1 2021 11 30 13
df = wr.athena.read_sql_query(
    sql="SELECT * FROM temperature_humidity where device_id = '00158d0006c9d545' order by timestamp desc limit 10 /*연구소온습도센서*/;",
    database="ambt_b2c"
)
df
device_id sk timestamp model id e_type hum temp battery status year month day hour
0 00158d00049b8ff3 1637558253000 2021-11-22 14:17:33 a2_sen_th_01 683fa131 reported 29 226 0 1 2021 11 22 14
1 00158d00049b8ff3 1637557952000 2021-11-22 14:12:32 a2_sen_th_01 2ac12530 reported 29 228 0 1 2021 11 22 14
2 00158d00049b8ff3 1637557952000 2021-11-22 14:12:32 a2_sen_th_01 42741e86 reported 30 228 0 1 2021 11 22 14
3 00158d00049b8ff3 1637557350000 2021-11-22 14:02:30 a2_sen_th_01 24c99ff6 reported 30 229 0 1 2021 11 22 14
4 00158d00049b8ff3 1637556749000 2021-11-22 13:52:29 a2_sen_th_01 413bb2a0 reported 30 228 0 1 2021 11 22 13
5 00158d00049b8ff3 1637556147000 2021-11-22 13:42:27 a2_sen_th_01 6ea69ba3 reported 29 228 0 1 2021 11 22 13
6 00158d00049b8ff3 1637555846000 2021-11-22 13:37:26 a2_sen_th_01 3f9e8043 reported 29 227 0 1 2021 11 22 13
7 00158d00049b8ff3 1637554943000 2021-11-22 13:22:23 a2_sen_th_01 bb276dda reported 29 226 0 1 2021 11 22 13
8 00158d00049b8ff3 1637554642000 2021-11-22 13:17:22 a2_sen_th_01 14445641 reported 29 225 0 1 2021 11 22 13
9 00158d00049b8ff3 1637553739000 2021-11-22 13:02:19 a2_sen_th_01 5ccc4310 reported 28 225 0 1 2021 11 22 13
import datetime
datetimeobj  = datetime.datetime.fromtimestamp(1638227325000/1000)
datetimeobj
datetime.datetime(2021, 11, 30, 8, 8, 45)
from datetime import datetime
datetime.now().hour
if datetime.now().weekday() < 5 :
    if datetime.now().hour > 8 and datetime.now().hour < 17 :
        print ('working time')        
a = datetime(datetime.now().year, datetime.now().month, datetime.now().day, datetime.now().hour)
b = datetime(int(df['year'][0]), int(df['month'][0]), int(df['day'][0]), int(df['hour'][0]))
print('{}'.format(b))
2021-11-25 13:00:00
if a!=b:
    print('not same')
not same
df.head(1)['hour'][0]
'13'
df['year'][0]
'2021'
a = datetime(datetime.now().year, datetime.now().month, datetime.now().day, datetime.now().hour)
b = datetime(int(df['year'][0]), int(df['month'][0]), int(df['day'][0]), int(df['hour'][0]))
if a!=b:
    print('same')
import awswrangler as wr
df = wr.s3.read_parquet("s3://hdci-prototyping/motion_door/", dataset=True)
df
device_id sk timestamp model id e_type battery status evt year month day hour
0 00158d0005bb968a 1633822004000 2021-10-10 08:26:44 a2_sen_dor_01 b087f0a8 reported 0 1 0 2021 10 10 8
1 00158d0002cab225 1634000451000 2021-10-12 10:00:51 a2_sen_mt_01 c6a1edc2 reported 0 1 0 2021 10 12 10
2 00158d0002cab225 1634000825000 2021-10-12 10:07:05 a2_sen_mt_01 7b442366 reported 0 1 1 2021 10 12 10
3 00158d0002cab225 1634000886000 2021-10-12 10:08:06 a2_sen_mt_01 ab3c5929 reported 0 1 0 2021 10 12 10
4 00158d0002cab225 1634003953000 2021-10-12 10:59:13 a2_sen_mt_01 0cdda406 reported 0 1 1 2021 10 12 10
... ... ... ... ... ... ... ... ... ... ... ... ... ...
8269 00158d0006c9d665 1631145728000 2021-09-09 09:02:08 a2_sen_dor_01 2a101ef0 reported 6 1 0 2021 9 9 9
8270 00158d0005bb968a 1631148819000 2021-09-09 09:53:39 a2_sen_dor_01 2838e8cc reported 0 1 1 2021 9 9 9
8271 00158d0005bb96f3 1631148863000 2021-09-09 09:54:23 a2_sen_dor_01 7af0ae63 reported 0 1 1 2021 9 9 9
8272 00158d0006c9d665 1631148869000 2021-09-09 09:54:29 a2_sen_dor_01 3d8375e2 reported 6 1 0 2021 9 9 9
8273 00158d0002d545b4 1631149065000 2021-09-09 09:57:45 a2_sen_mt_01 99fb428f reported 0 1 1 2021 9 9 9

8274 rows × 13 columns