text-processing – Extract Rows Using Different Info in Different Columns

awktext processing

I have a big data file containing different information. I need to select and copy only some rows of this file in another one.

my_file.txt (Column are separated by "tab". I reported only the first column, but after that there is other information)

There are 2543 rows and 22 columns.

4gga_A_001_______________   clust_001   APC-coactivator_clust_001   4GGA-A  Q12834  2.04    CDC20   APC-coactivator
4ggc_A_002_______________   clust_001   APC-coactivator_clust_001   4GGC-A  Q12834  1.35    CDC20   APC-coactivator
4ggd_A_002_______________   clust_001   APC-coactivator_clust_001   4GGD-A  Q12834  2.43    CDC20   APC-coactivator
4n14_A_002_______________   clust_001   APC-coactivator_clust_001   4N14-A  Q12834  2.1 CDC20   APC-coactivator
5g04_R_002_______________   clust_001   APC-coactivator_clust_001   5G04-R  Q12834  3.9 CDC20   APC-coactivator
5khu_R_006_______________   clust_001   APC-coactivator_clust_001   5KHU-R  Q12834  4.8 CDC20   APC-coactivator
5lcw_Q_002_______________   clust_001   APC-coactivator_clust_001   5LCW-Q  Q12834  4.2 CDC20   APC-coactivator
6q6g_R_004_______________   clust_001   APC-coactivator_clust_001   6Q6G-R  Q12834  3.2 CDC20   APC-coactivator
6q6h_R_003_______________   clust_001   APC-coactivator_clust_001   6Q6H-R  Q12834  3.2 CDC20   APC-coactivator
6q6g_R_005_______________   clust_016   APC-coactivator_clust_016   6Q6G-R  Q12834  3.2 CDC20   APC-coactivator
6q6h_R_002_______________   clust_017   APC-coactivator_clust_017   6Q6H-R  Q12834  3.2 CDC20   APC-coactivator
1u6d_X_001_______________   clust_001   BTB_clust_001   1u6d_X  Q14145  1.85    KEAP1   BTB
1zgk_A_001_______________   clust_001   BTB_clust_001   1zgk_A  Q14145  1.35    KEAP1   BTB
2vpj_A_001_______________   clust_001   BTB_clust_001   2vpj_A  Q53G59  1.85    KLHL12  BTB
2xn4_A_001_______________   clust_001   BTB_clust_001   2xn4_A  O95198  1.99    KLHL2   BTB
3vng_A_001_______________   clust_001   BTB_clust_001   3vng_A  Q14145  2.1 KEAP1   BTB
3vnh_A_001_______________   clust_001   BTB_clust_001   3vnh_A  Q14145  2.1 KEAP1   BTB
3zgc_A_001_______________   clust_001   BTB_clust_001   3zgc_A  Q14145  2.2 KEAP1   BTB
3zgd_A_001_______________   clust_001   BTB_clust_001   3zgd_A  Q14145  1.98    KEAP1   BTB
4ch9_A_001_______________   clust_001   BTB_clust_001   4ch9_A  Q9UH77  1.84    KLHL3   BTB
4chb_A_001_______________   clust_001   BTB_clust_001   4chb_A  O95198  1.56    KLHL2   BTB
4ifj_A_001_______________   clust_001   BTB_clust_001   4ifj_A  Q14145  1.8 KEAP1   BTB
4ifl_X_001_______________   clust_001   BTB_clust_001   4ifl_X  Q14145  1.8 KEAP1   BTB
4ifn_X_001_______________   clust_001   BTB_clust_001   4ifn_X  Q14145  2.4 KEAP1   BTB
4in4_A_001_______________   clust_001   BTB_clust_001   4in4_A  Q14145  2.59    KEAP1   BTB
4iqk_A_001_______________   clust_001   BTB_clust_001   4iqk_A  Q14145  1.97    KEAP1   BTB
4l7b_A_001_______________   clust_001   BTB_clust_001   4l7b_A  Q14145  2.41    KEAP1   BTB
4l7b_B_001_______________   clust_001   BTB_clust_001   4l7b_B  Q14145  2.41    KEAP1   BTB
4l7c_A_001_______________   clust_001   BTB_clust_001   4l7c_A  Q14145  2.4 KEAP1   BTB
4l7d_A_001_______________   clust_001   BTB_clust_001   4l7d_A  Q14145  2.25    KEAP1   BTB
4n1b_A_001_______________   clust_001   BTB_clust_001   4n1b_A  Q14145  2.55    KEAP1   BTB
4xmb_A_001_______________   clust_001   BTB_clust_001   4xmb_A  Q14145  2.43    KEAP1   BTB
5f72_C_001_______________   clust_001   BTB_clust_001   5f72_C  Q14145  1.85    KEAP1   BTB
5nkp_A_001_______________   clust_001   BTB_clust_001   5nkp_A  Q9UH77  2.8 KLHL3   BTB
5wfl_A_001_______________   clust_001   BTB_clust_001   5wfl_A  Q14145  1.93    KEAP1   BTB
5wfv_A_001_______________   clust_001   BTB_clust_001   5wfv_A  Q14145  1.91    KEAP1   BTB
5wg1_A_002_______________   clust_001   BTB_clust_001   5wg1_A  Q14145  2.02    KEAP1   BTB
5whl_A_002_______________   clust_001   BTB_clust_001   5whl_A  Q14145  2.5 KEAP1   BTB
5whl_B_001_______________   clust_001   BTB_clust_001   5whl_B  Q14145  2.5 KEAP1   BTB
5who_A_002_______________   clust_001   BTB_clust_001   5who_A  Q14145  2.23    KEAP1   BTB
5who_B_001_______________   clust_001   BTB_clust_001   5who_B  Q14145  2.23    KEAP1   BTB
5wiy_A_001_______________   clust_001   BTB_clust_001   5wiy_A  Q14145  2.23    KEAP1   BTB
5wiy_B_001_______________   clust_001   BTB_clust_001   5wiy_B  Q14145  2.23    KEAP1   BTB
5x54_A_001_______________   clust_001   BTB_clust_001   5x54_A  Q14145  2.3 KEAP1   BTB
5yq4_A_001_______________   clust_001   BTB_clust_001   5yq4_A  Q9Y2M5  1.58    KLHL20  BTB
5yy8_A_001_______________   clust_001   BTB_clust_001   5yy8_A  Q9Y6Y0  1.98    IVNS1ABP    BTB
6fmp_A_001_______________   clust_001   BTB_clust_001   6fmp_A  Q14145  2.92    KEAP1   BTB
6fmq_A_001_______________   clust_001   BTB_clust_001   6fmq_A  Q14145  2.1 KEAP1   BTB
6gy5_A_001_______________   clust_001   BTB_clust_001   6gy5_A  Q9Y2M5  1.09    KLHL20  BTB
6hws_A_001_______________   clust_001   BTB_clust_001   6hws_A  Q14145  1.75    KEAP1   BTB
6n3h_A_001_______________   clust_001   BTB_clust_001   6n3h_A  Q9Y6Y0  2.6 IVNS1ABP    BTB
6rog_A_001_______________   clust_001   BTB_clust_001   6rog_A  Q14145  2.16    KEAP1   BTB

