Column Definition Tab (New Calculated Column)

This feature allows you to create a new column based on a formula.

Calculated columns are useful in data analysis; for example, you might define a column that is the derivative of another column with respect to time, a smoothed version of a sensor column, or it might be the ratio of two sensor columns. Calculated columns can be graphed or used in further analysis.

The new column can a function of another column or columns, or it may be defined by a rule that does not depend on other columns. The arithmetic operators ("^", "*", "/", "+", "-"), parentheses, and other mathematical functions can be used to create a new column based on one or more existing columns.  For example, if you have current and potential data, you can use a new column to calculate the power (current times potential). This definition is shown here.

You can give your new column a Name, a Short Name, and whatever Units you like by typing into the appropriate controls.
 
Name The long name is used in graph axis labels, column headings and menus whenever there is sufficient room.  You cannot use a quotation character (“) in  the Long Name
Short Name The short name is used in graph axis labels, column headings and menus whenever there is limited room.  If you do not enter a Short Name, the first letter of the Long Name will be used.
Units You can enter whatever units you desire for your new column.

You can choose special symbols (including subscripts and superscripts) to be included in all of the above fields by clicking the menu to the right of each text field.


When you enter your equation into the edit box labeled "Equation", you may type existing column names (inside quotation marks), or you can choose column names from the Variables (Columns) pull-down menu.  If you have multiple columns of the same name in different data sets, you can have the calculated column refer to the column in a specific data set.

If you choose a sensor column as a variable, a Display During Live Readouts checkbox will appear in the New Calculated Column dialog. If you check this option, the readouts for the calculated column will be displayed on the Toolbar.

A Calculated Column resides in a particular Data Set. The Destination menu allows you to choose which Data Set will receive the new column. If you check the Add to All Similar Data Sets option, copies of the calculated column will be added to Data Sets that share the same column names. For example, you might want to create a smoothed version of the data from an accelerometer, and you want such a column in each of your stored runs. To do this, add a calculated column to the Latest Data Set, and check the option to add to all similar data sets.

You may also type mathematical function names or choose them from the "Functions" pull-down menu (see below for function definitions).  For example, if you have a column named "X", and you want to create another column whose values are the square roots of the values in "X", make sure the focus is on the "Equations" control, then type (or choose from the controls):

sqrt("X")

Let's suppose you named your new column "Root".  If "X" had the values: 4, 25, and 64, then "Root" would have the values: 2, 5, and 8.  If you changed any of the values in "X", "Root" would also change.  "Root" is linked to and dependent on the column "X".  If you delete "X", "Root" will also be deleted.


When you create calculated columns, the input to a function will most often be another column.  Many functions, however, can also accept a single number as input.  Thus, if you give these functions a single number, they will return a single number.  If you give them a column, they will return a column.

Logger Pro supplies many mathematical functions; you can often build up the calculation you want by combining the supplied functions. You can nest functions, use arithmetic operators inside functions, and use parentheses to group operations.  For example, a valid expression (assuming you have columns named "X" and "Y"), is:

sin(("X" + "Y") / sqrt(3))

If the inputs you give to a function are not mathematically legal (e.g. square roots of negative numbers), the calculated column will silently fill in a special value (called a NAN -- Not A Number) which results as a blank cell.  Any further expression that uses a NAN will also generate a NAN.



Additional details on column selection:
When you give a calculated column an input column, you can either give just the column name, or you can give the full name (the Data Set name, |, and the column name).  The difference is subtle but useful in some cases.  When you give just the column name, you are saying that you want the calculated column to use a column of that name in the same Data Set. When you give the full name, you are referring to a specific column in a specific data set, regardless of which data set you have placed your calculated column. 

The Variables menu will provide column names in the current Data Set, but if you need to refer to a column in another data Set, choose Choose Specific Column from the list. You will see a dialog which lists all columns in all data sets. 

Functions

-  Trigonometric functions will use degrees or radians as set in the Settings for (file name) in the File menu.

-  For more information about Savitsky-Golay methods see Numerical Recipes in C:
           http://lib-www.lanl.gov/numerical/bookcpdf.html chapter 15
 
Function
Description
analysis->

analysis ("X", startRow, endRow)

will take all columns named "X" and extract startRow to endRow for each of those columns, appending the values into a single column.

dataSets
datasets("X") will append the dataset names of all datasets that have a column named "X".  Use analysis and datasets together to create a graph (analysis on the vertical axis and datasets on the horizontal).

for example, if you had 3 datasets as follows:
DS1  DS2  DS3
 X    X    Y
 1    11   21
 2    12   22

