<jupyter_start><jupyter_text>Engineering a training datasetIn this notebook we will create a dataset to train a regression model, and guess customer engagement with an uploaded recipeThe approach will be to create all these columns, and then cherry-pick the data we want to use to apply our different models in another notebook.![table](../images/table-planning.jpg) We start shaping the MVP data<jupyter_code>import pickle
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns 
%matplotlib inline
import datetime
from tqdm import tqdm<jupyter_output><empty_output><jupyter_text>start using RAW_interactions to score the interactions per recipes.<jupyter_code>interactions = pd.read_csv("../data/RAW_interactions.csv")
interactions
interactions.rename(columns={'date':'interaction_date'}, inplace=True)
interactions.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 1132367 entries, 0 to 1132366
Data columns (total 5 columns):
 #   Column            Non-Null Count    Dtype 
---  ------            --------------    ----- 
 0   user_id           1132367 non-null  int64 
 1   recipe_id         1132367 non-null  int64 
 2   interaction_date  1132367 non-null  object
 3   rating            1132367 non-null  int64 
 4   review            1132198 non-null  object
dtypes: int64(3), object(2)
memory usage: 43.2+ MB<jupyter_text>we need to use pandas_groupby to start calculating aggregations<jupyter_code>recipes = pd.read_csv("../data/RAW_recipes.csv")
recipes.rename(columns={'id':'recipe_id'}, inplace=True)
recipes.head()<jupyter_output><empty_output><jupyter_text>As the database covers up to end of 2018 interactions, we will use 2018-12-31 as the reference day, as if we were doing this analysis then.<jupyter_code>REF_DAY = datetime.datetime(2018, 12, 31, 0, 0)<jupyter_output><empty_output><jupyter_text>Now I want to bring over the subitted day of each recipe into the interaction dataset.<jupyter_code>submission_table = recipes[['recipe_id','submitted','contributor_id']]
submission_table
interactions = interactions.merge(submission_table, on='recipe_id')
interactions
interactions['interaction_date'] = pd.to_datetime(interactions['interaction_date'])
interactions['submitted'] = pd.to_datetime(interactions['submitted'])
interactions.info()
interactions['days_after_sub'] = interactions['interaction_date']-interactions['submitted']
interactions['days_after_sub'] = [d.days for d in interactions['days_after_sub']]
interactions.info()
interactions<jupyter_output><empty_output><jupyter_text>We will do the sentiment analysis now before calculating these aggregations<jupyter_code>from textblob import TextBlob
pol = lambda x: TextBlob(x).polarity
# this takes a while to go through the whole column
interactions['review_pol'] = [pol(str(rev)) for rev in tqdm(interactions['review'])]
interactions.info()
sns.displot(interactions['review_pol']);
interactions = interactions[['recipe_id',
                             'user_id',
                             'review',
                             'rating',
                             'interaction_date',
                             'submitted',
                             'contributor_id',
                             'days_after_sub',
                             'review_pol']]
interactions<jupyter_output><empty_output><jupyter_text>Save in a clean dataframe to be used later in tableau for more exploratory analysis<jupyter_code>interactions.to_csv("../data/clean-data/interactions_clean.csv")<jupyter_output><empty_output><jupyter_text>Now we calculate aggregations in order to enrich the recipe dataframe<jupyter_code>#Checkpoint to retake the notebook from this point
interactions = pd.read_csv("../data/clean-data/interactions_clean.csv")
interaction_nums = interactions[['recipe_id','user_id','rating','days_after_sub','review_pol']]
interaction_nums<jupyter_output><empty_output><jupyter_text>We aggregate by RECIPE ID, to add parameters about user interaction to each recipe<jupyter_code>interact_agg = interaction_nums.groupby('recipe_id').agg({'user_id':'count',
                                                          'rating':'mean',
                                                          'days_after_sub':['max','min','mean'],
                                                          'review_pol':'mean'})<jupyter_output><empty_output><jupyter_text>We want to create new column names to flatten the 2 dimensional columns name<jupyter_code>interact_agg.columns = ['_'.join(col).strip() for col in interact_agg.columns.values]