I need to extract rows using the values in 3rd, 5th and 6th column. In details, for equal third column strings (e.g. APC-coactivator_clust_001, or APC-coactivator_clust_016 …) I must extract the row to which corresponds, for each different fifth column value (e.g. Q12834 …) the lowest sixth column value.
I don’t know if I was clear enough. Anyway I bring you the output file that I should get.

outpout.txt

4ggc_A_002_______________   clust_001   APC-coactivator_clust_001   4GGC-A  Q12834  1.35    CDC20   APC-coactivator
6q6g_R_005_______________   clust_016   APC-coactivator_clust_016   6Q6G-R  Q12834  3.2 CDC20   APC-coactivator
6q6h_R_002_______________   clust_017   APC-coactivator_clust_017   6Q6H-R  Q12834  3.2 CDC20   APC-coactivator
1zgk_A_001_______________   clust_001   BTB_clust_001   1zgk_A  Q14145  1.35    KEAP1   BTB
2vpj_A_001_______________   clust_001   BTB_clust_001   2vpj_A  Q53G59  1.85    KLHL12  BTB
4chb_A_001_______________   clust_001   BTB_clust_001   4chb_A  O95198  1.56    KLHL2   BTB
4ch9_A_001_______________   clust_001   BTB_clust_001   4ch9_A  Q9UH77  1.84    KLHL3   BTB
5yy8_A_001_______________   clust_001   BTB_clust_001   5yy8_A  Q9Y6Y0  1.98    IVNS1ABP    BTB
6gy5_A_001_______________   clust_001   BTB_clust_001   6gy5_A  Q9Y2M5  1.09    KLHL20  BTB

