Finding unique combinations of columns from a dataframe

Question

In my below data set, I need to find unique sequences and assign them a serial no ..

DataSet :

user    age maritalstatus   product
A   Young   married 111
B   young   married 222
C   young   Single  111
D   old single  222
E   old married 111
F   teen    married 222
G   teen    married 555
H   adult   single  444
I   adult   single  333

Expected output:

young   married     0
young   single      1
old     single      2
old     married     3
teen    married     4
adult   single      5

After finding the unique values like shown above, if I pass a new user like below,

user age maritalstatus  
X     young  married 

it should return me the products as a list .

X : [111, 222]

if there is no sequence, like below

user     age     maritalstatus  
    Y     adult  married

it should return me an empty list

Y : []

Answer 1

First select only columns for output and add drop_duplicates, last add new column by range:

df = df[['age','maritalstatus']].drop_duplicates()
df['no'] = range(len(df.index))
print (df)
     age maritalstatus  no
0  Young       married   0
1  young       married   1
2  young        Single   2
3    old        single   3
4    old       married   4
5   teen       married   5
7  adult        single   6

If want convert all values to lowercase first:

df = df[['age','maritalstatus']].apply(lambda x: x.str.lower()).drop_duplicates()
df['no'] = range(len(df.index))
print (df)
     age maritalstatus  no
0  young       married   0
2  young        single   1
3    old        single   2
4    old       married   3
5   teen       married   4
7  adult        single   5

EDIT:

First convert to lowercase:

df[['age','maritalstatus']] = df[['age','maritalstatus']].apply(lambda x: x.str.lower())
print (df)
  user    age maritalstatus  product
0    A  young       married      111
1    B  young       married      222
2    C  young        single      111
3    D    old        single      222
4    E    old       married      111
5    F   teen       married      222
6    G   teen       married      555
7    H  adult        single      444
8    I  adult        single      333

And then use merge for unique product converted to list:

df2 = pd.DataFrame([{'user':'X', 'age':'young', 'maritalstatus':'married'}])
print (df2)
     age maritalstatus user
0  young       married    X

a = pd.merge(df, df2, on=['age','maritalstatus'])['product'].unique().tolist()
print (a)
[111, 222]

---

df2 = pd.DataFrame([{'user':'X', 'age':'adult', 'maritalstatus':'married'}])
print (df2)
     age maritalstatus user
0  adult       married    X

a = pd.merge(df, df2, on=['age','maritalstatus'])['product'].unique().tolist()
print (a)
[]

But if need column use transform:

df['prod'] = df.groupby(['age', 'maritalstatus'])['product'].transform('unique')
print (df)
  user    age maritalstatus  product        prod
0    A  young       married      111  [111, 222]
1    B  young       married      222  [111, 222]
2    C  young        single      111       [111]
3    D    old        single      222       [222]
4    E    old       married      111       [111]
5    F   teen       married      222  [222, 555]
6    G   teen       married      555  [222, 555]
7    H  adult        single      444  [444, 333]
8    I  adult        single      333  [444, 333]

EDIT1:

a = (pd.merge(df, df2, on=['age','maritalstatus'])
       .groupby('user_y')['product']
       .apply(lambda x: x.unique().tolist())
       .to_dict())
print (a)
{'X': [111, 222]}

Detail:

print (pd.merge(df, df2, on=['age','maritalstatus']))
  user_x    age maritalstatus  product user_y
0      A  young       married      111      X
1      B  young       married      222      X

Answer 2

One way is pd.factorize. Note I convert columns to lower case first for results to make sense.

for col in ['user', 'age', 'maritalstatus']:
    df[col] = df[col].str.lower()

df['category'] = list(zip(df.age, df.maritalstatus))
df['category'] = pd.factorize(df['category'])[0]

#   user    age maritalstatus  product  category
# 0    a  young       married      111         0
# 1    b  young       married      222         0
# 2    c  young        single      111         1
# 3    d    old        single      222         2
# 4    e    old       married      111         3
# 5    f   teen       married      222         4
# 6    g   teen       married      555         4
# 7    h  adult        single      444         5
# 8    i  adult        single      333         5

Finally, drop duplicates:

df_cats = df[['age', 'maritalstatus', 'category']].drop_duplicates()

#      age maritalstatus  category
# 0  young       married         0
# 2  young        single         1
# 3    old        single         2
# 4    old       married         3
# 5   teen       married         4
# 7  adult        single         5

To map a list of products, try this:

s = df.groupby(['age', 'maritalstatus'])['product'].apply(list)
df['prod_catwise'] = list(map(s.get, zip(df.age, df.maritalstatus)))

Another option is to use categorical data, which I highly recommend for workflows. You can easily extract codes from a categorical series via pd.Series.cat.codes.