多Excel表日期匹配关联返回空DataFrame问题排查求助

我有三个Excel表格，需要拼接它们的部分列：

• Coilid：1084行
• Retifica5：1456行
• Retifica4：1456行

需求是提取Coilid的数据，验证其中的DT_START是否处于Retifica5的Data Entrada与Data Saída区间内，若匹配则输出Coilid中的CD_COIL、Retifica5中的Ret.字段；同时对Retifica4执行相同逻辑。

但最终运行结果返回空DataFrame：

Empty DataFrame
Columns: []
Index: []

变量cc的长度为0，我找不到问题所在。

import pandas as pd

# Load dataframes
retifica5 = pd.read_excel('C:\Doideira.xlsx', sheet_name='Plan1')
retifica4 = pd.read_excel('C:\Doideira.xlsx', sheet_name='Plan2')
coilid = pd.read_excel('C:\Arquivoparamim.xlsx', sheet_name='Planilha2')

# Sort dataframes by dates
retifica5 = retifica5.sort_values(by=['Data Retífica'], ascending=True)
retifica4 = retifica4.sort_values(by=['Data Retífica'], ascending=True)
coilid = coilid.sort_values(by=['DT_START'], ascending=True)

# Perform the comparison using vectorized operations
coildate = coilid['DT_START']
coil = coilid['CD_COIL']
retidata5 = retifica5['Data Entrada']
retidatas5 = retifica5['Data Saída']
retidata4 = retifica4['Data Entrada']
retidatas4 = retifica4['Data Saída']
reti5 = retifica5['Ret.']
reti4 = retifica4['Ret.']


coildate_filtered = coildate.reset_index(drop=True)
coil_filtered = coil.reset_index(drop=True)
retidata5_filtered = retidata5.reset_index(drop=True)
retidatas5_filtered = retidatas5.reset_index(drop=True)
retidata4_filtered = retidata4.reset_index(drop=True)
retidatas4_filtered = retidatas4.reset_index(drop=True)
reti5_filtered = reti5.reset_index(drop=True)
reti4_filtered = reti4.reset_index(drop=True)

cc=pd.DataFrame()
#Começando a unir lógicas
x=0
y=0
for x in range (coilname_filtered.shape[0]):
    for y in range  (retidata4_filtered.shape[0]):
        if (coildate_filtered[x]>retidata5_filtered[y])and(coildate_filtered[x]<retidatas5_filtered[y]):
            cc=[coilname_filtered[x],reti5_filtered[y]]
        
        break
        
print(cc)

coilname_filtered

0       A623040100_1
1       A624140300_1
2       A624140400_1
3       A624140500_1
4       A624140600_1
            ...     
1079    C498350300_1
1080    C498350400_1
1081    C498350500_1
1082    C498350600_1
1083    C498350800_1
Name: CD_COIL, Length: 1084, dtype: object 


Coildata_filtered

0       44198.726921
1       44200.816458
2       44200.820231
3       44200.825486
4       44200.847986
            ...     
1079    44712.710289
1080    44712.717431
1081    44712.722674
1082    44712.976574
1083    44712.993438
Name: DT_START, Length: 1084, dtype: float64

retidata5_filtered
 0       44455.912500
1       44441.646528
2       44331.458333
3       44331.824306
4       44329.004167
            ...     
1503    45024.504861
1504    45025.026389
1505    45025.168750
1506    45024.771528
1507    45026.004167
Name: Data Entrada, Length: 1508, dtype: float64 

retidatas5_filtered
0       44455.988194
1       44236.783333
2       44331.465278
3       44331.965278
4       44329.038889
            ...     
1503    45024.771528
1504    45025.168750
1505    45026.004167
1506    45025.026389
1507    45026.206250
Name: Data Saída, Length: 1508, dtype: float64

Reti5_filtered

Name: Data Saída, Length: 1508, dtype: float64 0       18
1       18
2       18
3       18
4       18
        ..
1503    19
1504    19
1505    19
1506    18
1507    18
Name: Ret., Length: 1508, dtype: int64

1. 变量名错误：代码中使用coilname_filtered，但实际定义的变量是coil_filtered，属于硬编码错误。
2. 循环逻辑失效：内层循环直接添加break，导致每个Coil行只检查Retifica表的第一行，无法遍历所有区间，自然难以匹配。
3. 区间条件错误：观察数据，Coilid的DT_START最小值为44198，而Retifica5的Data Entrada最小值为44329，coildate_filtered[x]>retidata5_filtered[y]的条件永远不成立，没有匹配结果。同时Retifica表排序字段错误，应按Data Entrada排序而非Data Retífica。
4. DataFrame赋值错误：将cc定义为DataFrame后，用cc=[...]赋值会把它变成列表，而非DataFrame，最终输出不符合预期。

以下是修复后的代码，用向量化操作替代嵌套循环，同时解决上述所有问题：

import pandas as pd

# 用原始字符串避免路径转义问题
retifica5 = pd.read_excel(r'C:\Doideira.xlsx', sheet_name='Plan1')
retifica4 = pd.read_excel(r'C:\Doideira.xlsx', sheet_name='Plan2')
coilid = pd.read_excel(r'C:\Arquivoparamim.xlsx', sheet_name='Planilha2')

# 按区间起始日期排序，优化匹配效率
retifica5 = retifica5.sort_values(by=['Data Entrada'], ascending=True).reset_index(drop=True)
retifica4 = retifica4.sort_values(by=['Data Entrada'], ascending=True).reset_index(drop=True)
coilid = coilid.sort_values(by=['DT_START'], ascending=True).reset_index(drop=True)

# 清洗数据：确保Data Saída >= Data Entrada（避免无效区间）
retifica5 = retifica5[retifica5['Data Saída'] >= retifica5['Data Entrada']]
retifica4 = retifica4[retifica4['Data Saída'] >= retifica4['Data Entrada']]

# 定义匹配函数，实现Coil与Retifica表的区间匹配
def match_coil_retifica(coil_df, retifica_df):
    # 向量化广播比较所有Coil日期与Retifica区间
    dt_start = coil_df['DT_START'].values[:, None]
    entrada = retifica_df['Data Entrada'].values
    saida = retifica_df['Data Saída'].values
    
    # 标记每个Coil日期是否在任意Retifica区间内
    matches = (dt_start >= entrada) & (dt_start <= saida)
    # 获取每个Coil行的第一个匹配区间索引
    match_indices = matches.argmax(axis=1)
    # 过滤无匹配的行
    valid_mask = matches.any(axis=1)
    
    # 构造结果DataFrame
    result = coil_df.loc[valid_mask, ['CD_COIL']].copy()
    result['Ret.'] = retifica_df.loc[match_indices[valid_mask], 'Ret.'].values
    result['来源表'] = retifica_df.name
    return result

# 给表命名，方便标记来源
retifica5.name = 'Retifica5'
retifica4.name = 'Retifica4'

# 分别匹配两个表
result_5 = match_coil_retifica(coilid, retifica5)
result_4 = match_coil_retifica(coilid, retifica4)

# 合并最终结果
final_result = pd.concat([result_5, result_4], ignore_index=True)

print(final_result)

额外说明 #

• 用向量化操作替代嵌套循环，数据量大时效率提升明显；
• 新增数据清洗步骤，过滤掉Data Saída < Data Entrada的无效区间；
• 合并两个表的匹配结果，方便统一查看；
• 若仍无匹配结果，需检查Coil的DT_START是否真的落在Retifica表的任意区间内，可通过coilid['DT_START'].describe()和Retifica表的日期区间分布交叉验证。

内容的提问来源于stack exchange，提问作者Aline De Souza Silva