Here is the simplistic way.
>>> df = pd.DataFrame(data={'Letter': list('AABBBCCCC'),
>>> 'Number': [1,2,1,2,3,1,2,3,4]})
>>> dfx = df.groupby('Letter').agg({'Number':list})
>>> dfx
Number
Letter
A [1, 2]
B [1, 2, 3]
C [1, 2, 3, 4]
>>> dfx = dfx['Number'].apply(pd.Series)
>>> dfx
0 1 2 3
Letter
A 1.0 2.0 NaN NaN
B 1.0 2.0 3.0 NaN
C 1.0 2.0 3.0 4.0
>>> dfx.T.fillna(0).astype(int)
Letter A B C
0 1 1 1
1 2 2 2
2 0 3 3
3 0 0 4
So basically the sequence is:
- aggregate by letter and put all numbers into a single cell by using
df.groupby('Letter').agg({'Number':list})
.
apply(pd.Series)
: turn "column with lists" into 2-dimensional array
T
to transpose, and cleanup with fillna
and type casting.