英文:
Reorder multiple column levels at once in a pandas DataFrame
问题
df = pd.pivot_table(raw, values=['Shipped','Sold'], index=['Category', 'Model No'], columns=['Customer', 'Week Start Date'], aggfunc=np.sum, fill_value=0)
英文:
I am trying to create a report using pandas pivot table and currently I have below code with this output
df = pd.pivot_table(raw, values=['Shipped','Sold'], index=['Category', 'Model No'], columns=['Customer', 'Week Start Date'], aggfunc=np.sum, fill_value=0)
output
But the output i am desiring is below
how can i make it like the second report?
thank you!
答案1
得分: 3
使用MultiIndex.reorder_levels重新排序列轴，并使用sort_index按axis=1排序：
df.columns = df.columns.reorder_levels((1, 2, 0))df = df.sort_index(axis=1)
示例：
np.random.seed(42)columns = pd.MultiIndex.from_product([['Shipped', 'Sold'], ['A', 'B'], ['d1', 'd2']])data = np.random.randint(0, 100, size=(5, 8))df = pd.DataFrame(data, columns=columns)dfShipped Sold # level 0A B A B # level 1d1 d2 d1 d2 d1 d2 d1 d2 # level 20 51 92 14 71 60 20 82 861 74 74 87 99 23 2 21 522 1 87 29 37 1 63 59 203 32 75 57 21 88 48 90 584 41 91 59 79 14 61 61 46
# 1st level now 0th, 2nd level now 1st, 0th level now lastdf.columns = df.columns.reorder_levels((1, 2, 0))df = df.sort_index(axis=1)dfA Bd1 d2 d1 d2Shipped Sold Shipped Sold Shipped Sold Shipped Sold0 51 60 92 20 14 82 71 861 74 23 74 2 87 21 99 522 1 1 87 63 29 59 37 203 32 88 75 48 57 90 21 584 41 14 91 61 59 61 79 46
为了记录，我还将在评论中包含的Quang Hoang的选项使用stack加上unstack：
df.stack(0).unstack(-1)A Bd1 d2 d1 d2Shipped Sold Shipped Sold Shipped Sold Shipped Sold0 51 60 92 20 14 82 71 861 74 23 74 2 87 21 99 522 1 1 87 63 29 59 37 203 32 88 75 48 57 90 21 584 41 14 91 61 59 61 79 46
尽管请注意，这通常不是一个非常高效的选项，因为它实际上必须重新整形您的DataFrame。
英文:
Use MultiIndex.reorder_levels and then sort the column axis using sort_index with axis=1:
df.columns = df.columns.reorder_levels((1, 2, 0))df = df.sort_index(axis=1)
Example:
np.random.seed(42)columns = pd.MultiIndex.from_product([['Shipped', 'Sold'], ['A', 'B'], ['d1', 'd2']])data = np.random.randint(0, 100, size=(5, 8))df = pd.DataFrame(data, columns=columns)dfShipped Sold # level 0A B A B # level 1d1 d2 d1 d2 d1 d2 d1 d2 # level 20 51 92 14 71 60 20 82 861 74 74 87 99 23 2 21 522 1 87 29 37 1 63 59 203 32 75 57 21 88 48 90 584 41 91 59 79 14 61 61 46
<!-- -->
# 1st level now 0th, 2nd level now 1st, 0th level now lastdf.columns = df.columns.reorder_levels((1, 2, 0))df = df.sort_index(axis=1)dfA Bd1 d2 d1 d2Shipped Sold Shipped Sold Shipped Sold Shipped Sold0 51 60 92 20 14 82 71 861 74 23 74 2 87 21 99 522 1 1 87 63 29 59 37 203 32 88 75 48 57 90 21 584 41 14 91 61 59 61 79 46
For posterity I'll also include the option by Quang Hoang in the comments using stack plus unstack:
df.stack(0).unstack(-1)A Bd1 d2 d1 d2Shipped Sold Shipped Sold Shipped Sold Shipped Sold0 51 60 92 20 14 82 71 861 74 23 74 2 87 21 99 522 1 1 87 63 29 59 37 203 32 88 75 48 57 90 21 584 41 14 91 61 59 61 79 46
Although note that this is generally not a very performant option since it has to actually reshape your DataFrame.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论