回答此问题可获得 20 贡献值,回答如果被采纳可获得 50 分。
<h3>起点</h3>
<p>我有一个数据帧<code>df</code>,它有一个三级多索引。最内层是datetime。你知道吗</p>
<pre><code> value data_1 data_2 data_3 data_4
id_1 id_2 effective_date
ADH10685 CA1P0 2018-07-31 0.000048 17901701 3mra Actual 198.00
2018-08-31 0.000048 17901701 3mra Actual 198.00
CB0N0 2018-07-31 4.010784 17901701 3mra Actual 0.01
2018-08-31 2.044298 17901701 3mra Actual 0.01
2018-10-31 11.493831 17901701 3mra Actual 0.01
2018-11-30 13.929844 17901701 3mra Actual 0.01
2018-12-31 21.500490 17901701 3mra Actual 0.01
CB0P0 2018-07-31 22.389493 17901701 3mra Actual 0.03
2018-08-31 23.600726 17901701 3mra Actual 0.03
2018-09-30 45.105458 17901701 3mra Actual 0.03
2018-10-31 32.249056 17901701 3mra Actual 0.03
2018-11-30 60.790889 17901701 3mra Actual 0.03
2018-12-31 46.832914 17901701 3mra Actual 0.03
</code></pre>
<p>可以使用以下代码重新创建此数据帧:</p>
<pre><code>df = pd.DataFrame({'id_1': ['ADH10685','ADH10685','ADH10685','ADH10685','ADH10685','ADH10685','ADH10685','ADH10685','ADH10685','ADH10685','ADH10685','ADH10685','ADH10685'],\
'id_2': ['CA1P0','CA1P0','CB0N0','CB0N0','CB0N0','CB0N0','CB0N0','CB0P0','CB0P0','CB0P0','CB0P0','CB0P0','CB0P0'],\
'effective_date': ['2018-07-31', '2018-08-31', '2018-07-31', '2018-08-31', '2018-10-31', '2018-11-30', '2018-12-31', '2018-07-31', '2018-08-31', '2018-09-30', '2018-10-31', '2018-11-30', '2018-12-31'],\
'value': [0.000048, 0.000048, 4.010784, 2.044298, 11.493831, 13.929844, 21.500490, 22.389493, 23.600726, 45.105458, 32.249056, 60.790889, 46.832914],\
'data_1': [17901701,17901701,17901701,17901701,17901701,17901701,17901701,17901701,17901701,17901701,17901701,17901701,17901701],\
'data_2': ['3mra','3mra','3mra','3mra','3mra','3mra','3mra','3mra','3mra','3mra','3mra','3mra','3mra'],\
'data_3': ['Actual','Actual','Actual','Actual','Actual','Actual','Actual','Actual','Actual','Actual','Actual','Actual','Actual'],\
'data_4': [198.00, 198.00, 0.01, 0.01,0.01,0.01,0.01,0.03,0.03,0.03,0.03,0.03,0.03]})
df.effective_date = pd.to_datetime(df.effective_date)
df = df.groupby(['id_1', 'id_2', 'effective_date']).first()
</code></pre>
<h3>期望结果</h3>
<p>我感兴趣的日期范围是<code>2018-07-31</code>到<code>2018-12-31</code>。对于<code>id_1</code>和<code>id_2</code>的每个组合,我想对值进行重采样。你知道吗</p>
<p>对于<code>('ADH10685', 'CA1P0')</code>,我想得到从九月到十二月的<code>0</code>值。对于<code>CB0N0</code>,我想将九月设置为<code>0</code>,对于<code>CB0P0</code>,我不想做任何更改。你知道吗</p>
<pre><code> value data_1 data_2 data_3 data_4
id_1 id_2 effective_date
ADH10685 CA1P0 2018-07-31 0.000048 17901701 3mra Actual 198.00
2018-08-31 0.000048 17901701 3mra Actual 198.00
2018-09-30 0.000000 17901701 3mra Actual 198.00
2018-10-31 0.000000 17901701 3mra Actual 198.00
2018-11-30 0.000000 17901701 3mra Actual 198.00
2018-12-31 0.000000 17901701 3mra Actual 198.00
CB0N0 2018-07-31 4.010784 17901701 3mra Actual 0.01
2018-08-31 2.044298 17901701 3mra Actual 0.01
2018-09-30 0.000008 17901701 3mra Actual 0.01
2018-10-31 11.493831 17901701 3mra Actual 0.01
2018-11-30 13.929844 17901701 3mra Actual 0.01
2018-12-31 21.500490 17901701 3mra Actual 0.01
CB0P0 2018-07-31 22.389493 17901701 3mra Actual 0.03
2018-08-31 23.600726 17901701 3mra Actual 0.03
2018-09-30 45.105458 17901701 3mra Actual 0.03
2018-10-31 32.249056 17901701 3mra Actual 0.03
2018-11-30 60.790889 17901701 3mra Actual 0.03
2018-12-31 46.832914 17901701 3mra Actual 0.03
</code></pre>
<h3>我所尝试的</h3>
<p>我问了几个与这个主题相关的问题,所以我知道如何设置日期的上限和下限,以及如何在保持非<code>value</code>序列不变的情况下重新采样。你知道吗</p>
<p>我已经开发了下面的代码,如果我硬编码切片每一个级别。你知道吗</p>
<pre><code>min_date = '2018-07-31'
max_date = '2018-12-31'
# Slice to specific combination of id_1 and id_2
s = df.loc[('ADD00785', 'CA1P0')]
if not s.index.isin([min_date]).any():
s.loc[pd.to_datetime(min_date)] = np.nan
if not s.index.isin([max_date]).any():
s.loc[pd.to_datetime(max_date)] = np.nan
s.resample('M').first().fillna({'value': 0}).ffill().bfill()
</code></pre>
<p>我正在寻找关于如何最好地遍历大型数据帧并将逻辑应用于每对<code>(id_1, id_2)</code>的指导。我也期待着清理我的样本代码以上更有效。你知道吗</p>