CODEWITHSUNDEEP
pythonloopsopenpyxl
vmicrobio
vmicrobio

Reputation: 341

color raws in xls with python with conditions

I would like to color raws of file.xls according to 3 parameters:

file.xls

reference   pos REF ALT qual    depth   freq    gene    mutation
BX571857.1  7716    C   A   1280.26 468 0.985294    pr209   P308T
BX571857.1  7854    T   C   3.85731e-15 410 0.031941    pr209   S354P
BX571857.1  7940    T   C   100168  531 1   pr210   N898D
BX571857.1  9942    G   A   100168  473 1   pr211   S897L

list1

mutation    gene
P308T   pr209
S354P   pr209
N898D   pr210

list2

mutation    gene
S897L   pr211

How can I do that in python? I tried something like this but couldn't achieve my goal:

import openpyxl
from openpyxl.styles import PatternFill

def Color(s, t):
    yellow = "00FFFF00"
    red = "00FF0000"
    blue = "000000FF"
            for cell in rows:
                 if 
                cell.fill = PatternFill(start_color=yellow, end_color=yellow,
                                        fill_type = "solid")
            elseif
                cell.fill = PatternFill(start_color=red, end_color=red,
                                        fill_type = "solid")
                 if 
                cell.fill = PatternFill(start_color=blue, end_color=blue,
                                        fill_type = "solid")



with open('file.xls', 'r') as input_1:
    nt = input_1.readline(
    nt = int(nt)

    for i in range(nt):
        s = input_1.readline()
        print(s)
        t = input_1.readline()
        print(t)
        Color(s, t)

thanks a lot!

Upvotes: 0

Views: 85

Answers (2)

vmicrobio
vmicrobio

Reputation: 341

By doing this:

import openpyxl
from openpyxl.styles import PatternFill

# define lists
list1 = [["P308T", "pr209"], ["S354P", "pr209"], ["N898D", "pr210"]]
list2 = [["S897L", "pr211"]]

# open workbook
wb = openpyxl.load_workbook('file.xlsx')
sheet = wb['Sheet1']

# loop through rows
for row in sheet.iter_rows():
    # skip header row
    if row[6].value != 'freq':
        # check for freq value between 0.11 and 0.5
        if float(row[6].value) >= 0.11 and float(row[6].value) <= 0.5:
            # check for list1 mutations
            for entry in list1:
                if row[7].value == entry[0] and row[8].value == entry[1]:
                    row[0].fill = PatternFill(start_color="00FFFF00", end_color="00FFFF00", fill_type="solid")
        # check for freq value between 0.51 and 1
        elif float(row[6].value) >= 0.51 and float(row[6].value) <= 1:
            # check for list1 mutations
            for entry in list1:
                if row[7].value == entry[0] and row[8].value == entry[1]:
                    row[0].fill = PatternFill(start_color="00FF0000", end_color="00FF0000", fill_type="solid")
        # check for list2 mutations
        for entry in list2:
            if row[7].value == entry[0] and row[8].value == entry[1]:
                row[0].fill = PatternFill(start_color="000000FF", end_color="000000FF", fill_type="solid")

# save workbook
wb.save('file.xlsx')

I don't have any colored rows in the final file.

Upvotes: 0

imburningbabe
imburningbabe

Reputation: 792

This is just an example on how to deal with conditional formatting:

wb = openpyxl.load_workbook('file.xlsx') 
ws = wb.worksheets[0]

def Color(s, t):
    yellow = "FFFFFF00"
    red = "00FF0000"
    blue = "000000FF"
    if s == 'C' and t == 'A': return openpyxl.styles.colors.Color(rgb=yellow) 
    if s == 'T' and t == 'C': return openpyxl.styles.colors.Color(rgb=red) 
    if s == 'G' and t == 'A': return openpyxl.styles.colors.Color(rgb=blue)  
            

for row in list(ws.rows)[1:]:
    for cell in row:
        color = Color(row[2].value,row[3].value)
        cell.fill = PatternFill(fill_type='solid',start_color=,colorend_color=color)

wb.save('file1.xlsx')

Upvotes: 1

Related Questions