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>You could get all your <code>Record2</code> values under the <code>Record1</code> columns as follows:</p>

<p><strong>Data Setup:</strong>   </p>

<pre><code>data = StringIO(
'''
dataTime Record1Field1 Record1Field2 Record1Field3 Record2Field1 Record2Field2 Record2Field3
01-01-2015 1 2 3 4 5 6 
''')

df = pd.read_csv(data, delim_whitespace=True, parse_dates=['dataTime'])
print (df)

    dataTime  Record1Field1  Record1Field2  Record1Field3  Record2Field1  \
0 2015-01-01              1              2              3              4   

   Record2Field2  Record2Field3  
0              5              6 
</code></pre>

<p><strong>Operations:</strong></p>

<pre><code>df.set_index('dataTime', inplace=True)

# Filter column names corresponding to Record2
tempdf = df[[col for col in list(df) if col.startswith('Record2')]]

# Drop those columns after assigning to tempdf
df.drop(tempdf.columns, inplace=True, axis=1)

# Rename the column names for appending
tempdf.columns = [col for col in list(df) if col.startswith('Record1')]

# Concatenate row-wise
print (df.append(tempdf))

            Record1Field1  Record1Field2  Record1Field3
dataTime                                               
2015-01-01              1              2              3
2015-01-01              4              5              6
</code></pre>

python pandas dataframe