大熊猫 - pivot_table,同时维持秩序失败
问题描述:
我有以下的数据帧,其中一周是不是ISO星期,但财政周(1月的第一个星期,六月的最后一周52):大熊猫 - pivot_table,同时维持秩序失败
> df
domain week count
0 A 43 5
1 A 45 1
2 A 50 1
3 A 51 4
4 A 1 3
5 A 3 12
6 B 43 1
7 B 44 1
8 B 45 4
9 B 50 11
10 B 2 3
11 B 3 12
12 C 51 6
13 C 1 14
14 C 5 1
我想转动这个表,同时保留一周,以获得新的数据帧,将看起来像值是计数以下和柱有域:
> new_df
week A B C
43 5 1 NaN
44 NaN 1 NaN
45 1 4 NaN
50 1 11 NaN
51 4 NaN 6
1 3 NaN 14
2 NaN 3 NaN
3 12 12 NaN
5 NaN NaN 1
我尝试使用追星族和脱胶,但得到此错误:
> df = df.groupby(['week'], sort=False)['count'].unstack('domain')
AttributeError: Cannot access callable attribute 'unstack' of 'SeriesGroupBy' objects, try using the 'apply' method
答
选项1]可以使用自定义排序weeks
指数助手和.loc
In [4810]: weeks = pd.Index(list(range(26, 52)) + list(range(26)))
In [4819]: dfp = df.groupby(['week','domain'])['count'].sum().unstack()
In [4820]: dfp.loc[weeks & dfp.index]
Out[4820]:
domain A B C
43 5.0 1.0 NaN
44 NaN 1.0 NaN
45 1.0 4.0 NaN
50 1.0 11.0 NaN
51 4.0 NaN 6.0
1 3.0 NaN 14.0
2 NaN 3.0 NaN
3 12.0 12.0 NaN
5 NaN NaN 1.0
选项2]或者,使用pivot
In [4821]: dfp = df.pivot('week', 'domain', 'count')
In [4822]: dfp.loc[weeks & dfp.index]
Out[4822]:
domain A B C
43 5.0 1.0 NaN
44 NaN 1.0 NaN
45 1.0 4.0 NaN
50 1.0 11.0 NaN
51 4.0 NaN 6.0
1 3.0 NaN 14.0
2 NaN 3.0 NaN
3 12.0 12.0 NaN
5 NaN NaN 1.0
选项3]或者,的reindex
代替.loc
In [4830]: dfp.reindex(weeks & dfp.index)
Out[4830]:
domain A B C
43 5.0 1.0 NaN
44 NaN 1.0 NaN
45 1.0 4.0 NaN
50 1.0 11.0 NaN
51 4.0 NaN 6.0
1 3.0 NaN 14.0
2 NaN 3.0 NaN
3 12.0 12.0 NaN
5 NaN NaN 1.0
详细
In [4826]: weeks
Out[4826]:
Int64Index([26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42,
43, 44, 45, 46, 47, 48, 49, 50, 51, 0, 1, 2, 3, 4, 5, 6, 7,
8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24,
25],
dtype='int64')
In [4827]: weeks & dfp.index
Out[4827]: Int64Index([43, 44, 45, 50, 51, 1, 2, 3, 5], dtype='int64')
答
你需要的week
的定制订单,所以需要ordered categorical与客户订单,并省略sort=False
:
cats = list(range(26, 52)) + list(range(26))
print (cats)
[26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46,
47, 48, 49, 50, 51, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15,
16, 17, 18, 19, 20, 21, 22, 23, 24, 25]
df['week'] = df['week'].astype('category', ordered=True, categories=cats)
df = df.groupby(['week','domain'])['count'].sum().unstack()
print (df)
domain A B C
week
43 5.0 1.0 NaN
44 NaN 1.0 NaN
45 1.0 4.0 NaN
50 1.0 11.0 NaN
51 4.0 NaN 6.0
1 3.0 NaN 14.0
2 NaN 3.0 NaN
3 12.0 12.0 NaN
5 NaN NaN 1.0
的问题是,周44和周2是不必要的。第44周应该介于43和45之间,第2周应介于1和3之间。 –
嗯,所以排序是[26,27 ...,51,0,1,...,25]? – jezrael