How to use awk to condense columns by an ID column?

Question

I have code I normally run in R, but the file is too big, so I am trying to run the same commands in awk.

I am trying to group values in columns by an ID column (or Gene column in my case).

My data looks like:

Gene       col1   col2   col3
ACE         1     0.4    BP
ACE         2     0.5    DP
RPP-I.1     1     0.01   BP
NOS2      -0.1   0.2    DP
NOS2       1.4   2.5    SP
NOS2        1      1    BP

I want to group it by Gene to look like:

Gene     col1          col2          col3
ACE      1, 2          0.4, 0.5      BP, DP
RPP-I.1  1             0.01          BP
NOS2     -0.1, 1.4, 1  0.2, 2.5, 1   BP, SP, DP

My real data is 14.8GB with ~200 columns and 24972316 lines, I was originally trying to use R's data.table but this is giving a bus error with trying to read in the file.

Is there a way I can try this with awk?

Answer 1

A generic solution using GNU awk would be:

gawk 'NR>1{ for (i=2; i<=NF; i++) {
               c[i][$1]= c[i][$1]?c[i][$1] s $i:$i;
           } next;
}1;

ENDFILE{
    for (x in c[2]) {
        printf ("%s", x);
        for (i=2;i<=NF;i++) { printf ("\t%s", c[i][x]); delete c[i][x]; };
        print "";
     };
}' s=', ' infile  |column -s $'\t' -t

---

Above command will load not all but almost near whole your input file into the memory and you said you have 30GB of RAM and your file size is ~15GB, so if you have enough free memory at least 15GB, there would be no issue I think.

But below is a workaround but not optimal solution to chunks your bigfile.txt into small files each on having the same GeneName then apply above awk command for those all *.small files and save output to single file in appending mode.

I'm saying it's not optimal because maybe distribution of the GeneNames were not equal and probably some are less and some are much; however here you go:

1. Split the input file into small sizes on first column Gene:

   awk 'NR>1{ print >$1".small"; }' bigfile.txt
   

2. Then perform run given awk command above on *.small files; Just remove condition NR>1 at the beginning since when we split the bigfile.txt we already skip it.

   gawk '{ ... }; ENDFILE{ ... }' s=', ' *.small >>proccedfile
   

3. and remove rm *.small files later.

Answer 2

The following is designed to work with huge files by only having sort required to process the whole file at once and sort is designed to deal with that by using demand-paging, etc. so it doesn't actually have to store the whole input in memory. Within the awk command only the values for the current $1 are stored at a time so it won't have any memory issues:

$ cat tst.sh
#!/usr/bin/env bash

awk -v OFS='\t' '{print (NR>1), NR, $0}' "${@:--}" |
sort -k1,1n -k3,3 -k2,2n |
cut -f 3- |
awk '
    BEGIN { OFS="\t" }
    NR == 1 { $1=$1; print; next }
    $1 != prev { prt() }
    {
        for (i=2; i<=NF; i++) {
            col[i] = (i in col ? col[i] ", " : "") $i
        }
    }
    END { prt() }

    function prt(       i) {
        if ( prev != "" ) {
            printf "%s%s", prev, OFS
            for (i=2; i<=NF; i++) {
                printf "%s%s", col[i], (i<NF ? OFS : ORS)
            }
        }
        delete col
        prev = $1
    }
'

$ ./tst.sh file
Gene     col1          col2         col3
ACE      1, 2          0.4, 0.5     BP, DP
NOS2     1, 1.4, -0.1  1, 2.5, 0.2  BP, SP, DP
RPP-I.1  1             0.01         BP

The output of the above script is tab-separated which I assume is good for you as you can then easily run other tools on it, import it to a spreadsheet, etc. If you want it to produce visually aligned columns instead then add | column -s $'\t' -t to the end of the script BUT then you've introduced another program that potentially has to read the whole output file into memory to calculate the max field widths before printing so YMMV. If you can't live with tab-separated output and can't use column to produce tabular output then post a new question just about that.

The above is also written to work whether the input is coming from a file or a pipe.