# OneNET Http推流 实时数据处理 Json转储为可读csv ## 概述 OneNET Http推流的数据是一种json格式数据，我使用了Django接收这部分数据后，转存至onenet-http-log.txt。包含单个设备的实时数据。一行一个http 的 body。可视为 json 进行处理。文章末尾提供了原始数据。使用python进行处理，生成csv文件。 性能测试结果：1W条原始数据(txt) 700k左右，csv 600k 左右，生成 csv 时间大约 50s 。 ## 主程序 ``` python import json from os import times import tablib import demjson import datetime import time from pandas import Series import pandas as pd fileHandler = open ("onenet-http-log.txt", "r") # 注意！此程序按照一个设备编写，未考虑txt内多设备的情况，可在后台接收时进行分类并写入不同文件 listOfLines = fileHandler.readlines() fileHandler.close() # 每行1个json文件 dataall = [] starttimeset = 1 # 可设置开始结束时间，注意13位时间戳 endtimeset = 9999999999999 # 13位 # maxtime = 0 # 用于文件名，如果此程序指定开始结束时间，则可以直接使用输入的时间点作为文件名 # mintime = 9999999999999 for line in listOfLines: jsondict = {} # 1行json化为1个字典 colname = [] # 列名，实际上基本固定，本程序每解析1行json都会更新一次colname，最后记得加上时间 rowvalues = [] # 1行的数据，最后记得加上时间 rows = demjson.decode(line.strip()) # 将1行str格式的json取出，由于json不规范，使用demjson rows = demjson.decode(rows['msg']) deviceName = rows['deviceName'] # 取出设备名，同理可取出其他基本信息 rows = rows['data'] rows = rows['params'] # 类型字典，结构：{'Atmosphere': {'value': 67, 'time': 1628743259567}, 'CO2Value': {'value': 65, 'time': 1628743259567}} for line2 in rows: # 解析上一行所说结构 dictintrows = rows[line2] name = line2 # str value = dictintrows['value'] # int bool double timestamp = dictintrows['time'] # int if( timestamp < starttimeset ): # 在此循环中，可以对时间范围进行限制 continue if( timestamp > endtimeset ): continue jsondict[name] = value colname.append(name) rowvalues.append(value) # maxtime = max(timestamp,maxtime) # 取最大时间作为文件名 # mintime = min(timestamp,mintime) # 取最小时间作为文件名 timestamp = int(timestamp/1000) # 将13位时间戳处理成10位 timeArray = time.localtime(timestamp) timerreadable = time.strftime("%Y-%m-%d %H:%M:%S",timeArray) # 注意时间格式 jsondict['time'] = timerreadable # 将时间戳化为人类可读 colname.append('time') # 增加时间栏 if( timestamp < starttimeset ): continue if( timestamp > endtimeset ): continue rowvalues.append(timerreadable) # 如果想使用int时间戳，可传入timestamp dataall.append(rowvalues) # 为整个表增加一行数据 df = pd.DataFrame(dataall,columns=colname) fromtime = time.localtime(starttimeset/1000) totime = time.localtime(endtimeset/1000) csvname = deviceName+"_"+time.strftime("%Y%m%d_%H%M%S",fromtime)+"_"+time.strftime("%Y%m%d_%H%M%S",totime)+".csv" # 格式不正确会报错，文件正在读写也会报错，如果太多错误，可在后面加上当前时间戳62进制化后的字符串 print(csvname) df.to_csv(csvname) print(df) # 结果 # Atmosphere CO2Value CON1 CON2 CON3 CurrentTemperature LightLux PM10 PM1D0 PM2D5 RelativeHumidity time # 0 67 65 False False 0 54 65 213 32 43 54 2021-08-12 12:40:46 # 1 67 65 False False 0 54 65 213 32 43 54 2021-08-12 12:40:47 # 2 67 65 False False 0 54 65 213 32 43 54 2021-08-12 12:40:59 ``` ### CSV文件 dev0_19700101_080000_19700101_080000.csv csv文件可以使用excel等文件进行查看。 ``` ,Atmosphere,CO2Value,CON1,CON2,CON3,CurrentTemperature,LightLux,PM10,PM1D0,PM2D5,RelativeHumidity,time 0,67,65,False,False,0,54,65,213,32,43,54,2021-08-12 12:40:46 1,67,65,False,False,0,54,65,213,32,43,54,2021-08-12 12:40:47 2,67,65,False,False,0,54,65,213,32,43,54,2021-08-12 12:40:59 ``` {{< image src="https://cdn.ftls.xyz/images/2021/12/20210819105527.png" caption="" >}} ## onenet-http-log.txt原始数据 ``` json {'msg': '{"projectId":"abcdef","productId":"abcdefghij","deviceName":"dev0","messageType":"notify","notifyType":"property","data":{"id":"1628743246605","version":"1.0","params":{"Atmosphere":{"value":67,"time":1628743246684},"CO2Value":{"value":65,"time":1628743246684},"CON1":{"value":false,"time":1628743246685},"CON2":{"value":false,"time":1628743246685},"CON3":{"value":0,"time":1628743246685},"CurrentTemperature":{"value":54,"time":1628743246685},"LightLux":{"value":65,"time":1628743246685},"PM10":{"value":213,"time":1628743246685},"PM1D0":{"value":32,"time":1628743246685},"PM2D5":{"value":43,"time":1628743246685},"RelativeHumidity":{"value":54,"time":1628743246685}}}}', 'signature': 'xxxxx==', 'time': 1628743246697, 'id': 'xxxxx', 'nonce': 'xxxxx'} {'msg': '{"projectId":"abcdef","productId":"abcdefghij","deviceName":"dev0","messageType":"notify","notifyType":"property","data":{"id":"1628743247811","version":"1.0","params":{"Atmosphere":{"value":67,"time":1628743247887},"CO2Value":{"value":65,"time":1628743247887},"CON1":{"value":false,"time":1628743247887},"CON2":{"value":false,"time":1628743247887},"CON3":{"value":0,"time":1628743247887},"CurrentTemperature":{"value":54,"time":1628743247887},"LightLux":{"value":65,"time":1628743247887},"PM10":{"value":213,"time":1628743247887},"PM1D0":{"value":32,"time":1628743247887},"PM2D5":{"value":43,"time":1628743247887},"RelativeHumidity":{"value":54,"time":1628743247887}}}}', 'signature': 'xxxxx==', 'time': 1628743247900, 'id': 'xxxxx', 'nonce': 'xxxxx'} {'msg': '{"projectId":"abcdef","productId":"abcdefghij","deviceName":"dev0","messageType":"notify","notifyType":"property","data":{"id":"1628743259488","version":"1.0","params":{"Atmosphere":{"value":67,"time":1628743259567},"CO2Value":{"value":65,"time":1628743259567},"CON1":{"value":false,"time":1628743259567},"CON2":{"value":false,"time":1628743259567},"CON3":{"value":0,"time":1628743259567},"CurrentTemperature":{"value":54,"time":1628743259567},"LightLux":{"value":65,"time":1628743259567},"PM10":{"value":213,"time":1628743259567},"PM1D0":{"value":32,"time":1628743259567},"PM2D5":{"value":43,"time":1628743259567},"RelativeHumidity":{"value":54,"time":1628743259567}}}}', 'signature': 'xxxxx==', 'time': 1628743259580, 'id': 'xxxxx', 'nonce': 'xxxxx'} ``` ### 格式化后的json，便于观看 ``` json {'msg': '{ "projectId": "abcdef", "productId": "abcdefghij", "deviceName": "dev0", "messageType": "notify", "notifyType": "property", "data": { "id": "1628743246605", "version": "1.0", "params": { "Atmosphere": { "value": 67, "time": 1628743246684 }, "CO2Value": { "value": 65, "time": 1628743246684 }, "CON1": { "value": false, "time": 1628743246685 }, "CON2": { "value": false, "time": 1628743246685 }, "CON3": { "value": 0, "time": 1628743246685 }, "CurrentTemperature": { "value": 54, "time": 1628743246685 }, "LightLux": { "value": 65, "time": 1628743246685 }, "PM10": { "value": 213, "time": 1628743246685 }, "PM1D0": { "value": 32, "time": 1628743246685 }, "PM2D5": { "value": 43, "time": 1628743246685 }, "RelativeHumidity": { "value": 54, "time": 1628743246685 } } } }', 'signature': 'xxxxx==', 'time': 1628743246697, 'id': 'xxxxx', 'nonce': 'xxxxx' } {'msg': '{ "projectId": "abcdef", "productId": "abcdefghij", "deviceName": "dev0", "messageType": "notify", "notifyType": "property", "data": { "id": "1628743247811", "version": "1.0", "params": { "Atmosphere": { "value": 67, "time": 1628743247887 }, "CO2Value": { "value": 65, "time": 1628743247887 }, "CON1": { "value": false, "time": 1628743247887 }, "CON2": { "value": false, "time": 1628743247887 }, "CON3": { "value": 0, "time": 1628743247887 }, "CurrentTemperature": { "value": 54, "time": 1628743247887 }, "LightLux": { "value": 65, "time": 1628743247887 }, "PM10": { "value": 213, "time": 1628743247887 }, "PM1D0": { "value": 32, "time": 1628743247887 }, "PM2D5": { "value": 43, "time": 1628743247887 }, "RelativeHumidity": { "value": 54, "time": 1628743247887 } } } }', 'signature': 'xxxxx==', 'time': 1628743247900, 'id': 'xxxxx', 'nonce': 'xxxxx' } {'msg': '{ "projectId": "abcdef", "productId": "abcdefghij", "deviceName": "dev0", "messageType": "notify", "notifyType": "property", "data": { "id": "1628743259488", "version": "1.0", "params": { "Atmosphere": { "value": 67, "time": 1628743259567 }, "CO2Value": { "value": 65, "time": 1628743259567 }, "CON1": { "value": false, "time": 1628743259567 }, "CON2": { "value": false, "time": 1628743259567 }, "CON3": { "value": 0, "time": 1628743259567 }, "CurrentTemperature": { "value": 54, "time": 1628743259567 }, "LightLux": { "value": 65, "time": 1628743259567 }, "PM10": { "value": 213, "time": 1628743259567 }, "PM1D0": { "value": 32, "time": 1628743259567 }, "PM2D5": { "value": 43, "time": 1628743259567 }, "RelativeHumidity": { "value": 54, "time": 1628743259567 } } } }', 'signature': 'xxxxx==', 'time': 1628743259580, 'id': 'xxxxx', 'nonce': 'xxxxx' } ```