Conditional Formatting with Pandas and Python | Excel Hacks

Подписаться 19 тыс.

Просмотров 38 тыс.

50% 1

Learn how to perform conditional formatting by using pandas. Learn how to style multiple stylesheets and export the result to excel. Process several excel sheets with ease and speed.
Learn how to build a machine learning API that connects to a back end machine learning model and returns a response based on feedback from the ML model.
👉 Patreon: patreon.com/SATSifaction
👉 Facebook Group: / theaiwarriors
👉 Instagram: @theaiwarriors
👉 Corporate Training and Up skilling: levers.ai
Netfirms (Affiliate) - bit.ly/2KdJ4Dp
Linode Server - bit.ly/2XpqGi9
Bluehost (Affiliate) - bit.ly/2GxxBh1
PythonAnywhere (Affiliate) - bit.ly/2kWORVe
Heroku - www.heroku.co
NordVPN (Affiliate) - bit.ly/2W87je0
✅ Here is a link to my python for beginners, master python course: bit.ly/2HIZS42
What Does a Data Science Executive Carry Around in 2019?
Bag - amzn.to/2HfHkJo (will fit laptop plus one tablet and a few accessories)
Laptop - 2018 MacBook Pro with Touch Bar
Tablet - 2017 9.7” iPad or
Tablet - Amazon Fire HD 10 - amzn.to/2HjeLec
Folding Keyboard - amzn.to/2JLMbE7
Power Bank - amzn.to/2VsNASc
Bose QC35 - amzn.to/2LEpqEx
Home Setup for more intense analysis and editing:
AMD MSI Vega 56 Graphics Card - amzn.to/2HkiJU1
Razor X Core egpu with thunderbolt 3 - amzn.to/2Hix579
Two LG 29” Ultrawide Monitors - amzn.to/2YtZfSi
Other options to run Python on a budget
Raspberry Pi Starters kit - amzn.to/2Q4lzPE
Any inexpensive Android Tablet (all Fire tablets with playstore side loaded)
Minimalist Keyboard and Mouse - amzn.to/2Q2w2ek
Favourite Mobile Apps to Run Python and Code
iOS - Pythonista (paid) - apple.co/2HjRVTJ
Android - Dcoder (free) - bit.ly/2Vrjb6N
Music:
Finally by Loxbeats / loxbeats
Creative Commons - Attribution 3.0 Unported - CC BY 3.0
Free Download: bit.ly/FinallyLoxbeats
Music promoted by Audio Library • Finally - Loxbeats (No...

Хобби

Опубликовано:

11 авг 2019

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 93

@robmonkriedlinger 3 года назад

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

@SATSifaction 3 года назад

You’re welcome

@SayanChakraborty10397 2 года назад

This was really helpful. Thanks!

@willykitheka7618 2 года назад

very useful stuff! Thanks for sharing!

@shinrafahell Год назад

Thank you sir this tutorial is quite comprehensive!

@asdak6027 Год назад

That was a very simple explanation. Thank you!

@SATSifaction Год назад

You’re welcome

@JAH7070 3 года назад

life saver!! thanks

@saurabhjain507 3 года назад

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

@SATSifaction 3 года назад

Thank you

@iubob98 3 года назад

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.

@Lnd2345 2 года назад

Did you try googling it?

@iubob98 2 года назад

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

@sumit_singh_live 3 года назад

Very well taught.

@SATSifaction 3 года назад

Thank you. Please do share with others.

@erickballesteros4531 3 года назад

this was helpful :)

@EmirTT Год назад

It was helpful

@imothar 3 года назад

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?

@suneelreddy4396 4 года назад

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 4 года назад

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

@bacchemankesacche 2 года назад

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

@glennchiajunweng8547 2 года назад

does it work for you ?

@ibrahimezz4317 4 года назад

Amazing

@SATSifaction 4 года назад

Thank you 🙏

@jefralston 3 года назад

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?

@ganeshgupta6201 2 года назад

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

@chriskeo392 5 месяцев назад

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

@sakshiagrawal3809 2 года назад

How to add dynamic borders to output excel file.

@richardrich6461 3 года назад

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

@chriskeo392 5 месяцев назад

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

@granand 3 года назад

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

@BrWilsonOSRS 3 года назад

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

@shrshar99 3 года назад

I want it too!

@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!

@surakshapanchangam1093 3 года назад

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

@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.

@coltinbarney5353 2 года назад

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

@swingtrades4241 2 года назад

how to get count of colored cells for each column

@RamHomier 3 года назад

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 3 года назад

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 3 года назад

@@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?

@aloknagar485 2 года назад

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

@Magic4255 3 года назад

Love pandas :)

@SATSifaction 3 года назад

So do i 😀

@CalitranoN 3 года назад

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

@hellofrombd1960 4 года назад

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 4 года назад

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.

@imwatchingmystories 2 года назад

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

@AlexMar-r 3 года назад

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

@alfonsoarayazamorano5224 4 года назад

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 3 года назад

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)

@ANUP507 4 года назад

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 3 года назад

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/

@sungatiseev9034 3 года назад

How can we do it without loop?

@naturalboi1 4 года назад

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

@SATSifaction 4 года назад

check this playlist : ru-vid.com/group/PLM30lSIwxWOgceZawjGXHiGF0nKkq163G

@d.i.oluwaseyi182 2 года назад

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????

@LetLifeLiveYou 4 года назад

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 3 года назад

@bc4198 2 года назад

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 2 года назад

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.

@jefrin 2 года назад

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 2 года назад

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

@jefrin 2 года назад

@@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 😊

@Thenationhopper 2 года назад

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

@SATSifaction 2 года назад

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

@akshathbharathi7376 2 года назад

Can it be done using sublime text?

@SATSifaction 2 года назад

Yes of course

@martin-xq7te 2 года назад

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 2 года назад

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

@AdarshKumar-bx1zn 2 года назад

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

@SATSifaction 2 года назад

More of a rookie youtuber mistake than logic. :)

@chobblegobbler6671 4 года назад

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

@SATSifaction 4 года назад

What os are you using? Version of excel?

@chobblegobbler6671 4 года назад

@@SATSifaction I am using Excel 2016 and Windows 10

@chobblegobbler6671 4 года назад

Is there any solution to this issue?

@SATSifaction 4 года назад

@@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 4 года назад

@@SATSifaction Ok .. Thank you!