python等效于Excel嵌套如果用于过滤Pandas DataFrame行的条件

Question

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

If(And(Or(A<>({"Closed",""}),Or(B<>({"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

Answer 1

我将使用此示例数据：

           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"])的值， CC_2的值是 True的行

~df.B.isin列显示了 ~df.B.isin(["Closed","No Data"])的值， CC_7的值是b的 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的行。

Wen-Ben的评论中的建议：

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中都不包含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]

另外，您在No data中使用小写d拼写data，而在其他地方则使用大写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