<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Explore with Sqlite databases
Step1:  Get utterances from certain time periods in each experiment or for certain episodes
Step2:  Get mutual information between words used in referring expressions and properties of the referent
<ASSISTANT_TASK:>
Python Code:
import sys
sys.path.append("../python/")
import pentoref.IO as IO
import sqlite3 as sqlite
# Create databases if required
if False:   # make True if you need to create the databases from the derived data
    for corpus_name in ["TAKE", "TAKECV", "PENTOCV"]:
        data_dir = "../../../pentoref/{0}_PENTOREF".format(corpus_name)
        dfwords, dfutts, dfrefs, dfscenes, dfactions = IO.convert_subcorpus_raw_data_to_dataframes(data_dir)
        IO.write_corpus_to_database("{0}.db".format(corpus_name),
                                    corpus_name, dfwords, dfutts, dfrefs, dfscenes, dfactions)
# Connect to database
CORPUS = "PENTOCV"
db = sqlite.connect("{0}.db".format(CORPUS))
cursor = db.cursor()
# get the table column header names
print("utts", [x[1] for x in cursor.execute("PRAGMA table_info(utts)")])
print("words", [x[1] for x in cursor.execute("PRAGMA table_info(words)")])
print("refs", [x[1] for x in cursor.execute("PRAGMA table_info(refs)")])
print("scenes", [x[1] for x in cursor.execute("PRAGMA table_info(scenes)")])
print("actions", [x[1] for x in cursor.execute("PRAGMA table_info(actions)")])
Explanation: Explore with Sqlite databases
End of explanation
for row in db.execute("SELECT gameID, starttime, speaker, utt_clean FROM utts" + \
                    " WHERE starttime >= 200 AND starttime <= 300" + \
                     ' AND gameID = "r8_1_1_b"' + \
                    " ORDER BY gameID, starttime"):
    print(row)
Explanation: Get utterances from certain time periods in each experiment or for certain episodes
End of explanation
from collections import Counter
from pentoref.IOutils import clean_utt
piece_counter = Counter()
word_counter = Counter()
word_piece_counter = Counter()
for row in db.execute("SELECT id, gameID, text, uttID FROM refs"):
#for row in db.execute("SELECT shape, colour, orientation, gridPosition, gameID, pieceID FROM scenes"):
    #isTarget = db.execute('SELECT refID FROM refs WHERE gameID ="' + row[4] + '" AND pieceID ="' + row[5] + '"')
    #target = False 
    #for r1 in isTarget:
    #    target = True
    #if not target:
    #    continue
    #print(r)
    #shape, colour, orientation, gridPosition, gameID, pieceID = row
    #piece = gridPosition #shape + "_" + colour
    piece, gameID, text, uttID = row
    
    
    if CORPUS in ["TAKECV", "TAKE"]:
        for f in db.execute('SELECT word from words WHERE gameID ="' + str(gameID) + '"'):
            #print(f)
            for word in f[0].lower().split():
                word_counter[word] += 1
                word_piece_counter[piece+"__"+word]+=1
                piece_counter[piece] += 1
    elif CORPUS == "PENTOCV":
        for word in clean_utt(text.lower()).split():
            word_counter[word] += 1
            word_piece_counter[piece+"__"+word]+=1
            piece_counter[piece] += 1
good_pieces = ["X", "Y", "P", "N", "U", "F", "Z", "L", "T", "I", "W", "V", "UNK"]
print("non standard pieces", {k:v for k,v in piece_counter.items() if k not in good_pieces})
piece_counter
word_counter.most_common(20)
word_total = sum(word_piece_counter.values())
piece_total= sum(piece_counter.values())
for piece, p_count in piece_counter.items():
    print("piece:", piece, p_count)
    p_piece = p_count/piece_total
    highest = -1
    best_word = ""
    rank = {}
    for word, w_count in word_counter.items():
        if w_count < 3: 
            continue
        p_word = w_count / word_total
        p_word_piece = word_piece_counter[piece+"__"+word] / word_total
        mi = (p_word_piece/(p_piece * p_word))
        rank[word] = mi
        if mi > highest:
            highest = mi
            best_word = word
    if True:
        top = 5
        for k, v in sorted(rank.items(), key=lambda x:x[1], reverse=True):
            print(k, v)
            top -=1
            if top <= 0: 
                break
    print("*" * 30)
db.close()
Explanation: Get mutual information between words used in referring expressions and properties of the referent
End of explanation
<END_TASK>