0
$\begingroup$
  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 = 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)
  1. Is there any other alternative way of doing this without using loops? Any help would be very much appreciated.
$\endgroup$

1 Answer 1

0
$\begingroup$

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.
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.