<jupyter_start><jupyter_text>Classifier of food itemsTakes a CSV file, formats and cleans relevant columns and then applies rules for Good Choice badge for each item in each specified category. Outputs an Excel file with one tab for each PHE category as well as the original file with 'badge status' column appended.  How to use this Colab notebook- Click 'Open in playground' in the top left corner below the menu bar- You'll need to be signed in to a Google account to use this (that is either a Gmail address or Google-linked email, not PHE email)- You'll need to click the play button in each cell which you'll see when you hover your mouse over ```[ ]``` symbol- After clicking the play button for teh first time you'll get a pop-up notification warning that this notebook was not authored by Google - click 'Accept'- The first one may take a couple of seconds before the file upload widget appears- IMPORTANT: Execute each cell in sequential order form top to bottom- Each cell should only take a few seconds to run - if you get any error messages:  - Try clicking the play button on that cell again - Alternatively try clicking 'Reset all runtimes...' inside the 'Runtime' option on the menu bar - If all fails copy and paste the error message (or a screenshot it) and email it to me- Make sure the spreadsheet you're uploading is correctly formatted or else the code won't work - Before processing another spreadsheet:  - Click on 'Reset all runtimes...' inside the 'Runtime' option on the menu bar  - And optionally also on 'Clear all outputs' inside the 'Edit' option on the menu bar Notes on spreadsheet formatting- Make sure you are uploading a ```.csv```,  not ```.xls``` or ```.xlsx``` file. To convert an Excel spreadsheet to CSV simply select the option  'CSV (Comma delimited) (*.csv)' in Excel's 'Save As' dropdown menu.- Make sure the spreadsheet file is a table with:  - Column headings in the first row  - No empty rows - Ideally no unnecessary columns - No comments or notes anywhere on the spreadsheet- We need the following column names (which are case sensitive) on the uploaded CSV file for the code to work. For example, if you have a column 'fat /100g' rename it to 'Fat per 100g'. - **'PHE category'** (See note in below) - **'Product name'** - **'Product ingredients'** - **'Fat per 100g'** - **'Salt per 100g'** - **'Sugar per 100g'** - **'Fibre per 100g'** - **'Fat per 100ml'** - **'Sugar per 100ml'** - **'Saturates per 100g'**- 'PHE category' column: - You will have to identify this column on any given dataset - look for PHE category words 'cheese', 'vegetable', 'cereals', etc. - The data you'll receive will likely have three levels of categories - the second level category is likely to be the 'PHE category' - For example, if level 1 is 'Deli' and level 2 (or sub-category) is 'Cheese' and level 3 is 'Soft cheese' - level 2 is the PHE category  Let's get strated<jupyter_code>#@title Upload your formatted .csv file

# libraries and packages
import pandas as pd
import re
from google.colab import files

# upload widget
file = files.upload()

#@title Check the uploaded file - this is a preview of the first 5 rows

# get the file name of the uploaded file
for name in file.items():
    file_name = name[0]

# try ISO encoding for the CSV
try:
    df = pd.read_csv(
    
    file_name,
    encoding = "ISO-8859-1"
    )

# if ISO doesn't work try utf-8 encoding
except UnicodeDecodeError:
    df = pd.read_csv(
    
    file_name,
    encoding = "utf-8"
    )
        
df.head()<jupyter_output><empty_output><jupyter_text>Check formatting of the uploaded file<jupyter_code>#@title Check that you have all the necessary columns on your file?

# check that lookup columns used in the function are present
lookup_columns = [

    'PHE category',
    'Product name',
    'Product ingredients',
    'Fat per 100g',
    'Salt per 100g',
    'Sugar per 100g',
    'Fibre per 100g',
    'Fat per 100ml',
    'Sugar per 100ml',
    'Saturates per 100g'
]

# list all missing columns if there are any
missing_columns = []
for column in lookup_columns:
    if column not in set(df.columns):
        missing_columns.append(column)
    else:
        pass

# test whether all lookup columns are present
# print a message to the user
if set(lookup_columns).issubset(set(df.columns)) is not True:
    print(
        f'The following columns are missing: {missing_columns}'
        )
else:
    print('Yes, you do have them.')<jupyter_output><empty_output><jupyter_text>IMPORTANT: If you see any missing any columns after running the cell above please check that you have correctly labelled your columns on the uploaded CSV (as per the spreadsheet formatting notes above). If you need to rename columns you can use the widget below to rename them.You can also create missing columns - only if you don't need them for product evaluation - by running the cell below. For example, if you have no drinks on the spreadsheet you may not need 'Sugar per 100ml' column but the evaluation code needs it.<jupyter_code>#@title If you need to rename a column use this widget
