<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/analysing-digital-water-meter-data-using-the-tidyverse/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://r.prevos.net/digital-water-meter-data/"> The Devil is in the Data</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>In <a rel="nofollow" href="https://r.prevos.net/simulating-water-consumption/" target="_blank">last week’s article</a>, I discussed how to simulate water consumption data to help develop analytics and reporting. This post describes how to create a diurnal curve from standard digital metering data.</p>
<h2>Data Source</h2>
<p>The simulated data consists  of three fields:</p>
<ul>
<li><em>DevEUI</em>: Unique identifier of the digital water meter.</li>
<li><em>Timestamp</em> in <a rel="nofollow" href="https://en.wikipedia.org/wiki/Coordinated_Universal_Time" target="_blank">Coordinated Universal Time</a></li>
<li>Cumulative <em>count</em> of pulses.</li>
</ul>
<p>All analysis is undertaken in the local Australian Eastern Standard Time (AEST). The input to all functions is thus in AEST. The digital water meters send an hourly pulse at a random time within the hour. Each transmitter (RTU) uses a random offset to avoid network congestion. The digital meter counts each time the impeller makes a full turn, and for this analysis, we assume that this equates to a five-litre volume. The ratio between volume and count depends on the meter brand and type. The image below shows a typical data set for an RTU, including some missing data points.</p>
<div class="wp-caption"><a rel="nofollow" href="https://r.prevos.net/simulating-water-consumption/consumption/" target="_blank"><img data-orig-file="https://i1.wp.com/r.prevos.net/wp-content/uploads/sites/11/2018/01/consumption.png?fit=2521%2C912&amp;ssl=1" data-orig-size="2521,912" data-medium-file="https://i1.wp.com/r.prevos.net/wp-content/uploads/sites/11/2018/01/consumption.png?fit=300%2C109&amp;ssl=1" data-attachment-id="1117" data-image-meta="{" data-image-description="" src="https://i1.wp.com/r.prevos.net/wp-content/uploads/sites/11/2018/01/consumption.png?resize=584%2C211&ssl=1" data-permalink="https://r.prevos.net/simulating-water-consumption/consumption/" data-large-file="https://i1.wp.com/r.prevos.net/wp-content/uploads/sites/11/2018/01/consumption.png?fit=584%2C211&amp;ssl=1" alt="Simulated water consumption." data-image-title="consumption" sizes="(max-width: 584px) 100vw, 584px" width="450" data-comments-opened="1" class="wp-image-1117" data-recalc-dims="1"></a>
<p class="wp-caption-text">Simulated water consumption (red: measured points, blue: interpolated points.</p>
</div>
<p>To analyse the data we need two auxiliary functions: one to slice the data we need and one to interpolate data for the times we need it. The <a rel="nofollow" href="https://www.tidyverse.org/" target="_blank">Tidyverse</a> heavily influences the code in this article. I like the Tidyverse way of doing things because it leads to elegant code that is easy to understand.</p>
<pre>
library(tidyverse)
library(lubridate)
library(magrittr)
meter_reads &lt;- read.csv(&quot;Hydroinformatics/DigitalMetering/meter_reads.csv&quot;)
rtu &lt;- unique(meter_reads$DevEUI)
meter_reads$TimeStampUTC &lt;- as.POSIXct(meter_reads$TimeStampUTC, tz = &quot;UTC&quot;)
</pre>
<h2>Slicing Digital Water Metering Data</h2>
<p>Data analysis is undertaken on slices of the complete data set. This function slices the available data by a vector of RTU ids and a timestamp range in AEST. This function adds a new timestamp variable in AEST. If no date range is provided, all available data for the selected RTUs is provided. The output of this function is a data frame (a Tibble in Tydiverse language).</p>
<pre>
slice_reads &lt;- function(rtus, dates = range(meter_reads$TimeStampUTC)) { filter(meter_reads, DevEUI %in% rtus) %>%
    mutate(TimeStampAEST = as.POSIXct(format(TimeStampUTC, tz = &quot;Australia/Melbourne&quot;))) %>%
    filter(TimeStampAEST >= as.POSIXct(dates[1]) &amp; 
             TimeStampAEST &lt;= as.POSIXct(dates[2])) %>%
    arrange(DevEUI, TimeStampAEST)
}
</pre>
<h2>Interpolation of Meter Reads</h2>
<p>This function interpolates the cumulative counts for a series of RTUs over a vector of timestamps in AEST. The function creates a list to store the results for each RTU, interpolates the data using the <a rel="nofollow" href="https://stat.ethz.ch/R-manual/R-devel/library/stats/html/approxfun.html" target="_blank">approx function</a> and then flattens the list back to a data frame. The interpolation function contains a different type of pipe because of the approx for interpolation function does not take a data argument. The %$% pipe from the Magrittr<a rel="nofollow" href="https://cran.r-project.org/web/packages/magrittr/README.html" target="_blank"> package</a> solves that problem.</p>
<p>The output is a data frame with DevEUI, the timestamp in AEST and the interpolated cumulative count. The image above shows the counts for two meters over two days an the graph superimposes an interpolated point over the raw data. Although the actual data consists of integer counts, interpolated values are numeric values. The decimals are retained to distinguish them from real reads.</p>
<pre>
interpolate_count &lt;- function(rtus, timestamps) {
  timestamps &lt;- as.POSIXct(timestamps, tz = &quot;Australia/Melbourne&quot;)
  results &lt;- vector(&quot;list&quot;, length(rtus))
  for (r in seq_along(rtus)) {
    interp &lt;- slice_reads(rtus[r]) %$%
      approx(TimeStampAEST, Count, timestamps)
    results[[r]] &lt;- data_frame(DevEUI = rep(rtus[r], length(timestamps)), TimeStampAEST = timestamps, Count = interp$y) } return(do.call(rbind, results)) } interpolate_count(rtu[2:3], seq.POSIXt(as.POSIXct(&quot;2020-02-01&quot;), as.POSIXct(&quot;2020-02-2&quot;), by = &quot;day&quot;)) slice_reads(rtu[2], c(&quot;2020-02-06&quot;, &quot;2020-02-08&quot;)) %>%
  ggplot(aes(x = TimeStampAEST, y = Count))  + 
  geom_line(col = &quot;grey&quot;, size = 1) + 
  geom_point(col = &quot;red&quot;) + 
  geom_point(data = interpolate_count(rtu[2], as.POSIXct(&quot;2020-02-06&quot;) + (0:2)*24*3600), colour = &quot;blue&quot;) + 
  ggtitle(paste(&quot;DevEUI&quot;, rtu[2]))
</pre>
<p>With these two auxiliary functions, we can start analysing the data.</p>
<h2>Daily Consumption</h2>
<p>Daily consumption for each connection is a critical metric in managing water resources and billing customers. The daily consumption of any water connection is defined by the difference between the cumulative counts at midnight. The interpolation function makes it easy to determine daily consumption. This function interpolates the midnight reads for each of the RTUs over the period, starting the previous day. The output of the function is a data frame that can be piped into the plotting function to visualise the data. When you group the data by date, you can also determine the total consumption over a group of services.</p>
<pre>
daily_consumption &lt;- function(rtus, dates) {
  timestamps &lt;- seq.POSIXt(as.POSIXct(min(dates)) - 24 * 3600, as.POSIXct(max(dates)), by = &quot;day&quot;) interpolate_count(rtus, timestamps) %>%
    group_by(DevEUI) %>%
    mutate(Consumption = c(0, diff(Count)) * 5,
           Date = format(TimeStampAEST, &quot;%F&quot;)) %>%
    filter(TimeStampAEST != timestamps[1]) %>%
    select(DevEUI, Date, Consumption)
}

daily_consumption(rtu[32:33], c(&quot;2020-02-01&quot;, &quot;2020-02-7&quot;)) %>%
  ggplot(aes(x = Date, y = Consumption)) + geom_col() + 
  facet_wrap(~DevEUI) + 
  theme(axis.text.x = element_text(angle = 90, hjust = 1))
</pre>
<div class="wp-caption"><a rel="nofollow" href="https://r.prevos.net/digital-water-meter-data/daily/" target="_blank"><img data-orig-file="https://i2.wp.com/r.prevos.net/wp-content/uploads/sites/11/2018/02/daily.png?fit=2006%2C1290&amp;ssl=1" data-orig-size="2006,1290" data-medium-file="https://i2.wp.com/r.prevos.net/wp-content/uploads/sites/11/2018/02/daily.png?fit=300%2C193&amp;ssl=1" data-attachment-id="1153" data-image-meta="{" data-image-description="" src="https://i2.wp.com/r.prevos.net/wp-content/uploads/sites/11/2018/02/daily.png?resize=584%2C376&ssl=1" data-permalink="https://r.prevos.net/digital-water-meter-data/daily/" data-large-file="https://i2.wp.com/r.prevos.net/wp-content/uploads/sites/11/2018/02/daily.png?fit=584%2C376&amp;ssl=1" alt="Analysing digital water meter data: Daily consumption." data-image-title="daily" sizes="(max-width: 584px) 100vw, 584px" width="450" data-comments-opened="1" class="wp-image-1153" data-recalc-dims="1"></a>
<p class="wp-caption-text">Analysing digital water meter data: Daily consumption.</p>
</div>
<h2>Diurnal Curves</h2>
<p>The diurnal curve is one of the most important pieces of information used in the design of water supply systems. This curve shows the usage of one or more services for each hour in the day. This curve is a reflection of human behaviour, as we use most water in the morning and the evenings.</p>
<p>This function slices data for a vector of RTUs over a period and then plots the average diurnal curve. The data is obtained by interpolating the cumulative counts for each whole hour in the period. The function then calculates the flow in litres per hour and visualises the minimum, mean and maximum value.</p>
<pre>
plot_diurnal_connections &lt;- function(rtus, dates) {
  timestamps &lt;- seq.POSIXt(as.POSIXct(dates[1]), as.POSIXct(dates[2]), by = &quot;hour&quot;) interpolate_count(rtus, timestamps) %>% 
    mutate(Rate = c(0, diff(Count * 5)),
           Hour = as.integer(format(TimeStampAEST, &quot;%H&quot;))) %>% 
    filter(Rate >= 0) %>%
    group_by(Hour) %>%
    summarise(min = min(Rate), mean = mean(Rate), max = max(Rate)) %>%
    ggplot(aes(x = Hour, ymin = min, ymax = max)) + 
    geom_ribbon(fill = &quot;lightblue&quot;, alpha = 0.5) + 
    geom_line(aes(x = Hour, y = mean), col = &quot;orange&quot;, size = 1) +
    ggtitle(&quot;Connections Diurnal flow&quot;) + ylab(&quot;Flow rate [L/h]&quot;)
}

plot_diurnal_connections(rtu[12:20], c(&quot;2020-02-01&quot;, &quot;2020-03-01&quot;))
</pre>
<div class="wp-caption"><a rel="nofollow" href="https://r.prevos.net/digital-water-meter-data/diurnal_line/" target="_blank"><img data-orig-file="https://i0.wp.com/r.prevos.net/wp-content/uploads/sites/11/2018/02/diurnal_line.png?fit=2006%2C1290&amp;ssl=1" data-orig-size="2006,1290" data-medium-file="https://i0.wp.com/r.prevos.net/wp-content/uploads/sites/11/2018/02/diurnal_line.png?fit=300%2C193&amp;ssl=1" data-attachment-id="1151" data-image-meta="{" data-image-description="&lt;p&gt;Analysing digital meter data: Diurnal Curves&lt;/p&gt;
" src="https://i0.wp.com/r.prevos.net/wp-content/uploads/sites/11/2018/02/diurnal_line.png?resize=584%2C376&ssl=1" data-permalink="https://r.prevos.net/digital-water-meter-data/diurnal_line/" data-large-file="https://i0.wp.com/r.prevos.net/wp-content/uploads/sites/11/2018/02/diurnal_line.png?fit=584%2C376&amp;ssl=1" alt="Analysing digital water meter data: Diurnal curve." data-image-title="diurnal_line" sizes="(max-width: 584px) 100vw, 584px" width="450" data-comments-opened="1" class="wp-image-1151" data-recalc-dims="1"></a>
<p class="wp-caption-text">Analysing digital water meter data: Diurnal curve.</p>
</div>
<p>Boxplots are also an informative way to visualise this curve. This method provides more statistical information on one page, and the ggplot function does all the statistical analysis.</p>
<pre>
plot_diurnal_box &lt;- function(rtus, dates) {
  timestamps &lt;- seq.POSIXt(as.POSIXct(dates[1]), as.POSIXct(dates[2]), by = &quot;hour&quot;) interpolate_count(rtus, timestamps) %>% 
    mutate(Rate = c(0, diff(Count * 5)),
           Hour = as.integer(format(TimeStampAEST, &quot;%H&quot;))) %>% 
    filter(Rate >= 0) %>%
    group_by(Hour) %>%
    ggplot(aes(x = factor(Hour), y = Rate)) + 
    geom_boxplot() + 
    ggtitle(&quot;Diurnal flow&quot;) + ylab(&quot;Flow rate [L/h]&quot;) + xlab(&quot;Time&quot;)
}

plot_diurnal_box(rtu[12:20], c(&quot;2020-02-01&quot;, &quot;2020-03-01&quot;))
</pre>
<div class="wp-caption"><a rel="nofollow" href="https://r.prevos.net/digital-water-meter-data/diurnal_box/" target="_blank"><img data-orig-file="https://i0.wp.com/r.prevos.net/wp-content/uploads/sites/11/2018/02/diurnal_box.png?fit=2006%2C1290&amp;ssl=1" data-orig-size="2006,1290" data-medium-file="https://i0.wp.com/r.prevos.net/wp-content/uploads/sites/11/2018/02/diurnal_box.png?fit=300%2C193&amp;ssl=1" data-attachment-id="1152" data-image-meta="{" data-image-description="&lt;p&gt;Analysing digital water meter data: Diurnal curve.&lt;/p&gt;
" src="https://i0.wp.com/r.prevos.net/wp-content/uploads/sites/11/2018/02/diurnal_box.png?resize=584%2C376&ssl=1" data-permalink="https://r.prevos.net/digital-water-meter-data/diurnal_box/" data-large-file="https://i0.wp.com/r.prevos.net/wp-content/uploads/sites/11/2018/02/diurnal_box.png?fit=584%2C376&amp;ssl=1" alt="Analysing digital water meter data: Diurnal curve." data-image-title="diurnal_box" sizes="(max-width: 584px) 100vw, 584px" width="450" data-comments-opened="1" class="wp-image-1152" data-recalc-dims="1"></a>
<p class="wp-caption-text">Analysing digital water meter data: Diurnal curve.</p>
</div>
<h2>Further Analysing Digital Water Metering Data</h2>
<p>These are only glimpses into what is possible with this type of data. Further algorithms need to be developed to extract additional value from this data. I am working on developing leak detection algorithms and clustering diurnal curves, daily consumption graphs and so on. Any data science enthusiast who is interested in helping me to develop an Open Source R library to analyse digital metering data.</p>
<p>The code for this article is available on <a rel="nofollow" href="https://github.com/pprevos/r.prevos.net/tree/master/Hydroinformatics/DigitalMetering" target="_blank">GitHub</a>.</p>
<p> </p>
<p>The post <a rel="nofollow" href="https://r.prevos.net/digital-water-meter-data/" target="_blank">Analysing Digital Water Meter Data using the Tidyverse</a> appeared first on <a rel="nofollow" href="https://r.prevos.net/" target="_blank">The Devil is in the Data</a>.</p>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://r.prevos.net/digital-water-meter-data/"> The Devil is in the Data</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/WHj5oI07jsQ" height="1"></a></div>