How best to use pandas.DataFrame.pivot?
<p>I am trying to translate a dataframe from rows of key, value to a table with keys as the columns and values as cells. For example:</p>

<p>Input dataframe with key, value:</p>

<pre><code>&gt;&gt;&gt;df = pd.DataFrame([['TIME', 'VAL1', 'VAL2', 'VAL3', 
                       'TIME', 'VAL1', 'VAL2', 'VAL3'],
                      ["00:00:01",1,2,3,"00:00:02", 1,2,3]]).T

    0       1
0   TIME    00:00:01
1   VAL1    1
2   VAL2    2
3   VAL3    3
4   TIME    00:00:02
5   VAL1    1
6   VAL2    2
7   VAL3    3
</code></pre>

<p>I want it to look like:</p>

<pre><code>TIME      VAL1 VAL2 VAL3
00:00:01  1    2    3
00:00:02  1    2    3
</code></pre>

<p>I can almost get what I want with pivot:</p>

<pre><code>&gt;&gt;&gt;df.pivot(columns=0, values=1)
    TIME        VAL1    VAL2    VAL3
0   00:00:01    None    None    None
1   None        1       None    None
2   None        None    2       None
3   None        None    None    3
4   00:00:02    None    None    None
5   None        1       None    None
6   None        None    2       None
7   None        None    None    3
</code></pre>

<p>And I can merge the rows to get what I want:</p>

<pre><code>&gt;&gt;&gt; df.pivot(columns=0, values=1).ffill().drop_duplicates(subset='TIME',
                                                          keep='last').set_index('TIME')
TIME      VAL1 VAL2 VAL3
00:00:01  1    2    3
00:00:02  1    2    3
</code></pre>

<p>But this seems like a rather awkward way to do it that would waste a lot of memory for a large data set. Is there a simpler method?</p>

<p>I tired looking at <code>pd.DataFrame.from_items()</code> and <code>pd.DataFrame.from_records()</code> but was not having success.</p>

<p>Another way to do this:</p>

<pre><code>In [65]: df
Out[65]: 
      0         1
0  TIME  00:00:01
1  VAL1         1
2  VAL2         2
3  VAL3         3
4  TIME  00:00:02
5  VAL1         1
6  VAL2         2
7  VAL3         3

In [66]: newdf = pd.concat([df[df[0] == x].reset_index()[1] for x in df[0].unique()], axis=1)

In [67]: newdf.columns = df[0].unique()

In [68]: newdf
Out[68]: 
       TIME VAL1 VAL2 VAL3
0  00:00:01    1    2    3
1  00:00:02    1    2    3
</code></pre>

python pandas