Select unique values of a column with multiple columns condition

user_16062635 0
Viewed 715

I have a dataframe that has some rows that have missing data, but there are rows that are completed and are the same as those that have missing data. I would like my dataframe to have only the complete ID but not exclude those that do not have any information. For example among these identical IDs which ones contain more information taking into account the TYPE.

The input is:

      ID   TYPE   HEIGHT   KG 
 -----------------------------
    MEXU    DOL     NaN    40
    RFGT    DOL     140    47
    RFGT    DOL     NaN   NaN
    RFGT    RET      90   NaN
    OJKU    NaN     NaN   NaN
    TYED    NaN     NaN    80
    TYED    NaN     100    80
    TYED    DOL     100    80
    PJLO    RET     NaN   NaN
    PJLO    DOL     NaN   NaN
    BUAR    NaN     NaN   NaN

Do I have to use some sort of groupby or agg in pandas?

Expected output:

      ID   TYPE   HEIGHT   KG 
    -----------------------------
    MEXU    DOL     NaN    40
    RFGT    DOL     140    47
    RFGT    RET      90   NaN
    OJKU    NaN     NaN   NaN
    TYED    DOL     100    80
    PJLO    RET     NaN   NaN
    PJLO    DOL     NaN   NaN
    BUAR    NaN     NaN   NaN
pandas-groupby pandas aggregate python
2 Answers

Try drop_duplicates:

df.drop_duplicates(['ID', 'TYPE'])

Output:

      ID TYPE  HEIGHT    KG
0   MEXU  DOL     NaN  40.0
1   RFGT  DOL   140.0  47.0
3   RFGT  RET    90.0   NaN
4   OJKU  NaN     NaN   NaN
5   TYED  NaN     NaN  80.0
7   TYED  DOL   100.0  80.0
8   PJLO  RET     NaN   NaN
9   PJLO  DOL     NaN   NaN
10  BUAR  NaN     NaN   NaN
user_63615310

Use the groupby.first function.

I attempted to replicate the data, but only did so for the first several rows.

import pandas as pd

source = {'ID': ['MEXU ','RFGT','RFGT', 'OJKU', 'TYED'], 'TYPE': ['DOL','DOL','DOL', 'RET', 'NaN'], 'HEIGHT': ['NaN', 140, 'NaN', 90, 'NaN'], 'KG': [40, 47, 'NaN', 'NaN', 'NaN']}

df = pd.DataFrame(data=source)


grouped = df.groupby('ID', as_index=False).first()

print(grouped)

prints


      ID TYPE HEIGHT   KG
0   MEXU  DOL    NaN   40
1   OJKU  RET     90  NaN
2   RFGT  DOL    140   47
3   TYED  NaN    NaN  NaN
user_43523170
Related