| View previous topic :: View next topic |
| Author |
Message |
lyallp
Veteran
Joined: 15 Jul 2004
Posts: 1599
Location: Adelaide/Australia
|
 Posted: Wed May 30, 2018 9:41 am Post subject: GNUPlot assistance request |
 |
|
I have a SQLite3 database which contains data similar to the following
| Code: |
time_index Circuit dateTimeRecorded activepower
404503 GPOs 2018-05-30 00:03:50 224.57
404503 Grid 2018-05-30 00:03:50 4.34
404503 Shed 2018-05-30 00:03:50 24.86
404504 GPOs 2018-05-30 00:08:51 223.89
404504 Grid 2018-05-30 00:08:51 5.69
404504 Shed 2018-05-30 00:08:51 25.25
404505 GPOs 2018-05-30 00:13:52 247.23
404505 Grid 2018-05-30 00:13:52 3.81
404505 Shed 2018-05-30 00:13:52 24.43
404506 GPOs 2018-05-30 00:18:52 223.19
404506 Grid 2018-05-30 00:18:52 4.58
404506 Shed 2018-05-30 00:18:52 25.11
404507 GPOs 2018-05-30 00:23:52 222.57
404507 Grid 2018-05-30 00:23:52 4.01
404507 Shed 2018-05-30 00:23:52 24.86
|
I usually graph my data using R and rstudio, problem is, I recently threw qt4 out of my system and rstudio, when built with the portage system, uses the qt5 system, and as a consequence, core dumps.
(VirtualBox also does not like qt5)
As such, I can no longer generate my pretty graphs.
I tried using ODBC to map the SQLite DB into libreoffice BASE and then use 'Calc' to do the graphs but I hit a snag with SQL incompatability between SQLite and BASE so I gave up on that.
Do we have any gnuplot experts available?
How do I go around showing the time plot of the 3 Circuits? The number of different circuits could vary (I may add or remove circuits).
If the data was horizontal (as in each record had a GPOs, Grid and Shed value), I think it would be easier.
Any assistance would be greatly appreciated.
_________________
...Lyall |
|
| Back to top |
|
 |
