Derive

Format: Der[ive] [column [expression]]

This is a powerful command which allows you to derive the values in one column from the values in others. One column can be set to be the total of three others, the maximum, the product, or can be produced by an arbitrarily complicated arithmetic and logical expression. In addition two special options are provided to make one column the rank of the values in another, or to be the normalised version of those values i.e. with the column mean subtracted and divided by the column standard deviation.

The command can also be applied to variables and indeed is the only way that a variable can be set to a given value.

In the format set out above "expression" can be an arithmetic and/or logical expression like (c3+c4)/2 or it can be one of these two special functions of a column, ZED or RANK:

ZED column: - (column - mean)/standard deviation
RANK column: - rank of the values in given the column

Examples:

Enter command -  der c3 c4-c5

This sets the values in column 3 to be those in column 4 minus those in column 5.

Enter command -  DERIVE 
Enter column to derive:   c3 
Enter formula for new values:   c4+c5

If you don't supply the column and expression on the command line then EASISTAT will ask for them. Here is third column becomes the sum of the fourth and fifth.

Enter command -  der c6 zed c5

Values in column 5 have the column mean subtracted and are then divided by the standard deviation so that the values in column 6 will now have mean of zero and standard deviation of one.

Enter command -  der c6 rank c5

Now the values in column 6 are derived from the ranks of those in column 5, so that the row containing the lowest value in column 5 will have a 1 in column 6, the next lowest a 2, and so on.

Enter command -  der v3 arccos(-1)+v2

The third general purpose variable is set to the value of the second added to pi (the arccosine in radians of -1).

Enter command -  der v3 c4

Now the third variable takes the value from the fourth column of the first valid row. When the DERIVE command is applied to a column, it is applied repeatedly to that column for every row of the data table. However when it is applied to a variable then any references to columns are taken to apply to the first valid row of the table. (Valid means as selected by the NARROW and WIDEN commands. This feature is similar to the usage of the ARITHMETIC command, where again column references apply to the first valid row.)

Advanced tips

Enter command - der c6 (c3*c4*c5) pow (1/3)

This makes column 6 the geometric mean of the previous three columns.

Enter command -  der c6 c3*(c3>c4)+c4*(c4>=c3) 
Enter command -  der c6 c5*(c5>c6)+c6*(c6>=c5)

These two lines would make column 6 the maximum of the previous three (if you don't see why refer back to the section on "Combining arithmetic and logical expressions").

Enter command -  der c6 ln(c5/(1-c5))

Here column 6 is derived from column 5 by the "logit" transformation which statisticians sometimes use.

If you have one hundred rows of data the following lines will produce a table showing the probability value for Student's t with 5 degrees of freedom over a range from 0 to 10:

Enter command -  new c1
Enter command -  der c1 row/10
Enter command -  fo c1 4 1
Enter command -  new c2
Enter command -  der c2 5
Enter command -  new c3
Enter command -  fo c3 7 5
Enter command -  der c3 c1 pt c2

Three new columns are created. The first consists of a series of numbers ascending from 0.1 to 10.0 (assuming there are 100 rows) in steps of 0.1. The second column is set to all 5's. The third is derived from the first two using the PT function. The first column is formatted to display one decimal place, and the third to display five.

The DERIVE command can be used to put data into classes to make further manipulation easier. Suppose that column 3 contains values for age, then we can make column 4 contain a coding for up to 15, 16-25, 26-35, 36-45, 46-55 and 56+ like this:

Enter command -  der c4 1*(c3<=15) + 2*(c3>15&c3<=25) 
Enter command -  der c4 c4 + 3*(c3>25&c3<=35) 
Enter command -  der c4 c4 + 4*(c3>35&c3<=45) 
Enter command -  der c4 c4 + 5*(c3>45&c3<=55) + 6*(c3>55)

Again, referring to the section on "Combining arithmetic and logical operators" may be helpful if you do not understand this. Note the necessity to include column 4 itself in the second, third and fourth expressions, so as not to lose the result of the first one.

The same effect can be achieved with the following sequence of NARROW and WIDEN commands. More commands are needed, but it may be clearer to see what is going on:

Enter command -  narrow (c3<=15)
Enter command -  der c4 1
Enter command -  widen
Enter command -  narrow (c3>15&c3<=25)
Enter command -  der c4 2
Enter command -  widen
Enter command -  narrow (c3>25&c3<=35)
Enter command -  der c4 3
Enter command -  widen
Enter command -  narrow (c3>35&c3<=45)
Enter command -  der c4 4
Enter command -  widen
Enter command -  narrow (c3>45&c3<=55)
Enter command -  der c4 5
Enter command -  widen
Enter command -  narrow (c3>55)
Enter command -  der c4 6
Enter command -  widen

It is possible to number the data rows as follows:

Enter command - new c1 rnum 
Enter command - derive rnum row

A new first column is created called RNUM, and is filled with the value of each row of the table.