Pandas rearrange data into columns of same values

Question

I have a dataframe with unorganized data.

df = pd.DataFrame({'id':['1', '2', '3', '4'],
               'name':['Jon', 'Jack', 'Jane', 'Jill'],
               'A':['C', 'A', 'E', 'B'],
               'B':['', 'C', 'D', 'E',],
               'C':['E', 'E', '', 'A'],
               'D':['', '', 'B', 'D'],
               'Z':['', '', 'A', 'C']})

id  name    A   B   C   D   Z
1   Jon     C       E       
2   Jack    A   C   E       
3   Jane    E   D       B   A
4   Jill    B   E   A   D   C

I want to organize the equal row values under the same columns:

id  name    A   B   C   D   Z
1   Jon             C       E
2   Jack    A       C       E
3   Jane    A   B       D   E
4   Jill    A   B   C   D   E

Thank you for your help.

Answer 1

Let's try broadcasting:

cols = np.array(['A','B','C','D','Z'])
vals = np.array(['A','B','C','D','E'])

a = (df[cols].values[:,None,:] == vals[None,:,None])

# any(-1) checks if any `cols` is hit
df[cols] = np.where(a.any(-1), vals, '')

Output:

  id  name  A  B  C  D  Z
0  1   Jon        C     E
1  2  Jack  A     C     E
2  3  Jane  A  B     D  E
3  4  Jill  A  B  C  D  E