interact_agg.columns<jupyter_output><empty_output><jupyter_text>now we bring over the recipes again (in case the notebook was taken from the checkpoint)<jupyter_code>recipes = pd.read_csv("../data/RAW_recipes.csv")
recipes.rename(columns={'id':'recipe_id'}, inplace=True)
recipes.head()
combined_df = pd.merge(recipes, interact_agg, how='left', on='recipe_id')
combined_df.head()<jupyter_output><empty_output><jupyter_text>For the MVP we want to have a dataset with the following columns (descriptions are in the table at the top of this notebook): **recipe_id** (will be dropped before applying the model, we use it to track the recipe if we need to append things afterwards)  **comm_rec_month** (dependant variable)  **no_recipes_submitted**  **avg_comments_recipes**  **avg_ratings_recipes**  **avg_sentiment_recipes**  **time_to_cook**  **no_steps**  **no_ingredients**  **nutrition_score****weekday_posted****month_posted**<jupyter_code>combined_df['submitted'] = pd.to_datetime(combined_df['submitted'])
combined_df['days_since_posted'] = [d.days for d in REF_DAY - combined_df['submitted']]
combined_df['comm_rec_month'] = combined_df['user_id_count']/combined_df['days_since_posted']*30<jupyter_output><empty_output><jupyter_text>For the columns coming from user data, we need to make some aggregations in the dataset<jupyter_code>recipe_agg = combined_df.groupby('contributor_id').agg({'recipe_id':'count',
                                                        'user_id_count':'mean',
                                                        'rating_mean':'mean',
                                                        'review_pol_mean':'mean',
                                                        })
recipe_agg
recipe_agg.rename(columns = {'recipe_id':'no_recipes_submitted',
                             'user_id_count':'avg_comments_recipes',
                             'rating_mean':'avg_ratings_recipes',
                             'review_pol_mean':'avg_sentiment_recipes'
                             }, inplace=True)
recipe_agg
combined_df = pd.merge(combined_df, recipe_agg, how='left', on=['contributor_id'])
combined_df.head()
combined_df.columns<jupyter_output><empty_output><jupyter_text>Steps after MVP Using spacy to find verbs in the steps and giving a difficulty  Knowing our context is recipes, we expect to find a limited number of verbs that will describe different actions and techniques. we will see most common verbs we can find, and we will consider recipes with low or no verbs to be simpler than others that have more verbs into more complex recipes.<jupyter_code>import spacy
nlp = spacy.load("en_core_web_sm")
verbs_set = set()
for prep in tqdm(combined_df['steps'].sample(3000)):
    doc = nlp(prep)
    verbs = [token.text for token in doc if token.pos_=="VERB"]
    for verb in verbs:
        verbs_set.add(verb)
        

