python之在 Pandas 中用 NaN 替换某些字符串

我有 pandas DF，我需要在其中迭代两列(位置和事件)的值，并将字符串“Gate-3”“NO Access”替换为 NaN。

下面是DF的例子。

Time        Location    Event               Badge ID 
18:28:59    Gate-2      Access Granted      81002 
18:28:12    Gate-1      Access Granted      80557 
18:27:55    Gate-3      Access Granted      80557 
18:27:44    Gate-3      NO Access           80398 
18:25:38    Gate-1      NO Access           80978 
18:25:30    Gate-2      Access Granted      73680 
18:23:56    Gate-1      Access Granted      73680 
18:23:52    Gate-2      Access Granted      80557 
18:23:19    Gate-2      NO Access           128 
18:23:16    Gate-1      Access Granted      80557 

预期的输出是

       Time Location           Event  Badge ID 
0  18:28:59   Gate-2  Access Granted     81002 
1  18:28:12   Gate-1  Access Granted     80557 
2  18:27:55      NaN  Access Granted     80557 
3  18:27:44      NaN             NaN     80398 
4  18:25:38   Gate-1             NaN     80978 
5  18:25:30   Gate-2  Access Granted     73680 
6  18:23:56   Gate-1  Access Granted     73680 
7  18:23:52   Gate-2  Access Granted     80557 
8  18:23:19   Gate-2             NaN       128 
9  18:23:16   Gate-1  Access Granted     80557 

请您参考如下方法:

您可以在加载 XLS 文件时通过指定 na_values 参数进行设置。

df = pd.read_excel('file.xls', na_values=['Gate-3', 'NO Access']) 
print(df) 
 
       Time Location           Event  Badge ID 
0  18:28:59   Gate-2  Access Granted     81002 
1  18:28:12   Gate-1  Access Granted     80557 
2  18:27:55      NaN  Access Granted     80557 
3  18:27:44      NaN             NaN     80398 
4  18:25:38   Gate-1             NaN     80978 
5  18:25:30   Gate-2  Access Granted     73680 
6  18:23:56   Gate-1  Access Granted     73680 
7  18:23:52   Gate-2  Access Granted     80557 
8  18:23:19   Gate-2             NaN       128 
9  18:23:16   Gate-1  Access Granted     80557 

在我看来，这比在加载数据后清理数据要好。