Add color to certain cells in Excel via pandas - python

Question

I want to add a highlight specific cells in a .CSV file using the highlight_special function.

The code runs in the terminal with no exceptions but when I look at the .CSV it stays the same

The code takes a csv file runs it to see if there are any words with special characters and then if there are it adds themes to the siders list.

The siders list is then iterated over in order to highlight cells that contain text that is in the siders list.

Thanks in advance.

import pandas as pd
#import numpy as np 4 LATER
import os

# get the file path
dfile = input("please enter the name of the file you wish ro analyse plus the type(.csv/.xls/.bat): ")
dfile = os.getcwd() + "\\" + dfile
# list of the words with the special letters
siders = []
# special letters list
special_characters = ["\\", ",", "-", "_", "+", ".", "?", "\\", "#", "*", "&", "!", "'", "\""]


# analasys function
def special(data, filter_col):
    # loads the file as a csv
    global datafile
    datafile = pd.read_csv(data)
    # iterates the file line by line plus stating the number of line
    for row, i in datafile.iterrows():
        # tlowercase the column indicated by [filter_col
        lowi = str(i[filter_col]).lower()
        # looks for a special letter in lowi stated..
        for chr in special_characters:
            if chr in lowi:
                siders.append(lowi)  # adds the words with special letters to a side list
                print("succes special character {} found in row {}".format(chr, str(row)))
            else:
                continue
                # print("{} no special chars where found".format(str(row)))
    count = 0
    for index, word in enumerate(siders):
        count += 1
        print(str(index) + " " + word + "\n ")  # prints the special woprds
    print("count of words that need manual review is: {}".format(count))


def highlight_special(cells):  # cells=datafile
    for each in cells:
        if each in siders:
            return ['background_color: yellow']
        else:
            return ['background_color: white']
    datafile.style.apply(highlight_special, axis=1)


def duplicants(datafile):
    pass


highlight_special(dfile)
special(dfile, 'Account Name')

Answer 1

When you call highlight_special(), siders is still empty. You have to call your method special()before.

highlight_special also misused (see here), and it is calling itself in the datafile.style.apply.

Also, you're using global variables and you are settings them in the function. It won't work unless you are doing something like this (see the doc):

x = ""
def myfunc():
  global x
  x = "fantastic"

myfunc()

---

here is a working example with of colouring an excel file with applymap

siders = [1]

df = pd.DataFrame([{'value': 1, "value_2": 913}])
def highlight_cells(value):
    color = "yellow" if value in siders else "white"
    return f"background-color: {color}"

writer = pd.ExcelWriter(f"/tmp/test.xlsx", engine="xlsxwriter")
df2 = df.style.applymap(highlight_cells)
df2.to_excel(writer)
writer.save()