This is a test version of Biostars. For the public version, visit https://www.biostars.org.
command for to know the count of matching and not matching

I have a file in which two-column present one is read and the other is circBase.

      read              circBase
hsa_circ_0000160    hsa_circ_0000160
hsa_circ_0000160    hsa_circ_0000160
hsa_circ_0000175    hsa_circ_0000175
hsa_circ_0000175    hsa_circ_0000175
hsa_circ_0000211    hsa_circ_0017614
hsa_circ_0000211    hsa_circ_0000211
hsa_circ_0000211    hsa_circ_0000211
hsa_circ_0000219    hsa_circ_0000219
hsa_circ_0000219    hsa_circ_0000219
hsa_circ_0000219    hsa_circ_0000219
hsa_circ_0000236    hsa_circ_0000236

I want to make a file with 3 columns in which the 1st column is a unique read name from the above read column The 2nd column is the count of the match with each other and the third column is the count of which does not match the first column. The below is an example of the output file which I want.

read    match   not_match
hsa_circ_0000160    2   0
hsa_circ_0000175    2   0
hsa_circ_0000211    2   1
hsa_circ_0000219    3   0
hsa_circ_0000236    1   0

I search for it but I can't find out how can I do this. So please can anyone help me with this issue. Thanks in advance

command
• 2,183 views • link
updated by GenoMax GenoMax • written by harry • harry

1 answer

A solution with csvtk (please use binaries of this link)

$ cat t.tsv \          
    | csvtk mutate2 -t -n match     -L 0 -e '$read == $circBase ? 1 : 0' \
    | csvtk mutate2 -t -n not_match -L 0 -e '$read == $circBase ? 0 : 1' \
    | csvtk summary -t -g read -n 0 -f match:sum -f not_match:sum \
    | csvtk rename2 -t -f -read -p ':sum'

read    match   not_match
hsa_circ_0000160        2       0
hsa_circ_0000175        2       0
hsa_circ_0000211        2       1
hsa_circ_0000219        3       0
hsa_circ_0000236        1       0
• 0 views • link
written by shenwei356 shenwei356

My t.tsv file looks like:

read,circBase
hsa_circ_0000160,hsa_circ_0000160
hsa_circ_0000175,hsa_circ_0000175
hsa_circ_0000175,hsa_circ_0000175
hsa_circ_0000211,hsa_circ_0017614
hsa_circ_0000211,hsa_circ_0000211
hsa_circ_0000211,hsa_circ_0000211
hsa_circ_0000219,hsa_circ_0000219
hsa_circ_0000219,hsa_circ_0000219
hsa_circ_0000219,hsa_circ_0000219
hsa_circ_0000236,hsa_circ_0000236

I used below command and showing some error:

cat t.tsv | /home/aclab/csvtk mutate2 -t -n match -L 0 -e '$read == $circBase ? 1 : 0'| /home/aclab/csvtk mutate2 -t -n not_match -L 0 -e '$read == $circBase ? 0 : 1' | /home/aclab/csvtk summary -t -g read -n 0 -f match:sum -f not_match:sum| /home/aclab/csvtk rename2 -t -f -read -p ':sum'

After this command run then it shows the below error:-

[ERRO] column "read" not existed in file: - [ERRO] xopen: no content [ERRO] xopen: no content [ERRO] xopen: no content

Can you please sort out this issue? Thanks in advance.

• 0 views • link
written by harry • harry

Just remove all -t for CSV file.

-t, --tabs specifies that the input CSV file is delimited with tabs.

• 0 views • link
written by shenwei356 shenwei356

Thanks now its working

• 0 views • link
written by harry • harry

If an answer was helpful, you should upvote it; if the answer resolved your question, you should mark it as accepted. You can accept more than one if they work.
upvote_bookmark_accept

• 0 views • link
written by GenoMax GenoMax

Log in to answer this question.

