def do_profile(self, database, file_name, skip_rows, n_rows, metadata): printv = self.printv self.db_name = metadata['db_name'] self.last_sumary = None self.column_metadata = pd.DataFrame( columns=['Database-id', 'Column-name', 'Group', 'Key', 'Value']) readable_time_start = TimeUtils.current_time_formated() time_start = time.time() total_rows = len(database.index) total_missing_values = 0 text_cols = [] text_cols_names = [] num_cols = [] num_cols_names = [] geo_cols = [] geo_cols_names = [] temp_cols = [] temp_cols_names = [] null_cols = [] null_cols_names = [] self.gps_cols = [] self.zip_cols = [] self.types_summary = pd.DataFrame(columns=[ 'database-id', 'column-name', 'socrata-type', 'profiler-type', 'profiler-most-detected' ]) str_cols_types_percent = '' str_cols_types_percent_simple = '' printv('\nMetadata Types (Socrata Only): ') metadata_types = {} if metadata is not None and MetadataConstants.TYPES in metadata.keys(): metadata_types = metadata[MetadataConstants.TYPES] for col in metadata_types.keys(): printv(' ' + col + ': ' + metadata_types[col]) SocrataUtils.prepare_location_columns(database, metadata_types) col_names = database.dtypes.keys() printv('\nProfiling {0} columns.'.format(col_names.size)) # print 'col_names=', col_names for i in range(0, col_names.size): # reset col description unique = counts = vmin = vmax = std = mean = length_min = length_max = length_std = length_mean = missing = None col = database[col_names[i]] col_name = col_names[i] App.debug('Profiling column: ', col_name) App.debug('Pandas DType: ', col.dtype) if col_name in metadata_types: metadata_type = metadata_types[col_name] else: metadata_type = '' App.debug('Metadata type: ', metadata_type) unique_data = pd.DataFrame() temp = col.dropna() temp = temp.unique( ) # <= gives this error probably is a pandas error: TypeError: unhashable type: 'dict' unique_data[col_name] = temp unique_data = unique_data[col_name] col_types_percent_detailed = TypeDetector.types_of(unique_data) App.debug('[var] col_types_percent_detailed: ', col_types_percent_detailed) processed_col_name = col_name.replace("'", "`") str_cols_types_percent += "'{0}': {1}\n".format( processed_col_name, col_types_percent_detailed) col_types_percent = TypeDetector.simplify( col_types_percent_detailed) printv('{0}: {1}'.format(col_name, col_types_percent)) str_cols_types_percent_simple += "'{0}': {1}\n".format( processed_col_name, col_types_percent) data_type = TypeDetector.simple_type_of_considering_all( col_types_percent_detailed, metadata_type, col_name) most_detected, precision = TypeDetector.most_detected( col_types_percent_detailed) valid_col_values = TypeDetector.valid_values_of_type( most_detected, col) ########## Geographic ########## if data_type is TypeDetector.GEO: printv( "Processing Column {0}: {1} - Geo".format( i + 1, col_names[i]), str(i + 1)) # printv(' Processing Count', '.') count = col.count() missing = total_rows - count total_missing_values += missing # printv(' Processing Value Counts', '.') value_counts = valid_col_values.value_counts() top = value_counts.first_valid_index() freq = 0 if top is not None: freq = str(value_counts[top]) # improve readability if is zip code if type(top) is not str and isinstance(top, int): top = '%i' % top unique = len(value_counts) geo_cols_names.append(col_name) geo_cols.append({ 'Count': count, 'Missing': missing, 'Unique Values': unique, 'Most Frequent': top, 'Top Frequency': freq, 'Min': col.min(), 'Max': col.max(), 'Types Percent': str(col_types_percent_detailed) }) if most_detected == TypeDetector.GEO_GPS: self.gps_cols.append(col_name) # Save column metadata # Only makes sense to save these numeric descriptors if is a lat, lon if most_detected == TypeDetector.GEO_GPS_LATLON: self.add_column_metadata(col_name, 'Type Details', 'min', vmin) self.add_column_metadata(col_name, 'Type Details', 'max', vmax) self.add_column_metadata(col_name, 'Type Details', 'std', std) self.add_column_metadata(col_name, 'Type Details', 'mean', mean) ########## Numeric ########## elif data_type is TypeDetector.NUMERIC: printv( "Processing Column {0}: {1} - Numeric".format( i + 1, col_names[i]), str(i + 1)) # printv(' Get valid Numeric gps_data', '.') col_data_numeric = TypeDetector.get_numeric_data(col) printv(' Processing Count', '.') count = col_data_numeric.count() missing = total_rows - count total_missing_values += missing if count > 0: # printv(' Processing Mean', '.') mean = np.mean(col_data_numeric) # printv(' Processing Std', '.') std = np.std(col_data_numeric) # printv(' Processing Min', '.') vmin = np.min(col_data_numeric) # printv(' Processing Max', '.') vmax = np.max(col_data_numeric) # printv(' Processing Unique', '.') unique = col_data_numeric.nunique() value_counts = col_data_numeric.value_counts() top = value_counts.keys()[0] freq = value_counts[top] # Histogram Data, default bins=10 num_bins = min([Profiler.MAX_BINS, unique]) hist_counts, bins = np.histogram(col_data_numeric, bins=num_bins) hist = pd.Series(hist_counts, index=bins[:-1]) hist_json = hist.to_json() else: printv(' All NaN values') hist_json = mean = std = vmin = vmax = unique = freq = None num_cols_names.append(col_name) num_cols.append({ 'Count': count, 'Missing': missing, 'Mean': mean, 'Std': std, 'Min': vmin, 'Max': vmax, 'Unique Values': unique, 'Most Frequent': top, 'Top Frequency': freq, 'Types Percent': str(col_types_percent_detailed), 'Histogram Data JSON': hist_json, }) # Save column metadata self.add_column_metadata(col_name, 'Type Details', 'min', vmin) self.add_column_metadata(col_name, 'Type Details', 'max', vmax) self.add_column_metadata(col_name, 'Type Details', 'std', std) self.add_column_metadata(col_name, 'Type Details', 'mean', mean) self.add_column_metadata(col_name, 'Type Details', 'Histogram Data JSON', hist_json) ########## Temporal ########## elif data_type is TypeDetector.TEMPORAL: # data_type is PandasUtils.Temporal: printv( "Processing Column {0}: {1} - Temporal".format( i + 1, col_names[i]), str(i + 1)) # printv(' Processing Info', '.') info = col.astype(str).describe() temp_cols_names.append(col_name) printv(' Processing Count', '.') count = info['count'] missing = total_rows - count total_missing_values += missing # printv(' Processing Lenght', '.') lenghts = col.str.len() len_min = lenghts.min() len_max = lenghts.max() len_mean = lenghts.mean() len_std = lenghts.std() top = None if 'top' in info.keys(): top = info['top'].__repr__().strip() freq = None if 'freq' in info.keys(): freq = info['freq'] # printv(' Processing Min', '.') vmin = col.min() # printv(' Processing Max', '.') vmax = col.max() unique = info['unique'] value_counts = valid_col_values.value_counts() temp_cols.append({ 'Count': count, 'Missing': missing, 'Unique Values': unique, 'Most Frequent': top, 'Top Frequency': freq, 'Min': vmin, 'Max': vmax, # 'Lenght Min': '{0:.0f}'.format(len_min), # 'Lenght Max': '{0:.0f}'.format(len_max), # 'Lenght Mean': '{0:.2f}'.format(len_mean), # 'Lenght Std': '{0:.2f}'.format(len_std), 'Types Percent': str(col_types_percent) }) # Save column metadata self.add_column_metadata(col_name, 'Type Details', 'min', vmin) self.add_column_metadata(col_name, 'Type Details', 'max', vmax) self.add_column_metadata(col_name, 'Type Details', 'std', std) self.add_column_metadata(col_name, 'Type Details', 'mean', mean) # ########## Textual ########## elif data_type is TypeDetector.TEXTUAL: printv( "Processing Column {0}: {1} - Text".format( i + 1, col_names[i]), str(i + 1)) #printv(' Processing Info', '.') info = col.astype(str).describe() text_cols_names.append(col_name) #printv(' Processing Count', '.') count = info['count'] missing = total_rows - count total_missing_values += missing #printv(' Processing Lenght', '.') lenghts = col.astype(str).str.len() length_min = lenghts.min() length_max = lenghts.max() length_mean = lenghts.mean() length_std = lenghts.std() App.debug('Counting words...') word_counts = col.astype(str).apply(lambda x: len(x.split()) if x is not None else 0) word_count_min = word_counts.min() word_count_max = word_counts.max() word_count_std = word_counts.std() word_count_mean = word_counts.mean() top = None if 'top' in info.keys(): top = info['top'].__repr__().strip() freq = None if 'freq' in info.keys(): freq = info['freq'] unique = info['unique'] text_cols.append({ 'Count': count, 'Missing': missing, 'Unique Values': unique, 'Most Frequent': top, 'Top Frequency': freq, 'Lenght Min': '{0:.0f}'.format(length_min), 'Lenght Max': '{0:.0f}'.format(length_max), 'Lenght Mean': '{0:.2f}'.format(length_mean), 'Lenght Std': '{0:.2f}'.format(length_std), 'Word Count Min': word_count_min, 'Word Count Max': word_count_max, 'Word Count Std': word_count_std, 'Word Count Mean': word_count_mean, 'Types Percent': str(col_types_percent_detailed) }) # Save Column Metadata self.add_column_metadata(col_name, 'Type Details', 'length-min', length_min) self.add_column_metadata(col_name, 'Type Details', 'length-max', length_max) self.add_column_metadata(col_name, 'Type Details', 'length-std', length_std) self.add_column_metadata(col_name, 'Type Details', 'length-mean', length_mean) self.add_column_metadata(col_name, 'Type Details', 'words-min', word_count_min) self.add_column_metadata(col_name, 'Type Details', 'words-max', word_count_max) self.add_column_metadata(col_name, 'Type Details', 'words-mean', word_count_mean) self.add_column_metadata(col_name, 'Type Details', 'words-std', word_count_std) value_counts = valid_col_values.value_counts() else: # data_type is TypeDetector.NULL: printv( "Processing Column {0}: {1} - {2}".format( i + 1, col_names[i], data_type), str(i + 1)) # printv(' Processing Info', '.') info = col.astype(str).describe() null_cols_names.append(col_name) # printv(' Processing Count', '.') count = info['count'] missing = len(col) - col.count() total_missing_values += missing # printv(' Processing Lenght', '.') lenghts = col.astype(str).apply(lambda x: len(x)) length_min = lenghts.min() length_max = lenghts.max() length_mean = lenghts.mean() length_std = lenghts.std() top = None if 'top' in info.keys(): top = info['top'].__repr__().strip() freq = None if 'freq' in info.keys(): freq = info['freq'] unique = info['unique'] null_cols.append({ 'Count': count, 'Missing': missing, 'Unique Values': unique, 'Most Frequent': top, 'Top Frequency': freq, 'Lenght Min': '{0:.0f}'.format(length_min), 'Lenght Max': '{0:.0f}'.format(length_max), 'Lenght Mean': '{0:.2f}'.format(length_mean), 'Lenght Std': '{0:.2f}'.format(length_std), 'Types Percent': str(col_types_percent_detailed) }) # Save Column Metadata self.add_column_metadata(col_name, 'Type Details', 'length-min', length_min) self.add_column_metadata(col_name, 'Type Details', 'length-max', length_max) self.add_column_metadata(col_name, 'Type Details', 'length-std', length_std) self.add_column_metadata(col_name, 'Type Details', 'length-mean', length_mean) # print 'valid_col_values[:10]=', valid_col_values[:10] # if len(valid_col_values) > 0: value_counts = valid_col_values.value_counts() # else: # value_counts = {} # #Add column info column_data = { 'database-id': self.db_name, 'column-name': col_name, 'socrata-type': metadata_type, 'profiler-type': data_type, 'profiler-most-detected_%': precision, 'profiler-most-detected': most_detected, 'unique': unique, 'missing': missing, 'values': count, } # General self.add_column_metadata(col_name, 'General', 'top-value', top) self.add_column_metadata(col_name, 'General', 'top-freq', freq) # self.add_column_metadata(col_name, 'General', 'profiler-most-detected_%', precision) # self.add_column_metadata(col_name, 'General', 'profiler-most-detected', most_detected) # Add column index to column metadata if ProfilerUtils.COLUMN_INDEXES in metadata and col_name in metadata[ ProfilerUtils.COLUMN_INDEXES]: self.add_column_metadata( col_name, 'General', 'index', metadata[ProfilerUtils.COLUMN_INDEXES][col_name]) if value_counts is not None: top_k = {} unique_values = len(value_counts) limit = min(Profiler.MAX_TOP_K, unique_values) if self.part: limit = unique_values for k in value_counts.keys()[:limit]: # top_k[str(k)] = value_counts[k] top_k[str(k)] = value_counts[k] # TODO: Fix a bug here self.add_column_metadata(col_name, 'Type Details', 'top-k', top_k) # Simple type info for k in col_types_percent.keys(): self.add_column_metadata(col_name, 'Simple Type', k, col_types_percent[k]) # Complete type info for k in col_types_percent_detailed.keys(): self.add_column_metadata(col_name, 'Detailed Type', k, col_types_percent_detailed[k]) self.types_summary = self.types_summary.append(column_data, ignore_index=True) # ============================================================================== # ============================ SUMMARYZE gps_data ================================== # ============================================================================== # Sumary DataFrames self.numeric_DataFrame = pd.DataFrame(num_cols, index=num_cols_names) self.geo_DataFrame = pd.DataFrame(geo_cols, index=geo_cols_names) self.textual_DataFrame = pd.DataFrame(text_cols, index=text_cols_names) self.null_DataFrame = pd.DataFrame(null_cols, index=null_cols_names) self.temporal_DataFrame = pd.DataFrame(temp_cols, index=temp_cols_names) time_end = time.time() readable_time_end = TimeUtils.current_time_formated() # Database Sumary total_values = total_rows * col_names.size missing_percent = (total_missing_values * 100.0) / (total_values) missing_percent = '{0:.2f}'.format(missing_percent) processing_time = '{0:.2f}'.format(time_end - time_start) printv('\n\n=============== DATABASE SUMARY ===============') printv('File: {0}'.format(self.db_name)) printv('Rows: {0:n}'.format(total_rows)) printv('Columns: {0:n}'.format(col_names.size)) printv(' - Geo: {0:n}'.format(len(geo_cols_names))) printv(' - Temporal: {0:n}'.format(len(temp_cols_names))) printv(' - Numeric: {0:n}'.format(len(num_cols_names))) printv(' - Textual: {0:n}'.format(len(text_cols_names))) printv('Values') printv(' - Total: {0:n} (Rows x Columns)'.format(total_values)) printv(' - Missing: {0:n} ({1}%)'.format(total_missing_values, missing_percent)) printv('Processing time: {0} sec'.format(processing_time)) used_memory = resource.getrusage(resource.RUSAGE_SELF).ru_maxrss / 1000 null_cols = col_names.size - len(num_cols_names) - len( geo_cols_names) - len(temp_cols_names) - len(text_cols_names) self.last_sumary = pd.DataFrame( [{ 'Name': self.db_name, 'Rows': total_rows, 'Columns': col_names.size, 'Columns Numeric': len(num_cols_names), 'Columns Temporal': len(temp_cols_names), 'Columns Geo': len(geo_cols_names), 'Columns Text': len(text_cols_names), 'Columns Null': len(null_cols_names), 'Column Names Numeric': str(num_cols_names), 'Column Names Geo': str(geo_cols_names), 'Column Names Text': str(text_cols_names), 'Column Names Temporal': str(temp_cols_names), 'Values': total_values, 'Values Missing': total_missing_values, 'Values Missing Percent': missing_percent, 'ETL-Profiler Processing Time (sec)': processing_time, 'ETL-Profiler Time Begin': readable_time_start, 'ETL-Profiler Time End': readable_time_end, 'ETL-Profiler Input File': file_name, 'ETL-Profiler Input File Size (KB)': metadata['file_size'], 'ETL-Profiler Total Memory (MB)': used_memory, Profiler.STATUS: Profiler.STATUS_SUCCESS }], columns=Profiler.SUMARY_COLUMNS, index=[self.db_name]) if self.ignore_metadata: printv('=============== Metadata Ignored ===============') elif metadata is not None and MetadataUtils.has_success(metadata): try: printv('=============== PROVIDED METADATA ===============') for key in metadata.keys(): if key == MetadataConstants.TYPES: continue #Ignore Provided Types in dataset metadata value = metadata[key] App.debug(key, '=', value, ' - type:', type(value)) self.last_sumary[key] = value.__str__() self.last_sumary[MetadataConstants. PRIMARY] = MetadataUtils.is_primary(metadata) printv('') except (UnicodeEncodeError) as ex: #do nothing printv('UnicodeEncodeError with socrata metadata.') if self.stop_on_error: raise ex # ============================================================================== # ============================ Process Geo gps_data ==================================== # ============================================================================== if self.ignore_index: printv('Ignoring geo-temp index') else: printv('Processing Geographic gps_data') self.generate_index(database) # ============================================================================== # ============================ SHOW RESULTS ==================================== # ============================================================================== if self.show_details: if self.show_all_columns: numeric_info_to_show = self.numeric_DataFrame.columns.tolist() text_info_to_show = self.textual_DataFrame.columns.tolist() if 'Value Counts' in text_info_to_show: text_info_to_show.remove('Value Counts') geo_info_to_show = self.geo_DataFrame.columns.tolist() if 'Value Counts' in geo_info_to_show: geo_info_to_show.remove('Value Counts') temporal_info_to_show = self.temporal_DataFrame.columns.tolist( ) if 'Value Counts' in temporal_info_to_show: temporal_info_to_show.remove('Value Counts') else: numeric_info_to_show = Profiler.NUMERIC_INFO_SMALL text_info_to_show = Profiler.TEXT_INFO_SMALL geo_info_to_show = Profiler.GEO_INFO_SMALL temporal_info_to_show = Profiler.TEMPORAL_INFO_SMALL null_info_to_show = Profiler.TEMPORAL_INFO_SMALL if len(geo_cols) > 0: print '\n=============== Geo gps_data Sumary:' # print self.geo_DataFrame[geo_info_to_show] # for count, row in self.geo_DataFrame[geo_info_to_show].iterrows(): # print '>>>>>>>>>>>>>>>>>>>>', row print self.geo_DataFrame[geo_info_to_show] if len(num_cols) > 0: print '=============== Numeric Sumary:' print self.numeric_DataFrame[numeric_info_to_show] if len(temp_cols) > 0: print '\n=============== Temporal Sumary:' print self.temporal_DataFrame[temporal_info_to_show] if len(text_cols) > 0: print '\n=============== Textual Sumary:' print self.textual_DataFrame[text_info_to_show] if len(null_cols_names) > 0: print '\n=============== Null Sumary:' print self.null_DataFrame[null_info_to_show] printv( '\n========================================================= Types Information:' ) printv(' --- Complete ---') printv(str_cols_types_percent.rstrip('\n')) printv( '\n ------------------- Types Summary ------------------- ' ) printv(self.types_summary) printv( '===============================================================' ) printv(self.last_sumary.T) # printv('\n ------------------- Column Metadata ------------------- ') # printv( '===============================================================') # printv (self.column_metadata) printv( '===============================================================') # ============================================================================== # ================================ SAVE FILES ================================== # ============================================================================== if self.save_output_files: print 'Generated Files:' # self.save_dataframe_to_csv(self.numeric_DataFrame, '_profiled_numeric') # self.save_dataframe_to_csv(self.geo_DataFrame, '_profiled_geo') # self.save_dataframe_to_csv(self.textual_DataFrame, '_profiled_textual') # self.save_last_sumary_to_json() # filename = self.to_folder + self.db_name + '_types.json' # self.types_summary.to_csv() if self.last_zip_rows is None: print ' No Zip file to save.' else: filename = self.to_folder + self.db_name + '_zip.csv' self.last_zip_rows.to_csv(filename) print ' ' + filename if self.last_gps_rows is None: print ' No GPS file to save.' else:
def test_SSN_valid_example_218099999_without_dashes(self): values = pandas.Series(['218099999']) detected = TypeDetector.valid_values_of_type(TypeDetector.SSN, values) self.assertEquals(len(detected), 1)
def test_phone_valid_example_national_2(self): values = pandas.Series(['541 754 3010']) detected = TypeDetector.valid_values_of_type(TypeDetector.PHONE, values) self.assertEquals(len(detected), 1)
def test_SSN_valid_example_218_09_9999(self): values = pandas.Series(['218-09-9999']) detected = TypeDetector.valid_values_of_type(TypeDetector.SSN, values) self.assertEquals(len(detected), 1)
def test_SSN_invalid_examples_all_0s_in_each_part(self): values = pandas.Series(['000-09-9999', '218-00-9999', '218-09-0000']) detected = TypeDetector.valid_values_of_type(TypeDetector.SSN, values) self.assertEquals(len(detected), 0)
def test_SSN_invalid_examples(self): values = pandas.Series(['219-09-9999', '078-05-1120']) detected = TypeDetector.valid_values_of_type(TypeDetector.SSN, values) self.assertEquals(len(detected), 0)
def test_SSN_invalid_if_less_than_9_numbers(self): values = pandas.Series(['1', '12345678', '123-45-678']) detected = TypeDetector.valid_values_of_type(TypeDetector.SSN, values) self.assertEquals(len(detected), 0)
def clean_invalid_values(type, col_name, index): valid_values = TypeDetector.valid_values_of_type(type, index[col_name]).unique() index = index[index[col_name].astype(str).apply(lambda x: x in valid_values)]