<jupyter_start><jupyter_text>Part 4 of creating auto-populate feature Save it all to put in a database Load and Prep the Data<jupyter_code>import csv
import pandas as pd
df = pd.read_csv('allrecipes_recipes_combined.csv')
df.head()<jupyter_output><empty_output><jupyter_text>Some of the recipe names have '&reg;' as text and not the symbol.<jupyter_code>df['name'] = df['name'].str.replace('&reg;', '')<jupyter_output><empty_output><jupyter_text>The Ingredients Data has been saved as a string. Convert to list.<jupyter_code>import ast

def string_to_list(x):
    return ast.literal_eval(x)
df['ingredients'] = df['ingredients'].apply(string_to_list)
df.head()<jupyter_output><empty_output><jupyter_text>Need to change vulgar fractions (single character fractions) into long-form strings. ('½' to '1/2')<jupyter_code># Dictionary to map unicode fractions to expanded strings. 
# These are all of the vulgar fraction options. (Aside from one with a zero numerator.)
fraction_dict = {'½': '1/2',
                 '⅓': '1/3',
                 '⅔': '2/3',
                 '¼': '1/4',
                 '¾': '3/4',
                 '⅕': '1/5',
                 '⅖': '2/5',
                 '⅗': '3/5',
                 '⅘': '4/5',
                 '⅙': '1/6',
                 '⅚': '5/6',
                 '⅐': '1/7',
                 '⅛': '1/8',
                 '⅜': '3/8',
                 '⅝': '5/8',
                 '⅞': '7/8',
                 '⅑': '1/9',
                 '⅒': '1/10'}
def fraction_mapper(x):
    for key in fraction_dict:
        for i in range(len(x)):
            if key in x[i]:
                x[i] = x[i].replace(key, fraction_dict[key])
                
    return(x)
df['ingredients'] = df['ingredients'].apply(fraction_mapper)
df.head()
df['ingredients'][0]<jupyter_output><empty_output><jupyter_text>Remove ingredients that only appear once<jupyter_code>from collections import Counter
ingredient_counter = Counter()

# Count each instance of each ingredient
for i in range(len(df)):
    for j in range(len(df['ingredients'][i])):
        ingredient = df['ingredients'][i][j]
        ingredient_counter.update({ingredient: 1})
# Get the ingredients that only appear once
single_ing= []
for ing, num in ingredient_counter.items():
    if num == 1:
        single_ing.append(ing)
# Number of ingredients that only appear once in the 70k recipes.
# These are likely incredibly specific entries.
len(single_ing)
import datetime
# Get rid of the single-time ingredients counting backwards in each list
# so as to not go out of index range after removing one
for i in range(len(df)):
    for j in range(len(df['ingredients'][i])-1, -1, -1):
        if df['ingredients'][i][j] in single_ing:
            ingredient = df['ingredients'][i][j]
            # Remove from the ingredients
            df['ingredients'][i].remove(ingredient)
            # Remove from list to not slow down loop
            single_ing.remove(ingredient)
    if i % 2000 == 0:
        print(i, datetime.datetime.now())<jupyter_output>0 2020-05-15 13:51:19.720658
2000 2020-05-15 13:52:16.488010
4000 2020-05-15 13:53:15.251607
6000 2020-05-15 13:54:09.084974
8000 2020-05-15 13:55:02.154044
10000 2020-05-15 13:55:52.996995
12000 2020-05-15 13:56:42.864223
14000 2020-05-15 13:57:31.489191
16000 2020-05-15 13:58:21.119231
18000 2020-05-15 13:59:04.871275
20000 2020-05-15 13:59:46.476517
22000 2020-05-15 14:00:26.478474
24000 2020-05-15 14:01:01.240330
26000 2020-05-15 14:01:29.043022
28000 2020-05-15 14:01:53.159571
30000 2020-05-15 14:02:17.789336
32000 2020-05-15 14:02:43.058782
34000 2020-05-15 14:03:09.679638
36000 2020-05-15 14:03:33.603553
38000 2020-05-15 14:03:50.559672
40000 2020-05-15 14:04:06.437071
42000 2020-05-15 14:04:18.752220
44000 2020-05-15 14:04:29.583879
46000 2020-05-15 14:04:39.332232
48000 2020-05-15 14:04:48.053117
50000 2020-05-15 14:04:56.049660
52000 2020-05-15 14:05:05.211611
54000 2020-05-15 14:05:10.672331
56000 2020-05-15 14:05:15.278275
58000 2020-05-15 14:05:19.918911
60000 2020-05-15 1[...]<jupyter_text>Some recipes have an unneeded number of ingredients. I'm limiting recipes to 30<jupyter_code>ingredients_len = []
for i in range(len(df)):
    ingredients_len.append(len(df['ingredients'][i]))