More posts like this
  • Sum of the column of the CSV file
    written by harry • harry
    I have a CSV file like below and I want to sum the columns 2 and column 3 and write the total at the end …
  • grep command for fasta header
    written by harry • harry
    I used this command:-- grep -Fw -A 1 -f header.txt test.fa >test_result.fa But it extracts only 1 header, not the whole which are present in …
  • command for common between three files
    written by harry • harry
    I have three text files and I want to know the difference between the 3 files and the common between 3 files. It looks like …
  • How to match data from file 2 on 1st with deleting unmatched data
    written by shubhamkumbhar420 • shubhamkumbhar420
    I have two .tsv files, 1st file contains variant calling data in which column "gene known gene" contains all variant gene names. In my 2nd …
  • Script for making exon file
    written by harry • harry
    Hi everyone, I have an exon.fasta file which looks like --- >ENSE00002234944 TTAACTTGCCGTCAGCCTTTTCTTTGACCTCTTCTTTCTGTTCATGTGTATTTGCTGTCTCTTAGCCCAGACTTCCCGTGTCCTTTCCACCGGGCCTTTGAGAGGTCACAGGGTCTTGATGCTGTGGTCTTCATCTGCAGGTGTCTGACTTCCAGCAACTGC TGGCCTGTGCCAGGGTGCAAGCTGAGCACTGGAGTGGAGTTTTCCTGTGGAGAGGAGCCATGCCTAGAGTGGGATGGGCCATTGTTCATCTTCTGGCCCCTGTTGTCTGCATGTAACTTAATACCACAACCAGGCATAGGGGAAAGATTGGAGGAAAGATGA GTGAGAGCATCAACTTCTCTCACAACCTAGGCCA >ENSE00001746346 CAGGGCCATCAGGCACCAAAGGGATTCTGCCAGCATAGTGCTCCTGGACCAGTGATACACCCGGCACCCTGTCCTGGACACGCTGTTGGCCTGGATCTGAGCCCTGGTGGAGGTCAAAGCCACCTTTGGTTCTGCCATTGCTGCTGTGTGGAA >ENSE00002312635 CAGGGCCATCAGGCACCAAAGGGATTCTGCCAGCATAGTGCTCCTGGACCAGTGATACACCCGGCACCCTGTCCTGGACACGCTGTTGGCCTGGATCTGAGCCCTGGTGGAGGTCAAAGCCACCTTTGGTTCTGCCATTGCTGCTGTGTGGAAGTTCACTCC TGCCTTTTCCTTTCCCTAGAGCCTCCACCACCCCGAGATCACATTTCTCACTGCCTTTTGTCTGCCCAGTTTCACCAGAAGTAGGCCTCTTCCTGACAGGCAGCTGCACCACTGCCTGGCGCTGTGCCCTTCCTTTGCTCTGCCCGCTGGAGACGGTGTTTG TCATGGGCCTGGTCTGCAGGGATCCTGCTACAAAGGTGAAACCCAGGAGAGTGTGGAGTCCAGAGTGTTGCCAGGACCCAGGCACAGGCATTAGTGCCCGTTGGAGAAAACAGGGGAATCCCGAAGAAATGGTGGGTCCTGGCCATCCGTGAGATCTTCCCA GGGCAGCTCCCCTCTGTGGAATCCAATCTGTCTTCCATCCTGCGTGGCCGAGGGCCAGGCTTCTCACTGGGCCTCTGCAGGAGGCTGCCATTTGTCCTGCCCACCTTCTTAGAAGCGAGACGGAGCAGACCCATCTGCTACTGCCCTTTCTATAATAACTAA AGTTAGCTGCCCTGGACTATTCACCCCCTAGTCTCAATTTAAGAAGATCCCCATGGCCACAGGGCCCCTGCCTGGGGGCTTGTCACCTCCCCCACCTTCTTCCTGAGTCATTCCTGCAGCCTTGCTCCCTAACCTGCCCCACAGCCTTGCCTGGATTTCTAT CTCCCTGGCTTGGTGCCAGTTCCTCCAAGTCGATGGCACCTCCCTCCCTCTCAACCACTTGAGCAAACTCCAAGACATCTTCTACCCCAACACCAGCAATTGTGCCAAGGGCCATTAGGCTCTCAGCATGACTATTTTTAGAGACCCCGTGTCTGTCACTGA AACCTTTTTTGTGGGAGACTATTCCTCCCATCTGCAACAGCTGCCCCTGCTGACTGCCCTTCTCTCCTCCCTCTCATCCCAGAGAAACAGGTCAGCTGGGAGCTTCTGCCCCCACTGCCTAGGGACCAACAGGGGCAGGAGGCAGTCACTGACCCCGAGACG …
  • intersect two file
    written by harry • harry
    I have 2 transcript file - one contain only transcript id and other file contain transcript id, gene name and gene description. 1st file looks …
  • grep multiple lines form file
    written by harry • harry
    I have one output.txt file in which there are 20000 strings. see below it looks like that. It is genome coordinate file. X:10063445|10067490 X:10063445|10079804 X:10063445|10098579 …
  • compare two columns of two files
    written by Sam Sam
    Dear All I have two files of gene list in tab format, I want to compare 2nd column if 1st file file with 1st column …
  • RNA-seq: Explain STAR quantMode geneCounts values
    written by williantafsilva • williantafsilva
    **Background:** I mapped my reads against the reference genome using the *STAR* command: star --runThreadN 10 --runMode alignReads --readFilesCommand zcat --outFilterMultimapNmax 1 --outFilterMatchNmin 35 --outSAMtype …
  • Do you use anti-sens reads for differential gene expression analysis with Stranded Paired-End Libra…
    written by ZheFrench ZheFrench
    I have a Total RNA TrueSeq Illumina Stranded Paired-end Ribo0 Library. I'm using Star to get a matrice of reads count. I dont know which …