代码之家 › 专栏 › 技术社区 › JE_Muc

移动不重叠的列,使其重叠/对齐

pandas-groupby aggregate-functions time-series pandas python

JE_Muc · 技术社区 · 7 年前

1min minor 在这个示例中,它有时会延迟几行,有时会正确对齐。我需要找到一种方法,将“minor”中非零块的末端与“minor”中非零块的末端对齐 major ,如示例所示:

major = [0,0,0,0,0,0,0,0,4,4,4,4,4,5,6,7,0,0,0,0,4,3,5,6,4,0,0,0]
minor = [0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,0,0,0,0,0,1,.9,0]
# correctly aligned minor row:
minor_aligned = [0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,0,0,0,0,0,0,0,1,.9,0,0,0]
df = pd.DataFrame(
data={'major': major, 'minor': minor, 'minor_aligned': minor_aligned})
df.index.name = 'index'

预期产出:
中的值应按中所示对齐 minor_aligned .

简要说明:
中每个连续非零值块的最后一个非零值必须与中每个块的最后一个非零值对齐 专业 ,如图所示 . 以下附加限制适用:

少数的 将在95%的时间内精确为1(或0),其余值将介于两者之间。
少数的 > 0 哪里 major > 0
少数的 最多可以与中的对应块一样长 专业 ,但永远不会。它的长度通常比伦敦的街区要短得多 专业
少数的 必须是 0

到目前为止,我所尝试的:
从[这篇文章]我复制了 block -此外,我还尝试实现了一些掩蔽,并用各种方法进行了实验 cumcount cumsum

df['mask_mult'] = pd.DataFrame(  # mask where shifted rows exist
    np.where((df.minor != 0.) & (df.major == 0.), 1 * df.minor, 0),
    index=df.index, columns=['outliers'])
# block counting method:
df['block'] = (df.minor.shift(1) != df.minor).astype(int).cumsum()
df.loc[:, 'block'][df['minor'] == 0] = 0  # set zero-blocks to zero

使用groupby、categories和aggregate(不知道如何更好地使用它),我尝试使用掩码/块,但没有成功:

# make block counting categories:
df_cat = df.set_index(pd.cut(df.block, np.arange(-1, df.block.max() + 1)))
# groupby blocks and use mask as amount of shift indices:
df_grpd = df.groupby('block').sum()

我想我可以在 df_cat 获取移位索引或迭代中的分组块 df_grpd 要执行相同的操作(并使用 mask 作为要移位的行数),但在这两种情况下,由于 0.9 -价值观。

你知道我如何利用这些价值观吗如果可能的话,完全避免循环?
提前谢谢!

1 回复 | 直到 7 年前

JE_Muc 7 年前

在了解了如何解决问题后,我找到了一个解决方案 agg / aggregate 作品我无法完全避免循环,但至少它只是在聚合块上循环,而其余的是矢量化的。
只要满足沿轴1连接的一般形状和索引要求,此解决方案应适用于大多数类型的输入/类型。

def align_non_overlapping(ser_maj, ser_min, full_output=False):
    minor_colname = (  # backup name of minor if available
        ser_min.name if isinstance(ser_min, pd.Series)
        else ser_min.column if isinstance(ser_min, pd.DataFrame) else 'minor')
    # merge both series in df
    df = pd.DataFrame({'major': ser_maj, 'minor': ser_min})
    df_idx = df.index  # backup and drop index for easy general shifting
    df.reset_index(drop=True, inplace=True)
    # make mask where values are not overlapping
    df['mask_no'] = np.where((df.minor != 0.) & (df.major == 0.), 1, 0)
    # make mask where minor values are not zero
    df['mask_nz'] = (df.minor != 0).astype(int)
    # get blocks of consecutive non-zero values in minor
    df['block'] = (
        df['mask_nz'].shift(1) != df['mask_nz']).astype(int).cumsum()
    # set block to zero where minor is zero
    df.loc[df['minor'] == 0., 'block'] = 0
    # set block to zero where
    # generate shifting information. summing of mask_no gives amount of non
    # overlapping rows (n rows to shift), index min and max gives start and
    # end indices of blocks of non zero blocks. reset index to be able to apply
    # aggregate function on index
    shifter = df.reset_index().groupby('block').agg(
        {'mask_no': 'sum',
         'index': {'index_start': 'min', 'index_stop': 'max'}})
    # drop first level of MultiIndex for easier indexing
    shifter.columns = shifter.columns.droplevel(0)
    # loop over blocks and shift each block by aggregated values
    for blck in shifter.index:
        if shifter.loc[blck, 'sum'] == 0:  # skip all zero blocks/shifts
            continue
        n_shift, istart, iend = shifter.loc[blck]  # extract shifting bounds
        df.loc[istart - n_shift:iend, 'minor'] = np.roll(  # roll window
            df.loc[istart - n_shift:iend, 'minor'], -n_shift)
    df.set_index(df_idx, inplace=True)  # set backup index
    df.rename(columns={'minor': minor_colname}, inplace=True)  # set old name
    if not full_output:  # return full output with information only if required
        return df[minor_colname]
    else:
        return df

测试结果与预期结果对比:

ser_maj = pd.Series([0,0,0,0,0,0,0,0,4,4,4,4,4,5,6,7,0,0,0,0,4,3,5,6,4,0,0,0], name='major')
ser_min = pd.Series([0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,0,0,0,0,0,1,.9,0], name='minor')
minor_aligned = [0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,0,0,0,0,0,0,0,1,.9,0,0,0]
print(align_non_overlapping(ser_maj, ser_min, full_output=False) == minor_aligned).all())
# Out: True

这些条件目前被忽略:

小调只能是 > 0 哪里 major > 0
小调中的非零块最多可以与大调中的对应块一样长,但不能更长。它通常比大调的街区要短得多

但两者都可以很容易地用 df[df.major == 0] = 0 .