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.

Are the column names always the same a teh values? Do you have values for which you have no columns? (Also really seems like dummies would be a bit simpler) — ALollz, Feb 23 '21 at 16:45
The column names are not always the same as the values. I should have included an example column with that. I have updated my question to show that. Thank you — bLund, Feb 23 '21 at 16:48

Quang Hoang · Accepted Answer · 2021-02-23T21:02:51.070

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

edited Feb 23 '21 at 21:02

answered Feb 23 '21 at 16:49

Quang Hoang

117,517
10
34
52

I am accepting this answer as it answered my question as originally written. Thank you. Do you know how to deal with a column name that is not the same as the row values and row values that do not have a corresponding column name? I have updated column E to column Z. – bLund Feb 23 '21 at 20:17
I’m not really sure what you mean. Do you want to put all E values under the Z column? – Quang Hoang Feb 23 '21 at 20:19
Correct, I made the change in my original question to show that. – bLund Feb 23 '21 at 20:54
@bLund see if the updated answer helps. – Quang Hoang Feb 23 '21 at 21:03
Oh yeah. Thanks so much. I had that up in my head somewhere, but forgot it was there. Thanks for your help. – bLund Feb 23 '21 at 21:12

Pandas rearrange data into columns of same values

1 Answers1