Reshaping Pandas DataFrame I am trying to do the following as shown below.


    Input       Output
Letter Number    A B C
A      1         1 1 1
A      2         2 2 2
B      1           3 3
B      2             4
B      3
C      1
C      2
C      3
C      4



I have wrote the following code which works just fine.


import pandas as pd

df = pd.read_excel('Test.xlsx')
df = df.pivot(columns='Letter', values='Number')

list = []
for col in df.columns:
    col = df[col].sort_values()
    col.index = range(len(col))
    list.append(col)
ndf = pd.concat(list, axis=1, sort=False)
ndf = ndf.dropna(axis=0, how='all')
print(ndf)



Is there any other alternative way of doing this without using loops? Any help would be very much appreciated.
Here is the simplistic way.

&gt;&gt;&gt; df = pd.DataFrame(data={'Letter': list('AABBBCCCC'),
&gt;&gt;&gt;                        'Number': [1,2,1,2,3,1,2,3,4]})


&gt;&gt;&gt; dfx = df.groupby('Letter').agg({'Number':list})
&gt;&gt;&gt; dfx


              Number
Letter              
A             [1, 2]
B          [1, 2, 3]
C       [1, 2, 3, 4]


&gt;&gt;&gt; dfx = dfx['Number'].apply(pd.Series)
&gt;&gt;&gt; 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

&gt;&gt;&gt; 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.