I have two pandas dataframes dfA,dfB with different lengths & columns. I want to create new column on dfA with values from dfB with conditions comparing timestamp values from dfA and dfB.
The new column named "idx" will be filled with values from dfB"h_id" that meets the condition (dfA.timestamp1 < dfB.timestop && dfA.timestamp1>dfB.timestart)
import pandas as pd
data1 = {
"timestamp1": ["2022-04-06 09:24:56.437", "2022-04-06 07:44:21.807", "2022-04-06 07:44:01.890","2022-04-06 06:24:21.890"],
"plate": ["K9", "K11", "K7","K2"]
}
data2 = {
"timestart": ["2022-04-06 08:49:40.000", "2022-04-06 08:05:28.000", "2022-04-06 07:20:24.000","2022-04-06 06:35:10.000","2022-04-06 05:49:03.000"],
"timestop": ["2022-04-06 09:34:17.000","2022-04-06 08:49:36.000","2022-04-06 08:05:21.000","2022-04-06 07:20:16.000","2022-04-06 06:35:05.000"],
"h_id": [1001, 1002, 1003,1004,1005]
}
data3 = {
"timestamp1": ["2022-04-06 09:24:56.437", "2022-04-06 07:44:21.807", "2022-04-06 07:44:01.890","2022-04-06 06:24:21.890"],
"plate": ["K9", "K11", "K7","K2"],
"idx":[1001,1003,1003,1005]
}
dfA = pd.DataFrame(data1)
dfB = pd.DataFrame(data2)
dfA_new = pd.DataFrame(data3)
