<jupyter_start><jupyter_text>TODO: update to more recent and modular version of deepdive. The modularity may make some of these steps easier.  Report confusion matrix stats from deepdive database.Using DeepDive : v0.7.0-0-gf4e6dfe.We have to jump through some hoops to get the confusion matrix based on our training as well as our test data due to the limited way DeepDive reports inference.We want the confusion matrix|  | true negative | true positive ||--|--------------- |--|--||predicted negative | | ||predicted positive | | |The only true negative and true positive results deepdive gives us free is for the holdout fraction.We want to recover the full set of true positive and true negative, and be able to select that portion that was in the training set, and that portion that was in the 'test' or holdout set.We can obtain all these since we included copies of the full true 'negative' and 'positive' sets along with unknown observations.We might further have a set of 'known' unknonwns, those that were very likely to be positive because of the strong MeSH recovery of relevant articles. see also 8_0_3_quick_auditory_test for some details on brief testing of these ideas. Warning: Possible duplicates depending on sql reporting or table handling.We note that when DeepDive reports inference for the holdout portion of the labeled 'known' data it may report slightly different expect values vs. when the same inference is done on the same data when it is labeled 'unknown'. The difference does not seem to be strongly directional, maybe it is random, but we do not know.We handle this issue of non-perfect duplicates in the R script by choosing only one of the duplicate expect values for any given inference.  Save inference tableWe'll use an external R script to generate the confusion matrix, so all we are doing here is extracting those IDs that are in the training data and<jupyter_code># work within app directory to make sure connected to sql as per
# deepdive db.url
# OR disintermediate deepdive.
app = '/Users/ccarey/Documents/Projects/NAMI/rdoc/tasks/deepdive_app/8_0_3_quick_auditory_perception'
%cd {app}
%pwd
#cmd = 'select sentence_id from _annotated_sentences_has_term_inference where has_term is not null order by sentence_id'
#!deepdive sql eval "{cmd}" format=tsv > cc_holdout_ids.tsv
cmd = ('SELECT a.has_term, r.terms, a.sentence_id, expectation '
       'FROM '
       '_annotated_sentences_has_term_inference as a JOIN '
       '_raw_sentences as r ON '
       'a.sentence_id = r.sentence_id '
       'ORDER BY a.sentence_id')
cmd
!deepdive sql eval "{cmd}" format=tsv > cc_all_predictions.tsv<jupyter_output><empty_output><jupyter_text>APPENDIX A.1 Difference in predictions between holdout as known or unknown.The holdouts are reported in inference twice. Once as *labeled knowns* and once as *unkown knowns* due to our pseudocoding a duplicate of all knowns to unknowns. (But note the portion of the knowns used in training is only reported in the pseudocoded 'unknowns' state.)Not many of these, but enough we should be aware of it.<jupyter_code># cmd = ('SELECT DISTINCT r.terms as has_term,a.sentence_id,expectation INTO '
#                'cc_test FROM '
#                '_annotated_sentences_has_term_inference as a JOIN '
#                '_raw_sentences as r ON '
#                'a.sentence_id = r.sentence_id WHERE '
#                'r.terms IS NOT NULL '
#                'ORDER BY a.sentence_id') 

# !deepdive sql 'SELECT * FROM cc_test t1 JOIN (SELECT sentence_id FROM cc_test GROUP BY sentence_id HAVING COUNT(*) >= 2) t2 ON t1.sentence_id = t2.sentence_id'
# has_term | sentence_id | expectation | sentence_id
# ----------+-------------+-------------+-------------
#  t        | 23827717    |       0.828 | 23827717
#  t        | 23827717    |       0.844 | 23827717
#  f        | 23462484    |           0 | 23462484
#  f        | 23462484    |       0.002 | 23462484
#  t        | 24821552    |       0.998 | 24821552
#  t        | 24821552    |       0.992 | 24821552<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07741617357001973, 'fraction_numerical': 0.03328402366863906, 'mean_word_length': 3.3159574468085107, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 12, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '117591', 'license': 'CC-BY-4.0', 'n_tokens_mistral': 1267, 'n_tokens_neox': 1137, 'n_words': 507, 'path': 'jupyter_notebooks/10_1_design_deepdive_sql_reports_for_confusion_matrix.ipynb', 'repo_name': 'charlieccarey/rdoc', 'size': '4056'}