Conditional Formatting with Pandas and Python | Excel Hacks

[@iubob98]

WOW!!!! i spent almost 3 days at the office trying to figure out how to style only selected columns in df. I TRIED EVERYTHING. I should've just watched your video. could've saved me so much time. But regardless learned a lot through the process.

[@Frdy12345]

Did you try googling it?

[@iubob98]

@@Frdy12345 yeah ofc! it eventually led me here . Very good tutorial

[@robmonkriedlinger]

Just what I was looking for - thank you so much!

[@SATSifaction]

You’re welcome

[@saurabhjain507]

You should consider making more videos. Thanks for this video.

[@SATSifaction]

Thank you

[@willykitheka7618]

very useful stuff! Thanks for sharing!

[@SayanChakraborty10397]

This was really helpful. Thanks!

[@shinrafahell]

Thank you sir this tutorial is quite comprehensive!

[@asdak6027]

That was a very simple explanation. Thank you!

[@SATSifaction]

You’re welcome

[@radams7532]

Everything was great! My only issue is it will not format the $$ amounts. I dont get errors, just doesnt format. Any thoughts? I am using Spyder and checking results in Excel. Speaking to that, is there a way to check condition formatting is Spyder w/o having to export to excel? Main question is about the formatting. Thanks and again, love the videos. YOU have single handedly made me a better python user!

[@chriskeo392]

@6:19 can we do else pass? I have the row already a certain color and dont want to change it. Video is awesome!

[@JAH7070]

life saver!! thanks

[@bacchemankesacche]

yes yes I got this video !!!!!!!!!!!!!!😎😎😎😎

[@glennchiajunweng8547]

does it work for you ?

[@imothar]

Thank you for an interesting video👍 Can you do the same formatting with strings? If so, can you recommend a video or documentation on this matter?

[@sumit_singh_live]

Very well taught.

[@SATSifaction]

Thank you. Please do share with others.

[@RamHomier]

Very nice. If I study and play around with what you are doing in this video, will I be able to also format stuff like headers in different font size, bold, borders and anything that you would do to make a spreadsheet look nice in excel? The way I would probably think of doing it but not sure how is make a spread sheet with all the styles but with no data, then I would create a spreadsheet with the data and copy paste into my styled one but this defeats the "no touch" approach you mentioned.

[@SATSifaction]

It really depends on how much customization you want to do. There are parts of it you can style but the major selling point is whatever you style, you should be able to automate. The more further style the more recoding which sometimes defeats the purpose. In this example its a simple bare vanilla styling. The other thing you can do is keep this code to write to the excel spreadsheet in a plain format and then write a separate script to write that data into a new template if your template changes a lot. Hope this helps.

[@RamHomier]

@@SATSifaction Thank you for the reply. Is their a similar function to write data into an existing excel and keep the formatting of the excel spreadsheet?

[@jefralston]

If I have an existing spreadsheet that has a bunch of customization to it, can I take that spreadsheet and create a "style sheet template" from it? That way I can use this stylesheet to use as the basis for creating other sheets with identical formatting but different data? Can you point me in the correct direction for such a task?

[@coltinbarney5353]

Is it possible to kick this back into excel with the highlighting?

[@chriskeo392]

Possible to used styled and have the columns auto fit when we use to_excel?

[@Thenationhopper]

If we convert this to colourful dataframe to HTML string, it will retain the colorful properties?

[@SATSifaction]

I don’t think so but you can try and let us know

[@BrWilsonOSRS]

Nice video..thanks! Do you know if you can use RGB values for both background and text for conditional formatting with pandas?

[@shrshar99]

I want it too!

[@gd6noob]

I can not seem to have the conditional formatting to be exported into .xlsx file. The conditional formatting appears in my VS Code Jupyter notebook.

[@CalitranoN]

ya,,, but thats in Jupiter, how do you save it to the file system? The colors do not appear in the sheet.

[@aloknagar485]

One query...When I export formatting in excel using (openpyxl->.to_excel), it randomly export conditional formatting by column/field. Can you please suggest

[@ganeshgupta6201]

Can you tell how can we assign custom colours using hex values

[@suneelreddy4396]

Hi Sir your videos are very informative, a specially its useful for who learning python,Machine learning and Data science. Sir I request you, please do some videos on python libraries like Pandas and Matplotlib.

[@SATSifaction]

+Suneel Reddy thanks for the feedback. I’ll add it to my list

[@ANUP507]

Hi, nice tuto! , could you also mention the requirements for this formating feature and excel export? Which python version? panda version? excel version? I am having problem to export the formats in excel

[@deepspace]

