0

I am dealing with the analysis of multi-column data file (here is an example of 10 lines, but the real log will contain 150 lines!) in the following format:

ID(Prot),   ID(lig),    ID(cluster),    dG(rescored),   dG(before), POP(before)
9000,   lig662, 1,  0.421573,   -7.8400,    153
10V2,   lig662, 1,  0.42692,    -8.0300,    149
3000,   lig158, 1,  0.427342,   -8.1900,    147
3000,   lig158, 1,  0.427342,   -8.1900,    147
10V2,   lig342, 1,  0.432943,   -9.4200,    137
10V1,   lig807, 1,  0.434338,   -8.0300,    147
10V2,   lig369, 1,  0.440377,   -7.3200,    156
10V1,   lig342, 1,  0.441205,   -9.4200,    135
10V1,   lig369, 1,  0.465029,   -7.3600,    148
10V1,   lig158, 1,  0.504513,   -7.4800,    135

From this data I need to focus on the indices in the first (like 9000, 10V1 or 3000) as well as the second columns (like ligXXX). Particularly I need to print top three indexes from both columns as well as the number of their occurrences in all of the lines of the CSV (thus indicating the most common indices in two columns):

TOP PROT; TOP LIG
10V1 (number of cases:4), lig 158 (number of cases: 3)
10V2 (number of cases:3), lig 662 (number of cases: 2)
3000 (number of cases: 2), lig 369 (number of cases: 2)

AWK may be applied directly to calculate the number of occurrences in the selected column which may be sorted etc.

awk '{print $1}' file.csv | sort | uniq -c

I need to develop this idea for both columns and the ranking by occurrence.

0
1

Using GNU awk for arrays of arrays and sorted_in:

$ cat tst.awk
BEGIN { FS=", *"; OFS=", " }
NR > 1 {
    cnts[1][$1]++
    cnts[2][$2]++
}
END {
    numRows = 3
    numCols = 2

    PROCINFO["sorted_in"] = "@val_num_desc"
    for (colNr=1; colNr<=numCols; colNr++) {
        rowNr = 0
        for (key in cnts[colNr]) {
            vals[++rowNr][colNr] = sprintf("%s (number of cases: %d)", key, cnts[colNr][key])
        }
    }

    print "TOP PROT", "TOP LIG"
    for (rowNr=1; rowNr<=numRows; rowNr++) {
        for (colNr=1; colNr<=numCols; colNr++) {
            printf "%s%s", vals[rowNr][colNr], (colNr<numCols ? OFS : ORS)
        }
    }
}

$ awk -f tst.awk file
TOP PROT, TOP LIG
10V1 (number of cases: 4), lig158 (number of cases: 3)
10V2 (number of cases: 3), lig662 (number of cases: 2)
3000 (number of cases: 2), lig369 (number of cases: 2)
6
  • top! just one question: to increase number of the best hits from 3 to 5, need I just set numRows = 5 ? would it be possible also to print the name of the input file as well as the total number of lines in the top of the output message (I am goin to test your script on many many csvs ..) ?
    – Hot JAMS
    Jun 3 at 13:57
  • 1
    Yes numRows = 5 and and yes print FILENAME, NR. If you want that, though, then of course you should once again edit the example in your question ASAP to show it as chameleon questions (i.e. adding/changing requirements, especially in comments, after the question has been answered) are strongly discouraged on this forum.
    – Ed Morton
    Jun 3 at 14:18
  • right, thank you! very much just last question, if I want to use your script inside of my bash script to loop the csv filles etc , would it be enought to execute it as gawk 'your_code_is_here' ${input_csv} >> final_results_for_all_csvs.csv ?
    – Hot JAMS
    Jun 3 at 14:52
  • 1
    Yes you can do that but if you wanted to run it on multiple CSVs you should have said that in your question and had more than 1 CSV for the sample input/output as it can be written slightly differently to do what you want for multiple CSVs in one call to awk without the need for a shell loop and even if you are using a shell loop, appending to the output file within the loop is usually a worse approach than simply redirecting the loop output to a file. You may want to ask a new question about that.
    – Ed Morton
    Jun 3 at 14:55
  • right, thank you again! Indeed this time I am using loop to test your script on several separate CSVs in parallel and then compare data within the same log for test purposes...
    – Hot JAMS
    Jun 3 at 14:58
2

Using GNU awk

gawk -F',[[:blank:]]+' -v N=3 '
    {
        count["prot"][$1]++
        count["lig"][$2]++
    }

    function show(thing,      n, id) {
        print "TOP " toupper(thing)
        n = N
        for (id in count[thing]) {
            printf "%s (number of cases: %d)\n", id,  count[thing][id]
            if (--n == 0) break
        }
    }

    END {
        PROCINFO["sorted_in"] = "@val_num_desc"
        show("prot")
        show("lig")
    }
' file.csv | pr -2Ts$'\t' | sed 's/\t/, /'
TOP PROT, TOP LIG
10V1 (number of cases: 4), lig158 (number of cases: 3)
10V2 (number of cases: 3), lig662 (number of cases: 2)
3000 (number of cases: 2), lig369 (number of cases: 2)
2
  • thank you! I tried to integrate it into my bash workflow looping several csvs and saveing all outputs in the same log. here is the error from gawk gawk: cmd. line:10: fatal: attempt to use a scalar value as array. And then pr: illegal option -- T
    – Hot JAMS
    Jun 3 at 14:40
  • What OS are you on? It seems your tools are quite old. Jun 3 at 16:20

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.