python - Pandas column content to new columns, with other original columns

Question

A table like below, and I want to make a new table from it (using the values in the 'Color' column).

I've tried:

import pandas as pd
import functools

data = {'Seller': ["Mike","Mike","Mike","Mike","David","David","Pete","Pete","Pete"], 
'Code' : ["9QBR1","9QBR1","9QBW2","9QBW2","9QD1X","9QD1X","9QEBO","9QEBO","9QEBO"],
'From': ["2020-01-03","2020-01-03","2020-01-03","2020-01-03","2020-01-03","2020-01-03","2020-01-03","2020-01-03","2020-01-03"],
'Color_date' : ["2020-02-14","2020-02-14","2020-05-18","2020-05-18","2020-01-04","2020-01-04","2020-03-04","2020-03-13","2020-01-28"],
'Color' : ["Blue","Red","Red","Grey","Red","Grey","Blue","Orange","Red"],
'Delivery' : ["Nancy","Nancy","Kate","Kate","Lilly","Lilly","John","John","John"]}

df = pd.DataFrame(data)

df_1 = df.set_index([df.index, 'Color'])['Color_date'].unstack()
df_1['Code'] = df['Code']

final_df = functools.reduce(lambda left,right: pd.merge(left,right,on='Code'), [df, df_1])

The "df_1" looks ok but the "final_df" is much longer than expected.

Where went wrong, and how can I correct it? Thank you.

question from:https://stackoverflow.com/questions/65932602/pandas-column-content-to-new-columns-with-other-original-columns

Answer 1

Use DataFrame.join with append=True in DataFrame.set_index for add new column to index:

df_1 = df.join(df.set_index('Color', append=True)['Color_date'].unstack())
print (df_1)
  Seller   Code        From  Color_date   Color Delivery        Blue  
0   Mike  9QBR1  2020-01-03  2020-02-14    Blue    Nancy  2020-02-14   
1   Mike  9QBR1  2020-01-03  2020-02-14     Red    Nancy         NaN   
2   Mike  9QBW2  2020-01-03  2020-05-18     Red     Kate         NaN   
3   Mike  9QBW2  2020-01-03  2020-05-18    Grey     Kate         NaN   
4  David  9QD1X  2020-01-03  2020-01-04     Red    Lilly         NaN   
5  David  9QD1X  2020-01-03  2020-01-04    Grey    Lilly         NaN   
6   Pete  9QEBO  2020-01-03  2020-03-04    Blue     John  2020-03-04   
7   Pete  9QEBO  2020-01-03  2020-03-13  Orange     John         NaN   
8   Pete  9QEBO  2020-01-03  2020-01-28     Red     John         NaN   

         Grey      Orange         Red  
0         NaN         NaN         NaN  
1         NaN         NaN  2020-02-14  
2         NaN         NaN  2020-05-18  
3  2020-05-18         NaN         NaN  
4         NaN         NaN  2020-01-04  
5  2020-01-04         NaN         NaN  
6         NaN         NaN         NaN  
7         NaN  2020-03-13         NaN  
8         NaN         NaN  2020-01-28