3.8 mchgnum - Substitute Values within Numerical Range

The field name for encoding is specified at f= parameter, number and range criteria is specified at the R= parameter, the substitution string specified in the v= parameter replaces the value in the defined field.

Format

mchgnum f= R= [O=|-F] [v=] [-A] [-r] [i=] [o=] [-nfn] [-nfno] [-x] [--help] [--version]

Parameters

f=

Replace the specified field (multiple fields can be specified) according to the replacement string list

 

at v= parameter and the numerical ranges list at R= parameter.

R=

Specify the numerical range to be replaced (multiple fields can be specified)

 

(1.1,2.5 : more than 1.1 and less than 2.5).

 

Use MIN for minimum value, MAX for maximum value ( MIN, 2.5 : 2.5 or less).

O=

Out of range character strings

 

Specify the replacement string when values fall outside the numeric range list at the

 

R= parameter (returns NULL values in output when this parameter is not specified).

-F

Display out of range values in the column.

 

Retain the out of range values in the output even though the values fall outside the

 

specified numeric range defined in the R= parameter.

v=

Specify the replacement character string corresponding to the numerical range in the R= parameter.

 

More than 1 argument must be defined at R=.

-A

This option adds output to a new column instead of replacing the specified item.

-r

The range defined at R= parameter deals with ’ greater than  less than’.

 

For example, 1.1_2.5 represents "greater than 1.1 < less than 2.5".

Examples

Example 1: Basic Example

Encodes the numeric values in quantity column to character strings where values of less than but not equal to 10 are treated as low, 10 or more but less than 20 are treated as middle, values of 20 or more is treated as high.

$ more dat1.csv
customer,quantity
A,5
B,10
C,15
D,2
E,50
$ mchgnum f=quantity R=MIN,10,20,MAX v=low,middle,high i=dat1.csv o=rsl1.csv
#END# kgchgnum R=MIN,10,20,MAX f=quantity i=dat1.csv o=rsl1.csv v=low,middle,high
$ more rsl1.csv
customer,quantity
A,low
B,middle
C,middle
D,low
E,high

Example 2: Equal to paramter range

Replace the numeric values in quantity column to character strings where 10 or below is treated as low, more than 10 but less than or equal to 20 is treated as middle, more than 20 is treated as high.

$ mchgnum f=quantity R=MIN,10,20,MAX v=low,middle,high -r i=dat1.csv o=rsl2.csv
#END# kgchgnum -r R=MIN,10,20,MAX f=quantity i=dat1.csv o=rsl2.csv v=low,middle,high
$ more rsl2.csv
customer,quantity
A,low
B,low
C,middle
D,low
E,high

Example 3: Replace values out of the list of numeric range

Replace numeric values in quantity column to character strings where 10 or above and less than 20 is coded as low, 20 or above and less than 30 is coded as middle, 30 or more is coded as high, values that are less than 10 is coded as out of range.

$ mchgnum f=quantity R=10,20,30,MAX v=low,middle,high O="out of range" i=dat1.csv o=rsl3.csv
#END# kgchgnum O=out of range R=10,20,30,MAX f=quantity i=dat1.csv o=rsl3.csv v=low,middle,high
$ more rsl3.csv
customer,quantity
A,out of range
B,low
C,low
D,out of range
E,high

Example 4: Add a new column

Replace the numeric values in quantity column to character strings where values less than 10 is treated as low, 10 or above but less than 20 is treated as middle, 20 or above is treated as high. Store the output of replacement strings in a new column as evaluate.

$ mchgnum f=quantity:evaluate R=MIN,10,20,MAX v=low,middle,high -A i=dat1.csv o=rsl4.csv
#END# kgchgnum -A R=MIN,10,20,MAX f=quantity:evaluate i=dat1.csv o=rsl4.csv v=low,middle,high
$ more rsl4.csv
customer,quantity,evaluate
A,5,low
B,10,middle
C,15,middle
D,2,low
E,50,high

Example 5: Display original values in column if out of defined range

Replace the numeric values in quantity column to character strings where values of 10 or above but less than 20 is coded as low, 20 or above but less than 30 is coded as middle, 30 or above is coded as high. Retain original values in the output if the value is less than 10.

$ mchgnum f=quantity R=10,20,30,MAX v=low,middle,high -F i=dat1.csv o=rsl5.csv
#END# kgchgnum -F R=10,20,30,MAX f=quantity i=dat1.csv o=rsl5.csv v=low,middle,high
$ more rsl5.csv
customer,quantity
A,5
B,low
C,low
D,2
E,high

Related Commands

mchgstr : Substitute string.

msed : Replace text with regular expressions.