<jupyter_start><jupyter_text>This notebook is intended to show the process for going from a database connection to two files the first associating an issueto its components and the second to assocating an issue to its comments. These files can then be used a machine learning pipelinethat will apply cleaning, vectorization of the text and building models.<jupyter_code>%pylab inline
from __future__ import print_function
from __future__ import division
import pandas as pd
from bs4 import BeautifulSoup
from collections import defaultdict
import pickle
import MySQLdb as mdb
connection = mdb.connect(host='', user='', db='monorail')
cursor = connection.cursor()
def table_to_dataframe(name, connection):
    return pd.read_sql("SELECT * FROM {};".format(name) , con=connection)
issue = table_to_dataframe('Issue', connection)
comment = table_to_dataframe('Comment', connection)
issue_component = table_to_dataframe('Issue2Component', connection)
issue.rename(columns={'id':'issue_id'}, inplace=True)
chrome_issue = issue[issue['project_id'] == 16].copy()
chrome_issue_id_set = set(chrome_issue['issue_id'])<jupyter_output><empty_output><jupyter_text>Associate an issue withs its components<jupyter_code>components_by_issue = defaultdict(list)
i = 0
for index, row in issue_component.iterrows():
    if row['issue_id'] in chrome_issue_id_set:
        components_by_issue[row['issue_id']].append(row['component_id'])
    if i % 100000 == 0:
        print(i)
    i += 1

chrome_issue['components'] = chrome_issue['issue_id'].apply(lambda i_id: components_by_issue[i_id])<jupyter_output><empty_output><jupyter_text>Associate an issue withs its comments<jupyter_code>comments_by_issue = defaultdict(list)
i = 0
for index, row in chrome_comment.iterrows():
    comments_by_issue[row["issue_id"]].append((index, row.created))
    if i % 1000000 == 0:
        print(i)
    i += 1

chrome_issue["comments"] = chrome_issue["issue_id"].apply(lambda i_id: 
                                                          [tup[0] for tup 
                                                           in sorted(comments_by_issue[i_id], 
                                                                     key=lambda x: x[1])])<jupyter_output><empty_output><jupyter_text>Only work with closed issues for training<jupyter_code>closed_chrome_issues = chrome_issue[chrome_issue["closed"] > 0]<jupyter_output><empty_output><jupyter_text>Subsample the data (faster to run experiments)<jupyter_code>num_issues = len(closed_chrome_issues)
issue_subset = closed_chrome_issues.sample(int(num_issues * 0.05))<jupyter_output><empty_output><jupyter_text>Very light cleaning of text (removing markup).<jupyter_code>comment_index_to_text = defaultdict(unicode)

i = 0
for index, row in issue_subset.iterrows():
    for num, comment_id in enumerate(row['comments']):
        text =  BeautifulSoup(comment.loc[comment_id]['content']).get_text().strip().lower()
        comment_index_to_text[comment_id] = text
    
    if i % 10000 == 0:
        print(i)
    i += 1
issue_subset.to_pickle('subset_issue.pkl')
pickle.dump(comment_index_to_text, open('comment_text.pkl', 'w'))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10609409535838332, 'fraction_numerical': 0.011682980738869593, 'mean_word_length': 4.538461538461538, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 26, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 7, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '322892', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 1003, 'n_tokens_neox': 957, 'n_words': 225, 'path': 'appengine/monorail/tools/datalab/subsample_data.ipynb', 'repo_name': 'allaparthi/monorail', 'size': '3167'}