In [1]:

import numpy as np
import pandas as pd

In [2]:

df = pd.read_excel('https://www.gairuo.com/file/data/dataset/team.xlsx')
df.head()

Out[2]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	C	36	37	37	57
2	Ack	A	57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [3]:

# 样式对象：此时还没设置任何样式，显示原样的数据
df.head().style

Out[3]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	C	36	37	37	57
2	Ack	A	57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [4]:

# 查看类型
type(df.style)

Out[4]:

pandas.io.formats.style.Styler

In [5]:

df.iloc[1,1] = np.nan
df.head().style.highlight_null()

Out[5]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	nan	36	37	37	57
2	Ack	A	57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [6]:

# 指定颜色名称
df.head().style.highlight_null(null_color='blue')

Out[6]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	nan	36	37	37	57
2	Ack	A	57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [7]:

# 指定颜色值
df.head().style.highlight_null(null_color='#ccc')

Out[7]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	nan	36	37	37	57
2	Ack	A	57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [8]:

df.head().style.highlight_max() # 自动仅对数值内容奏效

Out[8]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	nan	36	37	37	57
2	Ack	A	57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [9]:

df.head().style.highlight_min()

Out[9]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	nan	36	37	37	57
2	Ack	A	57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [10]:

df.head().style \
.highlight_max(color='lime') \
.highlight_min()

Out[10]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	nan	36	37	37	57
2	Ack	A	57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [11]:

# 按行
df.head().style \
.highlight_max(color='lime', axis=1) \
.highlight_min(axis=1)

Out[11]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	nan	36	37	37	57
2	Ack	A	57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [12]:

# 作用于数据子集
df.head().style.highlight_min(subset=['Q1','Q4'])

Out[12]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	nan	36	37	37	57
2	Ack	A	57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [13]:

# 矩形数据区域
df.head().style.highlight_min(subset=pd.IndexSlice[1:4, ['Q2']])

Out[13]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	nan	36	37	37	57
2	Ack	A	57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [14]:

# 根据数值大小（文本内无作用），背景颜色由深到浅渐变
df.head().style.background_gradient()

Out[14]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	nan	36	37	37	57
2	Ack	A	57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [15]:

# 指定色系
df.head().style.background_gradient(subset=['Q1'], cmap='BuGn')

Out[15]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	nan	36	37	37	57
2	Ack	A	57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [16]:

# 指定色系使用范围
df.head().style.background_gradient(low=0.5, high=1)

Out[16]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	nan	36	37	37	57
2	Ack	A	57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [17]:

# 指定字体颜色深浅，方便凸显文本(0~1 深到浅)
df.head().style.background_gradient(text_color_threshold=0.)

Out[17]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	nan	36	37	37	57
2	Ack	A	57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [18]:

#绘图范围在 30~100
df.head().style.bar(subset=['Q1'], vmin=30, vmax=100) 

Out[18]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	nan	36	37	37	57
2	Ack	A	57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [19]:

# 指定颜色
df.head().style.bar(subset=['Q1'], vmin=30, vmax=100, color='green') 

Out[19]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	nan	36	37	37	57
2	Ack	A	57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [20]:

# 默认作用数字字段
df.head().style.bar()

Out[20]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	nan	36	37	37	57
2	Ack	A	57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [21]:

# 按行
df.loc[df.index==1,:].style.bar(axis=1)

Out[21]:

	name	team	Q1	Q2	Q3	Q4
1	Arry	nan	36	37	37	57

In [22]:

# 图占比（覆盖单元格的百分比）
df.head().style.bar(subset=['Q1'], width=50)

Out[22]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	nan	36	37	37	57
2	Ack	A	57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [23]:

# 对齐方式
df.head().style.bar(subset=['Q1'], align='mid') # mid=(max-min)/2 值在中间

Out[23]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	nan	36	37	37	57
2	Ack	A	57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [24]:

df.head().style.bar(subset=['Q1'], align='left') # 最小值开始，默认也是这样

Out[24]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	nan	36	37	37	57
2	Ack	A	57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [25]:

df.iloc[2,2] = -57
df.head().style.bar(subset=['Q1'], align='zero') # 0值在中间

Out[25]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	nan	36	37	37	57
2	Ack	A	-57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [26]:

# 分别设置上涨、下降颜色
df.iloc[2,2] = -57
df.head().style.bar(subset=['Q1'], align='zero', color=['#ffe4e4','#bbf9ce']) 

Out[26]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	89	21	24	64
1	Arry	nan	36	37	37	57
2	Ack	A	-57	60	18	84
3	Eorge	C	93	96	71	78
4	Oah	D	65	49	61	86

