CODEWITHSUNDEEP
bashshelljoinawk
Veljko
Veljko

Reputation: 1808

Shell script - join two files using the AWK on specific column

I have first file test1.csv separated with pipes and one tab always:

ug|s|B|city|bg1|1|8D|ON-05|100  10|28|288
ug|s|B|city|bg1|1|D9|ON-05|150  11|28|288
ug|s|B|city|bg2|2|94|ON-05|350  12|28|288

I have second file test2.csv only with one tab:

bg1 250
bg2 350

I want to join them using the column number 5 from the first file (bg1,bg2) so I can take the values from second file (250,350)

So the final output should be:

ug|s|B|city|bg1|1|8D|ON-05|100|250  10|28|288
ug|s|B|city|bg1|1|D9|ON-05|150|250  11|28|288
ug|s|B|city|bg2|2|94|ON-05|350|350  12|28|288

I tried using the AWK to join them:

awk -F '|' -v OFS='|' '
    FNR==NR {  } {
        split($NF, b, "\t")
        $NF = b[1] FS a[b[1]] "\t" b[2]
    } 1' test2.csv test1.csv > final.csv

but not getting correct results

Upvotes: 1

Views: 70

Answers (3)

stack0114106
stack0114106

Reputation: 8711

You can do this with Perl also.

$ cat test2.csv
bg1     250
bg2     350

$ cat test1.csv
ug|s|B|city|bg1|1|8D|ON-05|100  10|28|288
ug|s|B|city|bg1|1|D9|ON-05|150  11|28|288
ug|s|B|city|bg2|2|94|ON-05|350  12|28|288

$ perl -F'\t' -lane 'if($ARGV eq "test2.csv") { $kv{$F[0]}=$F[1]}; if( $ARGV eq "test1.csv" ) { ($x,$y)=(split(/\|/,$F[0]))[4,8]; if($kv{$x}) {$F[0]=~s/$y$/$kv{$x}/g } pr
int "$F[0]\t$F[1]" } ' test2.csv  test1.csv
ug|s|B|city|bg1|1|8D|ON-05|250  10|28|288
ug|s|B|city|bg1|1|D9|ON-05|250  11|28|288
ug|s|B|city|bg2|2|94|ON-05|350  12|28|288

$

Upvotes: 0

Ed Morton
Ed Morton

Reputation: 203219

awk '
    BEGIN { FS=OFS="\t"; subFs="|" }
    NR==FNR { map[$1]=$2; next }
    { split($1,subFlds,subFs); print $1 subFs map[subFlds[5]], $2 }
' test2.csv test1.csv
ug|s|B|city|bg1|1|8D|ON-05|100|250      10|28|288
ug|s|B|city|bg1|1|D9|ON-05|150|250      11|28|288
ug|s|B|city|bg2|2|94|ON-05|350|350      12|28|288

Upvotes: 2

William Pursell
William Pursell

Reputation: 212218

This is the perfect use case for my general proposition that -v should be avoided in favor of direct assignment on the command line. You can easily switch FS between files and do:

$ awk 'NR==FNR{f[$1]=$2; next} {print $0, f[$5]}' test2.csv FS=\| OFS=\| test1.csv
ug|s|B|city|bg1|1|8D|ON-05|100  10|28|288|250
ug|s|B|city|bg1|1|D9|ON-05|150  11|28|288|250
ug|s|B|city|bg2|2|94|ON-05|350  12|28|288|350

This doesn't give you exactly the output you desire, but that's easy enough to fix, albeit a little awkward:

awk 'NR==FNR{f[$1]=$2; next} {split($9,a,"\t"); \
    $9=a[1] "|" f[$5] "\t" a[2]}1' test2.csv FS=\| OFS=\| test1.csv

Upvotes: 1

Related Questions