python - 从数据框中查找列的唯一组合

Question

在我下面的数据集中，我需要找到唯一的序列并为它们分配一个序列号..

数据集:

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

预期输出:

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

找到如上所示的唯一值后，如果我传递如下所示的新用户，

user age maritalstatus  
X     young  married 

它应该将产品作为列表返回给我。

X : [111, 222]

如果没有序列，如下所示

user     age     maritalstatus  
    Y     adult  married

它应该给我一个空列表

Y : []

Answer 1

首先只选择输出列并添加 drop_duplicates , 最后通过 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

如果要先将所有值转换为小写:

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

编辑:

先转换成 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

然后使用 merge 独特的 product转换为 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)
[]

但如果需要列使用 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]

编辑1:

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]}

详情 :

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