<jupyter_start><jupyter_text>Data Quality Assessment for FCFRD DatasetsThis script evaluates and removes duplicate rows where needed, calculates the number of unique values per column, calculates the number/percent of nulls per column, and counts number of rows with the same set of null column(s). It also creates histogram for quick data analysis and counts Nulls/NaN values on a date field._Author: <NAME>_  Prepare Analysis  Set File Read Parameters<jupyter_code>#Run from Git Hub project folder structure (script is in "notebooks" folder)
localFolder='../data/01_raw/'
fileName='20210225-ems-raw-v04.xlsx'
sheetNameMed='Medications'
sheetNameProcs='Procedures'
sheetNamePatnt='Patients'<jupyter_output><empty_output><jupyter_text>Import Libraries<jupyter_code># Importing required libraries for the project
import sys # for python library version
import pandas as pd
import numpy as np
print('Python: {}'.format(sys.version))  # Python version
print('numpy: {}'.format(np.__version__))  # Numpy version
print('pandas: {}'.format(pd.__version__))  # Pandas version<jupyter_output>Python: 3.8.6 | packaged by conda-forge | (default, Jan 25 2021, 22:54:47) [MSC v.1916 64 bit (AMD64)]
numpy: 1.20.1
pandas: 1.2.2<jupyter_text>Import From Excel Spreadsheet  Reading 'Medications' tab (dataset)<jupyter_code>#Import single tab - Medications
dfMed=pd.read_excel(localFolder+fileName,sheet_name=sheetNameMed)

#Print (1) number of rows/columns and (2) column names/types for quick confirmation of successful import
numOriginalRowsMed=len(dfMed)
numColsMed=len(dfMed.columns)
print(sheetNameMed)
print('Number of Rows = '+str(numOriginalRowsMed))
print('Number of Columns = '+str(numColsMed))
dfMed.dtypes<jupyter_output><ipython-input-4-2301c3432609>:2: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.
  dfMed=pd.read_excel(localFolder+fileName,sheet_name=sheetNameMed)<jupyter_text>Reading 'Procedures' tab (dataset)<jupyter_code>#Import single tab - Procedures
dfProc=pd.read_excel(localFolder+fileName,sheet_name=sheetNameProcs)

#Print (1) number of rows/columns and (2) column names/types for quick confirmation of successful import
numOriginalRowsProc=len(dfProc)
numColsProc=len(dfProc.columns)
print(sheetNameProcs)
print('Number of Rows = '+str(numOriginalRowsProc))
print('Number of Columns = '+str(numColsProc))
dfProc.dtypes<jupyter_output><ipython-input-5-67f352ca8acb>:2: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.
  dfProc=pd.read_excel(localFolder+fileName,sheet_name=sheetNameProcs)<jupyter_text>Reading 'Patients' tab (dataset)<jupyter_code>#Import single tab - Patients
dfPatient=pd.read_excel(localFolder+fileName,sheet_name=sheetNamePatnt)
#Print (1) number of rows/columns and (2) column names/types for quick confirmation of successful import
numOriginalRowsPatient=len(dfPatient)
numColsPatient=len(dfPatient.columns)
print(sheetNamePatnt)
print('Number of Rows = '+str(numOriginalRowsPatient))
print('Number of Columns = '+str(numColsPatient))
dfPatient.dtypes<jupyter_output><ipython-input-6-8b2899caea15>:2: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.
  dfPatient=pd.read_excel(localFolder+fileName,sheet_name=sheetNamePatnt)<jupyter_text>Creating Histogram<jupyter_code>%matplotlib inline
import matplotlib as mlb
import matplotlib.pyplot as plt
## Histogram for patient outcomes for a quick glace of the 'Patient outcome' frqquency count
dfPatient['PatientOutcome'].value_counts().plot(kind='bar')    
## Another Histogram using Seaborn - for patient outcomes
import seaborn as sns
sns.set(rc={'figure.figsize':(11.7,8.27)})
ax = sns.countplot(x=dfPatient['PatientOutcome'])
ax.set_xticklabels(ax.get_xticklabels(), rotation=40, ha="right", fontsize=9)
ax.set_title('Counting Patient Outcomes')
plt.xlabel("Patient Outcome")
plt.ylabel("Outcome Count(Frequency)")
plt.tight_layout()
plt.show()<jupyter_output><empty_output><jupyter_text>Numeric Analysis  Duplicate RowsDuplicate rows will likely need to be removed during data conditioning.<jupyter_code>## Medications dataframe
#Determine which rows are duplicates (True=duplicate, False=first instance of row)
duplicateRowIdentifierMed=dfMed.duplicated()