and then added analysis("X", 1, 1) and datasets("X") you would get:

datasets analysis
  DS1       1
  DS2       11
beats per minute BeatsPerMinute("Signal", "Time", intervalInSeconds, minPercent, maxPercent, noise)
This function returns the number of beats per minute of the values in "Signal" vs. "Time". This function is similar to the rate function except that the interval given here is always in seconds and the returned value is always in minutes. For example, if "Time" is in seconds then: beatsPerMinute("Signal", "Time", interval, min, max, noise) = 60 * rate("Signal", "Time", interval, min, max, noise)
blood pressure ->

diastolic The measured arterial pressure when the heart is at rest. "Pressure" and a "Time" column as inputs and return a single number.
diastolic("Pressure", "Time")
"Pressure" Pressure values from the BPS
"Time" Time the pressure values were recorded
Returns the smaller number of blood pressure
meanArterialPressure
meanArterialPressure("Pressure", "Time") Returns the pressure value at the max peak used for blood pressure calculations
"Pressure" Pressure values from the BPS
"Time" Time the pressure values were recorded
Returns the mean arterial pressure.
Oscillations
Oscillations("Pressure", "Time") Returns the Oscillations of the peaks and valleys used to calculate systolic and other blood pressure values
"Pressure" Pressure values from the BPS
"Time" Time the pressure values were recorded
Returns the Oscillations of the peaks.
OscillatoryPeaks
OscillatoryPeaks("Pressure", "Time") Returns the peaks used to calculate systolic, diastolic, and pulse (the "high" values in "Oscillations").
"Pressure" Pressure values from the BPS
"Time" Time the pressure values were recorded
Returns the peaks.
pulse
pulse("Pressure", "Time") Returns the rate of blood pressure vs. time -- in beats per minute
"Pressure" Pressure values from the BPS
"Time" Time the pressure values were recorded
Returns the pulse rate in beats per minute.
Computes the beats per minute using the inputs from the Blood Pressure Sensor (similar results, different algorithm as the other beats-per-minute functions)
systolic
The measured arterial pressure when the heart contracts.
systolic("Pressure", "Time")
"Pressure" Pressure values from the BPS
"Time" Time the pressure values were recorded
Returns the larger number of blood pressure
boolean->
For the boolean functions a 1 is considered true, 0 false and anything else an invalid input
 AND
AND(X, Y) return 1 if and only if X and Y are both 1
 NOT
NOT(X) return 1 if X is 0; 0 if X is 1
 OR
OR(X, Y) return 1 if X or Y is 1
 XOR
XOR(X, Y) return 1 if X or Y is 1 but not both
calculus ->
derivative
Numerical derivative. ("Y", "X")
"Y": A column of real numbers
"X": Optional. A column of real numbers
The numerical derivative is the weighted average of the slope of 'n' points around each point.  You can set 'n' in Settings for (Name).  If you don't supply an "X" column, the program will find one.
derivativeSG
Savitsky-Golay derivative.("Y", "X")
"Y":   A column of real numbers
"X":   Optional. A column of real numbers
Fits a polynomial to 'n' points around each point and computes the derivative of the polynomial at that point.  You can set 'n' in Settings for (Name).  If you don't supply an "X" column, the program will find one.
derivativeTimeShift
DerivativeTimeShift ("Y", "X"): returns the derivative of "Y" with respect to "X".  This function is specifically designed to be used with photogate and picket fence data. The derivatives returned are adjusted to estimate values at the start of the timing interval, instead of the midpoint. For details see The Physics Teacher, Vol 35, April 1997, p.220.

Average velocity during the time interval is equal to the instantaneous velocity at midpoint of the time interval.


Where

secondderivative
Numerical second derivative"Y", "X") "Y":   A column of real numbers
"X":   Optional. A column of real numbers
Calculates the numerical second derivative of "Y" with respect to "X".  If you don't supply an "X" column, the program will find one.
secondderivativeSG
Savitsky-Golay second derivative("Y", "X") "Y":   A column of real numbers
"X":   Optional. A column of real numbers
Fits a polynomial to 'n' points around each point and computes the second derivative of the polynomial at that point.  You can set 'n' in Settings for (Name).  If you don't supply an "X" column, the program will find one.
secondderivative
Time Shift
Numerical time-shifted second derivative("Y", "X")
"Y": A column of real numbers
"X": Optional. A column of real numbers
Numerical time-shifted second derivative. Calculates the second numerical derivative of "Y" with respect to "X".  The values are shifted so that the derivatives are calculated at the midpoints between each two values.  If you don't supply an "X" column, the program will find one.

integral
Numerical integral. ("Y","X")
"Y":   A column of real numbers
"X":   Optional. A column of real numbers
The numerical integral is the running sum of the areas of rectangles calculated by the midpoint rule.  The i'th rectangle is (Yi - Y(i-1)) / (Xi - X(i-1)).  If you don't supply an "X" column, the program will find one.
collapse
collapse("X"): Returns a column with all the non-numerical cells (blanks and text) removed.
collapseIndirect
collapseIndirect(X, Y) Returns a column of only the rows in "X" corresponding to rows in "Y" that have valid numerical cells.
constant Constant.(x, num)
x:   A real number
num: A real number or a column
Generates a constant column filled with the value 'x'.  The number of values in the returned column is num, or if a column was passed in, the size of the passed-in column.
delta Delta "X": A column of real numbers
Returns a column of values where the i'th value is the i'th value in "X" minus the (i-1)'th value in "X".
ElectrophoresisInterpolate ElectrophoresisInterpolate("Std. Dist", "Std. BP", "Dist")
"Std. Dist": Distances from the standard
"Std. BP": Base Pair Counts from the standard
"Dist": Distances to interpolate
Returns a column of base pair counts based on the Electrophoresis curve fit for "Std. Dist" vs. "Std. BP" given "Dist".  Will NOT work if curve fit has been deleted.  This function is used automatically when doing a Gel Analysis (Electrophoresis).
exp Exponent "X":    A column of real numbers
exp(x) = e^x, where e is the natural log base (2.17...).
integer
integer("X"): Extracts the integral part of values in "X"
interpolate interpolate("X"): Fills in missing values using linear interpolation
ln Natural logarithm. "X": A column of real numbers larger than 0
If b = ln(a) then e^b = a  (Where e is the constant 2.17...).
log (Log base 10) If b = log(a) then 10^b = a.
"X":    A column of real numbers larger than 0.
modulo modulo("X", n)
"X": A column of integers
n: An integer larger than 0
Returns the remainder of each of the numbers in "X" when divided by n.
photogate ->
Blocked to Blocked
BlockedToBlocked("Time", "Gate1", "Gate2")
"Time":    Optional. A column of real numbers (the times of events)
"Gate1": A column of photogate states (1's and 0's)
"Gate2": Optional. A column of photogate states (1's and 0's)
Returns a column of the times between successive blocked events in gate 1 and blocked events in gate 2.  If you don't enter a "Time" column, the program will find one.  If you don't enter "Gate2", "Gate1" will be used.
Blocked to Unblocked
BlockedToUnblocked("Time", "Gate1", "Gate2")
"Time": Optional. A column of real numbers (the times of events)
"Gate1": A column of photogate states (1's and 0's)
"Gate2": Optional. A column of photogate states (1's and 0's)
Returns a column of the times between successive blocked events in gate 1 and unblocked events in gate 2.  If you don't enter a "Time" column, the program will find one.  If you don't enter "Gate2", "Gate1" will be used.
Unblocked to Blocked
UnblockedToBlocked("Time", "Gate1", "Gate2")
"Time": Optional. A column of real numbers (the times of events)
"Gate1": A column of photogate states (1's and 0's)
"Gate2": Optional. A column of photogate states (1's and 0's)
Returns a column of the times between successive unblocked events in gate 1 and blocked events in gate 2.  If you don't enter a "Time" column, the program will find one.  If you don't enter "Gate2", "Gate1" will be used.
Unblocked to Unblocked
UnblockedToUnblocked("Time", "Gate1", "Gate2")
"Time": Optional. A column of real numbers (the times of events)
"Gate1": A column of photogate states (1's and 0's)
"Gate2": Optional. A column of photogate states (1's and 0's)
Returns a column of the times between successive unblocked events in gate 1 and unblocked events in gate 2.  If you don't enter a "Time" column, the program will find one.  If you don't enter "Gate2", "Gate1" will be used.
Blocked MidTimes
BlockedMidTimes("Time", "Gate1", "Gate2")
"Time": Optional. A column of real numbers (the times of events)
"Gate1": A column of photogate states (1's and 0's)
"Gate2": Optional. A column of photogate states (1's and 0's)
Calculate the average times between blocked events from Gate 1 to Gate 2.  If you don't enter a "Time" column, the program will find one.  If you don't enter "Gate2", "Gate1" will be used.
Blocked to Unblocked MidTimes
Blocked to Unblocked MidTimes
"Time": Optional. A column of real numbers (the times of events)
"Gate1": A column of photogate states (1's and 0's)
"Gate2": Optional. A column of photogate states (1's and 0's)
Calculate the average time between the blocked events in gate 1 and unblocked events in gate 2.  If you don't enter a "Time" column, the program will find one.  If you don't enter "Gate2", "Gate1" will be used.
Unblocked to Blocked MidTimes
Unblocked to Blocked MidTimes
"Time": Optional. A column of real numbers (the times of events)
"Gate1": A column of photogate states (1's and 0's)
"Gate2": Optional. A column of photogate states (1's and 0's)
Calculate the average time between unblocked events in gate 1 and blocked events in gate 2.  If you don't enter a "Time" column, the program will find one.  If you don't enter "Gate2", "Gate1" will be used.
Unblocked MidTimes
UnblockedMidTimes("Time", "Gate1", "Gate2")
"Time":  Optional. A column of real numbers (the times of events)
"Gate1": A column of photogate states (1's and 0's)
"Gate2":  Optional. A column of photogate states (1's and 0's)
Calculate the average times between unblocked events from Gate 1 to Gate 2.  If you don't enter a "Time" column, the program will find one.  If you don't enter "Gate2", "Gate1" will be used.
Pendulum Period
PendulumPeriod("Time", "Gate1")
"Time": Optional. A column of real numbers (the times of events)
"Gate1": A column of photogate states (1's and 0's)
Calculate the time between every other blocked event on Gate 1.  If you don't enter a "Time" column, the program will find one.
derivativeTimeShift DerivativeTimeShift ("Y", "X"): returns the derivative of "Y" with respect to "X".  This function is specifically designed to be used with photogate and picket fence data. The derivatives returned are adjusted to estimate values at the start of the timing interval, instead of the midpoint. For details see The Physics Teacher, Vol 35, April 1997, p.220.

Average velocity during the time interval is equal to the instantaneous velocity at midpoint of the time interval.


Where


secondDerivativeTimeShift Numerical time-shifted second derivative("Y", "X")
"Y": A column of real numbers
"X": Optional. A column of real numbers
Numerical time-shifted second derivative. Calculates the second numerical derivative of "Y" with respect to "X".  The values are shifted so that the derivatives are calculated at the midpoints between each two values.  If you don't supply an "X" column, the program will find one.


rate rate("Y", "X", t, m1, m2, n)
"Y":   A column of real numbers
"X":   Optional. A column of real numbers
t:   Optional. Time interval
m1:   Optional. Minimum threshold
m2:   Optional. Maximum threshold
n:   Optional. Noise threshold
Returns the rate of "Y" with respect to "X", where t is the time interval measured, m1 is min percentage threshold, m2 is max percentage threshold, and n is noise threshold.  "X", t, m1, m2, and noise are all optional with default values "X" is time column, t = 1/10 the range, m1 = 40%, m2 = 60%, and noise = 0. Details
round (round) round(x) = the closest integer to x. (If x is equidistant to two integers, round(x) gives the largest of the two).
"X":    A column of real numbers
Round. round(x) = the closest integer to x. (round(0.5) = 1).
smoothAve Smooth Average. "X": A column of real numbers
Smooth Average. "X": A column of real numbers
Returns a column of moving averages of the values in "X".  The width of the "window" to use when averaging points can be set in Settings for (Name)...
smoothSG Savitsky-Golay Smoothing. ("Y", "X")
"Y":   A column of real numbers -- the values to smooth
"X":   Optional. A column of real numbers
Fits a polynomial to 'n' points around each point and computes the value of the polynomial at that point.  You can set 'n' in Settings for (Name).  If you don't supply an "X" column, the program will find one.
statistics ->
abs
Absolute value "X": A column of real numbers. If x less than 0, then abs(x) = -x.  Otherwise, abs(x) = x.
ceiling
Ceiling. "X": A column of real numbers
ceiling(x) = the smallest integer larger than or equal to x.
floor
Floor. "X": A column of real numbers
floor(x) = the largest integer smaller than or equal to x.
mean 
Mean "X":  A column of real numbers
Arithmetic mean.  Returns the sum of all the values in "X" divided by the number of values.
median
Median. "X":    A column of real numbers
 If m = median("X"), then half the numbers in "X" are greater than (or equal) to m, and half are less than or equal.
