• クラウドにずっと保管されている保証はない。(無料のサービスであるがゆえ、いつ終了するかは分からない)
• 2個以上のセンサーのデータをAmbientに蓄積すると、得られるCSVが見にくくなる。
• 時系列以外の観点で解析することはできない。

データの流れ

• Windows10
• Anaconda Navigator v1.9.6
• Spyder v3.3.2
• Python v3.7.1
• xlsxwriter v1.1.2

                        
import xlsxwriter

 YEAR = 2019    #希望の西暦を入力
 MONTH = 2      #希望の月を入力

 #面倒なので毎月1日から31日までを指定
 startDay = 1
 endDay = 31

 #Ambientの1チャンネルに含まれるデータ系列数 
 Number_of_Data = 6   

 #作成するExcelブックを指定
 workbook = xlsxwriter.Workbook(str(YEAR) + "." + str(MONTH) + ".xlsx")
 
 #読み込むCSVファイル名を指定
 NameOfFile = "data9999.csv"

 for i in range(startDay, endDay + 1):
     speSheetName = str(YEAR) + "." + str(MONTH) + "." + str(i)
     worksheet = workbook.add_worksheet(speSheetName)

# セル座標の初期化
 crow = 0
 ccol = 0 
 prow = 0
 pcol = 5
 arow = 0
 acol = 10

 with open(NameOfFile, mode = "r", encoding="utf-8") as f:
     for line in f:
         buf = line.rstrip().split(",")
         if not "created" in buf[0]:

             #時間の文字列を分解しタイムスタンプを作成
             #フォーマットは、yyyy-mm-ddThh:mm.ss.fffZ(小文字がデータの数字)
             sbuf = buf[0].split("T")
             ye, mo, da = sbuf[0].split("-")
             ho, mi, sec = sbuf[1].split(":")
             se, microsecond = sec.split(".")
             ye = int(ye)
             mo = int(mo)
             da = int(da)
             if str(ye)+"."+str(mo)+"."+str(da)== speSheetName:

                 tStamp = float(ho)+float(mi)/60+float(se)/3600

                 #ユニット(1)の温度・湿度データを抽出しExcelに書き込み
                 if buf[1] != "" and buf[2]!= "":
                     worksheet.write(crow,ccol,buf[0])
                     worksheet.write(crow,ccol+1,tStamp)
                     worksheet.write(crow,ccol+2,float(buf[1]))
                     worksheet.write(crow,ccol+3,float(buf[2]))
                     center_row += 1

 
                 #ユニット(2)の温度・湿度データを抽出しExcelに書き込み 
                 if buf[3] != "" and buf[4]!= "":
                     worksheet.write(prow,pcol, buf[0])
                     worksheet.write(prow,pcol+1,tStamp)
                     worksheet.write(prow,pcol+2,float(buf[3]))
                     worksheet.write(prow,pcol+3,float(buf[4]))
                     pot_row += 1

 
                 #ユニット(3)の温度・湿度データを抽出しExcelに書き込み 
                 if buf[5] != "" and buf[6]!= "":
                     worksheet.write(ac_row, ac_col, buf[0])
                     worksheet.write(ac_row, ac_col + 1, tStamp)
                     worksheet.write(arow,acol+2,float(buf[5]))
                     worksheet.write(arow,acol+3,float(buf[6]))
                     ac_row += 1


             #最初の列項目を作成
             else:
                 worksheet.write(crow,ccol,buf[0])
                 worksheet.write(crow,ccol+1,"timestamp")
                 worksheet.write(crow,ccol+2,buf[1])
                 worksheet.write(crow,ccol+3,buf[2])
                 crow += 1
                 worksheet.write(prow,pcol,buf[0])
                 worksheet.write(prow,pcol+1,"timestamp")
                 worksheet.write(prow,pcol+2,buf[3])
                 worksheet.write(prow,pcol+3,buf[4])
                 pot_row += 1
                 worksheet.write(arow,acol,buf[0])
                 worksheet.write(arow,acol+1,"timestamp")
                 worksheet.write(arow,acol+2,buf[5])
                 worksheet.write(arow,acol+3,buf[6])
                 ac_row += 1


    # 3系列の温度データを1つのグラフに描画
    chart_temp = workbook.add_chart({"type": "scatter"})
    chart_temp.set_x_axis({"name":"Time(h)","name_font":{"size":14, "bold":True},"min":0,"max":24,"major_unit":2})
    chart_temp.set_y_axis({"name":"Temperature(deg)","name_font":{"size":14, "bold":True},"min":10,"max":40,"major_unit":5})
    chart_temp.add_series({"categories":"="+speSheetName+"!$B$2:$B$480","values":"="+speSheetName+"!$C$2:$C$480",
                           "name":"Center","line":{"width":1.5, "color":"#FF0000"},"marker":{"type":"none"}})
    chart_temp.add_series({"categories":"="+speSheetName+"!$G$2:$G$480","values":"="+speSheetName+"!$H$2:$H$480",
                           "name":"Pot","line":{"width":1.5, "color":"#0000FF"},"marker":{"type":"none"}})
    chart_temp.add_series({"categories": "="+speSheetName+"!$L$2:$L$480","values":"="+speSheetName+"!$M$2:$M$480",
                           "line":{"width":1.5, "color":"#00FF00"},"marker":{"type":"none"},"name":"AirCon"})
    chart_temp.set_size({"width":600, "height": 480})
    chart_temp.set_title({"name":"Temperature Trend"})
    chart_temp.set_plotarea({
          "border":{"color":"#000000","width":1.5, "dash_type":"solid"}})
    worksheet.insert_chart("C7",chart_temp)

 
     # 3系列の湿度データを1つのグラフに描画 
    chart_humid = workbook.add_chart({"type": "scatter"})
    chart_humid.set_x_axis({"name":"Time(h)","name_font":{"size":14, "bold":True},"min":0,"max":24,"major_unit":2})
    chart_humid.set_y_axis({"name":"Humidity(%)","name_font":{"size":14, "bold":True},"min":20,"max":80,"major_unit":10})
    chart_humid.add_series({"categories":"="+speSheetName+"!$B$2:$B$480","values":"="+speSheetName+"!$D$2:$D$480",
                            "line":{"width":1.5, "color":"#FF0000"},"marker":{"type":"none"},"name":"Center"})
    chart_humid.add_series({"categories":"="+speSheetName+"!$G$2:$G$480","values":"="+speSheetName+"!$I$2:$I$480",
                            "line":{"width":1.5, "color":"#0000FF"},"marker":{"type":"none"},"name":"Pot"})
    chart_humid.add_series({"categories":"="+speSheetName+"!$L$2:$L$480","values":"="+speSheetName+"!$N$2:$N$480",
                            "line":{"width":1.5, "color":"#00FF00"},"marker":{"type":"none"},"name":"AirCon"})
    chart_humid.set_size({"width":600, "height": 480})
    chart_humid.set_title({"name":"Humidity Trend"})
    chart_humid.set_plotarea({"border":{"color":"#000000","width":1.5, "dash_type":"solid"}})
    worksheet.insert_chart("M7", chart_humid)
   

 workbook.close()
}