Recompose a table from an SQL with row and colum ID in Python
Question: I'm reading from an SQLite table created by a program I have no control on.

The idea behind the layout of this SQL table eludes me, but that's the way it
is.

This table looks like this in SQL:

![SQL Table](http://i.stack.imgur.com/iKpnt.jpg)

What it really is, and what I'd like to get in python-pandas dataframe is
this:

![Equivalent HTML table](http://i.stack.imgur.com/vaJbh.jpg)

So far, this only way I can think of doing this is by getting a list of RowID,
looping on them, and reconstructing the table.

But I'm wondering if there's an easier and faster way (database is huge),
either when doing the SELECT statement or later on the pandas dataframe
itself.

To load the data I'm using:

    
    
    import pandas as pd
    import sqlite3
    
    con = sqlite3.connect(sql_path)
    #Simplified here, to produce the Table I filter on a few things
    df = pd.read_sql('SELECT * from Table',con=con, index_col='RowID')
    

I've desperately tried and failed to use a bunch of stuff including pd.pivot
or pd.groupby but it might just be because I don't know how to use it
properly.

Version:

> Python: 2.7.8 | Pandas: 0.16.0 | sqlite3: 2.6.0


Answer: Just set the index and unstack. You can use 'rowid' instead of 'rowname' or
include both in the index if you don't want to leave either one out, but it's
redundant as far as the solution.

    
    
    df = pd.DataFrame({ 'rowname':[1,1,2,2], 'colname':[1,2,1,2], 'values':['a11','a12','a21','a22'] })
    
    df
       colname  rowname values
    0        1        1    a11
    1        2        1    a12
    2        1        2    a21
    3        2        2    a22
    
    df.set_index(['rowname','colname']).unstack()
    
            values     
    colname      1    2
    rowname            
    1          a11  a12
    2          a21  a22
    

