<jupyter_start><jupyter_text>Problem       : Irregular Casing Solution       : Converting all the text to Upper case  Justification: Easy to Comprehend<jupyter_code>#Checking the first record of the csv file before performing upper case
food[0]
#Converting all the string data to upper case for better Visuals
for row in food:
        row['DBA Name']=row['DBA Name'].upper()
        
        row['AKA Name']=row['AKA Name'].upper()
        
        row['Facility Type']=row['Facility Type'].upper()
        
        row['Risk']=row['Risk'].upper()
        
        row['Address']=row['Address'].upper()
        
        row['City']=row['City'].upper()
        
        row['State']=row['State'].upper()
        
        row['Zip']=row['Zip'].upper()
        
        row['Inspection Type']=row['Inspection Type'].upper()
        
        row['Results']=row['Results'].upper()
        
        row['Violations']=row['Violations'].upper()
# Re-checking the first record of the file after converting to upper case 
food[0]<jupyter_output><empty_output><jupyter_text>Problem: Encountered NULL Value in the data set  Solution: Assigning values based on the type of entry Justification : To get a clean and complete data set<jupyter_code># Checking the count for NUll in AKA Name
Counter([row['AKA Name'] for row in food if row['AKA Name'] == ''])
# Above result shows that AKA Name has NULL value so replacing all of them with the corresponding DBA Name, as-
# most of the entries in AKA Name is same as the corresponding entries in DBA Name.
# Therefore, the Null values in AKA Name has been replaced with the-
# corresponding DBA Name
for row in food:
    if row['AKA Name'] == '':
        row['AKA Name']+=row['DBA Name']
# Re-checking the count after replacing all the AKA Names with DBA Name
Counter([row['AKA Name'] for row in food if row['AKA Name'] == ''])
#Checking if state has any NULL value 
Counter([row['State'] for row in food if row['State'] == ''])
#Above result shows that State has NULL values 
#Thus, replacing blank states with 'IL', since 'IL' is the state code for the entire data set
for row in food:
      if row['State'] == '' :
            row['State'] = 'IL'
#Re-checking the count for state after performing the above opertion 
Counter([row['State'] for row in food if row['State'] == ''])
#Checking if Risk has any NULL value
Counter([row['Risk'] for row in food if row['Risk'] == ''])
#Above result shows that Risk has NULL values so replacing all the NULL values with 'NA', to get a clean and complete data
for row in food:
    if row['Risk'] == '' :
        row['Risk'] = 'NA'
#Re-checking the count after replacing all the Risk with NA
Counter([row['Risk'] for row in food if row['Risk'] == ''])
#Checking if Violations has NULL value
Counter([row['Violations'] for row in food if row['Violations'] == ''])
#Above Result shows that Violations has NULL value so replacing it with NA to get a clean and complete data
for row in food:
    if row['Violations'] == '' :
        row['Violations'] = 'NA'
#Re-checking the count after replacing all the Violations with NA
Counter([row['Violations'] for row in food if row['Violations'] == ''])
#Checking if Inspection Type has NULL value 
Counter([row['Inspection Type'] for row in food if row['Inspection Type'] == ''])
#Above Result shows that Inspection Type has NULL value so replacing it with NA to get a clean and complete data
for row in food:
    if row['Inspection Type'] == '' :
        row['Inspection Type'] = 'NA'
#Re-checking the Inspection Type for Inspection ID(1946612)and confirming if the Inspection Type is replaced with 'NA'
Counter([row['Inspection Type'] for row in food if row['Inspection Type'] == ''])
#Checking the count in City column which are NULL
Counter((row['City']) for row in food if row['City']== '')
#Above Result shows that City has NULL value so replacing it with NA to get a clean and complete data
for row in food:
      if row['City'] == '' :
            row['City'] = 'NA'
