Goal
If sub-column min
equals to sub-column max
and if min
and max
sub-column do not equal to each other in any of the column (ao, his, cyp1a2s, cyp3a4s in this case), drop the row.
Example
arrays = [np.array(['ao', 'ao', 'hia', 'hia', 'cyp1a2s', 'cyp1a2s', 'cyp3a4s', 'cyp3a4s']),
np.array(['min', 'max', 'min', 'max', 'min', 'max', 'min', 'max'])]
tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples, names=['',''])
df = pd.DataFrame(np.array([[1, 1, 0, 0, float('nan'), float('nan'), 0, 0],
[1, 1, 0, 0, float('nan'), 1, 0, 0],
[0, 2, 0, 0, float('nan'), float('nan'), 1, 1],]), index=['1', '2', '3'], columns=index)
df
ao hia cyp1a2s cyp3a4s
min max min max min max min max
1 1.0 1.0 0.0 0.0 NaN NaN 0.0 0.0
2 1.0 1.0 0.0 0.0 NaN 1.0 0.0 0.0
3 0.0 2.0 0.0 0.0 NaN NaN 1.0 1.0
Want
df = pd.DataFrame(np.array([[1, 1, 0, 0, float('nan'), float('nan'), 0, 0]]), index=['1'], columns=index)
df
ao hia cyp1a2s cyp3a4s
min max min max min max min max
1 1.0 1.0 0.0 0.0 NaN NaN 0.0 0.0
Attempt
df.apply(lambda x: x['min'].map(str) == x['max'].map(str), axis=1)
KeyError: ('min', 'occurred at index 1')
Note
The actual dataframe has 50+ columns.
Use DataFrame.xs
for DataFrame
by second levels of MultiIndex
, replace NaN
s:
df1 = df.xs('min', axis=1, level=1).fillna('nan')
df2 = df.xs('max', axis=1, level=1).fillna('nan')
Or convert data to strings:
df1 = df.xs('min', axis=1, level=1).astype('str')
df2 = df.xs('max', axis=1, level=1).astype('str')
Compare Dataframes by DataFrame.eq
and test if all True
s by DataFrame.all
and last filter by boolean indexing
:
df = df[df1.eq(df2).all(axis=1)]
print (df)
ao hia cyp1a2s cyp3a4s
min max min max min max min max
1 1.0 1.0 0.0 0.0 NaN NaN 0.0 0.0