Melding several columns by unique value

Question

I have a dataset with a lot of columns. The data is structured like this:

id  Artist 1    Artist 2    Artist 3
1   Red hot     
2   Wiz         Red hot 
3   Red hot     Wiz        Bronson 
4   Bronson     Bruce      Red hot
5   Wiz         Bronson 
6   Red Hot     

And I need it to be like this:

id  Artist
1   Red hot     
2   Wiz     
2   Red hot     
3   Red hot     
3   Wiz     
3   Bronson     
4   Red hot
4   Bronson     
4   bruce       
5   Wiz     
5   Bronson     
6   Red hot     

Is there an easy way to do this in pandas? I've tried using melt from this response, but it's not what im aiming it: pandas convert some columns into rows

thx in advance!

Answer 1

I think you can use:

#if column `id` is not index, first set it to index
df.set_index('id', inplace=True)
#create multiindex from columns
df.columns = df.columns.str.split(expand=True)
print (df)
     Artist                  
          1        2        3
id                           
1   Red hot      NaN      NaN
2       Wiz  Red hot      NaN
3   Red hot      Wiz  Bronson
4   Bronson    Bruce  Red hot
5       Wiz  Bronson      NaN
6   Red Hot      NaN      NaN

#reshape it
print (df.stack().reset_index(drop=True, level=1).reset_index())
    id   Artist
0    1  Red hot
1    2      Wiz
2    2  Red hot
3    3  Red hot
4    3      Wiz
5    3  Bronson
6    4  Bronson
7    4    Bruce
8    4  Red hot
9    5      Wiz
10   5  Bronson
11   6  Red Hot