I want to use a local variable to track cyclic data

[#1]

Hi all,

I'm very new to using more powerful statistical software and am slowly learning the ropes. I'm good at implementing feedback, but not so great at my own research.

I work with batteries. We gather a ton of data, and we use different tests depending on what we want to find out. The data generated is cyclical in nature, as we charge the cell, rest it, discharge it, and rest it again. These are called steps.

In one test (I call them runs), we may have several cycles at one regime, then several cycles in another. Therefore, it is important for me to be able to track responses (currents, voltages, capacities, energies, etc.) as factors of cycles, steps as well as runs.

Onto my query: I need a column formula which will start at 11, and then increment up by a value of 1 every 4 rows. This column will track the total number of cycles across multiple runs. The reason why it needs to increment by 4 is that that is how many steps there are in each cycle.

To further clarify, let me give an example.

Column 1 is labelled Run. It tracks the number of a particular test in series.

Column 2 is labelled Total Cycle Number. This is the column which I need to code properly.

Column 3 is labelled Actual Cycle Number. It describes the cycle number of a particular test. These values are cyclical, and go back to 1 at the start of each new Run. Their upper bound is not consistent. The values also repeat depending on how many steps are in each cycle. An example of the values would be 1,1,1,1,2,2,2,2,3,3,3,3,4,4,4,4,1,1,1,1,2,2,2,2, etc.

Column 5 is labelled RawStepID. It resets back to 1 at the start of each cycle. An example would be 1,2,3,4,1,2,3,4,1,2,3,4, etc.

Column 6+ is any data that is obtained.

My approach so far to Column 2 has been to try and prepare an IF statement with a local variable. I want it to increment this variable by a value of 1 everytime RawStepID == 1. I need it to start at 11, then become 12 when RawStepID=1, become 13 next time RawStepID=1, etc. Then I need the formula to display the current value of the variable.

I.e., if using the data I typed for Column 3, I need the values of this column to become

11,11,11,11,12,12,12,12,13,13,13,13,14,14,14,14,15,15,15,15,16,16,16,16,17,17,17,17,18,18,18,18, etc.

I hope this is clear. In the meantime I will try and work out how to include a table in a forum post as I've seen other members do, or failing that create a dummy table to attach to here.

Thanks,

ScaredAntelope.

[#2 · Accepted solution]

Hi @ScaredAntelope,

Two ways you might approach column 2 'Total Cycle Number' could be to explicitly define it by row:

11 + Floor( (Row() - 1) / 4 )

or calculate it the way I think you are doing it now:

If(
	Row() == 1, 11,
	:RawStepID == 1 & Lag( :RawStepID ) != 1, Lag( :Total Cycle Number ) + 1,
	Lag( :Total Cycle Number )
)

Note the two conditions required to increment the Total Cycle number in the middle condition. You could extend that to check other columns too, for example maybe the Actual cycle number should also be 1.

[#3]

Hello @ih,

Thank you! The second approach you provided is exactly what I was trying to implement. I won't share what my code looked like but I can assure you that it was spaghetti of the most overboiled kind.

If I understand correctly, it's also a very flexible solution. I can change the starting number of the row by changing the 11 in line 2, I can change the incrementation by changing lag of total cycle number at the end of line 3, and I can see how to adapt this code to increment e.g., the Run value every time Actual Cycle Number cycles back to 1.

If( 
Row() == 1, 23,
:ActualCycleNumber == 1 & Lag ( :ActualCycleNumber ) != 1, Lag ( :Run ) + 1,
Lag ( :Run )
)

I don't understand the effect of the second condition for incrementation of total cycle number. Or how to use Lag in general. Could you explain or point me to a good resource for learning such principles?

[#4]

Lag just looks at the value of the previous row, searching for lag in Help or the Scripting Index has a little more information.  That second condition was in case the ActualCycleNumber is 1 for a few sequential rows and it should only reset on the first row that is a 1.  You might not need that for your application.

[#5]

What questions are you trying to answer?  A quick look, column 2 (11,11,11,11,12...) will be confounded with column 1 (1,1,1,1,2...)so this would not be useful.  Each column should be a unique X or Y.

[#6]

Hi @statman 

That is the case for this particular scenario, yes. However, Total Cycle Number is something that I use extensively in queries in order to track data across multiple runs. So for example, I could have this table which is specific for Run 1, and another table for data specific to Run 2. In both, the Actual Cycle Number will start at 1 and increase up to whatever (defined within the test). However, the Total Cycle Number will start at the cumulative sum of Actual Cycle Numbers experienced by that battery across all runs.

Another way to view it is that Actual Cycle Number is the raw data I obtain from my experimental software, while Total Cycle Number is processed data. And one final example:

Run 1 contains 30 cycles. In this case, both Total Cycle and Actual Cycle Numbers will start at 1 and end at 30. Run 2 contains 15 cycles. Total Cycle Number will start at 31, end at 45. Actual Cycle Number will start at 1 and end at 15. Run 3 contains 102 cycles. Total starts at 46, ends at 147, Actual starts at 1 ends at 102, etc. etc. Often this kind of incrementation happens within the same data table. Keeping track of the total number of cycles across all runs is very useful for analysing, as that is one of the key factors for battery health.

I hope that clears up my need for the data as is!