This is a test version of Biostars. For the public version, visit https://www.biostars.org.
How to merge multiple files with different families and their count numbers for different genomes

Hi

I am trying to combine the output from protein domain search in my multiple genomes and want to combine the final results to make a heatmap based on the number of predicted domain. I have my results as following

Genome  Family  Count
A   GH1 1
A   CE9 2
A   GT2 3
A   CBM2+GH6    9
A   CBM50   4

Similarly my second file looks like

Genome  Family  Count
B   GH1 5
B   GH51    1
B   AA3 5
B   CBM2+GH6    2
B   GT2 3

and so on for around 150 genomes. I want to have my output as

    GH1    GH51 GT2   CE9    AA3    CBM2+GH6    CBM50
A     1       0   3     2          0             9                  4
B     5       1   3     0           5             2                 0

Can you please let me know the best way to do this?

protein domain
• 1,196 views • link
updated by Ram Ram • written by rishi.bhandari63 • rishi.bhandari63

1 answer

have a look at GNU datamash + crosstab https://www.gnu.org/software/datamash/

something like (not tested)

cat input*.tsv | sort -t $'\t' -k1,1 -k2,2 | datamash -s crosstab 1,2 sum 3
• 0 views • link
written by Pierre Lindenbaum Pierre Lindenbaum

that would fail due to headers (strings).

$ sed -s 1d *.txt | datamash crosstab 1,2 sum 3 --filler 0

    AA3 CBM2+GH6    CBM50   CE9 GH1 GH51    GT2
A   0   9   4   2   1   0   3
B   5   2   0   0   5   1   3
• 0 views • link
written by cpad0112 cpad0112

Log in to answer this question.

More posts like this
  • Understanding Uncommon Genotype Formats in VCF Files Generated by nf-core RNAvar Pipeline
    written by ASid • ASid
    Hello BioStars Community, I've been working with VCF files generated by the nf-core RNAvar pipeline, which employs the GATK best practices for RNA-seq variant calling. …
  • Metagenomic Data management
    written by MSRS MSRS
    I have taxonomic assignment data from the metagenomic sample. How to merge data as described below? The data > Name Group-1 Name Group-2 Name Group-3 …
  • UpsetR intersection numbers
    written by Mehmet Mehmet
    Dear All, I am using UpsetR for visualize OrthoFinder gene count results based on the command below: upset(fromList(mydata), nsets = 7, matrix.color = "red", sets.bar.color …
  • Get distance matrix based on node tree
    written by Chvatil Chvatil
    Hi, I need some help getting a phylogenetic distance matrix. Here are two examples: Example one: tree=ete3.Tree('(((A,B),C),D);') print(tree) /-A /-| /-| \-B | | --| …
  • New Column based on another data frame
    written by j.lunger18 • j.lunger18
    Hi, I am trying to apply this problem to a data frame with variants so that I can say within which domain each variant is …
  • combine columns from multiple files
    written by bk11 • bk11
    I have more than 100 tab delimited files with seven different columns in each files. I was to get a single file with first and …
  • How to create the taxonomy file for RDP Classifier?
    written by muraliadithya315 • muraliadithya315
    The RDP classifier requires a custom taxid file if the user has to train the classifier with a custom database. I would greatly appreciate if …
  • R: compare 1 columns from two matrices and get the output if they are the same
    written by IrK IrK
    Hi guys, assume I have two matrices, and I want to get the third, C. If value of the first column of A is the …
  • searching reads with a certain sequence in fastq file
    written by sumithrasank75 sumithrasank75
    I have a file say master.fastq which looks like : ``` @M00990:202:000000000-ADM27:1:1101:21678:1536 2:N:0:291 CCTTTTACCGACCCGCTCTTTCTCTCCTACGCTTATTTCCGTCTACCCTTCTCTTCACTCGCTATTTCTATTCTTAAAACTATCTTAATGTTCTGCCTTTGCTCTTTTCTTTTTTCTATAACCTCTCTACAGCCAACTCACCCATCTCCTTCCCTGCTACGCTATTCCTCTGTTAGTTTTTCTTCATCATACTTTTCTCATCTCACACTACCTTTGCACTTCTTCCTTTCCACGTCCCCTTTCTCCTACC + -----,,6;6,@+8++6+,,,<C5@,,,,,,+8,,;,,<,,,,,,,;,,;,,,<C,,,;,,8,,8CC,C,,,5,C,,,,99,+,+4,,,3,,9,,6,@<,,,,,,,9,,,,,,,4),,0**,,5),,5**,)59*0),,*)5,)))*,9,3,0++))5D:)))))5;+;+0)*;+*6++++),******3**50,6,+++**,0*,,31)88*0*1*5*1)0*:*7>C;3,035:0)))8).*2**.*:) @M00990:202:000000000-ADM27:1:1101:22685:1539 2:N:0:291 CTTATCACCGACTCTCTCCTTCTCTTCCAAGTTTATTTCCGACTCCCCTTATCTTCACTTGCTATTTCTATTCTTAAAACTATCTCGACCTTTCACCTTTCCCTCTTTCCTTCTTTTCTCTCCTTCTACACTCCCACCCACTCTTACTTCTTTCTTGTCACCGTTTCCATATTATACTTTCTTCTCTTACATAATTTTCTTCCTGCAAACTATTTAAGCAATCTCTTTCTTTCACCCCTTTTATCTCGCC + -----,,<;67@+B,,6,,,,;C5@,,,,,66<,,6,,<,,,66,,;,66,,;;C,,,;,4<,,<CC6C,;,,,;,,6,:,6?A9=,,+++2,5,,,9<?D,,,,,:C?,@,@,,5,?*3*,9**,,0**,*)93))4+))19*0**,,52,56+**5*03*)3)))42+2***5*+=3+,*4*2****,**2*,3,+++*0,50,,**5*****0****)5)***0**,,***3*)0)))3***0*))) ``` I want only …
  • How to extract unique mapped results from Bowtie2 bam results?
    written by zhwjmch • zhwjmch
    How to extract unique mapped results from Bowtie2 bam results? I used `samtools view -bq 1 WG.bam > unique.bam` However, my results contain 54792 lines, …