0

I have a 33GB pipe-delimited flat file.

I need to extract specific columns from the file, from lines of which the first and 20th column meets a condition.

I used the below code to process the input file.

awk -F"|" '('$1~/^BL|^FR|^GF|^GP|^MC|^MF|^MQ|^NC|^PF|^PM|^RE|^TF|^WF|^YT/&&$20=="TRUE"') {print $0}' <input file> | cut -d'|' -f1-3,6,10,11,13,19,20 >> <output file>

$1 and $20 are the column position in the input file

This code works fine. It is however taking nearly 1.5 hours to extract the data. Is there a way to process the file faster?

  • Just for the non- awk people: to make sure; could you describe what should be done with the line, if it needs what conditions? This can usually be done pretty fast with python per-line approach. – Jacob Vlijm Mar 9 '17 at 14:02
  • So to tell in short, I have a 33gb file. From this i have to extract several files based on the country column (1st column). And im not giving all columns. Only few columns at different position needs to be extracted. – royal23enfield Mar 9 '17 at 14:05
  • @JacobVlijm Don't bet with closed eyes on python for such operations. Have a look in this similar benchmark – George Vasiliou Mar 10 '17 at 12:26
  • @GeorgeVasiliou I don't, it differs per case, but looking at the timing of the python option and OP's command, python is dramatically faster than this one. I don't see benchmarks comparison on the other ones. – Jacob Vlijm Mar 10 '17 at 12:31
3

Try with grep.

(
  export LC_ALL=C
  grep -E '^(BL|FR|[GMTW]F|GP|M[CQ]|NC|PM|RE|YT)([^|]*\|){19}TRUE(\||$)' |
    cut -d'|' -f1-3,6,10,11,13,19,20
)

As suggested by @don_crissti, and assuming all the lines contain at least 20 fields, you may also try cutting first which depending on the number and lengths of the fields on each line and the proportion of lines that match may give better performance:

(
  export LC_ALL=C
  cut -d'|' -f1-3,6,10,11,13,19,20 |
    grep -xE '(BL|FR|[GMTW]F|GP|M[CQ]|NC|PM|RE|YT).*\|TRUE'
)
  • 1
    I had the opposite in mind (cut first then grep) - do you think that'd be slightly faster as it would require only anchoring TRUE to end of line instead of using a quantifier ? – don_crissti Mar 9 '17 at 14:16
  • @don_crissti, quite possibly, depends on the shape of the dataset I suppose and how grep and cut performance compare. I'd say it's very likely. I'll let you add it as an answer. – Stéphane Chazelas Mar 9 '17 at 14:20
  • Well, I'll leave it as a comment here if you don't mind as it assumes each line has at least 20 columns - which may not be true and in that case it'd prolly report false positives with lines less than 20 columns where last extracted column matches TRUE. – don_crissti Mar 9 '17 at 14:29
1

Try mawk? Use version 1.34 or above. Possible speedup for a task processing a large file could be 8x in some person's example:

https://brenocon.com/blog/2009/09/dont-mawk-awk-the-fastest-and-most-elegant-big-data-munging-language/

To make an absolute comparison to your current performance, that task took 1 minute (with mawk) to process 1GB. An attempt using Java (JIT) code was no faster.

Also, many utilities seem to have had their performance degraded when UTF-8 support was added. A google search suggests this can affect at least some versions of awk very dramatically: Try running with the environment variable LC_ALL=C (e.g. LC_ALL=C awk ...).

  • Can you help on using mawk and LC_ALL=c commands. – royal23enfield Mar 9 '17 at 14:24
  • 1) Install the package called mawk on your OS, available in your OS package manager. Type mawk where you previouslly typed awk. 2) Put LC_ALL=C in front of your awk or mawk command. If you already put e.g. time in front of your awk command, you will need to write e.g. time sh -c 'LC_ALL=C awk ...' instead. Or, use export LC_ALL=C, this will affect all commands run from the same shell (e.g. until you close that terminal window). – sourcejedi Mar 9 '17 at 14:47
1

You could at least get rid of the cut:

awk -F '|' 'BEGIN { OFS=FS } $20 == "TRUE" && /^(BL|FR|GF|GP|MC|MF|MQ|NC|PF|PM|RE|TF|WF|YT)/ { print $1,$2,$3,$6,$10,$11,$13,$19,$20 }' indata >outdata

I don't know if that runs any faster, but it avoids having to split each line into fields twice at least.

You may also try cutting out the correct columns first (to reduce the work for awk to just the filtering):

cut -d '|' -f 'columnspec' indata | awk -F '|' 'BEGIN { OFS=FS } $20 == "TRUE" && /^(BL|FR|GF|GP|MC|MF|MQ|NC|PF|PM|RE|TF|WF|YT)/ { print }' >outdata

Another approach is to split the file into manageable chunks, filter these in parallel, and then concatenate the result. See the manual for split on your Unix. You may have to use the -a flag with split if you generate many hundreds of files, but I'd recommend to count the number of lines in the in-data file and split in about 10 or so files.

  • Kusalananda i tried this. But for few files we have a big list of columns. like it starts from 1 to 150. so writing that values in print is a tough. – royal23enfield Mar 9 '17 at 14:06
  • @royal23enfield That was not clear from the question. In that case, cut first, then filter with awk. – Kusalananda Mar 9 '17 at 14:07
0

Using python's per line- approach

The script below returns an arbitrary set of columns in your line, if the first column equals a defined string. Both the required match and the columns to return are arguments to run the script. An example:

python3 /path/to/script.py /path/to/file.txt monkey 3 12 > output.txt

returns column 0, 2 and 11 (the first column is 0) of the lines in file.txt, if the first column equals "monkey"

Timing

On a 30.000.000 lines- file of a few GB's, the script did the job in less then a minute on my 10+ year old box. Since the script reads and processes per line, we may assume the consumed time is more or less linear, and the script will do the job substantially faster than your command.

The script

#!/usr/bin/env python3
import sys

s = sys.argv[2]; cols = [int(n) for n in sys.argv[3:]]

with open(sys.argv[1]) as src:
    for l in src:
        l = l.split("|"); match = l[0].strip()
        if match == s:
            print(match, " ".join(list(l[i].strip() for i in cols)))

How to use

  1. Copy the script into an empty file, save it as get_cols.py
  2. Run it with the following arguments:

    • the source file
    • the required match of the first column (string)
    • the columns to output

    For example:

    python3 /path/to/get_cols.py Germany 2 12 > output.txt
    

That's it

  • @royal23enfield just curious, but did you try? – Jacob Vlijm Mar 12 '17 at 10:32

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.