rename_from = "" #@param {type:"string"}
rename_to = "" #@param {type:"string"}

# rename columns
df.rename(
    columns={rename_from: rename_to},
    inplace=True
    )

#message to the user
print("Done. Column renamed.")

#@title If you need to create the missing columns use this widget
user_input = "No need to run this cell? Go to the next step" #@param["Create all missing columns with no values in them", "No need to run this cell? Go to the next step"]

if user_input == 'Create all missing columns with no values in them':
    for column in missing_columns:
        df[column] = ''
    print("""
    WARNING: Columns with no values were created.
    This is only to make the rules evaluation code work.
    Are you sure you didn't need them for product evaluation?
    This step did not rename any existing columns if they were misspelled 
    or incorrectly labelled - which is what should have been done 
    prior to uplaoding your CSV file. If you're unsure
    please go back to your CSV and start the whole process over.
    """)
else:
    print('No new columns were created.')

#@title Check that all columns are unique

# check there are no duplicate lookup columns
# and print a message for the user
if len(list(df.columns)) != len(set(df.columns)):
    print("There are duplicate columns on the uploaded spreadsheet.")
else:
    print("All good. No duplicate columns.")

#@title Check that all nutritional columns have numerical values only

# list of columns the function will check against
nutritional_columns = [

    'Fat per 100g',
    'Salt per 100g',
    'Sugar per 100g',
    'Fibre per 100g',
    'Fat per 100ml',
    'Sugar per 100ml',
    'Saturates per 100g'
]

# make sure nutritional columns are numeric data type
# remove 'g' or 'ml' if necessary
for column in nutritional_columns:
    df[column] = pd.to_numeric(            
        df[column].astype(str).str.strip('gml/'),
        errors='coerce')

# print message and dtypes to the user
print("They should be float64 or int64: \n")
for col in nutritional_columns:
    print(f"{col}: {df[col].dtype}")<jupyter_output><empty_output><jupyter_text>Apply rules to file<jupyter_code>#@title Create and preview 'Badge status' column

# helper variables for regex to make the code more readable
hard_cheeses = (
    'cheddar|parmesan|padano|pecorino|edam|gouda|iberico|'
    'parmigiano|manchego|gruyere'
    )
soft_cheeses = 'feta|philadelphia|soft|quark'
blue_cheeses = 'blue|stilton|gorgonzola'
all_cheeses = (
    hard_cheeses + soft_cheeses + blue_cheeses +
    'cottage|mozzarella|spread|string'
    )
cereals = 'cereal|porridge|oats|granola'
drinks = 'drink|beverage|juice|squash|frozen|dairy'
snacks = 'bakery|nuts|seeds|snack'
breads = 'bread|rolls|loaf|teacakes|fruit|bagels|loaf'
nut_exclusions = 'salt|coat|flavour|savoury|sweet|chocolate|spice'


