代码之家 › 专栏 › 技术社区 › MathMan 99

Python:根据日期索引的小时和最近分钟连接两个数据帧

timestamp datetime dataframe pandas python

MathMan 99 · 技术社区 · 4 年前

我有两个不同日期的数据帧,如下所示:

df1 = pd.DataFrame(index=['2022-01-01 00:37:57', '2022-01-01 03:49:12', '2022-01-01 09:30:11'], columns = ['price'])
df1['price'] = [10,13,12]
df1.index = df1.index.rename('date')
df1:
                        price
date                      
2022-01-01 00:37:57     10
2022-01-01 03:49:12     13
2022-01-01 09:30:11     12 

df2 = pd.DataFrame(index=['2022-01-01 00:35:00', '2022-01-01 00:47:00', '2022-01-01 00:56:12', '2022-01-01 03:45:00', '2022-01-01 03:50:32',
                        '2022-01-01 09:29:20', '2022-01-01 09:31:21'], columns=['price'])
df2['price'] = [3000,3210, 2999, 3001, 3027, 3021, 3002]
df2.index = df2.index.rename('date')
df2:
                      price
date                      
2022-01-01 00:35:00   3000
2022-01-01 00:47:00   3210
2022-01-01 00:56:12   2999
2022-01-01 03:45:00   3001
2022-01-01 03:50:32   3027
2022-01-01 09:29:20   3021
2022-01-01 09:31:21   3002

我想左加入df1和df2, df1.join(df2,how='left') ,在小时和最近的一分钟获取以下信息:

df:
                        price_x price_y
date
2022-01-01 00:37:57     10      3000
2022-01-01 03:49:12     13      3210
2022-01-01 09:30:11     12      3021

例如,左边的最后一行在日期“2022-01-01 09:29:20”时连接,因为它最接近“2022-01-01 09:30:11”。

如何做到这一点?

1 回复 | 直到 4 年前

Andrej Kesely 4 年前

尝试 pd.merge_asof() (假设索引为DateTime类型并已排序):

print(
    pd.merge_asof(
        df1,
        df2,
        left_index=True,
        right_index=True,
        direction="nearest",
    )
)

印刷品:

                     price_x  price_y
date                                 
2022-01-01 00:37:57       10     3000
2022-01-01 03:49:12       13     3027
2022-01-01 09:30:11       12     3021

ShyGuyRyRyNewlyTheDataGuy 4 年前

Anrej Kesely给出了很好的回应。我猜熊猫比我更有效率。我没有资格为澄清你的问题添加评论。但是,如果您正在寻找df2中最接近的日期,该日期出现在df1中的日期之前。这个代码可以用。

import pandas as pd
import numpy as np
from datetime import datetime
df1 = pd.DataFrame(index=['2022-01-01 00:37:57', '2022-01-01 03:49:12', '2022-01-01 09:30:11'], columns = ['price'])
df1['price'] = [10,13,12]
df1.index = df1.index.rename('date')
df1 = df1.reset_index()
df2 = pd.DataFrame(index=['2022-01-01 00:35:00', '2022-01-01 00:47:00', '2022-01-01 00:56:12', '2022-01-01 03:45:00', '2022-01-01 03:50:32',
                        '2022-01-01 09:29:20', '2022-01-01 09:31:21'], columns=['price'])
df2['price'] = [3000,3210, 2999, 3001, 3027, 3021, 3002]
df2.index = df2.index.rename('date')
df2 = df2.reset_index()
display(df1)
def min_diff(date, df):
    min_diff = -18000000
    min_index = -1
    for i in range(len(df)):
        difference = int(datetime.strptime((df['date'][i]),"%Y-%m-%d %H:%M:%S").timestamp()) - int(datetime.strptime(date,"%Y-%m-%d %H:%M:%S").timestamp())
        if difference < 0:
            if (difference > min_diff):
                min_diff = difference
                min_index = i
    return min_index

print(df2.loc[min_diff(df1['date'][0], df2)])   
df1['Price from 2'] = ''
for i in range(len(df1)):
    df1.loc[i,'Price from 2'] = df2.loc[min_diff(df1['date'][i], df2),'price']
display(df1)

这将显示以下内容:,

                  date  price Price from 2
0  2022-01-01 00:37:57     10         3000
1  2022-01-01 03:49:12     13         3001
2  2022-01-01 09:30:11     12         3021

如果你只是在寻找最近的约会,而不考虑方向@Anrej Kesely给出了一个很好的回答。希望我们俩都能帮忙!