Skip to main content
 首页 » 编程设计

Python 相当于 excel 嵌套 if 用于过滤 Pandas DataFrame 行的条件

2024年11月24日12softidea

使用 python 选择特定的 excel 行。所以在 excel 中我会做

If(And(Or(A<>({"Closed",""}),Or(B<>({"Closed",""}))) 

用于获取数据框中既非 Closed 也非空白的列。尝试使用

df = df[(~df.A.isin([Closed","No Data"])) &(~df.B.isin([Closed","No Data"]))] 

问题是 python 正在删除列,例如:

A                        B 
Approved       Closed 
No Data          Restrict 
Restrict           No Data 

我不想要 正如其中一个链接中所建议的那样,也尝试过

df.loc[(df[A] != "Closed") & (df[B] != "Closed") & (df[A] != "No data") & (df[B] != "No data") 

得到与我尝试 .isin 时相同的结果

请您参考如下方法:

我将使用此示例数据:

           A         B  ~df.A.isin  ~df.B.isin  ~A & ~B  ~A | ~B 
0     Closed    Closed       False       False    False    False 
1     Closed   No Data       False       False    False    False 
2   Approved    Closed        True       False    False     True 
3    No Data   No Data       False       False    False    False 
4     Closed  Approved       False        True    False     True 
5    No Data  Restrict       False        True    False     True 
6   Approved   No Data        True       False    False     True 
7     Closed  Restrict       False        True    False     True 
8   Approved  Approved        True        True     True     True 
9    No Data  Approved       False        True    False     True 
10  Restrict   No Data        True       False    False     True 
11  Restrict  Approved        True        True     True     True 

~df.A.isin列显示 ~df.A.isin(["Closed","No Data"]) 的值,即 True对于 A 既不包含 Closed 的行也不No Data

~df.B.isin列显示 ~df.B.isin(["Closed","No Data"]) 的值,即 True对于 B 既不包含 Closed 的行也不No Data

~A & ~B列显示 (~df.A.isin(["Closed","No Data"])) &(~df.B.isin(["Closed","No Data"])) 的值

~A | ~B列显示 (~df.A.isin(["Closed","No Data"])) |(~df.B.isin(["Closed","No Data"])) 的值

您第一次尝试缺少 "在Closed"的开头.添加它我们有

df[(~df.A.isin(["Closed","No Data"])) &(~df.B.isin(["Closed","No Data"]))] 

这给了我们:

           A         B  ~df.A.isin  ~df.B.isin  ~A & ~B  ~A | ~B 
8   Approved  Approved        True        True     True     True 
11  Restrict  Approved        True        True     True     True 

结果只显示那些完全没有 Closed 的行没有 No Data .

文本评论中的建议:

df[(~df.A.isin(["Closed","No Data"])) |(~df.B.isin(["Closed","No Data"]))] 

给我们:

           A         B  ~df.A.isin  ~df.B.isin  ~A & ~B  ~A | ~B 
2   Approved    Closed        True       False    False     True 
4     Closed  Approved       False        True    False     True 
5    No Data  Restrict       False        True    False     True 
6   Approved   No Data        True       False    False     True 
7     Closed  Restrict       False        True    False     True 
8   Approved  Approved        True        True     True     True 
9    No Data  Approved       False        True    False     True 
10  Restrict   No Data        True       False    False     True 
11  Restrict  Approved        True        True     True     True 

这里我们有| ( or ) 而不是 & ( and ),因此行可以包含 Closed或 No Data ,但不在 A 和 B 中。这意味着具有以下行:

       A         B 
Approved    Closed 
 No Data  Restrict 
Restrict   No Data 

将包括在内,但不具有以下内容的行:

     A         B 
Closed    Closed 
Closed   No Data 

你的第二次尝试:

df.loc[(df[A] != "Closed") & (df[B] != "Closed") & 
       (df[A] != "No data") & (df[B] != "No data") 

需要在列标签周围加上引号。您可以使用 df.A或 df['A'] , 但不是 df[A]

此外,您拼写了 data在 No data小写 d , 而在其他地方你用大写 D - No Data .在 python 中,这是不一样的。如果我们解决这个问题:

df.loc[(df['A'] != "Closed") & (df['B'] != "Closed") & 
       (df['A'] != "No Data") & (df['B'] != "No Data")] 

这给了我们与第一次尝试相同的结果:

           A         B  ~df.A.isin  ~df.B.isin  ~A & ~B  ~A | ~B 
8   Approved  Approved  True  True  True  True  True  True  True 
11  Restrict  Approved  True  True  True  True  True  True  True 

如果稍微重新排列此表达式,请使用括号和 | ( or ):

df.loc[((df['A'] != "Closed") & (df['A'] != "No Data")) |  
       ((df['B'] != "Closed") & (df['B'] != "No Data"))] 

我们得到:

           A         B  ~df.A.isin  ~df.B.isin  ~A & ~B  ~A | ~B 
2   Approved    Closed        True       False    False     True 
4     Closed  Approved       False        True    False     True 
5    No Data  Restrict       False        True    False     True 
6   Approved   No Data        True       False    False     True 
7     Closed  Restrict       False        True    False     True 
8   Approved  Approved        True        True     True     True 
9    No Data  Approved       False        True    False     True 
10  Restrict   No Data        True       False    False     True 
11  Restrict  Approved        True        True     True     True