max(ingredients_len)
ingredients_len.index(56)
indices = [i for i, x in enumerate(ingredients_len) if x > 30]
indices
for i in indices:
    print(df.iloc[i])
    print('='*30)
for i in indices:
    print(df['ingredients'][i])
indices[::-1]
for i in indices[::-1]:
    print(i)
for i in indices[::-1]:
    df = df.drop(i, axis=0)
df = df.reset_index(drop=True)
ingredients_len = []
for i in range(len(df)):
    ingredients_len.append(len(df['ingredients'][i]))

max(ingredients_len)
indices = [i for i, x in enumerate(ingredients_len) if x > 30]
indices<jupyter_output><empty_output><jupyter_text>Put the ingredients into a dictionary that contains values of measurement quantity, measurement unit, and ingredient ('1/4', 'cup', 'butter, softened').<jupyter_code># These are measurement units from another notebook.
measurement_units = [
 'packages', 'package', 'slices', 'sliced', 'slice', 
 'bags', 'bag', 'bars', 'bar', 'bottles', 'bottle', 'boxes' 'box', 'bulbs', 'bulb', 'bunches', 'bunch', 
 'cans', 'can', 'containers', 'container', 'cubes', 'cube', 'cups', 'cup', 
 'dashes', 'dash', 'drops', 'drop',
 'envelopes', 'envelope',
 'fillets', 'fillet',
 'gallons', 'gallon', 'granules', 'granule',
 'halfes', 'half', 'heads', 'head',
 'jars', 'jar',
 'layers', 'layer', 'leaf', 'leaves', 'legs', 'leg', 'links', 'link', 'loaf', 'loaves',
 'ounces', 'ounce',
 'packets', 'packet', 'pieces', 'piece', 'pinches', 'pinch', 'pints', 'pint', 'pounds', 'pound',
 'quarts', 'quart',
 'sprigs', 'sprig', 'squares', 'square', 'stalks', 'stalk', 'strips', 'strip',
 'tablespoons', 'tablespoon','teaspoons', 'teaspoon', 'thighs', 'thigh', 'trays', 'tray']
import re
def ingred_dict(x):
    '''
    This function is meant to take in a list of ingredients for a recipe.
    It then parses out the ingredients and saves the quantity of an ingredient,
    the unit of measurement for that ingredient, and the name of the ingredient.
    This information is then saved in a dictionary and returned.
    '''
    my_dict = {}  # Dictionary for the current recipe
    pattern = re.compile(r'^[\d/\s]+')  # Include white space to catch compound fractions

    for i in range(len(x)):
        matches = pattern.finditer(x[i])
        ingredient_test = x[i]

        for match in matches:

            quantity = match.group(0).strip()  # Quantity of measurement set

            ingredient_test = ingredient_test.strip(quantity)  # Save everything after removing quantity

            check = 0
            breaker = False

            pattern_2 = re.compile(r'^[(\d\s]+')  # Check for any numbers in parenthesis
            matches_2 = pattern_2.finditer(ingredient_test)

            for unit in measurement_units:
                if matches_2:  # If there's a match for a number in parenthesis
                    matches_2 = False  # Don't check this conditional again
                    continue  # Skip this unit of measurement
                elif unit in ingredient_test:
                    ingredient = ingredient_test.split(unit)[1].strip()  # Ingredient set
                    units = (ingredient_test.split(unit)[0] + unit).strip()  # Unit set (including any parenthesis before)
                    check = 1  # Set check to 1 so the last conditional doesn't execute
                    breaker = True
                if breaker == True:
                    break
            if check == 0:  # If no unit measurement is found (like the ingredient is "1 egg")
                ingredient = ingredient_test.strip()
                units = None
            
            ingred_num = f'ingredient{i+1}'
            # Save ingredient information as a list
            my_dict[ingred_num] = [quantity, units, ingredient]

    return my_dict
