<jupyter_start><jupyter_text>**Integrate Data**In this notebook we combine the different tables obtained from the previous sections (selection, cleaning and cosntructing) into one single table that has, for each developer, the attributes needed to difined their profile.<jupyter_code>import pandas as pd
import numpy as np
from datetime import datetime<jupyter_output><empty_output><jupyter_text>COMMITS_FREQUENCY<jupyter_code>numberCommits = pd.read_csv("../../data/interim/DataPreparation/ConstructData/NUMBER_COMMITS.csv")
numberCommits.head()<jupyter_output><empty_output><jupyter_text>FIXED_ISSUES<jupyter_code>fixedIssues = pd.read_csv("../../data/interim/DataPreparation/ConstructData/FIXED_ISSUES.csv").iloc[:,1:]
fixedIssues.head()
fixedIssues = fixedIssues.rename(columns={'SZZIssues':'fixedSZZIssues','SonarIssues':'fixedSonarIssues','JiraIssues':'fixedJiraIssues'})
fixedIssues.head()<jupyter_output><empty_output><jupyter_text>INDUCED_ISSUES<jupyter_code>inducedIssues = pd.read_csv("../../data/interim/DataPreparation/ConstructData/INDUCED_ISSUES.csv").iloc[:,1:]
inducedIssues.head()
inducedIssues = inducedIssues.rename(columns={'SZZIssues':'inducedSZZIssues','SonarIssues':'inducedSonarIssues'})
inducedIssues.head()<jupyter_output><empty_output><jupyter_text>---After reading these three tables, we can start by joining them according to the attribute `committer` (which identifies the developers). First we join `COMMITS_FREQUENCY` with `FIXED_ISSUES`:<jupyter_code>dataFrame = pd.merge(numberCommits, fixedIssues,  how='outer', left_on=['committer'], right_on = ['committer'])
len(dataFrame.committer.unique())
print(dataFrame.shape)
dataFrame<jupyter_output>(2459, 5)<jupyter_text>After the merge, we see that some of the attributes have missing values. A missing values means that the developer has never made a commit so we can replace these `NaN`'s by $0$. The other attributes are the number of issues fixed by the developer so a missing in this case means that the person did not fix any issue. Therefore, we can replace de `NaN`'s by zeros too.<jupyter_code>dataFrame = dataFrame.fillna(0.0)
dataFrame<jupyter_output><empty_output><jupyter_text>Now we join the Data Frame obtenined in the previous join with the table `INDUCED_ISSUES`:<jupyter_code>dataFrame = pd.merge(dataFrame, inducedIssues,  how='outer', left_on=['committer'], right_on = ['committer'])
print(dataFrame.shape)
dataFrame.head()<jupyter_output>(2460, 7)<jupyter_text>For the same reason as before, we fill the missing values with zeros:<jupyter_code>dataFrame = dataFrame.fillna(0)
dataFrame.head()<jupyter_output><empty_output><jupyter_text>After joining these three table we have 2.460 developers:<jupyter_code>print(len(dataFrame.committer.unique()))<jupyter_output>2460<jupyter_text>---  TIME_IN_PROJECT<jupyter_code>timeInProject = pd.read_csv("../../data/interim/DataPreparation/ConstructData/TIME_IN_PROJECT.csv").iloc[:,1:]
timeInProject.head()
timeInProject = timeInProject.rename(columns={'time':'timeInProject'})
timeInProject.head()
timeInProject.groupby(['projectID', 'committer']).size().shape
timeInProject.groupby(['committer']).size().shape<jupyter_output><empty_output><jupyter_text>As we can see above, there are some committers that are in more than one project, so we have to compute the mean of time in projects by `committer`:<jupyter_code>timeInProject = timeInProject.groupby(['committer']).mean().iloc[1:,:]
timeInProject<jupyter_output><empty_output><jupyter_text>Now, we have a row per developer so can join the table with the previous Data Frame using the attribute `committer` on both sides:<jupyter_code>dataFrame = pd.merge(dataFrame, timeInProject,  how='outer', left_on=['committer'], right_on = ['committer'])
print(dataFrame.shape)
dataFrame.head()
dataFrame.isna().sum()<jupyter_output><empty_output><jupyter_text>As we can see, there are many `NaN` values in the `timeInProject` attribute; a missing value means that the developer did not do any commit so we could not compute its time in the project; so we also replace these `NaN`s with zeros:<jupyter_code>dataFrame = dataFrame.fillna(0)
dataFrame.head()<jupyter_output><empty_output><jupyter_text>After joining thin new table we have 2460 developers:<jupyter_code>len(dataFrame.committer.unique())<jupyter_output><empty_output><jupyter_text>---  JIRA_ISSUES_timeFrom this table we are interested in knowing the type, priority and resolution time of the bugs in which a developer is working on, so we are interested in the attribute `assignee` to identify the developers.<jupyter_code>jiraIssues = pd.read_csv("../../data/interim/DataPreparation/ConstructData/JIRA_ISSUES_time.csv").iloc[:,1:]
jiraIssues.head()<jupyter_output><empty_output><jupyter_text>Before joining this table with the previous ones, we have to aggregate by developer (`assignee`). For the categorical attributs, we first have to transform them into binary attributes:<jupyter_code>dum = pd.get_dummies(jiraIssues[["type", 'priority']], prefix=['type', 'priority'])
dum
TypePriority = jiraIssues[['assignee']].join(dum)
TypePriority<jupyter_output><empty_output><jupyter_text>Erase the rows that have been not assigned as we cannot extract information from any developer.<jupyter_code>TypePriority = TypePriority[TypePriority.assignee!='not-assigned'].reset_index().iloc[:,1:]
TypePriority<jupyter_output><empty_output><jupyter_text>Now we can aggregate by developer, adding the values:<jupyter_code>TypePriority = TypePriority.groupby(["assignee"]).sum()
TypePriority<jupyter_output><empty_output><jupyter_text>The attribute `resolutionTime` can be aggregated by `assignee` computing the mean value:<jupyter_code>resolutionTime = jiraIssues.loc[:,['assignee','resolutionTime']]
resolutionTime = resolutionTime.groupby(["assignee"]).mean()
resolutionTime<jupyter_output><empty_output><jupyter_text>And now, we can join this attribute, with the ones previously aggregated:<jupyter_code>jiraIssues = resolutionTime.join(TypePriority)
jiraIssues
jiraIssues = jiraIssues.reset_index().rename(columns={'assignee':'committer'})
jiraIssues<jupyter_output><empty_output><jupyter_text>Having the table aggregated by developer, we can merge it with the Data Frame:<jupyter_code>dataFrame = pd.merge(dataFrame, jiraIssues, how='left', left_on=['committer'], right_on = ['committer'])
print(dataFrame.shape)
dataFrame.head()<jupyter_output>(2460, 27)<jupyter_text>The missing values that we get can be filled with zeros because they all make referance to quantities:<jupyter_code>dataFrame.isna().sum()
dataFrame = dataFrame.fillna(0.0)<jupyter_output><empty_output><jupyter_text>With this new table we still have 2460 developers:<jupyter_code>len(dataFrame.committer.unique())<jupyter_output><empty_output><jupyter_text>---  GIT_COMMITS_CHANGES_cleanOnce more, some of the attributes of this table are cathegorical, so we first have to binarize them in order to aggregate:<jupyter_code>gitCommitsChanges = pd.read_csv("../../data/interim/DataPreparation/CleanData/GIT_COMMITS_CHANGES_clean.csv").iloc[:,2:]
print(gitCommitsChanges.shape)
gitCommitsChanges.head()
dum = pd.get_dummies(gitCommitsChanges[["changeType"]])
dum = dum.rename(columns={'changeType_ModificationType.ADD':'ADD', 'changeType_ModificationType.DELETE':'DELETE', 'changeType_ModificationType.MODIFY':'MODIFY', 'changeType_ModificationType.RENAME':'RENAME', 'changeType_ModificationType.UNKNOWN':'UNKNOWN'})
dum
Lines = gitCommitsChanges[["commitHash",'linesAdded','linesRemoved']]
gitCommitsChanges = pd.concat([Lines,dum], axis=1)
gitCommitsChanges<jupyter_output><empty_output><jupyter_text>Now we can aggregate by commit, computing the addition or the mean:<jupyter_code>gitCommitsChanges = gitCommitsChanges.groupby(['commitHash']).agg({'ADD':'sum',	'DELETE':'sum',	'MODIFY':'sum',	'RENAME':'sum',	'UNKNOWN':'sum', 'linesAdded':'mean', 'linesRemoved':'mean'})
gitCommitsChanges<jupyter_output><empty_output><jupyter_text>To be able to aggregate by developer, we first have to join this table with the table `GIT_COMMITS`, which has `commitHash` (as this table) and `committer`:<jupyter_code>gitCommits = pd.read_csv("../../data/interim/DataPreparation/CleanData/GIT_COMMITS_clean.csv")
gitCommits.head()
gitCommitsChanges = pd.merge(gitCommits, gitCommitsChanges, how='left', left_on=['commitHash'], right_on = ['commitHash'])
gitCommitsChanges<jupyter_output><empty_output><jupyter_text>Once these two table are merged, we can group by commiter, computing the mean of the values:<jupyter_code>gitCommitsChanges = gitCommitsChanges[['committer','ADD',	'DELETE',	'MODIFY',	'RENAME',	'UNKNOWN',	'linesAdded',	'linesRemoved']]

