Melding several columns by unique value
<p>I have a dataset with a lot of columns. The data is structured like this:</p>

<pre><code>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     
</code></pre>

<p>And I need it to be like this:</p>

<pre><code>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     
</code></pre>

<p>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: 
<a href="http://stackoverflow.com/questions/28654047/pandas-convert-some-columns-into-rows">pandas convert some columns into rows</a></p>

<p>thx in advance!</p>

<p>I think you can use:</p>

<pre><code>#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
</code></pre>

python excel pandas