CODEWITHSUNDEEP
pythonpandas
liamod
liamod

Reputation: 326

Remove duplicates in a row based on column value

Hi couldn't find anything about this specifically, sorry if its a duplicate...

How do I remove column values of a single row that contain the same information (with some exceptions)

Example:

      Name     Age     Job    How_Old    Occupation   Happy   Married?
 0    John     35      Dev    35         Dev          True    True
 1    Sally    42      CA     42         CA           False   False

I would like to drop columns of different names that contain the same information except for ones that contain some obvious duplicates like a binary column.

Output:

     Name     Age    Job   Happy    Married?
0    John     35     Dev   True     True
1    Sally    42     CA    False    False

Thanks, also please note that I need to perform this operation on a massvie flattend and normalised json file, so looping would be quite time expensive.

Upvotes: 1

Views: 78

Answers (2)

Valdi_Bo
Valdi_Bo

Reputation: 30971

Define the following function, returning a list of column names to be deleted:

def chkColToDel(df):
    # Column names excluding bool columns
    cols = df.select_dtypes(exclude=bool).columns.tolist()
    colsToDel = []
    while len(cols) > 1:
        cn1 = cols.pop(0)        # Column name, left side
        if cn1 not in colsToDel: # Not marked for deletion earlier
            c1 = df[cn1]         # The column itself
            t1 = c1.dtype.name   # Type name
            for cn2 in cols:     # Check remaining columns
                c2 = df[cn2]     # Column name, right side
                if t1 == c2.dtype.name and c1.equals(c2):
                    # Same types and equal values
                    colsToDel.append(cn2) # Mark for deletion
    return colsToDel

Then call it:

colsToDel = chkColToDel(df)

And the only remaining thing is to drop the returned columns, if any:

if len(colsToDel) > 0:
    df.drop(columns=colsToDel, inplace=True)

I assume that some exceptions mentioned in your post refer actually to bool columns. If the list of exceptions is broader, change my code accordingly.

Upvotes: 0

jezrael
jezrael

Reputation: 862581

First exlude boolean columns by DataFrame.select_dtypes, transpose and get duplicates by DataFrame.duplicated per all rows, then invert mask by ~ and add removed boolean columns by Series.reindex, last is filtered by DataFrame.loc for all rows by first : and columns names by mask:

m = (~df.select_dtypes(exclude=bool).T.duplicated()).reindex(df.columns, fill_value=True)

Another idea is convert values to tuples and call Series.duplicated:

m = ((~df.select_dtypes(exclude=bool).apply(tuple).duplicated())
         .reindex(df.columns, fill_value=True))

df = df.loc[:, m]
print (df)
    Name  Age  Job  Happy  Married?
0   John   35  Dev   True      True
1  Sally   42   CA  False     False

Details:

#exlude boolean columns
print (df.select_dtypes(exclude=bool))
    Name  Age  Job  How_Old Occupation
0   John   35  Dev       35        Dev
1  Sally   42   CA       42         CA

#transpose
print (df.select_dtypes(exclude=bool).T)
               0      1
Name        John  Sally
Age           35     42
Job          Dev     CA
How_Old       35     42
Occupation   Dev     CA

#checked duplicates per all columns
print (df.select_dtypes(exclude=bool).T.duplicated())
Name          False
Age           False
Job           False
How_Old        True
Occupation     True

#inverse mask True->False, False->True
print ((~df.select_dtypes(exclude=bool).T.duplicated()))
Name           True
Age            True
Job            True
How_Old       False
Occupation    False
dtype: bool

#added removed boolean columns with Trues
print ((~df.select_dtypes(exclude=bool).T.duplicated())
           .reindex(df.columns, fill_value=True))
Name           True
Age            True
Job            True
How_Old       False
Occupation    False
Happy          True
Married?       True
dtype: bool

Upvotes: 3

Related Questions