OneNET Http推流 实时数据处理 Json转储为可读csv

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

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

1
2
3
4

,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

1
2
3

{'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'}

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174

{'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'
}