擅长:python、mysql、java
<p>如果数据中缺少年份和月份,则可以对数据执行<code>reindex</code>操作并创建<code>pivot_table</code></p>
<pre><code>df = df.groupby(["ID", "RY", "Month"])['INCIDENT_NO'].sum().reset_index()
idx = pd.MultiIndex.from_product([df['ID'].unique(),
range(df['RY'].min(), df['RY'].max()+1),
range(df['Month'].min(), df['Month'].max()+1)],
names=['ID', 'RY', 'Month'])
(df.set_index(["ID", "RY", "Month"]).reindex(idx).sort_index().fillna(0).reset_index()
.pivot_table(index='ID', values='INCIDENT_NO', columns='RY', aggfunc='sum'))
</code></pre>
<hr/>
<pre><code>Out[205]:
RY 2011 2012 2013 2014 2015 2016 2017 2018 2019
ID
AB10 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 6.0
AB1286 2.0 2.0 0.0 1.0 0.0 0.0 0.0 0.0 0.0
</code></pre>