The built-in pandas styling Excel support is pretty limited, and only supports styling that is achievable by CSS. See pandas.pydata.org/pandas-docs/stable/user_guide/style.html#Export-to-Excel. However, you can have a look at a library I maintain that attempts to bridge that gap. styleframe.readthedocs.io/en/latest/

[@erickballesteros4531]

this was helpful :)

[@jefrin]

Thank you very much!.....I am using pycharm ide. In your line df.styl.apply, I want to assign to a variable (in Pycharm) which I no need to in Jupyter. Can you pls help me over that. Other than that the code is perfect & this is all I wanted.

[@SATSifaction]

Why cant you just use it in pycharm? Not sure i fully understand the issue?

[@jefrin]

@@SATSifaction I mean the conditional formating can be visualized online but cannot be written into excel with the same ecolor coding. I tried it like, pd.df.to_excel( ). Could you please suggest any way to that? Thanks 😊

[@granand]

Please guide me from start of course so i follow & understand what I am doing ..that would be great help

[@keepcontinue]

It was helpful

[@hellofrombd1960]

Would you please explain me why should i use python for this task.i can do this in excel easily using if else condition.

[@SATSifaction]

Python scales better than excel in multiple ecosystems. It’s for that reason it’s the fastest growing language in the world right now and the top skill required for data scientists.

[@naturalboi1]

Do you have any resource you'd recommend to further learn pandas and excel with python?

[@SATSifaction]

check this playlist : kzbin.info/aero/PLM30lSIwxWOgceZawjGXHiGF0nKkq163G

[@akshathbharathi7376]

Can it be done using sublime text?

[@SATSifaction]

Yes of course

[@bc4198]

I find conditional formatting to be really shaky in Excel, where it seems to bog down and crash more frequently than without. I'm sure it's cleaner in code, but can you tell if it's more stable?

[@SATSifaction]

In my experience it’s definitely more stable in code. When you have a large excel file the rendering can take a long time on large files.

[@richardrich6461]

How if it's a string data type can you help me? Thanks

[@swingtrades4241]

how to get count of colored cells for each column

[@d.i.oluwaseyi182]

Please where is the link as promised for windows if you are getting errors when trying to confirm if python has been successfully installed on your system????

[@Magic4255]

Love pandas :)

[@SATSifaction]

So do i 😀

[@surakshapanchangam1093]

what if I want color only column names that is (category, month scale, margin, margin_rate ) .

[@imwatchingmystories]

Why did this work and not present a Styler datatype error?

[@alfonsoarayazamorano5224]

What could i do if the import excel is already under conditional formatting, is there a way for panda to read the cell color?

[@deepspace]

pandas by default does not have such a feature, but you can have a look at a library I maintain that allows that. styleframe.readthedocs.io/en/latest/ (and more specific to your question, styleframe.readthedocs.io/en/latest/styleframe.html#styleframe.StyleFrame.read_excel)

[@sungatiseev9034]

How can we do it without loop?

[@AlexMar-r]

I get can't not unpack non-iterable int object .

[@ibrahimezz4317]

Amazing

[@SATSifaction]

Thank you 🙏

[@LetLifeLiveYou]

Hello, I just wanted to know how can we transfer styled df to excel. And I have df as below A B C 1&&2. 5&&6 b 3. G. H&& And above df is dynamic I want to highlight cells having && with color in excel where I m transporting it Can you help me on it. Have tried many things but doesn't seems working.

[@deepspace]

The built-in pandas styling Excel support is pretty limited, and only supports styling that is achievable by CSS. See pandas.pydata.org/pandas-docs/stable/user_guide/style.html#Export-to-Excel. However, you can have a look at a library I maintain that attempts to bridge that gap. styleframe.readthedocs.io/en/latest/

[@martin-xq7te]

Great video but the screen was out of focus. Also the spreadsheet was too small to see. Other than that great work, I learnt a lot

[@SATSifaction]

Thanks for the feedback. Will take your feedback into consideration in future. Thanks for watching. Please consider subscribing if you haven’t already

[@chobblegobbler6671]

Hi, When writing to excel the formatting is not transferred. The colors don't appear

[@SATSifaction]

What os are you using? Version of excel?

[@chobblegobbler6671]

@@SATSifaction I am using Excel 2016 and Windows 10

[@chobblegobbler6671]

Is there any solution to this issue?

[@SATSifaction]

@@chobblegobbler6671 you're best bet would be to run this application in a virtual environment and use the same requirements. Outside of that diagnosing it like this would be extremely difficult to troubleshoot

[@chobblegobbler6671]

@@SATSifaction Ok .. Thank you!

[@AdarshKumar-bx1zn]

What is your logic behind keeping the font so small in size?? 😆

[@SATSifaction]

More of a rookie youtuber mistake than logic. :)

[@sakshiagrawal3809]

How to add dynamic borders to output excel file.