0

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?

5
  • My file is tab-separated and input file is not sorted by gene the Gene column, and it is a character class with no numeric values - I'll change my example genes to real example to be more clear, sorry about that
    – DN1
    Dec 2 '20 at 11:29
  • Does the output order matter? What happens to duplicate combinations like Gene3+BP? Do they need to be counted somehow in the output, or is it sufficient to show them once? Dec 2 '20 at 11:32
  • I think that the operation that you want to perform is called "group" instead of "compress".
    – RubioRic
    Dec 2 '20 at 11:37
  • Thank you both, I've updated the question to say group. The output order doesn't matter. I'm not sure what is meant by duplicate combinations here - sorry for my basic understanding, I'm from a biology background
    – DN1
    Dec 2 '20 at 11:39
  • No, when I try to read in the file in R within my HPC with 30gb of ram I get a bus error
    – DN1
    Dec 2 '20 at 11:56
2

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.

0
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.

4
  • this also assume distribution of the Gene names $1 are equally and not only just 10 or 100 but as much more different Gene names then much less memory will occupy else it will be load whole file into the memory in a worst case that all Gene names were same; same memory issue as processing bigfile. this applies to my workaround answer too as well as first version obviously Dec 2 '20 at 16:56
  • Yes, if there was just 1 gene name then the whole file would be loaded into memory. It doesn't assume they're distributed equally, just that the data for any one of them won't fill the memory.
    – Ed Morton
    Dec 2 '20 at 17:00
  • "equally" I used, because that's the best case to use memory optimally, file contains ~25M lines, with 25M unique lines it's the best case, and 25M all single Gene is worst case. so as much Gene distribution were equally it's more optimal Dec 2 '20 at 17:14
  • Ah, I see. To me "equally" given 25M lines sample input could mean you only have 2 genes and each has 12.5M lines associated with it but I get what you meant now. Yeah, if any one gene has enough data associated with it to use up all the memory then we'd need to do something different and what that "something different" is will depend on how much disk space is available, which additional field(s) can be added to the separation criteria for splitting the input, etc.
    – Ed Morton
    Dec 2 '20 at 17:31

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.