#Re-Checking the count in City column 
Counter((row['City']) for row in food if row['City']== '')<jupyter_output><empty_output><jupyter_text>Problem: Incorrect spelling,unwanted paranthesis,backslash and single inverted quote  Solution: Using rstrip, lstrip and replace  function to rectify the above problem Justification: To get  a consistent, relevant and efficient data from the data set whenever necessary<jupyter_code>#Checking for incorrect data
Counter(row['Facility Type'] for row in food)
#Checking the length of incorrect data
len(Counter(row['Facility Type'] for row in food))<jupyter_output><empty_output><jupyter_text>Removing unwanted paranthesis,backslash and single inverted quote by using rstrip, lstrip and replace  function<jupyter_code>#Rectifying the above problem.
for row in food:
    if row['Facility Type'] == 'DAY CARE 1023':
        continue
        
    elif row['Facility Type'].startswith('1023') and len(row['Facility Type'])>4:
        row['Facility Type'] = "'1023 CHILDREN'S SERVICES FACILITY'"#spelling mistake  
        
    elif row['Facility Type'] == '(CONVENIENCE STORE)' or row['Facility Type'] == '(GAS STATION)':
        row['Facility Type'] = row['Facility Type'].lstrip('(')#unwanted paranthesis
        row['Facility Type'] = row['Facility Type'].rstrip(')')#unwanted paranthesis
        
    elif row['Facility Type'] == 'ASSISSTED LIVING': 
        row['Facility Type'] = 'ASSISTED LIVING'#spelling mistake
        
    elif row['Facility Type'] == 'BANQUET ROOMS' or row['Facility Type'] == 'BANQUETS': 
        row['Facility Type'] = row['Facility Type'].rstrip('S')#Extra Letter
        
    elif row['Facility Type'] == 'CHARTER SCHOOL/CAFETERIA': 
        row['Facility Type'] = row['Facility Type'].replace('/',' ')#Unwanted backslash
        
    elif row['Facility Type'] == 'CHILDERN ACTIVITY FACILITY':
        row['Facility Type'] = row['Facility Type'].replace('CHILDERN','CHILDREN')#spelling mistake
        
    elif row['Facility Type'].startswith("CHILDERN'S") or row['Facility Type'].startswith("CHILDRENS"):
        row['Facility Type'] = "CHILDREN'S SERVICES FACILITY"#spelling mistake
        
    elif row['Facility Type'].startswith('CHURCH/SPECIAL'):
        row['Facility Type'] = 'CHURCH(SPECIAL EVENTS)'#unwanted backslash and missing data
        
    elif row['Facility Type'] == 'COMMIASARY':
        row['Facility Type'] = row['Facility Type'].replace('COMMIASARY','COMMISARY')##spelling mistake
        
    elif row['Facility Type'] == 'CONVINIENT STORE':
        row['Facility Type'] = 'CONVINIENCE STORE'#spelling mistake
        
    elif row['Facility Type'] == 'CONVNIENCE STORE':
        row['Facility Type'] = 'CONVINIENCE STORE'#spelling mistake
        
    elif row['Facility Type'].startswith('FROZEN DESSERTS'):
        row['Facility Type'] = 'FROZEN DESSERTS DISPENSER-NON-MOTORIZED'#spelling mistake
        
    elif row['Facility Type'] == 'GAS STATION/STORE':
        row['Facility Type'] = row['Facility Type'].replace('/',' ')#unwanted backslash
        
    elif row['Facility Type'] == 'HERBALIFE':
        row['Facility Type'] = 'HERBAL LIFE'#spelling mistake
        
    elif row['Facility Type'] == 'HERBALIFE STORE':
        row['Facility Type'] = 'HERBAL LIFE STORE'#spelling mistake
        
    elif row['Facility Type'] == "KIDS CAFE'":   
        row['Facility Type'] = row['Facility Type'].replace("'",'')#unwanted single inverted quote
        
    elif row['Facility Type'] == 'MOBILE DESSERTS VENDOR':
        row['Facility Type'] ='MOBILE DESSERT VENDOR'#spelling mistake
        
    elif row['Facility Type'].startswith('MOBILE FROZEN') and 'DISP' in row['Facility Type']: 
        row['Facility Type'] ='MOBILE FROZEN DESSERTS DISPENSER-NON-MOTORIZED'##spelling mistake
        
    elif row['Facility Type'].endswith('TOPS') or row['Facility Type'] == 'ROOFTOP':
        row['Facility Type'] = 'ROOF TOP'#spelling mistake
        
    elif 'TREM)' in row['Facility Type']:
        row['Facility Type'] = 'SHARED KITCHEN USER LONG TERM'#spelling mistake
        
    elif row['Facility Type'] == 'TAVERN/LIQUOR':
        row['Facility Type'] =row['Facility Type'].replace('/','-')#unwanted backslash
        
    elif row['Facility Type'] == 'THEATRE':
        row['Facility Type'] = row['Facility Type'].replace('THEATER','THEATRE')##spelling mistake
        
    elif row['Facility Type'] == 'WRIGLEY ROOFTOP':
        row['Facility Type'] = 'WRIGLEY ROOF TOP'#spelling mistake
