<jupyter_start><jupyter_text>This notebook illustrates how to obtain the training data for a specific repo (issues with labels), in this case [kubernetes/kubernetes](https://github.com/kubernetes/kubernetes)Note there are several ways to get the training data: (1) BigQuery and (2) Scraping or the API.  This notebook shows how to obtain training data from BigQuery.  In production scenarios we will likely use the API or scraping as not all data is contained in BigQuery.  Part 1 Acquire Data Using [BigQuery](https://console.cloud.google.com/bigquery?sq=1073071082706:e0106dd7b95a4acaabe5ee94d8bffdf9)```sqlstandardSQLSELECT *FROM (  SELECT    updated_at    , MAX(updated_at) OVER (PARTITION BY url) as last_time    , FORMAT("%T", ARRAY_CONCAT_AGG(labels)) as labels    , repo, url, title, body, len_labels  FROM(      SELECT          TIMESTAMP(REGEXP_REPLACE(JSON_EXTRACT(payload, '$.issue.updated_at'), "\"", "")) as updated_at        , REGEXP_EXTRACT(JSON_EXTRACT(payload, '$.issue.url'), r'https://api.github.com/repos/(.*)/issues') as repo        , JSON_EXTRACT(payload, '$.issue.url') as url          -- extract the title and body removing parentheses, brackets, and quotes        , LOWER(TRIM(REGEXP_REPLACE(JSON_EXTRACT(payload, '$.issue.title'), r"\\n|\(|\)|\[|\]||\*|`|\"", ' '))) as title        , LOWER(TRIM(REGEXP_REPLACE(JSON_EXTRACT(payload, '$.issue.body'), r"\\n|\(|\)|\[|\]||\*|`|\"", ' '))) as body        , REGEXP_EXTRACT_ALL(JSON_EXTRACT(payload, "$.issue.labels"), ',"name\":"(.+?)","color') as labels        , ARRAY_LENGTH(REGEXP_EXTRACT_ALL(JSON_EXTRACT(payload, "$.issue.labels"), ',"name\":"(.+?)","color')) as len_labels      FROM `githubarchive.month.20*`      WHERE           _TABLE_SUFFIX BETWEEN '1601' and '1912'          and type="IssuesEvent"  )  WHERE     repo = 'kubernetes/kubernetes'  GROUP BY updated_at, repo, url, title, body, len_labels)WHERE last_time = updated_at and len_labels >= 1``` Data from the above query is available on GCS: [https://storage.googleapis.com/issue_label_bot/k8s_issues/000000000000.csv](https://storage.googleapis.com/issue_label_bot/k8s_issues/000000000000.csv)<jupyter_code>df = pd.read_csv('https://storage.googleapis.com/issue_label_bot/k8s_issues/000000000000.csv')

# convert from string to list
df.labels = df.labels.apply(lambda x: eval(x)) 


# change data type of last_time to date
df['last_time'] = pd.to_datetime(df.last_time)

df.head()<jupyter_output><empty_output><jupyter_text>Clean Data- Remove labels that are deprecated and [no longer specified here](https://raw.githubusercontent.com/kubernetes/test-infra/master/label_sync/labels.yaml). - Remove bot commands that contain the label name (otherwhise this will be data leakage).<jupyter_code>def get_current_labels(url="https://raw.githubusercontent.com/kubernetes/test-infra/master/label_sync/labels.yaml"):
    """
    Get list of valid issue labels (b/c labels get deprecated over time).
    See: https://kubernetes.slack.com/archives/C1TU9EB9S/p1561570627363100
    """
    req = requests.get(url)
    yml = yaml.safe_load(req.content)
    return [x['name'] for x in yml['default']['labels']]


# remove deprecated labels
current_labels = get_current_labels()
df.labels = df.labels.apply(lambda x: [l for l in x if l in current_labels])


# get rid of bot commands
df['body'] = df.body.apply(lambda x: re.sub('(/sig|/kind|/status/triage/|priority) \S+', '', str(x)))<jupyter_output><empty_output><jupyter_text>Filter IssuesRemove Issues that:- Do not have any labels- Do not contain at least one label that has a minimum frequency of 25<jupyter_code># remove labels that do not occur at least 25 times
c = Counter()

for row in df.labels:
    c.update(row)
    
min_threshold = 25
min_threshold_labels = [k for k in c if c[k] >= min_threshold]

df['labels'] = df.labels.apply(lambda x: [l for l in x if l in min_threshold_labels])

# filter out issues without any labels
df = df[df.labels.apply(lambda x: x != [])]
# remove extreanous columns
df = df[['last_time', 'repo', 'title', 'body', 'labels']]
print(f'Number of labeled issues after filtering and cleaning: {df.shape[0]:,}')
df.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1419308357348703, 'fraction_numerical': 0.02521613832853026, 'mean_word_length': 4.705479452054795, 'pattern_counts': {'":': 2, '<': 13, '<?xml version=': 0, '>': 15, 'https://': 9, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 3, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '491301', 'license': 'MIT', 'n_tokens_mistral': 1469, 'n_tokens_neox': 1349, 'n_words': 361, 'path': 'Issue_Embeddings/notebooks/07_Get_Repo_TrainingData_BigQuery.ipynb', 'repo_name': 'rbrishabh/code-intelligence', 'size': '4164'}