英文:
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)
df
Shipped Sold # level 0
A B A B # level 1
d1 d2 d1 d2 d1 d2 d1 d2 # level 2
0 51 92 14 71 60 20 82 86
1 74 74 87 99 23 2 21 52
2 1 87 29 37 1 63 59 20
3 32 75 57 21 88 48 90 58
4 41 91 59 79 14 61 61 46
# 1st level now 0th, 2nd level now 1st, 0th level now last
df.columns = df.columns.reorder_levels((1, 2, 0))
df = df.sort_index(axis=1)
df
A B
d1 d2 d1 d2
Shipped Sold Shipped Sold Shipped Sold Shipped Sold
0 51 60 92 20 14 82 71 86
1 74 23 74 2 87 21 99 52
2 1 1 87 63 29 59 37 20
3 32 88 75 48 57 90 21 58
4 41 14 91 61 59 61 79 46
为了记录,我还将在评论中包含的Quang Hoang的选项使用stack
加上unstack
:
df.stack(0).unstack(-1)
A B
d1 d2 d1 d2
Shipped Sold Shipped Sold Shipped Sold Shipped Sold
0 51 60 92 20 14 82 71 86
1 74 23 74 2 87 21 99 52
2 1 1 87 63 29 59 37 20
3 32 88 75 48 57 90 21 58
4 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)
df
Shipped Sold # level 0
A B A B # level 1
d1 d2 d1 d2 d1 d2 d1 d2 # level 2
0 51 92 14 71 60 20 82 86
1 74 74 87 99 23 2 21 52
2 1 87 29 37 1 63 59 20
3 32 75 57 21 88 48 90 58
4 41 91 59 79 14 61 61 46
<!-- -->
# 1st level now 0th, 2nd level now 1st, 0th level now last
df.columns = df.columns.reorder_levels((1, 2, 0))
df = df.sort_index(axis=1)
df
A B
d1 d2 d1 d2
Shipped Sold Shipped Sold Shipped Sold Shipped Sold
0 51 60 92 20 14 82 71 86
1 74 23 74 2 87 21 99 52
2 1 1 87 63 29 59 37 20
3 32 88 75 48 57 90 21 58
4 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 B
d1 d2 d1 d2
Shipped Sold Shipped Sold Shipped Sold Shipped Sold
0 51 60 92 20 14 82 71 86
1 74 23 74 2 87 21 99 52
2 1 1 87 63 29 59 37 20
3 32 88 75 48 57 90 21 58
4 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论