3.41 mpaste - Match and Merge Fields from Reference File

Merge input file with the reference file for matching rows. If data is different in size, merge with data with smaller size. It is also possible to match all data with different sizes with the inclusion of null values by specifying -n and -N.

Format

mpaste [f=] -n -N m=| i= [o=] [-nfn] [-nfno] [-x] [--help] [--version]

Parameters

`f=`	field name(s) (multiple fields can be specified) to merge with reference file.
	All fields except the key field are merged.
`m=`	Reference file name.
	Read from standard input when this parameter is noted defined (when `i=` is specified).
`-n`	Output NULL values when input data does not consist of reference data.
`-N`	Output NULL values when reference data does not consist of input data.

Examples

Example 1: Basic Example

$ more dat1.csv
id1
1
2
3
4
$ more ref1.csv
id2
a
b
c
d
$ mpaste m=ref1.csv i=dat1.csv o=rsl1.csv
#END# kgpaste i=dat1.csv m=ref1.csv o=rsl1.csv
$ more rsl1.csv
id1,id2
1,a
2,b
3,c
4,d

Example 2: Example of merging data of different sizes

If the number of rows in the input file is different from the reference file , merge records according to the smaller file.

$ more ref2.csv
id2
a
b
$ mpaste m=ref2.csv i=dat1.csv o=rsl2.csv
#END# kgpaste i=dat1.csv m=ref2.csv o=rsl2.csv
$ more rsl2.csv
id1,id2
1,a
2,b

Example 3: Outer join

If there are less number of rows in the reference file, NULL values will be assigned to records that did not match with the input file when -n option is specified.

$ mpaste m=ref2.csv -n i=dat1.csv o=rsl3.csv
#END# kgpaste -n i=dat1.csv m=ref2.csv o=rsl3.csv
$ more rsl3.csv
id1,id2
1,a
2,b
3,
4,

Example 4: Define fields to join

$ more ref3.csv
id2,val
a,R0
b,R1
c,R2
d,R3
$ mpaste f=val m=ref3.csv i=dat1.csv o=rsl4.csv
#END# kgpaste f=val i=dat1.csv m=ref3.csv o=rsl4.csv
$ more rsl4.csv
id1,val
1,R0
2,R1
3,R2
4,R3

Related Command

mjoin : Join using key field(s) if row numbers are not present.