<p>I'm amazed at your tenacity in getting Excel to do what you want; that looks like many hours of work to count mismatches.</p>
<p>The hammer I use at the moment for this sort of stuff is LAST, which will do <em>local</em> alignments of sequences to references. If the number of mismatches are not too large (as would be expected for primer sequences), a local alignment will extend to the end of one of the sequences, and I expect that should work fine for your purposes.</p>
<p>I'm going to demonstrate two options, one that can be done just using Excel and web services, and one that needs command-line access. Note that these are only two ways to fetch mismatch counts; you might want to do more than that (it's not clear from your question), and there are <em>many</em> other ways. Also, this may not work for your particular circumstances, but I think it should be quick enough that it's worth a try:</p>
<h1>Option 1 - Web-based LAST</h1>
<p>In this, I use the <a href="http://lastweb.cbrc.jp/" rel="nofollow noreferrer">Web version of LAST</a> to generate alignments for the primers [as &quot;reference&quot;] mapped to the sequences [as &quot;query&quot;].</p>
<ol>
<li>To help create the mismatch (or in this case more correctly, &quot;edit distance&quot;) statistic, in the <em>Advanced Parameters</em> section, set the <code>a: gap existence cost</code> to 1, and the <code>b: gap extension cost</code> to 1.</li>
<li>Click on <code>Align!</code></li>
<li>Once the Alignment Results appear, click on the <code>Alignments in tabular format</code> link down the bottom, and save the file to your computer as <code>last.tab.csv</code> (the <code>.csv</code> at the end is the most important bit, to make sure that Excel recognises it as something it can open).</li>
<li>Open that file in Excel, set the delimiter to <code>&lt;tab&gt;</code></li>
<li>The first column should be the alignment score, which in this case is the <em>match</em> score. This can be converted into a <em>mismatch</em> score by subtracting it from the primer sequence length (column 6).</li>
</ol>
<h1>Option 2 - Local LAST Installation</h1>
<p><strong>Installation</strong></p>
<p>Install <a href="http://last.cbrc.jp/doc/last.html" rel="nofollow noreferrer">LAST</a> and <a href="http://www.htslib.org/download/" rel="nofollow noreferrer">samtools</a>.</p>
<p><strong>Index Generation</strong></p>
<p>The first step is to generate a reference index. LAST doesn't care too much about what's query and what's reference, but because I want all the genome sequences to appear on each line, the genome sequences will be the <em>query</em>, and the primer sequences will be the <em>target</em>. I use Martin Frith's <a href="https://github.com/mcfrith/last-rna/blob/master/last-long-reads.md" rel="nofollow noreferrer">lastdb mantra</a> to do this:</p>
<pre><code>$ lastdb -uNEAR -R01 primers.fa primers.fa
</code></pre>
<p><strong>Mapping</strong></p>
<p>The next step is to do the actual alignment with LAST. I set the match and mismatch to be both 1 (i.e. the default for the Web version), set the gap existence and extension to be 1, change the output format to <code>tab</code>, and save the result to <code>last.tab.csv</code>:</p>
<pre><code>$ lastal -r 1 -q 1 -a 1 -b 1 primers.fa sequences.fa -f tab &gt; last.tab.csv
</code></pre>
<p><strong>Aggregating data</strong></p>
<p>Finally, it's a matter of aggregating the count information. I use <code>grep -v</code> to exclude lines that begin with a <code>#</code>, use <code>awk</code> to subtract field 1 from field 6, then <code>sort</code> the results and use <code>uniq -c</code> to count the mismatches:</p>
<pre><code><span class="math-container">$ grep -v '^#' last.tab.csv | awk '{print ($6 - $</span>1)}' | sort | uniq -c
2118 0
   5 1
   5 2
</code></pre>

CC BY-SA 4.0
2020-06-24T03:37:08.223Z
2020-06-24T07:01:50.107Z
2020-06-24T07:01:50.107Z
gringer
<p>I'm working with primer analysis and design, and so far I've performing it in Excel but: a) I don't like Excel, b) I do it manually, c) I don't like Excel, d) it gets tedious when working with thousands of sequences and tens of primers. My analysis consists of establishing how many sequences have mismatches on each position of my primers, and also count how many mismatches each sequence has with the corresponding primers (see attached image). This last analysis I've been able to (partially) solve it by using ncbi-blast+ tool. However, <strong>I will find very helpful a program/script where you input your sequences and primers fasta files and retrieve the number of mismatches at every primer position. Does anyone know something that could help me achieve all this?</strong> If it also has an output tab-separated file as the one attached that would be perfect for easy visualization of results. Suggestion/orientations are also grateful! I just want to know if there's something to help me out, otherwise I'll try writing a script to automatize my analysis. Thank you!</p>
<p><a href="https://i.sstatic.net/oIc8h.jpg" rel="nofollow noreferrer"><img src="https://i.sstatic.net/oIc8h.jpg" alt="enter image description here" /></a></p>

