根据 pandas 中的时间（HH：MM：SS）值将一列从一个数据帧转移到另一个数据帧

发布于 2025-01-10 03:40:21 字数 945 浏览 0 评论 0原文

我有两个数据框， df1 有“时间”列，df2 有 3 列“开始时间”、“结束时间”和“值” 所有基于时间的列均采用 HH:MM:SS 格式且为对象类型。

我想根据时间列将“值”列从 df2 带到 df1，即如果 df1["Time"] 位于 df2["Start Time"] 和 df2["End Time"] 之间，则将相应的值与df1 中的“时间”列。

以下是我的输入数据帧：

df1=pd.DataFrame({'Time':['10:27:30','6:27:00','14:57:59']})

df2=pd.DataFrame({'Start Time':['10:20:33','05:53:42','15:00:00'],
 'End Time': ['11:20:33','06:28:42','15:30:59'],
 'Value':[123,360,100]})

df1 中所需的输出

我正在尝试做这样的事情

df1['Time'] = pd.to_datetime(df1['Time'])
df2['Start Time'] = pd.to_datetime(df2['Start Time'])
df2['End Time'] = pd.to_datetime(df2['End Time'])


if (df2['Start Time'] <= df1['Time']<=df2['End Time']):
    df=pd.merge(df1,df2,on=['Key1','Key2'],how='left')

原文

I have two dataframes,
df1 has a column "Time" and df2 has 3 columns "Start Time", "End Time" and "Value"
all the time based columns are in HH:MM:SS format and are of object type.

I want to bring column "Value" from df2 to df1 based on time's column, i.e. if df1["Time"] is between the df2["Start Time"] and df2["End Time"] then put that respective value against the "Time" column in df1.

Below are my input dataframes:

df1=pd.DataFrame({'Time':['10:27:30','6:27:00','14:57:59']})

df2=pd.DataFrame({'Start Time':['10:20:33','05:53:42','15:00:00'],
 'End Time': ['11:20:33','06:28:42','15:30:59'],
 'Value':[123,360,100]})

required output in df1

I am tryting to do something like this

df1['Time'] = pd.to_datetime(df1['Time'])
df2['Start Time'] = pd.to_datetime(df2['Start Time'])
df2['End Time'] = pd.to_datetime(df2['End Time'])


if (df2['Start Time'] <= df1['Time']<=df2['End Time']):
    df=pd.merge(df1,df2,on=['Key1','Key2'],how='left')

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

成熟的代价 2025-01-17 03:40:21

在两个 DataFrame 之间使用交叉联接，然后按进行过滤Series. Between 和最后通过右连接添加缺失的行：

df1['Time'] = pd.to_datetime(df1['Time'].astype(str))
df2['Start Time'] = pd.to_datetime(df2['Start Time'].astype(str))
df2['End Time'] = pd.to_datetime(df2['End Time'].astype(str))


df = df1.merge(df2, how='cross')
df = df[df['Time'].between(df['Start Time'], df['End Time'])].merge(df1, how='right')
print (df)
                 Time          Start Time            End Time  Value
0 2022-02-25 10:27:30 2022-02-25 10:20:33 2022-02-25 11:20:33  123.0
1 2022-02-25 06:27:00 2022-02-25 05:53:42 2022-02-25 06:28:42  360.0
2 2022-02-25 14:57:59                 NaT                 NaT    NaN

最后如果需要时间：

df['Time'] = df['Time'].dt.time
df['Start Time'] = df['Start Time'].dt.time
df['End Time'] = df['End Time'].dt.time
print (df)

       Time Start Time  End Time  Value
0  10:27:30   10:20:33  11:20:33  123.0
1  06:27:00   05:53:42  06:28:42  360.0
2  14:57:59        NaT       NaT    NaN

Use cross join between both DataFrames, then filter by Series.between and last add missing rows by right join:

df1['Time'] = pd.to_datetime(df1['Time'].astype(str))
df2['Start Time'] = pd.to_datetime(df2['Start Time'].astype(str))
df2['End Time'] = pd.to_datetime(df2['End Time'].astype(str))


df = df1.merge(df2, how='cross')
df = df[df['Time'].between(df['Start Time'], df['End Time'])].merge(df1, how='right')
print (df)
                 Time          Start Time            End Time  Value
0 2022-02-25 10:27:30 2022-02-25 10:20:33 2022-02-25 11:20:33  123.0
1 2022-02-25 06:27:00 2022-02-25 05:53:42 2022-02-25 06:28:42  360.0
2 2022-02-25 14:57:59                 NaT                 NaT    NaN

Last if need times:

df['Time'] = df['Time'].dt.time
df['Start Time'] = df['Start Time'].dt.time
df['End Time'] = df['End Time'].dt.time
print (df)

       Time Start Time  End Time  Value
0  10:27:30   10:20:33  11:20:33  123.0
1  06:27:00   05:53:42  06:28:42  360.0
2  14:57:59        NaT       NaT    NaN

回复收藏 0 原文

~没有更多了~