D14 D14 - 2 months ago 8
SQL Question

Convert Dataframe from Row based to Columnar

My Dataframe (df) looks like this:

Date FieldA ValueA ValueB
09-02-2016 TypeA 3 5
09-02-2016 TypeB 6 7


I want the dataframe to look like below:

Date TypeA_ValueA TypeA_ValueB TypeB_ValueA TypeB_ValueB
09-02-2016 3 5 6 7


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. pandas_pivot

Exception: Data must be 1-dimensional

Answer
df1 = df.set_index(['Date', 'FieldA']).unstack()
df1.columns = df1.columns.map('_'.join)

df1.reset_index()

enter image description here


Setup Reference

from StringIO import StringIO
import pandas as pd

text = """Date       FieldA  ValueA ValueB
09-02-2016 TypeA   3       5
09-02-2016 TypeB   6       7"""

df = pd.read_csv(StringIO(text), delim_whitespace=True)

df

enter image description here

Comments