SBL test



In [8]:
import requests
import pandas
import time
import json
from influxdb import InfluxDBClient
import numpy

def download_file(url):
    local_filename = url.split('/')[-1]
    # NOTE the stream=True parameter
    r = requests.get(url, stream=True)
    with open(local_filename, 'wb') as f:
        for chunk in r.iter_content(chunk_size=1024): 
            if chunk: # filter out keep-alive new chunks
                f.write(chunk)
                f.flush()
    return local_filename
    
def LoadSiteIds(file="SiteIDs.json"):
    fp = open(file,"r")
    dic = json.load(fp)
    fp.close()
    return dic
    
def ParseSLBData(slb_id="h00t",start=time.time()-(24*60*60),stop=time.time()):
  
  starttime = time.strftime("%y%m%d%H%M",time.localtime(start))
  stoptime = time.strftime("%y%m%d%H%M",time.localtime(stop))
  url = "http://slb.nu/soldata/index.php?KEY=%s&start=%s&stop=%s" %(slb_id,starttime,stoptime)

  df = pandas.read_csv(url,sep = ";",parse_dates=[[0, 1]],skiprows=8, header = None ,infer_datetime_format = True,na_values = ["     ","    ","  "," ",""])
  cl = pandas.read_csv(url,sep = ";", header = 6,error_bad_lines= False,na_values = [""],nrows=1)

  #Align keys to data and rename time col. 
  cols = cl.keys()
  cols = cols[2:]
  col2 = cols.insert(0,"Time")
  col2 = col2.insert(-1,"NAN")

  #Remove SLB station id from key.
  NewCols = []

  for datakey in col2:
    NewCols.append(datakey.strip(slb_id))
  
  #Set data keys as column descriptors
  df.columns = NewCols
  
  #Delete trailing columns with junk. 
  #for key in df.keys()[-5:df.shape[1]-1]:
  #    if key.find(slb_id) == -1:
  #        del df[key]

  #Reformat timestamps
  for i in range(0,df.shape[0]):
    try:
      timestamp = time.mktime(time.strptime(df["Time"][i],"%y-%m-%d %H:%M"))
      df["Time"][i] = timestamp
    except:
      df = df.drop(df.index[i])
      
  return df
  



def ParseCVS(file):
  file = open(file,"r")
  
  df = pandas.read_csv(file)


  print df
  print df['Line']

class InfluxFeedLTSInterface(InfluxDBClient):
  def __init__(self,config_file="influx.json"):

    #Load database credentials
    fp = open(config_file,"r")
    self.config = json.load(fp)
    fp.close()
    
    #Connect
    InfluxDBClient.__init__(self,self.config["host"], self.config["port"], self.config["user"], self.config["password"], self.config["database"])

  def GetLastTimeStamp(self,FluxId):

    result = self.query('select time from \"%s\" order desc limit 1;' % FluxId, time_precision='m')

    try:
      return float(result[0]["points"][0][0])/1000.0
    except:
      return 0.0

  def SendToInfluxDB(self,df,FeedId):
    #Series name
    #series = FeedId + "/raw_data" 
    
    rows = 0

    #Save each row
    for i in range(0,Data.shape[0]):
      timestamp = Data.irow(i)[0]
      column = ["time"]
      data = [int(timestamp*1000)]
      
      
      #Iterate each value and remove NANs
      for j in range(1,Data.shape[1]):
        if numpy.isnan(Data.iloc[i,j]):
          continue
          
        #Add key
        column.append(Data.keys()[j])
        data.append(Data.iloc[i,j])

      #If there where only nan on this row continue to next row. 
      if len(column) == 1:
        continue
          
      fdata = [{
          "points": [data],
          "name": FeedId,
          "columns": column
          }]

      self.write_points_with_precision(fdata,"m")
      
      rows += 1
        
    return rows