##Re-Checking for incorrect data
Counter([row['Facility Type'] for row in food])
#Re-checking the length after fixing the incorrect data
len(Counter([row['Facility Type'] for row in food]))
#Checking all the cities which starts with CH
Counter([row['City'] for row in food if 'CH' in row['City']])
#The above result shows that there are lot of spelling mistakes for the city CHICAGO. 
#Thus, fixing all the incorrect city name with correct spelling 'CHICAGO'
for row in food:
    if row['City'] == 'CHICAGO.' or row['City'] == 'CHICAGOI':
        row['City'] = 'CHICAGO'
    if row['City'].endswith('CAGO') and row['City']!= 'CHICAGO HEIGHTS' and row['City']!= '312CHICAGO':
        row['City'] = "CHICAGO"
#Re-checking the count of all cities after fixing the above mentioned mistakes
Counter([row['City'] for row in food if 'CH' in row['City']])<jupyter_output><empty_output><jupyter_text>Problem : Extra Space and un-organised data Solution : Used Split function to fix the above problem Justification: To ensure that the data is organised<jupyter_code># Checking the first record of Violation Column 
food[0]['Violations']
# The above result displays the reasons for Violations, in the form of paragraph which-
# makes it difficult to read data and has lot of space between comments 
# Thus, using split function to organise the data
for row in food:
    row['Violations'] = row['Violations'].split('| ')
# Re-checking the first record of Violation Column after performing split function-
# which after exploring becomes easier to read as they are categorized numerically
food[0]['Violations']<jupyter_output><empty_output><jupyter_text>Problem: Redundant data in the data set Solution: Using del function to rectify the above problem Justification: An average person does not require Latitude and Longitude to find address -  in daily life +  Location column itself contains latitude and longtitude which if required can be fetched from location column<jupyter_code>#Checking the first record of the csv file which consists of Latitude and  Longtitude
food[0]
# Deleting Latitude and  Longtitude as location contains data of both column
for row in food:
    del row['Latitude']
    del row['Longitude']
#Re-checking the first record of the csv file after performing deletion
food[0]<jupyter_output><empty_output><jupyter_text>Problem: No fixed format used Solution: zfill is used to bring the Zip and License columns in the standard format based on the maximum length Justification: To fetch a structured data and maintain uniformity<jupyter_code># Checking the Maximum length of License
max_length = max([len(row['License #']) for row in food])
print(max_length)
#Checking length of all the records in License column 
Counter(len(row['License #']) for row in food)
# Converting all the License field data whose length is less than the maximum length
# Thus,replacing irregular License Length using zfill in order to maintain uniformity,
# as zfill replaces all the null values with 0 by default
for row in food:
    if row['License #']== '':
        row['License #'] = row['License #'].zfill(7)
    elif (len(row['License #']) < max_length):
        row['License #'] = row['License #'].zfill(7)
#Re-checking length of all the records in License column after using Zfill function
Counter(len(row['License #']) for row in food)
# Checking the Maximum length of Zip
max_length = max([len(row['Zip']) for row in food])
print(max_length)
# Checking the length of all records of Zip  
Counter(len(row['Zip']) for row in food)
# The above result shows that Zip has Null Values and the maximum length of Zip is 5.
# Thus,replacing all the blank value with 0 in order to maintain uniformity.
# Using zfill to replace all the values with max_length,
# as zfill replaces all the null values with 0 by default
for row in food:
    if row['Zip']=='':
        row['Zip'] = row['Zip'].zfill(max_length)
#Re-Checking the length of all records of Zip   
Counter(len(row['Zip']) for row in food)
#writing the cleaned csv file
with open('Food-Inspections-Cleaned.csv',"w",newline = '') as food_write:
    data = csv.DictWriter(food_write,list(food[0].keys()))
    data.writeheader()
    data.writerows(food)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12044685705294626, 'fraction_numerical': 0.0028321925890960587, 'mean_word_length': 4.07667731629393, 'pattern_counts': {'":': 1, '<': 30, '<?xml version=': 0, '>': 30, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '313234', 'license': 'NCSA', 'n_tokens_mistral': 3871, 'n_tokens_neox': 3716, 'n_words': 1396, 'path': 'food_inspections.ipynb', 'repo_name': 'renukasachani/Food_Inspections', 'size': '12711'}