0

I need to extract some values from a file in bash, on my CentOS system. In myfile.txt I have a list of objects called Info_region in which each object is identified with a code (eg. BARD1_region_005 or BIRC2_region_002 etc.) Moreover there are some others columns in which are reported some numerical variable. The same object (same code name) can be repeated several times in my file. I also have a file that contains a completed list with all object codes without duplicates. I would like to obtain an output.txt file in which each object (code name) is reported only once as in my list-file.txt and I would like to associate to this the maximum possible values associated with that code name in myfile.txt.

myfile.txt: (columns are separated by tab)

Info_region Lig_score   Lig_prevista    Lig_prevista_+1 Int_score   Expo_score  Protac_score
BARD1_region_005    0   3   3   0   1   1
BARD1_region_006    0   1   1   0   1   1
BIRC2_region_001    1   6   7   0   1   2
BIRC2_region_001    1   7   8   0   1   2
BIRC2_region_001    0   2   2   0   0   0
BIRC2_region_001    0   12  12  0   1   1
BIRC2_region_001    1   10  11  -1  1   1
BIRC2_region_001    1   2   3   0   1   2
BIRC2_region_001    1   0   1   0   1   2
BIRC2_region_001    1   6   7   0   1   2
BIRC2_region_002    0   0   0   0   1   1
BIRC2_region_002    1   0   0   -1  0.5 0.5
BIRC2_region_003    0   0   0   0   1   1
BIRC2_region_004    0   1   1   0   1   1
UHRF1_region_004    0   0   0   1   1   2
UHRF1_region_004    0   0   0   1   1   2
UHRF1_region_004    1   0   1   0   0.5 1.5
UHRF1_region_004    0   0   0   1   1   2
UHRF1_region_005    0   3   3   1   1   2
UHRF1_region_005    1   0   0   -1  1   1

file-list.txt:

Info_region
BARD1_region_005
BARD1_region_006
BIRC2_region_001
BIRC2_region_002
BIRC2_region_003
BIRC2_region_004
UHRF1_region_004
UHRF1_region_005

output.txt:

Info_region Lig_score   Lig_prevista    Lig_prevista_+1 Int_score   Expo_score  Protac_score
BARD1_region_005    0   3   3   0   1   1
BARD1_region_006    0   1   1   0   1   1
BIRC2_region_001    1   12  12  0   1   2
BIRC2_region_002    1   0   0   0   1   1
BIRC2_region_003    0   0   0   0   1   1
BIRC2_region_004    0   1   1   0   1   1
UHRF1_region_004    1   0   1   1   1   2
UHRF1_region_005    1   3   3   1   1   2

Could someone help me please? Thank you!

3
  • do you mean the maximum per column (field)? where an Info_region occurs more than once in the original file, should the values per column be added or considered separately?
    – gogoud
    Apr 9 at 11:27
  • Is there anything you have tried? There are similar questions here where you can find good information (e.g.). Hint: awk or datamash should do.
    – pLumo
    Apr 9 at 11:38
  • What do you mean by "bash" exactly? Using bash or any shell for text [processing is not a good idea. Do you really mean you want to use bash or are you OK with tools like sed, awk, etc? Do you just mean you want a command line solution or does it really need to be bash for some reason?
    – terdon
    Apr 9 at 11:48
5

Assuming the data is in the file called file and that it is sorted on the first column, the GNU datamash utility could do this in one go on the data file alone:

datamash -H -W -g 1 max 2-7 <file

This instructs the utility to use whitespace separated columns (-W; remove this if your column are truly tab-delimited), that the first line of the data contains headers (-H), to group by he first column (-g 1), and to calculate the maximum values for the 2nd through to the 7th columns.

The result, given the data in the question:

GroupBy(Info_region)    max(Lig_score)  max(Lig_prevista)       max(Lig_prevista_+1)    max(Int_score)     max(Expo_score) max(Protac_score)
BARD1_region_005        0       3       3       0       1       1
BARD1_region_006        0       1       1       0       1       1
BIRC2_region_001        1       12      12      0       1       2
BIRC2_region_002        1       0       0       0       1       1
BIRC2_region_003        0       0       0       0       1       1
BIRC2_region_004        0       1       1       0       1       1
UHRF1_region_004        1       0       1       1       1       2
UHRF1_region_005        1       3       3       1       1       2

You could also use --header-in in place of -H to get header-less output, and then take the header from the original data file:

{ head -n 1 file; datamash --header-in -W -g 1 max 2-7 <file; } >output

Here, I'm also writing the result to some new output file called output.


Using awk and assuming tab-delimited fields:

awk -F '\t' '
    BEGIN { OFS = FS }
    NR == 1 { print; next }
    {
        n[$1] = 1
        for (i = 2; i <= NF; ++i)
            a[$1,i] = (a[$1,i] == "" || $i > a[$1,i] ? $i : a[$1,i])
    }
    END { 
        nf = NF
        for (j in n) {
            $0 = j
            for (i = 2; i <= nf; ++i)
                $i = a[$1,i]
            print
         }
     }' file

This calculates the maximum value in each column for each group. These numbers are stored in the a array while the n array just holds the group names as keys.

3
  • If my columns are separeated by tab? I can modify the script? Apr 9 at 11:45
  • I have a problem.....bash: datamash: command not found Apr 9 at 11:47
  • @TommasoPalomba The output is tab delimited. It does not matter if you use tabs or spaces for the input. If you don't have datamash, you may have to install it or ask for it to be installed (I have compiled it for myself without needing to be root).
    – Kusalananda
    Apr 9 at 11:49
2

I am assuming you don't actually want to do this in bash (and you shouldn't), but are willing to use other tools. Here's a GNUawk approach:

$ gawk -vOFS="\t" -F'\t' \
    '{ 
        if(NR==1){print; } 
        else{ 
            for(i=2;i<=NF;i++){ 
                if($i > a[$1][i] || ! a[$1][i]){a[$1][i]=$i}
           }
        } 
     }
     END{ 
        for (region in a){ 
            printf "%s", region; 
            for(i=2;i<=NF;i++){ 
                printf "%s%s", OFS,a[region][i]
            } 
            printf "\n";  
        } 
     }' myfile.txt 
Info_region Lig_score   Lig_prevista    Lig_prevista_+1 Int_score   Expo_score  Protac_score
BIRC2_region_001    1   12  12  0   1   2
BIRC2_region_002    1   0   0   -1  1   1
BIRC2_region_003    0   0   0   0   1   1
BIRC2_region_004    0   1   1   0   1   1
BARD1_region_005    0   3   3   0   1   1
BARD1_region_006    0   1   1   0   1   1
UHRF1_region_004    1   0   1   1   1   2
UHRF1_region_005    1   3   3   1   1   2
0

This is an approach using neither true multidimensional arrays nor emulated multi-dimensional. Instead , we Store chunks in the same key.

awk '
BEGIN { OFS = FS = "\t" }
NR==1 { print; next }
{
  obj = $1; $1=""; sub(FS, "")
  a[obj] = a[obj] $0 FS
}
END {
  nf0 = NF
  for (obj in a) {
    $1 = obj
    nf = split(a[obj], f)
    for (i=1; i<=nf0; i++) {
      $(i+1) = f[i]
      for (j=0; j<int(nf/nf0); j++) {
        idx = i + nf0*j
        if ( f[idx] > $(i+1) ) $(i+1) = f[idx]
      }
    }
    print
  }
}
' myfile.txt

Not the answer you're looking for? Browse other questions tagged or ask your own question.