<p>下面是一种方法:</p>
<pre><code>df = pd.DataFrame({
"Name": ["A", "A", "A", "B", "B", "B"],
"Date": "2020-07-01",
"Value": [0.1, 0.2, 3, 2, -.5, 4],
"Category": ["Z", "Y", "X", "Z", "Y", "X"]
})
piv = pd.pivot_table(df, index="Category", columns="Name", aggfunc=sum)
piv.columns = [c[1] for c in piv.columns]
piv["diff"] = piv.B - piv.A
</code></pre>
<p>输出(<code>piv</code>)是:</p>
<pre><code> A B diff
Category
X 3.0 4.0 1.0
Y 0.2 -0.5 -0.7
Z 0.1 2.0 1.9
</code></pre>
<p>要为A和B添加“总计”,请执行以下操作</p>
<pre><code>piv.loc["total"] = piv.sum()
</code></pre>
<p>从“差异”列中删除总计:</p>
<pre><code>piv.loc["total", "diff"] = "" # or np.NaN, if you'd like to be more
# 'pandas' style.
</code></pre>
<p>现在的输出是:</p>
<pre><code> A B diff
Category
X 3.0 4.0 1.0
Y 0.2 -0.5 -0.7
Z 0.1 2.0 1.9
total 3.3 5.5
</code></pre>
<p>如果此时您想在类别顶部添加标题“名称”,请执行以下操作:</p>
<pre><code>piv.columns = pd.MultiIndex.from_product([["Name"], piv.columns])
</code></pre>
<p><code>piv</code>现在是:</p>
<pre><code> Name
A B diff
Category
X 3.0 4.0 1.0
Y 0.2 -0.5 -0.7
Z 0.1 2.0 1.9
total 3.3 5.5
</code></pre>
<p>要将日期添加到每列,请执行以下操作:</p>
<pre><code>date = df.Date.max()
piv.columns = pd.MultiIndex.from_tuples([c+(date,) for c in piv.columns])
==>
Name
A B diff
2020-07-01 2020-07-01 2020-07-01
Category
X 3.0 4.0 1
Y 0.2 -0.5 -0.7
Z 0.1 2.0 1.9
total 3.3 5.5
</code></pre>
<p>最后,要为列着色(例如,如果您使用的是Jupyter),请执行以下操作:</p>
<pre><code>second_col = piv.columns[2]
piv.style.background_gradient("PiYG", subset = [second_col]).highlight_null('white').set_na_rep("")
</code></pre>
<p><a href="https://i.stack.imgur.com/sVpoX.png" rel="noreferrer"><img src="https://i.stack.imgur.com/sVpoX.png" alt="enter image description here"/></a></p>