df['ingredient_dict'] = df['ingredients'].apply(ingred_dict)
df.head()
df['ingredient_dict'][0]
len(df['ingredient_dict'][0])
ing = 'ingredient1'
df['ingredient_dict'][0][ing]
for j in range(len(df['ingredient_dict'][0])):
    print(df['ingredient_dict'][0][f'ingredient{j+1}'])<jupyter_output>['21', None, 'chocolate sandwich cookies, crushed']
['1/4', 'cup', 'butter, softened']
['1', 'cup', 'heavy cream']
['1', '(12 ounce) package', 'semisweet chocolate chips']
['1', 'teaspoon', 'vanilla extract']
['1', 'pinch', 'salt']
['2', 'cups', 'heavy cream']
['1/4', 'cup', 'white sugar']
['1', 'cup', 'heavy cream, chilled']
['1/4', 'cup', 'white sugar']<jupyter_text>Prepare data to save to CSV for database<jupyter_code>col_list = ['name']
for i in range(max(ingredients_len)):
    col_list.append(f'ingredient{i+1}')

df_csv = pd.DataFrame(columns=col_list)

df_csv.head()
for i in range(len(df)):
    new_dict = {'name': df['name'][i]}
    for j in range(len(df['ingredient_dict'][i])):
        try:
            new_dict[f'ingredient{j+1}'] = df['ingredient_dict'][i][f'ingredient{j+1}']
        except:
            continue
    df_csv = df_csv.append(new_dict, ignore_index=True)
    
    if i % 2000 == 0:
        print(i, datetime.datetime.now())
df_csv.head()
type(df_csv['name'][0])<jupyter_output><empty_output><jupyter_text>Save to CSV<jupyter_code>df_csv.to_csv('recipes_table_v2.csv', index=False, na_rep='')
df_csv.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 70881 entries, 0 to 70880
Data columns (total 31 columns):
 #   Column        Non-Null Count  Dtype 
---  ------        --------------  ----- 
 0   name          70881 non-null  object
 1   ingredient1   67286 non-null  object
 2   ingredient2   67835 non-null  object
 3   ingredient3   65070 non-null  object
 4   ingredient4   60441 non-null  object
 5   ingredient5   53979 non-null  object
 6   ingredient6   46660 non-null  object
 7   ingredient7   39072 non-null  object
 8   ingredient8   31828 non-null  object
 9   ingredient9   24985 non-null  object
 10  ingredient10  18892 non-null  object
 11  ingredient11  13849 non-null  object
 12  ingredient12  9875 non-null   object
 13  ingredient13  6884 non-null   object
 14  ingredient14  4750 non-null   object
 15  ingredient15  3180 non-null   object
 16  ingredient16  2103 non-null   object
 17  ingredient17  1354 non-null   object
 18  ingredient18  857 non-null    object
 19  ingr[...]<jupyter_text>Now I need a new way of retrieving the appropriate ingredients<jupyter_code>df_csv.iloc[0]
pd.notnull(df_csv.iloc[0])
pd.notnull(df_csv.iloc[0])[1]
df_csv.columns[1]
df_csv.iloc[0][1]
# I can make a dictionary for the ingredients, but then what?
ingredient_dict = {}
for i in range(1, len(df_csv.iloc[0])):
    if pd.notnull(df_csv.iloc[0])[i]:
        ingredient_dict[df_csv.columns[i]] = df_csv.iloc[0][i]
        '''
        Instead of creating a dictionary here to store the ingredients for each returned recipe
        the ingredient counting could happen which would free up some processing and move more
        quickly through the whole loop.
        '''