Best Answer

Using awk and process input file only once:

awk 'min[$3, $5]!=""{ if(min[$3, $5]>$6){ line[$3, $5]=$0; min[$3, $5]=$6}; next }
                    { min[$3, $5]=$6; line[$3, $5]=$0 }
END{ for(x in line) print line[x] }' infile

To "keep lines with equal minimum values" in 6^th column:

awk 'min[$3, $5]!=""{ if(min[$3, $5] >$6){ line[$3, $5]=$0; min[$3, $5]=$6 };
                      if(min[$3, $5]==$6){ line[$3, $5]=line[$3, $5] ORS $0 }; next
                    }
                    { min[$3, $5]=$6; line[$3, $5]=$0 }
END{ for(x in line) print line[x] }' infile

Explanation

The -l trims newlines from each input line, the -a splits input fields on whitespace into the array @F and the -p prints each input line after applying the script given by -e.

The script itself iterates over each input field (the @F array), saving each as $i. The substitution looks for 2 or more consecutive $i followed by 0 or more spaces and replaces them with $i+.

text-processing – How to Convert Rows to Columns

If walking the file twice is not a (big) problem (will store only one line in memory):

awk -F : '{printf("%s\t ", $1)}' infile
echo
awk -F : '{printf("%s\t ", $2)}' infile

Which, for a general count of fields would be (which could have many walks of the file):

#!/bin/bash
rowcount=2
for (( i=1; i<=rowcount; i++ )); do
    awk -v i="$i" -F : '{printf("%s\t ", $i)}' infile
    echo
done

But for a really general transpose, this will work:

awk '$0!~/^$/{    i++;
                  split($0,arr,":");
                  for (j in arr) {
                      out[i,j]=arr[j];
                      if (maxr<j){ maxr=j} # max number of output rows.
                  }
            }
    END {
        maxc=i                             # max number of output columns.
        for     (j=1; j<=maxr; j++) {
            for (i=1; i<=maxc; i++) {
                printf( "%s\t", out[i,j])  # out field separator.
            }
            printf( "%s\n","" )
        }
    }' infile

And to make it pretty (using tab \t as out field separator) :

./script | |column -t -s $'\t'

Virtual_Machine  ID                                Status   Memory  Uptime  Server              Pool     HA     VCPU  Type     OS
OL6U7            0004fb00000600003da8ce6948c441bd  Running  65536   17103   MyOVS1.vmworld.com  HA-POOL  false  16    Xen PVM  Oracle Linux 6

The code above for a general transpose will store the whole matrix in memory.
That could be a problem for really big files.

Update for new text.

To process the new text posted in the question, It seems to me that two pass of awk are the best answer. One pass, as short as fields exist, will print the header field titles. The next awk pass will print only field 2. In both cases, I added a way to remove leading and trailing spaces (for better formatting).

#!/bin/bash
{
awk -F: 'BEGIN{ sl="Virtual Machine"}
         $1~sl && head == 1 { head=0; exit 0}
         $1~sl && head == 0 { head=1; }
         head == 1 {
             gsub(/^[ \t]+/,"",$1);   # remove leading  spaces
             gsub(/[ \t]+$/,"",$1);   # remove trailing spaces
             printf( "%s\t", $1)
         }
         ' infile
#echo
awk -F: 'BEGIN { sl="Virtual Machine"}
         $1~sl { printf( "%s\n", "") }
         {
             gsub(/^[ \t]+/,"",$2);   # remove leading  spaces
             gsub(/[ \t]+$/,"",$2);   # remove trailing spaces
             printf( "%s\t", $2)
         }
         ' infile
echo
} | column -t -s "$(printf '%b' '\t')"

The surrounding { ... } | column -t -s "$(printf '%b' '\t')" is to format the whole table in a pretty way.
Please note that the "$(printf '%b' '\t')" could be replaced with $'\t' in ksh, bash, or zsh.

Best Answer

Related Solutions

Bash – merge duplicate rows in columns

Explanation

text-processing – How to Convert Rows to Columns

Update for new text.

Related Question