This is a test version of Biostars. For the public version, visit https://www.biostars.org.
make a different color for a given amino acid

I have a xls file which I want to color some strings differently

My xlsx file is like below lets call it sample.xlsx

>sp|Q96B97|SH3K1_HUMAN SH3 domain-containing kinase-binding protein 1 OS=Homo sapiens OX=9606 GN=SH3KBP1 PE=1 SV=2
MVEAIVEFDYQAQHDDELTISVGEIITNIRKEDGGWW
>sp|Q9UBS4|DJB11_HUMAN DnaJ homolog subfamily B member 11 OS=Homo sapiens OX=9606 GN=DNAJB11 PE=1 SV=1
KLALQLHPDRNPDDPQAQEKFQDLGAAYEVLSDSEKRKQYD
>sp|P61916|NPC2_HUMAN NPC intracellular cholesterol transporter 2 OS=Homo sapiens OX=9606 GN=NPC2 PE=1 SV=1
CQLSKGQSYSVNVTFTSNIQSKSSKAVVHGILMGVP

I want to read it and then save it when I color some letters. I want to color D in a yellow color 

>sp|Q96B97|SH3K1_HUMAN SH3 domain-containing kinase-binding protein 1 OS=Homo sapiens OX=9606 GN=SH3KBP1 PE=1 SV=2
MVEAIVEF**D**YQAQH**DD**ELTISVGEIITNIRKE**D**GGWW
>sp|Q9UBS4|DJB11_HUMAN DnaJ homolog subfamily B member 11 OS=Homo sapiens OX=9606 GN=DNAJB11 PE=1 SV=1
KLALQLHPDRNP**DD**PQAQEKFQ**D**LGAAYEVLS**D**SEKRKQY**D**
>sp|P61916|NPC2_HUMAN NPC intracellular cholesterol transporter 2 OS=Homo sapiens OX=9606 GN=NPC2 PE=1 SV=1
CQLSKGQSYSVNVTFTSNIQSKSSKAVVHGILMGVP

Now I can read the xls and print them 

#!/usr/bin/perl

use strict;
use warnings;
use Excel::Reader::XLSX;

    my $reader   = Excel::Reader::XLSX->new();
    my $workbook = $reader->read_file( 'sample.xlsx' );
    if ( !defined $workbook ) {
        die $reader->error(), "\n";
    }
    for my $worksheet ( $workbook->worksheets() ) {
        my $sheetname = $worksheet->name();
        print "Sheet = $sheetname\n";
        while ( my $row = $worksheet->next_row() ) {
            while ( my $cell = $row->next_cell() ) {
                my $row   = $cell->row();
                my $col   = $cell->col();
                my $value = $cell->value();
                print "  Cell ($row, $col) = $value\n";
            }
        }
    }

#Sheet = Sheet1
#  Cell (0, 0) = >sp|Q96B97|SH3K1_HUMAN SH3 domain-containing kinase-binding protein 1 OS=Homo sapiens OX=9606 GN=SH3KBP1 PE=1 SV=2
#  Cell (1, 0) = MVEAIVEFDYQAQHDDELTISVGEIITNIRKEDGGWW
#  Cell (2, 0) = >sp|Q9UBS4|DJB11_HUMAN DnaJ homolog subfamily B member 11 OS=Homo sapiens OX=9606 GN=DNAJB11 PE=1 SV=1
#  Cell (3, 0) = KLALQLHPDRNPDDPQAQEKFQDLGAAYEVLSDSEKRKQYD
#  Cell (4, 0) = >sp|P61916|NPC2_HUMAN NPC intracellular cholesterol transporter 2 OS=Homo sapiens OX=9606 GN=NPC2 PE=1 SV=1
#  Cell (5, 0) = CQLSKGQSYSVNVTFTSNIQSKSSKAVVHGILMGVP

Now here I want to focus on the Cell(1,0) and Cell (3, 0) and Cell (5, 0)

perl
• 3,969 views • link
updated by Joe Joe • written by Learner Learner

@Asaf I converted the Fasta to xlsx , it is because I am doing some other analysis. No I am not interested in coloring them in terminal. I want to save them with another color in xlsx

• 0 views • link
written by Learner Learner

would an R-based solution also work for you? The openxlsx package is great for formatting, see their exhaustive set of examples.

My guess is your code may look something along those lines (taken from the documentation linked above):

wb <- createWorkbook()
addWorksheet(wb, "cellIs")

# add data without styling
writeData(wb, "cellIs", -5:5)
writeData(wb, "cellIs", LETTERS[1:11], startCol=2)

