How To extract the whole string using awk?

Question

Hi I have some directories and it looks like below:

TBS890
  |___________ A.ctab
TBS345A
  |___________ A.ctab
TBS567C
  |___________ A.ctab

I'm interested in extracting some data from the A.ctab files present in all the directories. lets say A.ctab has 12 columns. I'm interested in the 6th and 12th columns. For that I'm trying to do like below, but don't get all the information.

awk 'FNR==1 { print substr(FILENAME,1,$NF) >substr(FILENAME,1,$NF)".tmp" } 
     FNR >1 { print $12 > substr(FILENAME,1,$NF)".tmp" } 
     NR==FNR{ print $6  >"first_column.tmp" }' TBS*/A.ctab

As you see in my above command I used $NF which will be the end of the FILENAME. I know this is wrong, so can anyone help me out here.

If you see in my above example, two directories are ending with the ann alphabet, but one directory doesn't. What I have to give to get the name till the end.

t_id    chr     strand  start   end     t_name  num_exons       length  gene_id gene_name       cov     FPKM
1       1       -       10060   10614   MSTRG.1.1       1       555     MSTRG.1 .       0.000000        0.000000
2       1       +       11140   30023   MSTRG.10.1      12      3981    MSTRG.10        .       2.052715        0.284182
3       1       -       11694   29342   MSTRG.11.1      8       6356    MSTRG.11        .       0.557588        0.077194
4       1       +       11869   14409   ENST00000456328.2       3       1657    MSTRG.10        DDX11L1 0.000000        0.000000
5       1       +       11937   29347   MSTRG.10.3      12      3544    MSTRG.10        .       0.000000        0.000000
6       1       -       11959   30203   MSTRG.11.2      11      4547    MSTRG.11        .       0.369929        0.051214
7       1       +       12010   13670   ENST00000450305.2       6       632     MSTRG.10        DDX11L1 0.000000        0.000000
8       1       +       12108   26994   MSTRG.10.5      10      5569    MSTRG.10        .       0.057091        0.007904
9       1       +       12804   199997  MSTRG.10.6      12      3567    MSTRG.10        .       0.000000        0.000000
10      1       +       13010   31097   MSTRG.10.7      12      4375    MSTRG.10        .       0.000000        0.000000
11      1       -       13068   26832   MSTRG.11.3      9       5457    MSTRG.11        .       0.995280        0.137788

From all the A.ctab files I want to extract only t_name and FPKM column and create a new file. In the new file the FPKM column should be the sample name. It should look like below:

t_name              TBS890          TBS345A              TBS567C
MSTRG.1.1              0            0.028181                 0
MSTRG.10.1         0.284182         0.002072             0.046302
MSTRG.11.1         0.077194         0.685535             0.105849
ENST00000456328.2      0            0.307315             0.038961
MSTRG.10.3             0            0.446015             0.009946
MSTRG.11.2         0.051214         0.053577             0.036081
ENST00000450305.2      0            0.110438             0.040319
MSTRG.10.5         0.007904             0                1.430825
MSTRG.10.6             0                0                0.221105
MSTRG.10.7             0            0.199354                 0
MSTRG.11.3         0.137788         0.004792                 0

Answer 1

In case ALL the files do have identical t_name and line count, try

$ awk '
FNR == NR       {print $6 > "COL1.TMP"
                }

FNR == 1        {FN = FILENAME
                 sub (/\/[^\/]*$/, ".TMP", FN)
                 print FN  > FN
                 next
                }

                {print $12 > FN
                }
' TBS*/A.ctab
$ paste -d"\t\t\t\n" COL1.TMP TBS*.TMP

Should that not be the case, the join utility comes to mind; but it requires sorted inputs.