ingredient_dict
# Make a df to store results
results_df = pd.DataFrame(columns=['ingredients'])
results_df
# Add the ingredient_dict to this new df
results_df = results_df.append({'ingredients' :ingredient_dict}, ignore_index=True)
results_df
results_df['ingredients'][0]<jupyter_output><empty_output><jupyter_text>This appears to work and will make it so much of the code below will be useable with minor modifications. It's only a matter of working out the query for the words entered and matching those with recipe names in the database.<jupyter_code>##################################################################################################################################
# To use for this error: InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block #
##################################################################################################################################

# cursor = conn.cursor()

# cursor.execute("""rollback;
# """)

# cursor.close()
%%capture
pip install psycopg2
# Connect to database.

import os
import psycopg2

conn = psycopg2.connect(database ='postgres', user = 'postgres', password = '<PASSWORD>', host = 'mydishdb-dev.c3und8sjo4p2.us-east-2.rds.amazonaws.com', port = '5432')
# String comes in from frontend. Split the string into words. 

string = 'chicken noodle soup'
split_words = string.split()
cursor = conn.cursor()

command = """SELECT name 
                FROM recipes 
;
          """

cursor.execute(command)

name_table = cursor.fetchall()

cursor.close()

name_table[0]
cursor = conn.cursor()

command = """SELECT index, name 
                FROM recipes 
;
          """

cursor.execute(command)

test_table = cursor.fetchall()

cursor.close()

test_table[0]
cursor = conn.cursor()

command = """SELECT index, name 
                FROM recipes
                WHERE index in (0, 2, 8)
;
          """

cursor.execute(command)

test = cursor.fetchall()

cursor.close()

test
test[0][1]
type(test[0][1])
# Then query the recipe database to get recipe names that have matching words.

cursor = conn.cursor()

command = """SELECT name 
                FROM recipes 
                WHERE name ILIKE '%chicken%' AND
                    name ILIKE '%noodle%' AND
                    name ILIKE '%soup%'
          ;"""

cursor.execute(command)

table = cursor.fetchall()

cursor.close()

table

cursor = conn.cursor()

command = """SELECT * 
                FROM recipes 
                WHERE name ILIKE '%chicken%' AND
                    name ILIKE '%noodle%' AND
                    name ILIKE '%soup%'
          ;"""

cursor.execute(command)

recipe_table = cursor.fetchall()

cursor.close()

recipe_table[0]

type(recipe_table)
type(recipe_table[0])
len(recipe_table[0])
len(recipe_table)
for i in range(len(recipe_table)):
    print(i, recipe_table[i][1])
string_to_list(recipe_table[0][2])
string_to_list(recipe_table[0][2])[2]
# Count instances of each ingredient to find most common.

# Initialize a Counter for tabulating how often each ingredient occurs
ingredient_counts = Counter()

# Count each instance of each ingredient
for i in range(len(recipe_table)):
    for j in range(2, len(recipe_table[i])):
        if recipe_table[i][j]:
            ingredient = string_to_list(recipe_table[i][j])[2]
            ingredient_counts.update({ingredient: 1})

ingredient_counts
# Loop through most common to save quantity and measurement to get most common of those.

# Get the top 30 ingredients sorted by most common
top_30 = sorted(ingredient_counts.items(), key=lambda x: x[1], reverse=True)[:30]

# Get the ingredients that occured in at least 25% of recipes returned
above_25_percent = [(tup[0], round(100*tup[1]/len(recipe_table), 1)) for tup in top_30 if 100*tup[1]/len(recipe_table) >= 25]

above_25_percent
for item in above_25_percent:
    print(item[0])
# for i in range(len(recipe_table)):
        
#     for j in range(2, len(recipe_table[i])):
        
#         if recipe_table[i][j]:
            
#             print(string_to_list(recipe_table[i][j])[2])
# Create dictionary of information. Turn into dictionary (then JSON) and return.
results_list = []

# Get the ingredient information and put it in a dictionary
for item in above_25_percent:
    quantity_list = []
    unit_list = []

    for i in range(len(recipe_table)):
        
        for j in range(2, len(recipe_table[i])):

            if recipe_table[i][j]:
                if string_to_list(recipe_table[i][j])[2] == item[0]:
                    #print(recipe_table[i][j])

                    quantity = string_to_list(recipe_table[i][j])[0]
                    unit = string_to_list(recipe_table[i][j])[1]

                    quantity_list.append(quantity)
                    unit_list.append(unit)
                    