In [27]:

# 给所有数据加一个[]
df.head().style.format("[{}]")

Out[27]:

	name	team	Q1	Q2	Q3	Q4
0	[Liver]	[E]	[89]	[21]	[24]	[64]
1	[Arry]	[nan]	[36]	[37]	[37]	[57]
2	[Ack]	[A]	[-57]	[60]	[18]	[84]
3	[Eorge]	[C]	[93]	[96]	[71]	[78]
4	[Oah]	[D]	[65]	[49]	[61]	[86]

In [28]:

# 百分号
df.head().style.format({'Q1':"{:.2%}"})

Out[28]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	E	8900.00%	21	24	64
1	Arry	nan	3600.00%	37	37	57
2	Ack	A	-5700.00%	60	18	84
3	Eorge	C	9300.00%	96	71	78
4	Oah	D	6500.00%	49	61	86

In [29]:

import datetime

df.iloc[2,0] = np.nan
df.loc[:,'team'] = datetime.datetime.now()
df.iloc[2,3] = 60.12345
df.iloc[2,4] = -18
df.iloc[2,5] = 123584.25

df.head().style.format({
    'name':str.upper, # 大写
    'team':'{:%Y-%m}',
    'Q1':'{:.2%}', # 百分号
    'Q2':'{:.2f}', # 保留2位小数
    'Q3':'{:+.2f}', # 正负号
    'Q4':'￥{0:,.2f}' # 千分符，货币符
}, na_rep='空值' # 缺失值替代
) 

Out[29]:

	name	team	Q1	Q2	Q3	Q4
0	LIVER	2023-08	8900.00%	21.00	+24.00	￥64.00
1	ARRY	2023-08	3600.00%	37.00	+37.00	￥57.00
2	空值	2023-08	-5700.00%	60.12	-18.00	￥123,584.25
3	EORGE	2023-08	9300.00%	96.00	+71.00	￥78.00
4	OAH	2023-08	6500.00%	49.00	+61.00	￥86.00

In [30]:

# 加标题
df.head().style.set_caption('学生成绩表')

Out[30]:

学生成绩表
	name	team	Q1	Q2	Q3	Q4
0	Liver	2023-08-19 18:33:48.424294	89	21.000000	24	64.000000
1	Arry	2023-08-19 18:33:48.424294	36	37.000000	37	57.000000
2	nan	2023-08-19 18:33:48.424294	-57	60.123450	-18	123584.250000
3	Eorge	2023-08-19 18:33:48.424294	93	96.000000	71	78.000000
4	Oah	2023-08-19 18:33:48.424294	65	49.000000	61	86.000000

In [31]:

# 全局保留两位小数，前提浮点型
df.iloc[2,2] = 50.12345
df.head().style.set_precision(2)

Out[31]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	2023-08-19 18:33:48.424294	89.00	21.00	24	64.00
1	Arry	2023-08-19 18:33:48.424294	36.00	37.00	37	57.00
2	nan	2023-08-19 18:33:48.424294	50.12	60.12	-18	123584.25
3	Eorge	2023-08-19 18:33:48.424294	93.00	96.00	71	78.00
4	Oah	2023-08-19 18:33:48.424294	65.00	49.00	61	86.00

In [32]:

# 缺失值统一显示
na = np.nan

(df.head() 
.eval('name=@na') # 设置name列为缺失值
.style 
.set_na_rep('暂无')
)

Out[32]:

	name	team	Q1	Q2	Q3	Q4
0	暂无	2023-08-19 18:33:48.424294	89.000000	21.000000	24	64.000000
1	暂无	2023-08-19 18:33:48.424294	36.000000	37.000000	37	57.000000
2	暂无	2023-08-19 18:33:48.424294	50.123450	60.123450	-18	123584.250000
3	暂无	2023-08-19 18:33:48.424294	93.000000	96.000000	71	78.000000
4	暂无	2023-08-19 18:33:48.424294	65.000000	49.000000	61	86.000000

In [33]:

# 隐藏索引
df.head().style.hide_index()

Out[33]:

name	team	Q1	Q2	Q3	Q4
Liver	2023-08-19 18:33:48.424294	89.000000	21.000000	24	64.000000
Arry	2023-08-19 18:33:48.424294	36.000000	37.000000	37	57.000000
nan	2023-08-19 18:33:48.424294	50.123450	60.123450	-18	123584.250000
Eorge	2023-08-19 18:33:48.424294	93.000000	96.000000	71	78.000000
Oah	2023-08-19 18:33:48.424294	65.000000	49.000000	61	86.000000

