Python transpose sqlite table
<p>I have an sqlite database with a table organized like:</p>

<p>itemdata</p>

<pre><code>date_time       | item_code | value

3/13/2015 12:23 | fridge21  | 345.45
3/13/2015 12:23 | heater12  | 12.34
3/13/2015 12:23 | fan02     | 63.78
3/13/2015 12:24 | fridge21  | 345.47
</code></pre>

<p>I would like to retrieve the data in the itemdata table to be like:</p>

<pre><code>date_time | fridge21 | heater12 | fan02 

3/13/2015 12:23 | 345.45 | 12.34 | 63.78
3/13/2015 12:24 | 345.47 | 12.45 | 63.23
</code></pre>

<p>I have tried:</p>

<pre><code>conn = sqlite3.connect(DB_NAME)  
sql = "SELECT * FROM {0}".format(table_name)
df = pd.read_sql_query(sql, conn)
df1 = pd.DataFrame(df.values.T, columns=df.columns)
</code></pre>

<p>But I don't want to exactly transpose the itemdata table (which has 1x10^6 rows and 3 columns), I want to a unique item_codes column only as per the desired output above.</p>

<p>Any hints on how to do this?</p>

<p>Use <code>pivot</code> against a raw dataframe that loads the sqllite table:</p>

<pre><code>df.pivot('date_time', 'item_code', 'value')
</code></pre>

python sqlite pandas