Replace all value in a pandas subset by values from a list

user_11228737 0
Viewed 160

I have a dataset composed of 3 differents groups A, B, C :

df = pd.DataFrame([[1, "A", "A"], [2, "B", "B"], [3, "A", "A"], [4, "B", "B"], [5, "C", "C"], [6, "C", "C"],
               [7, "A", "A"], [8, "B", "B"], [9, "C", "C"]],
              columns=["Index", "Group", "Cluster"])

For each group I determine some cluster and I would like to replace the cluster column by the value of the cluster. I have one list for each cluster

listA = [0, 1, 1]
listB = [1, 0, 1]
listC = [0, 0, 1]

I would like to have something like that at the end :

Index Group Cluster
1 A 0
2 B 1
3 A 1
4 B 0
5 C 0
6 C 0
7 A 1
8 B 1
9 C 1
pandas subset list python replace
4 Answers

You can groupby.cumcount and then merge:

d = {"A":listA,"B":listB,"C":listC}
u = pd.Series(d).explode().to_frame("Cluster")

v = df.assign(k=df.groupby('Group').cumcount()).merge(
           u.assign(k=u.groupby(level=0).cumcount()).reset_index() ,
left_on=['Group','k'],right_on=['index','k'],suffixes=('','_y'))

v['Cluster'] = v['Cluster_y']
out = v.reindex(columns=df.columns)

print(out)

   Index Group Cluster
0      1     A       0
1      2     B       1
2      3     A       1
3      4     B       0
4      5     C       0
5      6     C       0
6      7     A       1
7      8     B       1
8      9     C       1
user_98406370

Try with for loop

for x, y in zip(list('ABC'), [listA,listB,listC]):
     df.loc[df.Group == x, 'Cluster'] = y
df
Out[395]: 
   Index Group Cluster
0      1     A       0
1      2     B       1
2      3     A       1
3      4     B       0
4      5     C       0
5      6     C       0
6      7     A       1
7      8     B       1
8      9     C       1
user_79645270

This may be kind of naïve, but assuming the size of the groups matches the size of the cluster labels, you could sort, assign, and resort:

df.sort_values(['Group', 'Index'], inplace=True)
df['Cluster'] = listA + listB + listC
df.sort_values('Index', inplace=True)

Resulting df:

   Index Group  Cluster
0      1     A        0
1      2     B        1
2      3     A        1
3      4     B        0
4      5     C        0
5      6     C        0
6      7     A        1
7      8     B        1
8      9     C        1
user_133869790

You could also do groupby and a transform, using a dict to map the labels to the clusters (as done by anky in another answer):

d = {"A":listA, "B":listB, "C":listC}
df['Cluster'] = df.groupby('Group')['Cluster'].transform(lambda x : d[x.name])

Result:

   Index Group  Cluster
0      1     A        0
1      2     B        1
2      3     A        1
3      4     B        0
4      5     C        0
5      6     C        0
6      7     A        1
7      8     B        1
8      9     C        1
user_133869790
Related