# Reshaping Pandas DataFrame

1. 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

1. I have wrote the following code which works just fine.
import pandas as pd

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)

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

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.