Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
GNUPlot assistance request
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Multimedia
View previous topic :: View next topic  
Author Message
lyallp
Veteran
Veteran


Joined: 15 Jul 2004
Posts: 1555
Location: Adelaide/Australia

PostPosted: Wed May 30, 2018 9:41 am    Post subject: GNUPlot assistance request Reply with quote

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
View user's profile Send private message
theotherjoe
Guru
Guru


Joined: 22 Nov 2003
Posts: 393

PostPosted: Thu May 31, 2018 11:03 am    Post subject: Reply with quote

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
View user's profile Send private message
lyallp
Veteran
Veteran


Joined: 15 Jul 2004
Posts: 1555
Location: Adelaide/Australia

PostPosted: Thu May 31, 2018 11:13 am    Post subject: Reply with quote

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
View user's profile Send private message
theotherjoe
Guru
Guru


Joined: 22 Nov 2003
Posts: 393

PostPosted: Thu May 31, 2018 11:21 am    Post subject: Reply with quote

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
View user's profile Send private message
lyallp
Veteran
Veteran


Joined: 15 Jul 2004
Posts: 1555
Location: Adelaide/Australia

PostPosted: Thu May 31, 2018 11:39 am    Post subject: Reply with quote

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
View user's profile Send private message
theotherjoe
Guru
Guru


Joined: 22 Nov 2003
Posts: 393

PostPosted: Thu May 31, 2018 11:49 am    Post subject: Reply with quote

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
View user's profile Send private message
lyallp
Veteran
Veteran


Joined: 15 Jul 2004
Posts: 1555
Location: Adelaide/Australia

PostPosted: Thu May 31, 2018 11:53 am    Post subject: Reply with quote

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
View user's profile Send private message
theotherjoe
Guru
Guru


Joined: 22 Nov 2003
Posts: 393

PostPosted: Thu May 31, 2018 12:01 pm    Post subject: Reply with quote

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
View user's profile Send private message
lyallp
Veteran
Veteran


Joined: 15 Jul 2004
Posts: 1555
Location: Adelaide/Australia

PostPosted: Thu May 31, 2018 12:38 pm    Post subject: Reply with quote

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
View user's profile Send private message
theotherjoe
Guru
Guru


Joined: 22 Nov 2003
Posts: 393

PostPosted: Thu May 31, 2018 4:45 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Multimedia All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum