The Transformation operation creates a new column in a worksheet that is the result of a transformation. Phoenix supports arithmetic, baseline, custom, and function transformations. All data in the source column are transformed regardless of exclusions, which are carried over to the new column.
The Transformation user interface consists of two main parts, the Setup tab and the Options tab.
Main Mappings panel
Options tab
Users must map a dataset to the Main Mappings panel when the first step is set up. All subsequent steps will automatically be mapped to the same dataset. Required input will be highlighted orange in the interface.
Users can select to assign the units of an input (mapped) column to a new column created via a transformation. If the Output Column Unit selection is mapped, the column mapped to that context will have its unit used in the new output column.
Context associations for a Transformation object change depending on which transformation type is selected in the Options tab.
Arithmetic transformation mappings
None: Columns mapped to this context are simply included in the output.
x Column and y Column: Columns in a worksheet used in an arithmetic transformation.
Output Columns:
None: Units of columns mapped to this context are not used in the new output column.
Unit: The column whose units are to be used in the new output column.
Baseline transformation mappings
None: Columns mapped to this context are simply included in the output.
x Column: The independent variable that is used to compute the baseline values.
Sort: Sort variable(s) used to define individual profiles.
Time: Time values that are used to compute the baseline values.
Output Column:
None: Units of columns mapped to this context are not used in the new output column.
Unit: The column whose units are to be used in the new output column
Custom transformation mappings
Output Column:
None: Units of columns mapped to this context are not used in the new output column.
Unit: The column whose units are to be used in the new output column.
Other mapping contexts are defined in the Formula field.
Function transformation mappings
None: Columns mapped to this context are simply included in the output.
x Column: The independent variable that is used to provide the values for the function transformations.
Output Column:
None: Units of columns mapped to this context are not used in the new output column.
Unit: The column whose units are to be used in the new output column.
The Options tab is used to select the transformation type and specify options for each transformation type. The Options tab also allows users to specify the sequence of the operations in the Data Wizard.
Phoenix supports the following types of transformations:
Arithmetic: (x – y)/y, (x + y)/y*100, x – n, x – y, x*n, x*y, x/n, x/y, x^n, x^y, x + n, x + y
Baseline: % Change from Baseline, Change from Baseline, Ratio from Baseline
Custom: Custom Function, see “Example custom functions”.
Functions: 1/x, Absolute value(x), e^x, LN(x), Log10(x), Square root(x)
In the Arithmetic and Functions options, x and y are columns in a worksheet, in the Arithmetic options, n is a number, and in the Functions options e is the base of the natural logarithm (2.718281828).
Note:Blank cells are treated as zero when operated on with cells that contain numbers.
In the Transformation Type menu, select one of four transformations: Arithmetic, Baseline, Custom, Functions.
In the Transformation Type menu, select Arithmetic.
In the Transformation menu, select the arithmetic transformation type.
If one of the x and y transformations are selected, the x Column and y Column mapping contexts are made available in the Main Mappings panel.
Use the option buttons to map data types in the worksheet to the x Column and y Column contexts. If one of the x and n transformations are selected, then a value for n must be specified.
In the Arithmetic Options field, type a value for n.
In the New Column Name field, type a name for the column used to contain the transformed values.
In the Destination Area, select one of the option buttons.
Append places the new column after the last column in the dataset.
Adjacent places the new column to the right of the data type mapped to the x Column context.
Users can choose whether to specify sort variables for baseline transformations. Sort variables define unique profiles that are used to compute change from baseline separately for each profile. Typically, a single variable representing subject IDs is sufficient. Other data may require more than one variable, such as Subject and Period. The baseline transformation sorts the output worksheet in alphabetical or numerical order, based on the sort variables.
Rules for using datasets in a baseline transformation:
By default, Phoenix uses the value corresponding to time=0 as the baseline for each unique profile. If a profile has more than one value at time zero, Phoenix uses the first value as the baseline.
If a zero time value is not in the dataset, the time value to use for the baseline value (normally the lowest time) must be specified, or the user must choose to use the minimum time for each group, in order to generate the baseline transformations.
Enter the lowest (or other) time value in the Fixed Value field or
Select MinimumPerGroup in the Starting Point dropdown menu.
If the columns mapped to x Column has a zero or missing value at the time specified for the baseline, then the % Change from Baseline and Ratio from Baseline options will produce empty results. An alternate baseline time value or the MinimumPerGroup option may be used.
To create a baseline transformation
In the Transformation Type menu, select Baseline.
In the Transformation menu, select the baseline transformation type.
If % Change from Baseline is selected, Phoenix performs a transformation on a column by calculating the percent change between each value and either a fixed baseline value or the minimum value for each distinct profile.
result=(x – baseline)/baseline*100
If Change from Baseline is selected, Phoenix performs a transformation on a column by calculating the change between each value and either a fixed baseline value or the minimum value for each distinct profile.
result=x – baseline
If Ratio from Baseline is selected, Phoenix performs a transformation on a column by determining the ratio to either a fixed baseline value or the minimum value from each distinct profile.
result=x/baseline
In the Main Mappings panel, click the option buttons to specify the x Column, the Sort column(s), and the Time column.
Users can map multiple or no columns in a worksheet to the Sort context. Each column mapped to the Sort context creates a unique profile. Baselines are calculated on a per profile basis.
In the New Column Name field, type a name for the column that will contain the transformed values.
In the Starting Point menu, select Fixed Point or Minimum per Group.
If Minimum per Group is selected, the lowest time point in each profile is used to generate the baseline transformation. Selecting this option means that the baseline transformation uses a different starting point for each profile defined by the sort key(s).
If Fixed Point is selected, users can enter custom starting time value in the Fixed Value field.
In the Fixed Value field, type a time value for a time column in Numeric format or in Text format.
If a time column is in Text format, users must type fixed time value in the same format used to enter the time values in the worksheet. For example, if the first time point used as the baseline is 1:00, then 1:00 must be typed in the Fixed Value column. For acceptable dates and times in Text format, see “Date and time formats”.
The default is zero, because Phoenix, by default, evaluates the change from baseline at time=0. If a value is entered that is higher than the highest time value, then no output values are produced in the new column.
In the Treat Time Values as menu, select the format used by the time column.
Numeric Format means the Time column is in Numeric format.
Date\Time Format means the Time column is in Text format. If selected, the Time Units menu is made available. The selected units are applied to the transformed time column, and are converted if they do not match the source time column units.
In the Time Units menu, select the units to use in the transformed time column.
If Date\Time Format is selected, the Create Transformed Time Column checkbox is made available.
Check the Create Transformed Time Column box to create a Numeric time column in the output that is based on the values in a Text time column or clear the checkbox to not create a transformed time column.
In the Destination Area, select one of the option buttons.
Append places the new column after the last column in the dataset.
Adjacent places the new column to the right of the data type mapped to the x Column context.
Users can create a custom transformation that transforms one or more columns in a worksheet using the functions provided by Phoenix.
To create a custom transformation
In the Transformation Type menu, select Custom.
Custom Function is selected by default in the Transformation menu. There are no other options in the menu for custom functions.
In the New Column Name field, type a name for the column that will contain the transformed values.
In the Formula field, type a custom function or use the pointer to select a function name in the Function List and drag the function name to the Formula field.
Custom functions can be defined, edited, and combined in the Formula field. The if custom function allows users to create a compound function using the logical operators & (and) and | (or). For example, if((col1=1) & (col2=2), 1, 0). Each condition in the if statement must be placed in parentheses. The function name must be followed immediately by the left parenthesis, there cannot be a space.
See “Example custom functions” for a list of all possible functions and examples of how to use them. The same information is also listed in the Function List in the Options tab.
In the Main Mappings panel, click the option buttons to specify the mapping contexts created by the custom function.
To find the absolute value, logarithmic values, or square root
In the Transformation Type menu, select Functions.
In the Transformation menu, select the function transformation type.
Use the option buttons to map a column in the worksheet to the x Column context.
In the New Column Name field, type a name for the column used to contain the transformed values.
In the Destination Area, select one of the option buttons.
Append places the new column after the last column in the dataset.
Adjacent places the new column to the right of the data type mapped to the x Column context.
Note:Using the Data Wizard, it is easy to create a sequence of operations, whether transformations or combinations of the other Wizard operations.
Below are some common functions that may be helpful.
substitute(conc,”BQL”,0.1,0)
In the mapped column “conc”, replace each cell value found to be equal to BQL with 0.1 in the new column.
substitute(CobsPop, 0.1,"BQL",0)
In the mapped column “CobsPop”, replace each cell value found to be 0.1 with “”BQL” in the new column.
if(CobsPop==0.01, 1, 0) or;
Note: There are 2 equal signs.
If any cell in the mapped column “CobsPop” is equal to 0.1, then enter a 1 in the new column; otherwise enter a 0.
if(CobsPop = 0.01, 1, 0)
Note: When using a single equal sign, spaces are needed before and after the equal sign.
If any cell in the mapped column “CobsPop” is equal to 0.1, then enter a 1 in the new column; otherwise enter a 0. (Same as previous function.)
if(CobsPop = 0.1, 1, if(CobsPop < 1, 2, 0))
Note: You need spaces before and after the < sign or use two << signs without spaces.
Embedded ifs. If any cell in the mapped column “CobsPop” is equal to 0.1, then enter a 1 in the new column; if it is less than 1, enter a 2; otherwise enter a 0.
if(CobsPop = 0.1, 1, if(id==1, 2, 0))
Embedded ifs with different vars. If any cell in the mapped column “CobsPop” is equal to 0.1, then enter a 1 in the new column; if the mapped column “id” is equal to 1, enter a 2; if none of these conditions are met, enter a 0.
replace(conc,2,2,"LQ")
Starting at number 2 position in the mapped column “conc”, replace the string with 2 letters “LQ”.
This is useful for converting BQL to BLQ, but notice that it does not discriminate, so a number 1.537753 ends up being 1LQ37753 (position 2 and 3 replaced). Use with caution!
Ln(conc)
Write the natural log of the value in the mapped column conc in the new column.
abs: Return the absolute value of the supplied parameter.
abs(–12)=12;
abs(ColumnA)
acos: Return the inverse cosine of parameter1.
acos(–0.5)=2.094395
acosh: Calculate the inverse hyperbolic cosine of parameter1.
acosh(10)=2.99322
asin: Calculate the inverse sine of parameter1.
asin(–0.5)= –0.5236
asinh: Calculate the inverse hyperbolic sine of parameter1.
asinh(–2.5)= –1.6472311
atan: Calculate the inverse tangent of parameter1.
atan(1)=0.785398
atan2: Calculate the arc tangent of two coordinates.
atan2(x coord, y coord);
atan2(–1,1)= –2.3561945
atanh: Calculate the inverse hyperbolic tangent of a value.
atanh(0.76159416)=1
average: Return the average of the supplied parameters.
average(A1:A10);
average(12, 5, 7, 8, 11.4)
ceiling: Return the smallest integer value greater than, but not equal to, the value supplied.
ceiling(12.5)
chiinv: Calculate the inverse chi-square distribution
chiinv(p, df)
clean: Clean text of non-printable characters.
clean(“string with non-printable chars”);
clean(A1)
concatenate: Combine two or more strings into one string.
concatenate(A1:A5);
concatenate(ColumnA, “& “, ColumnB)
cos: Calculate the cosine of parameter1.
cos(1.047)=0.50017107
cosh: Calculate the hyperbolic cosine of parameter1.
cosh(4)=27.30823
count: Return the number of values in a list, including empty cells.
count(A1:A5)
counta: Return the number of non-empty cells in a given range.
counta(A1:A5)
date: Return the sequential serial number that represents a particular date.
date(2007, 11, 27)=39411
datevalue: Use datetime to return the serial number of the date represented by date_text. Use this function to convert a date represented by text to a serial number. If time is associated with the date, then the serial date will be a decimal and not a whole number.
datevalue(“11/27/2017”)=39411
day: Return the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.
day(serial_time);
day(39411)=27
even: Return the smallest even integer greater than the argument.
even(12.1)
exp: Return e raised to the power of the supplied parameter.
exp(3);
exp(ColumnA)
fact: Return the factorial of the supplied parameter.
fact(3)
Finv: Calculates the quantile of F-distribution and returns the absolute value.
Finv(p,df1,df2);
Finv(0.7, 1, 10) = 1.194776
floor: Return the largest integer less than the supplied parameter.
floor(12.9)
format: Format a string using .NET string formatting commands.
format(ColumnA, “G8”);
format(“.12345”, “G8”)
geomean: Return the geometric mean of a range of positive data.
geomean(A1:A15)
geovar: Return the geometric variance of a range of positive data.
geovar(A1:A15)
harmean: Return the harmonic mean of a range of data.
harmean(A1:B32)
harmvar: Return the harmonic variance of a range of data.
harvar(A1:C33)
hour: Return the hour of a time value. The hour is given as an integer, ranging from zero (12:00 A.M.) to 23 (11:00 P.M.).
hour(serial_time);
hour(38711.06302)=1;
hour(37811.27135)=6
if: Return true if conditional argument is true; false otherwise.The if function also supports using & (and) and | (or) in statements, and supports nested if statements.
Note: parameter 1 (logical test) must return true or false; place a space between cell reference and '='
if((Time=1) & (Conc>2),…);
if((Time=1) | (Conc>2),…);
if(trt=”Reference”, “R”, “T”);
if(CVWR<=30, ‘125.00’,
if(CVWR<=50, 100*exp(+0.76*sqrt(Estimate)), 143.19))
if((A1=0.25), 1, 0)
IsNull: Return true if value of cell is NULL (empty).
IsNull(ColumnA)
left: Return the specified number of characters (from left to right) of a string.
left(“This is a test”, 1)=T;
left(“This is a test”, 3)=Thi; left(A1, 3)
length: Return the length of a string.
length(“this is a test”)=14;
length(A1)
ln: Return the natural log of the supplied parameter.
ln(2.3);
ln(ColumnA)
log: Return the log of the first parameter to the base of the second parameter.
log(number, base);
log(5,3)
log10: Return the log base 10 of the parameter.
log10(5);
log10(ColumnA)
lower: Return the supplied string as lower case.
lower(“This Is A Test”)=“this is a test”;
lower(A1)
mid: Return a specific number of characters from a string from a given position and length parameters.
mid(text, start pos, distance);
mid(“This is a test”, 2, 3)=his
minute: Return the minutes of a time value. The minute is given as an integer, ranging from zero to 59.
minute(serial_time);
minute(38711.06302)=30
mod: Return the remainder after parameter1 is divided by parameter2.
mod(number, divisor);
mod(12,2)
month: Return the month of a date represented by a serial number.
The month is given as an integer, ranging from 1 (January) to 12 (December).
month(serial_time);
month(39411)=11
now: Return the current date and time as a double (serial date/time).
now()=current date
odd: Return the smallest odd integer greater than the supplied parameter.
odd(13.1)
pi: Return the value of pi.
pi()
product: Return the product of a list of real numbers.
product(A1:Z88)
rand: Return a random number based on a lower limit, an upper limit, and a seed.
rand(,);
rand(0,1,92756)
replace: Replace a sub-string with another string.
replace(text, start pos, distance, replace with);
replace(“T_%_025”, 2, 3, “–”)=T–025;
replace(ColumnA,1,4,””)
right: Return a specific number of characters from a string starting from the right and moving left.
right(“This is a test”, 3)=“est”
root: Return the root of a parameter1, using parameter2/parameter3.
x^(a/b);
root(x, a, b);
root(2, 3, 4)
round: Round parameter1 to the number of decimal places defined by parameter2.
round(12.345, 2)=12.35;
round(ColumnA,1)
roundsig: Round parameter1 to the number of significant digits defined by parameter2. Note that Phoenix worksheets do not support displaying trailing zeros in decimals, so not all significant digits will be displayed in this case.
roundsig(12.234, 3)=12.3
search: Return the one-based starting position of a substring within a larger string. The third argument is the one-based starting position for the search. The function returns –1 if there is no match.
search(“Phoenix”, “oe”, 1)=3
second: Return the seconds of a time value. The second is given as an integer in the range zero to 59.
second(38711.06302)=44
sign: Determine the sign of a number. Returns 1 if the number is positive, 0 if the number is zero, or –1 if the number is negative.
sign(–12);
sign(0);
sign(200)
sin: Calculate the sine of parameter1.
sin(1)=0.84147098
sinh: Calculate the hyperbolic sine of parameter1.
sinh(1)=1.175201194
sqrt: Return the square root of parameter1.
sqrt(9)
stdev: Estimate standard deviation based on a range of data.
stdev(A1:C1)
strcmp: An integer indicating the case sensitive lexical relationship between the two comparands.
Value condition < zero, stringA is < stringB
Value condition = zero, stringA = stringB
Value condition > zero, stringA is > stringB
strcmp(stringA, stringB)
substitute: Substitute new text for old text in a text string and optionally [for a given instance]. [] is optional but the preceding comma is required; substitute(ColumnA,”_%_”,””,)
substitute(text, target text, replacement text, [instance])
sum: Return the sum of the specified values.
sum(A1:A12);
sum(1,2,3,4,5)
sumsq: Return the sum of the squares of the arguments.
sumsq(C1:C33)
tan: Calculate the tangent of parameter1.
tan(0.785)=0.99920399
tanh: Calculate the hyperbolic tangent of parameter1.
tanh(–2)= –0.96403
time: Return the decimal number for a particular time.
time(hour, min, sec);
time(12,2,3)=0.50142361
timevalue: Use datetimes to return the decimal number of the time represented by a text string, with precision up to the second.
timevalue(“2:24 AM”)=0.1
tinv: Calculate the inverse t distribution and returns absolute value.
tinv(p, df);
tinv(0.7, 10)=tinv(0.3, 10)=0.541528
today: Return the serial number of the current date.
today()=current date as serial time
trim: Remove all spaces from text except for single spaces between words.
trim(“ this is a test”)=“this is a test”
trunc: Truncate a real number to an integer by removing the decimal places.
trunc(number);
trunc(12.1234)
upper: Return parameter1 in upper case.
upper(“this is a test”)=“THIS IS A TEST”
var: Return the variance of a set of real numbers.
var(A1:A5)
weekday: Return the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday).
weekday(serial_time);
weekday(39411)=Monday (Sunday is the first day)
year: Return the year corresponding to a date. The year is returned as an integer in the range 1900–9999.
year(39411)=2007
