0

I have multiple files named p1.bbmap.mapping.csv, p2.bbmap.mapping.csv, p3.bbmap.mapping.csv ... p3.bbmap.mapping.csv

Each file has two columns.

p1_60_length_504_cov_1.580902   12
p1_61_length_503_cov_4.457447   24
p1_62_length_500_cov_4.037534   35
p1_63_length_500_cov_1.718499   6
p5_1_length_5181_cov_48.147804  0
p5_2_length_4872_cov_28.387777  0
p5_4_length_4057_cov_39.930534  0
p5_5_length_3873_cov_30.397758  0
p5_6_length_3431_cov_43.591404  8
p5_8_length_3325_cov_10.154159  6
p5_10_length_3289_cov_30.577166 0
p5_11_length_3288_cov_48.411262 0
p5_12_length_3263_cov_28.849171 67
p5_13_length_3258_cov_16.862344 2
p5_14_length_3149_cov_24.703839 0
p5_15_length_3099_cov_329.678331    0
p5_16_length_3055_cov_34.035861 0
p5_17_length_3039_cov_29.560096 0
p5_18_length_2924_cov_22.790490 0
p5_20_length_2793_cov_13.807577 0
p5_21_length_2779_cov_35.737179 0
p5_22_length_2682_cov_23.347554 0
p5_23_length_2682_cov_17.336986 0
p5_24_length_2668_cov_23.246753 0
p5_25_length_2652_cov_46.648317 0
p5_26_length_2639_cov_9.353105  0
p5_27_length_2599_cov_20.695388 1
p5_28_length_2576_cov_28.790935 0
p5_29_length_2571_cov_14.885025 6
p5_30_length_2551_cov_26.988036 1
p5_31_length_2462_cov_10.844540 0
p5_32_length_2323_cov_22.107923 0
p5_33_length_2261_cov_41.717901 0
p5_34_length_2250_cov_34.612341 0
p5_35_length_2242_cov_7.208983  0
p5_37_length_2140_cov_15.349727 0
p8_280_length_1323_cov_4.788462 0
p8_281_length_1317_cov_21.436975    10
p8_282_length_1317_cov_13.748739    0
p8_283_length_1317_cov_5.379832 0
p8_284_length_1315_cov_10.328283    0

Number of rows is equal in all files. I want to merge them and add above each column (with numbers) name of the file (part of the name). Output should look like this:

                                    p1  p2  p3  pn
    p1_60_length_504_cov_1.580902   12  51  0   51
    p1_61_length_503_cov_4.457447   24  121 0   21
    p1_62_length_500_cov_4.037534   35  151 0   51
    p1_63_length_500_cov_1.718499   6   5418    0   4
    p5_1_length_5181_cov_48.147804  0   0   0   1
    p5_2_length_4872_cov_28.387777  0   0   1561    0
    p5_4_length_4057_cov_39.930534  0   0   151 0
    p5_5_length_3873_cov_30.397758  0   0   0   0
    p5_6_length_3431_cov_43.591404  8   48  0   0
    p5_8_length_3325_cov_10.154159  6   0   2132    0
    p5_10_length_3289_cov_30.577166 0   21  0   0
    p5_11_length_3288_cov_48.411262 0   0   0   0
    p5_12_length_3263_cov_28.849171 67  0   0   0
    p5_13_length_3258_cov_16.862344 2   0   1521    0
    p5_14_length_3149_cov_24.703839 0   0   0   0
    p5_15_length_3099_cov_329.678331    0   0   0   15
    p5_16_length_3055_cov_34.035861 0   11  0   0
    p5_17_length_3039_cov_29.560096 0   0   115 121
    p5_18_length_2924_cov_22.790490 0   0   1251    0
    p5_20_length_2793_cov_13.807577 0   0   0   15
    p5_21_length_2779_cov_35.737179 0   0   0   0
    p5_22_length_2682_cov_23.347554 0   0   0   0
    p5_23_length_2682_cov_17.336986 0   6   151 0
    p5_24_length_2668_cov_23.246753 0   0   0   0
    p5_25_length_2652_cov_46.648317 0   0   0   0
    p5_26_length_2639_cov_9.353105  0   0   0   151
    p5_27_length_2599_cov_20.695388 1   0   0   0
    p5_28_length_2576_cov_28.790935 0   0   0   0
    p5_29_length_2571_cov_14.885025 6   0   0   0
    p5_30_length_2551_cov_26.988036 1   0   0   0
    p5_31_length_2462_cov_10.844540 0   0   0   0
    p5_32_length_2323_cov_22.107923 0   0   0   0
    p5_33_length_2261_cov_41.717901 0   0   0   0
    p5_34_length_2250_cov_34.612341 0   18  0   0
    p5_35_length_2242_cov_7.208983  0   0   0   0
    p5_37_length_2140_cov_15.349727 0   0   0   0
    p8_280_length_1323_cov_4.788462 0   0   0   0
    p8_281_length_1317_cov_21.436975    10  0   0   0
    p8_282_length_1317_cov_13.748739    0   0   0   0
    p8_283_length_1317_cov_5.379832 0   0   0   0
    p8_284_length_1315_cov_10.328283    0   0   0   0

Any ideas?

0

This seems to do the trick:

$ printf "\t"; find input? | xargs printf "%s\t"; echo "";  paste input? | awk 'BEGIN { OFS="\t" } {printf "%s%s", $1,OFS; for( i=2;i<=NF;i++) { if( $1 != $i ) { printf "%s%s", $i, OFS } } printf "\n" }'
    input1  input2  input3
p1_61_length_503_cov_4.457447   24  24  24
p1_62_length_500_cov_4.037534   35  35  35
p1_63_length_500_cov_1.718499   6   6   6
p5_1_length_5181_cov_48.147804  0   0   0
p5_2_length_4872_cov_28.387777  0   0   0
p5_4_length_4057_cov_39.930534  0   0   0

The columns here are tab-separated for easy processing; making the columns line up vertically is relatively trivial from here.

The actual awk script:

BEGIN { 
   OFS="\t" 
} 

{
   printf "%s%s", $1,OFS;
   for( i=2;i<=NF;i++) { 
      if( $1 != $i ) { 
         printf "%s%s", $i, OFS
      }
    } 
    printf "\n"
}
  • Sorry, I dont understand. What should I put insttead "find input?" and "paste input"?? – k_a_r_o_l Jun 11 '18 at 11:13
  • find input? is a command which will execute find to locate all files matching the glob 'input?. There are three files in my example case that match that glob: input1, input2, and input3. The same thing is done with the paste command, only rather than spitting out just file filenames (which find does for the header), it spits out the requested data from the files with the awk script. – DopeGhoti Jun 11 '18 at 16:00

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.