topic Re: Delta calculations in Discussions

Delta calculations

Prometheus — Sun, 17 Sep 2023 06:11:32 GMT

I have a dataset with hourly observations the last three years og different water meters.
The issue is that the observations shows the meter stand, and not the water usage.
I want to calculate/show a delta change for each hour, for each water meter. How do I approach that?

Some of the analysis I want to make with this dataset is to find if a meter has a negative usage at one point (indicating water running the wrong way) or that a meter has to big a delta indicating a leakage.

My workplace is starting to work on data quality, so besides the above I will also want to look at things like if timestamps are consistent or if there is change in the timestamps (if observations for a specific meter is not comming back at the same minut-number each hour).
One analysis tool I am looking forward to explore is the process quality

Re: Delta calculations

hogi — Sun, 17 Sep 2023 14:09:37 GMT

You could sort the data by meter and time and then use the lag function (lag (:column, 1) = value of the previous row) to calculate the consumption:

new table("test",add rows(100),
new column ("meter", Nominal, Set Each value (Step(row(),10*(0::11)+1,(0::11)))),
new column ("time",Format( "m/d/y h:m"), set each value(Date DMY( 17, 9, 2023 )+mod(row()-1,10)*3600)),
new column ("reading"));

new column("consumption",  set each value(if(row()==1 |:meter != lag(:meter,1),0,random uniform(0,10))));
for each row( if(row()==1 |meter != lag(:meter,1), :reading =random uniform(0,10000),: reading = lag(:reading,1)+ :consumption));


new column("consumption-calc",Formula( if(row()==1 |:meter != lag(:meter,1),0, :reading - lag(:reading,1))));

Re: Delta calculations

Prometheus — Sun, 17 Sep 2023 09:11:11 GMT

Thanks. I can try it tomorrow.
But will this be able to differentiate between each unique meter?
(I have 22 at the moment, scaling to several thousands later) .
I have the rows: MeterNumber; Datetime; CounterValue;

Exported data from the metering software is by default ordered by datetime (2023-09-17 13:09)

Re: Delta calculations

hogi — Sun, 17 Sep 2023 09:55:56 GMT

Fear not,

if(:meter != lag(:meter,1)

will make sure of that - but you have to sort the data accordingly:

With meter on the left, select the columns for meter and time and then:

Re: Delta calculations

hogi — Sun, 17 Sep 2023 11:19:18 GMT

To facilitate the task:

This is definitely a case that should be included in the :)

Re: Delta calculations

txnelson — Sun, 17 Sep 2023 13:37:58 GMT

I do not know what the input file type is, but if it is and Excel file, or a .csv file your DateTime values should be read into JMP as JMP Date/Time values. If not, you should take the time to convert them.

Below is a simple example that might give you a leg up on calculating the usage data

Names Default To Here( 1 );
dt = Current Data Table();

// Sort the data if not already sorted
dt << sort( by( :MeterNumber, :DateTime ), Replace table( 1 ) );

// Create the new Usage column
dt << New Column( "Usage",
	formula(
		//As Constant( Usage = 0 );
		If( :MeterNumber != Lag( :MeterNumber ),
			usage = 0,
			:Usage = :CounterValue - Lag( :CounterValue )
		);
	)
);

Re: Delta calculations

jthi — Sun, 17 Sep 2023 14:04:18 GMT

You can also use Col Moving Average() to get the value from previous row (can make it easier when working with group columns)

Names Default To Here(1);

// sample data
dt = New Table("Untitled",
	Add Rows(6),
	Compress File When Saved(1),
	New Column("Meter", Character, "Nominal", Set Values({"A", "A", "A", "B", "B", "B"})),
	New Column("Time", Numeric, "Continuous", Format("Best", 12), Set Values([1, 2, 3, 1, 2, 3])),
	New Column("Value", Numeric, "Continuous", Format("Best", 12), Set Values([1, 0, 2, 0, 1, 2]))
);
// Data should be sorted by time


dt << New Column("D", Numeric, Continuous, Formula(
	:Value - (Col Moving Average(:Value, 1, 1, 0, Meter) * 2 - :Value)
	// 2 * :Value - (Col Moving Average(:Value, 1, 1, 0, Meter) * 2
));

Re: Delta calculations

hogi — Sun, 17 Sep 2023 14:44:49 GMT

cool!

much more robust and compact :)

Re: Delta calculations

Prometheus — Sun, 17 Sep 2023 18:36:59 GMT

Good advice. I will start my day tomorrow spending some time with datetime formatting.

I have a bit difficult getting the syntax for your example and the previous one.
I am hoping I can use R /dplyr library in JMP? I think I can write something that does the trick

Re: Delta calculations

Prometheus — Sun, 17 Sep 2023 18:57:56 GMT

I have this R code that should do the trick.

library(dplyr)

your_data <- your_data %>%
arrange(MeterName, DateTime) %>%
group_by(MeterName) %>%
mutate(DeltaMeterValue = MeterValue - lag(MeterValue, default = first(MeterValue))) %>%
ungroup()

Re: Delta calculations

jthi — Sun, 17 Sep 2023 19:40:01 GMT

You can use R with JMP, but that solution looks fairly similar to the solution using Lag() in JMP (you could also use Dif()). My solution with Col Moving Average is more difficult to understand but the idea is to use Col Moving Average to get the value of previous row.

Scripting Guide > Extending JMP > Work with R

JSL Syntax Reference > JSL Functions, Operators, and Messages > R Integration Functions

Re: Delta calculations

Prometheus — Mon, 18 Sep 2023 06:20:08 GMT

When I try to use any of the solutions here, I only get the first date (=0) and not the rest -I tried to sort in different ways to see if it is here I am doing something wrong

Re: Delta calculations

jthi — Mon, 18 Sep 2023 06:42:48 GMT

Your column names seem to be wrong, start by fixing those in the formula

Re: Delta calculations

Prometheus — Mon, 18 Sep 2023 09:17:51 GMT

Ah, I see the spelling error.

Re: Delta calculations

Prometheus — Wed, 20 Sep 2023 12:35:43 GMT

A build in function that calculates delta within groups would be good.
I got it to work but I do have difficult understanding the scripting language, and I tried drag/drop making the formula from scratch but I simply cant get all the parts placed correctly as they are in the script you gave me here

Re: Delta calculations

Prometheus — Wed, 20 Sep 2023 12:44:41 GMT

Is there a description of the col moving average function?
When I look in the support page the description dos not say anything about how many values it takes.

Col Moving Average

Returns the moving average over a given interval based at the current row. Missing values are ignored

Re: Delta calculations

jthi — Wed, 20 Sep 2023 12:46:32 GMT

There is a way to do this interactively. Right click on your grouping column and make it Group By column for new formulas (data must be sorted by groups and most likely by time or something similar)

Next right click on your calculation/value column and go to Row->Difference

With this you get a formula like

Re: Delta calculations

hogi — Wed, 20 Sep 2023 12:58:12 GMT

I will try to upload a new version of the .

The new version has an option to calculate the difference to the previous row - based on the trick that @jthi provided here.
Jarmo's code (via Col Moving Average) is much better than what you get via the right click new formula column menu :)

After the update, it will look like this.
New: "N-1"

Re: Delta calculations

jthi — Wed, 20 Sep 2023 12:49:55 GMT

I did provide link to the documentation in one of my posts: JSL Syntax Reference > JSL Functions, Operators, and Messages > Statistical Functions - Col Moving Average . In my opinion the documentation for Col Moving Average isn't very clear and usually I have to make some small tests to get it working properly (and remember how it works).

Other place to find some documentation (sometimes different...) is Scripting Index from JMP's help menu