如何根据Python中的条件将另一个数据帧中的值分配给当前数据帧？

Question

我想根据DatetimeIndex条件将值从一个数据帧分配给另一个数据帧

我有这个数据帧：（第一个）

date                importance
2006-12-05 10:35:00     HIGH
2006-12-13 02:40:00     LOW

这个数据帧：（第二）

index                     value
2006-12-05 08:03:01.985    6
2006-12-05 08:11:34.130    7
2006-12-05 08:20:05.959    6
2006-12-05 08:28:38.104    6
2006-12-05 08:37:02.995    6
2006-12-05 08:45:35.140    5
2006-12-05 08:54:06.969    6
2006-12-05 09:02:59.928    6
2006-12-05 09:11:32.072    6
2006-12-05 09:20:03.901    6
2006-12-05 09:28:36.046    5
2006-12-05 09:37:00.937    5
2006-12-05 09:45:33.082    6
2006-12-05 09:54:04.911    6
2006-12-05 10:02:04.889    6
2006-12-05 10:10:37.034    5
2006-12-05 10:19:08.863    6
2006-12-05 10:27:41.008    5
2006-12-05 10:36:04.953    5
2006-12-05 10:44:37.098    5
.
.
.
2006-12-13 02:06:00.898    1
2006-12-13 02:14:33.043    1
2006-12-13 02:23:04.872    1
2006-12-13 02:31:03.904    1
2006-12-13 02:39:36.048    1
2006-12-13 02:48:07.878    2
2006-12-13 02:56:40.022    5
2006-12-13 03:05:04.914    2
2006-12-13 03:13:37.058    3
2006-12-13 03:22:08.888    6
2006-12-13 03:31:03.108    1
2006-12-13 03:39:34.937    1
2006-12-13 03:48:07.081    1
2006-12-13 03:56:38.911    2
2006-12-13 04:05:04.117    3

最终结果应该是：

index                      value    new_value
2006-12-05 08:03:01.985    6        
2006-12-05 08:11:34.130    7        
2006-12-05 08:20:05.959    6        
2006-12-05 08:28:38.104    6
2006-12-05 08:37:02.995    6
2006-12-05 08:45:35.140    5
2006-12-05 08:54:06.969    6
2006-12-05 09:02:59.928    6
2006-12-05 09:11:32.072    6
2006-12-05 09:20:03.901    6
2006-12-05 09:28:36.046    5
2006-12-05 09:37:00.937    5
2006-12-05 09:45:33.082    6
2006-12-05 09:54:04.911    6
2006-12-05 10:02:04.889    6
2006-12-05 10:10:37.034    5
2006-12-05 10:19:08.863    6
2006-12-05 10:27:41.008    5        
2006-12-05 10:36:04.953    5            HIGH
2006-12-05 10:44:37.098    5
.
.
.
2006-12-13 02:06:00.898    1
2006-12-13 02:14:33.043    1
2006-12-13 02:23:04.872    1
2006-12-13 02:31:03.904    1
2006-12-13 02:39:36.048    1            LOW
2006-12-13 02:48:07.878    2
2006-12-13 02:56:40.022    5
2006-12-13 03:05:04.914    2
2006-12-13 03:13:37.058    3
2006-12-13 03:22:08.888    6
2006-12-13 03:31:03.108    1
2006-12-13 03:39:34.937    1
2006-12-13 03:48:07.081    1
2006-12-13 03:56:38.911    2
2006-12-13 04:05:04.117    3

我试过这个：

def getNearestDate(items, pivot):
    return min(items, key=lambda x: abs(x - pivot))

items = second_df.index
for pivot in first_df.date:
    d = getNearestDate(items, pivot)
    print(d)
    second_df.loc[second_df.index == d, 'new_value'] = first_df.importance

它会打印最近的日期：

2006-12-05 10:36:04.953000
2006-12-13 02:39:36.048000

因此，在这些日子里，它应该把价值观从“重要性”中分离出来。 此外，在new_value列上，所有内容都是NAN

你能帮我解决这个问题吗

Answer 1

您已经有了second_df.index == d所需的掩码。这将生成一个^{}，其中值True为真，值False为假。您可以将|=多个掩码放在一起，以获得任何掩码中True的所有行。只需将该系列作为“new_value”列附加到第二个数据帧中

mask = False
for pivot in first_df.date:
    mask |= second_df.index == getNearestDate(second_df.index, pivot)
second_df['new_value'] = mask

如果确实希望'X'和''成为True和False的别名，也可以在将序列添加到数据帧之前应用一个简单的lambda来转换它们

mask = False
for pivot in first_df.date:
    mask |= second_df.index == getNearestDate(second_df.index, pivot)
second_df['new_value'] = mask.apply(lambda x: 'X' if bool(x) else '')

编辑：

如果要获取第一个数据帧的importance值，只需使用getNearestDate函数确定哪些行需要该值，然后将它们与第二个数据帧合并

first_df['index'] = first_df.apply(
    lambda x: getNearestDate(second_df.index, x.date),
    axis = 1,
    result_type = 'reduce'
)
second_df = second_df.merge(first_df, how='left', on='index')

Answer 2

只要做些小小的改变，希望它能起作用

loc=[]

    def getNearestDate(items, pivot):
        return min(items, key=lambda x: abs(x - pivot))
    
    items = second_df.index
    for pivot in first_df.date:
        d = getNearestDate(items, pivot)
        loc.append(second_df.set_index('index').index.get_loc(d))
    
    ## Adding Data to your second df   
    second_df['importance']=[]
    for index,locations in enumerate(loc):
        df['importance'][int(location)]=first_df['importance'][index]

Answer 3

您已在loc中使用该条件 second_df.index == d 它在满足条件的索引处返回true，而不是索引

改用 second_df[second_df.index == d].index.values