Pandas how to pivot/unpivot/add a dummy column name

I want to convert from a long to a wide table with dummy column names created based on the number of accid

sample excel input vs output attached

Please help

Answer

I was able to get down to 2 steps, pivot_table using aggfunc=list, and then creating new columns from that list.

I’m not sure I’ve come up with what you want though, because the assignment to columns is just filling up from the left.

Create the DataFrame:

so = pd.DataFrame({'AccID': 'B1 B2 B3 B4 B5 B6 B7'.split(),
                   'UserID': 'A1 A1 A1 A2 A2 A3 A4'.split()}
                 )

  AccID UserID
0    B1     A1
1    B2     A1
2    B3     A1
3    B4     A2
4    B5     A2
5    B6     A3
6    B7     A4

Pivot table:

tmp = pd.pivot_table(data=so, index='UserID', aggfunc=list)

               AccID
UserID              
A1      [B1, B2, B3]
A2          [B4, B5]
A3              [B6]
A4              [B7]

New columns from list:

ans = pd.DataFrame(tmp['AccID'].to_list(), index=tmp.index)

         0     1     2
UserID                
A1      B1    B2    B3
A2      B4    B5  None
A3      B6  None  None
A4      B7  None  None

Changing the column names:

ans.columns = [f"AccID{i + 1}" for i in ans.columns]

       AccID1 AccID2 AccID3
UserID                     
A1         B1     B2     B3
A2         B4     B5   None
A3         B6   None   None
A4         B7   None   None