I have 1775 .txt files, and each .txt file has 3023 lines that they looks like this:

FQC-10090295         0.007813
FQC-10119363         0.023438
FQC-10132112         0.093750
FQC-10090295 0.187500
FQC-10119363 0.023438
FQC-10132112 0.039063

How do I make a matrix which represents transposed those txt files so they look like this:

                                     FQC-10090295 FQC-10119363 FQC-10132112  ...
RIBBY_g_5ZCV995_BI_SNP_D04_38774.CEL 0.007813     0.023438     0.093750  ...
UNTIL_g_3ECO791_BI_SNP_H10_36454.CEL 0.187500     0.023438     0.039063  ...
  • 1
    how many files are to be processed and what's the approximate size (in lines) of each file? – RomanPerekhrest Jul 24 at 18:59
  • there is 1775 .txt files, and each .txt file has 3023 lines – anikaM Jul 24 at 19:01
  • 1
    Whenever you have multiple input files in real life you should provide an example that shows at least 2 input files as with just 1 input file we can't see how multiple affects the output. Get rid of the ... line from the input, add a second input file (with some different FQC-... key values from those in file1 if that's possible in reality) and update the expected output. Right now I don't even know if that'll be 1 output file or 2. Also - is RIBBY_g_5ZCV995_BI_SNP_D04_38774.CEL the name of the input file or the first line of the input file? – Ed Morton Jul 24 at 22:02

Your problem contains many unspecified parameters, but as a starting point, consider this bash script:


for f in *.CEL
    if [ -z "$header" ]
        l=${#f}                 # length of filename ...
        fmt="%-${l}s"           # ... determines width of first column

        header="$(printf "$fmt" "")"    # first column of first row is blank

        for col in $(awk '{print $1}' $f)
            l=${#col}               # width of column
            [ $l -lt 8 ] && l=8     # min width of 8
            header="$header $(printf "%-${l}s" "$col")"     # append column label to header
            fmt="$fmt %-$l.6f"
        printf '%s\n' "$header"         # header is first row of output

    printf "$fmt\n" "$f" $(awk '{print $2}' $f)     # print filename and all column 2 values

Running the script gives:

                                     FQC-10090295 FQC-10119363 FQC-10132112
RIBBY_g_5ZCV995_BI_SNP_D04_38774.CEL 0.007813     0.023438     0.093750    

I copied your input file to a second filename, and re-ran:

                                     FQC-10090295 FQC-10119363 FQC-10132112
RIBBY_g_5ZCV995_BI_SNP_D04_38774.CEL 0.007813     0.023438     0.093750    
RIBBY_g_5ZCV995_BI_SNP_D04_38775.CEL 0.007813     0.023438     0.093750    

This script works by first, looping over all *.CEL files.

For the first file, the script builds a header line header based on the assumption that all the filenames will be the same length. While building a header line, the script also builds a printf format string fmt which will be used for displaying the columnar data. The first field in the format string is a string of sufficient length to display the filename.

The script next loops over all the "column 1" values in the first input file, which are the value labels. For each column, the script again takes the length of the value label (but not less than 8), and adds that label to the header. It also adds a floating-point format spec to the fmt string.

After the script has looped over all the column labels, it outputs the header string that has been constructed.

All of the above steps happen for the first file only.

The final step in processing the first file, and the only step in processing all files after the first one, is to use printf to display the filename, and the "column 2" values using the fmt string that was created during the first loop iteration. The script then continues to process the next *.CEL file, if any.

This approach assumes that the column labels in all files are the same, and that all the *.CEL filenames are the same length.

  • Hi Jim, thank you so much! I put your script in transpose.sh and run it like: sh transpose.sh > output.txt Is that how I suppose to run it if not how do I put the output in one file? – anikaM Jul 24 at 22:39
  • 1
    That command will work, so long as you are in the directory where the *.CEL files are. – Jim L. Jul 24 at 22:47

Something like this is what you need:

$ cat tst.awk
BEGIN { OFS="\t" }
FNR==1 {
    row = $1
    col = $1
    vals[row,col] = $2
    printf "%s", ""
    for (col in cols) {
        printf "%s%s", OFS, col
    print ""
    for (row in rows) {
        printf "%s", row
        for (col in cols) {
            printf "%s%s", OFS, vals[row,col]
        print ""

but without input/output that clarifies your requirements and we can test against (see my comment), it's just an untested guess.

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.