如何将函数应用于数据帧中行中的下一个非空单元格

Question

我有一个巨大的数据框

          23/12/2020 15:38     23/12/2020 15:39   23/12/2020 15:40

1                                [12,10]           [15,10]              
2           [52, 21]                               [14,7]
3           [1,0]                [10,14]

数据框中的值对应于地理坐标。我想有一个新的数据框，它表示日期和坐标之间的平均速度。我编写了一个函数distance(wsp1, wsp2)，它将2个坐标列表作为输入，并返回它们之间的距离。我还有一个函数，它计算两个日期之间的差值diff_between_dates(date1, date2)。我的问题是如何创建一个新的数据帧，它在第一列中具有一行中前两个非空单元格之间的平均速度，在第二列中具有一行中第二个和第三个非空单元格之间的平均速度，依此类推。因此，在我的示例中，期望的输出是：

       1   
1    distance([15,10],[12,10])/diff_between_dates(23/12/2020 15:40,23/12/2020 15:39)
2    distance([52,21],[14,7])/diff_between_dates(23/12/2020 15:40,23/12/2020 15:38)
3    distance([10,14],[1,0])/diff_between_dates(23/12/2020 15:39,23/12/2020 15:38)

Answer 1

import io
import pandas as pd
import numpy as np


# create sample df
df_str = '''
23/12/2020 15:38;23/12/2020 15:39;23/12/2020 15:40
;[12,10];[15,10]              
[52, 21];;[14,7]
[1,0];[10,14];
'''
df = pd.read_csv(io.StringIO(df_str.strip()), sep=';')
df.index = [1,2,3]
df = df.applymap(lambda x: eval(x) if isinstance(x, str) else x)
print(df)

#   23/12/2020 15:38 23/12/2020 15:39 23/12/2020 15:40
# 1              NaN         [12, 10]         [15, 10]
# 2         [52, 21]              NaN          [14, 7]
# 3           [1, 0]         [10, 14]              NaN

# stack -> DataFrame to Series -> reset_index
dfn = df.stack().reset_index()
dfn.columns = ['idx', 'time', 'coor']
print(dfn)

#    idx              time      coor
# 0    1  23/12/2020 15:39  [12, 10]
# 1    1  23/12/2020 15:40  [15, 10]
# 2    2  23/12/2020 15:38  [52, 21]
# 3    2  23/12/2020 15:40   [14, 7]
# 4    3  23/12/2020 15:38    [1, 0]
# 5    3  23/12/2020 15:39  [10, 14]

# convert datatime
dfn['time'] = pd.to_datetime(dfn['time'])

print(dfn)
#    idx                time      coor
# 0    1 2020-12-23 15:39:00  [12, 10]
# 1    1 2020-12-23 15:40:00  [15, 10]
# 2    2 2020-12-23 15:38:00  [52, 21]
# 3    2 2020-12-23 15:40:00   [14, 7]
# 4    3 2020-12-23 15:38:00    [1, 0]
# 5    3 2020-12-23 15:39:00  [10, 14]

# sort values by idx and time
dfn.sort_values(['idx', 'time'], inplace=True)

# def sample function
def distance(x,y):
    res = abs(x[0] - x[1]) ** 2 + abs(y[0] - y[1]) ** 2
    res = np.sqrt(res)
    return res

def diff_between_dates(x,y):
    time_diff = abs(pd.to_datetime(x) - pd.to_datetime(y))
    return time_diff.seconds


# calculate by split dfn by group using idx
res_dict = dict()
for idx, group in dfn.groupby('idx'):
    # can modify by purpose
    dates = group.iloc[:2, 1].tolist()
    coors = group.iloc[:2, 2].tolist()
    
    # calculate use function 
    dist_diff = distance(*coors)
    dates_diff = diff_between_dates(*dates)

    result = dist_diff/dates_diff
    res_dict[idx] = result

obj_res = pd.Series(res_dict)

print(obj_res)

# 1    0.089753
# 2    0.264837
# 3    0.068718
# dtype: float64