crossmatching

[krava]

Hi,

I would like to cross-match 2 files with awk using different columns. Example:

file1 with 3 CSV columns:

A1,B1,C1
1,2,3
4,5,6
7,8,9


file2 similary:
A2,B2,C2
2,11,13
7,34,56
8,32,12

Matching 2 files using B1 column of the first file and A2 column of the second should give:



A1,B2,C1,A2,B2,C2
1,2,3,2,11,13
7,8,9,8,32,12



It is important to keep headerof these files in the resulting file as shown in the above example.
Could anybody help me with this?


tkanks
k.

 

[PHV]

What have you tried so far and where in your code are code are you stuck ?
Tip: test NR against FNR to populate an associative array.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[krava]

I wrote awk for matching of 2 files using the same columns like this:

#USAGE: awkcrossmatch file1 file2 2 output
test_awkcrossmatch() {
awk -F, -v col1="$3" 'NR==1 { h=$0 ; next}
{v=$col1} NR==FNR {a[v]=$0; print v, a[v]; next}
NR==1 { print h FS $0 ; print v; next }
v in a {print a[v] FS $0}' OFS="," $1 $2 > $4
}


Now I would like to modify this to be able to crossmatch like in example given above but stucked ...

 

[PHV]

awk -F, -v col1=2 -v col2=1 'NR==1 { h=$0 ; next}
NR==FNR {v=$col1;a[v]=$0; print v, a[v]; next}
{v=$col2}
FNR==1 { print h FS $0 ; print v; next }
v in a {print a[v] FS $0}'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[krava]

Thanks,


I am trying to generalise this that is to match 2 files usin 2 columns in both files. For example to match 1 and 2 columns of file1 with columns 3 and 4 of file2

#USAGE:test_awkcrossmatch file1 1 2 file2 3 4 output
test_awkcrossmatch() {
awk -F, -v col1_fl1="$2" -v col2_fl1="$3"
-v col1_fl2="$5" -v col1_fl2="$6"
'NR==1 { h=$0; next}
NR==FNR {v=$col1_fl1 SUBSEP $col2_fl1;a[v]=$0;next}
{v=$col1_fl2 SUBSEP $col2_fl2}
FNR==1 { print h FS $0 ;
next }
v in a {print a[v] FS $0}' OFS="," $1 $4 > $7


I do misstake but where?