# food item evaluation function applied below in lambda to each row
def badge_finder(row):

    # functions to use within conditions to improve code readabilty
    def present_in_name(string):
        if re.search(string, row['Product name'], re.I) is not None:
                return True
        else:
            return False

    def present_in_category(string):
        if re.search(string, row['PHE category'], re.I) is not None:
            return True
        else:
            return False

    def not_present_in_name(string):
        if re.search(string, row['Product name'], re.I) is None:
            return True
        else:
            return False

    def not_present_in_ingredients(string):
        if re.search(string, row['Product ingredients'], re.I) is None:
            return True
        else:
            return False

    # helper varieables for columns to improve code redability
    sugar = row['Sugar per 100g']
    fat = row['Fat per 100g']
    fibre = row['Fibre per 100g']
    salt = row['Salt per 100g']
    sugar_ml = row['Sugar per 100ml']
    fat_ml = row['Fat per 100ml']
    saturates = row['Saturates per 100g']

    # cheese categories
    if present_in_category('cheese') and fat <= 3:

        if present_in_name(hard_cheeses) and salt <= 2:
            return 'qualifies'

        elif present_in_name(soft_cheeses) and salt <= 0.75:
            return 'qualifies'

        elif present_in_name('cottage') and salt <= 0.63:
            return 'qualifies'

        elif present_in_name('mozzarella') and salt <= 1.5:
            return 'qualifies'

        elif present_in_name(blue_cheeses) and salt <= 2.1:
            return 'qualifies'

        elif present_in_name('spread') and salt <= 2.25:
            return 'qualifies'

        elif present_in_name('string') and salt <= 2:
            return 'qualifies'

        elif present_in_name(all_cheeses) and salt <= 2.25:
            return 'maybe qualifies'

        else:
            return 'does not qualify'

    # cereal categories
    elif present_in_category('cereal') \
            and fibre >= 3 and salt <= 1.125:

        if present_in_name(cereals) and sugar <= 5:
            return 'qualifies'

        elif present_in_name('muesli|müsli') \
                and not_present_in_ingredients('sugar'):
            return 'qualifies'

        else:
            return 'does not qualify'

    # yogurt drink category
    elif present_in_name('yog|kefir') \
            and present_in_name('drink') \
            and fat_ml <= 1.5 and sugar_ml <= 5.4:
        return 'qualifies'

    # yogurt non-drink(ing) category
    elif present_in_name('frais|pot|yog') \
            and fat <= 3 and sugar <= 10.8:
        return 'qualifies'

    # other yogurt categories
    # TBC

    # fruit and veg category
    elif present_in_category('fruit|veg|fresh') \
            and not_present_in_ingredients('salt|sugar|oil|butter'):
        return 'qualifies'

    # pasta and noodles
    elif present_in_category('pasta|noodles') \
            and not_present_in_name('fresh|canned|frozen|filled') \
            and salt <= 0.88:
        return 'qualifies'

    # rice pulses grains - flavoured and seasoned
    elif present_in_category('rice|pulses|grains') \
            and present_in_name('flavour|season') \
            and salt <= 0.63:
        return 'qualifies'

    # rice pulses grains - generic
    elif present_in_category('rice|pulses|grains') \
            and salt <= 0.2:
        return 'qualifies'

    # confectionery and sweets
    elif present_in_category('confectionery|sweet') \
            and fat <= 5 and sugar == 0:
        return 'qualifies'

    # drinks categories
    elif present_in_category(drinks):

        if not_present_in_ingredients('sugar') \
                and sugar_ml <= 2.5:
            return 'qualifies'

        elif present_in_name('water') \
                and sugar_ml <= 0.5:
            return 'qualifies'

        elif present_in_name('ice|pop|lolly') \
                and sugar <= 2.5 and fat <= 17.5 \
                and saturates <= 5 and salt <= 1.5:
            return 'qualifies'

        elif present_in_name('skimmed|1%'):
            return 'qualifies'

        elif present_in_name('milk') \
                and fat_ml <= 1.5 and sugar_ml <= 5.4:
            return 'qualifies'

        elif present_in_name('smoothie') \
                and not_present_in_ingredients('sugar'):
            return 'qualifies'

        else:
            return 'does not qualify'

    # snacks categories
    elif present_in_category(snacks):

        if present_in_name(breads) \
                and sugar <= 22.5 and fat <= 1.5 \
                and fibre >= 3 and salt <= 1.25:
            return 'qualifies'

        elif present_in_name('peanuts|cashews|mix|trail') \
                and not_present_in_name(nut_exclusions):
            return 'qualifies'

        elif present_in_name('hummus|dip|salsa') \
                and present_in_name('plain|reduced|low') \
                and sugar <= 22.5 and fat <= 17.5 \
                and saturates <= 5 and salt <= 1.5:
            return 'qualifies'

        elif present_in_name('bar|crisp') \
                and not_present_in_name('protein') \
                and sugar <= 22.5 and fat <= 17.5 \
                and sataurates <= 5 and salt <= 1.5:
            return 'qualifies'

        elif present_in_name('popcorn') \
                and sugar <= 4 and fat <= 3 and salt <= 0.2:
            return 'qualifies'

        else:
            return 'does not qualify'

    # desserts categories
    elif present_in_category('pudding|dessert'):

        if present_in_name('jelly') and sugar <= 0.5:
            return 'qualifies'

        elif present_in_name('rice|custard') \
                and sugar <= 10.8 and fat <= 3:
            return 'qualifies'

        else:
            return 'does not qualify'

    # everything else
    else:
        return 'does not qualify'

# apply helper function to df
df['Badge status'] = df.apply(lambda row: badge_finder(row), axis=1)

# preview the output
df.head()<jupyter_output><empty_output><jupyter_text>Download resultsNOTE: If you get a 'Failed to fetch' error just press the play button on the cell to execute the code again.<jupyter_code>#@title Save processed .xlsx file to my computer