print(verbs_set)<jupyter_output>{'hardboiled', 'contains', 'store', 'turning', 'deal', 'stripped', 'coming', 'rests', 'brush', 'mush', 'filled', 'pushing', 'firm', 'totaling', 'epazote', 'result', 'featuring', 'meets', 'devein', 'packaged', 'sample', 'grated', 'weigh', 'enable', 'assemble', 'aiming', 'occur', 'bast', 'load', 'curled', 'trimmed', 'lighten', 'griddle', 'sliding', 'satisfaction', 'seasoned', 'mound', 'swap', 'deglaze', 'emulsified', 'squirt', 'doneness', 'sweetened', 'vegetarian', 'line', 'invented', 'shimmering', 'piped', 'bun', 'powdered', 'roquefort', 'fry', 'googling', 'reduces', 'chilling', 'referred', 'let', 'mashing', 'creating', 'keep', 'passed', 'fill', 'google', 'found', 'clams', 'does', 'lke', 'zest', 'mixed', 'brown', 'using', 'blanched', 'collapse', 'says', 'pairing', 'flaten', 'freeze', 'cannellini', 'papered', 'ladled', 'step', 'seen', 'serrated', 'start', "'re", 'smoothing', 'granulted', 'relieve', 'bought', 'scooped', 'mins', 'crumble', 'recommended', 'microwave', 'prodded', 'wire', 'fr[...]<jupyter_text>This won't work without a more specific definition of what are verbs that correspond to cooking techniques in a kitchen. also spacy will detect several ingredient names as verbs, so we'll leave the complexity of the recipe to the column no_steps only and we will focus in creating categories with the tags.   Find all tags from all recipes and decide if we want to extract categories from them<jupyter_code>tags_set = set()
for tags in combined_df['tags'].sample(10):
        print (tags)
tags_set = set()
tags_set = set()
for row_tags in tqdm(combined_df['tags']):
    for tag in [a.strip("' ") for a in row_tags.strip("[]").split(",")]:
        tags_set.add(tag)
len(tags_set)
tags_set
categories=['appetizers',
            'baking',
            'barbecue',
            'desserts',
            
            'breakfast',
            'brunch',
            'lunch',
            'cocktails',
            'dinner-party',
            
            'meat',
            'vegan',
            'vegetarian',
            
            'spicy',
            
            'low-carb',
            'low-fat',
            'low-protein',
            'low-sodium',
            'nut-free',
            'egg-free',
            'dairy-free',
            'gluten-free',
            
            'microwave',
            'easy',
            'no-cook',
            'deep-fry',
            
            'pasta',
            'pizza',
            'cupcakes',
            'muffins',
            'macaroni-and-cheese',
            
            'peanut-butter',
            'vegetables',
            'fruit',
            'bacon',
            'avocado',                       
            'cheese',
            'chocolate',
            ]
len(categories)
combined_df.to_csv("../data/clean-data/recipes_combined_clean.csv")<jupyter_output><empty_output><jupyter_text>* and we create a dataframe with dummies regarding these categories:  **WARNING this will take several hours to go through all 200k+ recipes**  If you don't want to wait, go to the next checkpoint and load the dataframe, as these columns wont be used anymore in this notebook<jupyter_code>categories_df = pd.DataFrame(columns=categories)

for i,tags in tqdm(enumerate(combined_df['tags'])):
    row = []
    for cat in categories:
        if cat in tags:
            row.append(1)
        else:
            row.append(0)
    categories_df.loc[i] = row

categories_df
combined_df = pd.concat([combined_df,categories_df], axis =1)<jupyter_output><empty_output><jupyter_text>We save this dataframe to recall it in other iterations.<jupyter_code>combined_df.to_csv("../data/clean-data/recipes_combined_clean.csv")
#Checkpoint to retake the notebook from this point
combined_df = pd.read_csv("../data/clean-data/recipes_combined_clean.csv", index_col=0)
combined_df.columns<jupyter_output><empty_output><jupyter_text>Generating new dependent variablesThe first dependent variable we chose is too small to become a tangible parameter, and even due it does give insight on how much traffic our recipe might attract to our page, it is very unpredictable when people will actually interact with the recipe further away than a few days after the posting.   Because of this, we will change the focus and try to measure the number of interactions the posted recipe has **only during the 30 days after the recipe was posted**. We will also generate a new column with the average rating of the recipe, for further applications of the model.* We first check both submitted date and interaction date are in interactions dataframe<jupyter_code>interactions.dtypes<jupyter_output><empty_output><jupyter_text>* Then turn our date columns into datetime types<jupyter_code>interactions['interaction_date'] = pd.to_datetime(interactions['interaction_date'])
interactions['submitted'] = pd.to_datetime(interactions['submitted'])<jupyter_output><empty_output><jupyter_text>* Now we can create a column that is true when the comment is inside the first seven days since posted<jupyter_code>interactions['in_first30d'] = interactions['interaction_date']<interactions['submitted']+datetime.timedelta(days=30)
interactions['in_first30d'].value_counts()
in_first_month = interactions.groupby('recipe_id').agg({'in_first30d':'count',
                                                   'rating':'mean'})
combined_df = combined_df.merge(in_first_month, how='left', on='recipe_id')
 combined_df.head()<jupyter_output><empty_output><jupyter_text>And we save this resulting dataframe again<jupyter_code>combined_df.to_csv("../data/clean-data/recipes_combined_clean.csv")<jupyter_output><empty_output><jupyter_text>Making a dataframe for the regression models<jupyter_code>#Checkpoint to retake the notebook from this point
combined_df = pd.read_csv("../data/clean-data/recipes_combined_clean.csv", index_col=0)
combined_df['submitted'] = pd.to_datetime(combined_df['submitted'])
combined_df['month_posted'] = [d.month for d in combined_df['submitted']]<jupyter_output><empty_output><jupyter_text>Now we need the columns from recipe. time_to_cook, no_steps and no_ingredients are already on the table, let's try to get a nutrition score, as well as the weekday and month on which the recipe was posted<jupyter_code>combined_df.columns
MVP_export = combined_df[['recipe_id',
                        'comm_rec_month',
                        'in_first30d',
                        'rating',
                        'no_recipes_submitted',
                        'avg_comments_recipes',
                        'avg_ratings_recipes',
                        'avg_sentiment_recipes',
                        'minutes',
                        'n_steps',
                        'n_ingredients',
                        'month_posted',
                        'days_since_posted']]
MVP_export.to_csv("../data/mvp_export.csv")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11348408710217756, 'fraction_numerical': 0.008375209380234505, 'mean_word_length': 3.406211562115621, 'pattern_counts': {'":': 0, '<': 93, '<?xml version=': 0, '>': 92, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 16, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '23', 'dir': 'jupyter-structured-clean-dedup', 'id': '60069', 'license': 'Apache-2.0', 'n_tokens_mistral': 4380, 'n_tokens_neox': 4189, 'n_words': 1109, 'path': 'code/1-generating-features-and-wrangling.ipynb', 'repo_name': 'Alex-Skp/Recipe-Popularity-Predictor', 'size': '14328'}