#Calculate number of duplicate rows
numDuplicateRowsMed=len(duplicateRowIdentifierMed[duplicateRowIdentifierMed==True])
numUniqueRowsMed=numOriginalRowsMed-numDuplicateRowsMed

#Calculate percentage of rows that are duplicates
percentDuplicateRowsMed=round(numDuplicateRowsMed/len(dfMed)*100,4)

#Print results
print("Number of Duplicate Rows =" )
print(numDuplicateRowsMed)
print('Percentage of Rows that are Duplicates = ')
print(percentDuplicateRowsMed)
#Medication Dataset - with duplicates (the rows that are flagged as duplicates ( but not removed) )
dfMedDuplicates=dfMed.loc[duplicateRowIdentifierMed==True,:]

#Confirm
print('Expected Number of duplicate Rows = '+str(numDuplicateRowsMed))
print('Updated Data Frame Shape =')
print(dfMedDuplicates.shape)<jupyter_output>Expected Number of duplicate Rows = 258
Updated Data Frame Shape =
(258, 6)<jupyter_text>Duplicate rows will be removed before conducting the remaining analysis in this script.<jupyter_code>#Medication Dataset - Reduce to the rows that were not flagged as duplicates
dfMed=dfMed.loc[duplicateRowIdentifierMed==False,:]

#Confirm
print('Expected Number of Rows = '+str(numUniqueRowsMed))
print('Updated Data Frame Shape =')
print(dfMed.shape)<jupyter_output>Expected Number of Rows = 62910
Updated Data Frame Shape =
(62910, 6)<jupyter_text>Now checking for Procedure dataset<jupyter_code>##Procedure dataframe
#Determine which rows are duplicates (True=duplicate, False=first instance of row)
duplicateRowIdentifierProc=dfProc.duplicated()

#Calculate number of duplicate rows
numDuplicateRowsProc=len(duplicateRowIdentifierProc[duplicateRowIdentifierProc==True])
numUniqueRowProc=numOriginalRowsProc-numDuplicateRowsProc

#Calculate percentage of rows that are duplicates
percentDuplicateRowProc=round(numDuplicateRowsProc/len(dfProc)*100,4)

#Print results
print("Number of Duplicate Rows =" )
print(numDuplicateRowsProc)
print('Percentage of Rows that are Duplicates = ')
print(percentDuplicateRowProc)
#Procedure Dataset - with duplicates (the rows that are flagged as duplicates ( but not removed) )
dfProcDuplicates=dfProc.loc[duplicateRowIdentifierProc==True,:]

#Confirm
print('Expected Number of duplicate Rows = '+str(numDuplicateRowsProc))
print('Updated Data Frame Shape =')
print(dfProcDuplicates.shape)<jupyter_output>Expected Number of duplicate Rows = 647
Updated Data Frame Shape =
(647, 6)<jupyter_text>Unique Values Per ColumnThis is a simple calculation of the number of unique values per individual column.<jupyter_code># Medication dataset -- Calculate the number of unique values per columns (NULL counts as a value)
dsNumUniqueMed=dfMed.nunique(dropna=False)
dsNumUniqueMed.name='Number of Unique Values'
print(dsNumUniqueMed)<jupyter_output>Dim_Medication_PK                    62910
PatientId                            38405
Medication_Given_RXCUI_Code             33
Medication_Given_Description            33
FRDPersonnelID                         845
Medication_Administered_Date_Time    60069
Name: Number of Unique Values, dtype: int64<jupyter_text>Null Values Per ColumnThis is a simple calculation of the number/percentage of null values per individual column.<jupyter_code>#Medication dataset - Calculate the number of null values per columns
dsNumNullMed=dfMed.isnull().sum()
dsNumNullMed.name='Number Rows With Nulls'
print(dsNumNullMed)
#Calculate the percentage of null values per columns
dsPercentNullMed=(dsNumNullMed/numUniqueRowsMed*100).round(2)
dsPercentNullMed.name='Percent Rows With Nulls in Medications dataset'
print(dsPercentNullMed)
#Merge data series (by column) together
dfPerColMed=pd.DataFrame()
dfPerColMed[dsNumUniqueMed.name]=dsNumUniqueMed
dfPerColMed[dsNumNullMed.name]=dsNumNullMed
dfPerColMed[dsPercentNullMed.name]=dsPercentNullMed<jupyter_output><empty_output><jupyter_text>Null Values Per RowThis section determines which columns have a null value per each row, resulting in a data series matching the number of unique rows in the dataset. The series values are tuples of column names. The resulting table shows the unique tuples of column names with their corresponding row count. Once the data subsets for the project questions are determined, this will help inform the completeness of those data subsets.<jupyter_code>#Medication dataset -- For a single row, determine which columns have null values
def NullsPerRow(dsRow):  
    columnsNull=list(dsRow[dsRow.isnull()].index)
    columnsNull.sort()
    columnsNull=tuple(columnsNull) #used tuple since lists can't be used by pandas unique()
    return(columnsNull)