min
Minimum. min("X")
Compares all the values in a single column (e.g. min("X")), returns a single number -- the smallest number in "X". 
min2
Minimum. min("X", "Y")
"X":    A column of real numbers
"Y":    A column of real numbers or a single number.
Compares all the values in 2 columns (e.g. min2("X", "Y"))
Compares all the values in a column against a real number (e.g min2("X", 5.1))
max
Maximum.  max("X")
Compares all the values in a single column (e.g. max("X")), returns a single number -- the greatest number in "X". 
max2
Maximum.  max2("X", "Y")
"X":    A column of real numbers
"Y":    A column of real numbers or a single number.
Compares all the values in a column against a real number (e.g max2("X", 5.1))
numRows
NumRows "X": A column of real numbers
Returns a single value -- the number of rows in "X".
randInt
Random Integer. randInt(min, max, num): 
min: A real number
max: A real number
num: A real number or a column
Returns a column of random integers between min and max (inclusive).  The size of the returned column is num. If num is a column, then the size will be will be the number of rows in that column.
randReal Random Real.:  randReal(min, max, num)
min: A real number
max: A real number
num: A real number or a column
Returns a column of random real numbers between min and max (inclusive).  The size of the returned column is num. If num is a column, then the size will be will be the number of rows in that column.
stddev Standard Deviation. "X": A column of real numbers
Returns a column representing the standard deviations of each of the numbers in "X".
step step(start, increment, num, first, skip)
start:   Start value
increment:   Increment value
num:   Number of values to generate
first:   First non-empty row
skip:   Rows to skip between each value
Generates a column "num" rows long starting with "start" and incrementing by "increment".  "num" can be a positive integer or a column name.  Optional parameters: "first" is the first non-empty row and "skip" is the number of rows to skip between each value.
StepColumnBase  stepColumnBased("X", start, increment, first, skip)
start: Start value
increment: increment value
first: optional. First non-empty row
skip: Optional. Row to skip between each value
Generates a column based on non-empty values in column "X" starting with "start" and incrementing by "increment." "First" is the first non-empty row and "skip" is the number of rows to skip between each value.
subset subset("X", startRow, step)
"X": A column of real numbers
startRow: An integer larger than 0
step: An integer larger than 0
Extract a subset. Returns a column extracted from "X" starting with 'startRow' by 'step'.  For example, subset("X", 1, 2) will get every second row of "X" starting with row 1.
sum "X": A column of real numbers
Sum("X") returns a column whose n'th value is the sum of the values in "X" from row 1 to n.
sqrt Square root. "X": A column of non-negative real numbers.
If x is the square root of y, then x*x = y.
trigonometric ->
sin
"X": A column of real numbers
In a right triangle with angle between two sides 'x', sin(x) is the length of the opposite side divided by the hypotenuse.
cos
"X": A column of real numbers
In a right triangle with angle between two sides 'x', cos(x) is the length of the adjacent side divided by the hypotenuse.
tan
"X": A column of real numbers
In a right triangle with angle between two sides 'x', tan(x) is the length of the opposite side divided by the adjacent side.
asin
Arcsine function.  "X": A column of real numbers between -1 and 1
asin(x) = the angle whose sine is x.
acos
Arccosine function.  "X": A column of real numbers between -1 and 1
acos(x) = the angle whose cosine is x.
atan
Arctangent(x) "X": A column of real numbers
atan(x) = the angle whose tangent is x.  The result will be between -pi/2 and pi/2.
sinh
"X": A column of real numbers
Hyperbolic sine.
cosh
"X": A column of real numbers
Hyperbolic cosine.
tanh
"X": A column of real numbers
Hyperbolic tangent.
Value Value(n, "X")
n: Number of rows backwards (when n < 0) or forwards (n >0) in column "X" to extract a value from.
"X": Column to extract values from.
Create a new column from another column by extracting offset values.

For some of the above functions (e.g. derivative), you do not have to specify a second column.  If you leave out the second column, the software will choose a second column for you.  It automatically chooses the time column or the first column if there is no time column.

If data are imported from an experiment file, you may want to specify the independent column. For example, if the imported data included “time” in the first column but you wanted to calculate the derivative of pH with respect to volume, you have to define the derivative as derivative("pH","Volume").
 

Parameters:  

A User Parameter is a place to store an adjustable numeric value. The parameter can be referred to in calculated columns, trigger level thresholds, and similar fields. New User Parameters can be added from the Data menu. To insert a specific parameter into a calculated column's equation, select its name from the Parameters menu.




You can edit the calculated columns by either double-clicking the column name Data Browser or choosing Column Options from the Data Menu.

 

See Also:

Options

Creating and Editing Columns