Skip to main content
added 131 characters in body
Source Link
mozway
  • 267k
  • 13
  • 56
  • 106
m = df['y'].isna()
out = df[mdf[
    (m.groupby(df['x']).transform('all')|~m] & ~df.loc[m, 'x'].duplicated()) | ~m
]
   x    y
0  A  NaN
2  B  1.0
3  C  2.0
4  C  3.0
5  C  4.0
6  D  NaN
7  D  NaN
   x    y      m duplicated  groupby.transform('all')     ~m      |final
0  A  NaN   True      False                      True  False   True
1  B  NaN   True      False                     False  False  False
2  B  1.0  False        NaN                     False   True   True
3  C  2.0  False        NaN                     False   True   True
4  C  3.0  False        NaN                     False   True   True
5  C  4.0  False        NaN                     False   True   True
6  D  NaN   True      False                      True  False   True
7  D  NaN   True       True               True  False     True  False  False
# which values are not-null?
m1 = df['y'].notna()
# which groups only contain null values?
m2 = ~df['x'].isin(df.loc[m1, 'x'])
# which nulls are not duplicated?
m3 = ~df[['x', 'y']].duplicated()
# keep rows that match either condition above
out = df[m1 | (m2 & m3)]
m = df['y'].isna()
out = df[m.groupby(df['x']).transform('all')|~m]
   x    y
0  A  NaN
2  B  1.0
3  C  2.0
4  C  3.0
5  C  4.0
6  D  NaN
7  D  NaN
   x    y      m  groupby.transform('all')     ~m      |
0  A  NaN   True                      True  False   True
1  B  NaN   True                     False  False  False
2  B  1.0  False                     False   True   True
3  C  2.0  False                     False   True   True
4  C  3.0  False                     False   True   True
5  C  4.0  False                     False   True   True
6  D  NaN   True                      True  False   True
7  D  NaN   True                      True  False   True
# which values are not-null?
m1 = df['y'].notna()
# which groups only contain null values?
m2 = ~df['x'].isin(df.loc[m1, 'x'])
# which nulls are duplicated?
m3 = ~df[['x', 'y']].duplicated()
# keep rows that match either condition above
out = df[m1 | (m2 & m3)]
m = df['y'].isna()
out = df[
    (m.groupby(df['x']).transform('all') & ~df.loc[m, 'x'].duplicated()) | ~m
]
   x    y
0  A  NaN
2  B  1.0
3  C  2.0
4  C  3.0
5  C  4.0
6  D  NaN
   x    y      m duplicated  groupby.transform('all')     ~m  final
0  A  NaN   True      False                      True  False   True
1  B  NaN   True      False                     False  False  False
2  B  1.0  False        NaN                     False   True   True
3  C  2.0  False        NaN                     False   True   True
4  C  3.0  False        NaN                     False   True   True
5  C  4.0  False        NaN                     False   True   True
6  D  NaN   True      False                      True  False   True
7  D  NaN   True       True                      True  False  False
# which values are not-null?
m1 = df['y'].notna()
# which groups only contain null values?
m2 = ~df['x'].isin(df.loc[m1, 'x'])
# which nulls are not duplicated?
m3 = ~df[['x', 'y']].duplicated()
# keep rows that match either condition above
out = df[m1 | (m2 & m3)]
added 239 characters in body
Source Link
mozway
  • 267k
  • 13
  • 56
  • 106
# which values are not-null?
m1 = df['y'].notna()
# which groups only contain null values?
m2 = ~df['x'].isin(df.loc[m1, 'x'])
# which nulls are duplicated?
m3 = ~df[['x', 'y']].duplicated()
# keep rows that match either condition above
out = df[m1 | m2](m2 & m3)]
out = df[
    (m :=df['y']= df['y'].notna())
    | (~df['x'].isin(df.loc[m, 'x']) & ~df[['x', 'y']].duplicated())
]
   x    y
0  A  NaN
2  B  1.0
3  C  2.0
4  C  3.0
5  C  4.0
6  D  NaN
7  D  NaN
   x    y     m1     m2     m3  m1 | (m2 & m3)
0  A  NaN  False   True   True            True
1  B  NaN  False  False   True           False
2  B  1.0   True  False   True            True
3  C  2.0   True  False   True            True
4  C  3.0   True  False   True            True
5  C  4.0   True  False   True            True
6  D  NaN  False   True   True            True
7  D  NaN  False   True  False   True        False
# which values are not-null?
m1 = df['y'].notna()
# which groups only contain null values?
m2 = ~df['x'].isin(df.loc[m1, 'x'])
# keep rows that match either condition above
out = df[m1 | m2]
out = df[(m:=df['y'].notna()) | ~df['x'].isin(df.loc[m, 'x'])]
   x    y
0  A  NaN
2  B  1.0
3  C  2.0
4  C  3.0
5  C  4.0
6  D  NaN
7  D  NaN
   x    y     m1     m2  m1 | m2
0  A  NaN  False   True     True
1  B  NaN  False  False    False
2  B  1.0   True  False     True
3  C  2.0   True  False     True
4  C  3.0   True  False     True
5  C  4.0   True  False     True
6  D  NaN  False   True     True
7  D  NaN  False   True     True
# which values are not-null?
m1 = df['y'].notna()
# which groups only contain null values?
m2 = ~df['x'].isin(df.loc[m1, 'x'])
# which nulls are duplicated?
m3 = ~df[['x', 'y']].duplicated()
# keep rows that match either condition above
out = df[m1 | (m2 & m3)]
out = df[
    (m := df['y'].notna())
    | (~df['x'].isin(df.loc[m, 'x']) & ~df[['x', 'y']].duplicated())
]
   x    y
0  A  NaN
2  B  1.0
3  C  2.0
4  C  3.0
5  C  4.0
6  D  NaN
   x    y     m1     m2     m3  m1 | (m2 & m3)
0  A  NaN  False   True   True            True
1  B  NaN  False  False   True           False
2  B  1.0   True  False   True            True
3  C  2.0   True  False   True            True
4  C  3.0   True  False   True            True
5  C  4.0   True  False   True            True
6  D  NaN  False   True   True            True
7  D  NaN  False   True  False           False
added 734 characters in body
Source Link
mozway
  • 267k
  • 13
  • 56
  • 106

Or a better approach, without groupby. Identify the null rows, select the X for which there is at least one non-null values and select the others. You can do this only with boolean masks and an OR (|):

# which values are not-null?
m1 = df['y'].notna()
# which groups only contain null values?
m2 = ~df['x'].isin(df.loc[m1, 'x'])
# keep rows that match either condition above
out = df[m1 | m2]

As a one-liner:

out = df[(m:=df['y'].notna()) | ~df['x'].isin(df.loc[m, 'x'])]

Output:

   x    y
0  A  NaN
2  B  1.0
3  C  2.0
4  C  3.0
5  C  4.0
6  D  NaN
7  D  NaN

Intermediates:

   x    y     m1     m2  m1 | m2
0  A  NaN  False   True     True
1  B  NaN  False  False    False
2  B  1.0   True  False     True
3  C  2.0   True  False     True
4  C  3.0   True  False     True
5  C  4.0   True  False     True
6  D  NaN  False   True     True
7  D  NaN  False   True     True

Or a better approach, without groupby. Identify the null rows, select the X for which there is at least one non-null values and select the others. You can do this only with boolean masks and an OR (|):

# which values are not-null?
m1 = df['y'].notna()
# which groups only contain null values?
m2 = ~df['x'].isin(df.loc[m1, 'x'])
# keep rows that match either condition above
out = df[m1 | m2]

As a one-liner:

out = df[(m:=df['y'].notna()) | ~df['x'].isin(df.loc[m, 'x'])]

Output:

   x    y
0  A  NaN
2  B  1.0
3  C  2.0
4  C  3.0
5  C  4.0
6  D  NaN
7  D  NaN

Intermediates:

   x    y     m1     m2  m1 | m2
0  A  NaN  False   True     True
1  B  NaN  False  False    False
2  B  1.0   True  False     True
3  C  2.0   True  False     True
4  C  3.0   True  False     True
5  C  4.0   True  False     True
6  D  NaN  False   True     True
7  D  NaN  False   True     True
Source Link
mozway
  • 267k
  • 13
  • 56
  • 106
Loading