Satyaswarup/profiling
0
1import streamlit as st2import pandas as pd3import openai4import itertools5from presidio_analyzer import AnalyzerEngine , PatternRecognizer6from presidio_analyzer import AnalyzerEngine7import os8from dateutil.parser import parse9import datetime10import re11import isbnlib12import pycountry13import pycountry14from geopy.geocoders import Nominatim15import phonenumbers16import pycountry17import ast18from phonenumbers.phonenumberutil import (19 region_code_for_country_code,20 region_code_for_number,21)22org_name = os.environ["org_name"]23api_key = os.environ["api_key"]24openai.organization = org_name25#openai.api_key = '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'26#openai.api_key='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'27openai.api_key= api_key28 29def main():30 st.title('Column Profiling Analysis')31 32 # Upload the Dataset 33 file_upload = st.sidebar.file_uploader("Upload your input CSV file", type=["csv"])34 35 if file_upload is not None:36 try:37 data = pd.read_csv(file_upload, encoding='utf-8')38 except UnicodeDecodeError:39 file_upload.seek(0) # Reset the file pointer, so we can read the file again40 data = pd.read_csv(file_upload, encoding='ISO-8859-1')41 #t.write('**Data Snapshot:**')42 # Show the top 5 dataset43 if st.button('data sample snapshot'):44 st.write(data.head(5))45 # Show the buttom 5 dataset46 #f st.button('Show buttom 5 data'):47 # st.write(data.tail(5))48 # Show column names49 if st.button('Show column names'):50 st.write(data.columns)51 #primary key52 if st.button('primary Key identify'):53 def is_datetime_column(data, column):54 return pd.api.types.is_datetime64_any_dtype(data[column])55 def is_float_column(data, column):56 return pd.api.types.is_float_dtype(data[column])57 def find_primary_key_columns(data):58 primary_key_columns = []59 for column in data.columns:60 if is_datetime_column(data, column) or is_float_column(data, column) or data[column].nunique() != len(data):61 continue62 primary_key_columns.append(column)63 print("datetime col check complete")64 # Check if any predefined constraints exist in the column names or data types65 for column in data.columns:66 if 'unique' in column.lower() or 'not null' in column.lower() or 'primary key' in column.lower():67 primary_key_columns.append(column)68 # Exclude boolean columns69 boolean_columns = data.select_dtypes(include='boolean').columns70 primary_key_columns = [col for col in primary_key_columns if col not in boolean_columns]71 print(primary_key_columns)72 return primary_key_columns73 primary_key_columns = find_primary_key_columns(data)74 st.write(primary_key_columns)75 76 #show PII data77 if st.button('PII Validation'):78 analyzer=AnalyzerEngine()79 PII={}80 pii_col_list=[]81 for column in data.columns:82 for entry in data[column]:83 analysis_results=analyzer.analyze(text=str(entry),entities=["PERSON"],language='en')84 if analysis_results:85 pii_col_list.append(column)86 break87 PII['PII_Columns']=pii_col_list88 st.write(PII)89 90 # Show dimensions91 if st.button('Show dimensions'):92 st.write(f'Number of rows: {data.shape[0]}')93 st.write(f'Number of columns: {data.shape[1]}')94 95 if st.button('Date types'):96 datatype={}97 col_list=data.columns.values.tolist()98 for col in col_list:99 if data[col].dtype=="object":100 if data[col].isin([0,1]).all():101 #print("{}: {}".format(col,'Binary'))102 datatype.update({col:"Binary"})103 elif data[col].isin(['Yes','No']).all():104 #print("{}: {}".format(col,'Boolean'))105 datatype.update({col:"Boolean"})106 elif data[col].isin(['True','False']).all():107 #print("{}: {}".format(col,'Boolean'))108 datatype.update({col:"Boolean"})109 else:110 #print("{} : {} ".format(col,"string"))111 datatype.update({col:"string"})112 elif data[col].dtype=='int64':113 if data[col].isin([0,1]).all():114 #print("{}: {}".format(col,'Binary'))115 datatype.update({col:"Binary"})116 elif 'date' in col.lower() or '_DT' in col or 'dt' in col.lower() or 'dat' in col.lower():117 #print("{}: {}".format(col,'Datetime'))118 datatype.update({col:"Datetime"})119 else:120 #print("{}: {}".format(col,df[col].dtype))121 datatype.update({col:data[col].dtype})122 else:123 #print("{}: {}".format(col,df[col].dtype))124 datatype.update({col:data[col].dtype})125 st.write(datatype)126 127 # Show summary128 if st.button('Show data stats summary'):129 st.write(data.describe())130 #quantile info131 if st.button('Quantile info'):132 x=data.quantile([.1, .25, .5, .75], axis = 0)133 st.write(x)134 #Boolean data in dataset and stats135 if st.button('boolean data check'):136 boolean_columns = []137 stats = {}138 for column in data.columns:139 unique_values = data[column].unique()140 # Check if the column has only 2 unique values141 if len(unique_values) == 2:142 boolean_columns.append(column)143 value_counts = data[column].value_counts().to_dict()144 stats[column] = value_counts145 st.write('Boolean fields with counts')146 st.write(stats)147 #Anomaly / outlier detection148 if st.button('Anomaly / Outlier column detection'):149 outlier_df=[]150 col_list=data.columns.values.tolist()151 for col in col_list:152 if data[col].dtype=="int64" or data[col].dtype=='float64':153 #print("outlier check for {}".format(col))154 q1=data[col].quantile(0.25)155 q3=data[col].quantile(0.75)156 IQR=q3-q1157 fence_low = q1-1.5*IQR158 fence_high = q3+1.5*IQR159 df_out = data.loc[(data[col] < fence_low) & (data[col] > fence_high)]160 if len(df_out) > 0:161 outlier_df.append({col:df_out})162 if len(outlier_df) > 0:163 st.write(outlier_df)164 else:165 st.write("no outlier found in dataset")166 167 #negetive count check 168 if st.button('Negetive count check in cols'):169 negetive_counts={}170 col_list=data.columns.values.tolist()171 for col in col_list:172 if data[col].dtype=="int64" or data[col].dtype=='float64':173 neg_count=data[col].lt(0).sum()174 if neg_count > 0:175 #print("{} having negetive values {}".format(col,neg_count))176 negetive_counts.update({"negetive counts "+ col:neg_count})177 if len(negetive_counts)==0:178 negetive_counts.update({"negetive counts": 0})179 #negetive_counts_info=json.dumps(negetive_counts, default=str)180 st.write(negetive_counts)181 #check consecutive columns 182 if st.button('Columns with consecutive number'):183 numerics = ['int16', 'int32', 'int64', 'float16', 'float32', 'float64']184 numeric_df = data.select_dtypes(include=numerics)185 x=numeric_df.diff().dropna().eq(1).all()186 st.write(x)187 #serial date check188 if st.button('Columns with date in sequence'):189 col_list=data.columns.values.tolist()190 serial_date={}191 for col in col_list:192 #print(col.lower())193 if 'date' in col.lower() or '_DT' in col or 'dt' in col.lower() or 'dat' in col.lower():194 #print("date field {}".format(col))195 data[col] = pd.to_datetime(data[col])196 if data[col].is_monotonic_increasing:197 #print(" date column follow sequence:{}".format(col))198 serial_date.update({"date column in sequence":col})199 #serial_date_info=json.dumps(serial_date, default=str)200 if len(serial_date)==0:201 st.write('No cols with sequence dates')202 else:203 st.write(serial_date)204 #columns with special char205 if st.button('Columns with special chars'):206 pattern = r'[^\a-zA-Z0-9\s\.]'207 col_list=data.columns.values.tolist()208 special_char_column={}209 # this pattern matches any character that is not a word or whitespace character210 for col in col_list:211 contains_special_chars = data[col].astype(str).str.contains(pattern, regex=True).any()212 if contains_special_chars:213 #print("column with special chars: {}".format(col))214 special_char_column.update({"column with special char": col})215 #special_char_info=json.dumps(special_char_column, default=str)216 if len(special_char_column)==0:217 st.write('No columns with special chars')218 else:219 st.write(special_char_column)220 #Columns with noisy values221 if st.button('columns with noisy values'):222 #Columns with Noise values N/A, DONOTUSE, NODATA, NOAPPLICABLE223 col_list=data.columns.values.tolist()224 noisy_column={}225 noisy_list=['N/A','DONOTUSE','DO NOT USE','NODATA','NO DATA','NOAPPLICABLE','NOT APPLICABLE','NAN','NaN']226 noisy_list1=[x.lower() for x in noisy_list]227 for col in col_list:228 if data[col].dtype=="object":229 if data[col].str.lower().isin(noisy_list1).any():230 #print("col with noisy values.{}".format(col))231 noisy_column.update({"nosiy column": col})232 if len(noisy_column) == 0:233 noisy_column.update({"nosiy columns in file ": 0})234 st.write(noisy_column)235 #check system generated or user input date236 if st.button('check system generated date / user input date columns'):237 regex=re.compile("^\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}$")238 def check_date_format(date):239 match = re.match(regex, date)240 if (match):241 return True242 else:243 return False244 def is_date(string, fuzzy=False):245 try:246 parse(string, fuzzy=fuzzy)247 return True248 except ValueError:249 return False250 col_list=data.columns.values.tolist()251 system_generated_date={}252 user_input_date={}253 for col in col_list:254 if data[col].dtype=="object":255 x=data[col][0]256 timestamp=str(x)257 if is_date(timestamp):258 if check_date_format(timestamp):259 system_generated_date.update({"system generated datetime":col})260 else:261 user_input_date.update({"user generated datetime":col})262 else:263 continue264 else:265 timestamp=data[col][0]266 timestamp=str(timestamp)267 if data[col].dtype=="int64" and len(str(timestamp))==10:268 dt_object = datetime.datetime.fromtimestamp(int(timestamp))269 output_date=dt_object.strftime("%Y-%m-%d")270 if int(output_date[0:4]) >= 1970 and int(output_date[0:4]) <=2099:271 system_generated_date.update({"system generated datetime":col})272 else:273 if is_date(timestamp):274 user_input_date.update({"user generated datetime":col})275 st.write('system generated date:',system_generated_date)276 st.write('user input date:',user_input_date)277 #overload or multiuse columns , different data types 278 if st.button('Overload / multi use columns'):279 overload_col=[]280 def is_mixed_column(data, column):281 column_values = data[column].astype(str)282 return any(column_values.str.isdigit()) and any(~column_values.str.isdigit())283 col_list=col_list=data.columns.values.tolist()284 for column in col_list:285 mixed_col=is_mixed_column(data,column)286 if mixed_col:287 overload_col.append(column)288 #if len(set(column.apply(type))) > 1:289 #if len(set(data[column].apply(type))) > 1:290 # overload_col.append(column)291 if len(overload_col)==0:292 st.write('no overload or multiuse columns')293 else:294 st.write(overload_col)295 296 #ISBN validation 297 if st.button('ISBN column check'):298 col_list=data.columns.values.tolist()299 isbn_col={}300 for col in col_list:301 isbn=data[col][0]302 isbn=str(isbn)303 if isbnlib.is_isbn10(isbn) or isbnlib.is_isbn13(isbn):304 isbn_col['isbn_col']=col305 st.write(isbn_col) 306 #business context of column307 if st.button('show business context of columns'):308 col_list=data.columns.values.tolist()309 business_context={}310 messages = [ {"role": "system", "content":311 "You are a intelligent assistant."} ]312 for i in col_list:313 prompt = "what is the business meaning of databse column" + i + "in 50 words or less"314 model = "text-davinci-002"315 response = openai.Completion.create(316 engine=model,317 prompt=prompt,318 max_tokens=50319 )320 reply = response.choices[0].text.replace("\n", "")321 business_context[i]=reply322 st.write(business_context)323 #check country name324 if st.button('country name check in column'):325 col_list=data.columns.values.tolist()326 country_col={}327 for col in col_list:328 try:329 field=data[col][0]330 country = pycountry.countries.lookup(field)331 if country:332 country_col['country_col']=col333 except LookupError:334 country_col['country_col']="No country column"335 st.write(country_col)336 #city name check337 if st.button('city name check in columns'):338 col_list=data.columns.values.tolist()339 city_col=[]340 geolocator = Nominatim(user_agent="my-custom-application")341 for col in col_list:342 field=data[col][0]343 location = geolocator.geocode(field, exactly_one=True, timeout=10)344 if location is not None and location.raw.get('type') == 'city':345 city_cols.append(col)346 st.write(city_col)347 #phone number check348 if st.button('columns with phone number'):349 col_list=data.columns.values.tolist()350 phone_number_col={}351 for col in col_list:352 field=data[col][0]353 field=str(field)354 try:355 phone_number = phonenumbers.parse(field, None)356 if phonenumbers.is_valid_number(phone_number):357 phone_number_col['phone_no_col']=col358 except phonenumbers.phonenumberutil.NumberParseException:359 continue360 st.write(phone_number_col)361 #hyperlink check362 if st.button('columns with hypyerlink / email'):363 hyperlink_email_cols={}364 hyperlink_email_col_list=[]365 col_list=data.columns.values.tolist()366 link_pattern = r'https?://\S+|www\.\S+'367 email_pattern = r'\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}\b'368 # check if each cell in the 'my_column' column contains a hyperlink or email369 for col in col_list:370 is_link = data[col].astype(str).str.contains(link_pattern, flags=re.IGNORECASE, regex=True)371 is_email = data[col].astype(str).str.contains(email_pattern, flags=re.IGNORECASE, regex=True)372 # print the rows that contain hyperlinks or emails373 if is_email.any() or is_link.any():374 hyperlink_email_col_list.append(col)375 hyperlink_email_cols['hyperlink_email_cols']=hyperlink_email_col_list376 if len(hyperlink_email_cols)==0:377 st.write('no columns with hyperlink or emails')378 else:379 st.write(hyperlink_email_cols)380 381 382 383if __name__ == '__main__':384 main()