# fill missing values to avoid KeyError when slicing df
for col in df.columns:
    if df[col].dtype == object:
        df[col].fillna(value='', inplace=True)
    else:
        pass


# function for creating df slices
def df_slice(regex_pattern, column='PHE category'):
    return df[df[column].str.contains(regex_pattern, case=False, regex=True)
    ].sort_values(by='Badge status', ascending=False) 

# dataframe slice for each category to use in spreadsheet tabs
cheese = df_slice('cheese')
cereal = df_slice('cereal')
yogurt = df_slice('yog|frais|kefir', 'Product name')
fruit_veg = df_slice('fruit|veg|fresh')
pasta_grains = df_slice('pasta|rice|pulses|grain')
confectionery = df_slice('confection|sweet')
drinks = df_slice('drink|beverage|juice|squash|frozen|dairy')
snacks = df_slice('bakery|nut|seed|snack')
desserts = df_slice('pudding|dessert')

# get original file name minus extension
spreadsheet_name = re.split('\.', file_name)[0]

# create a spreadsheet tab for each category
with pd.ExcelWriter(f'{spreadsheet_name}_processed.xlsx') as writer:
    
    df.to_excel(
        writer,
        sheet_name='Original plus badge status',
        index=False
    )
    cheese.to_excel(
        writer,
        sheet_name='Cheese',
        index=False,
        columns=[       
            'PHE category',
            'Product name',
            'Fat per 100g',
            'Salt per 100g',
            'Badge status',
            'Product ingredients']
    )
    cereal.to_excel(
        writer,
        sheet_name='Cereal',
        index=False,
        columns=[        
            'PHE category',
            'Product name',
            'Salt per 100g',
            'Sugar per 100g',
            'Fibre per 100g',
            'Badge status',
            'Product ingredients']
    )
    yogurt.to_excel(
        writer,
        sheet_name='Yogurt',
        index=False,
        columns=[        
            'PHE category',
            'Product name',
            'Fat per 100g',
            'Sugar per 100g',
            'Fat per 100ml', 
            'Sugar per 100ml',
            'Badge status',
            'Product ingredients']
    )
    fruit_veg.to_excel(
        writer,
        sheet_name='Fruit and veg',
        index=False,
        columns=[        
            'PHE category',
            'Product name',
            'Badge status',
            'Product ingredients']
    )
    pasta_grains.to_excel(
        writer,
        sheet_name='Pasta and grains',
        index=False,
        columns=[        
            'PHE category',
            'Product name',
            'Salt per 100g',
            'Sugar per 100g',
            'Badge status',
            'Product ingredients']
    )
    confectionery.to_excel(
        writer,
        sheet_name='Confectionery',
        index=False,
        columns=[        
            'PHE category',
            'Product name',
            'Fat per 100g',
            'Sugar per 100g',
            'Badge status',
            'Product ingredients']
    )
    drinks.to_excel(
        writer,
        sheet_name='Drinks',
        index=False,
        columns=[        
            'PHE category',
            'Product name',
            'Fat per 100g',
            'Salt per 100g',
            'Sugar per 100g',
            'Fat per 100ml', 
            'Sugar per 100ml',
            'Saturates per 100g',
            'Badge status',
            'Product ingredients']
    )
    snacks.to_excel(
        writer,
        sheet_name='Snacks',
        index=False,
        columns=[        
            'PHE category',
            'Product name',
            'Fat per 100g',
            'Salt per 100g',
            'Sugar per 100g',
            'Fibre per 100g',
            'Saturates per 100g',
            'Badge status',
            'Product ingredients']
    )
    desserts.to_excel(
        writer,
        sheet_name='Desserts',
        index=False,
        columns=[        
            'PHE category',
            'Product name',
            'Fat per 100g',
            'Sugar per 100g',
            'Badge status',
            'Product ingredients']
    )

# download the Excel file
files.download(f'{spreadsheet_name}_processed.xlsx')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09351196585813841, 'fraction_numerical': 0.015504294743665928, 'mean_word_length': 2.466666666666667, 'pattern_counts': {'":': 0, '<': 65, '<?xml version=': 0, '>': 23, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '627810', 'license': 'MIT', 'n_tokens_mistral': 5809, 'n_tokens_neox': 5327, 'n_words': 1894, 'path': 'badge_classifier.ipynb', 'repo_name': 'janilles/good_choice_badge', 'size': '18511'}