python - How to apply euclidean distance function to a groupby object in pandas dataframe?

Question

I have a set of objects and their positions over time. I would like to get the average distance between objects for each time point. An example dataframe is as follows:

time = [0, 0, 0, 1, 1, 2, 2]
x = [216, 218, 217, 280, 290, 130, 132]
y = [13, 12, 12, 110, 109, 3, 56]
car = [1, 2, 3, 1, 3, 4, 5]
df = pd.DataFrame({'time': time, 'x': x, 'y': y, 'car': car})
df

             x       y      car
     time
      0     216     13       1
      0     218     12       2
      0     217     12       3
      1     280     110      1
      1     290     109      3
      2     130     3        4
      2     132     56       5

The end result I would like to have is:

df2

              average distance
              between cars       
     time
      0           1.55     
      1           10.05     
      2           53.04    

any idea on how to proceed? I've been trying apply the scipy.spatial.distance function to the dataframe, but I'm not sure how to apply it to df.groupby('time'), and then get the mean value of all those distances. Any help appreciated!

Answer 1

For me using apply or for loop does not have much different

l1=[]
l2=[]

for y,x in df.groupby('time'):
    v=np.triu(spatial.distance.cdist(x[['x','y']].values, x[['x','y']].values),k=0)

    v = np.ma.masked_equal(v, 0)
    l2.append(np.mean(v))
    l1.append(y)


pd.DataFrame({'ave':l2},index=l1)

Out[250]: 
         ave
0   1.550094
1  10.049876
2  53.037722