dsNulls=dfMed.apply(NullsPerRow,axis=1)

#Create data frame for counting 
dfNulls=pd.DataFrame(dsNulls.unique(),columns=['Columns With Null'])
dfNulls['Number of Rows']=0

#Count the number of rows per each tuple of null columns
for index in dfNulls.index:
    dfNulls.loc[index,'Number of Rows']=len(dsNulls[dsNulls==dfNulls.loc[index,'Columns With Null']])
dfNulls.sort_values(by='Number of Rows',ascending=False,inplace=True)
dfNulls.reset_index(drop=True,inplace=True)

#Add a column for percent of rows
dfNulls['Percent of Rows']=(dfNulls['Number of Rows']/numUniqueRowsMed*100).round(2)
dfNulls<jupyter_output><empty_output><jupyter_text>Visualizations<jupyter_code>### For Medications Dataset Visualization
print(sheetNameMed+' Dataset\n')
print('Original Number of Rows = '+str(numOriginalRowsMed))
print('Duplicate Number of Rows = '+str(numDuplicateRowsMed))
print('Percent Duplicate Rows = '+str(percentDuplicateRowsMed)+'\n')
print('Duplicate rows removed prior to remaining analysis')
print('Unique Number of Rows = '+str(numUniqueRowsMed)+'\n')
dfPerColMed
dfNulls
## Needs to install 1st time only to setup your environment
##!pip install -U pandasql
## You will need to 'pip' install 'pandasql' to import 'sqldf'
from pandasql import sqldf
## For Medication Dataset counting total "duplicate" row for verication to check above count, from cross checking as above's duplicate number.
outputDupMed = sqldf("select count(*) from dfMedDuplicates")
outputDupMed 
## For Procedure Dataset counting total "duplicate" row for verication to check above count, from cross checking as above's duplicate number.
outputDupProc = sqldf("select count(*) from dfProcDuplicates")
outputDupProc

## Checking FRDPersonnelID ='9E5D40DC-5DC6-E411-80C7-001DD8B71D38' for duplication validation that appears in appear in two agencies
outputMed = sqldf("select * from dfMed where FRDPersonnelID='9E5D40DC-5DC6-E411-80C7-001DD8B71D38'")
outputMed<jupyter_output><empty_output><jupyter_text>Query to verify duplicate exists in TABLE - Procedures<jupyter_code>## Similarly from Procedure dataset, Checking FRDPersonnelID ='9E5D40DC-5DC6-E411-80C7-001DD8B71D38' for duplication validation that appears in appear in two agencies
outputProcedure = sqldf("select * from dfProc where FRDPersonnelID='9E5D40DC-5DC6-E411-80C7-001DD8B71D38'")
outputProcedure

