<jupyter_start><jupyter_text>Clean Allerhande Export This file describes how we cleaned the raw export from the Allerhande site and added columns with features<jupyter_code>import pandas as pd
import ast
import re
import numpy as np
from sklearn.preprocessing import MultiLabelBinarizer
df = pd.read_csv('./data/allerhande_raw.csv')
df = df.drop(['Unnamed: 0'], axis=1)
df = df.drop_duplicates(subset=['id']) #drop duplicates
df["ingredients"] =  df["ingredients"].map(lambda d : ast.literal_eval(d))
df["tags"] =  df["tags"].map(lambda d : ast.literal_eval(d))<jupyter_output><empty_output><jupyter_text>Cleaning<jupyter_code>df = df[df.id != 429220] #this particular recipe contains many nan values<jupyter_output><empty_output><jupyter_text>Ingredients Turn dictionary of ingredients and their quantities into a plain list of all the ingredients<jupyter_code>ing = []
for idx, row in df['ingredients'].iteritems():
    x = {k.lower(): v for k, v in row.items()}
    x = {k.replace('\s+', '_'): v for k, v in row.items()}
    x = {k.replace('(', ''): v for k, v in row.items()}
    x = {k.replace(')', ''): v for k, v in row.items()}
    x = {k.replace('_tomeato_gehakt', 'tomeato_gehakt'): v for k, v in row.items()}
    k = list(x.keys())
    #k = x.keys()
    ing.append(k)
df['ingredient_list'] = ing
#df['ingredient_list'] = df['ingredient_list'].apply(lambda x: sorted(x))

df = df.drop('ingredients', axis=1)<jupyter_output><empty_output><jupyter_text>Nutritional information Standardize nutritional info<jupyter_code>nutritional = ['calories', 'protein', 'carbohydrates', 'fat', 'saturated_fat', 'sodium', 'fiber']
df[nutritional] = df[nutritional].fillna(-1) #replace NAN values with -1

nutritional_g = ['carbohydrates', 'fat', 'saturated_fat','fiber','protein']

df[nutritional_g] = df[nutritional_g].replace('g', '', regex=True) #remove g notation

df['sodium'] = df['sodium'].str.replace(r'mg', '') #remove mg notation
df['calories'] = df['calories'].str.replace(r'kcal', '') #remove kcal notation

df[['fat','saturated_fat','fiber']] = df[['fat','saturated_fat','fiber']].replace(',', '.', regex=True)
df['sodium'] = df['sodium'].str.replace(r'.', '')

df[nutritional] = df[nutritional].apply(pd.to_numeric)
df['sodium'] = df['sodium'] / 1000 #convert from mg to g

#rename columns to include quantities
df.rename(columns={'protein': 'protein (g)', 'carbohydrates': 'carbohydrates (g)',
                   'fat': 'fat (g)', 'saturated_fat': 'saturated_fat (g)', 
                   'fiber': 'fiber (g)', 'sodium': 'sodium (g)', 'calories': 'calories (kcal)'}, inplace=True)<jupyter_output><empty_output><jupyter_text>Cooking Time<jupyter_code># change cooking time to minutes
df['cooking_time'] = df['cooking_time'].str.replace(r' bereiden', '')
df['minutes'] = df['cooking_time'].str.extract(r'(\d{1,2} min)', expand=False)
df['minutes'] = df['minutes'].str.replace(r' min', '').str.strip()
df['minutes'] = pd.to_numeric(df['minutes'])
df['minutes'].fillna(0, inplace=True)
df['hours'] = df['cooking_time'].str.replace(r'(\b\d{1,2} (min.))', '')
df['hours'] = df['hours'].str.replace(r'uur', '')
df['hours'] = df['hours'].str.replace(r'u.', '')
df['hours'] = df['hours'].str.replace(r'wachten', '')
df['hours'] = df['hours'].str.strip()
df['hours'] = pd.to_numeric(df['hours'])
df['hours'] = df['hours'] * 60
df['hours'].fillna(0, inplace=True)
df['cooking_time'] = df['minutes'] + df['hours']
drop_columns = ['minutes','hours']
df = df.drop(drop_columns, axis=1)<jupyter_output><empty_output><jupyter_text>Ingredients Import list of ingredients extracted from website and manually cleaned. Remove stopwords, export list of ingredients,and pivot to dummy table.<jupyter_code>columns = ['id', 'raw', 'ingredient_singular', 'ingredient_plural', 'quantity', 'empty','unit', 'additional information']
df_ingredients = pd.read_csv("./data/ingredients_full_clean.csv", names = columns)
df_ingredients = df_ingredients.drop('empty', axis=1)
df_ingredients['value'] = 1
stop = ['(versgemalen)', '(groene)', '(versgeraspte)', '(vierseizoenen)', 'verse', 'Provençaalse']
df_ingredients['ingredient_singular_clean'] = df_ingredients['ingredient_singular'].apply(lambda x: ' '.join([word for word in x.split() if word not in (stop)]))

df_ingredients['ingredient_singular_clean'] = df_ingredients['ingredient_singular_clean'].str.replace('(', '')
df_ingredients['ingredient_singular_clean'] = df_ingredients['ingredient_singular_clean'].str.replace(')', '')
#unique_ingredients = pd.DataFrame(df_ingredients['ingredient_singular_clean'].value_counts().keys().unique())
#unique_ingredients.to_csv('./data/unique_ingredients.csv', header=None, index=False)

