thescoop thescoop - 1 year ago 93
Python Question

Using Groups, drop duplicated NaNs only

With my data grouped by Assay, Image and Roi, for each group I wish to delete all but the first rows which have a NaN value in the 'Intensity' column.

My attempt is able to delete duplicates, but this isn't specific to NaN values.

from pandas import Series, DataFrame
import pandas as pd
import numpy as np

df = DataFrame({'assay':['cnt']*11,
df.loc[df.intensity < 10, ['intensity','xy']] = np.nan

enter image description here

df.groupby(['assay','image','roi']).apply(lambda x: x.drop_duplicates(['intensity'], keep='first'))

enter image description here

Answer Source

Instead of using drop_duplicates you can pass the duplicated & is.null checker into the lambda function as:

df.groupby(['assay','image','roi']).apply(lambda x: x.loc[~(x.duplicated(['intensity']) & x.intensity.isnull())])
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download