CC BY-SA 4.0
2020-06-24T00:48:45.530Z
Its important to note this isnt a very clear question, for example how many loci are you examining, <10 or 100s? We tend to like knowing the organism and goal of the research. Anyway, at a guess I would just use NCBI's primer-blast program, here https://www.ncbi.nlm.nih.gov/tools/primer-blast/ . This appears to do everything on your list of requests
M__
CC BY-SA 4.0
2020-06-24T14:46:38.127Z
Michael, thanks for your comments! At the time, I'm working with >50000 sequences and 5 pair of primers/probes for one gene. Primer-blast does not support IUPAC nucleotide nomenclature, so that's a first limitation I've found. Also, I don't find the output information from primer-blast very helpful since it does not summarize the number of mismatches per primer position, and that's what I need in order to improve primer/probe design (I need a count of sequences rather than a list of them). Or perhaps I'm not setting correctly the output format parameters. Any further suggestions? Bests!
Gaston
<phylogenetics><blast><sequence-analysis><primer>
Do you know a program/script to count how many sequences have mismatches on each position of my primers?
# Do you know a program/script to count how many sequences have mismatches on each position of my primers?

(Asked by: username_0 on 2020-06-24)

I'm working with primer analysis and design, and so far I've performing it in Excel but: a) I don't like Excel, b) I do it manually, c) I don't like Excel, d) it gets tedious when working with thousands of sequences and tens of primers. My analysis consists of establishing how many sequences have mismatches on each position of my primers, and also count how many mismatches each sequence has with the corresponding primers (see attached image). This last analysis I've been able to (partially) solve it by using ncbi-blast+ tool. However, **I will find very helpful a program/script where you input your sequences and primers fasta files and retrieve the number of mismatches at every primer position. Does anyone know something that could help me achieve all this?** If it also has an output tab-separated file as the one attached that would be perfect for easy visualization of results. Suggestion/orientations are also grateful! I just want to know if there's something to help me out, otherwise I'll try writing a script to automatize my analysis. Thank you!

[![enter image description here](https://i.sstatic.net/oIc8h.jpg)](https://i.sstatic.net/oIc8h.jpg)

**Tags:** phylogenetics, blast, sequence-analysis, primer
> **Comments:**
> **username_1**: Its important to note this isnt a very clear question, for example how many loci are you examining, <10 or 100s? We tend to like knowing the organism and goal of the research. Anyway, at a guess I would just use NCBI's primer-blast program, here https://www.ncbi.nlm.nih.gov/tools/primer-blast/ . This appears to do everything on your list of requests  
> **username_0**: Michael, thanks for your comments! At the time, I'm working with >50000 sequences and 5 pair of primers/probes for one gene. Primer-blast does not support IUPAC nucleotide nomenclature, so that's a first limitation I've found. Also, I don't find the output information from primer-blast very helpful since it does not summarize the number of mismatches per primer position, and that's what I need in order to improve primer/probe design (I need a count of sequences rather than a list of them). Or perhaps I'm not setting correctly the output format parameters. Any further suggestions? Bests!  

---

## 1 answers

### Answer by username_2 on 2020-06-24. Score: 1

I'm amazed at your tenacity in getting Excel to do what you want; that looks like many hours of work to count mismatches.
The hammer I use at the moment for this sort of stuff is LAST, which will do local alignments of sequences to references. If the number of mismatches are not too large (as would be expected for primer sequences), a local alignment will extend to the end of one of the sequences, and I expect that should work fine for your purposes.
I'm going to demonstrate two options, one that can be done just using Excel and web services, and one that needs command-line access. Note that these are only two ways to fetch mismatch counts; you might want to do more than that (it's not clear from your question), and there are many other ways. Also, this may not work for your particular circumstances, but I think it should be quick enough that it's worth a try:
Option 1 - Web-based LAST
In this, I use the Web version of LAST to generate alignments for the primers [as "reference"] mapped to the sequences [as "query"].

To help create the mismatch (or in this case more correctly, "edit distance") statistic, in the Advanced Parameters section, set the a: gap existence cost to 1, and the b: gap extension cost to 1.
Click on Align!
Once the Alignment Results appear, click on the Alignments in tabular format link down the bottom, and save the file to your computer as last.tab.csv (the .csv at the end is the most important bit, to make sure that Excel recognises it as something it can open).
Open that file in Excel, set the delimiter to 
The first column should be the alignment score, which in this case is the match score. This can be converted into a mismatch score by subtracting it from the primer sequence length (column 6).

Option 2 - Local LAST Installation
Installation
Install LAST and samtools.
Index Generation
The first step is to generate a reference index. LAST doesn't care too much about what's query and what's reference, but because I want all the genome sequences to appear on each line, the genome sequences will be the query, and the primer sequences will be the target. I use Martin Frith's lastdb mantra to do this:
$ lastdb -uNEAR -R01 primers.fa primers.fa

Mapping
The next step is to do the actual alignment with LAST. I set the match and mismatch to be both 1 (i.e. the default for the Web version), set the gap existence and extension to be 1, change the output format to tab, and save the result to last.tab.csv:
$ lastal -r 1 -q 1 -a 1 -b 1 primers.fa sequences.fa -f tab > last.tab.csv

Aggregating data
Finally, it's a matter of aggregating the count information. I use grep -v to exclude lines that begin with a #, use awk to subtract field 1 from field 6, then sort the results and use uniq -c to count the mismatches:
$ grep -v '^#' last.tab.csv | awk '{print ($6 - $1)}' | sort | uniq -c
2118 0
   5 1
   5 2


---