unique_ingredients = df_ingredients['ingredient_singular_clean'].value_counts()
unique_ingredients.to_csv('./data/unique_ingredients.csv', header=None)

unique_units = pd.DataFrame(df_ingredients['unit'].value_counts(5).keys().unique())
unique_units.to_csv('./data/unique_units.csv', header=None, index=False)
unique_ingredients.keys()
df_ingredients['ingredient_singular_clean'].value_counts()
df_ingredients.to_csv('./data/ingredients_list.csv')
dummy_table = df_ingredients.pivot(index='id', columns='ingredient_singular_clean', values='value')

## old method to make dummy table. This is based on uncleaned list of ingredients.
# dummy_table = pd.DataFrame()
# mlb = MultiLabelBinarizer()
# dummy_table = df.join(pd.DataFrame(mlb.fit_transform(df['ingredient_list']),
#                           columns=mlb.classes_,
#                           index=df.index))
dummy_table.to_csv('./data/ingredients_dummy.csv')<jupyter_output><empty_output><jupyter_text>Recipe Yield<jupyter_code>#transform recipe yield into two categories one for portion per person one for number of pieces
df_personen = df[df.recipe_yield.str.contains('personen', na=False)].copy()
df_personen.recipe_yield = df_personen.recipe_yield.str.replace('personen', '')
df_personen['recipe_yield_personen'] = pd.to_numeric(df_personen['recipe_yield'])
df['recipe_yield_personen'] = df_personen['recipe_yield_personen']
df_stuks = df[df.recipe_yield.str.contains('stuks', na=False)].copy()
df_stuks.recipe_yield = df_stuks.recipe_yield.str.replace('stuks', '')
df_stuks['recipe_yield_stuk'] = pd.to_numeric(df_stuks['recipe_yield'])
df['recipe_yield_stuk'] = df_stuks['recipe_yield_stuk']
df = df.drop(['recipe_yield'], axis=1)<jupyter_output><empty_output><jupyter_text>Type<jupyter_code>df['source_type'] = df['source']
df['source_type'] = df['source_type'].astype(str)
df.source_type = df.source_type.apply(lambda x: 'magazine' if 'magazine' in x.lower() else x)
df.source_type = df.source_type.apply(lambda x: 'kookboek' if 'kookboek' in x.lower() else x)
df.source_type = df.source_type.apply(lambda x: 'receptkaart' if 'receptkaart' in x.lower() else x)
df.source_type = df.source_type.apply(lambda x: 'box' if 'allerhande box' in x.lower() else x)
threshold = 300 # Anything that occurs less than this will be removed.
value_counts = df['source_type'].value_counts()
to_remove = value_counts[value_counts <= threshold].index
df['source_type'].replace(to_remove, 'other', inplace=True)<jupyter_output><empty_output><jupyter_text>Date<jupyter_code>df[['year_nr1','year_nr2','year_nr3','year_nr4']] = df['source'].str.extractall(r'(\d{4} nr. \d{2})').unstack()
df[['year_nr1','year_nr2','year_nr3','year_nr4']] = df[['year_nr1','year_nr2','year_nr3','year_nr4']].replace(' nr. ', '.', regex=True)
df['minimum_year_nr'] = df.loc[:, ['year_nr1','year_nr2','year_nr3','year_nr4']].astype(float).min(axis=1)
df[['year_week1', 'year_week2']] = df['source'].str.extractall(r'(\d{4} week \d{2}-\d{2})').unstack()
df[['year_week1','year_week2']] = df[['year_week1','year_week2']].replace(' week ', '.', regex=True)
df['year_week1'] = df['year_week1'].str.split('-').str[0].str.strip()
df['year_week2'] = df['year_week2'].str.split('-').str[0].str.strip()
df[['week_year1','week_year2']] = df['source'].str.extractall(r'(week \d{2} \d{4})').unstack()
df['week_year1'] = df['week_year1'].str[7:] + '.' + df['week_year1'].str[5:7]
df['week_year2'] = df['week_year2'].str[7:] + '.' + df['week_year2'].str[5:7]
df['year_week1'] = df['year_week1'].fillna(df['week_year1'])
df['year_week2'] = df['year_week2'].fillna(df['week_year2'])
df['minimum_year_week'] = df.loc[:, ['year_week1','year_week2']].astype(float).min(axis=1)
#only keep first mention
drop_cols = ['source', 'year_nr1', 'year_nr2', 'year_nr3', 'year_nr4', 'year_week1', 'year_week2', 'week_year1', 'week_year2']
df = df.drop(drop_cols, axis=1)<jupyter_output><empty_output><jupyter_text>Tags<jupyter_code>#df['tags'] = df['tags'].apply(lambda x: sorted(x)) #sort tags alphabetically
df['tags_str'] = df['tags'].apply(lambda x: ', '.join(x))
#split tags into three columns for each tag
df = df.join(pd.DataFrame(df.tags.values.tolist()).add_prefix('tag_'))

# Make a dummy table of all the tags
mlb = MultiLabelBinarizer()
df_tags_matrix = pd.DataFrame(mlb.fit_transform(df['tags']), columns=mlb.classes_, index=df.index)
df_tags_matrix['id'] =  df['id']
df_tags_matrix = df_tags_matrix.dropna()
df = df.drop('tags', axis=1) #drop dictionary column of tags
df_tags_matrix.to_csv('./data/tags_dummy.csv')
df.to_csv('./data/allerhande_clean.csv', index=False)<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup