#!/usr/bin/env python
# coding: utf-8

# #Load DB

# In[2]:


from pandas import DataFrame


# In[5]:


import sqlite3 as sql
conn = sql.connect("/ipython_notebooks/germ_database.db3")
cursor = conn.cursor()


# #Query and convert to dataframe

# In[75]:


cursor_execute_long = cursor.execute('''
SELECT
	one.struct_id,
    one.resNum,
    score_types.score_type_name,
    one.score_value,
    rpi.pdb_residue_number,
    rpi.chain_id
    hc.pdbnum as "mutant_res",
    hc.germ,
    hc.wt
FROM
	residue_scores_1b one
INNER JOIN score_types ON score_types.score_type_id = one.score_type_id
inner join residues r on r.resNum = one.resNum and one.struct_id = r.struct_id
inner join residue_pdb_identification rpi on rpi.residue_number = r.resNum and rpi.struct_id = r.struct_id
left outer join heavy_chain_mutation_shift hc on hc.chain_id = rpi.chain_id and hc.pdbnum = rpi.pdb_residue_number
where rpi.chain_id = "H"
limit 10000
''')


# In[133]:


cursor_execute_short = cursor.execute('''
SELECT
	one.struct_id,
    one.resNum,
    score_types.score_type_name,
    one.score_value
    -- rpi.pdb_residue_number,
    -- rpi.chain_id
FROM
	residue_scores_1b one
INNER JOIN score_types ON score_types.score_type_id = one.score_type_id
inner join residues r on r.resNum = one.resNum and one.struct_id = r.struct_id
inner join residue_pdb_identification rpi on rpi.residue_number = r.resNum and rpi.struct_id = r.struct_id
limit 10000
''')


# In[134]:


df = DataFrame([i for i in cursor_execute_short])


# In[140]:


df.columns = [i[0] for i in cursor.description]
print df.columns
print df.head(30)


# ##Pivot
# 
# ###Why doesn't pivot work here?

# In[149]:


df.pivot(columns='score_type_name',values='score_value',index=['struct_id','resNum'])


# #Pivot Table
# 
# ###Does work, but not ideal

# In[152]:


pivoted = df.pivot_table(columns='score_type_name',values='score_value',index=['struct_id','resNum'])


# In[ ]: