Python is gifted with an variety of tools to optimise the cosmetics of the data analysed. One among them is the Styler which shall be explored to its lengths & breadths in this article. We shall narrow down particularly to how these styling are transferred from Python to an Excel spreadsheet.
Styler object is very effective for formatting and displaying the dataframe to the extent that the untrained eyes may be deceived that they are looking at an Excel spreadsheet. The appearance is so compelling!
Most of us working with MS Excel won’t be finding it as a big news, when someone tells that one can tailor the sheets or cells by adding colours to it or highlight data of importance or do something similar to suit our needs. This article shall explain the same, but by using the styler from a python to bend the MS Excel aesthetics to its will.
Getting started with Styling
We need to use an accessor to unravel the potential of all the styling properties for the pandas dataframe. Also it helps to modify the styler object which spreads its controlling the nuances of how a dataframe is being displayed. Following are the different styling that shall be attempted in this article:
- Highlighting minimum values
- Highlighting maximum values
- Highlighting null values
- Colouring Table borders & textual data
- Truncating the decimals displayed
- Hiding the Index of Dataframe
- Writing the style into an Excel
Highlight Minimum Values
df.style.highlight_min()
The above code shall be used to highlight the minimum values in each of the column in any given dataset. Following is an example in which the minimum values are highlighted in yellow in each column.
Highlight Maximum Values
df.style.highlight_max()
It is time to highlight the maximum values! The above code shall be used to highlight the maximum values in each of the column in any given dataset. Following is an example in which the maximum values are highlighted in yellow in each column.
Highlight Null Values
It is not always numerical data that we deal with. At times, some erroneous values might seep-in in the form of text string. This command shall aid us in throwing a spotlight on those as shown below.
df.style.highlight_null()
Color Table Borders and Text Data
Jupyter notebook go hand-in-hand with Python. The dataframes displayed as an output usually is touched up by using HTML and CSS. Thusly, the table properties can be modified using the “set_properties” alongside style in order to set multiple data-independent properties.
df.style.set_properties(**{'border': '1.3px solid red', 'color': 'green'})
Truncate Decimal Display
The dataframe does not contain only whole numbers. Sometimes, it has floating values whose decimals extend without knowing any bounds. So, in order to control the number of digits that gets past the decimal point, we use the set.precision( ) as shown below.
df.style.set_precision(3)
Hide Dataframe Index
The index is the foremost column of the dataframe which serves as a reference, but not in all cases. So, if one ought to remove them from existence, then hide_index( ) is the way to go.
df.style.hide_index()
Export Styled Data to Excel
One can choose any of the above changes or transfer all of them into the data of a MS Excel spreadsheet by using the to_excel function. Here is what needs to be done, a simple trickery to get it across – combining the styler with the to_excel() function. Following is a combination of some stylings that are written into an Excel file.
df.style.highlight_min().set_properties(**{'border': '1.3px solid red','color': 'green'}). set_precision(3).to_excel('File.xlsx', engine = 'openpyxl')
Yep! You have read that right. Combine all those styling that ought to be done & then route the file upon which these changes are to be done. Remember to declare the engine parameter as openpyxl and we are good to go!
Conclusion
Now that we have reached the end of this article, hope it has elaborated on how to use the styler along with the to_excel( ) function to edit the formatting of the contents within an Excel sheet. Here is another article that details how to extract date from datetime in Python. There are numerous other enjoyable and equally informative articles in AskPython that might be of great help to those who are looking to level up in Python. Audere est facere!