import os
import csv
import sys
import math
import numpy
import MySQLdb

db = MySQLdb.connect(host="localhost", port=3307, user="root", passwd="enter_your_password",db="project_aers")
c = db.cursor()

query = """
select stitch_id, t_statistic, prr, drug_num
from pred_drug_events_b
join gold_drug_ae using (stitch_id, umls_id)
where t_statistic > 0
and prr > 1
"""
c.execute(query)

data = c.fetchall()

estimates = sorted([x[3] for x in data])
num_bins = 50
bin_length = len(estimates)/num_bins
bins = [(estimates[i*bin_length], estimates[(i+1)*bin_length]) for i in range(num_bins)]
bins[-1] = (bins[-1][0], max(estimates)+0.01)

binned = dict()
for bin in bins:
    binned[bin] = list()

for sid, tstat, prr, num in data:
    bin = [x for x in bins if x[0] <= num < x[1]][0]
    binned[bin].append(prr)

for bin in bins:
    values = [x for x in binned[bin]]
    print >> sys.stdout, (bin[0]+bin[1])/2.0, numpy.mean(values), 1.96*numpy.std(values)/math.sqrt(len(sims))