您可以使用reindex http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reindex.html与新的Multindex
已创建from_product http://pandas.pydata.org/pandas-docs/stable/generated/pandas.MultiIndex.from_product.html:
Notice:
大熊猫的大小和数量有什么区别? https://stackoverflow.com/questions/33346591/what-is-the-difference-between-size-and-count-in-pandas
df = pd.DataFrame({
'month': [0, 0, 0, 0, 1, 1, 1, 2, 2, 7],
'code': ['s_A', 's_A', 's_A', 's_A', 's_A', 's_A', 's_A', 's_B', 's_B', 's_B'],
'id': ['sally1','sally1','sally1','sally','sally','sally','sally','sally','sally','sally']})
print (df)
code id month
0 s_A sally1 0
1 s_A sally1 0
2 s_A sally1 0
3 s_A sally 0
4 s_A sally 1
5 s_A sally 1
6 s_A sally 1
7 s_B sally 2
8 s_B sally 2
9 s_B sally 7
df = df.groupby(['id', 'code', 'month']).size()
n = ['id','code','month']
mux = pd.MultiIndex.from_product([df.index.levels[0],df.index.levels[1], range(13)], names=n)
df = df.reindex(mux, fill_value=0)
print (df)
id code month
sally s_A 0 1
1 3
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 0
12 0
s_B 0 0
1 0
2 2
3 0
...
...
旧的解决方案:
reindex
with unstack http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.unstack.html and stack http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.stack.html,但随后需要一些数据清理:
df = df.groupby(['id', 'code', 'month']).size() \
.to_frame('count') \
.unstack([0,1], fill_value=0) \
.reindex(range(13), fill_value=0) \
.stack([1,2], dropna=False) \
.fillna(0) \
.astype(int) \
.swaplevel(0,2) \
.sort_index()
print (df)
count
code id month
s_A sally 0 1
1 3
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 0
12 0
sally1 0 3
1 0
2 0
3 0
4 0