In [34]:

# 隐藏列
df.head().style.hide_columns(['name','team'])

Out[34]:

	Q1	Q2	Q3	Q4
0	89.000000	21.000000	24	64.000000
1	36.000000	37.000000	37	57.000000
2	50.123450	60.123450	-18	123584.250000
3	93.000000	96.000000	71	78.000000
4	65.000000	49.000000	61	86.000000

In [35]:

# 指定列字体颜色
df.head().style.set_properties(subset=['name','team'], **{'color':'red'})

Out[35]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	2023-08-19 18:33:48.424294	89.000000	21.000000	24	64.000000
1	Arry	2023-08-19 18:33:48.424294	36.000000	37.000000	37	57.000000
2	nan	2023-08-19 18:33:48.424294	50.123450	60.123450	-18	123584.250000
3	Eorge	2023-08-19 18:33:48.424294	93.000000	96.000000	71	78.000000
4	Oah	2023-08-19 18:33:48.424294	65.000000	49.000000	61	86.000000

In [36]:

## 效果：鼠标移动上去时整行背景变黄
df.head().style.set_table_styles([{
    'selector':'tr:hover',
    'props':[('background-color','yellow')]
}])

Out[36]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	2023-08-19 18:33:48.424294	89.000000	21.000000	24	64.000000
1	Arry	2023-08-19 18:33:48.424294	36.000000	37.000000	37	57.000000
2	nan	2023-08-19 18:33:48.424294	50.123450	60.123450	-18	123584.250000
3	Eorge	2023-08-19 18:33:48.424294	93.000000	96.000000	71	78.000000
4	Oah	2023-08-19 18:33:48.424294	65.000000	49.000000	61	86.000000

In [37]:

# 自定义样式函数
def background_color(row):
    '''
    Q1学科成绩：90分以上整行红色，60-90整行黄色
    '''
    if row.Q1 >= 90:
        return ['background-color: red'] * len(row)
    elif row.Q1 >= 60:
        return ['background-color: yellow'] * len(row)
    else:
        return [''] * len(row)
    
df.head().style.apply(background_color, axis=1)

Out[37]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	2023-08-19 18:33:48.424294	89.000000	21.000000	24	64.000000
1	Arry	2023-08-19 18:33:48.424294	36.000000	37.000000	37	57.000000
2	nan	2023-08-19 18:33:48.424294	50.123450	60.123450	-18	123584.250000
3	Eorge	2023-08-19 18:33:48.424294	93.000000	96.000000	71	78.000000
4	Oah	2023-08-19 18:33:48.424294	65.000000	49.000000	61	86.000000

In [38]:

# 样式复用
style1 = df.head().style.apply(background_color, axis=1)
df.tail().style.use(style1.export())

Out[38]:

	name	team	Q1	Q2	Q3	Q4
95	Gabriel	2023-08-19 18:33:48.424294	48.000000	59.000000	87	74.000000
96	Austin7	2023-08-19 18:33:48.424294	21.000000	31.000000	30	43.000000
97	Lincoln4	2023-08-19 18:33:48.424294	98.000000	93.000000	1	20.000000
98	Eli	2023-08-19 18:33:48.424294	11.000000	74.000000	58	91.000000
99	Ben	2023-08-19 18:33:48.424294	21.000000	43.000000	41	74.000000

In [39]:

# 清除样式
df.head().style.clear()

In [ ]:

In [41]:

# 生成
df.iloc[2,2] = -57
html = df.head().style.bar(subset=['Q1'], align='zero', color=['#ffe4e4','#bbf9ce']).render()

In [42]:

# 展示
from IPython.display import HTML
HTML(html)

Out[42]:

	name	team	Q1	Q2	Q3	Q4
0	Liver	2023-08-19 18:33:48.424294	89.000000	21.000000	24	64.000000
1	Arry	2023-08-19 18:33:48.424294	36.000000	37.000000	37	57.000000
2	nan	2023-08-19 18:33:48.424294	-57.000000	60.123450	-18	123584.250000
3	Eorge	2023-08-19 18:33:48.424294	93.000000	96.000000	71	78.000000
4	Oah	2023-08-19 18:33:48.424294	65.000000	49.000000	61	86.000000

In [ ]:

日食三餐夜眠六尺

导航

公告

Pandas样式-Styler对象

一、高亮空值¶

二、极值高亮¶

三、背景渐进¶

四、条形图¶

五、数据格式化¶

六、高级样式¶

七、导出Excel（待总结）¶

八、生成HTML¶