我有几个土壤湿度传感器，记录田间不同深度的湿度和温度数据。我正在尝试将数据从CSV文件(数据记录为json msg)重新格式化为新的CSV文件。
我已经包含了我用来将数据分成不同列并删除收发器数据的代码，但我想为每个"传感器ID"创建一个新表，并按时间戳(在日期&时间格式)。
我当前的代码是:
import csv
source_filename = r"C:*source file path*mycsv1.csv"
target_filename = r"C:*output file path*mycsv2.csv"
temp1 = []
def removeSegment(temp8,temp7):
temp11 = ""
temp9 = temp8.split(",")
for temp10 in range(len(temp9)):
if (temp7 not in temp9[temp10]):
if (len(temp11)==0):
temp11 = temp9[temp10]
else:
temp11 = temp11 + "," + temp9[temp10]
return temp11
with open(source_filename) as myfile:
myreader = csv.reader(myfile)
for myrow in myreader:
if ("sensor" in myrow[1]) and ("val" not in myrow[2]):
temp1.append("sensor_id:"+myrow[1].split("/")[1]+","+myrow[2].replace('"','')[1:-1])
temp3 = dict()
for temp2 in range(len(temp1)):
temp4 = temp1[temp2].split(",")
for temp5 in range(len(temp4)):
temp6 = temp4[temp5]
if (temp6.split(":")[0] == "timestamp"):
temp3[temp6.split(":")[1]] = removeSegment(temp1[temp2],"timestamp")
mywriter = open(target_filename, 'w')
mywriter.write('timestamp,sensor_id,m1,m2,t1,t2,t3,t4n')
for temp12, temp13 in temp3.items():
mywriter.write(temp12+",")
temp15 = temp13.split(",")
for temp14 in range(len(temp15)):
mywriter.write(temp15[temp14].split(":")[1])
if temp14 == len(temp15)-1:
mywriter.write("n")
else:
mywriter.write(",")
mywriter.close()
我有一个同事帮我写代码来提取时间戳，但在转换中有一个错误，只有10位数字被转移到新的工作表，而不是13位。
此外，我正在努力将每个"传感器ID"(每个不同的传感器)的数据分组在excel上的新"表/页"上。例如，在topic列下，sensor/200301000000000000000000 = sensor 301。这将要求该传感器的所有数据在按日期排序的新表格下。时间。
文本格式的CSV数据集示例:
> receivedTS topic messageData
1.62967E+12 sensor/200301000000000000000000/501 {"m1":210,"m2":359,"t1":83,"t2":87,"t3":91,"t4":96,"timestamp":1629668261}
1.62967E+12 sensor/200300000000000000000000/501 {"m1":285,"m2":384,"t1":82,"t2":85,"t3":86,"t4":88,"timestamp":1629667612}
1.62967E+12 sensor/200294000000000000000000/501 {"m1":310,"m2":406,"t1":67,"t2":73,"t3":83,"t4":83,"timestamp":1629667609}
1.62967E+12 sensor/200297000000000000000000/501 {"m1":198,"m2":371,"t1":80,"t2":85,"t3":86,"t4":92,"timestamp":1629667604}
1.62967E+12 sensor/200303000000000000000000/501 {"m1":495,"m2":483,"t1":85,"t2":90,"t3":95,"t4":97,"timestamp":1629667600}
1.62967E+12 sensor/200302000000000000000000/501 {"m1":499,"m2":463,"t1":102,"t2":107,"t3":106,"t4":108,"timestamp":1629667596}
1.62967E+12 sensor/200296000000000000000000/501 {"m1":258,"m2":358,"t1":88,"t2":93,"t3":96,"t4":95,"timestamp":1629667592}
1.62967E+12 sensor/200298000000000000000000/501 {"m1":381,"m2":358,"t1":77,"t2":83,"t3":86,"t4":86,"timestamp":1629667584}
1.62967E+12 sensor/200187000000000000000000/501 {"m1":248,"m2":386,"t1":78,"t2":85,"t3":91,"t4":87,"timestamp":1629667580}
1.62967E+12 sensor/200295000000000000000000/501 {"m1":219,"m2":416,"t1":85,"t2":90,"t3":88,"t4":92,"timestamp":1629667576}
1.62967E+12 sensor/200301000000000000000000/501 {"m1":210,"m2":359,"t1":83,"t2":87,"t3":91,"t4":96,"timestamp":1629667573}
1.62967E+12 sensor/200300000000000000000000/501 {"m1":285,"m2":384,"t1":82,"t2":86,"t3":87,"t4":88,"timestamp":1629666929}
1.62967E+12 sensor/200294000000000000000000/501 {"m1":311,"m2":407,"t1":67,"t2":73,"t3":83,"t4":83,"timestamp":1629666925}
1.62967E+12 sensor/200297000000000000000000/501 {"m1":198,"m2":371,"t1":80,"t2":85,"t3":86,"t4":92,"timestamp":1629666920}
1.62967E+12 sensor/200303000000000000000000/501 {"m1":495,"m2":483,"t1":83,"t2":90,"t3":96,"t4":97,"timestamp":1629666916}
1.62967E+12 sensor/200302000000000000000000/501 {"m1":499,"m2":463,"t1":102,"t2":107,"t3":107,"t4":108,"timestamp":1629666912}
1.62967E+12 sensor/200296000000000000000000/501 {"m1":258,"m2":358,"t1":90,"t2":93,"t3":96,"t4":96,"timestamp":1629666908}
1.62967E+12 sensor/200299000000000000000000/501 {"m1":248,"m2":354,"t1":77,"t2":85,"t3":88,"t4":92,"timestamp":1629666904}
1.62967E+12 sensor/200298000000000000000000/501 {"m1":381,"m2":359,"t1":77,"t2":83,"t3":85,"t4":86,"timestamp":1629666900}
1.62967E+12 sensor/200187000000000000000000/501 {"m1":248,"m2":386,"t1":78,"t2":85,"t3":91,"t4":87,"timestamp":1629666895}
1.62967E+12 sensor/200295000000000000000000/501 {"m1":219,"m2":416,"t1":85,"t2":90,"t3":88,"t4":92,"timestamp":1629666891}
1.62967E+12 sensor/200301000000000000000000/501 {"m1":210,"m2":359,"t1":83,"t2":87,"t3":91,"t4":96,"timestamp":1629666889}
1.62967E+12 sensor/200300000000000000000000/501 {"m1":285,"m2":384,"t1":82,"t2":85,"t3":87,"t4":90,"timestamp":1629666244}
1.62967E+12 sensor/200294000000000000000000/501 {"m1":310,"m2":406,"t1":67,"t2":73,"t3":83,"t4":83,"timestamp":1629666240}
1.62967E+12 sensor/200297000000000000000000/501 {"m1":198,"m2":371,"t1":80,"t2":85,"t3":86,"t4":91,"timestamp":1629666236}
1.62967E+12 sensor/200303000000000000000000/501 {"m1":495,"m2":483,"t1":83,"t2":90,"t3":96,"t4":97,"timestamp":1629666233}
1.62967E+12 sensor/200302000000000000000000/501 {"m1":499,"m2":463,"t1":102,"t2":107,"t3":107,"t4":108,"timestamp":1629666228}
1.62967E+12 sensor/200296000000000000000000/501 {"m1":259,"m2":358,"t1":90,"t2":93,"t3":96,"t4":96,"timestamp":1629666224}
1.62967E+12 sensor/200299000000000000000000/501 {"m1":248,"m2":354,"t1":77,"t2":85,"t3":87,"t4":92,"timestamp":1629666219}
1.62967E+12 sensor/200298000000000000000000/501 {"m1":381,"m2":358,"t1":77,"t2":83,"t3":85,"t4":86,"timestamp":1629666215}
1.62967E+12 sensor/200187000000000000000000/501 {"m1":249,"m2":386,"t1":78,"t2":85,"t3":91,"t4":87,"timestamp":1629666210}
1.62967E+12 sensor/200295000000000000000000/501 {"m1":219,"m2":416,"t1":85,"t2":90,"t3":88,"t4":91,"timestamp":1629666207}
1.62967E+12 sensor/200301000000000000000000/501 {"m1":208,"m2":359,"t1":83,"t2":87,"t3":91,"t4":95,"timestamp":1629666204}
1.62967E+12 sensor/200300000000000000000000/501 {"m1":285,"m2":384,"t1":82,"t2":86,"t3":86,"t4":88,"timestamp":1629665560}
1.62967E+12 sensor/200294000000000000000000/501 {"m1":310,"m2":406,"t1":67,"t2":75,"t3":83,"t4":83,"timestamp":1629665553}
1.62967E+12 sensor/200297000000000000000000/501 {"m1":198,"m2":371,"t1":80,"t2":85,"t3":86,"t4":92,"timestamp":1629665549}
1.62967E+12 sensor/200303000000000000000000/501 {"m1":495,"m2":483,"t1":85,"t2":90,"t3":96,"t4":97,"timestamp":1629665546}
1.62967E+12 sensor/200302000000000000000000/501 {"m1":499,"m2":463,"t1":103,"t2":107,"t3":107,"t4":108,"timestamp":1629665542}
1.62967E+12 sensor/200296000000000000000000/501 {"m1":259,"m2":358,"t1":90,"t2":93,"t3":96,"t4":96,"timestamp":1629665536}
1.62967E+12 sensor/200299000000000000000000/501 {"m1":248,"m2":354,"t1":77,"t2":85,"t3":87,"t4":92,"timestamp":1629665532}
1.62967E+12 sensor/200298000000000000000000/501 {"m1":381,"m2":359,"t1":78,"t2":83,"t3":86,"t4":86,"timestamp":1629665527}
1.62967E+12 sensor/200187000000000000000000/501 {"m1":249,"m2":386,"t1":78,"t2":85,"t3":91,"t4":87,"timestamp":1629665524}
1.62967E+12 sensor/200295000000000000000000/501 {"m1":220,"m2":416,"t1":85,"t2":90,"t3":88,"t4":92,"timestamp":1629665519}
1.62967E+12 sensor/200301000000000000000000/501 {"m1":208,"m2":359,"t1":83,"t2":87,"t3":91,"t4":95,"timestamp":1629665516}
1.62967E+12 transceiver/004900314E46500D2033334D/data/mains-voltage {"mv":21379,"timestamp":1629664992}
1.62967E+12 transceiver/004900314E46500D2033334D/data/battery-voltage {"mv":13638,"timestamp":1629664992}
1.62966E+12 sensor/200300000000000000000000/501 {"m1":285,"m2":384,"t1":82,"t2":85,"t3":87,"t4":88,"timestamp":1629664872}
1.62966E+12 sensor/200294000000000000000000/501 {"m1":310,"m2":406,"t1":67,"t2":75,"t3":83,"t4":83,"timestamp":1629664869}
1.62966E+12 sensor/200297000000000000000000/501 {"m1":198,"m2":371,"t1":80,"t2":85,"t3":86,"t4":91,"timestamp":1629664864}
1.62966E+12 sensor/200303000000000000000000/501 {"m1":495,"m2":483,"t1":85,"t2":90,"t3":96,"t4":97,"timestamp":1629664860}
1.62966E+12 sensor/200302000000000000000000/501 {"m1":499,"m2":463,"t1":103,"t2":107,"t3":107,"t4":108,"timestamp":1629664857}
1.62966E+12 sensor/200296000000000000000000/501 {"m1":259,"m2":358,"t1":90,"t2":93,"t3":96,"t4":96,"timestamp":1629664853}
1.62966E+12 sensor/200299000000000000000000/501 {"m1":248,"m2":354,"t1":77,"t2":85,"t3":87,"t4":92,"timestamp":1629664846}
1.62966E+12 sensor/200298000000000000000000/501 {"m1":381,"m2":359,"t1":78,"t2":85,"t3":85,"t4":86,"timestamp":1629664842}
1.62966E+12 sensor/200187000000000000000000/501 {"m1":249,"m2":386,"t1":78,"t2":85,"t3":91,"t4":87,"timestamp":1629664838}
1.62966E+12 sensor/200295000000000000000000/501 {"m1":220,"m2":416,"t1":85,"t2":90,"t3":90,"t4":92,"timestamp":1629664834}
1.62966E+12 sensor/200301000000000000000000/501 {"m1":211,"m2":359,"t1":85,"t2":87,"t3":91,"t4":95,"timestamp":1629664831}
1.62966E+12 sensor/200300000000000000000000/501 {"m1":285,"m2":384,"t1":82,"t2":86,"t3":87,"t4":88,"timestamp":1629664187}
1.62966E+12 sensor/200294000000000000000000/501 {"m1":310,"m2":406,"t1":67,"t2":73,"t3":83,"t4":83,"timestamp":1629664180}
1.62966E+12 sensor/200297000000000000000000/501 {"m1":198,"m2":371,"t1":80,"t2":85,"t3":86,"t4":92,"timestamp":1629664177}
1.62966E+12 sensor/200303000000000000000000/501 {"m1":495,"m2":483,"t1":85,"t2":90,"t3":96,"t4":97,"timestamp":1629664172}
1.62966E+12 sensor/200302000000000000000000/501 {"m1":499,"m2":463,"t1":105,"t2":107,"t3":107,"t4":108,"timestamp":1629664168}
1.62966E+12 sensor/200296000000000000000000/501 {"m1":259,"m2":358,"t1":90,"t2":93,"t3":96,"t4":95,"timestamp":1629664164}
1.62966E+12 sensor/200299000000000000000000/501 {"m1":249,"m2":354,"t1":77,"t2":85,"t3":87,"t4":91,"timestamp":1629664160}
1.62966E+12 sensor/200298000000000000000000/501 {"m1":381,"m2":359,"t1":77,"t2":85,"t3":85,"t4":86,"timestamp":1629664154}
1.62966E+12 sensor/200187000000000000000000/501 {"m1":249,"m2":386,"t1":78,"t2":85,"t3":91,"t4":87,"timestamp":1629664150}
1.62966E+12 sensor/200295000000000000000000/501 {"m1":219,"m2":416,"t1":85,"t2":90,"t3":88,"t4":92,"timestamp":1629664147}
1.62966E+12 sensor/200301000000000000000000/501 {"m1":210,"m2":359,"t1":83,"t2":87,"t3":91,"t4":96,"timestamp":1629664143}
1.62966E+12 sensor/200300000000000000000000/501 {"m1":285,"m2":384,"t1":82,"t2":86,"t3":87,"t4":90,"timestamp":1629663497}
1.62966E+12 sensor/200294000000000000000000/501 {"m1":310,"m2":406,"t1":67,"t2":75,"t3":83,"t4":83,"timestamp":1629663493}
1.62966E+12 sensor/200297000000000000000000/501 {"m1":198,"m2":371,"t1":80,"t2":85,"t3":86,"t4":92,"timestamp":1629663489}
1.62966E+12 sensor/200303000000000000000000/501 {"m1":495,"m2":483,"t1":85,"t2":90,"t3":96,"t4":97,"timestamp":1629663487}
1.62966E+12 sensor/200302000000000000000000/501 {"m1":499,"m2":463,"t1":103,"t2":107,"t3":107,"t4":108,"timestamp":1629663478}
1.62966E+12 sensor/200296000000000000000000/501 {"m1":259,"m2":358,"t1":90,"t2":93,"t3":96,"t4":95,"timestamp":1629663474}
1.62966E+12 sensor/200299000000000000000000/501 {"m1":248,"m2":354,"t1":77,"t2":85,"t3":87,"t4":92,"timestamp":1629663468}
1.62966E+12 sensor/200298000000000000000000/501 {"m1":381,"m2":359,"t1":78,"t2":83,"t3":85,"t4":86,"timestamp":1629663464}
1.62966E+12 sensor/200187000000000000000000/501 {"m1":249,"m2":386,"t1":78,"t2":85,"t3":91,"t4":87,"timestamp":1629663460}
1.62966E+12 sensor/200295000000000000000000/501 {"m1":219,"m2":416,"t1":86,"t2":90,"t3":88,"t4":92,"timestamp":1629663456}
1.62966E+12 sensor/200301000000000000000000/501 {"m1":210,"m2":359,"t1":85,"t2":87,"t3":92,"t4":95,"timestamp":1629663453}
1.62966E+12 sensor/200300000000000000000000/501 {"m1":285,"m2":384,"t1":83,"t2":86,"t3":87,"t4":88,"timestamp":1629662809}
1.62966E+12 sensor/200294000000000000000000/501 {"m1":310,"m2":406,"t1":67,"t2":75,"t3":83,"t4":83,"timestamp":1629662806}
1.62966E+12 sensor/200297000000000000000000/501 {"m1":198,"m2":371,"t1":80,"t2":85,"t3":86,"t4":91,"timestamp":1629662801}
1.62966E+12 sensor/200303000000000000000000/501 {"m1":495,"m2":483,"t1":85,"t2":90,"t3":96,"t4":97,"timestamp":1629662798}
1.62966E+12 sensor/200302000000000000000000/501 {"m1":499,"m2":463,"t1":105,"t2":107,"t3":107,"t4":108,"timestamp":1629662789}
1.62966E+12 sensor/200296000000000000000000/501 {"m1":259,"m2":358,"t1":90,"t2":93,"t3":96,"t4":96,"timestamp":1629662786}
1.62966E+12 sensor/200299000000000000000000/501 {"m1":249,"m2":354,"t1":77,"t2":85,"t3":87,"t4":91,"timestamp":1629662779}
1.62966E+12 sensor/200298000000000000000000/501 {"m1":380,"m2":359,"t1":78,"t2":83,"t3":85,"t4":86,"timestamp":1629662776}
1.62966E+12 sensor/200187000000000000000000/501 {"m1":249,"m2":386,"t1":78,"t2":85,"t3":91,"t4":87,"timestamp":1629662772}
1.62966E+12 sensor/200295000000000000000000/501 {"m1":219,"m2":416,"t1":85,"t2":90,"t3":88,"t4":92,"timestamp":1629662768}
1.62966E+12 sensor/200301000000000000000000/501 {"m1":210,"m2":359,"t1":85,"t2":88,"t3":91,"t4":95,"timestamp":1629662764}
1.62966E+12 sensor/200300000000000000000000/501 {"m1":285,"m2":384,"t1":82,"t2":86,"t3":87,"t4":88,"timestamp":1629662120}
我将使用pandas:
import pandas as pd
df = pd.read_csv('<your filepath goes here>', dtype=str)
df.receivedTS = df.receivedTS.apply(lambda x: int(float(x)))
def extract_sensor_id(row):
if 'sensor' in row:
identifying_number = row.split('/')[1]
# I'm assuming that sensor ids are only three digit numbers that come after 200
# This won't work otherwise but is very resolvable if you know the naming convention
# At present the code just gets 4th, 5th, 6th digits of the number
sensor_id = identifying_number[3:6]
return sensor_id
df['sensor_id'] = df.topic.apply(extract_sensor_id)
# To print out values for sensor id 301
print(df.loc[df.sensor_id == '301'].sort_values(by='receivedTS'))