从基于单索引级别的MultiIndex的数据帧中减去序列

我有一个DataFrame,用于描述笛卡尔2D宇宙中多个“对象”在几个不同“轨迹”上的运动。对于每个“曲目”,我也都有其“目标位置”。示例数据:

objs = ['car', 'bicycle', 'plane']
moves = [f'mov{i}' for i in range(1,11)]
multi = pd.MultiIndex.from_product([objs, moves, range(10)], names=['obj', 'mov', 'time'])
locations = pd.DataFrame(np.random.rand(300,2), columns=['X','Y'], index=multi)
targets = pd.DataFrame(np.random.rand(10,2), columns=['X','Y'], index=moves)

I'm interested in the euclidean-distance between the locations and the targets on each timestamp. Something like
distances = pd.Series(np.random.rand(300), index=multi)

Problem is I can't use Subtract method since both objects need to have the same index, and can't figure out how to get the 2 DataFrames' Indexes to "fit". Anyone has a nice (efficient) way for me to get those distances?

评论
  • pnon
    pnon 回复

    so apparently, unlike the subtract method that needs completely matching index-es for self and other, the sub method can take a level as an argument.
    So there's a simple one-liner for calculating these euclidean distances:
    movements.sub(targets, level=1).pow(2).sum(axis=1).transform(np.sqrt)

  • 小鸡鸡
    小鸡鸡 回复

    IICU:

    targets.reset_index(inplace=True)#Reset index
    targets.columns=['mov','x','y']#Rename columns
    locations.reset_index(inplace=True)#Reset index
    loctar = pd.merge(locations, targets, how='left', on='mov')#Merge location and target to loctar
    loctar[['dXx','dYY']]=loctar[['X','Y']] - loctar[['x','y']].values#caluclate delta x and y
    temp=loctar.loc[:, ~loctar.columns.isin(['obj','mov','time','X','Y','x','y'])]#create temporary datframe with the deltas
    result = ((temp**2).sum(axis=1))**0.5#Calculate euclidean-distance
    result = result.reset_index()#Reset index
    #Can merge result with lotar if you wanted