多重索引上的重采样（）

Question

起点

我有一个数据帧df，它有一个三级多索引。最内层是datetime。你知道吗

                                   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

可以使用以下代码重新创建此数据帧：

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()

期望结果

我感兴趣的日期范围是2018-07-31到2018-12-31。对于id_1和id_2的每个组合，我想对值进行重采样。你知道吗

对于('ADH10685', 'CA1P0')，我想得到从九月到十二月的0值。对于CB0N0，我想将九月设置为0，对于CB0P0，我不想做任何更改。你知道吗

                                   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

我所尝试的

我问了几个与这个主题相关的问题，所以我知道如何设置日期的上限和下限，以及如何在保持非value序列不变的情况下重新采样。你知道吗

我已经开发了下面的代码，如果我硬编码切片每一个级别。你知道吗

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()

我正在寻找关于如何最好地遍历大型数据帧并将逻辑应用于每对(id_1, id_2)的指导。我也期待着清理我的样本代码以上更有效。你知道吗

Answer 1

您可以使用reindex()来提取缺少的月份：

# create the MultiIndex based on the existing df.index.levels
midx = pd.MultiIndex.from_product(df.index.levels, names=df.index.names)

# run reindex() with the new indexes and then fix Nan `value` column
df1 = df.reindex(midx).fillna({'value':0})

df1                                                                                                                 
#Out[41]: 
#                                   value      data_1 data_2  data_3  data_4
#id_1     id_2  effective_date                                              
#ADH10685 CA1P0 2018-07-31       0.000048  17901701.0   3mra  Actual  198.00
#               2018-08-31       0.000048  17901701.0   3mra  Actual  198.00
#               2018-09-30       0.000000         NaN    NaN     NaN     NaN
#               2018-10-31       0.000000         NaN    NaN     NaN     NaN
#               2018-11-30       0.000000         NaN    NaN     NaN     NaN
#               2018-12-31       0.000000         NaN    NaN     NaN     NaN
#         CB0N0 2018-07-31       4.010784  17901701.0   3mra  Actual    0.01
#               2018-08-31       2.044298  17901701.0   3mra  Actual    0.01
#               2018-09-30       0.000000         NaN    NaN     NaN     NaN
#               2018-10-31      11.493831  17901701.0   3mra  Actual    0.01
#               2018-11-30      13.929844  17901701.0   3mra  Actual    0.01
#               2018-12-31      21.500490  17901701.0   3mra  Actual    0.01
#         CB0P0 2018-07-31      22.389493  17901701.0   3mra  Actual    0.03
#               2018-08-31      23.600726  17901701.0   3mra  Actual    0.03
#               2018-09-30      45.105458  17901701.0   3mra  Actual    0.03
#               2018-10-31      32.249056  17901701.0   3mra  Actual    0.03
#               2018-11-30      60.790889  17901701.0   3mra  Actual    0.03
#               2018-12-31      46.832914  17901701.0   3mra  Actual    0.03

# select columns except the 'value' column
cols = df1.columns.difference(['value'])

# update the selected columns with ffill/bfill per groupby on level=[0,1]
df1.loc[:,cols] = df1.loc[:,cols].groupby(level=[0,1]).transform('ffill')

Answer 2

首先，将每组id_1，id_2重新索引为dt。你知道吗

dt = pd.date_range('2018-07-31', '2018-12-31', freq='M')

df = (df.reset_index()
        .groupby(['id_1', 'id_2'])
        .apply(lambda x: x.set_index('effective_date').reindex(dt))
        .drop(columns=['id_1', 'id_2'])
        .reset_index()
        .rename(columns={'level_2':'effective_date'}))

然后在列值中填充缺少的值。你知道吗

df['value'] = df['value'].fillna(0)

填充其余缺少的值。你知道吗

df = df.groupby(['id_1', 'id_2']).apply(lambda x: x.ffill(axis=0).bfill(axis=0))

将id_1，id_2，生效日期设置回索引。你知道吗

df.set_index(['id_1', 'id_2', 'effective_date'], inplace=True)