Drop Duplicates in a DataFrame Keeping the Row with the Least Nulls
pandas, python
Solution
If you don't have duplicated index, you can do:
df.loc[df.notnull().sum(1).groupby(df.A).idxmax()]
# A B C D
#b AA 1.0 4.0 NaN
#d BB 2.0 5.0 3.0
#e CC 3.0 6.0 4.0
Problem
With this DataFrame: ``` d = {'A' : pd.Series(['AA', 'AA', 'AA', 'BB','CC'], index=['a', 'b', 'c', 'd','e']), 'B' : pd.Series([1., 2., 3.], index=['b', 'd','e']), 'C' : pd.Series([4., 5., 6.], index=['b', 'd', '']), 'D' : pd.Series([1., 2., 3.,4.], index=['a', 'c', 'd','e'])} In[1]: pd.DataFrame(d) Out[1]: A B C D a AA NaN NaN 1.0 b AA 1.0 4.0 NaN c AA NaN NaN 2.0 d BB 2.0 5.0 3.0 e CC 3.0 6.0 4.0 ``` I would like to drop duplicates on `df['A']` and keep the row with the fewest null values in the columns that are not being dropped `on`. ``` In[2]: pd.DataFrame(d).drop_duplicates(on='A', **magical_answer=True**) Out[1]: A B C D b AA 1.0 4.0 NaN d BB 2.0 5.0 3.0 e CC 3.0 6.0 4.0 ``` I can see a possible issue not enumerated in this example would occur if there are multiple rows with the fewest nulls, in that case it would be useful to have the `keep : {‘first’, ‘last’}` arg.