有一段時間得到我對 pandas 多維框架的期望，特別是當我嘗試在一列后繼另一列上對它們進行排序時。我將嘗試通過這個來最好地解釋我所追求的并感謝幫助。
因此，我有一個要分析的資料集，例如，它在 excel 中如下所示：
Data Opportunity Name Geo Map Forecast Category Total
0 source_1 opportunity_1 geo_1 pipeline 100
1 source_1 opportunity_2 geo_1 upside 200
2 source_1 opportunity_3 geo_1 commit 300
3 source_1 opportunity_4 geo_1 won 400
4 source_1 opportunity_5 geo_1 omitted 500
5 source_2 opportunity_6 geo_2 pipeline 600
6 source_2 opportunity_7 geo_2 upside 700
7 source_2 opportunity_8 geo_2 commit 800
8 source_2 opportunity_9 geo_2 won 900
9 source_2 opportunity_10 geo_2 omitted 1000
10 source_3 opportunity_11 geo_3 pipeline 1100
11 source_3 opportunity_12 geo_3 upside 1200
12 source_3 opportunity_13 geo_3 commit 1300
13 source_3 opportunity_14 geo_3 won 1400
14 source_3 opportunity_15 geo_3 omitted 1500
15 source_4 opportunity_16 geo_4 pipeline 1600
16 source_4 opportunity_17 geo_4 upside 1700
17 source_4 opportunity_18 geo_4 commit 1800
18 source_4 opportunity_19 geo_4 won 1900
19 source_4 opportunity_20 geo_4 omitted 2000
我通過將資料集從 excel 讀取到資料框 df 中，將該資料集加載到 pandas 中：
import pandas as pd
df = pd.read_excel('C:/Users/nick/Downloads/example_data.xlsx')
我想顯示首先在“地理地圖”欄位上索引的資料，然后在“預測類別”欄位上顯示資料，但我想控制這些值的排序順序。為了確保我能做到這一點，我以我認為可能需要的兩種格式為這兩個欄位創建了一個鍵盤映射：
regions_array = {'geo_2': 1, 'geo_1': 2, 'geo_4': 3, 'geo_3':4}
regions_list = ['geo_2', 'geo_1', 'geo_4', 'geo_3']
categories_array = {'pipeline': 1, 'upside': 2, 'commit': 3, 'won': 4, 'omitted': 5}
categories_list = ['pipeline', 'upside', 'commit', 'won', 'omitted']
當我將資料移動到資料透視表中時，我能夠通過以下資料透視表實作我所追求的多索引結構：
regional_numbers = pd.pivot_table(df, index=['Geo Map', 'Forecast Category'],
columns='Data', values='Total', fill_value=0, aggfunc='sum')
regional_numbers
結果：
Data source_1 source_2 source_3 source_4
Geo Map Forecast Category
geo_1 commit 300 0 0 0
omitted 500 0 0 0
pipeline 100 0 0 0
upside 200 0 0 0
won 400 0 0 0
geo_2 commit 0 800 0 0
omitted 0 1000 0 0
pipeline 0 600 0 0
upside 0 700 0 0
won 0 900 0 0
geo_3 commit 0 0 1300 0
omitted 0 0 1500 0
pipeline 0 0 1100 0
upside 0 0 1200 0
won 0 0 1400 0
geo_4 commit 0 0 0 1800
omitted 0 0 0 2000
pipeline 0 0 0 1600
upside 0 0 0 1700
won 0 0 0 1900
但是，現在我需要先按“地理地圖”，然后按“預測類別”重新排序我的索引。我曾嘗試排序索引，重新索引，但問題是一旦我根據第一級排序一切都很好，但第二級打破了框架。
我希望我的結果看起來是基于從我的鍵中排序的，首先是“地理地圖”，然后是“預測類別”：
Data source_1 source_2 source_3 source_4
Geo Map Forecast Category
geo_2 pipeline 0 600 0 0
upside 0 700 0 0
commit 0 800 0 0
won 0 900 0 0
omitted 0 1000 0 0
geo_1 pipeline 100 0 0 0
upside 200 0 0 0
commit 300 0 0 0
won 400 0 0 0
omitted 500 0 0 0
geo_4 pipeline 0 0 0 1600
upside 0 0 0 1700
commit 0 0 0 1800
won 0 0 0 1900
omitted 0 0 0 2000
geo_3 pipeline 0 0 1100 0
upside 0 0 1200 0
commit 0 0 1300 0
won 0 0 1400 0
omitted 0 0 1500 0
額外學分
如果我可以將總行數添加到等式中，那將是我正在尋找的最終狀態。我試圖在資料透視引數中添加 margins=True 和 margin_name 但是我需要對我希望避免的鍵中的 Total 列進行排序。額外信用的結束狀態將是：
Data source_1 source_2 source_3 source_4
Geo Map Forecast Category
geo_2 pipeline 0 600 0 0
upside 0 700 0 0
commit 0 800 0 0
won 0 900 0 0
omitted 0 1000 0 0
Total 0 4000 0 0
geo_1 ... ... ... ... ...
Total X X X X
geo_4 ... ... ... ... ...
Total X X X X
geo_3 ... ... ... ... ...
Total X X X X
TOTALS X X X X
將總數放在所有來源的右側以及在行級別上也會很好，但是無法正確顯示。
如果我要求總數的方式是不可能的，我相信我可以單獨計算出我想要的值，但會很好。
感謝您的任何幫助，您可以提供！
編輯以修復我的預期輸出。
uj5u.com熱心網友回復：
注意：categories_array有以大寫字母開頭的鍵，將它們更改為與資料框中的相同。
嘗試：
def add_total(x):
x = pd.concat(
[
x,
pd.DataFrame(
{c: [x[c].sum()] for c in x.columns}, index=[("", "Total")]
),
]
)
return x
regional_numbers = regional_numbers.reindex(
sorted(
regional_numbers.index,
key=lambda k: (regions_array[k[0]], categories_array[k[1]]),
)
)
regional_numbers = (
regional_numbers.groupby(level=0, as_index=False, sort=False)
.apply(add_total)
.droplevel(0)
)
regional_numbers["TOTAL"] = regional_numbers.sum(axis=1)
regional_numbers = pd.concat(
[
regional_numbers,
pd.DataFrame(
{
c: [regional_numbers.xs("Total", level=1).sum().loc[c]]
for c in regional_numbers.columns
},
index=[("TOTAL", "")],
),
]
)
print(regional_numbers)
印刷：
source_1 source_2 source_3 source_4 TOTAL
Geo_Map Forecast_Category
geo_2 pipeline 0 600 0 0 600
upside 0 700 0 0 700
commit 0 800 0 0 800
won 0 900 0 0 900
omitted 0 1000 0 0 1000
Total 0 4000 0 0 4000
geo_1 pipeline 100 0 0 0 100
upside 200 0 0 0 200
commit 300 0 0 0 300
won 400 0 0 0 400
omitted 500 0 0 0 500
Total 1500 0 0 0 1500
geo_4 pipeline 0 0 0 1600 1600
upside 0 0 0 1700 1700
commit 0 0 0 1800 1800
won 0 0 0 1900 1900
omitted 0 0 0 2000 2000
Total 0 0 0 9000 9000
geo_3 pipeline 0 0 1100 0 1100
upside 0 0 1200 0 1200
commit 0 0 1300 0 1300
won 0 0 1400 0 1400
omitted 0 0 1500 0 1500
Total 0 0 6500 0 6500
TOTAL 1500 4000 6500 9000 21000
uj5u.com熱心網友回復：
嘗試使用 pd.MultiIndex.from_products 然后重新索引：
idx = pd.MultiIndex.from_product([regions_list, categories_list], names=['Geo Map', 'Forecast Category'])
df_out = regional_numbers.reindex(idx)
df_out
輸出：
Data source_1 source_2 source_3 source_4
Geo Map Forecast Category
geo_2 pipeline 0 600 0 0
upside 0 700 0 0
commit 0 800 0 0
won 0 900 0 0
omitted 0 1000 0 0
geo_1 pipeline 100 0 0 0
upside 200 0 0 0
commit 300 0 0 0
won 400 0 0 0
omitted 500 0 0 0
geo_4 pipeline 0 0 0 1600
upside 0 0 0 1700
commit 0 0 0 1800
won 0 0 0 1900
omitted 0 0 0 2000
geo_3 pipeline 0 0 1100 0
upside 0 0 1200 0
commit 0 0 1300 0
won 0 0 1400 0
omitted 0 0 1500 0
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/488996.html
標籤：Python 熊猫 排序 jupyter-笔记本 多指标