CODEWITHSUNDEEP
pythonpandasunique
I.M.
I.M.

Reputation: 344

How to get unique values of a dataframe column when there are lists - python

I have the following dataframe where I would like to print the unique values of the color column.

df = pd.DataFrame({'colors': ['green', 'green', 'purple', ['yellow , red'], 'orange'], 'names': ['Terry', 'Nor', 'Franck', 'Pete', 'Agnes']})

Output:
           colors   names
0           green   Terry
1           green     Nor
2          purple  Franck
3  [yellow , red]    Pete
4          orange   Agnes

df.colors.unique() would work fine if there wasn't the [yellow , red] row. As it is I keep getting the TypeError: unhashable type: 'list' error which is understandable.

Is there a way to still get the unique values without taking this row into account?

I tried the followings but none worked:

df = df[~df.colors.str.contains(',', na=False)] # Nothing happens
df = df[~df.colors.str.contains('[', na=False)] # Output: error: unterminated character set at position 0
df = df[~df.colors.str.contains(']', na=False)] # Nothing happens

Upvotes: 5

Views: 4588

Answers (4)

Mahendra Singh
Mahendra Singh

Reputation: 528

Changes Input Sample

The input specified had a string which was a list(as specified by the poster), hence converted into a list of strings. 

# Required Import
from ast import literal_eval

df = pd.DataFrame({
    'colors': ['green', 'green', 'purple', "['yellow' , 'red']", 'orange'], 
    'names': ['Terry', 'Nor', 'Franck', 'Pete', 'Agnes']
})

Perform literal_eval. For more info check-out literal_eval

Literal eval in order to covert string to actual list only where there is a list as string

list_records = df.colors.str.contains('[', na=False, regex=False)
df.loc[list_records, 'colors'] = df.loc[list_records, 'colors'].apply(literal_eval)

Unique Colors

Works with pandas >= 0.25

df.explode('colors')['colors'].unique()

Gives

['green', 'purple', 'yellow', 'red', 'orange']

Upvotes: 1

Yaakov Bressler
Yaakov Bressler

Reputation: 12018

Assuming each of the values in your dataframe are important, here's a technique I frequently use to "unpack lists":

import re

def unlock_list_from_string(string, delim=','):
    """
    lists are stored as strings (in csv files) ex. '[1,2,3]'
    this function unlocks that list
    """
    if type(string)!=str:
        return string

    # remove brackets
    clean_string = re.sub('\[|\]', '', string)
    unlocked_string = clean_string.split(delim)
    unlocked_list = [x.strip() for x in unlocked_string]
    return unlocked_list

all_colors_nested = df['colors'].apply(unlock_list_from_string)
# unnest
all_colors = [x for y in all_colors_nested for x in y ]

print(all_colors)
# ['green', 'green', 'purple', 'yellow', 'red', 'orange']

Upvotes: 1

jezrael
jezrael

Reputation: 862661

If values are lists check it by isinstance method:

#changed sample data
df = pd.DataFrame({'colors': ['green', 'green', 'purple', ['yellow' , 'red'], 'orange'], 
                   'names': ['Terry', 'Nor', 'Franck', 'Pete', 'Agnes']})

df = df[~df.colors.map(lambda x : isinstance(x, list))]
print (df)
   colors   names
0   green   Terry
1   green     Nor
2  purple  Franck
4  orange   Agnes

Your solution should be changed with casting to strings and regex=False parameter:

df = df[~df.colors.astype(str).str.contains('[', na=False, regex=False)] 
print (df)
   colors   names
0   green   Terry
1   green     Nor
2  purple  Franck
4  orange   Agnes

Also if want all unique values included lists for pandas 0.25+:

s = df.colors.map(lambda x : x if isinstance(x, list) else [x]).explode().unique().tolist()
print (s)
['green', 'purple', 'yellow', 'red', 'orange']

Upvotes: 3

BENY
BENY

Reputation: 323236

Let us using type

df.colors.apply(lambda x : type(x)!=list)
0     True
1     True
2     True
3    False
4     True
Name: colors, dtype: bool

Upvotes: 2

Related Questions