3.63 mtrafld - Convert Transaction Field to Cross (pivot) Table

Create item pairs from the fields specified at f=, concatenate the item pairs and save as a new vector field (also referred to as transaction field).

Format

mtrafld a= [f=] [delim=] [delim2=] [-r] [-valOnly] [i=] [o=] [-nfn] [-nfno] [-x] [--help] [--version]

Parameters

a=

Specify the transaction field name.

f=

List of field name(s) (Multiple fields can be specified) [required when -r is specified,

 

otherwise, this parameter is optional]

 

The field names specified here will be created as connected items and saved in the transaction field.

 

When -r option is specified, specify the field name to extract from the transaction data.

 

This parameter is optional when -r option is specified.

 

If the parameter is not specified, all field names are processed as value pairs.

 

However, whenf= is not defined, this command cannot read standard input (using pipe).

delim=

Specify the character to separate each transaction field item (default delimiter:

 

space if this parameter is not defined).

delim2=

Specify the character to separate value pairs and field name (default character: =).

-r

Reverse conversion

 

Convert transaction field to cross table.

-valOnly

When this option is specified, the item do not return the prefix "field name=" in the output.

Examples

Example 1: Basic Example

Join the fields price and quantity to a string, rename output field as transaction.

$ more dat1.csv
customer,price,quantity
A,198,1
B,325,2
C,200,3
D,450,2
E,100,1
$ mtrafld a=transaction f=price,quantity i=dat1.csv o=rsl1.csv
#END# kgtrafld a=transaction f=price,quantity i=dat1.csv o=rsl1.csv
$ more rsl1.csv
customer,transaction
A,price=198 quantity=1
B,price=325 quantity=2
C,price=200 quantity=3
D,price=450 quantity=2
E,price=100 quantity=1

Example 2: Basic Example 2

Use -r option to revert the output results back to the original data.

$ mtrafld -r a=transaction f=price,quantity i=rsl1.csv o=rsl2.csv
#END# kgtrafld -r a=transaction f=price,quantity i=rsl1.csv o=rsl2.csv
$ more rsl2.csv
customer,price,quantity
A,198,1
B,325,2
C,200,3
D,450,2
E,100,1

Example 3: Specify the delimiter

Price and quantity field is separated by “_” (underscore) character and connected by 1 character string. Use colon and name the output field as transaction.

$ mtrafld a=transaction f=price,quantity delim=_ delim2=':' i=dat1.csv o=rsl3.csv
#END# kgtrafld a=transaction delim2=: delim=_ f=price,quantity i=dat1.csv o=rsl3.csv
$ more rsl3.csv
customer,transaction
A,price:198_quantity:1
B,price:325_quantity:2
C,price:200_quantity:3
D,price:450_quantity:2
E,price:100_quantity:1

Example 4: When data contains NULL value

$ more dat2.csv
customer,price,quantity
A,198,1
B,,2
C,200,
D,450,2
E,,
$ mtrafld a=transaction f=price,quantity i=dat2.csv o=rsl4.csv
#END# kgtrafld a=transaction f=price,quantity i=dat2.csv o=rsl4.csv
$ more rsl4.csv
customer,transaction
A,price=198 quantity=1
B,quantity=2
C,price=200
D,price=450 quantity=2
E,

Example 5: When data contains NULL value 2

Use -r option to revert the output results back to the original data.

$ mtrafld -r a=transaction f=price,quantity i=rsl4.csv o=rsl5.csv
#END# kgtrafld -r a=transaction f=price,quantity i=rsl4.csv o=rsl5.csv
$ more rsl5.csv
customer,price,quantity
A,198,1
B,,2
C,200,
D,450,2
E,,

Example 6: Specify -valOnly option

$ mtrafld -valOnly f=price,quantity a=transaction i=dat2.csv o=rsl6.csv
#END# kgtrafld -valOnly a=transaction f=price,quantity i=dat2.csv o=rsl6.csv
$ more rsl6.csv
customer,transaction
A,198 1
B,2
C,200
D,450 2
E,

Related Commands

mvsort : Vector based transaction data can be processed by a set of commands (with mv as prefix) which handles vector data.

mcross : Rather than converting as transaction data, every item is saved separately as individual field in the output.

mtra : Create transaction data using values in the field.

mtraflg : Create transaction data with field names.