Don Smythe Don Smythe - 2 months ago 11
Python Question

Python transpose sqlite table

I have an sqlite database with a table organized like:

itemdata

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


I would like to retrieve the data in the itemdata table to be like:

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


I have tried:

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)


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.

Any hints on how to do this?

Answer

Use pivot against a raw dataframe that loads the sqllite table:

df.pivot('date_time', 'item_code', 'value')