## From Procedure dataset, counting duplicates where BY Procedure_Performed_Date_Time,Procedure_Performed_Code, Procedure_Performed_Description, FRDPersonnelID, PatientId are same
output = sqldf("SELECT PatientId, Procedure_Performed_Code,Procedure_Performed_Description, FRDPersonnelID,Procedure_Performed_Date_Time, COUNT(*) FROM dfProc GROUP BY PatientId, Procedure_Performed_Code, Procedure_Performed_Description, FRDPersonnelID, Procedure_Performed_Date_Time HAVING COUNT(*) > 1")
##output['Procedure_Performed_Description']  
output<jupyter_output><empty_output><jupyter_text>List All Rows Containing Duplicates from TABLE - Procedures<jupyter_code>##List All Rows Containing "more than 2" duplicates from  'Procedures' datasets, where  Procedure_Performed_Code,Procedure_Performed_Description,PatientId, FRDPersonnelID, Procedure_Performed_Date_Time matches.
## This query also shows in some cases, same procedure was performed on the same patient by different FRDPersonnel at the same date/time
output2 = sqldf("SELECT a.*FROM dfProc a JOIN (SELECT Procedure_Performed_Date_Time, Procedure_Performed_Description, FRDPersonnelID,PatientId,Procedure_Performed_Code  FROM dfProc GROUP BY Procedure_Performed_Code,Procedure_Performed_Description,PatientId, FRDPersonnelID, Procedure_Performed_Date_Time HAVING count(*) > 2 ) b ON a.Procedure_Performed_Description = b.Procedure_Performed_Description AND a.FRDPersonnelID = b.FRDPersonnelID ORDER BY a.FRDPersonnelID")
output2<jupyter_output><empty_output><jupyter_text>Query to verify duplicate exists for FirePersonnel in TABLE - Patients Goal here is to find ( quick check), if there is same FRDPersonnelID and FRDPersonnelGender with multiple FRDPersonnelStartDate.  Manual validation may be required ( using the excel dataset) to verify this as same FirePersonnel may have multiple start date.<jupyter_code>outputP1 = sqldf("SELECT FRDPersonnelID, FRDPersonnelGender,FRDPersonnelStartDate, COUNT(*) as Total FROM dfPatient GROUP BY FRDPersonnelID,FRDPersonnelGender HAVING COUNT(*) > 1 ORDER BY Total desc")
outputP1<jupyter_output><empty_output><jupyter_text>Checking 'date' formats for confirmity<jupyter_code>##Ignoring an invalid 'DispatchTime' value 
##dfPatient['DispatchTime'] = pd.to_datetime(dfPatient['DispatchTime'], errors='ignore')

##Changing an invalid 'DispatchTime' value to NaN ( Note this method does not throw error but changes empty values to NaN)
dfPatient['DispatchTime'] = pd.to_datetime(dfPatient['DispatchTime'], errors='coerce')
dfPatient<jupyter_output><empty_output><jupyter_text>Finding the count of non-NaN value across the row axisSyntax: DataFrame.count(axis=0, level=None, numeric_only=False)Parameters:axis : 0 or ‘index’ for row-wise, 1 or ‘columns’ for column-wiselevel : If the axis is a MultiIndex (hierarchical), count along a particular level, collapsing into a DataFramenumeric_only : Include only float, int, boolean data<jupyter_code>##find the number of non-NaN/null value across the row axis in 'dfPatient' dataframe
dfPatient.count(axis = 0) 
# Count total NaN at each column in the DataFrame 
print(" \nCount total NaN at each column in a DataFrame : \n\n", dfPatient.isnull().sum())<jupyter_output>Count total NaN at each column in a DataFrame : 

 PatientId                     0
FRDPersonnelID                0
Shift                         0
UnitId                        0
FireStation                   0
Battalion                     0
PatientOutcome                2
PatientGender            131378
CrewMemberRoles               0
DispatchTime                  0
FRDPersonnelGender          474
FRDPersonnelStartDate       474
dtype: int64
{'fraction_non_alphanumeric': 0.08259528964114675, 'fraction_numerical': 0.018697106868726627, 'mean_word_length': 6.531620553359684, 'pattern_counts': {'":': 0, '<': 68, '<?xml version=': 0, '>': 74, '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': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '208466', 'license': 'MIT', 'n_tokens_mistral': 4870, 'n_tokens_neox': 4533, 'n_words': 1376, 'path': 'notebooks/20210307-au-data-quality-assessment.ipynb', 'repo_name': 'data-rescue/ems-analytics', 'size': '15249'}