Split and merge pandas dataframe
<p>I am trying to split and merge a Pandas dataframe.</p>

<p>The columns of the original data frame are arranged like so:</p>

<pre><code>dataTime Record1Field1 ... Record1FieldN Record2Field1 ... Record1FieldN
time1    &lt;&lt;     record 1 data         &gt;&gt; &lt;&lt;       record 2 data       &gt;&gt;
</code></pre>

<p>I would like to take split the <code>Record2</code> fields into a separate data frame <code>tempdf</code>, indexed by the dataTime. <code>tempdf</code> will therefore look something like this:</p>

<pre><code>dataTime Record2Field1 ... Record2FieldN
time1    &lt;&lt; record 2 data             &gt;&gt;
</code></pre>

<p>Once <code>tempdf</code> is populated, delete the Record2 columns from the original data frame. The first difficulty I'm having is in creating this <code>tempdf</code> which contains the record 2 data.</p>

<p>Then, I would like to rename the columns in <code>tempdf</code> so that they align with the <code>Record1</code> columns in the original data frame. (This portion I know how to do)</p>

<p>Finally I would like to merge <code>tempdf</code> back into the original data frame.</p>

<p>The end result should look something like this:</p>

<pre><code>dataTime Record1Field1 ... Record1FieldN
time1    &lt;&lt;record 1 data&gt;&gt;
time1    &lt;&lt;record 2 data&gt;&gt;
</code></pre>

<p>So far I haven't determined a good method of doing this. Any help is appreciated! Thanks.</p>

<p>To answer your immediate question, you could use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.filter.html" rel="nofollow"><code>df.filter</code></a> with a regex pattern to select the columns of the form <code>Record2FieldN</code>:</p>

<pre><code>In [29]: tempdf = df.filter(regex=r'Record2.*'); tempdf
Out[29]: 
   Record2Field0  Record2Field1  Record2Field2
0              3              8              4
1              2              6              3
2              1              2              2
3              5              9              4
</code></pre>

<p>and you could rename the columns using <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.rename.html" rel="nofollow"><code>tempdf.rename</code></a>:</p>

<pre><code>tempdf = tempdf.rename(columns={'Record2Field{}'.format(i):'Record1Field{}'.format(i) for i in range(3)})
</code></pre>

<p>and <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.drop.html" rel="nofollow">drop</a> the <code>Record2</code> fields from <code>df</code> with:</p>

<pre><code>df = df.drop(['Record2Field{}'.format(i) for i in range(3)], axis=1)
</code></pre>

<hr>

<p>But there is a better approach to your overall problem: Replace the flat column names <code>RecordMFieldN</code> with a 2-level <a href="http://pandas.pydata.org/pandas-docs/stable/advanced.html" rel="nofollow"><code>MultiIndex</code></a> which splits the <code>Record</code> from the <code>Field</code>. 
This will give you enough control to <em>stack</em> the data in the desired form:</p>

<pre><code>import numpy as np
import pandas as pd
np.random.seed(2016)

ncols, nrows = 3, 4
def make_dataframe(ncols, nrows):
    columns = ['Record{}Field{}'.format(i, j) for i in range(1,3) 
               for j in range(ncols)]
    df = pd.DataFrame(np.random.randint(10, size=(nrows, 2*ncols)), columns=columns)
    df['dataTime'] = pd.date_range('2000-1-1', periods=nrows)
    return df

df = make_dataframe(ncols, nrows)

# stash the `dataTime` in the row index so we can reassign 
# the column index to `new_index`
result = df.set_index('dataTime')
new_index = pd.MultiIndex.from_product([[1,2], df.columns[:ncols]], 
                                       names=['record', 'field'])
result.columns = new_index

# Now the problem can be solved by stacking.
result = result.stack('record')
result.index = result.index.droplevel('record')
</code></pre>

<p>yields</p>

<pre><code>field       Record1Field0  Record1Field1  Record1Field2
dataTime                                               
2000-01-01              3              7              2
2000-01-01              3              8              4
2000-01-02              8              7              9
2000-01-02              2              6              3
2000-01-03              4              1              9
2000-01-03              1              2              2
2000-01-04              8              9              8
2000-01-04              5              9              4
</code></pre>

python pandas dataframe