遍历DataFrame提取特定值时遇索引越界错误，求正确实现方案

问题描述 #

遍历DataFrame提取特定值时，编写的带判断条件的循环始终出现索引越界错误，原代码如下：

new_end = []
for i in range(size):
    if fifo.Same_New.iloc[i] == "New" and fifo.Same_New.iloc[i-1] == "New":
        new_end.append(fifo.EndTime.iloc[i])
    else:
        if fifo.Same_New.iloc[i] == 'Same' and fifo.Same_New.iloc[i+1] =="New":
            new_end.append(fifo.EndTime.iloc[i])
        else:
            new_end.append('Null')

示例DataFrame #

StartTime       EndTime                 Dura    Diff    S/N         
05/01/2024 12:27    05/01/2024 13:04    2224    1036    New
06/01/2024 07:05    06/01/2024 07:06    60      1081    New
06/01/2024 07:06    06/01/2024 07:06    0       0       Same
08/01/2024 10:26    08/01/2024 10:27    32      200     New
08/01/2024 11:45    08/01/2024 11:46    38      78      New
09/01/2024 13:48    09/01/2024 13:51    172     122     New
09/01/2024 16:41    09/01/2024 16:42    60      170     New
09/01/2024 16:43    09/01/2024 17:37    3267    1       Same
09/01/2024 17:38    09/01/2024 17:41    189     1       Same
10/01/2024 10:06    10/01/2024 10:08    142     985     New
10/01/2024 10:51    10/01/2024 10:53    116     43      New
10/01/2024 11:00    10/01/2024 11:00    26      7       New
12/01/2024 12:17    12/01/2024 12:30    752     77      New
15/01/2024 08:01    15/01/2024 08:01    36      1171    New
16/01/2024 15:44    16/01/2024 15:53    577     463     New
16/01/2024 16:43    16/01/2024 17:16    2003    50      New
16/01/2024 17:16    16/01/2024 17:17    69      0       Same
17/01/2024 00:03    17/01/2024 00:04    60      406     New
17/01/2024 00:04    17/01/2024 00:20    943     0       Same
17/01/2024 06:08    17/01/2024 06:09    16      348     New

判断逻辑 #

• 若当前行与下一行的S/N列均为"New"，则返回当前行的EndTime值；
• 若当前行S/N为"New"、下一行S/N为"Same"，则返回"Null"；
• 若当前行与下一行的S/N列均为"Same"，则返回"Null"；
• 若当前行S/N为"Same"、下一行S/N为"New"，则返回当前行的EndTime值。

期望输出 #

新增N_E列的DataFrame：

StartTime           EndTime          Dura Diff  S/N     N_E
05/01/2024 12:27    05/01/2024 13:04 2224 1036  New     05/01/2024 13:04
06/01/2024 07:05    06/01/2024 07:06 60   1081  New     Null
06/01/2024 07:06    06/01/2024 07:06 0    0     Same    06/01/2024 07:06
08/01/2024 10:26    08/01/2024 10:27 32   200   New     08/01/2024 10:27
08/01/2024 11:45    08/01/2024 11:46 38   78    New     08/01/2024 11:46
09/01/2024 13:48    09/01/2024 13:51 172  122   New     09/01/2024 13:51
09/01/2024 16:41    09/01/2024 16:42 60   170   New     Null
09/01/2024 16:43    09/01/2024 17:37 3267 1     Same    Null
09/01/2024 17:38    09/01/2024 17:41 189  1     Same    09/01/2024 17:41
10/01/2024 10:06    10/01/2024 10:08 142  985   New     10/01/2024 10:08
10/01/2024 10:51    10/01/2024 10:53 116  43    New     10/01/2024 10:53
10/01/2024 11:00    10/01/2024 11:00 26   7     New     10/01/2024 11:00
12/01/2024 12:17    12/01/2024 12:30 752  77    New     12/01/2024 12:30
15/01/2024 08:01    15/01/2024 08:01 36   1171  New     15/01/2024 08:01
16/01/2024 15:44    16/01/2024 15:53 577  463   New     16/01/2024 15:53
16/01/2024 16:43    16/01/2024 17:16 2003 50    New     Null
16/01/2024 17:16    16/01/2024 17:17 69   0     Same    16/01/2024 17:17
17/01/2024 00:03    17/01/2024 00:04 60   406   New     Null
17/01/2024 00:04    17/01/2024 00:20 943  0     Same    17/01/2024 00:20
17/01/2024 06:08    17/01/2024 06:09 16   348   New     17/01/2024 06:09
17/01/2024 06:09    17/01/2024 06:10 30   0     Same    Null

解决方案 #

原代码索引越界的核心原因：

1. 当i=0时，i-1=-1，iloc[-1]会取DataFrame最后一行，不符合逻辑；
2. 当i=size-1时，i+1=size，超出DataFrame最大索引，直接报错。

推荐用Pandas向量化操作替代循环，既避免索引问题，又提升效率：

方法1：布尔索引实现 #

# 获取下一行的S/N值，最后一行填充占位符
next_sn = fifo['S/N'].shift(-1).fillna('End')

# 定义三个有效条件
cond1 = (fifo['S/N'] == 'New') & (next_sn == 'New')
cond2 = (fifo['S/N'] == 'Same') & (next_sn == 'New')
cond_last = (fifo.index == fifo.index[-1]) & (fifo['S/N'] == 'New')

# 初始化N_E列为'Null'，满足条件的行赋值为EndTime
fifo['N_E'] = 'Null'
fifo.loc[cond1 | cond2 | cond_last, 'N_E'] = fifo['EndTime']

方法2：apply函数实现 #

next_sn = fifo['S/N'].shift(-1).fillna('End')

fifo['N_E'] = fifo.apply(
    lambda row: row['EndTime'] if (
        (row['S/N'] == 'New' and next_sn[row.name] == 'New') 
        or (row['S/N'] == 'Same' and next_sn[row.name] == 'New')
    ) else 'Null',
    axis=1
)

# 单独处理最后一行
last_idx = fifo.index[-1]
if fifo.loc[last_idx, 'S/N'] == 'New':
    fifo.loc[last_idx, 'N_E'] = fifo.loc[last_idx, 'EndTime']
else:
    fifo.loc[last_idx, 'N_E'] = 'Null'

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