Convert Pandas Dataframe from Row based to Columnar
<p>My Dataframe (df) looks like this:</p>

<pre><code>Date       FieldA  ValueA ValueB
09-02-2016 TypeA   3       5
09-02-2016 TypeB   6       7
</code></pre>

<p>I want the dataframe to look like below:</p>

<pre><code>Date       TypeA_ValueA   TypeA_ValueB  TypeB_ValueA TypeB_ValueB
09-02-2016  3             5             6            7
</code></pre>

<p>I tired the df.pivot in pandas where I can provide single Value column. It doesnt take more than one. When I provide more than one i get below exception. <a href="http://pandas.pydata.org/pandas-docs/stable/reshaping.html" rel="nofollow">pandas_pivot</a></p>

<pre><code>Exception: Data must be 1-dimensional
</code></pre>

<p>Use <code>pd.pivot_table</code>.</p>

<pre><code>In [1]: pd.pivot_table(df, index='Date', columns='FieldA', values=['ValueA', 'ValueB'])
Out[1]: 
           ValueA       ValueB      
FieldA      TypeA TypeB  TypeA TypeB
Date                                
09-02-2016      3     6      5     7
</code></pre>

<p>As a result, you'll get a DataFrame with MultiIndex. If you want to flatten it and have <code>_</code> as separator in the column name, you can just do:</p>

<pre><code>In [1]: df = pd.pivot_table(df, index='Date', columns='FieldA', values=['ValueA', 'ValueB'])

In [2]: df.columns = [ '{}_{}'.format(cat, val) for val, cat in df.columns ]

In [3]: df
Out[3]: 
            TypeA_ValueA  TypeB_ValueA  TypeA_ValueB  TypeB_ValueB
Date                                                              
09-02-2016             3             6             5             7
</code></pre>

python sql pandas pyspark spark-dataframe