# styling
negStyle <- createStyle(fontColour = "#9C0006", bgFill = "#FFC7CE") # define the colors
conditionalFormatting(wb, "cellIs", cols=1, rows=1:11, rule="!=0", style = negStyle) # define the condition for applying the style you just defined. Here the rule is that there shouldn't be a zero, in your case you may want to require letters
• 0 views • link
written by Friederike Friederike

irrespective of this, it may actually be easier to write a VBA script, like this one here or here. That way you won't have to deal with reading in and writing out etc.

I also thought this solution described here sounded nifty:

IF you had a consistent word you were looking for, you could create another column (and hide if needed) and use the formula =Find("Text",CellName) command to find a specific text in another cell. It would return a number of where that word starts. You could then conditional format based on if you received a number back or if it gave you a zero, the word is not there.

• 0 views • link
written by Friederike Friederike

@Friederike R could be a good choice, can you put more info there? a better script , it has no start no end ;-)

• 0 views • link
written by Learner Learner

well, the start would be something like:

library(openxslx)
indata <- read.xlsx(xlsxFile, sheet = 1, startRow = 1, colNames = TRUE, rowNames = FALSE)

I'm actually not 100% sure if that would allow you to specify part of a string within a cell to be colored because I have a feeling that openxlsx mostly reproduces the formula capabilities of Excel (which do not seem to allow for conditional formatting of parts of strings within the same cell). I would, in fact, suggest to look into an Excel-based solution to this unless you have compelling reasons not to use those.

• 0 views • link
written by Friederike Friederike

@Friederike this package is not even available for unix!!! use library("xlsx") if you can please

• 0 views • link
written by Learner Learner

sorry, you will need to install the library first, of course:

install.packages("openxlsx")

Works fine on my unix server

• 0 views • link
written by Friederike Friederike

@Friederike I just ran your code! then what ? it is doing nothing ? what are you trying to do with R exactly ? I am a bit confused

• 0 views • link
written by Learner Learner

Please, please, don't use Excel.

• 0 views • link
written by Joe Joe

@jrj.healey ok, lets do it in text , what is your solution ?

• 0 views • link
written by Learner Learner

what do you want to achieve with the coloring?

• 0 views • link
written by Friederike Friederike

@Friederike part of a code that I am writing

• 0 views • link
written by Learner Learner

I can give you a solution, but it won’t be in Perl... (see answer below).

• 0 views • link
written by Joe Joe

1 answer

Here’s a bash Function I keep handy:

quickcolor(){
 sed -e "s/A/$(tput setaf 1)A$(tput sgr0)/g" \
     -e "s/T/$(tput setaf 2)T$(tput sgr0)/g" \
     -e "s/C/$(tput setaf 3)C$(tput sgr0)/g" \
     -e "s/G/$(tput setaf 4)G$(tput sgr0)/g" $1 | cat
}

I can’t remember off then top of my head which nucleotide gets which colour, but you can change the number given to setaf to tweak it (it will need some modification to read a fasta/ignore headers)

Edit:

Sorry just noticed this thread was about amino acids. The same approach will work, you'll just have to modify the substitutions to suit.

Edit II:

Incorporated Asaf's addressing of headers, and added the ability to pass multiple fastas at once (e.g. $ quickcolor *.fasta)

quickcolor(){
 for i in "$@" ; do
  sed -e "/^>/!s/A/$(tput setaf 1)A$(tput sgr0)/g" \
      -e "/^>/!s/T/$(tput setaf 2)T$(tput sgr0)/g" \
      -e "/^>/!s/C/$(tput setaf 3)C$(tput sgr0)/g" \
      -e "/^>/!s/G/$(tput setaf 4)G$(tput sgr0)/g" "$i" | cat
 done
}
• 0 views • link
written by Joe Joe

I think this one should receive "post of the year".

• 0 views • link
written by cschu181 cschu181

Added to my .bashrc should be handy. And added a condition to avoid fasta headers:

quickcolor(){
 sed -e "/^>/!s/A/$(tput setaf 1)A$(tput sgr0)/g" \
 -e "/^>/!s/T/$(tput setaf 2)T$(tput sgr0)/g" \
 -e "/^>/!s/C/$(tput setaf 3)C$(tput sgr0)/g" \
 -e "/^>/!s/G/$(tput setaf 4)G$(tput sgr0)/g" $1 | cat
 }
• 0 views • link
written by Asaf Asaf

That's the syntax I was looking for! ;)

• 0 views • link
written by Joe Joe

Log in to answer this question.

More posts like this