How to transpose a column based on the condition in another column?

Question

I am sure this has been asked before (since it is a common question), but I am unable to find it.

So my dataframe looks like this:

ID     Name
1      A
1      B
2      X
2      Y
2      Z

I want it in this format (I don't care about the column names)

1    A    B
2    X    Y   Z  and so on...

https://stackoverflow.com/questions/6473679/transpose-list-of-lists — Mojtaba Kamyabi, Mar 10 '19 at 06:26
oh wow! Thank you @jezrael, I never knew this. I am still learning Pandas. Kudos! — Rishab Gupta, Mar 10 '19 at 06:51

score 3 · Answer 1 · answered Mar 10 '19 at 06:26

3

you can do something like:

df_new=df.groupby('ID')['Name'].apply(lambda x: ','.join(list(x))).reset_index()
df_new.join(df_new.pop('Name').str.split(",",expand=True))

   ID  0  1     2
0   1  A  B  None
1   2  X  Y     Z

answered Mar 10 '19 at 06:26

anky

74,114
11
41
70

jezrael · Accepted Answer · 2019-03-10T06:46:39.230

Create MultiIndex by DataFrame.set_index with counter by GroupBy.cumcount and reshape by Series.unstack with DataFrame.reset_index for column from index:

df1 = (df.set_index(['ID',df.groupby('ID').cumcount()])['Name']
         .unstack(fill_value='')
         .reset_index())
print (df1)
   ID  0  1  2
0   1  A  B   
1   2  X  Y  Z

Performnace in small DataFrame:

np.random.seed(123)
N = 1000
L = list('abcdefghijklmno')
df = pd.DataFrame({'Name': np.random.choice(L, N),
                   'ID':np.random.randint(100, size=N)}).sort_values('ID')
#print (df)

In [15]: %%timeit
    ...: df_new=df.groupby('ID')['Name'].apply(lambda x: ','.join(list(x))).reset_index()
    ...: df_new.join(df_new.pop('Name').str.split(",",expand=True))
    ...: 
22 ms ± 411 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [16]: %%timeit
    ...: df1 = (df.set_index(['ID',df.groupby('ID').cumcount()])['Name']
    ...:          .unstack(fill_value='')
    ...:          .reset_index())
    ...: 
6.05 ms ± 212 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

In [17]: %%timeit
    ...: df.set_index('ID').groupby('ID').apply(lambda x: x.reset_index(drop=True).T).reset_index(level=1,drop=True)
    ...: 
151 ms ± 1.25 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

score 0 · Answer 3 · answered Mar 10 '19 at 07:21

What you want can be created by following code.

    data = [[1,'A'] , [1 , 'B'] , [2 , 'X'] , [2 , 'Y'] , [2 , 'Z']]  
    df = pd.DataFrame(data , columns=['ID' , 'Name'])

    id_list = df['ID'][~df['ID'].duplicated()]
    t_rows = []
    max_val_num = 0
    for id_ in id_list:
        row = df[df['ID'] == id_]['Name'].tolist()
        t_rows.append(row)
        if len(row) >= max_val_num:
            max_val_num = len(row)

    df_transform = pd.DataFrame(t_rows , columns=['col_'+str(i) for i in range(max_val_num)])  
    print(df_transform)

How to transpose a column based on the condition in another column?

3 Answers3