# Pandas Style API

## Pandas\_UI เครื่องมือจัดการ Pandas DataFrame แบบง่าย ๆ – Pandas ep.7

![](https://3650666907-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M16IoaANYRvc0I-U9uw%2F-MDEJ2Jtoc9fAIs0K64N%2F-MDEMBTk5v3IpAiluZ-0%2Fimage.png?alt=media\&token=8044237a-8969-4a53-82c8-82771f9f1765)

![](https://3650666907-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M16IoaANYRvc0I-U9uw%2F-MDOCcOokacXnER-bQJN%2F-MDOF84vEzQ3_w7D8PJ8%2Fimage.png?alt=media\&token=5bf7511c-277b-4722-948a-61bd859b4ecd)

### Introduction

I have been working on a side project so I have not had as much time to blog. Hopefully I will be able to share more about that project soon.

ใน ep ที่แล้ว เราใช้ [Pandas Profiling](https://www.bualabs.com/archives/2297/exploratory-data-analysis-eda-pandas-profiling-pandas-dataframe-pandas-ep-6/) ในการช่วยทำการสำรวจข้อมูล Exploratory Data Analysis (EDA) แต่ถ้าเราต้องการเปลี่ยนแปลงข้อมูลนิด ๆ หน่อย ๆ หรือเราต้องการพล็อตกราฟที่ใน Pandas Profiling ไม่มีมาให้ล่ะ จะทำอย่างไร เราสามารถใช้ Pandas\_UI มาช่วยได้

In the meantime, I wanted to write an article about styling output in pandas. The API for styling is somewhat new and has been under very active development. It contains a useful set of tools for styling the output of your pandas DataFrames and Series. In my own usage, I tend to only use a small subset of the available options but I always seem to forget the details. This article will show examples of how to format numbers in a pandas DataFrame and use some of the more advanced pandas styling visualization options to improve your ability to analyze data with pandas.

### Pandas\_UI คืออะไร

### What is styling and why care?

![Pandas\_UI Main UI](https://www.bualabs.com/wp-content/uploads/2020/06/pandas_ui_11-1024x664.png)

The basic idea behind styling is that a user will want to modify the way the data is presented but still preserve the underlying format for further manipulation.

Pandas\_UI คือ เครื่องมือหน้าจอ User Interface Tools ที่ช่วยอำนวยความสะดวกให้เราสามารถจัดการ และแก้ไขข้อมูลใน Pandas DataFrame ได้อย่างรวดเร็ว และง่ายดาย

The most straightforward styling example is using a currency symbol when working with currency values. For instance, if your data contains the value 25.00, you do not immediately know if the value is in dollars, pounds, euros or some other currency. If the number is $25 then the meaning is clear.

เราสามารถเลือก Row, Column, เงื่อนไข และ Operation ที่ต้องการใน Pandas\_UI จะช่วย Generate Code ภาษา Python ให้เรา โดยที่เราไม่ต้องจำชื่อฟังก์ชัน ชื่อพารามิเตอร์ต่าง ๆ

Percentages are another useful example where formatting the output makes it simpler to understand the underlying analysis. For instance, which is quicker to understand: .05 or 5%? Using the percentage sign makes it very clear how to interpret the data.

Pandas\_UI ถูกสร้างด้วยเทคโนโลยีที่เราคุ้นเคย เช่น NumPy, plotly, ipywidgets, pandas\_profiling, qgrid โดยสร้างเป็น Jupyter Notebook Extension ทำให้สามารถรันใน [Jupyter Notebook](https://www.bualabs.com/archives/182/introduction-to-jupyter-notebook-ep-1/) ได้เลย

The key item to keep in mind is that styling presents the data so a human can read it but keeps the data in the same pandas data type so you can perform your normal pandas math, date or string functions.

### เรามาเริ่มกันเลยดีกว่า

Pandas styling also includes more advanced tools to add colors or other visual elements to the output. The [pandas documentation](https://pandas.pydata.org/pandas-docs/stable/user_guide/style.html) has some really good examples but it may be a bit overwhelming if you are just getting started. The rest of this article will go through examples of using styling to improve the readability of your final analysis.

ด้วย Code เพียงแค่ 3 บรรทัด

### Styling the data

```python
pip install pandas_ui
jupyter nbextension enable --py qgrid --sys-prefix
jupyter nbextension enable --py widgetsnbextension --sys-prefix
```

Let’s get started by looking at some data. For this example we will use some 2018 sales data for a fictitious organization. We will pretend to be an analyst looking for high level sales trends for 2018. All of the [data](https://github.com/chris1610/pbpython/blob/master/data/2018_Sales_Total.xlsx) and example [notebook](https://github.com/chris1610/pbpython/blob/master/notebooks/pandas-styling.ipynb) are on [github](https://github.com/chris1610/pbpython/). PLease note that the styling does not seem to render properly in github but if you choose to download the notebooks it should look fine.

[![Open In Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/gnoparus/bualabs/blob/master/nbs/23b_eda_pandas_ui.ipynb)[Check it out on github](https://github.com/gnoparus/bualabs/blob/master/nbs/23b_eda_pandas_ui.ipynb) Last updated: 02/07/2020 11:16:12

Import the necessary libraries and read in the data:

ใน ep ที่แล้ว เราใช้ Pandas Profiling ในการช่วยทำการสำรวจข้อมูล Exploratory Data Analysis (EDA) แต่ถ้าเราต้องการเปลี่ยนแปลงข้อมูลนิด ๆ หน่อย ๆ หรือเราต้องการพล็อตกราฟที่ใน Pandas Profiling ไม่มีมาให้ล่ะ จะทำอย่างไร เราสามารถใช้ Pandas\_UI มาช่วยได้

```python
import numpy as np
import pandas as pd

df = pd.read_excel('2018_Sales_Total.xlsx')
```

## Pandas\_UI คืออะไร <a href="#pandas_ui" id="pandas_ui"></a>

The data includes sales transaction lines that look like this:

Pandas\_UI คือ เครื่องมือหน้าจอ User Interface Tools ที่ช่วยให้เราสามารถจัดการ และแก้ไขข้อมูล Pandas DataFrame ได้อย่างสะดวก และรวดเร็ว

|   | account number | name                        | sku      | quantity | unit price | ext price | date                |
| - | -------------- | --------------------------- | -------- | -------- | ---------- | --------- | ------------------- |
| 0 | 740150         | Barton LLC                  | B1-20000 | 39       | 86.69      | 3380.91   | 2018-01-01 07:21:51 |
| 1 | 714466         | Trantow-Barrows             | S2-77896 | -1       | 63.16      | -63.16    | 2018-01-01 10:00:47 |
| 2 | 218895         | Kulas Inc                   | B1-69924 | 23       | 90.70      | 2086.10   | 2018-01-01 13:24:58 |
| 3 | 307599         | Kassulke, Ondricka and Metz | S1-65481 | 41       | 21.05      | 863.05    | 2018-01-01 15:05:22 |
| 4 | 412290         | Jerde-Hilpert               | S2-34077 | 6        | 83.21      | 499.26    | 2018-01-01 23:26:55 |

เราสามารถเลือก Row, Column, เงื่อนไข และ Operation ที่ต้องการใน Pandas\_UI จะช่วย Generate Code ภาษา Python ให้เรา โดยที่เราไม่ต้องจำชื่อฟังก์ชัน ชื่อพารามิเตอร์ต่าง ๆ

Given this data, we can do a quick summary to see how much the customers have purchased from us and what their average purchase amount looks like:

Pandas\_UI ถูกสร้างด้วยเทคโนโลยีที่เราคุ้นเคย เช่น NumPy, plotly, ipywidgets, pandas\_profiling, qgrid โดยสร้างเป็น Jupyter Notebook Extension ทำให้สามารถรันใน Jupyter Notebook ได้เลย

```python
df.groupby('name')['ext price'].agg(['mean', 'sum'])
```

## 0. Install <a href="#id-0.-install" id="id-0.-install"></a>

|                                 | mean        | sum       |
| ------------------------------- | ----------- | --------- |
| name                            |             |           |
| Barton LLC                      | 1334.615854 | 109438.50 |
| Cronin, Oberbrunner and Spencer | 1339.321642 | 89734.55  |
| Frami, Hills and Schmidt        | 1438.466528 | 103569.59 |
| Fritsch, Russel and Anderson    | 1385.366790 | 112214.71 |
| Halvorson, Crona and Champlin   | 1206.971724 | 70004.36  |

ติดตั้ง Library pandas\_ui และ Enable Jupyter Notebook Extension ให้เรียบร้อยIn \[1]:

For the sake of simplicity, I am only showing the top 5 items and will continue to truncate the data through the article to keep it short.

```
# ! pip install pandas_ui -q
# ! pip install fastai2 -q
```

As you look at this data, it gets a bit challenging to understand the scale of the numbers because you have 6 decimal points and somewhat large numbers. Also, it is not immediately clear if this is in dollars or some other currency. We can fix that using the DataFrame `style.format` .

In \[2]:

```python
(df.groupby('name')['ext price']
 .agg(['mean', 'sum'])
 .style.format('${0:,.2f}'))
```

```python
# ! jupyter nbextension enable --py qgrid --sys-prefix
# ! jupyter nbextension enable --py widgetsnbextension --sys-prefix
```

Here is what it looks like now:

## 1. Import <a href="#id-1.-import" id="id-1.-import"></a>

![Styling example 1](https://pbpython.com/images/styling_1.png)

Import Library ที่เราติดตั้งไว้ด้านบนIn \[3]:

Using the `format` function, we can use all the power of python’s string formatting tools on the data. In this case, we use `${0:,.2f}` to place a leading dollar sign, add commas and round the result to 2 decimal places.

```python
from pandas_ui import *

from fastai2.basics import *
```

For example, if we want to round to 0 decimal places, we can change the format to `${0:,.0f}`

## 2. Data <a href="#id-2.-data" id="id-2.-data"></a>

```python
(df.groupby('name')['ext price']
 .agg(['mean', 'sum'])
 .style.format('${0:,.0f}'))
```

ในเคสนี้เราจะใช้ Dataset ที่เป็นข้อมูลแบบ Tabular จาก Adult DatasetIn \[4]:

![Styling example 2](https://pbpython.com/images/styling_2.png)

```python
path = untar_data(URLs.ADULT_SAMPLE)
```

If you are like me and always forget how to do this, I found the [Python String Format Cookbook](https://mkaz.blog/code/python-string-format-cookbook/) to be a good quick reference. String formatting is one of those syntax elements that I always forget so I’m hoping this article will help others too.

ลอง ls ดูว่ามีไฟล์อะไรบ้างIn \[5]:

Now that we have done some basic styling, let’s expand this analysis to show off some more styling skills.

```python
path.ls()
```

If we want to look at total sales by each month, we can use the [grouper](https://pbpython.com/pandas-grouper-agg.html) to summarize by month and also calculate how much each month is as a percentage of the total annual sales.

Out\[5]:

```python
monthly_sales = df.groupby([pd.Grouper(key='date', freq='M')])['ext price'].agg(['sum']).reset_index()
monthly_sales['pct_of_total'] = monthly_sales['sum'] / df['ext price'].sum()
```

```
(#3) [Path('/home/keng/.fastai/data/adult_sample/export.pkl'),Path('/home/keng/.fastai/data/adult_sample/models'),Path('/home/keng/.fastai/data/adult_sample/adult.csv')]
```

![Styling example 3](https://pbpython.com/images/styling_3.png)

## 3. Explore Data <a href="#id-3.-explore-data" id="id-3.-explore-data"></a>

We know how to style our numbers but now we have a combination of dates, percentages and currency. Fortunately we can use a dictionary to define a unique formatting string for each column. This is really handy and powerful.

สั่งเปิดไฟล์ CSV ได้เลย

```python
format_dict = {'sum':'${0:,.0f}', 'date': '{:%m-%Y}', 'pct_of_total': '{:.2%}'}
monthly_sales.style.format(format_dict).hide_index()
```

In \[6]:

![Styling example 4](https://pbpython.com/images/styling_4.png)

```python
# pandas_ui??
```

I think that is pretty cool. When developing final output reports, having this type of flexibility is pretty useful. Astute readers may have noticed that we don’t show the index in this example. The `hide_index` function suppresses the display of the index - which is useful in many cases.

In \[9]:

In addition to styling numbers, we can also style the cells in the DataFrame. Let’s highlight the highest number in green and the lowest number in color Trinidad (#cd4f39).

```python
pandas_ui(path/'adult.csv')
```

```python
(monthly_sales
 .style
 .format(format_dict)
 .hide_index()
 .highlight_max(color='lightgreen')
 .highlight_min(color='#cd4f39'))
```

### ตัวอย่างหน้าจอการใช้งาน

![Styling example 5](https://pbpython.com/images/styling_5.png)

หน้าจอหลัก

One item to highlight is that I am using method chaining to string together multiple function calls at one time. This is a very powerful approach for analyzing data and one I encourage you to use as you get further in your pandas proficiency. I recommend Tom Augspurger’s [post](https://tomaugspurger.github.io/method-chaining) to learn much more about this topic.

![Pandas\_UI Main UI](https://www.bualabs.com/wp-content/uploads/2020/06/pandas_ui_11-1024x664.png)

Another useful function is the `background_gradient` which can highlight the range of values in a column.

ลบ Row ที่เลือกไว้

```python
(monthly_sales.style
 .format(format_dict)
 .background_gradient(subset=['sum'], cmap='BuGn'))
```

![Panda\_UI Remove Selected Rows](https://www.bualabs.com/wp-content/uploads/2020/06/pandas_ui_13-1024x660.png)

![Styling example 6](https://pbpython.com/images/styling_6.png)

ประวัติการแก้ไข เราสามารถนำโค้ด Python ที่ Generate มาให้ไปใช้ต่อได้เลย

The above example illustrates the use of the `subset` parameter to apply functions to only a single column of data. In addition, the `cmap` argument allows us to choose a color palette for the gradient. The matplotlib [documentation](https://matplotlib.org/tutorials/colors/colormaps.html) lists all the available options.

![Panda\_UI History](https://www.bualabs.com/wp-content/uploads/2020/06/pandas_ui_12-1024x267.png)

### Styling with Bars

แก้ไขข้อมูลใน DataFrame โดยตรง

The pandas styling function also supports drawing bar charts within the columns.

![Panda\_UI Add new rows and Modify Data](https://www.bualabs.com/wp-content/uploads/2020/06/pandas_ui_14.png)

Here’s how to do it:

มีฟังก์ชัน [Pandas Profiling](https://www.bualabs.com/archives/2297/exploratory-data-analysis-eda-pandas-profiling-pandas-dataframe-pandas-ep-6/) Report ในตัว

```python
(monthly_sales
 .style
 .format(format_dict)
 .hide_index()
 .bar(color='#FFA07A', vmin=100_000, subset=['sum'], align='zero')
 .bar(color='lightgreen', vmin=0, subset=['pct_of_total'], align='zero')
 .set_caption('2018 Sales Performance'))
```

![Panda\_UI Pandas Profiling Report Variables](https://www.bualabs.com/wp-content/uploads/2020/06/pandas_ui_15.png)

![Styling example 7](https://pbpython.com/images/styling_7.png)

![Panda\_UI Pandas Profiling Report Correlations](https://www.bualabs.com/wp-content/uploads/2020/06/pandas_ui_16.png)

This example introduces the `bar` function and some of the parameters to configure the way it is displayed in the table. Finally, this includes the use of the `set_caption` to add a simple caption to the top of the table.

เลือก 1 Column มาพล็อต Histogram เราสามารถนำโค้ด ภาษา Python ที่ Generate มาให้ไปใช้ต่อได้เลย

The next example is not using pandas styling but I think it is such a cool example that I wanted to include it. This specific example is from [Peter Baumgartner](https://twitter.com/pmbaumgartner/status/1084645440224559104) and uses the [sparkline](https://pypi.org/project/sparklines/) module to embed a tiny chart in the summary DataFrame.

![Pandas\_UI Plots Histogram](https://www.bualabs.com/wp-content/uploads/2020/06/pandas_ui_17.png)

Here’s the sparkline function:

รองรับกราฟ 3 มิติ

```python
import sparklines

def sparkline_str(x):
    bins=np.histogram(x)[0]
    sl = ''.join(sparklines(bins))
    return sl
sparkline_str.__name__ = "sparkline"
```

![Pandas\_UI Plots Scatter 3D](https://www.bualabs.com/wp-content/uploads/2020/06/pandas_ui_18.png)

We can then call this function like a standard aggregation function:

ตัวอย่างหน้าจอพล็อตกราฟ 3 มิติ Scatter 3D เราไม่ต้องจำชื่อ Parameter

```python
df.groupby('name')['quantity', 'ext price'].agg(['mean', sparkline_str])
```

![Pandas\_UI Form for Plots Scatter 3D](https://www.bualabs.com/wp-content/uploads/2020/06/pandas_ui_19-1024x500.png)

|                                 | quantity  | ext price  |             |            |
| ------------------------------- | --------- | ---------- | ----------- | ---------- |
|                                 | mean      | sparkline  | mean        | sparkline  |
| name                            |           |            |             |            |
| Barton LLC                      | 24.890244 | ▄▄▃▂▃▆▄█▁▄ | 1334.615854 | █▄▃▆▄▄▁▁▁▁ |
| Cronin, Oberbrunner and Spencer | 24.970149 | █▄▁▄▄▇▅▁▄▄ | 1339.321642 | █▅▅▃▃▃▂▂▁▁ |
| Frami, Hills and Schmidt        | 26.430556 | ▄▄▁▂▇█▂▂▅▅ | 1438.466528 | █▅▄▇▅▃▄▁▁▁ |
| Fritsch, Russel and Anderson    | 26.074074 | ▁▄▇▃▂▂█▃▄▄ | 1385.366790 | ▇█▃▄▂▂▁▂▁▁ |
| Halvorson, Crona and Champlin   | 22.137931 | ▇▆▆▇█▁▄▂▄▃ | 1206.971724 | ██▆▅▁▃▂▂▂▂ |

หน้าจอสร้างคำสั่ง Update ข้อมูลทีละเยอะ ๆ ตามเงื่อนไขที่กำหนด

I think this is a really useful function that can be used to concisely summarize data. The other interesting component is that this is all just text, you can see the underlying bars as lines in the raw HTML. It’s kind of wild.

![Panda\_UI Pandas Update Table Value Operation on Condition](https://www.bualabs.com/wp-content/uploads/2020/06/pandas_ui_20-1024x551.png)

### Conclusion

### Credit

The pandas style API is a welcome addition to the pandas library. It is really useful when you get towards the end of your data analysis and need to present the results to others. There are a few tricky components to string formatting so hopefully the items highlighted here are useful to you. There are other useful functions in this library but sometimes the documentation can be a bit dense so I am hopeful this article will get your started and you can use the official documentation as you dive deeper into the topic.

* <https://medium.com/@arunnbaba/pandas-ui-87316a4e19c7>
* <https://github.com/arunnbaba/pandas_ui>

Finally, thanks to [Alexas\_Fotos](https://pixabay.com/users/alexas_fotos-686414/) for the nice title [image](https://pixabay.com/illustrations/panda-animal-wild-abstract-1235263/).

### หมายเหตุ

Reference : <https://pbpython.com/styling-pandas.html>

* Library นี้ใหม่มาก ยังมี Bug อยู่หลายจุด
* ถ้าเกิด Error ให้รัน Cell ใหม่
* ขณะนี้ยังไม่รองรับ [Google Colab](https://www.bualabs.com/archives/1687/what-is-colab-open-jupyter-notebook-in-github-on-google-colab-create-open-in-colab-button-colab-ep-1/)

#### แชร์ให้เพื่อน:

[Facebook](https://www.bualabs.com/#facebook)[Twitter](https://www.bualabs.com/#twitter)[Email](https://www.bualabs.com/#email)[LinkedIn](https://www.bualabs.com/#linkedin)[Line](https://www.bualabs.com/#line)[Share](https://www.addtoany.com/share#url=https%3A%2F%2Fwww.bualabs.com%2Farchives%2F4299%2Fpandas_ui-pandas-dataframe-user-interface-tools-pandas-ep-7%2F\&title=Pandas_UI%20%E0%B9%80%E0%B8%84%E0%B8%A3%E0%B8%B7%E0%B9%88%E0%B8%AD%E0%B8%87%E0%B8%A1%E0%B8%B7%E0%B8%AD%E0%B8%88%E0%B8%B1%E0%B8%94%E0%B8%81%E0%B8%B2%E0%B8%A3%20Pandas%20DataFrame%20%E0%B9%81%E0%B8%9A%E0%B8%9A%E0%B8%87%E0%B9%88%E0%B8%B2%E0%B8%A2%20%E0%B9%86%20%E2%80%93%20Pandas%20ep.7)

#### บทความที่เกี่ยวข้อง:

1. [สำรวจข้อมูล Exploratory Data Analysis (EDA) ด้วย Pandas Profiling วิเคราะห์ Pandas DataFrame – Pandas ep.6](https://www.bualabs.com/archives/2297/exploratory-data-analysis-eda-pandas-profiling-pandas-dataframe-pandas-ep-6/)
2. [จัดการหมวดหมู่เล็ก ๆ ยิบย่อย รวมข้อมูลหมวดหมู่ Category เล็ก ๆ เป็นหมวดหมู่ Other ก่อนป้อนเทรน Machine Learning – Preprocessing ep.4](https://www.bualabs.com/archives/2393/combine-small-category-to-other-category-before-feed-machine-learning-preprocessing-ep-4/)
3. [Customer Segmentation คืออะไร สอนทฤษฎี Machine Learning หลักการ สร้าง Segment Profile กลุ่มลูกค้า, Customer Profile การแบ่งกลุ่มลูกค้า ด้วย K-Means Clustering – Tabular Data ep.4](https://www.bualabs.com/archives/3866/what-is-customer-segmentation-build-segment-profile-target-group-customer-profile-k-means-clustering-tabular-data-ep-4/)
4. [Latent Semantic Analysis (LSA) คืออะไร Text Classification ด้วย Singular Value Decomposition (SVD), Non-negative Matrix Factorization (NMF) – NLP ep.4](https://www.bualabs.com/archives/2971/lsa-latent-semantic-analysis-text-classification-singular-value-decomposition-svd-non-negative-matrix-factorization-nmf-nlp-ep-4/)

Reference : <https://www.bualabs.com/archives/4299/pandas_ui-pandas-dataframe-user-interface-tools-pandas-ep-7/#more-4299>
