Combining multiple sources

The information of healthcare is distributed in two sources which have the same structure. The first file corresponds to data breaches already investigated http://asayanalytics.com/breach_archive_csv. The second file contains the observations related to data breaches under investigation https://asayanalytics.com/breach_investigation_csv. For the purpose of this analysis, the two sources are combined in o single dataset adding an identifier called “Investigated” flagging the observations from first file with 1 and 0 for the ones that belong of the second file.

Columns of interest

The column “Web Description” from original data sources is excluded for the purposes of this exercise. The analysis is run over a copy from original dataset which doesn’t include “Web Description” column but it is still on original dataset. It could be used later on sentimental or context analysis.

 [1] "Name of Covered Entity"           "State"                           
 [3] "Covered Entity Type"              "Individuals Affected"            
 [5] "Breach Submission Date"           "Type of Breach"                  
 [7] "Location of Breached Information" "Business Associate Present"      
 [9] "Web Description"                  "Investigated"                    

Formating columns

The column “Breach Submission Date” is transformed to Date format. It allows to execute filters using date format.

To speed up coding, a new column called “year” which contains only the year from “Breach Submission Date” that allows to group and filter information by year of report.

Identifying and removing duplicates

Duplicates were identified for health entity names with some similarities but names are not a 100% match, also the date when the breach was reported is different. However, the state, type of entity, number of individuals affected, breach type and the location of the breach were equal. In this analysis, a column “Duplicated” with a value of 1 was added to the dataset to allow to run analysis filtering observations not duplicated (Duplicated = 0)

Columns with problematic content

The column “Type of Breach” contains all posibilities of breach type that apply for every single row in the same column separated by comma. It does not allow to run analysis by different breach type. The category contains 7 possible values (Hacking / IT Incident, Improper disposal, loss, theft, unauthorized access/disclosure, unknown and other). Therefore, in this exercise 7 new columns are created for every category with a value of 1 or 0 if the original column contains the category that belongs to the new column, for example: new column TB_Hacking_IT = 1 when Type of Breach contains “Hacking/IT Incident”.

Same behavior apply for the column “Location of Breached Information” that contains all posibilities of location that apply for every single row in the same column separated by comma. The column contains 8 possible values (Desktop computer, electronic medical record, email, laptop, network server, other portable electronic device, paper/films and other). Same strategy applies, 8 new columns are created for every category with a value of 1 or 0 if the original column contains the value that belongs to the new column, for example: new column LOC_DesktopComputer = 1 when Location of Breached Information contains “Desktop Computer”.

Outliers

For facility of analysis, outlier in column “Individuals Affected” are flagged in a new column called outlier with a value of 1 or 0. Outliers are required to be excluded in some of the analysis ahead.