Pandas: Splitting a Row Into Multiple Rows Efficiently
<p>The problem I am currently facing is taking a pandas DataFrame and efficiently taking each record and breaking it down into multiple records in the following way:</p>

<p>Input:</p>

<pre><code>In [16]: pd.DataFrame({'Name': 'Person1', 'State': 'Indiana', 'Money1': 100.42, 'Money2':54.54, 'Money3': 23.45}, index=[1])
Out[16]: 
   Money1  Money2  Money3  Name     State
1  100.42  54.54   23.45   Person1  Indiana
</code></pre>

<p>Output:</p>

<pre><code>   Money1  Money2  Money3  Name     State
1  100.42  np.nan  np.nan  Person1  Indiana 
2  np.nan  54.54   np.nan  Person1  Indiana      
3  np.nan  np.nan  23.45   Person1  Indiana
</code></pre>

<p>Essentially, the problem is splitting the original record into x records where x is a list of passed in columns to split by (in this case 'Money1', 'Money2', 'Money3'. I have tried doing this by creating DataFrames and concat'ing them but this is extremely slow and memory inefficient.</p>

<p>EDIT1:</p>

<p>Please not that the answer does not work if even one of your static columns (the ones that are turned into multi-indices) is full of NaN. This is a reported bug in pandas:
<a href="https://github.com/pydata/pandas/issues/6322" rel="nofollow">https://github.com/pydata/pandas/issues/6322</a></p>

<p>To get around it, use <code>fillna</code> or <code>replace</code> to fill in columns comprised entirely of <code>NaN</code> with empty string <code>''</code>, for example, and then after this process, put the <code>NaN</code> back in.</p>