how do I code an arbitrarily long chain of pipes?

Question

I am somewhat new to the Linux environment. I looked all over for an answer to this -- apologies if this has been asked before.

I wrote an awk script that operates on a big text file (11 gigs, 40 columns, 48M rows). The script is called "cycle.awk." It replace a column with a new version of it. It requires the data to be sorted first by that particular column. In order to run the script on all the columns, I wrote a bash command like this:

cat input.csv |
    sort -k 22 -t "," | awk -v val=22 -f cycle.awk |
    sort -k 23 -t "," | awk -v val=23 -f cycle.awk |
    sort -k 24 -t "," | awk -v val=24 -f cycle.awk |
    sort -k 25 -t "," | awk -v val=25 -f cycle.awk |
    sort -k 26 -t "," | awk -v val=26 -f cycle.awk |
    sort -k 27 -t "," | awk -v val=27 -f cycle.awk |
    sort -k 28 -t "," | awk -v val=28 -f cycle.awk |
    sort -k 29 -t "," | awk -v val=29 -f cycle.awk |
    sort -k 30 -t "," | awk -v val=30 -f cycle.awk |
    sort -k 31 -t "," | awk -v val=31 -f cycle.awk |
    sort -k 32 -t "," | awk -v val=32 -f cycle.awk |
    sort -k 33 -t "," | awk -v val=33 -f cycle.awk |
    sort -k 34 -t "," | awk -v val=34 -f cycle.awk |
    sort -k 35 -t "," | awk -v val=35 -f cycle.awk |
    sort -k 36 -t "," | awk -v val=36 -f cycle.awk |
    sort -k 37 -t "," | awk -v val=37 -f cycle.awk |
    sort -k 38 -t "," | awk -v val=38 -f cycle.awk |
    sort -k 39 -t "," | awk -v val=39 -f cycle.awk |
    sort -k 40 -t "," | awk -v val=40 -f cycle.awk |
    sort -k 41 -t "," | awk -v val=41 -f cycle.awk > output.csv

I figure there must be a more elegant way to do this. How can I write a bash script that will allow me to pass the columns I want to apply my awk script and then run this kind of piping procedure without needing to produce any temporary data files? I am avoiding temporary files because the input file is so large and I am interested in optimal performance.

BTW, the script is as follows. It basically shortens the values of some columns for purposes of compressing the text file. Any pointers on how to tighten it up? This procedures takes about 10 hours to run.

BEGIN{ FS=","; OFS=","; count=1 }
NR == 1 { temp=$val }
{
    if ( temp != $val ) {
        temp=$val;
        count++;
    }
    $val=count
    print $0
}

Input typically looks something like this:

id,c1
1,abcd
2,efgh
3,abcd
4,abcd
5,efgh

where the corresponding output would be:

id,c1
1,1
2,2
3,1
4,1
5,2

Technically, it would be sorted by c1 but that's not the point.

Answer 1

The real Right Answer is to rewrite your process to not need this kind of pipeline. However, if you do want to set up such a pipeline, use a recursive function (that pipes to itself):

process_column() {
  sort -k "$1" -t, | awk -v val="$1" -f cycle.awk
}

process_column_range() {
  local min_col=$1
  local max_col=$2
  if (( min_col < max_col )); then
    process_column "$min_col" \
     | process_column_range "$(( min_col + 1 ))" "$max_col"
  else
    process_column "$min_col"
  fi
}

...and then, to invoke (notice that no cat is needed):

process_column_range 22 41 <input.csv >output.csv

Answer 2

Here's a two-pass approach using the algorithm suggested by @robmayoff. It could be implemented in awk or native bash (the latter at a significant performance penalty), but I'm going with Python for readability:

#!/usr/bin/env python

import sys, collections, itertools

input_file_name = sys.argv[1]
col_start = int(sys.argv[2])
col_end = int(sys.argv[3]) + 1

vals = collections.defaultdict(set)

# first pass: build up translation tables for each column
for line in open(input_file_name, 'r'):
  cols = line.split(',') # if this is real CSV, use the Python csv module instead
  for col_no in range(col_start, col_end):
    val = cols[col_no]
    if not val in vals[col_no]: # O(1) operation on sets, vs O(n) on lists
      vals[col_no].add(val)

# interim processing: make sets into dicts w/ values in ascending order
for col_no in vals.iterkeys():
  vals[col_no] = dict(itertools.izip(sorted(list(vals[col_no])),
                                     (str(n) for n in itertools.count())))

# second pass: apply translation tables and print output
for line in open(input_file_name, 'r'):
  cols = line.split(',')
  for col_no in range(col_start, col_end):
    val = cols[col_no]
    cols[col_no] = vals[col_no][val]
  print ','.join(cols)

I don't propose this as an accepted answer, since it doesn't actually answer the question posed (constructing chains of pipes), but if the number of unique values in the columns being renumbered is low, it may be useful to you.

Invoke as:

./process_column_range input.csv 22 41 >output.csv

Answer 3

Here is my suggestion for a two-pass solution based on comments by @robmayoff. It uses gawk (which offers built-in sorting functions). As long as the space required to store all the distinct column values doesn't get into the multi-gigabyte range, it ought to run well, and will be much faster than doing 20 sorts and awk passes.

This sample sorts on columns 2, 3, and 4.

s.awk:

# makemaps() replaces the values in the str-indexed arrays with integers,
# sorted by ascending index value
function makemaps() {
    PROCINFO["sorted_in"]="@ind_str_asc";
    n=1;
    for(i in A2) A2[i]=n++;
    n=1;
    for(i in A3) A3[i]=n++;
    n=1;
    for(i in A4) A4[i]=n++;
    mapsdone=1;
}
BEGIN { FS=","; OFS=","; mapsdone=0; }
{
    if (NR == FNR) {
        # first pass
        # allocate array elements by index. Don't need to assign values yet.
        A2[$2];A3[$3];A4[$4];
    } else {
        # second pass
        # if not yet done, set up arrays' values to be small sequential integers
        if (!mapsdone) makemaps();
        # replace fields with the corresponding small integers
        $2=A2[$2];
        $3=A3[$3];
        $4=A4[$4];
        print $0;
    }
}

input file:

1,abcd,red,mercury
2,efgh,orange,mercury
3,abcd,green,venus
4,abcd,blue,earth
5,efgh,red,earth

output of gawk -f s.awk input input (you need to list the input file twice):

1,1,4,2
2,2,3,2
3,1,2,3
4,1,1,1
5,2,4,1

As a larger test, I used this script to generate a 48-million line input file with three 12-character columns:

BEGIN {
    OFS=",";
    for(i=0; i<48000000; i++) {
        print i,
        "aaaaaaaaa" int(1000*rand()),
        "bbbbbbbbb" int(1000*rand()),
        "ccccccccc" int(1000*rand());
    }
}

Running /usr/bin/time -v awk -f s.awk input input > output resulted in

Command being timed: "awk -f s.awk input input"
User time (seconds): 139.73
System time (seconds): 6.12
Percent of CPU this job got: 94%
Elapsed (wall clock) time (h:mm:ss or m:ss): 2:34.85
Maximum resident set size (kbytes): 1896

This is on a single-core VMWare CPU on a 3.4GHz system.

So with 20 columns, it may take 17 minutes or so and use up no more than 15 megabytes of RAM.