#                     print(quantity)

    # Getting and saving the most common quantity and unit for each ingredient
    data = Counter(quantity_list)
    quantity = data.most_common(1)
    data = Counter(unit_list)
    unit = data.most_common(1)

#     print(quantity)
    ingred_dict = {'quantity': quantity[0][0], 'unit': unit[0][0], 'ingredient': item[0]}

    results_list.append(ingred_dict)

results_list
conn.close()<jupyter_output><empty_output><jupyter_text>Put It All Together<jupyter_code>%%capture
pip install psycopg2
import ast
import psycopg2
from collections import Counter
def string_to_list(x):
    return ast.literal_eval(x)


def ingredient_getter(word):
    
    results_list = []
    split_words = word.split()
    
    conn = psycopg2.connect(database ='postgres', user = 'postgres', password = '<PASSWORD>', host = 'mydishdb-dev.c3und8sjo4p2.us-east-2.rds.amazonaws.com', port = '5432')
    cursor = conn.cursor()

    command = f"SELECT * FROM recipes WHERE name ILIKE '%{split_words[0]}%' "
    if len(split_words) > 1:
        for i in range(1, len(split_words)):
            command += f"AND name ILIKE '%{split_words[i]}%' "
    command += ";"

    cursor.execute(command)

    recipe_table = cursor.fetchall()

    cursor.close()
    conn.close()
    
    # Initialize a Counter for tabulating how often each ingredient occurs
    ingredient_counts = Counter()

    # Count each instance of each ingredient
    for i in range(len(recipe_table)):
        for j in range(2, len(recipe_table[i])):
            if recipe_table[i][j]:
                ingredient = string_to_list(recipe_table[i][j])[2]
                ingredient_counts.update({ingredient: 1})
    
    # Get the top 30 ingredients sorted by most common
    top_30 = sorted(ingredient_counts.items(), key=lambda x: x[1], reverse=True)[:30]

    # Get the ingredients that occured in at least 25% of recipes returned
    above_25_percent = [(tup[0], round(100*tup[1]/len(recipe_table), 1)) for tup in top_30 if 100*tup[1]/len(recipe_table) >= 25]

    # Get the ingredient information and put it in a dictionary
    for item in above_25_percent:
        quantity_list = []
        unit_list = []

        for i in range(len(recipe_table)):

            for j in range(2, len(recipe_table[i])):

                if recipe_table[i][j]:
                    if string_to_list(recipe_table[i][j])[2] == item[0]:

                        quantity = string_to_list(recipe_table[i][j])[0]
                        unit = string_to_list(recipe_table[i][j])[1]

                        quantity_list.append(quantity)
                        unit_list.append(unit)


        # Getting and saving the most common quantity and unit for each ingredient
        data = Counter(quantity_list)
        quantity = data.most_common(1)
        data = Counter(unit_list)
        unit = data.most_common(1)

        ingred_dict = {'quantity': quantity[0][0], 'unit': unit[0][0], 'ingredient': item[0]}

        results_list.append(ingred_dict)

    return results_list
    
ingredient_getter('brownies')
import time
start_time = time.time()
ingredient_getter('brownies')
print("--- %s seconds ---" % (time.time() - start_time))<jupyter_output>--- 1.2188889980316162 seconds ---
{'fraction_non_alphanumeric': 0.14167275002607155, 'fraction_numerical': 0.06225883825216394, 'mean_word_length': 3.18298800436205, 'pattern_counts': {'":': 0, '<': 48, '<?xml version=': 0, '>': 53, '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': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '426356', 'license': 'MIT', 'n_tokens_mistral': 7157, 'n_tokens_neox': 6310, 'n_words': 1693, 'path': 'Dishify/notebooks/ingredient_populater/4_allrecipes_data_to_csv_and_db_queries.ipynb', 'repo_name': 'NealWhitlock/MyDish-DS', 'size': '19198'}