How to reshape the dataframe and transform columns as rows?

Question

df = pd.DataFrame({'ProdcutID': {0: '2125',1: '1204',2: '4390'},

          'Color':{0:'R',1:'B',2:'Y'},

          'From':{0:'CA',1:'OH',2:'IN'},

         'Color1':{0:'P',2:'W'},

         'From1':{0:'NJ',2:'DE'},

         'Color3':{1:'G',2:'P'},

         'From3':{1:'MX',2:'PA'}})

I have above weird dataframe and want to transform the columns into rows. I tried to use df.T but didn't get what I want. Probably use df.groupby('ProductID')...?

Expected results:

Answer 1

You can rename some columns and then use the wide_to_long method from pandas. Like this:

df1 = df.rename(columns={'Color':'Color0','From':'From0'})

pd.wide_to_long(df1,['Color','From'],'ProdcutID','No').sort_index(level=0).dropna()