def calculate(): print 'Calculating frame t-score...' sql = """ UPDATE """+Frame._meta.db_table+""" AS f JOIN ( SELECT """+Frame.id.db_column+""", """+Frame.relativeFrequency.db_column+"""+0.0 as p1, """+Frame.verbFrequency.db_column+"""+0.0 as n1, (framesTotal - """+Frame.verbFrequency.db_column+""")+0.0 as n2, (("""+Frame.frameFrequency.db_column+""" - """+Frame.frequency.db_column+""")/(framesTotal - """+Frame.verbFrequency.db_column+"""))+0.0 as p2 FROM """+Frame._meta.db_table+""" JOIN ( SELECT SUM("""+Frame.frequency.db_column+""") AS framesTotal FROM """+Frame._meta.db_table+""") AS total ) AS faux ON faux."""+Frame.id.db_column+""" = f."""+Frame.id.db_column+""" SET """+Frame.tscore.db_column+""" = (p1-p2)/SQRT(POW(n1*p1*(1-p1),2) + POW(n1*p1*(1-p1),2))""" query = database.execute_sql(sql) sql = """ UPDATE """+Frame._meta.db_table+""" AS f JOIN ( SELECT MAX("""+Frame.tscore.db_column+""") as maxTscore, MIN("""+Frame.tscore.db_column+""") as minTscore FROM """+Frame._meta.db_table+""" ) AS faux SET """+Frame.tscore.db_column+""" = ("""+Frame.tscore.db_column+"""-minTscore) / (maxTscore-minTscore)""" query = database.execute_sql(sql)
def calculate(): print 'Calculating frame loglikelihood...' sql = """ UPDATE """+Frame._meta.db_table+""" AS f JOIN ( SELECT """+Frame.id.db_column+""", """+Frame.relativeFrequency.db_column+"""+0.0 as p1, """+Frame.frequency.db_column+"""+0.0 as k1, """+Frame.verbFrequency.db_column+"""+0.0 as n1, ("""+Frame.frameFrequency.db_column+""" - """+Frame.frequency.db_column+""")+0.0 as k2, (framesTotal - """+Frame.verbFrequency.db_column+""")+0.0 as n2, (("""+Frame.frameFrequency.db_column+""" - """+Frame.frequency.db_column+""")/(framesTotal - """+Frame.verbFrequency.db_column+"""))+0.0 as p2 , (("""+Frame.frameFrequency.db_column+""")/framesTotal)+0.0 as p FROM """+Frame._meta.db_table+""" JOIN ( SELECT SUM("""+Frame.frequency.db_column+""") AS framesTotal FROM """+Frame._meta.db_table+""") AS total ) AS faux ON faux."""+Frame.id.db_column+""" = f."""+Frame.id.db_column+""" SET """+Frame.logLikelihoodRatio.db_column+""" = 2 * ( (k1*LOG10(p1)+(n1-k1)*LOG10(1-p1)) +(k2*LOG10(p2)+(n2-k2)*LOG10(1-p2)) -(k1*LOG10(p)+(n1-k1)*LOG10(1-p)) -(k2*LOG10(p)+(n2-k2)*LOG10(1-p)))""" query = database.execute_sql(sql) sql = """ UPDATE """+Frame._meta.db_table+""" AS f JOIN ( SELECT MAX("""+Frame.logLikelihoodRatio.db_column+""") as maxLLR FROM """+Frame._meta.db_table+""" ) AS faux SET """+Frame.logLikelihoodRatio.db_column+""" = """+Frame.logLikelihoodRatio.db_column+""" / maxLLR""" query = database.execute_sql(sql)
def calculate(): print 'Calculating relative frequencies...' database.execute_sql(""" UPDATE """+Frame._meta.db_table+""" SET """+Frame.relativeFrequency.db_column+""" = """+Frame.frequency.db_column+"""/"""+Frame.verbFrequency.db_column+""" WHERE 1=1""")
def calculate(): print 'Calculating verb frequencies...' database.execute_sql(""" UPDATE """+Frame._meta.db_table+""" AS f SET """+Frame.verbFrequency.db_column+""" = (SELECT """+Verb.frequency.db_column+""" FROM """+Verb._meta.db_table+""" AS v WHERE v."""+Verb.id.db_column+""" = f."""+Frame.verb.db_column+""") WHERE 1=1""")
def calculate(): print 'Calculating frame frequencies...' database.execute_sql("""UPDATE """+Frame._meta.db_table+""" AS f JOIN ( SELECT """+Frame.frame.db_column+""", SUM("""+Frame.frequency.db_column+""") summer FROM """+Frame._meta.db_table+""" GROUP BY """+Frame.frame.db_column+""" ) AS faux ON faux."""+Frame.frame.db_column+""" = f."""+Frame.frame.db_column+""" SET """+Frame.frameFrequency.db_column+""" = faux.summer """)
def buildVerbListRestriction(self, verbList): if verbList: inSQL = ["\'%s\'" % (verb) for verb in verbList] sqlVerbs = "SELECT "+Verb.id.db_column+" FROM "+Verb._meta.db_table+" WHERE "+Verb.verb.db_column+" in ( "+ (",".join(inSQL)) +")" verbIds = [ str(row[0]) for row in database.execute_sql(sqlVerbs).fetchall() ] restriction = " AND f."+Verb.id.db_column+" IN ( "+ ",".join(verbIds) +" ) " else: restriction = "" return restriction
def calculate(): print 'Calculating verb power law...' for verb in VerbIterator(): sql = "SELECT "+Frame.frequency.db_column+" FROM "+Frame._meta.db_table+" WHERE "+Frame.verb.db_column+" = "+ str(verb.id) +" ORDER BY "+Frame.frequency.db_column frequencies = [ row[0] for row in database.execute_sql(sql).fetchall() ] try: [alpha, xmin, L] = plfit(frequencies, 'finite') except: alpha = None if alpha <> None: verb.alpha = alpha verb.save()
def countNotFilteredFrames(self): sql = self.queries['extracted'] result = database.execute_sql(sql) return result.fetchone()[0]
def countIntersection(self): sql = self.queries['intersection'] result = database.execute_sql(sql) return result.fetchone()[0]
def countGoldenFrames(self): sql = self.queries['golden'] result = database.execute_sql(sql) return result.fetchone()[0]
def filterFrames(self): Frame.update(filtered = False).execute() sql = "UPDATE "+Frame._meta.db_table+" AS f SET "+Frame.filtered.db_column+" = 1 "+ self.buildWhere() database.execute_sql(sql)
def filterVerbs(self, verbList): Verb.update(filtered = False).execute() sql = "UPDATE "+Verb._meta.db_table+" SET "+Verb.filtered.db_column+" = 1 WHERE "+Verb.verb.db_column+" NOT IN ('"+("','".join(verbList))+"') " database.execute_sql(sql)