Unique values across columns row-wise in pandas with missing values

user_10852841 0
Viewed 802

I have a dataframe like

import pandas as pd
import numpy as np

df = pd.DataFrame({"Col1": ['A', np.nan, 'B', 'B', 'C'],
                  "Col2": ['A', 'B', 'B', 'A', 'C'],
                  "Col3": ['A', 'B', 'C', 'A', 'C']})

I want to get the unique combinations across columns for each row and create a new column with those values, excluding the missing values.

The code I have right now to do this is

def handle_missing(s):
    
    return np.unique(s[s.notnull()])
    
def unique_across_rows(data):
    
    
    unique_vals = data.apply(handle_missing, axis = 1)
    
    # numpy unique sorts the values automatically
    merged_vals = unique_vals.apply(lambda x: x[0] if len(x) == 1 else '_'.join(x))
    
    return merged_vals

df['Combos'] = unique_across_rows(df)

This returns the expected output:

       Col1    Col2  Col3   Combos
  0       A       A     A       A
  1     NaN       B     B       B
  2       B       B     C       B_C
  3       B       A     A       A_B
  4       C       C     C       C

It seems to me that there should be a more vectorized approach that exists within Pandas to do this: how could I do that?

pandas unique python-3.x
4 Answers

You can try a simple list comprehension which might be more efficient for larger dataframes:

df['combos'] = ['_'.join(sorted(k for k in set(v) if pd.notnull(k))) for v in df.values]

Or you can wrap the above list comprehension in a more readable function:

def combos():
    for v in df.values:
        unique = set(filter(pd.notnull, v))
        yield '_'.join(sorted(unique))

df['combos'] = list(combos())

  Col1 Col2 Col3 combos
0    A    A    A      A
1  NaN    B    B      B
2    B    B    C    B_C
3    B    A    A    A_B
4    C    C    C      C
user_128331660

You can also use agg/apply on axis=1 like below:

df['Combos'] = df.agg(lambda x: '_'.join(sorted(x.dropna().unique())),axis=1)

print(df)

  Col1 Col2 Col3 Combos
0    A    A    A      A
1  NaN    B    B      B
2    B    B    C    B_C
3    B    A    A    A_B
4    C    C    C      C
user_98406370

Try (explanation to follow)

df['Combos'] = (df.stack()              # this removes NaN values
                  .sort_values()        # so we have A_B instead of B_A in 3rd row
                  .groupby(level=0)     # group by original index
                  .agg(lambda x: '_'.join(x.unique()))  # join the unique values
               )

Output:

  Col1 Col2 Col3 Combos
0    A    A    A      A
1  NaN    B    B      B
2    B    B    C    B_C
3    B    A    A    A_B
4    C    C    C      C
user_42384080

fill the nan with a string place-holder '-'. Create a unique array from the col1,col2,col3 list and remove the placeholder. join the unique array values with a '-'

import pandas as pd
import numpy as np

def unique(list1): 
   if '-' in list1:
       list1.remove('-')
   x = np.array(list1) 
   return (np.unique(x))

df = pd.DataFrame({"Col1": ['A', np.nan, 'B', 'B', 'C'],
              "Col2": ['A', 'B', 'B', 'A', 'C'],
              "Col3": ['A', 'B', 'C', 'A', 'C']}).fillna('-')

 s="-"
 for key,row in df.iterrows():
     df.loc[key,'combos']=s.join(unique([row.Col1, row.Col2, row.Col3])) 

 print(df.head())
user_40011770
Related