-1

Is there a way to color specific phrases in pandas dataframe when I export them to Excel? PS. Im want to have colored words in my .Excel file. I have dataframe like this:

import pandas as pd

my_words = ["great", "I like that", "fucking you", "having a good time", "you are idiot"]

df = pd.DataFrame({"my words":my_words})
df.to_csv("exported csv file.csv")

I want to have red colour for words "fucking you" and "idiot". So these 2 words should have red colour in my Excel file, not entire cells.

taga
  • 3,537
  • 13
  • 53
  • 119
  • 3
    CSV is a text format. It does not support colors. – DYZ Aug 23 '20 at 22:11
  • You can still use [ANSI color codes](https://stackoverflow.com/questions/37170990/is-there-a-list-of-ansi-color-escape-codes-somewhere-in-the-standard-libraries) or [HTML font coloring](https://www.tutorialspoint.com/How-to-set-font-color-in-HTML) but do not expect CSV readers to understand them off the shelf. – DYZ Aug 23 '20 at 22:19
  • You are probably better adding another column with a boolean flag, for example: `df['is_insult'] = df['my words'].isin(list_of_insulting_words)`. You could later use that flag with some macro in spreadsheet software that you use to view the csv, so that it renders the words in red where it is set to `True`. – alani Aug 23 '20 at 22:41
  • @DYZ Is there a way to do it with excel? – taga Aug 23 '20 at 22:48

1 Answers1

2
def change_color(a):
    d = {'fuck':'red', 'idiot':'red', 'great':'green'}
    d1 = {k: 'background-color:' + v for k, v in d.items()}
    tdf = pd.DataFrame(index=a.index, columns=a.columns)
    tdf = a.applymap(d1.get).fillna('')
    return tdf

df.style.apply(change_color, axis=None).to_excel('colored_excel.xlsx', engine='openpyxl', index=False)

Update based on comment:

bad_word = ["Fuck", "Fucking", "Idiot", "Monkey"]

d = dict.fromkeys(bad_word , "red")
AtanuCSE
  • 8,832
  • 14
  • 74
  • 112
  • But what If i have 1500 words, I cant make dict that you called 'd' – taga Aug 23 '20 at 22:48
  • @taga You have 1500 word list that need to marked as RED? – AtanuCSE Aug 23 '20 at 22:51
  • @taga updated my answer. You can provide as much as word as you want within 'bad_word' to create the dictionary 'd'. – AtanuCSE Aug 23 '20 at 22:55
  • 1
    But is there a way to color text, just the word not entire cell. I mean, I gave example with one word, but what if I have sentences and I want to mark only one word? – taga Aug 24 '20 at 09:27