Compare specific columns across multiple files and print matched specific column-CodePudding

I have multiple files (six files) in csv format. I am trying to compare $3,$4,$5 across multiple files and if match print $6 from all files along with column $2,$3,$4,$5 from file 1.

Input file 1:

Blink,Seeddensity(g/cm^3),1_0002,VU10,37586764,0.458533399568206
Blink,Seeddensity(g/cm^3),1_0004,VU08,37687622,0.548181169267479
Blink,Seeddensity(g/cm^3),1_0006,VU02,6629660,0.553099787284982

Input file 2:

Farmcpu,Seeddensity(g/cm^3),1_0002,VU10,37586764,0.907010463957269
Farmcpu,Seeddensity(g/cm^3),1_0004,VU08,37687622,0.782521980037194
Farmcpu,Seeddensity(g/cm^3),1_0006,VU02,6629660,0.589126094555234

Input file 3:

GLM,Seeddensity(g/cm^3),1_0002,VU10,37586764,0.24089
GLM,Seeddensity(g/cm^3),1_0004,VU08,37687622,0.25771
GLM,Seeddensity(g/cm^3),1_0006,VU02,6629660,0.31282

Desired output:

Trait   Marker  Chr Pos Blink   Farmcpu GLM
Seeddensity(g/cm^3) 2_27144 VU08    36984438    1.7853934213866E-11 0.907010463957269   0.24089
Seeddensity(g/cm^3) 2_13819 VU08    21705264    3.98653459293212E-09    0.782521980037194   0.25771
Seeddensity(g/cm^3) 2_07286 VU01    38953729    3.16663946775461E-07    0.589126094555234   0.31282

I have checked multiple awk commands but this is the closest one do a job across two files:

awk 'NR==FNR{ a[$2,$3,$4,$5]=$1; next } { s=SUBSEP; k=$2 s $3 s $4 s $5 }k in a{ print $0,a[k] }' File1 File2 > output

join <(sort File1) <(sort File2) | join - <(sort File3) | join - <(sort File4) | join - <(sort File5) | join - <(sort File6) > output

I believe join is not working as first column is not same across files so I tried this command:

join -t, -j3 -o 1.2,1.3,1.4,1.5,1.6,2.6,3.6,4.6,5.6,6.6 <(sort -k 3 File1) <(sort -k 3 File2) <(sort -k 3 File3) <(sort -k 3 File4) <(sort -k 3 File5) <(sort -k 3 File6) > output

But I am getting an error msg: join: invalid file number in field spec: ‘3.6’

For two files the following command works, but I am not sure how to use it for multiple files:

join -t, -j3 -o 1.2,1.3,1.4,1.5,1.6,2.6 <(sort -k 3 File1) <(sort -k 3 File2) > output

CodePudding user response：

Assuming you actually want CSV output then using GNU awk for ARGIND:

$ cat tst.awk
BEGIN { FS=OFS="," }
{ key = $3 FS $4 FS $5 }
ARGIND < (ARGC-1) {
    val[key,ARGIND] = $6
    next
}
{
    sfx = ""
    for (i=1; i<ARGIND; i  ) {
        if ( (key,i) in val ) {
            sfx = sfx OFS val[key,i]
        }
        else {
            next
        }
    }
    print $2, $3, $4, $5, $6 sfx
}

$ awk -f tst.awk file2 file3 file1
Seeddensity(g/cm^3),1_0002,VU10,37586764,0.458533399568206,0.907010463957269,0.24089
Seeddensity(g/cm^3),1_0004,VU08,37687622,0.548181169267479,0.782521980037194,0.25771
Seeddensity(g/cm^3),1_0006,VU02,6629660,0.553099787284982,0.589126094555234,0.31282

With any other awk just add a line that's FNR==1 { ARGIND } at the start of the script.