<p>这真的是你所表现出来的</p>
<ul>
<li><a href="https://stackoverflow.com/questions/14529838/apply-multiple-functions-to-multiple-groupby-columns/53096340">multiple columns in aggregations</a>用于<strong>概念上的_Done</strong></li>
<li>对于总计,使用合成列表示<code>groupby()</code></li>
</ul>
<pre><code>import pandas as pd
df ={'Trading_Book': ['A', 'A', 'B', 'B','C','C','C',],
'Client': ['AA', 'AA', 'BB', 'BB','CC','CC','CC',],
'Ccy':[ 'AUD', 'AUD', 'AUD', 'AUD','AUD','AUD','AUD',],
'RFQ_State': ['Done', 'Done', 'Done', 'Traded Away','Traded Away', 'Traded Away','Done'],
'Competing_Dealers': [2, 4, 6, 8, 10, 12,14],
'Notional': [1000, 1000, 1000, 1000,1000,1000,1000],
}
df = pd.DataFrame(df)
# generate sub-totals
dfs = (df.groupby(["Trading_Book","Client","Ccy"])
.agg(
Total_RFQs=("RFQ_State","count"),
Avg_Competing_Dealers=("Competing_Dealers","mean"),
Notional=("Notional","sum"),
Notional_Done=("Notional", lambda s: (df.loc[s.index,"RFQ_State"].eq("Done")*s).sum())
)
.assign(Hit_Rate=lambda d: d["Notional_Done"]/d["Notional"])
)
# generate grandtotals, use dummy column for groupby...
dft = (df.assign(grandtotal=1).groupby(["grandtotal"], as_index=False)
.agg(
Total_RFQs=("RFQ_State","count"),
Avg_Competing_Dealers=("Competing_Dealers","mean"),
Notional=("Notional","sum"),
Notional_Done=("Notional", lambda s: (df.loc[s.index,"RFQ_State"].eq("Done")*s).sum())
)
.assign(Hit_Rate=lambda d: d["Notional_Done"]/d["Notional"])
.drop(columns="grandtotal")
)
print(f"{dft.to_string(index=False)}\n{dfs.to_string()}")
</code></pre>
<h3>输出</h3>
<pre><code> Total_RFQs Avg_Competing_Dealers Notional Notional_Done Hit_Rate
7 8 7000 4000 0.571429
Total_RFQs Avg_Competing_Dealers Notional Notional_Done Hit_Rate
Trading_Book Client Ccy
A AA AUD 2 3 2000 2000 1.000000
B BB AUD 2 7 2000 1000 0.500000
C CC AUD 3 12 3000 1000 0.333333
</code></pre>