如何在没有重复值的情况下完成数据帧转置？

Question

我有一个数据框，它看起来像这样：

    QNumber  Supplier_name
0   0005267    BOWER CORP              
1   0005267    ROLLWAY                    
2   0005788    AIRCRAFT PRODUCTS  

现在我想转置它，但不只是简单的转置，我应用groupby来帮助我得到以下结果：

Merged_table.sort_values('QNumber').groupby('QNumber')['Supplier_name'].apply(lambda Merged_table: Merged_table.reset_index(drop=True)).unstack().add_prefix("Supplier_name ").reset_index()

    QNumber  Supplier_name_0        Supplier_name_1         
0   0005267    BOWER CORP               ROLLWAY                                                                  
1   0005788    AIRCRAFT PRODUCTS        NaN                                 

但是，我想要的是：

    QNumber  Supplier_name_0        Supplier_name_1      
0   0005267    BOWER CORP               NaN                                   
1   0005267    NaN                      ROLLWAY                               
2   0005788    AIRCRAFT PRODUCTS        NaN                                     

我很感激你的每一个建议

Answer 1

你可以做：

df2=df.pivot_table(index=[df.index, "QNumber"], columns=df.groupby("QNumber").cumcount(), values=["Supplier_name"], aggfunc=''.join)
#in order to reset multilevel index for columns:

df2.columns=[f"Supplier{i}" for i in range(len(df2.columns.values))]

df2.reset_index("QNumber", inplace=True)

和输出：

QNumber          Supplier0 Supplier1
0  0005267         BOWER CORP       NaN
1  0005267                NaN   ROLLWAY
2  0005788  AIRCRAFT PRODUCTS       NaN

Answer 2

IIUC，我会用：

df_out = df.set_index(['QNumber', df.groupby('QNumber').cumcount()], append=True).unstack()
df_out.columns = [f'{i}_{j}' for i, j in df_out.columns]
df_out = df_out.reset_index(level=1)
print(df_out)

输出：

   QNumber    Supplier_name_0 Supplier_name_1
0     5267         BOWER CORP             NaN
1     5267                NaN         ROLLWAY
2     5788  AIRCRAFT PRODUCTS             NaN