Reshaping Pandas DataFrame

Question

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

2. 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)

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

Answer 1

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.