Author Topic: Merging Exported data into one CSV file  (Read 47070 times)

EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #15 on: February 25, 2017, 03:19:45 PM »
Hi Felix,
Have you had the chance to try this out?  :-[

Thanks for your help!

I think I can implement a generic function to download all the CSV raw data of a node. There would be a default limit of about 100k data points.
The graph would look something like this, note the missing points and merged time index:



perky

  • Hero Member
  • *****
  • Posts: 873
  • Country: gb
Re: Merging Exported data into one CSV file
« Reply #16 on: February 25, 2017, 03:20:15 PM »
That's like a pull-up or two not being disabled and therefore sinking current. That could happen depending on the state of the signal when put to sleep.
Mark.

EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #17 on: February 25, 2017, 04:11:07 PM »
Thanks perky,
Any suggestions on how I can resolve the issue?

perky

  • Hero Member
  • *****
  • Posts: 873
  • Country: gb
Re: Merging Exported data into one CSV file
« Reply #18 on: February 25, 2017, 05:28:30 PM »
I'd put it to sleep, and while it's asleep measure the voltages on pins that you think might have a pull-up on it. If any of them are low look through the code and see if the internal pull-up is enabled (or there's an external pull-up) on that signal. A quick way might be to print out the input status and the pull-up status registers for those pins just prior to going to sleep.

Edit: BTW this can also happen when input pins are left floating. In that case it may or may not exhibit the behaviour depending on whether the input has charged up to the threshold, so look for tri-stated signals that have no pull-up/pull-down and are not being driven by any source. If it is this just putting a scope probe on signal and sometimes just touching the pins with your finger can change the current (assuming you're static discharged of course!)

Mark.
« Last Edit: February 26, 2017, 04:16:23 PM by perky »

Felix

  • Administrator
  • Hero Member
  • *****
  • Posts: 6866
  • Country: us
    • LowPowerLab
Re: Merging Exported data into one CSV file
« Reply #19 on: February 26, 2017, 03:53:37 PM »
@EnMon, attached is the patch to add node export to CSV.
Please try it and let me know how it works. Based on your feedback (and others!) I will tweak it and add it to the next release.

EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #20 on: February 27, 2017, 06:06:36 AM »
Hurray!!!!!!!!   It works 8)
Please find attached images.

I wouldn't have been able to tell easily that some data points were missing.

Thanks Felix, I really appreciate your time and effort.   :)


Felix

  • Administrator
  • Hero Member
  • *****
  • Posts: 6866
  • Country: us
    • LowPowerLab
Re: Merging Exported data into one CSV file
« Reply #21 on: February 27, 2017, 08:51:38 AM »
My pleasure, I don't really get paid for it but it's fun and hopefully you consider using my products :)

I will plan to merge all this more seamlessly into a graph on the node front page where graphs are all merged then you have the same range buttons to choose/zoom and another CSV button to download what you see in the selected graph range.

Play some more and find any quirks, I didn't extensively test it.

EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #22 on: February 27, 2017, 11:54:47 AM »
The new features will be great, upon addition.

I've been playing around with it and no issues thus far. I'll let you know if I run into any.

Quick question: I am still trying to figure out reasons for missing data points.
My assumption is that for a Weathernode, transmitting Temperature and Humidity data, the two sets of data should be transmitted at the same time since they are coming from the same sensor. Why would I receive Temp and not Humidity or Humidity and not Temp at the Gateway? Please lead me in the right direction on how to understand issues with some data points not been received. 


Felix

  • Administrator
  • Hero Member
  • *****
  • Posts: 6866
  • Country: us
    • LowPowerLab
Re: Merging Exported data into one CSV file
« Reply #23 on: February 27, 2017, 01:07:43 PM »
Often assumptions are != reality, please check your sketch to see if that is indeed the fact. If so I would fully expect the times to match, yours are not even close to each other.

EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #24 on: March 16, 2017, 10:07:46 PM »
Looks like I might have found a bug *sad*

I am still having issues with missing data points, I followed your lead as to checking my code but found no clues.

Fortunately, I checked the gateway.db file and the system log file and realized the gateway Moteino is actually receiving the data but I assume it's either not logging the data or it's not exporting it correctly to the web server.

As usual, i have attached images that reveal that the missing data point is actually existing in the gateway.db file but it just never makes it to the CSV download file.

