如何合并不匹配的Pandas DataFrame：关联机器状态与对应班次

我有机器运行状态数据和班次排班数据两类数据，需要将机器状态数据与对应时段的值班班次建立关联，最终合并为以机器数据为基础的格式。

机器运行状态数据示例 #

DateTime                State     Comment
2023-01-01 09:01:300    running   
2023-01-01 10:10:230    stopped   Finished 
2023-01-01 22:50:670    running   
2023-01-02 07:58:990    stopped   Finished
2023-01-02 15:06:800    running
2023-01-02 20:23:550    stopped   User aborted 
2023-01-03 07:02:650    running 
2023-01-03 10:07:130    stopped   Finished

班次排班数据示例 #

（A、B、C为班组，M代表早班，N代表中班）

A  B  C  
Date                  
2023-01-01  M  N  -  
2023-01-02  M  N  -  
2023-01-03  N  -  M  
2023-01-04  N  -  M  

期望合并后的数据格式 #

DateTime                State     Comment      Shift
2023-01-01 09:01:300    running                A
2023-01-01 10:10:230    stopped   Finished     A
2023-01-01 22:50:670    running                B
2023-01-02 07:58:990    stopped   Finished     A
2023-01-02 15:06:800    running                B
2023-01-02 20:23:550    stopped   User aborted B
2023-01-03 07:02:650    running                C
2023-01-03 10:07:130    stopped   Finished     C

内容的提问来源于stack exchange，提问作者Joost Lenten