0

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)?

10
  • Are they tab-separated or comma-separated?
    – Inian
    Feb 7, 2020 at 6:14
  • It is csv, but that’s not so important
    – Tim
    Feb 7, 2020 at 6:59
  • can you show us the format of the second file? given the files are csv are there any extraneous commas (e.g. within quotes) or do commas definitively demarcate fields?
    – gogoud
    Feb 7, 2020 at 7:21
  • Several things wrong with your gawk code. (a) It does not specify a FS -- it splits on whitespace. (b) It does not insert an OFS before the new field. (c) It does not output unmatched lines at all. (d) When you fix that, it needs a default field to maintain the field count. Feb 7, 2020 at 11:15
  • The performance should be linear with the input file size. I would expect gawk to do this on my Laptop at about 10,000 lines a second, so about 2 minutes per million rows. What did you expect, what are you getting? A side index of a million entries should be no bother at all, and is also not sensitive to data volume. Can you quantify "struggles". Memory, CPU, paging, I/O? What kind of system is this on? Feb 7, 2020 at 11:22

1 Answer 1

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--) 
2
  • Slower than I expected - around 4,500 lines per second. But then 500 columns and an average line of 4000 characters are a little more than usual. Feb 8, 2020 at 22:07
  • Going back to the sort/merge techniques I needed to use on my 48 KB "mainframe" pre-1972, I have come up with a dataflow that incurs no memory restrictions regardless of file sizes or number of fields. It requires three extracts, three sorts (one of them of the full side file), and two serial merges. I have implemented enough of it to know it will work (the run-time and workspace to sort a 20 GB file is unknown). Feb 9, 2020 at 18:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.