What can I do to resolve this?  :-[



Felix

  • Administrator
  • Hero Member
  • *****
  • Posts: 6866
  • Country: us
    • LowPowerLab
Re: Merging Exported data into one CSV file
« Reply #25 on: March 17, 2017, 11:29:18 AM »
That's because the logger does not log consecutive duplicates by default, to save DB space. There is a time limit however, which can be changed if you'd like.

Here's the logger function in logUtil.js that handles that and comments to explain:

Code: [Select]
// filename:  binary file to append new data point to
// timestamp: data point timestamp (seconds since unix epoch)
// value:     data point value (signed integer)
// duplicateInterval: if provided a duplicate value is only posted after this many seconds    <<<EXPLANATION
exports.postData = function post(filename, timestamp, value, duplicateInterval)

The gateway.js app calls this here:

Code: [Select]
dbLog.postData(logfile, ts, graphValue, matchingMetric.duplicateInterval || null);

The duplicateInterval can be used in each metric to define how often a duplicate will be logged, you will see a bunch of defaults in metrics.js like this: duplicateInterval:3600
that is 1 hour in seconds.

So ... you can change the duplicateInterval in metrics.js BUT i strongly recommend you redefine the metric you want customized in your own user metrics file, that's so it doesn't get overwritten in an upgrade.
By default the logger resolution is 1 second, so to make sure duplicates are logged all the time, just do duplicateInterval:1.
FWIW The null in the post() will fallback to the post() function's default of 1, but due to the merger of definitions between metrics.js and your own user metrics .js file, the merger is inclusive which means if you don't redefine it, it will retain the value in the master metrics.js.

Perhaps duplicateInterval should be a global setting but then some folks may want this customized per metric, which complicates matters. But I hope this helps you for now.

An example user V metric with customization is already given here. Just change 3600 to 1 in here and bob's your uncle. Make sure to restart the app from the settings page.

Code: [Select]
/*this sample metric will override that which is already defined in main metrics.js*/
/*you can redefine defaults or write your own new custom metrics in 1 or more files in this folder, separate them as you'd like - they all get merged together when the app loads*/
exports.metrics = {
  V:
  {
    name: 'V',
    regexp: /\b(?:V?BAT|VOLTS|V)\:([\d\.]+)v?\b/i,
    value: '',
    duplicateInterval: 1,

EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #26 on: March 17, 2017, 12:51:35 PM »
It works!
You really should consider taking up a faculty job in the EECS dept of any school you deem fit.  ;D
What happens when the DB gets full?
Or rather how long can I have this running if I receive data every 10 minutes from over 10 transmitting motes?



Pardon me for my numerous demands on your gateway app, considering building mine from the scratch for future projects  :-[

How can I include the formula for converting the Unix Time Stamp, so that I don't have to manually do the calculations in excel?

I tried looking through the gateway.js code and I found "var ts" and console.log as shown below:


Code: [Select]
 //log data for graphing purposes, keep labels as short as possible since this log will grow indefinitely and is not compacted like the node database
            if (existingNode.metrics[matchingMetric.name].graph==1)
            {
              var graphValue = metricsDef.isNumeric(matchingMetric.logValue) ? matchingMetric.logValue : metricsDef.determineGraphValue(matchingMetric, tokenMatch); //existingNode.metrics[matchingMetric.name].value;
              if (metricsDef.isNumeric(graphValue))
              {
               [color=yellow] var ts[/color] = Math.floor(Date.now() / 1000); //get timestamp in whole seconds
                var logfile = path.join(__dirname, dbDir, dbLog.getLogName(id, matchingMetric.name));
                try {
                  [color=yellow]console.log[/color]('post: ' + logfile + '[' + ts + ','+graphValue + ']');
                  dbLog.postData(logfile, ts, graphValue, matchingMetric.duplicateInterval || null);
                } catch (err) { console.error('   POST ERROR: ' + err.message); /*console.log('   POST ERROR STACK TRACE: ' + err.stack); */ } //because this is a callback concurrent calls to the same log, milliseconds apart, can cause a file handle concurrency exception
              }
              else console.log('   METRIC NOT NUMERIC, logging skipped... (extracted value:' + graphValue + ')');
            }

Can I safely insert this formula " /(1000*60*60*24)+"1/1/1970"+(-5/24)" without breaking the code?

What would be your recommended approach to getting this done?

Thank you so much for all your help!

Felix

  • Administrator
  • Hero Member
  • *****
  • Posts: 6866
  • Country: us
    • LowPowerLab
Re: Merging Exported data into one CSV file
« Reply #27 on: March 17, 2017, 04:38:59 PM »
You really should consider taking up a faculty job in the EECS dept of any school you deem fit.  ;D
Well thanks i feel flattered  :) bu i think I enjoy low power lab too much for now and i dont like sitting in traffic 2 times a day

What happens when the DB gets full?
Or rather how long can I have this running if I receive data every 10 minutes from over 10 transmitting motes?

The "DB" has no size limit, only your SD card.
I have only a handful of regular transmitting nodes, and I had them for years. I used a 4gb card (until it failed recently and was forced to upgrade to 8gb) and i havent counted the # of logged entries, but I have a backup of that point and it was 30MB. Divide by 9bytes per entry, about 3 million entries. Still that is far from filling a 4gb. If you do end up with a "full db" it's EZ .. upgrade your SD :)

How can I include the formula for converting the Unix Time Stamp, so that I don't have to manually do the calculations in excel?

Just calculate the "excel" timestamp right in the javascript and output it to the csv, that way when you open it in excel you just need to format it to DATE.
Let me know if you need help with that, im swamped.

PS MS products == ghetto, they dont support a lousy UNIX timestamp  ::)

EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #28 on: March 17, 2017, 05:28:38 PM »
There is nothing like working from the comfort of your home.  ;D

I can only imagine the workload you have, helping lots of folks like me. (I really admire your dedication!)

For calculating the excel time-stamp and outputting it to CSV, I'm thinking of creating a new variable say
 tx = ts / (1000*60*60*24)+"1/1/1970"+(-5/24) and replacing the "ts" with "tx" in the dblog.postData code line below:

dbLog.postData(logfile, ts, graphValue, matchingMetric.duplicateInterval || null);

Is there any other edits I need to make to make this work?


EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #29 on: March 21, 2017, 06:16:06 PM »
Hi felix,
Did you by any chance take a look at this?