In [26]:
if True:

  #ParseCVS("testdata/h00t_1310160720_1406030410.csv")
  SiteIDs = LoadSiteIds()

  Feeds = InfluxFeedLTSInterface()

  #Get all data until now + 1h
  StopTime = time.time() + 3600

  for Site in SiteIDs:
      FeedId = "%s/raw_data" % SiteIDs[Site]
      StartTime = Feeds.GetLastTimeStamp(FeedId)
      
      if StartTime == 0:
        print "No previous records in: " + FeedId
        print "Starting from Okt 2013"
        StartTime = time.mktime(time.strptime("2013-10-01","%Y-%m-%d"))
      else:
        print "Last record in stream: " + FeedId
        print "at: " + time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(StartTime))
      
      #Start a tiny bit after the last value.
      Current = StartTime + 0.5
      
      
      PeriodLen = 60*60*24*7
      
      while Current < StopTime:
          print "Reading SLB data from: " + Site 
          print "From: " + time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(Current))
          print "To:   " + time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(Current + PeriodLen))
          Data = ParseSLBData(Site,Current,Current + PeriodLen)

          #Remove duplicate
          if Data["Time"][0] == StartTime:
              Data = Data.drop(Data.index[0])
          
          Current += PeriodLen
          print "Sending data to influx as: " + FeedId

          r = Feeds.SendToInfluxDB(Data,FeedId)
          print "%i Rows written" % r


Last record in stream: 4a39b124-f594-11e3-a510-f23c9173ce4a/raw_data
at: 2014-06-17 04:00:00
Reading SLB data from: b02t
From: 2014-06-17 04:00:00
To:   2014-06-24 04:00:00
Sending data to influx as: 4a39b124-f594-11e3-a510-f23c9173ce4a/raw_data
226 Rows written
Last record in stream: 709e47a1-ca88-4c22-890f-2407e9cb131a/raw_data
at: 2014-06-17 04:00:00
Reading SLB data from: f06t
From: 2014-06-17 04:00:00
To:   2014-06-24 04:00:00
Sending data to influx as: 709e47a1-ca88-4c22-890f-2407e9cb131a/raw_data
120 Rows written
Last record in stream: e63710a4-78d9-4071-9a2e-ad35534355f4/raw_data
at: 2014-06-17 04:00:00
Reading SLB data from: b03t
From: 2014-06-17 04:00:00
To:   2014-06-24 04:00:00
Sending data to influx as: e63710a4-78d9-4071-9a2e-ad35534355f4/raw_data
114 Rows written
Last record in stream: 32383bde-7648-4abe-9dac-44701eabd72f/raw_data
at: 2014-06-17 04:00:00
Reading SLB data from: f04t
From: 2014-06-17 04:00:00
To:   2014-06-24 04:00:00
Sending data to influx as: 32383bde-7648-4abe-9dac-44701eabd72f/raw_data
116 Rows written
Last record in stream: 46d55815-f927-459f-a8e2-8bbcd88008ee/raw_data
at: 2014-06-17 04:00:00
Reading SLB data from: h00t
From: 2014-06-17 04:00:00
To:   2014-06-24 04:00:00
Sending data to influx as: 46d55815-f927-459f-a8e2-8bbcd88008ee/raw_data
221 Rows written
Last record in stream: f1136e33-d227-4fbd-ae72-d3488d70ba89/raw_data
at: 2014-06-17 04:00:00
Reading SLB data from: b01t
From: 2014-06-17 04:00:00
To:   2014-06-24 04:00:00
Sending data to influx as: f1136e33-d227-4fbd-ae72-d3488d70ba89/raw_data
116 Rows written
Last record in stream: 2a31fb24-347b-4924-ab89-5c434771a2ae/raw_data
at: 2014-06-17 04:00:00
Reading SLB data from: b06t
From: 2014-06-17 04:00:00
To:   2014-06-24 04:00:00
Sending data to influx as: 2a31fb24-347b-4924-ab89-5c434771a2ae/raw_data
114 Rows written
Last record in stream: 4cf6c743-8d0b-45f0-aa72-8a0c10315cf5/raw_data
at: 2014-06-17 04:00:00
Reading SLB data from: f09t
From: 2014-06-17 04:00:00
To:   2014-06-24 04:00:00
Sending data to influx as: 4cf6c743-8d0b-45f0-aa72-8a0c10315cf5/raw_data
120 Rows written
Last record in stream: 6f075b69-b823-4e6b-8021-dd751cc79eea/raw_data
at: 2014-06-17 04:00:00
Reading SLB data from: b04t
From: 2014-06-17 04:00:00
To:   2014-06-24 04:00:00
Sending data to influx as: 6f075b69-b823-4e6b-8021-dd751cc79eea/raw_data
115 Rows written
Last record in stream: b0dd71a8-efe8-41eb-bab1-633ebaaa778c/raw_data
at: 2014-06-17 04:00:00
Reading SLB data from: f07t
From: 2014-06-17 04:00:00
To:   2014-06-24 04:00:00
Sending data to influx as: b0dd71a8-efe8-41eb-bab1-633ebaaa778c/raw_data
121 Rows written
Last record in stream: 802afb51-c5eb-4230-9b22-9a77ef7260b3/raw_data
at: 2014-06-17 04:00:00
Reading SLB data from: b05t
From: 2014-06-17 04:00:00
To:   2014-06-24 04:00:00
Sending data to influx as: 802afb51-c5eb-4230-9b22-9a77ef7260b3/raw_data
114 Rows written
Last record in stream: face0d13-b22d-48fe-bec1-d29732604133/raw_data
at: 2014-06-17 04:00:00
Reading SLB data from: f05t
From: 2014-06-17 04:00:00
To:   2014-06-24 04:00:00
Sending data to influx as: face0d13-b22d-48fe-bec1-d29732604133/raw_data
220 Rows written
Last record in stream: e8a13982-f651-11e3-a510-f23c9173ce4a/raw_data
at: 2014-06-17 04:00:00
Reading SLB data from: f11t
From: 2014-06-17 04:00:00
To:   2014-06-24 04:00:00
Sending data to influx as: e8a13982-f651-11e3-a510-f23c9173ce4a/raw_data
223 Rows written
Last record in stream: c6261166-f651-11e3-a510-f23c9173ce4a/raw_data
at: 2014-06-17 04:00:00
Reading SLB data from: f10t
From: 2014-06-17 04:00:00
To:   2014-06-24 04:00:00
Sending data to influx as: c6261166-f651-11e3-a510-f23c9173ce4a/raw_data
226 Rows written