gitCommitsChanges = gitCommitsChanges.groupby(['committer']).mean()
gitCommitsChanges<jupyter_output><empty_output><jupyter_text>Now that we have the table `GIT_COMMITS_CHANGES` aggregated by commiter, we can join it with the Data Frame using the attribute `commiter`:<jupyter_code>dataFrame = pd.merge(dataFrame, gitCommitsChanges, how='left', left_on=['committer'], right_on = ['committer'])
dataFrame.head()<jupyter_output><empty_output><jupyter_text>As in previous cases, the missing values that appear after the merging, can be replaced by zeros:<jupyter_code>dataFrame = dataFrame.fillna(0.0)<jupyter_output><empty_output><jupyter_text>---  REFACTORING_MINER_bug<jupyter_code>refactoringMinerBug = pd.read_csv("../../data/interim/DataPreparation/ConstructData/REFACTORING_MINER_bug.csv").iloc[:,1:]
refactoringMinerBug.head()<jupyter_output><empty_output><jupyter_text>As in the previous case, first we have to binarize the cathegorical attributs:<jupyter_code>dum = pd.get_dummies(refactoringMinerBug[['refactoringType', 'bug']])
dum
commitHash = refactoringMinerBug[["commitHash"]]
refactoringMinerBug = pd.concat([commitHash,dum], axis=1)
refactoringMinerBug<jupyter_output><empty_output><jupyter_text>And now we can group by commit computing the sum of the values:<jupyter_code>refactoringMinerBug = refactoringMinerBug.groupby(['commitHash']).sum()
refactoringMinerBug<jupyter_output><empty_output><jupyter_text>As in the case of the previous tables, we have to first join this table with `GIT_COMMITS` in order to have the attribute `committer`:<jupyter_code>refactoringMinerBug = pd.merge(refactoringMinerBug, gitCommits, how='left', left_on=['commitHash'], right_on = ['commitHash'])
refactoringMinerBug = pd.concat([refactoringMinerBug[['committer']], refactoringMinerBug.iloc[:,:-4]], axis=1)
refactoringMinerBug<jupyter_output><empty_output><jupyter_text>Now we can aggregate by committer computing the mean of the values:<jupyter_code>refactoringMinerBug = refactoringMinerBug.groupby(['committer']).sum()
refactoringMinerBug<jupyter_output><empty_output><jupyter_text>Since we have the values aggregated by developer, we can join this table with the Data Frame using the attribute `committer`; and the fill the new missing values with zeros:<jupyter_code>dataFrame = pd.merge(dataFrame, refactoringMinerBug, how='left', left_on=['committer'], right_on = ['committer'])
dataFrame.head()
dataFrame = dataFrame.fillna(0.0)<jupyter_output><empty_output><jupyter_text>---  SONAR_MEASURES_difference<jupyter_code>sonarMeasures = pd.read_csv("../../data/interim/DataPreparation/ConstructData/SONAR_MEASURES_difference.csv").iloc[:,1:]
sonarMeasures.head()<jupyter_output><empty_output><jupyter_text>First of all, we merge this table with `GIT_COMMITS` using `commitHash` in order to have the attribut `committer`:<jupyter_code>gitCommits = pd.read_csv("../../data/interim/DataPreparation/CleanData/GIT_COMMITS_clean.csv")[['commitHash', 'committer']]
sonarMeasures = pd.merge(sonarMeasures, gitCommits, how='left', on='commitHash').iloc[:,2:]
sonarMeasures
list(sonarMeasures)<jupyter_output><empty_output><jupyter_text>Then, we aggregate by commiter computing the mean of the values:<jupyter_code>sonarMeasures_committer = sonarMeasures.groupby(['committer']).agg({'functions':'sum', 'commentLinesDensity':'mean', 
'complexity':'sum', 'functionComplexity':'sum', 'duplicatedLinesDensity':'mean', 'violations':'sum', 'blockerViolations':'sum',
 'criticalViolations':'sum','infoViolations':'sum','majorViolations':'sum','minorViolations':'sum','codeSmells':'sum',
 'bugs':'sum','vulnerabilities':'sum','cognitiveComplexity':'sum','ncloc':'sum','sqaleIndex':'sum',
 'sqaleDebtRatio':'sum','reliabilityRemediationEffort':'sum','securityRemediationEffort':'sum'}).reset_index()
