THE ELECTRONIC BAROMETER 3

In part 1 we discussed publishing real time data, in part 2 I highlighted storing data in a database and publishing them on a blog. In the last part 3 of this series I will pay attention to retrieving data from the database and visualizing the query results.

The simplest way to retrieve data from MYSQL is installing phpMyAdmin, which gives you complete control of the database, tables and queries. phpMyAdmin makes building a query and exporting it to .csv very easy. The exported .csv can be used for further analysis with for example Excel and visualizing with Google.

More interesting is to make a direct connection to the database from R, making the query, analyze and visualize directly from R. Finally, I pay attention to publishing these results online with plot.ly rest API.

For introduction to R: here is a beginners guide, and here is a free book highlighting the basics, YaRrr! The Pirate’s Guide to R.

library("DBI", lib.loc="~/R/win-library/3.3")

> library("utils", lib.loc="C:/Program Files/R/R-3.3.2/library")

> library("stats", lib.loc="C:/Program Files/R/R-3.3.2/library")

> library("RMySQL", lib.loc="~/R/win-library/3.3")

> library("plotly", lib.loc="~/R/win-library/3.3")

> library("foreign", lib.loc="~/R/win-library/3.3")

> mydb = dbConnect(MySQL(), user=xxxxx', password='xxxxxxxx', host='192.168.0.51')

> dbSendQuery(mydb, "USE wordpress")

<MySQLResult:52720008,0,0>

> dbListTables(mydb)

 [1] "temperatures"              "wp_commentmeta"          

 [3] "wp_comments"               "wp_links"                

 [5] "wp_options"                "wp_postmeta"             

 [7] "wp_posts"                  "wp_supsystic_tbl_columns"

 [9] "wp_supsystic_tbl_diagrams" "wp_supsystic_tbl_rows"   

[11] "wp_supsystic_tbl_tables"   "wp_term_relationships"   

[13] "wp_term_taxonomy"          "wp_termmeta"             

[15] "wp_terms"                  "wp_usermeta"             

[17] "wp_users"                  "wp_wpbi_ch_cols"         

[19] "wp_wpbi_charts"            "wp_wpbi_databases"       

[21] "wp_wpbi_queries"           "wp_wpbi_tables"          

[23] "wp_wpbi_tb_cols"           "wp_wpbi_vars" 

           

> dbListFields(mydb, 'temperatures')

[1] "id"           "temperature"  "humidity"     "dateMeasured" "hourMeasured"

[6] "pressure"     "timestamp"    "humidity2" 

> mydata = fetch(dbSendQuery(mydb, "SELECT id,temperature, humidity,timestamp,dateMeasured,hourmeasured pressure FROM temperatures;"))

> str(mydata)

'data.frame':  316 obs. of  6 variables:

 $ id          : int  1 2 3 4 5 6 7 8 9 10 ...

 $ temperature : num  35.3 34.9 34.9 34 33.9 ...

 $ humidity    : chr  "33.2" "33.5" "31.7" "34.3" ...

 $ timestamp   : chr  "2016-12-30 15:31:25" "2016-12-30 15:38:59" "2016-12-30 16:00:03" "2016-12-30 16:10:01" ...

 $ dateMeasured: chr  "2016-12-30" "2016-12-30" "2016-12-30" "2016-12-30" ...

 $ pressure    : int  931 938 960 970 1020 1030 1080 1090 1140 1150 ...

> head(mydata)

  id temperature humidity           timestamp dateMeasured pressure

1  1    35.29855     33.2 2016-12-30 15:31:25   2016-12-30      931

2  2    34.91549     33.5 2016-12-30 15:38:59   2016-12-30      938

3  3    34.89808     31.7 2016-12-30 16:00:03   2016-12-30      960

4  4    34.01005     34.3 2016-12-30 16:10:01   2016-12-30      970

5  5    33.87075     34.9 2016-12-30 17:00:04   2016-12-30     1020

6  6    34.06229     35.1 2016-12-30 17:10:02   2016-12-30     1030

> mydata<-transform(mydata, humidity = as.numeric(humidity))

> str(mydata)

'data.frame':  316 obs. of  6 variables:

 $ id          : int  1 2 3 4 5 6 7 8 9 10 ...

 $ temperature : num  35.3 34.9 34.9 34 33.9 ...

 $ humidity    : num  33.2 33.5 31.7 34.3 34.9 35.1 37.4 37 36.3 36.8 ...

 $ timestamp   : chr  "2016-12-30 15:31:25" "2016-12-30 15:38:59" "2016-12-30 16:00:03" "2016-12-30 16:10:01" ...

 $ dateMeasured: chr  "2016-12-30" "2016-12-30" "2016-12-30" "2016-12-30" ...

 $ pressure    : int  931 938 960 970 1020 1030 1080 1090 1140 1150 ...

> summary(mydata)

temperature       humidity              

 Min.   :24.96   Min.   :24.50           

 1st Qu.:27.93   1st Qu.:33.48   

 Median :30.45   Median :36.55   

 Mean   :30.26   Mean   :36.66                     

 3rd Qu.:31.91   3rd Qu.:39.50                     

 Max.   :37.30   Max.   :50.00   

pressure     

Min.   :   0.0  

1st Qu.: 310.0  

Median : 660.0  

Mean   : 688.3  

3rd Qu.:1080.0  

Max.   :1390.0  

p <- ggplot(mydata, aes(x=timestamp, y=temperature, group=1))

p<- p + geom_line()

p

 
Call:
        density.default(x = mydata$temperature)
 
Data: mydata$temperature (316 obs.);  Bandwidth 'bw' = 0.7483
 
       x               y            
 Min.   :22.71   Min.   :0.0000197  
 1st Qu.:26.92   1st Qu.:0.0065082  
 Median :31.13   Median :0.0559650  
 Mean   :31.13   Mean   :0.0593410  
 3rd Qu.:35.34   3rd Qu.:0.1047790  
 Max.   :39.55   Max.   :0.1464876  
dt<-density(mydata$temperature)
plot(dt)

  

write.csv(mydata, file = "mydata.csv")

Now we have the dataset in .csv.

Login to plotly and create a line graph

 

And finally, the embed code

<iframe width="900" height="800" frameborder="0" scrolling="no" src="https://plot.ly/~peterverweij/20.embed"></iframe>

And finally, the embed code