I have a file like this:

     OV2  OVI  1VI  OV3  3VI  
er    23   23   23   23   23  
tr    24   24   24   24   24

I want to print the 1st column along with any columns whose name contains VI (I don't know which columns will contain the string beforehand). In the example above, the output should be like this:

     OVI  1VI  3VI  
er    23   23   23     
tr    24   24   24  

all the columns should be tab delimited.

  • Will you know which columns have VI or does the solution need to find them? – terdon May 20 '17 at 11:42
  • i need to print the column cntains VI – Masum Billah May 20 '17 at 11:43
  • Yes, but do you know it will be the 2nd, 3d and last column? Or do our solutions need to find that out? – terdon May 20 '17 at 11:45
  • actually i have many columns containing VI. – Masum Billah May 20 '17 at 11:47
  • Wait, the first column doesn't have a header? Please always show representative examples. It is very tiring when you change the input data like this. – terdon May 20 '17 at 12:29
perl -lane '$,="\t";
   $. == 1 and @A = grep $F[$_] =~ /VI/, 0..$#F;
   print @F[0,@A];
' yourfile


ID      OVI     1VI     3VI
er      23      23      23
tr      24      24      24


  • From the first line, $. == 1 extract the indices of the fields that contain the string VI.
  • Armed with these list of indices now in array @A, we just plain go ahead and slice out the 1st field + the fields listed in the @A array from the the @F array. The OFS=$, has been set to a TAB. YMMV.


awk -v OFS="\t" '
      for ( i=2; i<=NF; i++ )
         if ( $i ~ /VI/ )
            str = str OFS i
      N = split(str, A, OFS)
      s = $1
      for ( i=2; i<=N; i++ )
         s = s OFS $(A[i])
      $0 = s
' yourfile


sed -e '
   # TAB->spc, multiple spc -> single spc, trim leading/trailing spc
   y/ / /;s/[ ]\{2,\}/ /g;s/^[ ][ ]*//;s/[ ][ ]*$//

   # only for first line, remove the first field and store remaining in hold area
         s/[ ]/\

   # append hold area (which now has 2nd...last fields
   # data of the first record) to the present line and
   # place a marker at the end of the first field
   s/[^ ][^ ]*[ ]/&\

   # setup a do-while loop which progressively either keeps VI data or trims it
      #  1     2                      3
      s/\(\n\)\([^ ][^ ]*\)[ ]\{0,1\}\(.*\n\)[^ ]*VI[^ ]*[ ]\{0,1\}/ \2\1\3/;tloop
      s/\(\n\)[^ ][^ ]*[ ]\{0,1\}\(.*\n\)[^ ][^ ]*[ ]\{0,1\}/\1\2/
   # loop ends when the two \ns collide at the end of line

   # remove the two \ns and what remains is what you wanted

' yourfile
  • thanks. it workes. but I am looking for awk solution. because I am used to with perl – Masum Billah May 20 '17 at 12:52
  • @MasumBillah I've added a bare-bones awk code. – user218374 May 20 '17 at 13:43

awk solution:

awk 'BEGIN{FS="[\t ]+"; OFS="\t"}NR==1{for(i=2;i<=NF;i++)
    {if($i~/VI/) a[i]; }}{r=$1; for(i in a) r=r OFS $i; print l}' file

The output:

    OVI 1VI 3VI
er  23  23  23
tr  24  24  24

  • FS="[\t ]+" - input field separator

  • OFS="\t" - output field separator

  • NR==1 - for the first header line

  • if($i~/VI/) a[i] - capturing field number if it matches VI

  • r=$1; for(i in a) r=r OFS $i; print r - iterating through needed field numbers and printing their respective values

If you encountering an order breaking use the following with asorti() function (to sort array by indices):

awk 'BEGIN{FS="[\t ]+"; OFS="\t"}NR==1{for(i=2;i<=NF;i++)
    {if($i~/VI/) a[i]; }}{r=$1; asorti(a,b); for(i in b) {r=r OFS $(b[i])} print r}' file
  • Sorry, this is not working as per my requirement. because, I want the first column remain as column 1 then other columns whose column head contains VI and it should be tab delimited. but as per your command the values are in same column and the first column goes to the last line – Masum Billah May 20 '17 at 12:05
  • @MasumBillah then please edit your question and make that requirement clear. You didn't mention it anywhere. – terdon May 20 '17 at 12:27
  • @MasumBillah, that's because you are playing with requirements "on the fly" - that's not good – RomanPerekhrest May 20 '17 at 12:37
  • @MasumBillah, see the final update – RomanPerekhrest May 20 '17 at 12:54
  • i checked, it is working but the first column is still in the last column, but i thibk it is ok now – Masum Billah May 20 '17 at 12:56

Python script solution. Operates on the basis of parsing first line, and building up a list of columns. Those columns that don't have VI in them, are set to None. All other lines are split into words and joined into pairs with column list items for comparison. If corresponding column item is None, that word from the current line won't be printed. Otherwise, things that are not None are printed

#!/usr/bin/env python3
import sys

with open(sys.argv[1]) as fd:
    indexes = []
    for index,line in enumerate(fd):
        if index == 0:
            columns = line.strip().split()
            for i,col in enumerate(columns):
                if 'VI' in col or i == 0:
            for x in indexes:
                if x:
                    print(x,end=" ")
        for j in zip(line.strip().split(),indexes):
            if j[1]:
                print(j[0],end=" ")

Note: replace end=" " with end="\t" to have tab-delimited output

Test run:

$ ./get_colums.py input.txt                                                                                              
er  23  23  23  
tr  24  24  24  

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.