Stacking a pair of columns by looking at the first column
<p>I struggle with making the move from Excel to Python since I'm so used to having everything be visible.  Below, I'm trying to convert the table up top to the table below.  Wanted to use pandas dataframes but if there's a different solution that's better then I'd love to hear it.</p>

<p>Also, as an added bonus, if someone can point me to some resources that are empathetic to visual excel converts to Python, that would be awesome!</p>

<p>*Note, there are actually ~350 rows of this and we could go as far as ID12 and Code 12.  Also, a state could repeat in my raw data source just like VA is doing here.</p>

<pre><code>State    ID     Code    ID2     Code2   ID3   Code3
VA       RIC    733     FFX     787     NULL  NULL
NC       WIL    798     GSB     698     WSS   444
VA       NPN    757     NULL    NULL    NULL  NULL
</code></pre>

<p>Required Output:</p>

<pre><code>State  ID   Code
VA     RIC  733
VA     FFX  787
VA     NPN  757
NC     WIL  798
NC     GSB  698
NC     WSS  444
</code></pre>

<p>I think <a href="https://github.com/pydata/pandas/blob/master/pandas/core/reshape.py#L805" rel="nofollow"><code>lreshape</code></a> would be ideal for this situation.</p>

<pre><code>pd.lreshape(df, {'Code': ['Code', 'Code2', 'Code3'], 'ID': ['ID', 'ID2', 'ID3']})  \       
  .sort_values('State', ascending=False)

  State   Code   ID
0    VA  733.0  RIC
2    VA  757.0  NPN
3    VA  787.0  FFX
1    NC  798.0  WIL
4    NC  698.0  GSB
5    NC  444.0  WSS
</code></pre>

<p>A more generic solution apart from @MaxU's would be:</p>

<pre><code>code_list = [col for col in list(df) if col.startswith('Code')]
id_list = [col for col in list(df) if col.startswith('ID')]

pd.lreshape(df, {'Code': code_list, 'ID': id_list}).sort_values('State', ascending=False)
</code></pre>

python pandas dataframe reshape