sonarMeasures_committer<jupyter_output><empty_output><jupyter_text>Once we have the table aggregated by commiter, we can merge it with the Data Frame using the attribute `committer`. Also, we fill the created missing values with zeros:<jupyter_code>dataFrame = pd.merge(dataFrame, sonarMeasures_committer, how='left', on='committer')
dataFrame = dataFrame.fillna(0)
dataFrame.head()<jupyter_output><empty_output><jupyter_text>---  SONAR_ISSUES_timeWe begin by taking the attributs from the table that we are interested in:<jupyter_code>sonarIssues = pd.read_csv("../../data/interim/DataPreparation/ConstructData/SONAR_ISSUES_time.csv").iloc[:,4:]
sonarIssues = pd.concat([sonarIssues[['creationCommitHash']], sonarIssues.iloc[:,2:-2], sonarIssues[['closeTime']]], axis=1)
sonarIssues.head()<jupyter_output><empty_output><jupyter_text>The first thing that we have to do in this table is to transform the values of the attribute `debt` so it can be used in the future modelling section. This attribute has time values are string that can be in minuts or hours depending on the last letters of the string. For example, a value can be `20min`, `1h` but also it is possible to have `1h20min`. To convert these strings to floats (in hours) we use the Timedelta function from the pandas library as follows:<jupyter_code>debtSec = sonarIssues.debt.apply(pd.Timedelta)
debtHour = debtSec.apply(lambda x: x.seconds/3600 + x.days*24)
debtHour
sonarIssues[['debt']] = debtHour.to_frame()
sonarIssues.head()<jupyter_output><empty_output><jupyter_text>In this table we also have some cathegorical attributs that have to be binarize in order to aggregate:<jupyter_code>dum = pd.get_dummies(sonarIssues[['type',	'severity']])
dum
sonarIssues = pd.concat([sonarIssues[['creationCommitHash','debt','closeTime']], dum], axis=1)
sonarIssues.head()
closeTime = sonarIssues.loc[:,['creationCommitHash','closeTime']]
closeTime = closeTime.groupby(["creationCommitHash"]).mean()
closeTime<jupyter_output><empty_output><jupyter_text>Now we aggregate by commit the `debt` attribute previously computed:<jupyter_code>Debt = sonarIssues[['creationCommitHash','debt']]
Debt = Debt.groupby(['creationCommitHash']).sum()
Debt<jupyter_output><empty_output><jupyter_text>We also aggregate by commit the cathegorical attributes previously binerized:<jupyter_code>TypesSeverities = pd.concat([sonarIssues[['creationCommitHash']], sonarIssues.iloc[:,3:]], axis=1)
TypesSeverities = TypesSeverities.groupby(['creationCommitHash']).sum()
TypesSeverities<jupyter_output><empty_output><jupyter_text>We concat the attributes that we have previously aggregated by commit:<jupyter_code>sonarIssues = pd.concat([Debt, closeTime, TypesSeverities], axis=1)
sonarIssues<jupyter_output><empty_output><jupyter_text>Now, we have to merge this table with the `GIT_COMMITS` table using `creationCommitHash` and `commitHash` in order to have the attribute `committer`:<jupyter_code>sonarIssues2 = pd.merge(sonarIssues, gitCommits, how='left', left_on=['creationCommitHash'], right_on=['commitHash'])
sonarIssues2<jupyter_output><empty_output><jupyter_text>As before, we aggregate the `closeTime` attribute by `committer`:<jupyter_code>closeTime = sonarIssues2.loc[:,['committer','closeTime']]
closeTime = closeTime.groupby(["committer"]).mean()
closeTime<jupyter_output><empty_output><jupyter_text>We also aggregate the `debt` attribute by `committer`:<jupyter_code>Debt = sonarIssues2[['committer','debt']]
Debt = Debt.groupby(['committer']).sum()
Debt<jupyter_output><empty_output><jupyter_text>And the cathegorical variables previously binerized:<jupyter_code>TypesSeverities = pd.concat([sonarIssues2[['committer']], sonarIssues2.iloc[:,2:-3]], axis=1)
TypesSeverities = TypesSeverities.groupby(['committer']).sum()
TypesSeverities<jupyter_output><empty_output><jupyter_text>Then, we concat the attributes to obtein the table aggregated by `committer`. This table, then, can be merged with the Data Frame using the `committer` attribute:<jupyter_code>sonarIssues2 = pd.concat([Debt, closeTime, TypesSeverities], axis=1)
sonarIssues2['committer'] = sonarIssues2.index
sonarIssues2 = sonarIssues2.iloc[:,:-1]
sonarIssues2
dataFrame = pd.merge(dataFrame, sonarIssues2, how='left', on='committer')
dataFrame.head()<jupyter_output><empty_output><jupyter_text>Again, the missing values can be replaced with zeros:<jupyter_code>dataFrame = dataFrame.fillna(0.0)
dataFrame.head()<jupyter_output><empty_output><jupyter_text>---  **Save the final Data Frame:** This is the final list of attributs included in the Data Frame:<jupyter_code>list(dataFrame)<jupyter_output><empty_output><jupyter_text>We now save this Data Frame into a *csv* file:<jupyter_code>dataFrame.to_csv('../../data/interim/DataPreparation/DATA_FRAME.csv', header=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12648611030563128, 'fraction_numerical': 0.005683465754219103, 'mean_word_length': 9.394213381555154, 'pattern_counts': {'":': 0, '<': 209, '<?xml version=': 0, '>': 209, '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': '53', 'dir': 'jupyter-structured-clean-dedup', 'id': '411385', 'license': 'MIT', 'n_tokens_mistral': 5865, 'n_tokens_neox': 5678, 'n_words': 1212, 'path': 'notebooks/2-DataPreparation/1-DB-integration.ipynb', 'repo_name': 'chus-chus/softwareDevTypes', 'size': '17243'}