join csv with many fields without disturbing order

Question

I have 2 csv files. The first file is big (>400 fields and many rows > 1 mil) and needs to have another field appended to it, via a matched join.

I want to join on a field $170

I have tried

gawk 'BEGIN {OFS=FS=","} NR==FNR{b[$1]=$2; next} 
$170 in b {print $0,b[$170]}
' b a

This works ok but struggles when file size increases, according to Why isn't this awk command doing a full outer join? (see answer by @cuonglm)

I have not tested this, but want to know the 'best' method as file sizes increase.

@cuonglm suggests using join, but this re-arranges the columns to put the joined field first.
I cannot easily write a long output format for the join command using the -o argument because this would be very long:

join -1 170 -2 1 -o1.1 1.2 1.3 1.4......1.300.... file1 file2

Is there a way to get join to do this more easily?

Or should I just stick with gawk, as the file size issue (max 5 mil rows in both files a and b, both with about 500 columns, for example)?

Answer 1

I carried out a full-scale gawk test. I made a CSV of 5 million lines by 500 columns (20 GB), and a side file of 5 million lines by 2 columns. The key fields are unique (I had five million prime numbers hanging around) and were in column 170 of the big file and column 1 of the side file. Both the files contained the keys in independent random orders. All the other fields contained random choices from about 14000 words (ripped from man pages).

The awk script ran for almost 20 minutes and used about 0.8 GB of memory throughout. That's on a 4 GB Laptop and a 5400 rpm HDD. This log shows the times and file sizes, and the number of columns.

Paul--) time ./datMerge

real    18m31.740s
user    10m21.632s
sys 1m48.316s
Paul--) wc -lc *max*
    5061456 20045559105 FileA.max.csv
    5061456    85634275 FileB.max.csv
    5061456 20085640276 FileC.max.csv
   15184368 40216833656 total
Paul--) for f in F*max*; do
> awk '-F,' '{ printf ("%8d %s\n", NF, FILENAME); }' "${f}"
> done | uniq -c
5061456      500 FileA.max.csv
5061456        2 FileB.max.csv
5061456      501 FileC.max.csv
Paul--) ls -l F*max*
-rw-r--r-- 1 paul paul 20045559105 Feb  8 19:49 FileA.max.csv
-rw-r--r-- 1 paul paul    85634275 Feb  8 19:49 FileB.max.csv
-rw-r--r-- 1 paul paul 20085640276 Feb  8 20:24 FileC.max.csv
Paul--) 

I made a mini version of the files to show what it does, six lines of six columns with the key in col 4.

Paul--) head F*mini*
==> FileA.mini.csv <==
rather,complies,finite,99999847,AM,Or
elapsed,plied,nearperfect,99999989,pr,WinChip
phiopt,reflects,bottom,99999959,128N,careful
fpackstructn,realworld,msingleexit,99999931,0xffff,maxmimum
simplified,mpopcntb,FrontCover,99999971,523x,requisite
cede,fdumprtlbtl2,atmega649a,99999941,LC_MESSAGES,errno

==> FileB.mini.csv <==
99999847,symbols
99999931,fdumprtldbr
99999959,ambiguous
99999971,crc
99999989,munsafedma

==> FileC.mini.csv <==
rather,complies,finite,99999847,AM,Or,symbols
elapsed,plied,nearperfect,99999989,pr,WinChip,munsafedma
phiopt,reflects,bottom,99999959,128N,careful,ambiguous
fpackstructn,realworld,msingleexit,99999931,0xffff,maxmimum,fdumprtldbr
simplified,mpopcntb,FrontCover,99999971,523x,requisite,crc
cede,fdumprtlbtl2,atmega649a,99999941,LC_MESSAGES,errno,Default
Paul--) 

This is the merge script. I can post the data creation script if that would be informative.

Paul--) cat datMerge
#! /bin/bash
#: datMerge

LC_ALL="C"

function Merge {

    local AWK='''
BEGIN { FS = ","; OFS = ","; K = 170; Null = "Default"; }
NR == FNR { htMap[$1] = $2; next; }
{ printf ("%s%s%s\n", $0, OFS, ($(K) in htMap) ? htMap[$(K)] : Null); }
'''
    awk "${AWK}" "${@}"
}

    Merge "FileB.max.csv" "FileA.max.csv" > "FileC.max.csv"

Paul--)