Turkish Recipe Collection
GOAL
RESULT
PROJECT DURATION
The objective was to design an engaging and informative dashboard that showcases a wide array of Turkish recipes, providing a user-friendly tool for culinary enthusiasts to discover and explore the rich flavors of Türkiye.
The final product is an interactive dashboard that presents a variety of Turkish dishes, complete with ingredients, recipe details, and a short description to entice the user’s culinary curiosity. It offers a visual feast that is as appealing to the eye as the recipes are to the palate, allowing users to navigate through different categories and recipes easily.
The project was completed within a time frame of 3 to 10 days. This timeline included the curation of recipes, data entry, testing, and designing the user interface for the best user experience.
CASE – TURKISH RECIPE COLLECTION DASHBOARD
In this project, I gathered traditional Turkish recipes and created a visually attractive and informative dashboard. The interface combines textual and visual elements to provide a comprehensive guide to the diverse culinary culture of Türkiye.
Data Collection and Conversion
The first step in our journey is to bridge the language gap and curate a dataset that reflects the rich culinary heritage of Turkey in a globally comprehensible format. We start by sourcing a comprehensive JSON file that details an array of Turkish recipes. This file serves as our raw material, brimming with potential but locked behind a linguistic barrier.
The first two contents of our JSON file appear as follows and in Turkish;
{
"Recipes": [
{
"ID" : "-MbNzUyCtfBnsghdACKO",
"CategoryBread" : "KÖFTE TARİFLERİ",
"Cuisine" : "Türk",
"Image" : "https://cdn.yemek.com/mnresize/1250/833/uploads/2021/02/soslu-misket-kofte-sunum.jpg",
"IngridientNames" : "dana kıyma;büyük boy soğan;sarımsak;yumurta;tuz;karabiber;kimyon;pul biber;bayat ekmek kırıntısı;sıvı yağ;domates salçası;domates püresi;tereyağı;sıcak su;pul biber;",
"Ingridients" : "● 400-450 gram dana kıyma\n● 1/2 adet büyük boy soğan\n● 1 diş sarımsak\n● 1 adet yumurta\n● 1 çay kaşığı tuz\n● 1 çay kaşığı karabiber\n● 1 çay kaşığı kimyon\n● 1 çay kaşığı pul biber\n● 3-4 yemek kaşığı bayat ekmek kırıntısı\n● 4 yemek kaşığı sıvı yağ\n● 1 yemek kaşığı domates salçası\n● 3 yemek kaşığı domates püresi\n● 1/2 tatlı kaşığı tereyağı\n● 1/3 çay bardağı sıcak su\n● 1 çay kaşığı pul biber\n",
"Keywords" : "soslu, misket, köfte",
"MainCategory" : "Köfte",
"Name" : "Soslu Misket Köfte Tarifi",
"PrepDetails" : "4 kişilik;25 dakika;25 dakika;",
"RecipeDetails" : "1.) Kıymayı geniş bir karıştırma kabına alın. Üzerine soğanı ve sarımsağı rendeleyin. Tüm baharatlarını ve yumurtayı ekleyip yoğurun.\n2.) Son olarak bayat ekmek kırıntılarını kaşık kaşık ekleyerek kıvam almasını sağlayın. Tüm harç birleşince dinlenmesi için en az 30 dakika buzdolabında bekletin.\n3.) Buzdolabından çıkardıktan sonra kiraz büyüklüğünde parçalar kopararak elinizde yuvarlayın. Elinizi suyla ıslatırsanız veya yağ ile yağlarsanız daha kolay şekil verebilirsiniz.\n4.) Tüm köftelerin eşit boyda olmasına özen gösterin, ne çok büyük ne de çok küçük olmalı.\n5.) Tavayı sıvı yağ ekleyip güzelce ısıtın. Misket köfteleri ekleyin, karıştırmadan pişirmeye başlayın. Bu şekilde 2 dakika pişirin.\n6.) Tavayı ara ara sallayarak 3-4 dakika pişirin. Ters dönmeyen köfteleri dikkatli ters çevirin. Piştiğine emin olduktan sonra tavadan alın.\n7.) Aynı tava içerisine salça, domates püresi, pul biber, suyu ekleyin. Karıştırın ve ısındıktan sonra tereyağını da ekleyip birleşmesini sağlayın.\n8.) Misket köfteleri tekrar tavaya alıp sosla birleştirin. Tavayı hafifçe ileri-geri sallayarak veya bir spatula kullanarak sosla bulanmasını sağlayın. 1-2 dakika pişirin.\n9.) Sıcakken servis edin. Pilav veya patates püresi ile servis edebilirsiniz. Afiyet olsun!\n",
"ShortDescription" : "Enfes sosu ve tam kıvamında köfte harcıyla soslu misket tarifi arayanları buraya alalım. Yanına da sade bir pilav varsa enfes!"
},{
"ID" : "-MbNzVCQy3cTLIQ9G_ga",
"CategoryBread" : "PRATİK YEMEK TARİFLERİ",
"Cuisine" : "Türk",
"Image" : "https://cdn.yemek.com/mnresize/1250/833/uploads/2021/02/firinda-kasarli-ispanak-yemegi-one-cikan.jpg",
"IngridientNames" : "ıspanak (ayıklanmış ve temizlenmiş);patates;zeytinyağı;tuz (azaltıp arttırılabilir);karabiber;un;tereyağı;süt;rendelenmiş kaşar peyniri;",
"Ingridients" : "● 500 gram ıspanak (ayıklanmış ve temizlenmiş)\n● 4 adet orta boy patates\n● 6 yemek kaşığı zeytinyağı\n● 1 çay kaşığı tuz (azaltıp arttırılabilir)\n● 1 çay kaşığı karabiber\n● 2 yemek kaşığı un\n● 2 yemek kaşığı tereyağı\n● 1,5 su bardağı süt\n● 1 su bardağı rendelenmiş kaşar peyniri\n",
"Keywords" : "fırında, kaşarlı, ispanak, yemeği",
"MainCategory" : "Sebze",
"Name" : "Fırında Kaşarlı Ispanak Yemeği Tarifi",
"PrepDetails" : "6 kişilik;10 dakika;30 dakika;",
"RecipeDetails" : "1.) Patateslerin kabuklarını soyup rendeleyin ve bir tavaya alın. Üzerine zeytinyağı ilave edin, suyunu salıp çekene kadar kavurun.\n2.) Temizlenmiş, yıkanmış ıspanakları doğrayın. Ispanakların bir kısmını ilave edip karıştırın. Ardından diğer kısmını, tuz ve baharatları da ekleyerek yaklaşık 4 dakika kadar kavurun.\n3.) Yağlanmış borcama ıspanaklı patatesli karışımı aktarın, üzerini spatula yardımıyla düzleştirin. Bir tencerede un ve tereyağını kavurun. Üzerine azar azar süt ilave edin. Bir yandan da karıştırarak topaklaşmasını engelleyin. Yoğunlaşınca beşamel sosunuz hazır.\n4.) Önceden ısıtılmış 200 derecelik fırında 10 dakika pişirin. 10 dakika pişirdikten sonra çıkarıp üzerine kaşar peyniri serpin.\n5.) Yeniden fırına verin, üzeri kızarıp peynirler tamamen eriyene kadar yaklaşık 10 dakika daha pişirin.\n6.) Üzeri kızarınca yemeğiniz hazır! Fırından dilim bir süre dinlendirin ve dilimleyerek servis edin. Ellerinize sağlık!\n",
"ShortDescription" : "Fırında kaşarlı ıspanak yemeği sebze tariflerinden hem hafif hem de çok doyurucu bir tarif. Üstelik resimli anlatımlı. Mutlaka deneyin."
},
Recognizing the need for accessibility, we apply a Python-based translation mechanism to transmute the dataset from Turkish to English. We deploy a script that seamlessly communicates with the DeepL API, renowned for its linguistic precision. Our fields of focus are diverse, spanning from the names and categories of dishes to the subtle nuances encapsulated in their preparation details and descriptions.
import json
import requests
def translate_text_deepl(text, api_key):
url = "https://api-free.deepl.com/v2/translate"
headers = {"Content-Type": "application/x-www-form-urlencoded"}
data = {
"auth_key": api_key,
"text": text,
"target_lang": "EN",
"source_lang": "TR"
}
response = requests.post(url, headers=headers, data=data)
if response.status_code == 200:
return response.json()['translations'][0]['text']
else:
print(f"Error: {response.status_code}")
return text
def translate_fields_in_json_deepl(file_path, output_file_path, fields_to_translate, api_key):
with open(file_path, 'r', encoding='utf-8') as file:
data = json.load(file)
for recipe in data['Recipes']:
for key in fields_to_translate:
if key in recipe:
translated_text = translate_text_deepl(recipe[key], api_key)
recipe[key] = translated_text
with open(output_file_path, 'w', encoding='utf-8') as file:
json.dump(data, file, ensure_ascii=False, indent=4)
fields_to_translate = [
'CategoryBread', 'Cuisine', 'IngridientNames', 'Ingridients',
'Keywords', 'MainCategory', 'Name', 'PrepDetails', 'RecipeDetails',
'ShortDescription'
]
file_path = 'C:/Users/msr-h/OneDrive/SQL/Turkish_Food/recipe_dataset_final.json'
output_file_path = 'C:/Users/msr-h/OneDrive/SQL/Turkish_Food/translated_recipes_sample4.json'
api_key = 'YOUR_API_KEY'
translate_fields_in_json_deepl(file_path, output_file_path, fields_to_translate, api_key)
The script meticulously iterates through each recipe, imparting linguistic clarity by replacing the original Turkish descriptions with their English counterparts. The result is a newly minted file, enriched with translations that convey more than just the semantics but the essence of Turkish gastronomy.
685 recipes, 1.5 million texts were translated in the JSON file and the first two recipes are as follows;
{
"Recipes": [
{
"ID": "-MbNzUyCtfBnsghdACKO",
"CategoryBread": "MEATBALL RECIPES",
"Cuisine": "Turkish",
"Image": "https://cdn.yemek.com/mnresize/1250/833/uploads/2021/02/soslu-misket-kofte-sunum.jpg",
"IngridientNames": "ground beef;large size onion;garlic;egg;salt;black pepper;cumin;red pepper flakes;stale bread crumbs;vegetable oil;tomato paste;tomato puree;butter;hot water;red pepper flakes;",
"Ingridients": "400-450 grams of ground beef\n1/2 large onion\n1 clove of garlic\n1 egg\n1 teaspoon salt\n1 teaspoon black pepper\n1 teaspoon cumin\n1 teaspoon chili pepper\n3-4 tablespoons of stale bread crumbs\n4 tablespoons of oil\n1 tablespoon tomato paste\n3 tablespoons tomato puree\n1/2 teaspoon butter\n1/3 cup hot water\n1 teaspoon chili pepper\n",
"Keywords": "meatballs, meatballs, with sauce",
"MainCategory": "Meatball",
"Name": "Meatballs with Sauce Recipe",
"PrepDetails": "4 people; 25 minutes; 25 minutes;",
"RecipeDetails": "1.) Put the minced meat in a large mixing bowl. Grate the onion and garlic on it. Add all spices and egg and knead.\n2.) Finally, add the stale bread crumbs spoon by spoon to get a consistency. When all the mixture is combined, refrigerate for at least 30 minutes to rest.\n3.) After taking it out of the fridge, pick cherry-sized pieces and roll them in your hands. If you wet your hand with water or grease it with oil, you can shape it more easily.\n4.) Make sure that all the meatballs are of equal size, neither too big nor too small.\n5.) Add oil to the pan and heat it well. Add the meatballs and start cooking without stirring. Cook like this for 2 minutes.\n6.) Cook for 3-4 minutes, shaking the pan occasionally. Carefully turn over the meatballs that do not turn over. Remove from the pan after making sure they are cooked.\n7.) Add tomato paste, tomato puree, red pepper flakes and water into the same pan. Stir and after it gets hot, add the butter and let it combine.\n8.) Put the meatballs back in the pan and combine them with the sauce. Gently shake the pan back and forth or use a spatula to coat them with the sauce. Cook for 1-2 minutes.\n9.) Serve while hot. You can serve with rice or mashed potatoes. Bon appetit!\n",
"ShortDescription": "Let's take those who are looking for a gravy marbles recipe with its delicious sauce and meatball mixture in the right consistency here. If you have a plain rice with it, it's delicious!"
},
{
"ID": "-MbNzVCQy3cTLIQ9G_ga",
"CategoryBread": "PRACTICAL RECIPES",
"Cuisine": "Turkish",
"Image": "https://cdn.yemek.com/mnresize/1250/833/uploads/2021/02/firinda-kasarli-ispanak-yemegi-one-cikan.jpg",
"IngridientNames": "spinach (sorted and cleaned);potatoes;olive oil;salt (can be increased or decreased);black pepper;flour;butter;milk;grated cheese;",
"Ingridients": "500 grams spinach (sorted and cleaned)\n4 medium-sized potatoes\n6 tablespoons of olive oil\n1 teaspoon salt (can be increased or decreased)\n1 teaspoon black pepper\n2 tablespoons flour\n2 tablespoons of butter\n1.5 cups of milk\n1 cup grated cheddar cheese\n",
"Keywords": "baked, cheddar, spinach, dish",
"MainCategory": "Vegetable",
"Name": "Baked Spinach with Kashkaval Recipe",
"PrepDetails": "6 people; 10 minutes; 30 minutes;",
"RecipeDetails": "1.) Peel and grate the potatoes and put them in a pan. Add olive oil and roast until the water is released and absorbed.\n2.) Chop the cleaned and washed spinach. Add some of the spinach and mix. Then add the other part, salt and spices and fry for about 4 minutes.\n3.) Transfer the spinach and potato mixture to the greased baking dish and smooth it with a spatula. Roast the flour and butter in a saucepan. Add milk little by little. Stir while stirring to prevent lumps from forming. When it thickens, your bechamel sauce is ready.\n4.) Bake in a preheated 200 degree oven for 10 minutes. After 10 minutes, take it out and sprinkle cheddar cheese on it.\n5.) Return to the oven and bake for about 10 more minutes until the top is browned and the cheese is completely melted.\n6.) When the top is golden brown, your meal is ready! Slice it out of the oven, let it rest for a while and slice and serve. Health to your hands!\n",
"ShortDescription": "Spinach dish with cheddar in the oven is a light and very satisfying recipe from vegetable recipes. Moreover, with illustrated narration. Be sure to try it."
},
This translation is not just about converting words; it’s an act of cultural translation, ensuring that each recipe carries with it the story, the history, and the soul of Turkish cuisine. It’s the first but crucial step in our project that paves the way for the subsequent stages of visualization, analysis, and sharing of Turkey’s culinary delights with the world.
Create a Database
With our translated dataset in hand, we now proceed to the technical core of our project: the creation of a digital ecosystem where these recipes can be stored, queried, and displayed. The foundational step in this phase is the construction of a database within MySQL, a robust system for managing relational data.
We initiate this process by crafting a SQL command that outlines the architecture of our ‘Recipes’ table. The command is precise and structured, intending to create a table where each column is tailored to hold specific details of our recipes—ranging from their unique identifiers to their luscious images and insightful descriptions.
Our table is composed of various data types to accommodate the nature of the content it is meant to hold. VARCHAR(255) fields are designated for textual data that requires a defined limit, while TEXT fields are reserved for longer entries where brevity is not a virtue.
The database is the bedrock upon which our project rests. By carefully creating this repository, we ensure that every delectable detail of Turkish culinary art is preserved digitally, ready to be served to curious minds through our forthcoming interactive dashboard. The meticulous design of the database schema reflects our commitment to preserving the integrity and richness of each recipe, ensuring that every flavor and spice is but a query away from discovery.
The first SQL script we implemented is as follows:
CREATE TABLE Recipes (
ID VARCHAR(255) PRIMARY KEY,
CategoryBread VARCHAR(255),
Cuisine VARCHAR(255),
Image TEXT,
IngredientNames TEXT,
Ingredients TEXT,
Keywords TEXT,
MainCategory VARCHAR(255),
Name VARCHAR(255),
PrepDetails VARCHAR(255),
RecipeDetails TEXT,
ShortDescription TEXT
);
After setting up our MySQL database, we turn to the task of importing our curated Turkish recipe data. Using a Python script, we automate the insertion of each recipe’s details into our database. The script connects to MySQL, iterates through our JSON file, and for each entry, executes an INSERT command. This process transforms our dataset into a dynamic culinary library within our database, ready for queries and exploration.
import json
import mysql.connector
config = {
'user': 'root',
'password': 'password*',
'host': '127.0.0.1',
'database': 'turkish_food',
'raise_on_warnings': True
}
json_file_path = 'C:/Users/msr-h/OneDrive/SQL/Turkish_Food/translated_recipes_dataset_final.jsonn'
try:
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
with open(json_file_path) as file:
data = json.load(file)
for recipe in data:
insert_stmt = (
"INSERT INTO Recipes (ID, CategoryBread, Cuisine, Image, IngredientNames, Ingredients, Keywords, MainCategory, Name, PrepDetails, RecipeDetails, ShortDescription) "
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
)
data_tuple = (
recipe["ID"], recipe["CategoryBread"], recipe["Cuisine"], recipe["Image"],
recipe["IngredientNames"], recipe["Ingredients"], recipe["Keywords"],
recipe["MainCategory"], recipe["Name"], recipe["PrepDetails"],
recipe["RecipeDetails"], recipe["ShortDescription"]
)
cursor.execute(insert_stmt, data_tuple)
cnx.commit()
except mysql.connector.Error as err:
print(f"Error: {err}")
finally:
if cnx.is_connected():
cursor.close()
cnx.close()
print("MySQL connection is closed")
Database view in MySql;
The database snapshot indicates a successful import of Turkish recipes into the MySQL ‘turkish_food’ database. The table ‘recipes’ contains multiple columns like ‘ID’, ‘CategoryBread’, ‘Cuisine’, ‘Image’, ‘IngredientNames’, ‘Ingredients’, ‘Keywords’, ‘MainCategory’, ‘Name’, ‘PrepDetails’, ‘RecipeDetails’, and ‘ShortDescription’, which are populated with various data entries. These columns correspond to the details of the recipes such as type of bread, the cuisine classification, image URLs, names and lists of ingredients, associated keywords, the main category of the dish, the dish’s name, details on how to prepare the dish, full recipe text, and a brief description.
The database is now ready to be utilized for the project’s next phases, which may include data analysis, creating visualizations, or developing a user interface for recipe exploration.
Dashboard Creation
After the meticulous process of translating and preparing the dataset, the next phase in our project was to develop the dashboard itself. This stage was critical in transforming the raw data into a compelling visual story that would entice and engage users. Here’s how we approached the development and visualization of the Turkish Recipe Collection Dashboard.
Data Preperation and Import
The preparation of our dataset for visualization began with its integration into a MySQL database. This approach allowed us to maintain a dynamic link between the data and the visualization tool, enabling real-time updates and interactions. Here’s how we managed this process:
MySQL Database Setup: We structured our MySQL database to reflect the rich details of the Turkish recipes, creating tables for categories, ingredients, preparation steps, and other relevant details. This setup ensured that our data was organized and ready for direct querying.
Direct Connection to Tableau: Connecting Tableau to the MySQL database was straightforward, thanks to Tableau’s robust database connectivity options. By establishing a live connection, we could pull data directly into Tableau, allowing for dynamic data visualization without the need for intermediate steps such as converting JSON to CSV.
Data Design in Tableau
With the data connection established, we focused on crafting a dashboard that was both aesthetically pleasing and functionally rich. Our design philosophy centered around several core elements:
Interactive Filters: To enhance user navigation through the diverse collection of recipes, we implemented interactive filters in Tableau. These filters, based on cuisine type, main ingredients, and recipe complexity, empowered users to tailor their exploration to their specific interests or dietary needs.
Visual Elements: Understanding the importance of visual appeal in culinary discovery, we integrated high-quality images for each recipe directly from our MySQL database connection with HTML links. These visuals served not only as enticing previews but also as essential elements of the user experience, stimulating curiosity and appetite.
Recipe Details: We designed the dashboard to offer detailed views of each recipe upon selection, including ingredient lists, and preparation instructions. This feature was aimed at providing a comprehensive experience from discovery to preparation.
Dish Worksheet
This sheet is likely the primary selector for the recipes. It appears to list the names of the dishes in a clear, straightforward manner, allowing users to select a dish and see more details. The design here would be focused on readability and ease of selection, possibly with a search or filter functionality to narrow down choices.
Functional Purpose: This list serves as an interactive menu for the dashboard. Users can browse the list of recipes sorted alphabetically for easy searching. The highlighted or selected recipe will then drive the content of other dashboard components such as ingredients list, recipe details and images.
Ingredients and Recipe Details Worksheet
This worksheet is dedicated to providing a detailed breakdown of the ingredients required for a dish as well as the step-by-step recipe instructions. It serves as the informational core of the culinary experience, guiding users through the cooking process.
Ingredients and Recipe Details Worksheet
The primary function of this worksheet is to offer a concise description of the chosen recipe. This can include information on the dish’s origins, traditional significance, flavor profile, or serving suggestions. For example, the description “Walnut jam is perhaps the most laborious jam to make, made from raw walnuts.” immediately conveys the effort and authenticity involved in the recipe.