In [25]:
ls


CVS Importer.ipynb  downloads/  grafana-1.5.4.tar.gz  influxDB.ipynb             influx.json       MQTT-Stage/     SiteIDs.json  Universe.ipynb   Untitled1.ipynb
CVS parser.ipynb    git/        ImportMetaData.ipynb  influxdb_latest_amd64.deb  influx.json.save  SBL test.ipynb  tmp/          Untitled0.ipynb

In [9]:
Current = time.time() - PeriodLen
Data = ParseSLBData(Site,Current,Current + PeriodLen)

In [10]:
Data


Out[10]:
Time M0Pac001 M0Pac002 MGLOB002 MGLOB003 MGLOc002 MGLOc003 MPOWc001 MPOWc002 MTmpM002 MTmpM003 NAN Unnamed: 12
0 1.401713e+09 0 0 NaN 487 NaN 3896 0 0 NaN 32 NaN NaN
1 1.401714e+09 0 0 NaN 704 NaN 3982 0 0 NaN 38 NaN NaN
2 1.401715e+09 0 0 NaN 844 NaN 4082 0 0 NaN 41 NaN NaN
3 1.401715e+09 0 0 NaN 497 NaN 4184 0 0 NaN 45 NaN NaN
4 1.401716e+09 0 0 NaN 587 NaN 4290 0 0 NaN 44 NaN NaN
5 1.401716e+09 0 0 NaN 767 NaN 4400 0 0 NaN 43 NaN NaN
6 1.401717e+09 0 0 NaN 545 NaN 4503 0 0 NaN 46 NaN NaN
7 1.401718e+09 0 0 NaN 647 NaN 4566 0 0 NaN 36 NaN NaN
8 1.401718e+09 0 0 NaN 701 NaN 4674 0 0 NaN 39 NaN NaN
9 1.401719e+09 0 0 NaN 636 NaN 4777 0 0 NaN 42 NaN NaN
10 1.401719e+09 0 0 NaN 631 NaN 4880 0 0 NaN 38 NaN NaN
11 1.40172e+09 0 0 NaN 598 NaN 4977 0 0 NaN 37 NaN NaN
12 1.401721e+09 0 0 NaN 572 NaN 5069 0 0 NaN 36 NaN NaN
13 1.401721e+09 0 0 NaN 544 NaN 5157 0 0 NaN 38 NaN NaN
14 1.401722e+09 0 0 NaN 518 NaN 5241 0 0 NaN 36 NaN NaN
15 1.401722e+09 0 0 NaN 490 NaN 5321 0 0 NaN 34 NaN NaN
16 1.401723e+09 0 0 NaN 463 NaN 5397 0 0 NaN 33 NaN NaN
17 1.401724e+09 0 0 NaN 436 NaN 5468 0 0 NaN 33 NaN NaN
18 1.401724e+09 0 0 NaN 408 NaN 5535 0 0 NaN 33 NaN NaN
19 1.401725e+09 0 0 NaN 380 NaN 5598 0 0 NaN 32 NaN NaN
20 1.401725e+09 0 0 NaN 351 NaN 5656 0 0 NaN 32 NaN NaN
21 1.401726e+09 0 0 NaN 323 NaN 5710 0 0 NaN 31 NaN NaN
22 1.401727e+09 0 0 NaN 295 NaN 5759 0 0 NaN 30 NaN NaN
23 1.401727e+09 0 0 NaN 266 NaN 5804 0 0 NaN 29 NaN NaN
24 1.401728e+09 0 0 NaN 238 NaN 5844 0 0 NaN 28 NaN NaN
25 1.401728e+09 0 0 NaN 212 NaN 5880 0 0 NaN 27 NaN NaN
26 1.401729e+09 0 0 NaN 185 NaN 5912 0 0 NaN 26 NaN NaN
27 1.40173e+09 0 0 NaN 159 NaN 5940 0 0 NaN 25 NaN NaN
28 1.40173e+09 0 0 NaN 133 NaN 5963 0 0 NaN 24 NaN NaN
29 1.401731e+09 0 0 NaN 109 NaN 5982 0 0 NaN 23 NaN NaN
30 1.401731e+09 0 0 NaN 87 NaN 5998 0 0 NaN 22 NaN NaN
31 1.401732e+09 0 0 NaN 67 NaN 6010 0 0 NaN 22 NaN NaN
32 1.401733e+09 0 0 NaN 21 NaN 6014 0 0 NaN 19 NaN NaN
33 1.401733e+09 0 0 NaN 19 NaN 6017 0 0 NaN 18 NaN NaN
34 1.401734e+09 0 0 NaN 17 NaN 6020 0 0 NaN 17 NaN NaN
35 1.401734e+09 0 0 NaN 14 NaN 6023 0 0 NaN 17 NaN NaN
36 1.401735e+09 0 0 NaN 12 NaN 6025 0 0 NaN 16 NaN NaN
37 1.401736e+09 0 0 NaN 10 NaN 6027 0 0 NaN 16 NaN NaN
38 1.401736e+09 0 0 NaN 7 NaN 6028 0 0 NaN 15 NaN NaN
39 1.401737e+09 0 0 NaN 5 NaN 6029 0 0 NaN 15 NaN NaN
40 1.401737e+09 0 0 NaN 3 NaN 6030 0 0 NaN 14 NaN NaN
41 1.401738e+09 0 0 NaN 1 NaN 6030 0 0 NaN 14 NaN NaN
42 1.401739e+09 0 0 NaN 0 NaN 6030 0 0 NaN 14 NaN NaN
43 1.401739e+09 0 0 NaN 0 NaN 6030 0 0 NaN 13 NaN NaN
44 1.40174e+09 0 0 NaN 0 NaN 6030 0 0 NaN 13 NaN NaN
45 1.40174e+09 0 0 NaN 0 NaN NaN NaN NaN NaN 0 NaN NaN
46 1.401741e+09 0 0 NaN 0 NaN NaN NaN NaN NaN 0 NaN NaN
47 1.401742e+09 0 0 NaN 0 NaN NaN NaN NaN NaN 0 NaN NaN
48 1.401742e+09 0 0 NaN 0 NaN NaN NaN NaN NaN 0 NaN NaN
49 1.401743e+09 0 0 NaN 0 NaN NaN NaN NaN NaN 0 NaN NaN
50 1.401743e+09 0 0 NaN 0 NaN NaN NaN NaN NaN 0 NaN NaN
51 1.401744e+09 0 0 NaN 0 NaN NaN NaN NaN NaN 0 NaN NaN
52 1.401745e+09 0 0 NaN 0 NaN NaN NaN NaN NaN 0 NaN NaN
53 1.401745e+09 0 0 NaN 0 NaN NaN NaN NaN NaN 0 NaN NaN
54 1.401746e+09 0 0 NaN 0 NaN NaN NaN NaN NaN 0 NaN NaN
55 1.401746e+09 0 0 NaN 0 NaN NaN NaN NaN NaN 0 NaN NaN
56 1.401747e+09 0 0 NaN 0 NaN NaN NaN NaN NaN 0 NaN NaN
57 1.401748e+09 0 0 NaN 0 NaN NaN NaN NaN NaN 0 NaN NaN
58 1.401748e+09 0 0 NaN 0 NaN NaN NaN NaN NaN 0 NaN NaN
59 1.401749e+09 0 0 NaN 0 NaN NaN NaN NaN NaN 0 NaN NaN
... ... ... ... ... ... ... ... ... ... ... ... ...

1009 rows × 13 columns


In [ ]: