i would like to merge 2 tables with multiple conditions.
I'm seeking guidance on how to accomplish this using a Python script.
is there any way to merge it using pd.merge?
我想合并两个有多个条件的表。我正在寻求如何使用Python脚本实现这一点的指导。有没有办法用pd.merge合并它?
table1 left merge table2 where
table1左合并table2,其中
- table1.item = table2.item and table1.type = table2.category
- if table1's type_c=1, match table1.entry_time > table2.update_time and (table1.entry_time-table2.update_time)<24hours
- if table1's type_c=2, match table1.exit_time < table2.update_time and (table1.exit_time-table2.update_time)<24hours
below is the input and expected output
以下是输入和预期输出
table1
表1
ITEM |
CATEGORY |
TYPE |
ENTRY_TIME |
EXIT_TIME |
ITEM1 |
A |
1 |
2023-08-30 08:00:00 |
2023-08-30 09:00:00 |
ITEM1 |
A |
1 |
2023-09-01 10:00:00 |
2023-09-01 11:00:00 |
ITEM1 |
C |
2 |
2023-09-01 12:00:00 |
2023-09-01 13:00:00 |
ITEM2 |
B |
2 |
2023-09-02 16:00:00 |
2023-09-02 17:00:00 |
ITEM3 |
D |
1 |
2023-09-02 18:00:00 |
2023-09-02 19:00:00 |
table2
表2
ITEM |
CATEGORY |
REF_ID |
BOOKING_C |
UPDATE_TIME |
ITEM1 |
A |
1111 |
C |
2023-08-30 07:00:00 |
ITEM1 |
C |
2222 |
R |
2023-09-01 14:00:00 |
ITEM2 |
B |
3333 |
R |
2023-09-02 18:00:00 |
ITEM3 |
D |
4444 |
C |
2023-09-02 17:00:00 |
expected result
预期结果
ITEM |
CATEGORY |
TYPE |
ENTRY_TIME |
EXIT_TIME |
REF_ID |
BOOKING_C |
UPDATE_TIME |
ITEM1 |
A |
1 |
2023-08-30 08:00:00 |
2023-08-30 09:00:00 |
1111 |
C |
2023-08-30 07:00:00 |
ITEM1 |
A |
1 |
2023-09-02 10:00:00 |
2023-09-02 11:00:00 |
|
|
|
ITEM1 |
C |
2 |
2023-09-01 12:00:00 |
2023-09-01 13:00:00 |
2222 |
R |
2023-09-01 14:00:00 |
ITEM2 |
B |
2 |
2023-09-02 16:00:00 |
2023-09-02 17:00:00 |
3333 |
R |
2023-09-02 18:00:00 |
ITEM3 |
D |
1 |
2023-09-02 18:00:00 |
2023-09-02 19:00:00 |
4444 |
|
|
更多回答
优秀答案推荐
我是一名优秀的程序员,十分优秀!