theotherjoe
Guru
Joined: 22 Nov 2003
Posts: 393
|
| Posted: Thu May 31, 2018 11:03 am Post subject: |
|
|
Hi Lyall,
it has been a whle since I played with gnuplot.
I tinkered for a while with your data example and you are perfectly
right, if you want to use gnuplot you probably would need to
rearrange your data. well, I did it by hand:
| Code: |
$ cat circuits.dat
#time_index Circuit dateTimeRecorded activepower
404503 GPOs 2018-05-30 00:03:50 224.57
404504 GPOs 2018-05-30 00:08:51 223.89
404505 GPOs 2018-05-30 00:13:52 247.23
404506 GPOs 2018-05-30 00:18:52 223.19
404507 GPOs 2018-05-30 00:23:52 222.57
404503 Grid 2018-05-30 00:03:50 4.34
404504 Grid 2018-05-30 00:08:51 5.69
404505 Grid 2018-05-30 00:13:52 3.81
404506 Grid 2018-05-30 00:18:52 4.58
404507 Grid 2018-05-30 00:23:52 4.01
404503 Shed 2018-05-30 00:03:50 24.86
404504 Shed 2018-05-30 00:08:51 25.25
404505 Shed 2018-05-30 00:13:52 24.43
404506 Shed 2018-05-30 00:18:52 25.11
404507 Shed 2018-05-30 00:23:52 24.86
|
that way I tried these examples:
| Code: |
$ gnuplot
G N U P L O T
Version 5.2 patchlevel 2 (Gentoo revision r0) last modified 2017-11-15
Copyright (C) 1986-1993, 1998, 2004, 2007-2017
Thomas Williams, Colin Kelley and many others
gnuplot home: http://www.gnuplot.info
faq, bugs, etc: type "help FAQ"
immediate help: type "help" (plot window: hit 'h')
Terminal type is now 'qt'
gnuplot> plot 'circuits.dat' index 0 using 1:5 w lp, '' index 1 using 1:5 w lp, '' index 2 using 1:5 w lp
...
gnuplot> plot 'circuits.dat' index 1 using 1:5 w lp
|
looking at my /usr/share/gnuplot and /usr/share/doc/gnuplot dirs
I cannot find any examples or handbook besides the built-in help system.
emerge tells me I didnt use the doc USE flag  |
|
| Back to top |
|
|
lyallp
Veteran
Joined: 15 Jul 2004
Posts: 1599
Location: Adelaide/Australia
|
| Posted: Thu May 31, 2018 11:13 am Post subject: |
|
|
Thanks for the response.
I simply changed my SQL order by to get the data in the order you showed, but ended up with the same graph, using
| Code: | set xdata time
set timefmt "%Y-%m-%d %H:%M:%S"
show xrange
set format x "%H:00"
plot '< sqlite3 /tmp/usb_database.db ".read PlotElectricityUsage.sql"' \
using 3:5 \
with lines \
title "Electricity Usage"
|
Where the SQL query is as follows | Code: | .header on
.separator " "
SELECT "DATA"."time_index",
Case
When EUI64 = '000D6F0005A5BE9D' then 'Grid'
When EUI64 = '000D6F0005A5D77E' then 'GPOs'
When EUI64 = '000D6F0005A5BCAE' then 'Shed'
Else '????'
End as "Circuit",
datetime("DATA"."TimeStamp", 'unixepoch', 'localtime') as "dateTimeRecorded",
"DATA"."activepower"
FROM "DATA"
WHERE "DATA"."devicetype" = 15
AND "DATA"."voltage" > 0
AND "dateTimeRecorded" BETWEEN date('now', 'start of day')
AND date('now', 'start of day', '+1 day')
AND "dateTimeRecorded" > Datetime('2018-03-09 13:00:00')
ORDER BY "Circuit",
"DATA"."time_index" ASC
;
|
_________________
...Lyall |
|
| Back to top |
|
|
theotherjoe
Guru
Joined: 22 Nov 2003
Posts: 393
|
| Posted: Thu May 31, 2018 11:21 am Post subject: |
|
|
havent looked at your reponse, yet.
all the docs and demos can be found at
http://gnuplot.sourceforge.net, but you knew that already  |
|
| Back to top |
|
|
lyallp
Veteran
Joined: 15 Jul 2004
Posts: 1599
Location: Adelaide/Australia
|
| Posted: Thu May 31, 2018 11:39 am Post subject: |
|
|
gnuplot handles time strings, hence the xdata time and timefmt set statements. It converts it internally to seconds since epoch.
I know gnuplot does not handle time zones, according to the manual, we are expected to sort that out ourselves before plotting.
The date and time are separated by a space and gnuplot appears to treat each as a column, hence my example using column 3 and 5, when, logically, there are only 4 columns.
You can see that I set the X axis format to be the nearest 'hour' using '%H:00'
What I had hoped was there was some way I could say to gnuplot, plot a separate line for each distinct value of 'Circuit', maybe even using the value of Circuit to drive the colour of the line.
_________________
...Lyall |
|
| Back to top |
|
|
theotherjoe
Guru
Joined: 22 Nov 2003
Posts: 393
|
| Posted: Thu May 31, 2018 11:49 am Post subject: |
|
|
I tried your plot sequence on the dataset I generate by hand.
change x format by: set format x "%M:00"
and it looks as if the timing position is OK. |
|
| Back to top |
|
|
lyallp
Veteran
Joined: 15 Jul 2004
Posts: 1599
Location: Adelaide/Australia
|
| Posted: Thu May 31, 2018 11:53 am Post subject: |
|
|
Setting the time format to %M may appear to work in the limited dataset you have but %H is the way to go when you have more data that includes additional hours
_________________
...Lyall |
|
| Back to top |
|
|
theotherjoe
Guru
Joined: 22 Nov 2003
Posts: 393
|
| Posted: Thu May 31, 2018 12:01 pm Post subject: |
|
|
The different datasets are picked up via the index keyword as
shown in my example.
and regarding the xdata I wanted to check if the plotting makes sense
which wasnt possible on the hours format.
edit: wonder if set format x "%H:%M" helps anything
edit2: http://gnuplot.sourceforge.net/demo/timedat.html |
|
| Back to top |
|
|
lyallp
Veteran
Joined: 15 Jul 2004
Posts: 1599
Location: Adelaide/Australia
|
| Posted: Thu May 31, 2018 12:38 pm Post subject: |
|
|
Ah, index doesn't work in my case, I am querying a database, there are no blank rows in the data.
_________________
...Lyall |
|
| Back to top |
|
|
theotherjoe
Guru
Joined: 22 Nov 2003
Posts: 393
|
| Posted: Thu May 31, 2018 4:45 pm Post subject: |
|
|
Lyall,
you may want to look at Octave instead of R.
Octave has a sqlite module and offers plotting facilities.
Have not looked in detail though. |
|
| Back to top |
|
|
|
Multimedia
