{ "cells": [ { "cell_type": "code", "execution_count": 3, "id": "6de0a395", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "--- Master Dataset Columns ---\n", "['title', 'subTitle', 'description', 'price', 'categoryName', 'address', 'neighborhood', 'street', 'city', 'postalCode', 'state', 'countryCode', 'claimThisBusiness', 'location', 'totalScore', 'permanentlyClosed', 'temporarilyClosed', 'placeId', 'categories', 'fid', 'cid', 'reviewsCount', 'imagesCount', 'imageCategories', 'scrapedAt', 'googleFoodUrl', 'hotelAds', 'openingHours', 'peopleAlsoSearch', 'placesTags', 'reviewsTags', 'additionalInfo', 'gasPrices', 'url', 'searchPageUrl', 'searchString', 'language', 'rank', 'isAdvertisement', 'imageUrl', 'kgmid', 'website', 'phone', 'phoneUnformatted', 'reviewsDistribution', 'additionalOpeningHours', 'locatedIn', 'source_file', 'hotelStars', 'checkInDate', 'checkOutDate', 'hotelDescription', 'updatesFromCustomers', 'reserveTableUrl']\n", "\n", "Toplam Sütun Sayısı: 54\n", "Temizlik sonrası kalan sütun sayısı: 30\n", "Gereksiz sütunlar başarıyla temizlendi \n", "Ticari ve ilgisiz yerler temizlendi. Kalan mekan sayısı: 605\n", "--- Temizlenmiş Şehir Dağılımı ---\n", "city\n", "Fatih 314\n", "Beyoğlu 87\n", "Kadıköy 63\n", "Üsküdar 58\n", "Beşiktaş 49\n", "Şişli 14\n", "Eyüpsultan 13\n", "Zeytinburnu 6\n", "Diğer 1\n", "Name: count, dtype: int64\n", "✅ Puan ve yorum sayısı boş olan satırlar 0 ile dolduruldu.\n", "Kalan boş totalScore sayısı: 0\n", "✅ 'phone' ve 'website' sütunlarındaki boşluklar yer tutucu metinlerle dolduruldu.\n", "✅ Street sütunu için fallback (neighborhood) uygulandı.\n", "Toplam 144 farklı özellik keşfedildi!\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:206: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:219: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master['is_24_7'] = df_master['openingHours'].apply(check_24_7)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:250: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master['openingHours_display'] = df_master.apply(impute_opening_hours, axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:252: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master['is_hours_estimated'] = df_master['openingHours'].apply(lambda x: not (isinstance(x, list) and len(x) > 0))\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:288: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master['display_title'] = df_master.apply(smart_title_fix, axis=1)\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:297: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master['latitude'] = coords['lat']\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:298: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master['longitude'] = coords['lng']\n", "C:\\Users\\hilal\\AppData\\Local\\Temp\\ipykernel_3892\\1209888824.py:331: PerformanceWarning: DataFrame is highly fragmented. This is usually the result of calling `frame.insert` many times, which has poor performance. Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n", " df_master['quality_score'] = df_master.apply(calculate_record_score, axis=1)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "✅ Tüm özellikler dinamik olarak sütunlara dönüştürüldü.\n", "✅ Koordinatlar başarıyla ayrıştırıldı. Örnek:\n", " latitude longitude\n", "0 41.006329 28.975705\n", "1 41.008384 28.977878\n", "2 41.011519 28.983379\n", "3 41.008583 28.980175\n", "4 41.005410 28.976814\n", "--- BAŞLANGIÇ: 605 satır ---\n", "Tespit edilen mükerrer (duplicate) mekan sayısı: 79\n", "\n", "--- Kalite Farkı Olan Mükerrer Kayıt Örnekleri ---\n", " title placeId quality_score \\\n", "100 Muutto Street Food Moda ChIJ-eZunpO5yhQRpWJolK417Lk 20 \n", "342 Muutto Street Food Moda ChIJ-eZunpO5yhQRpWJolK417Lk 20 \n", "163 Landmark 1923 ChIJ1anpz9G3yhQRP29dgW6q_W8 20 \n", "234 Landmark 1923 ChIJ1anpz9G3yhQRP29dgW6q_W8 20 \n", "34 Kariye Mosque ChIJ2cMuRRC6yhQRDNb0n0-3SXA 28 \n", "253 Kariye Mosque ChIJ2cMuRRC6yhQRDNb0n0-3SXA 28 \n", "5 Dolmabahçe Palace ChIJ4307Gna3yhQRC4M7zzg-09w 35 \n", "573 Dolmabahçe Palace ChIJ4307Gna3yhQRC4M7zzg-09w 35 \n", "99 Собака Тарчин ChIJ4e-TpIy5yhQRDQX_PgwDkA8 20 \n", "390 Собака Тарчин ChIJ4e-TpIy5yhQRDQX_PgwDkA8 20 \n", "\n", " source_file \n", "100 V1 \n", "342 V2 \n", "163 V1 \n", "234 V2 \n", "34 V1 \n", "253 V2 \n", "5 V1 \n", "573 V2 \n", "99 V1 \n", "390 V2 \n", "\n", "--- SONUÇ ---\n", "Toplam Silinen: 79\n", "Final Veri Seti (Benzersiz Mekan): 526\n", "--- Toplam 19 Adet Yabancı Alfabeli/Karakterli Veri Bulundu ---\n", "\n", "--- Silinecek Yabancı Kayıtlar ---\n", " title city source_file\n", "357 Caffè Nero Kadıköy V2\n", "99 Собака Тарчин Kadıköy V1\n", "56 伊斯坦堡老城區 Fatih V1\n", "528 Astérix Köyü Beyoğlu V2\n", "174 伊斯坦堡老城區 Beyoğlu V1\n", "325 مطعم الشجرة Fatih V2\n", "574 Dîner croisière Beyoğlu V2\n", "448 مطعم البيت اليمني Yemeni House Restaurant 🇹🇷 Fatih V2\n", "465 مطعم النجع للمأكولات الليبية Fatih V2\n", "558 Памятник паровозу . Fatih V2\n", "367 Phare de Léandre Üsküdar V2\n", "468 Salloura Oğlu Restaurant حلويات و مطعم سلورة Fatih V2\n", "444 عصملي كافيه Fatih V2\n", "606 Kávézó Fatih V2\n", "586 Цветная лестница Стамбул Балат Fatih V2\n", "461 مطعم بيت حلب Fatih V2\n", "557 اسکله امینونو Fatih V2\n", "348 Caffè Nero Kadıköy V2\n", "371 Фонтан до пазара на Юскюдар Üsküdar V2\n", "\n", "--- Kaynak Dosya Dağılımı ---\n", "source_file\n", "V2 16\n", "V1 3\n", "Name: count, dtype: int64\n", "\n", "✅ Yabancı alfabeli 19 kayıt temizlendi.\n", "🔍 Eşleşen dinamik sütun sayısı: 144\n", "✅ 'tags' sütunu başarıyla zenginleştirildi ve temizlik yapıldı.\n", "🧹 Kalan net mekan sayısı: 497\n", "✅ enriched_description sütunu dinamik özelliklerle birlikte oluşturuldu.\n", "🚀 Eski sütun sayısı: 185\n", "✨ Yeni sütun sayısı: 41\n", "📂 Kalan Sütunlar: ['title', 'subTitle', 'description', 'categoryName', 'address', 'neighborhood', 'street', 'city', 'postalCode', 'state', 'totalScore', 'permanentlyClosed', 'temporarilyClosed', 'placeId', 'categories', 'fid', 'cid', 'reviewsCount', 'imagesCount', 'openingHours', 'additionalInfo', 'url', 'searchString', 'rank', 'imageUrl', 'kgmid', 'website', 'phone', 'source_file', 'weighted_score', 'is_24_7', 'openingHours_display', 'is_hours_estimated', 'display_title', 'latitude', 'longitude', 'quality_score', 'main_category', 'sub_category', 'tags', 'enriched_description']\n", "--- FİNAL OPERASYON RAPORU ---\n", "✅ Toplam İşlenen Kayıt: 497\n", "🍟 Yemek Yerleri: 123\n", "🏛️ Gezi Rehberi: 374\n", "❓ Kategori Atanamayan (Null): 0\n", "\n", "--- Gezi Rehberi Alt Kategori Detayları ---\n", "main_category\n", "History & Heritage 147\n", "Museum & Art 76\n", "Religious & Spiritual 61\n", "Sightseeing 47\n", "Nature & Parks 34\n", "Squares & Plazas 7\n", "Shopping & Traditional Bazaar 2\n", "Name: count, dtype: int64\n", "\n", "💾 Dosyalar 'ready' formatında kaydedildi.\n" ] } ], "source": [ "import pandas as pd\n", "import re\n", "import numpy as np\n", "\n", "\n", "# 1. READING AND MERGING FILES\n", "df1 = pd.read_json(r\"C:\\Users\\hilal\\Documents\\GitHub\\group-i-main\\nlp_utils\\222veri.json\")\n", "df2 = pd.read_json(r\"C:\\Users\\hilal\\Documents\\GitHub\\group-i-main\\nlp_utils\\first400.json\")\n", "\n", "df1['source_file'] = 'V1'\n", "df2['source_file'] = 'V2'\n", "\n", "df_master = pd.concat([df1, df2], ignore_index=True)\n", "# List all column names\n", "print(\"--- Master Dataset Columns ---\")\n", "print(list(df_master.columns))\n", "\n", "# Total Column\n", "print(f\"\\nToplam Sütun Sayısı: {len(df_master.columns)}\")\n", "\n", "# Unecessary Columns\n", "cop_sutunlar = [\n", " 'claimThisBusiness', 'googleFoodUrl', 'hotelAds', 'gasPrices', \n", " 'searchPageUrl', 'isAdvertisement', 'phoneUnformatted', \n", " 'additionalOpeningHours', 'updatesFromCustomers', 'checkInDate', \n", " 'checkOutDate', 'hotelStars', 'hotelDescription', 'reviewsDistribution','scrapedAt','locatedIn', 'language', 'countryCode','price','peopleAlsoSearch', 'placesTags', 'reviewsTags', 'imageCategories','reserveTableUrl'\n", "]\n", "\n", "# Filter and Delete\n", "actual_drop = [col for col in cop_sutunlar if col in df_master.columns]\n", "df_master = df_master.drop(columns=actual_drop)\n", "\n", "print(f\"Temizlik sonrası kalan sütun sayısı: {df_master.shape[1]}\")\n", "print(\"Gereksiz sütunlar başarıyla temizlendi \")\n", "\n", "\n", "#---------------------------------- KATEGORİ VE İSİM BAKIMINDAN TEMİZLİK ------------------------\n", "\n", "# 1. Silinecek Kategori Listesi (Tam Eşleşme)\n", "silinecek_kategoriler = [\n", " 'Travel agency', 'Tour agency', 'Sightseeing tour agency', 'Tour operator',\n", " 'Tourist information center', 'Car rental agency', 'Limousine service', \n", " 'Parking lot', 'Clinic', 'Medical center', 'Corporate office', \n", " 'Lodging', 'Hotel', 'Real estate agency'\n", "]\n", "\n", "# 2. Başlıkta (Title) Geçerse Silinecek Anahtar Kelimeler\n", "# (Büyük/küçük harfe duyarsız arama yapacağız)\n", "silinecek_isim_anahtarlari = [\n", " 'Turizm', 'Travel', 'Agency', 'Acenta', 'Clinic', 'Klinik', \n", " 'Rent a Car', 'Transfer', 'Rota', 'Medical', 'Hizmetleri','Dog','Tour Poınt','Toplanma Salonu','Silivrikapı Geçiş','Mother and baby'\n", "]\n", "\n", "# A. Kategoriye göre temizlik\n", "df_master = df_master[~df_master['categoryName'].isin(silinecek_kategoriler)]\n", "\n", "# B. Başlığa (Title) göre temizlik \n", "# (İsimde 'Turizm' veya 'Agency' gibi kelimeler geçen ticari yerleri uçurur)\n", "pattern = '|'.join(silinecek_isim_anahtarlari)\n", "df_master = df_master[~df_master['title'].str.contains(pattern, case=False, na=False)]\n", "\n", "print(f\"Ticari ve ilgisiz yerler temizlendi. Kalan mekan sayısı: {len(df_master)}\")\n", "\n", "#-------------------------ŞEHİR MAPPİNG---------------------------\n", "# Tüm şehir değerlerini ve kaçar kez geçtiklerini gör\n", "city_counts = df_master['city'].value_counts()\n", "\n", "\n", "\n", "def final_city_cleaner(text):\n", " if pd.isna(text): \n", " return \"Diğer\"\n", " \n", " # Küçük harfe çevirip temizleyelim\n", " text = str(text).lower().strip()\n", " \n", " # 1. Özel Eşleşmeler (Haritadan tam karşılığını bulma)\n", " # Verisetindeki spesifik hataları buraya ekledik\n", " special_mapping = {\n", " \"haskoy\": \"Beyoğlu\",\n", " \"bulbol area\": \"Beyoğlu\",\n", " \"molla güarani\": \"Fatih\",\n", " \"galataport\": \"Beyoğlu\",\n", " \"sultanahmet/fatih\": \"Fatih\",\n", " \"vezirhan/fatih/fatih\": \"Fatih\",\n", " \"eyüp/eyüpsultan\": \"Eyüpsultan\"\n", " }\n", " \n", " if text in special_mapping:\n", " return special_mapping[text]\n", " \n", " # 2. İçerik Taraması (Metnin içinde ilçe adı geçiyor mu?)\n", " # Arkadaşının eksik bıraktığı döngüsel kontrol kısmı burasıdır.\n", " districts_map = {\n", " \"fatih\": \"Fatih\",\n", " \"beyoğlu\": \"Beyoğlu\",\n", " \"beyoglu\": \"Beyoğlu\",\n", " \"kadıköy\": \"Kadıköy\",\n", " \"kadikoy\": \"Kadıköy\",\n", " \"beşiktaş\": \"Beşiktaş\",\n", " \"besiktas\": \"Beşiktaş\",\n", " \"üsküdar\": \"Üsküdar\",\n", " \"uskudar\": \"Üsküdar\",\n", " \"şişli\": \"Şişli\",\n", " \"sisli\": \"Şişli\",\n", " \"eyüpsultan\": \"Eyüpsultan\",\n", " \"eyüp\": \"Eyüpsultan\",\n", " \"zeytinburnu\": \"Zeytinburnu\"\n", " }\n", " \n", " # Metnin içinde herhangi bir ilçe anahtar kelimesi geçiyor mu kontrol et\n", " for key, value in districts_map.items():\n", " if key in text:\n", " return value\n", " \n", " # Eğer hiçbir eşleşme bulunamazsa metni düzgün formatta döndür\n", " return text.title()\n", "\n", "# Fonksiyonu ana veriseti üzerinde uygula\n", "df_master['city'] = df_master['city'].apply(final_city_cleaner)\n", "\n", "# Sonuçları kontrol et\n", "print(\"--- Temizlenmiş Şehir Dağılımı ---\")\n", "print(df_master['city'].value_counts())\n", "\n", "#---------------------- SÜTUN DOLDURMA İŞLEMLERİ --------------------------\n", "\n", "# Puan ve yorum sayısı sütunlarındaki boşlukları 0 ile doldur\n", "df_master['totalScore'] = df_master['totalScore'].fillna(0)\n", "df_master['reviewsCount'] = df_master['reviewsCount'].fillna(0)\n", "\n", "# Değişikliği doğrula\n", "print(f\"✅ Puan ve yorum sayısı boş olan satırlar 0 ile dolduruldu.\")\n", "print(f\"Kalan boş totalScore sayısı: {df_master['totalScore'].isna().sum()}\")\n", "#------------------ RATİNG KISMI İÇİN YENİ SÜTUN-------------------------------\n", "\n", "def calculate_weighted_rating(df):\n", " C = df['totalScore'].mean() # Genel ortalama\n", " m = df['reviewsCount'].quantile(0.25) # En az yorum alan %25'lik dilimin sınırı (Örn: 10 yorum)\n", " \n", " def bayesian_score(row):\n", " v = row['reviewsCount']\n", " R = row['totalScore']\n", " # Bayesian formülünü uygula\n", " return (R * v + C * m) / (v + m)\n", " \n", " return df.apply(bayesian_score, axis=1)\n", "\n", "df_master['weighted_score'] = calculate_weighted_rating(df_master)\n", "#----------------------------------------------------------------------\n", "\n", "\n", "# Telefon ve web sitesi sütunlarını standart yer tutucularla doldur\n", "df_master['phone'] = df_master['phone'].fillna(\"No contact information\")\n", "df_master['website'] = df_master['website'].fillna(\"Website not available\")\n", "\n", "print(\"✅ 'phone' ve 'website' sütunlarındaki boşluklar yer tutucu metinlerle dolduruldu.\")\n", "\n", "# Eğer street hala null ise neighborhood ile doldur, o da yoksa boş bırak\n", "df_master['street'] = df_master['street'].fillna(df_master['neighborhood']).fillna(\"\")\n", "\n", "print(\"✅ Street sütunu için fallback (neighborhood) uygulandı.\")\n", "\n", "# additionalInfo Null olanları boş liste ile doldurma\n", "df_master['additionalInfo'] = df_master['additionalInfo'].apply(lambda x: x if isinstance(x, (list, dict)) else [])\n", "#------------------------------ADDITIONAL INFO İÇİN SÜTUNLARA AYRIŞTIRMA VE TRUE/FALSE DEĞER ATAMA----------------------\n", "\n", "def discover_all_features(df):\n", " unique_features = set()\n", " \n", " for info in df['additionalInfo']:\n", " if isinstance(info, dict):\n", " for category, items in info.items():\n", " if isinstance(items, list):\n", " for item in items:\n", " # Sözlükteki anahtarları al (Örn: 'Wheelchair accessible entrance')\n", " for feature_name in item.keys():\n", " unique_features.add(f\"{category}_{feature_name}\")\n", " return list(unique_features)\n", "\n", "\n", "# Tüm verisetindeki özellikleri bulalım\n", "all_possible_columns = discover_all_features(df_master)\n", "print(f\"Toplam {len(all_possible_columns)} farklı özellik keşfedildi!\")\n", "\n", "\n", "def dynamic_filler(row, feature_path):\n", " # feature_path örn: \"Accessibility_Wheelchair accessible entrance\"\n", " category, feature_name = feature_path.split('_', 1)\n", " \n", " info = row.get('additionalInfo', {})\n", " if not isinstance(info, dict):\n", " return False\n", " \n", " category_list = info.get(category, [])\n", " if isinstance(category_list, list):\n", " for item in category_list:\n", " if item.get(feature_name) is True:\n", " return True\n", " return False\n", "\n", "# Tüm keşfedilen özellikler için döngüyle sütun oluştur\n", "for feature in all_possible_columns:\n", " # Sütun isimlerini temizleyelim (Boşlukları alt tire yapalım)\n", " clean_col_name = feature.replace(\" \", \"_\").replace(\":\", \"\").lower()\n", " df_master[clean_col_name] = df_master.apply(lambda r: dynamic_filler(r, feature), axis=1)\n", "\n", "print(\"✅ Tüm özellikler dinamik olarak sütunlara dönüştürüldü.\")\n", "\n", "#---------------------------OPENİNG HOURS'DAN 24_7 SÜTUNU OLUŞTURMA----------------------------------------------------------------\n", "\n", "def check_24_7(hours_list):\n", " if not isinstance(hours_list, list) or len(hours_list) == 0:\n", " return False\n", " # Tüm günlerin \"Open 24 hours\" olup olmadığını kontrol et\n", " return all(\"Open 24 hours\" in str(day.get('hours', '')) for day in hours_list)\n", "\n", "# 24/7 Sütununu oluştur\n", "df_master['is_24_7'] = df_master['openingHours'].apply(check_24_7)\n", "\n", "#---------------------------OPENİNG HOURS İÇİN BOŞ VERİLERİN DOLDURULMASI----------------------------------------------------------------\n", "def impute_opening_hours(row):\n", " # If openingHours already exists, keep the original data\n", " # (Checking for empty list [] or NaN)\n", " hours = row.get('openingHours')\n", " if isinstance(hours, list) and len(hours) > 0:\n", " return hours\n", " \n", " cat = str(row.get('main_category', ''))\n", " \n", " # Assign standard texts based on the category\n", " if cat == 'Religious & Spiritual':\n", " return \"Open according to prayer times (Generally 05:00 - 22:00)\"\n", " \n", " elif cat == 'Museum & Art' or cat == 'History & Heritage':\n", " return \"Generally 09:00 - 17:00 (Check before visiting, may be closed on Mondays)\"\n", " \n", " elif cat == 'Nature & Parks' or cat == 'Squares & Plazas':\n", " return \"Open 24 hours every day of the week\"\n", " \n", " elif cat == 'Shopping & Traditional Bazaar':\n", " return \"Generally 09:00 - 19:00 (May vary on Sundays)\"\n", " \n", " elif cat == 'Food & Drink':\n", " return \"Generally 08:00 - 22:00 (Varies by establishment)\"\n", " \n", " return \"Opening hours not specified\"\n", "\n", "# Apply to a new display column for safer UI rendering\n", "df_master['openingHours_display'] = df_master.apply(impute_opening_hours, axis=1)\n", "\n", "df_master['is_hours_estimated'] = df_master['openingHours'].apply(lambda x: not (isinstance(x, list) and len(x) > 0))\n", "\n", "#------------------------- subTitle SÜTUNUNU EMBEDDİNG İÇİN DİSPLAY TİTLE SÜTUNUNDA KULLANMA--------------------------------------------\n", "\n", "# Türkçe karakter güvenli Title Case fonksiyonu\n", "def tr_title(text):\n", " if pd.isna(text): return \"\"\n", " # Küçük bir hile: 'i' harflerini önce özel bir işarete çevirip sonra büyütebiliriz \n", " # veya doğrudan replace kullanabiliriz. En temizi:\n", " words = str(text).split()\n", " fixed_words = []\n", " for w in words:\n", " if w.startswith('i'):\n", " fixed_words.append('İ' + w[1:].lower())\n", " elif w.startswith('ı'):\n", " fixed_words.append('I' + w[1:].lower())\n", " else:\n", " fixed_words.append(w.capitalize())\n", " return \" \".join(fixed_words)\n", "\n", "# 1. Önce subTitle sütununa bu temizliği uygula\n", "df_master['subTitle'] = df_master['subTitle'].apply(tr_title)\n", "\n", "\n", "def smart_title_fix(row):\n", " eng_title = str(row.get('title', '')).strip()\n", " tr_title = str(row.get('subTitle', '')).strip()\n", " \n", " # EĞER subTitle boşsa, NaN ise veya title ile aynıysa (case-insensitive)\n", " if not tr_title or tr_title.lower() == eng_title.lower() or tr_title == \"None\":\n", " return eng_title\n", " \n", " # Sadece gerçekten farklı bir isim varsa birleştir: \"Kız Kulesi (Maiden's Tower)\"\n", " return f\"{tr_title} ({eng_title})\"\n", "\n", "# Yeni bir 'display_title' sütunu oluşturabilirsin\n", "df_master['display_title'] = df_master.apply(smart_title_fix, axis=1)\n", "\n", "#-----------------------LOCATİON SÜTUNU İÇİN LAT VE LONG SÜTUNLARI OLUŞTURMA---------------------------------------\n", "\n", "# 1. location sütunundaki verileri güvenli bir şekilde parçala\n", "# apply(pd.Series) içindeki değerleri otomatik olarak yeni sütunlara (lat ve lng) böler\n", "coords = df_master['location'].apply(lambda x: pd.Series(x) if isinstance(x, dict) else pd.Series({'lat': np.nan, 'lng': np.nan}))\n", "\n", "# 2. Yeni sütunları ana tabloya ekle\n", "df_master['latitude'] = coords['lat']\n", "df_master['longitude'] = coords['lng']\n", "\n", "# 3. Eski karmaşık sütunu sil\n", "df_master = df_master.drop(columns=['location'])\n", "\n", "print(f\"✅ Koordinatlar başarıyla ayrıştırıldı. Örnek:\\n{df_master[['latitude', 'longitude']].head()}\")\n", "\n", "#------------------- AYNI PLACEID SAHİP OLAN KAYITLARI SİLME İŞLEMİ -----------------------------------------------------------------------\n", "print(f\"--- BAŞLANGIÇ: {len(df_master)} satır ---\")\n", "\n", "# 2. SENİN KALİTE PUANLAMA FONKSİYONUN (Entegre Edildi)\n", "def calculate_record_score(row):\n", " score = 0\n", " # Description (Açıklama) doluysa en büyük puanı ver\n", " if pd.notna(row.get('description')) and len(str(row['description'])) > 5: \n", " score += 10\n", " # additionalInfo (Ek bilgiler) liste/sözlük ise ve doluysa\n", " info = row.get('additionalInfo')\n", " if isinstance(info, (dict, list)) and len(info) > 0: \n", " score += 8\n", " # openingHours (Çalışma saatleri)\n", " hours = row.get('openingHours')\n", " if isinstance(hours, list) and len(hours) > 0: \n", " score += 7\n", " # subTitle (Türkçe isim/tanım)\n", " if pd.notna(row.get('subTitle')) and len(str(row['subTitle'])) > 2: \n", " score += 5\n", " # reviewsCount ve totalScore (Güvenilirlik puanları)\n", " if pd.notna(row.get('reviewsCount')): score += 3\n", " if pd.notna(row.get('totalScore')): score += 2\n", " return score\n", "\n", "# 3. Skorlama İşlemini Uygula\n", "df_master['quality_score'] = df_master.apply(calculate_record_score, axis=1)\n", "\n", "# 4. KRİTİK ADIM: Skorlara göre büyükten küçüğe sırala\n", "# Böylece drop_duplicates yaptığımızda 'keep=first' her zaman en yüksek puanlıyı tutacak\n", "df_master = df_master.sort_values(by=['placeId', 'quality_score'], ascending=[True, False])\n", "\n", "\n", "duplicate_count = df_master['placeId'].duplicated().sum()\n", "print(f\"Tespit edilen mükerrer (duplicate) mekan sayısı: {duplicate_count}\")\n", "\n", "# Mükerrerleri bir görelim (En iyi ve en kötü hallerini karşılaştırmalı gösterir)\n", "duplicates_view = df_master[df_master['placeId'].duplicated(keep=False)]\n", "print(\"\\n--- Kalite Farkı Olan Mükerrer Kayıt Örnekleri ---\")\n", "print(duplicates_view[['title', 'placeId', 'quality_score', 'source_file']].head(10))\n", "\n", "# 6. TEKİLLEŞTİRME (Sadece en kaliteli olanı tutar)\n", "df_clean = df_master.drop_duplicates(subset=['placeId'], keep='first')\n", "df_master = df_clean.copy()\n", "print(f\"\\n--- SONUÇ ---\")\n", "print(f\"Toplam Silinen: {duplicate_count}\")\n", "print(f\"Final Veri Seti (Benzersiz Mekan): {len(df_master)}\")\n", "\n", "#-----------------------------------------------yabancı harf temizliği-------------------------------------------------------------------------\n", "# 1. Latin alfabesi, rakamlar ve temel işaretler dışındakileri yakalayan regex\n", "# (İıĞğÜüŞşÖöÇç VE âîûÂÎÛ eklenmiştir)\n", "non_latin_regex = r'[^\\x00-\\x7FİıĞğÜüŞşÖöÇçâîûÂÎÛ\\s\\d\\.,\\-\\(\\)\\&\\'\\!]'\n", "\n", "# 2. Bu karakterleri içeren satırları yakalayalım\n", "foreign_data = df_master[df_master['title'].str.contains(non_latin_regex, na=False, regex=True)].copy()\n", "\n", "print(f\"--- Toplam {len(foreign_data)} Adet Yabancı Alfabeli/Karakterli Veri Bulundu ---\\n\")\n", "\n", "if len(foreign_data) > 0:\n", " # Tüm listeyi görmek için kısıtlamayı kaldıralım\n", " pd.set_option('display.max_rows', None)\n", " \n", " print(\"--- Silinecek Yabancı Kayıtlar ---\")\n", " # Listeyi ekrana yazdır (Örn: Arapça, Çince veya Kiril isimli mekanlar)\n", " print(foreign_data[['title', 'city', 'source_file']])\n", " \n", " # Hangi dosyadan ne kadar yabancı veri geldiğini görelim\n", " print(\"\\n--- Kaynak Dosya Dağılımı ---\")\n", " print(foreign_data['source_file'].value_counts())\n", "\n", " # 3. TEMİZLİK: Bu yabancı kayıtları ana veriden (df_master) silelim\n", " df_master = df_master.drop(foreign_data.index)\n", " print(f\"\\n✅ Yabancı alfabeli {len(foreign_data)} kayıt temizlendi.\")\n", " \n", " pd.reset_option('display.max_rows')\n", "else:\n", " print(\"Latin dışı hiçbir karakter bulunamadı.\")\n", "\n", "# -------------------------------------------- CATEGORY MAPPING & CLEANING -----------------------------------------\n", "\n", "# 1. Kara Liste Tanımlama (Gezi rehberinde asla olmaması gerekenler)\n", "kara_liste = [\n", " 'Turizm', 'Tourism', 'Agency', 'Travel', 'Clinic', 'Coiffeur', 'Halı', 'Carpet', \n", " 'Workshop', 'Medical', 'Acente', 'Gayrimenkul', 'Emlak', 'Acentesi', 'Global', 'AGN TURİZM','otobüsleri', 'kalkış noktası', 'bus station', 'departure'\n", "]\n", "\n", "# 2. Gelişmiş Mapping Kuralları (Sıralama Önceliği Korunarak Güncellendi)\n", "# NOT: 'Food & Drink' en sondadır. Böylece tarihi bir kafe önce 'History' olarak yakalanır.\n", "mapping_rules = {\n", "\n", " 'Food & Drink': [\n", " 'restaurant', 'cafe', 'coffee', 'kebab', 'bakery', 'pub', 'bar', 'breakfast', 'lokanta', 'kahve', 'fırın', \n", " 'tatlıcı', 'pastane', 'meyhane', 'döner', 'pizza', 'steakhouse', 'grill', 'bistro', 'patisserie', 'brasserie', \n", " 'cafeteria', 'mutfağı', 'sofrası', 'kebap', 'köfte', 'dürüm', 'restorant', 'winery', 'gastronomi'\n", " ],\n", "\n", " 'Museum & Art': [\n", " 'museum', 'art', 'gallery', 'exhibition', 'müze','müzesi', 'galeri', 'sergi', 'sanat', 'atolye', 'atölye',\n", " 'theater', 'opera', 'sinema', 'kütüphane', 'library', 'kültür merkezi', 'cultural center','mural', 'murral', 'murales', 'streetart', 'graffiti', 'stairs', 'staircase', 'merdiven'\n", " ],\n", " \n", " 'Religious & Spiritual': [\n", " 'mosque', 'church', 'synagogue', 'tomb', 'hazire', 'cemetery', 'cami','camii' 'kilise', 'havra', \n", " 'türbe', 'hazire', 'mezarlık', 'dergah', 'tekke', 'namazgah', 'kabri', 'mezarı', 'kabristan', \n", " 'mausoleum', 'cathedral'\n", " ],\n", " \n", " 'History & Heritage': [\n", " 'historical', 'monument', 'castle', 'palace', 'bridge', 'tower', 'landmark', 'saray', 'kale', 'kule', 'köprü', 'anıt', \n", " 'tarihi', 'sarnıç', 'cistern', 'hamam', 'bath', 'aqueduct', 'su kemeri', 'fountain', 'çeşme', 'sebil', 'terazi', \n", " 'obelisk', 'dikilitaş', 'köşk', 'pavilion', 'mansion', 'kasrı', 'sur', 'kapısı', 'gate', 'fortress', 'walls', \n", " 'medrese', 'madrasa', 'taşı', 'tekfur', 'bedesten','feneri', 'lighthouse', 'anıtı', 'memorial', 'statue', 'heykel', r'\\bhanı\\b' # \\bhanı\\b ile hancı kelimesini engelledik\n", " ],\n", " \n", " 'Shopping & Traditional Bazaar': ['bazaar', 'market', 'han', 'çarşı', 'pazar', 'shopping', 'mall', 'bedesten', 'arasta', 'pasaj'],\n", " \n", " 'Nature & Parks': [\n", " 'park', 'garden', 'scenic', 'nature', 'forest', 'island', 'bahçe', 'koru', 'ada', 'doğa', 'manzara', \n", " 'hill', 'tepe', 'sahil', 'coast', 'plaj', 'yürüyüş yolu', 'köy yolu','sunset', 'günbatımı', 'spot', 'seyir' # 'yol' kelimesi adreslerle karışmaması için spesifikleştirildi\n", " ],\n", " \n", " 'Squares & Plazas': ['plaza', 'square', 'meydan', 'alanı', 'iskelesi', 'iskeleye', 'eminönü', 'beşiktaş']\n", " \n", " \n", "}\n", "\n", "def evliyapp_mapper(row):\n", " # Verileri alalım\n", " title = str(row.get('title', '')).lower()\n", " sub_title = str(row.get('subTitle', '')).lower()\n", " cat_main = str(row.get('categoryName', '')).lower()\n", " \n", " # Categories listesini güvenli şekilde birleştir\n", " cats_list = row.get('categories', [])\n", " cats_joined = \" \".join(cats_list).lower() if isinstance(cats_list, list) else \"\"\n", " \n", " # --- ADIM 1: Kara Liste Kontrolü (Sadece Başlık ve subTitle üzerinden) ---\n", " if any(word.lower() in title or word.lower() in sub_title for word in kara_liste):\n", " return \"DELETE\", \"Blacklist\"\n", "\n", " # --- ADIM 2: Süper Birleşik Metin (Combined) ---\n", " # Her yerden ipucu topluyoruz: Başlık + Türkçe İsim + Ana Kategori + Kategori Listesi\n", " combined = f\"{title} {sub_title} {cat_main} {cats_joined}\"\n", "\n", " # --- ADIM 3: Kategori Mapping ---\n", " for main_cat, keywords in mapping_rules.items():\n", " for word in keywords:\n", " # Eğer kelime zaten regex (\\b) içeriyorsa doğrudan kullan, yoksa oluştur\n", " if r'\\b' in word:\n", " pattern = word\n", " elif len(word) <= 4:\n", " pattern = r'\\b' + re.escape(word) + r'\\b'\n", " else:\n", " pattern = re.escape(word)\n", " \n", " if re.search(pattern, combined):\n", " return main_cat, word.title()\n", " \n", " return 'Sightseeing', 'General'\n", "\n", "# 3. İŞLEMİ UYGULA\n", "results = df_master.apply(lambda r: pd.Series(evliyapp_mapper(r)), axis=1)\n", "df_master['main_category'] = results[0]\n", "df_master['sub_category'] = results[1]\n", "\n", "# 4. TEMİZLİK: Kara listedekileri uçur\n", "df_master = df_master[df_master['main_category'] != \"DELETE\"].copy()\n", "#----------------------DESCRIPTION SÜTUNUNU GÜNCELLEME---------------------------------------\n", "\n", "def generate_display_description(row):\n", " # 1. Mevcut ve yeterli bir açıklama varsa onu koru\n", " original = str(row.get('description', '')).strip()\n", " if pd.notna(row.get('description')) and len(original) > 25:\n", " return original\n", "\n", " # 2. Temel Değişkenler\n", " title = row.get('title', 'This location')\n", " city = row.get('city', 'Istanbul')\n", " main_cat = str(row.get('main_category', 'point of interest')).lower()\n", " score = row.get('totalScore')\n", " \n", " # 3. Kategoriye Özel Estetik Cümleler\n", " if main_cat == 'food & drink':\n", " sentence = f\"Discover the local flavors at {title}, a popular spot in {city} known for its inviting atmosphere.\"\n", " \n", " elif main_cat == 'religious & spiritual':\n", " sentence = f\"{title} stands as a serene spiritual site in {city}, offering visitors a peaceful retreat and historical depth.\"\n", " \n", " elif main_cat == 'museum & art':\n", " sentence = f\"Immerse yourself in cultural heritage at {title}, where {city}'s artistic and historical legacy comes to life.\"\n", " \n", " elif main_cat == 'history & heritage':\n", " sentence = f\"{title} is a landmark of historical significance in {city}, reflecting the rich architectural tapestry of the area.\"\n", " \n", " elif main_cat == 'shopping & traditional bazaar':\n", " sentence = f\"Experience the authentic vibe of {city} at {title}, a vibrant destination perfect for traditional shopping and local crafts.\"\n", " \n", " elif main_cat == 'nature & parks':\n", " sentence = f\"Enjoy a breath of fresh air at {title}, a beautiful green space in {city} ideal for relaxation and outdoor moments.\"\n", " \n", " elif main_cat == 'squares & plazzas': # Yazım hatası olasılığına karşı kontrol\n", " sentence = f\"{title} is a central landmark in {city}, serving as a lively meeting point and a great spot to observe city life.\"\n", " \n", " else:\n", " sentence = f\"{title} is a must-visit {main_cat} in {city}, contributing to the unique and diverse charm of the district.\"\n", "\n", " # 4. SOSYAL KANIT EKLEME (Social Proof)\n", " # Eğer mekanın puanı yüksekse açıklamaya bir 'güven' cümlesi ekleyelim\n", " if pd.notna(score) and score >= 4.2:\n", " sentence += f\" It is highly recommended by visitors with a remarkable {score} rating.\"\n", "\n", " return sentence\n", "\n", "# İşlemi uygula\n", "df_master['description'] = df_master.apply(generate_display_description, axis=1)\n", "\n", "#------------------------TAGS SÜTUNU OLUŞTURMA--------------------------------------\n", "\n", "# 1. ÖNCE SÖZLÜKLERİ TANIMLIYORUZ (Fonksiyonun dışında dursunlar)\n", "# Eşleşme garantisi için anahtarları (keys) küçük harf yapıyoruz\n", "raw_mapping = {\n", " # Accessibility\n", " \"Accessibility_Wheelchair accessible entrance\": \"Wheelchair Accessible\",\n", " \"Accessibility_Wheelchair-accessible entrance\": \"Wheelchair Accessible\",\n", " \"Accessibility_Wheelchair accessible parking lot\": \"Wheelchair Parking\",\n", " \"Accessibility_Wheelchair-accessible toilet\": \"Wheelchair Accessible\",\n", " \"Accessibility_Wheelchair accessible restroom\": \"Wheelchair Accessible\",\n", " \n", " # Atmosphere\n", " \"Atmosphere_Cosy\": \"Cozy\",\n", " \"Atmosphere_Cozy\": \"Cozy\",\n", " \"Atmosphere_Trending\": \"Trendy\",\n", " \"Atmosphere_Trendy\": \"Trendy\",\n", " \"Atmosphere_Historic\": \"Historic\",\n", " \"Atmosphere_History\": \"Historic\",\n", " \n", " # Amenities & Service\n", " \"Amenities_Toilet\": \"Restroom\",\n", " \"Amenities_Public restroom\": \"Restroom\",\n", " \"Amenities_Wi-Fi\": \"Free Wi-Fi\",\n", " \"Service options_Takeaway\": \"Takeout\",\n", " \"Service options_Takeout\": \"Takeout\",\n", " \n", " # Children\n", " \"Children_Good for kids\": \"Child Friendly\",\n", " \"Children_Kid-friendly activities\": \"Child Friendly\",\n", " \"Children_Good for kids birthday\": \"Child Friendly\",\n", " \n", " # Payments & Other\n", " \"Payments_Admission fee\": \"Entry Fee Required\", # İki girişin vardı, 'Required' olanı seçtim\n", " \"Offerings_Halal food\": \"Halal Options\",\n", " \"Highlights_Rooftop seating\": \"Rooftop\",\n", " \"Parking_lot\": \"Parking Available\",\n", " \"Parking_Paid_parking_lot\": \"Paid Parking\",\n", " \"Payments_Credit_cards\": \"Credit Cards\",\n", " \"Pets_Dogs allowed inside\": \"Dog Friendly\",\n", " \"Pets_Dogs allowed outside\": \"Dog Friendly\"\n", "}\n", "\n", "# Kodun içindeki karşılaştırma için mapping'i normalize ediyoruz\n", "tag_mapping = {k.lower().replace(\" \", \"_\").replace(\":\", \"\"): v for k, v in raw_mapping.items()}\n", "\n", "# UI'da gizlemek isteyebileceğin ama veride tuttuğumuz liste\n", "noise_tags = [\"Credit Cards\", \"Debit Cards\", \"Nfc Mobile Payments\", \n", " \"Dine-In\", \"Table Service\", \"Seating\", \"Groups\", \"Tourists\",\n", " \"Lunch\", \"Dinner\", \"Brunch\",'Food', 'Service', 'Dining']\n", "\n", "# 2. BİRLEŞTİRİLMİŞ ANA FONKSİYON\n", "def generate_all_tags(row, feature_columns, rules):\n", " tags = set()\n", " \n", " # --- BÖLÜM A: KATEGORİ MAPPING (Kelimelerden kategori yakalama) ---\n", " title = str(row.get('title', '')).lower()\n", " sub_title = str(row.get('subTitle', '')).lower()\n", " cat_name = str(row.get('categoryName', '')).lower()\n", " cats_list = row.get('categories', [])\n", " cats_joined = \" \".join(cats_list).lower() if isinstance(cats_list, list) else \"\"\n", " combined_text = f\"{title} {sub_title} {cat_name} {cats_joined}\"\n", " \n", " for main_cat, keywords in rules.items():\n", " for word in keywords:\n", " pattern = r'\\b' + re.escape(word) + r'\\b' if len(word) <= 4 else re.escape(word)\n", " if re.search(pattern, combined_text):\n", " tags.add(main_cat)\n", " break\n", "\n", " # --- BÖLÜM B: OPTİMİZE EDİLMİŞ DİNAMİK ÖZELLİKLER (144 Sütun İşleme) ---\n", " for col in feature_columns:\n", " if row.get(col) == True:\n", " # 1. ADIM: Önce sözlükte (tag_mapping) özel bir karşılığı var mı?\n", " # Sütun isimleri küçük harf olduğu için tag_mapping de küçük harf bakıyor\n", " if col in tag_mapping:\n", " clean_tag = tag_mapping[col]\n", " else:\n", " # 2. ADIM: Sözlükte yoksa, dinamik temizlik yap.\n", " if \"_\" in col:\n", " clean_tag = col.split(\"_\", 1)[1] \n", " else:\n", " clean_tag = col\n", " \n", " # Alt tireleri boşluğa çevir ve Baş Harflerini Büyüt\n", " clean_tag = clean_tag.replace(\"_\", \" \").title().strip()\n", " \n", " # Özel düzeltmeler (lot -> Parking Lot gibi)\n", " if clean_tag == \"Lot\": clean_tag = \"Parking Lot\"\n", " if clean_tag == \"Cards\": clean_tag = \"Credit Cards\"\n", " \n", " # 3. ADIM: Son kontrol (Anlamsız çok kısa tagleri engelle)\n", " if len(clean_tag) > 2:\n", " tags.add(clean_tag)\n", "\n", " return list(tags)\n", "\n", "# --- UYGULAMA ---\n", "\n", "# Dinamik sütun listesini hazırla - BURAYA .lower() EKLENDİ (Kritik Düzeltme)\n", "dynamic_cols = [c.replace(\" \", \"_\").replace(\":\", \"\").lower() for c in all_possible_columns]\n", "existing_dynamic_cols = [col for col in dynamic_cols if col in df_master.columns]\n", "\n", "# Kaç sütun bulunduğunu kontrol etmek için (Opsiyonel)\n", "print(f\"🔍 Eşleşen dinamik sütun sayısı: {len(existing_dynamic_cols)}\")\n", "\n", "# Tags sütununu oluştur\n", "df_master['tags'] = df_master.apply(lambda r: generate_all_tags(r, existing_dynamic_cols, mapping_rules), axis=1)\n", "\n", "# Sadece tags listesi boş olan ve kategorisi 'General' kalan 'noise' mekanları temizle\n", "df_master = df_master[~((df_master['tags'].map(len) == 0) & (df_master['main_category'] == 'Sightseeing (General)'))]\n", "\n", "print(\"✅ 'tags' sütunu başarıyla zenginleştirildi ve temizlik yapıldı.\")\n", "print(f\"🧹 Kalan net mekan sayısı: {len(df_master)}\")\n", "\n", "#------------------------------------PGVECTOR İÇİN ENRİCHED DESCRIPTION-----------------------------------------------------------\n", "\n", "\n", "def synthesize_enriched_description(row):\n", " # 1. Temel Kimlik (Önceden hazırladığımız display_title'ı kullanalım)\n", " title = str(row.get('display_title', row.get('title', '')))\n", " main_cat = str(row.get('main_category', ''))\n", " sub_cat = str(row.get('sub_category', ''))\n", " \n", " # 2. Etiketler (Senin 144 temizlenmiş tag'in buraya geliyor)\n", " tags_list = row.get('tags', [])\n", " tags_text = \", \".join(tags_list) if isinstance(tags_list, list) else \"\"\n", " \n", " # 3. Saat Bilgisi (Buradaki mantığın çok iyi, dokunmuyoruz)\n", " hours = row.get('openingHours_display', '')\n", " if isinstance(hours, list):\n", " hours = \"Open 24 hours\" if row.get('is_24_7') else \"Check hours online\"\n", " \n", " score = str(row.get('totalScore', ''))\n", " original_desc = str(row.get('description', ''))\n", " \n", " # 4. SENTEZ\n", " # Başlık zaten display_title ile temiz geldiği için parantez kontrolünü burada yapmana gerek kalmadı\n", " enriched = f\"{title}. Category: {main_cat} ({sub_cat}). \"\n", " \n", " if tags_text:\n", " enriched += f\"Features: {tags_text}. \"\n", " \n", " if hours:\n", " enriched += f\"Status: {hours}. \"\n", " \n", " enriched += f\"Info: {original_desc} \"\n", " \n", " if score and score != '0':\n", " enriched += f\"Rating: {score}/5.\"\n", " \n", " return enriched\n", "\n", "# --- Uygulama Adımları ---\n", "\n", "# Enriched sütununu oluşturalım\n", "df_master['enriched_description'] = df_master.apply(synthesize_enriched_description, axis=1)\n", "\n", "print(\"✅ enriched_description sütunu dinamik özelliklerle birlikte oluşturuldu.\")\n", "\n", "#---------------------------------------------------------------------------------------------------------------------------------\n", "final_cols = [col for col in df_master.columns if col not in existing_dynamic_cols]\n", "df_final = df_master[final_cols]\n", "\n", "# Değişimi kontrol edelim\n", "print(f\"🚀 Eski sütun sayısı: {len(df_master.columns)}\")\n", "print(f\"✨ Yeni sütun sayısı: {len(df_final.columns)}\")\n", "print(f\"📂 Kalan Sütunlar: {df_final.columns.tolist()}\")\n", "\n", "# 5. AYRIŞTIRMA (Refined Version)\n", "# .copy() kullanman çok doğru (SettingWithCopyWarning'i önler)\n", "# .reset_index() ise ileride mapping yaparken indeks karmaşasını önler\n", "\n", "df_food_app = df_master[df_master['main_category'] == 'Food & Drink'].copy().reset_index(drop=True)\n", "\n", "# Geri kalan her şeyi Sightseeing'e atıyoruz ama Food & Drink olmayanları filtreleyerek\n", "df_sightseeing_app = df_master[df_master['main_category'] != 'Food & Drink'].copy().reset_index(drop=True)\n", "\n", "# 6. EKSTRA KONTROL: Kategori Atanamamış Kayıtlar\n", "# Eğer main_category null kaldıysa bu kayıtlar 'Genel' bile olamamış demektir\n", "null_cats = df_master[df_master['main_category'].isna()]\n", "\n", "# 7. RAPORLAMA VE HATA AYIKLAMA (DEBUGGING)\n", "print(f\"--- FİNAL OPERASYON RAPORU ---\")\n", "print(f\"✅ Toplam İşlenen Kayıt: {len(df_master)}\")\n", "print(f\"🍟 Yemek Yerleri: {len(df_food_app)}\")\n", "print(f\"🏛️ Gezi Rehberi: {len(df_sightseeing_app)}\")\n", "print(f\"❓ Kategori Atanamayan (Null): {len(null_cats)}\")\n", "\n", "print(f\"\\n--- Gezi Rehberi Alt Kategori Detayları ---\")\n", "print(df_sightseeing_app['main_category'].value_counts())\n", "\n", "# 8. SIGHTSEEING (GENEL) ANALİZİ\n", "# Bu kısım senin 'mapping_rules' listeni güncellemen için bir altın madeni!\n", "genel_sayisi = len(df_sightseeing_app[df_sightseeing_app['main_category'] == 'Sightseeing (Genel)'])\n", "if genel_sayisi > 0:\n", " print(f\"\\n⚠️ 'Sightseeing (Genel)' grubunda {genel_sayisi} mekan var.\")\n", " print(\"En sık rastlanan kategoriName örnekleri:\")\n", " print(df_sightseeing_app[df_sightseeing_app['main_category'] == 'Sightseeing (Genel)']['categoryName'].value_counts().head(10))\n", "\n", "# 9. DOSYALARI KAYDET (Daha sonra data_ingestion.py ile yüklemek için)\n", "df_food_app.to_json('data_food_ready.json', orient='records', force_ascii=False)\n", "df_sightseeing_app.to_json('data_sightseeing_ready.json', orient='records', force_ascii=False)\n", "print(\"\\n💾 Dosyalar 'ready' formatında kaydedildi.\")" ] }, { "cell_type": "code", "execution_count": 4, "id": "020fcd38", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "--- MEKAN ANALİZİ: Solera Winery ---\n", "\n", "📍 ŞEHİR: Beyoğlu\n", "🏷️ TAGS (Filtreler):\n", "['For Solo Dining', 'Usually Difficult To Find A Space', 'Tourists', 'Options Onsite Services', 'Trendy', 'Nfc Mobile Payments', 'Great Dessert', 'Options Dessert', 'Great Wine List', 'Great Cocktails', 'Beer', 'Wine', 'Restroom', 'Late-Night Food', 'Options Dine-In', 'Alcohol', 'For Dinner', 'Credit Cards', 'Options Seating', 'Takeout', 'Options Table Service', 'Paid Parking', 'Bar Onsite', 'Options Outdoor Seating', 'Coffee', 'Food & Drink', 'Accepts Reservations', 'Debit Cards', 'Casual', 'Groups', 'Hard Liquor', 'Options Dinner', 'Dinner Reservations Recommended', 'Cozy', 'Cocktails']\n", "\n", "📝 UI DESCRIPTION (Kullanıcıya):\n", "Discover the local flavors at Solera Winery, a popular spot in Beyoğlu known for its inviting atmosphere. It is highly recommended by visitors with a remarkable 4.7 rating.\n", "\n", "🧠 ENRICHED DESCRIPTION (AI/Search):\n", "Solera Winery. Category: Food & Drink (Restaurant). Features: For Solo Dining, Usually Difficult To Find A Space, Tourists, Options Onsite Services, Trendy, Nfc Mobile Payments, Great Dessert, Options Dessert, Great Wine List, Great Cocktails, Beer, Wine, Restroom, Late-Night Food, Options Dine-In, Alcohol, For Dinner, Credit Cards, Options Seating, Takeout, Options Table Service, Paid Parking, Bar Onsite, Options Outdoor Seating, Coffee, Food & Drink, Accepts Reservations, Debit Cards, Casual, Groups, Hard Liquor, Options Dinner, Dinner Reservations Recommended, Cozy, Cocktails. Status: Check hours online. Info: Discover the local flavors at Solera Winery, a popular spot in Beyoğlu known for its inviting atmosphere. It is highly recommended by visitors with a remarkable 4.7 rating. Rating: 4.7/5.\n", "\n", "⭐ WEIGHTED SCORE: 4.69\n" ] } ], "source": [ "# Sütun genişliği kısıtlamasını kaldıralım ki metinler kesilmesin\n", "pd.set_option('display.max_colwidth', None)\n", "\n", "# Rastgele bir örnek seçelim\n", "ornek = df_master.sample(1).iloc[0]\n", "\n", "print(f\"--- MEKAN ANALİZİ: {ornek['display_title']} ---\")\n", "print(f\"\\n📍 ŞEHİR: {ornek['city']}\")\n", "print(f\"🏷️ TAGS (Filtreler):\\n{ornek['tags']}\")\n", "print(f\"\\n📝 UI DESCRIPTION (Kullanıcıya):\\n{ornek['description']}\")\n", "print(f\"\\n🧠 ENRICHED DESCRIPTION (AI/Search):\\n{ornek['enriched_description']}\")\n", "print(f\"\\n⭐ WEIGHTED SCORE: {ornek['weighted_score']:.2f}\")" ] }, { "cell_type": "code", "execution_count": 5, "id": "4ff291d4", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "--- Sightseeing (Genel) İçindeki En Sık Geçen Google Kategorileri ---\n", "categoryName\n", "Tourist attraction 47\n", "Name: count, dtype: int64\n", "\n", "--- Örnek Mekan İsimleri (Buradan anahtar kelime yakalayacağız) ---\n", " title subTitle \\\n", "229 Colorful umbrella's \n", "264 Colorful houses \n", "369 Cats House \n", "66 Balat \n", "146 Orient Bosphorus Dinner Cruise&Turkish Night Show \n", "299 \"Tropical Arts and Nations Group\" \n", "247 Colorful houses \n", "397 Topkapı Tepesi \n", "386 Alley \n", "563 Cartel Estambul \n", "439 Karakolkane \n", "101 ILoveIstanbul \n", "396 Spirit tree \n", "235 Colourful Buildings of Cihangir \n", "13 Visit Istanbul Visit Istanbul \n", "578 . \n", "329 Rainbow Steps \n", "475 Yürekçıkmazı \n", "366 SUHULET \n", "119 Istanbul center \n", "287 RİXTUR \n", "341 Axel Mengü \n", "250 Tersane Istanbul bosphorus view point \n", "368 Yste-en-boules \n", "395 Romanos Kapisi \n", "579 KızKulesi Kabataş Gişesi \n", "585 Escalones de colores en Balata \n", "590 Mismarcı Yokuşu Terası \n", "554 Gülhane sarnıcı \n", "333 Boğaziçi Yoltur İskelesi \n", "\n", " categoryName \n", "229 Tourist attraction \n", "264 Tourist attraction \n", "369 Tourist attraction \n", "66 Tourist attraction \n", "146 Tourist attraction \n", "299 Tourist attraction \n", "247 Tourist attraction \n", "397 Tourist attraction \n", "386 Tourist attraction \n", "563 Tourist attraction \n", "439 Tourist attraction \n", "101 Tourist attraction \n", "396 Tourist attraction \n", "235 Tourist attraction \n", "13 Tourist attraction \n", "578 Tourist attraction \n", "329 Tourist attraction \n", "475 Tourist attraction \n", "366 Tourist attraction \n", "119 Tourist attraction \n", "287 Tourist attraction \n", "341 Tourist attraction \n", "250 Tourist attraction \n", "368 Tourist attraction \n", "395 Tourist attraction \n", "579 Tourist attraction \n", "585 Tourist attraction \n", "590 Tourist attraction \n", "554 Tourist attraction \n", "333 Tourist attraction \n" ] } ], "source": [ "# Sightseeing (Genel) olanları detaylı inceleme\n", "genel_listesi = df_master[df_master['main_category'] == 'Sightseeing'].copy()\n", "\n", "print(\"--- Sightseeing (Genel) İçindeki En Sık Geçen Google Kategorileri ---\")\n", "print(genel_listesi['categoryName'].value_counts().head(10))\n", "\n", "print(\"\\n--- Örnek Mekan İsimleri (Buradan anahtar kelime yakalayacağız) ---\")\n", "print(genel_listesi[['title', 'subTitle', 'categoryName']].head(30))" ] }, { "cell_type": "code", "execution_count": 6, "id": "fc0364b5", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "--- EN KALİTELİ 5 VERİ ÖRNEĞİ ---\n", " display_title \\\n", "48 Çemberlitaş Hamamı (Cemberlitas Hammam) \n", "330 Kadırga Parkı (Kadırga Park) \n", "30 Rahmi M. Koç Müzesi (Rahmi M. Koç Museum) \n", "619 İbb Maçka Demokrasi Parkı (İBB Maçka Democracy Park) \n", "29 Galata Mevlevihanesi Müzesi (Galata Mevlevihanesi Museum) \n", "\n", " main_category \\\n", "48 History & Heritage \n", "330 Nature & Parks \n", "30 Museum & Art \n", "619 Nature & Parks \n", "29 Museum & Art \n", "\n", " tags \\\n", "48 [Sauna, History & Heritage, Options Onsite Services, Paid Parking] \n", "330 [Playground, Child Friendly, Wheelchair Parking, Nature & Parks, Swings, Wheelchair Accessible, Hiking, Kid-Friendly Hikes, Picnics, Options Onsite Services, Slides] \n", "30 [Child Friendly, Wheelchair Parking, Restaurant, Paid Parking, Museum & Art, Wheelchair Accessible, Has Changing Table(S), Restroom, Entry Fee Required, Free Parking Lot, Options Onsite Services] \n", "619 [Playground, Child Friendly, Wheelchair Parking, Nature & Parks, Options Onsite Services, Cycling Lanes, Swings, Wheelchair Accessible, Debit Cards, Dog Park, Restroom, Hiking, Kid-Friendly Hikes, Picnics, Skateboarding Area, Picnic Tables, Slides] \n", "29 [Child Friendly, Live Performances, Museum & Art, Wheelchair Accessible, Restroom, Entry Fee Required, Options Onsite Services] \n", "\n", " weighted_score \n", "48 4.002514 \n", "330 4.300193 \n", "30 4.799460 \n", "619 4.499495 \n", "29 4.498750 \n" ] } ], "source": [ "# En yüksek kaliteli 5 mekanı görelim\n", "print(\"--- EN KALİTELİ 5 VERİ ÖRNEĞİ ---\")\n", "display_cols = ['display_title', 'main_category', 'tags', 'weighted_score']\n", "print(df_master.sort_values('quality_score', ascending=False)[display_cols].head(5))" ] }, { "cell_type": "code", "execution_count": 7, "id": "cc9a5c9a", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "city\n", "Fatih 208\n", "Beyoğlu 68\n", "Üsküdar 35\n", "Kadıköy 35\n", "Eyüpsultan 9\n", "Beşiktaş 7\n", "Şişli 6\n", "Zeytinburnu 5\n", "Diğer 1\n", "Name: count, dtype: int64\n" ] } ], "source": [ "import pandas as pd\n", "\n", "# Veriyi oku\n", "df = pd.read_json('data_sightseeing_ready.json')\n", "\n", "# City sütunundaki değerlerin sayılarını bul\n", "sehir_sayilari = df['city'].value_counts()\n", "\n", "# Ekrana yazdır\n", "print(sehir_sayilari)" ] }, { "cell_type": "code", "execution_count": 8, "id": "6be8ff94", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "==================================================\n", "REPORT FOR: MAIN CATEGORIES\n", "==================================================\n", "Total Unique Values: 7\n", "------------------------------\n", "Most Frequent Values:\n", "main_category\n", "History & Heritage 147\n", "Museum & Art 76\n", "Religious & Spiritual 61\n", "Sightseeing 47\n", "Nature & Parks 34\n", "Squares & Plazas 7\n", "Shopping & Traditional Bazaar 2\n", "Name: count, dtype: int64\n", "------------------------------\n", "\n", "==================================================\n", "REPORT FOR: SUB CATEGORIES\n", "==================================================\n", "Total Unique Values: 53\n", "------------------------------\n", "Most Frequent Values:\n", "sub_category\n", "Historical 125\n", "General 47\n", "Museum 44\n", "Mosque 43\n", "Park 28\n", "Mural 8\n", "Stairs 5\n", "Murral 5\n", "Art 4\n", "Square 4\n", "Türbe 4\n", "Sunset 3\n", "Church 3\n", "Merdiven 3\n", "Feneri 2\n", "Tomb 2\n", "Eminönü 2\n", "Mezarı 2\n", "Bridge 2\n", "Bazaar 2\n", "Name: count, dtype: int64\n", "------------------------------\n", "\n", "==================================================\n", "REPORT FOR: NEIGHBORHOODS\n", "==================================================\n", "Total Unique Values: 97\n", "------------------------------\n", "Most Frequent Values:\n", "neighborhood\n", "Cankurtaran 32\n", "Balat 19\n", "Rasimpaşa 14\n", "Caferağa 13\n", "Kılıçali Paşa 12\n", "Mimar Sinan 10\n", "Binbirdirek 10\n", "Hoca Paşa 9\n", "Yedikule 9\n", "Süleymaniye 9\n", "Dervişali 8\n", "Topkapı 8\n", "Ayvansaray 7\n", "Kemankeş Karamustafa Paşa 7\n", "Rüstem Paşa 7\n", "Sultan Ahmet 6\n", "Bereketzade 6\n", "Alemdar 6\n", "Kocamustafapaşa 5\n", "Osmanağa 5\n", "Name: count, dtype: int64\n", "------------------------------\n", "\n", "==================================================\n", "REPORT FOR: ALL_TAGS\n", "==================================================\n", "Total Unique Tags: 52\n", "------------------------------\n", "Top 20 Most Used Tags:\n", " Count\n", "Child Friendly 248\n", "History & Heritage 175\n", "Wheelchair Accessible 170\n", "Restroom 112\n", "Wheelchair Parking 107\n", "Options Onsite Services 84\n", "Museum & Art 76\n", "Religious & Spiritual 64\n", "Nature & Parks 42\n", "Entry Fee Required 25\n", "Credit Cards 23\n", "Paid Parking 21\n", "Debit Cards 20\n", "Playground 20\n", "Getting Tickets In Advance Recommended 17\n", "Swings 16\n", "Hiking 16\n", "Cycling Lanes 16\n", "Picnics 15\n", "Kid-Friendly Hikes 15\n", "------------------------------\n" ] } ], "source": [ "import pandas as pd\n", "from collections import Counter\n", "\n", "# 1. Load the data from our JSON file\n", "file_path = 'data_sightseeing_ready.json' \n", "df = pd.read_json(file_path)\n", "\n", "df['main_category'] = df['main_category'].fillna('Unknown')\n", "df['sub_category'] = df['sub_category'].fillna('Unknown')\n", "df['neighborhood'] = df['neighborhood'].fillna('Unknown')\n", "\n", "def get_stats(series, name):\n", " unique_values = series.unique()\n", " counts = series.value_counts()\n", " print(f\"\\n{'='*50}\")\n", " print(f\"REPORT FOR: {name.upper()}\")\n", " print(f\"{'='*50}\")\n", " print(f\"Total Unique Values: {len(unique_values)}\")\n", " print(\"-\" * 30)\n", " print(\"Most Frequent Values:\")\n", " print(counts.head(20)) # Just display the top 20 items to keep the output readable\n", " print(\"-\" * 30)\n", "\n", "# 3. Let's run the analysis on our categories and locations\n", "get_stats(df['main_category'], \"Main Categories\")\n", "get_stats(df['sub_category'], \"Sub Categories\")\n", "get_stats(df['neighborhood'], \"Neighborhoods\")\n", "\n", "# 4. Deep dive into the Tags (Features)\n", "# The 'tags' column is a bit tricky since it contains nested lists, so we'll flatten it first to count them properly\n", "all_tags = [tag for tags_list in df['tags'] if isinstance(tags_list, list) for tag in tags_list]\n", "tag_counts = Counter(all_tags)\n", "tag_df = pd.DataFrame.from_dict(tag_counts, orient='index', columns=['Count']).sort_values(by='Count', ascending=False)\n", "\n", "print(f\"\\n{'='*50}\")\n", "print(f\"REPORT FOR: ALL_TAGS\")\n", "print(f\"{'='*50}\")\n", "print(f\"Total Unique Tags: {len(tag_df)}\")\n", "print(\"-\" * 30)\n", "print(\"Top 20 Most Used Tags:\")\n", "print(tag_df.head(20))\n", "print(\"-\" * 30)" ] }, { "cell_type": "code", "execution_count": 9, "id": "b596156f", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "--- POPULARITY AND RANKING STATISTICS ---\n", " reviewsCount rank\n", "count 374.000000 374.000000\n", "mean 5061.791444 24.302139\n", "std 22071.713722 13.175741\n", "min 0.000000 1.000000\n", "25% 14.000000 15.000000\n", "50% 90.000000 23.000000\n", "75% 914.750000 32.000000\n", "max 205344.000000 65.000000\n", "\n", "--- TOP 20 MOST POPULAR PLACES (By Review Count) ---\n", " title reviewsCount rank totalScore\n", " Galata Tower 205344 7 4.6\n", " Egyptian Bazaar 186809 13 4.5\n", " Grand Bazaar 180195 10 4.4\n", " Hagia Sophia Grand Mosque 134322 4 4.8\n", " The Blue Mosque 116535 5 4.7\n", " Topkapi Palace Museum 115222 3 4.6\n", " Basilica Cistern 91523 2 4.6\n", " Dolmabahçe Palace 89730 6 4.7\n", " Gülhane Park 67006 9 4.7\n", " Suleymaniye Mosque 63600 11 4.9\n", " Taksim Square 53744 27 4.4\n", " Galata Bridge 40371 8 4.6\n", " Sultanahmet Square 35525 1 4.7\n", " Kadikoy Bull Statue 28270 4 4.5\n", " Beyazit Square 27016 9 4.6\n", " Rahmi M. Koç Museum 26641 6 4.8\n", " Maiden's Tower 26593 12 4.7\n", "Istanbul Archaeological Museums 20225 10 4.6\n", " Çiçek Pasajı 18529 4 4.4\n", " Panorama 1453 History Museum 15698 15 4.5\n", "\n", "--- TOP 20 PLACES WITH THE BEST (LOWEST) RANK ---\n", " title rank reviewsCount\n", " Sultanahmet Square 1 35525\n", " Sunset View 1 21\n", " Basilica Cistern 2 91523\n", " Turkish & Islamic Arts Museum 3 4981\n", " Galata Walls 3 290\n", " Topkapi Palace Museum 3 115222\n", " Belgrade Gate 3 307\n", " Edirnekapi Walls 3 294\n", " The Golden Gate 3 606\n", " Murral by Dome 3 5\n", " Çiçek Pasajı 4 18529\n", " Rainbow Steps 4 266\n", " Kadikoy Bull Statue 4 28270\n", "Harbiye Military Museum and Cultural Site Command 4 5237\n", " Hagia Sophia Grand Mosque 4 134322\n", " Ufo large scale mural 5 9\n", " Column of Constantine 5 9639\n", " Eminönü 5 139\n", " Palace of Blachernae 5 63\n", " The Blue Mosque 5 116535\n" ] } ], "source": [ "import pandas as pd\n", "\n", "# 1. Load the dataset\n", "df = pd.read_json('data_sightseeing_ready.json')\n", "\n", "# 2. Numerical Analysis (Statistical Summary)\n", "# This section gives us a quick overview of the data's \"quality\" and distribution\n", "print(\"--- POPULARITY AND RANKING STATISTICS ---\")\n", "# The describe() function instantly provides min, max, mean, and quartiles\n", "print(df[['reviewsCount', 'rank']].describe())\n", "\n", "# 3. Crucial for the NLP Team: Top 20 Most Popular Places\n", "# This is the target list when a user searches for \"famous\" or \"well-known\" places\n", "print(\"\\n--- TOP 20 MOST POPULAR PLACES (By Review Count) ---\")\n", "popular_df = df[['title', 'reviewsCount', 'rank', 'totalScore']].sort_values(by='reviewsCount', ascending=False)\n", "print(popular_df.head(20).to_string(index=False))\n", "\n", "# 4. Rank Analysis: Highest Priority Places\n", "# These are the default top-priority places that will appear first in search results\n", "print(\"\\n--- TOP 20 PLACES WITH THE BEST (LOWEST) RANK ---\")\n", "rank_df = df[['title', 'rank', 'reviewsCount']].sort_values(by='rank', ascending=True)\n", "print(rank_df.head(20).to_string(index=False))\n" ] }, { "cell_type": "code", "execution_count": 10, "id": "32613ba7", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "--- SIGHTSEEING DATABASE COLUMN LIST (TOTAL: 185 COLUMNS) ---\n", "------------------------------------------------------------\n", " 1. accessibility_assisted_listening_devices\n", " 2. accessibility_assistive_hearing_loop\n", " 3. accessibility_auracast_broadcast_audio\n", " 4. accessibility_wheelchair-accessible_car_park\n", " 5. accessibility_wheelchair-accessible_entrance\n", " 6. accessibility_wheelchair-accessible_toilet\n", " 7. accessibility_wheelchair_accessible_entrance\n", " 8. accessibility_wheelchair_accessible_parking_lot\n", " 9. accessibility_wheelchair_accessible_restroom\n", " 10. accessibility_wheelchair_accessible_seating\n", " 11. activities_hiking\n", " 12. additionalInfo\n", " 13. address\n", " 14. amenities_bar_onsite\n", " 15. amenities_basketball_court\n", " 16. amenities_cycling_lanes\n", " 17. amenities_free_wi-fi\n", " 18. amenities_gender-neutral_restroom\n", " 19. amenities_picnic_tables\n", " 20. amenities_public_restroom\n", " 21. amenities_restaurant\n", " 22. amenities_restroom\n", " 23. amenities_skateboarding_area\n", " 24. amenities_slides\n", " 25. amenities_swings\n", " 26. amenities_toilet\n", " 27. amenities_volleyball_court\n", " 28. amenities_wi-fi\n", " 29. atmosphere_casual\n", " 30. atmosphere_cosy\n", " 31. atmosphere_cozy\n", " 32. atmosphere_historic\n", " 33. atmosphere_quiet\n", " 34. atmosphere_romantic\n", " 35. atmosphere_trending\n", " 36. atmosphere_trendy\n", " 37. atmosphere_upscale\n", " 38. categories\n", " 39. categoryName\n", " 40. children_discounts_for_kids\n", " 41. children_good_for_kids\n", " 42. children_good_for_kids_birthday\n", " 43. children_has_changing_table(s)\n", " 44. children_high_chairs\n", " 45. children_kid-friendly_activities\n", " 46. children_kid-friendly_hikes\n", " 47. children_kids'_menu\n", " 48. children_playground\n", " 49. cid\n", " 50. city\n", " 51. crowd_college_students\n", " 52. crowd_family-friendly\n", " 53. crowd_groups\n", " 54. crowd_tourists\n", " 55. crowd_university_students\n", " 56. description\n", " 57. dining_options_breakfast\n", " 58. dining_options_brunch\n", " 59. dining_options_catering\n", " 60. dining_options_counter_service\n", " 61. dining_options_dessert\n", " 62. dining_options_dinner\n", " 63. dining_options_lunch\n", " 64. dining_options_seating\n", " 65. dining_options_table_service\n", " 66. display_title\n", " 67. enriched_description\n", " 68. fid\n", " 69. from_the_business_identifies_as_women-owned\n", " 70. highlights_fireplace\n", " 71. highlights_great_beer_selection\n", " 72. highlights_great_cocktails\n", " 73. highlights_great_coffee\n", " 74. highlights_great_dessert\n", " 75. highlights_great_tea_selection\n", " 76. highlights_great_wine_list\n", " 77. highlights_live_music\n", " 78. highlights_live_performances\n", " 79. highlights_picnics\n", " 80. highlights_rooftop_seating\n", " 81. highlights_sports\n", " 82. imageUrl\n", " 83. imagesCount\n", " 84. is_24_7\n", " 85. is_hours_estimated\n", " 86. kgmid\n", " 87. latitude\n", " 88. longitude\n", " 89. main_category\n", " 90. neighborhood\n", " 91. offerings_alcohol\n", " 92. offerings_all_you_can_eat\n", " 93. offerings_beer\n", " 94. offerings_cocktails\n", " 95. offerings_coffee\n", " 96. offerings_dancing\n", " 97. offerings_food\n", " 98. offerings_food_at_bar\n", " 99. offerings_gift_shop\n", "100. offerings_halal_food\n", "101. offerings_happy_hour_drinks\n", "102. offerings_happy_hour_food\n", "103. offerings_hard_liquor\n", "104. offerings_healthy_options\n", "105. offerings_late-night_food\n", "106. offerings_organic_dishes\n", "107. offerings_prepared_foods\n", "108. offerings_private_dining_room\n", "109. offerings_quick_bite\n", "110. offerings_salad_bar\n", "111. offerings_sauna\n", "112. offerings_small_plates\n", "113. offerings_vegan_options\n", "114. offerings_vegetarian_options\n", "115. offerings_wine\n", "116. openingHours\n", "117. openingHours_display\n", "118. parking_free_of_charge_street_parking\n", "119. parking_free_parking_garage\n", "120. parking_free_parking_lot\n", "121. parking_free_street_parking\n", "122. parking_on-site_parking\n", "123. parking_paid_parking_garage\n", "124. parking_paid_parking_lot\n", "125. parking_paid_street_parking\n", "126. parking_somewhat_difficult_to_find_a_space\n", "127. parking_usually_difficult_to_find_a_space\n", "128. parking_usually_plenty_of_parking\n", "129. parking_usually_somewhat_difficult_to_find_a_space\n", "130. parking_valet_parking\n", "131. payments_admission_fee\n", "132. payments_checks\n", "133. payments_credit_cards\n", "134. payments_debit_cards\n", "135. payments_nfc_mobile_payments\n", "136. permanentlyClosed\n", "137. pets_dog_park\n", "138. pets_dogs_allowed_inside\n", "139. pets_dogs_allowed_outside\n", "140. phone\n", "141. placeId\n", "142. planning_accepts_reservations\n", "143. planning_appointment_required\n", "144. planning_appointments_recommended\n", "145. planning_brunch_reservations_recommended\n", "146. planning_dinner_reservations_recommended\n", "147. planning_getting_tickets_in_advance_recommended\n", "148. planning_lunch_reservations_recommended\n", "149. planning_quick_visit\n", "150. planning_reservations_required\n", "151. planning_usually_a_wait\n", "152. popular_for_breakfast\n", "153. popular_for_dinner\n", "154. popular_for_good_for_working_on_laptop\n", "155. popular_for_lunch\n", "156. popular_for_solo_dining\n", "157. postalCode\n", "158. quality_score\n", "159. rank\n", "160. reviewsCount\n", "161. searchString\n", "162. service_options_delivery\n", "163. service_options_dine-in\n", "164. service_options_drive-through\n", "165. service_options_in-store_pickup\n", "166. service_options_in-store_shopping\n", "167. service_options_no-contact_delivery\n", "168. service_options_on-site_services\n", "169. service_options_onsite_services\n", "170. service_options_outdoor_seating\n", "171. service_options_same-day_delivery\n", "172. service_options_takeaway\n", "173. service_options_takeout\n", "174. source_file\n", "175. state\n", "176. street\n", "177. subTitle\n", "178. sub_category\n", "179. tags\n", "180. temporarilyClosed\n", "181. title\n", "182. totalScore\n", "183. url\n", "184. website\n", "185. weighted_score\n" ] } ], "source": [ "file_name = 'data_sightseeing_ready.json'\n", "df = pd.read_json(file_name)\n", "\n", "# 2. Extract column names and sort them alphabetically for quick reference\n", "sorted_columns = sorted(df.columns.tolist())\n", "\n", "# 3. Display the structured results\n", "print(f\"--- SIGHTSEEING DATABASE COLUMN LIST (TOTAL: {len(sorted_columns)} COLUMNS) ---\")\n", "print(\"-\" * 60)\n", "\n", "for index, col in enumerate(sorted_columns, 1):\n", " # We use numbering here to improve readability and make index mapping easier for the NLP team\n", " print(f\"{index:3}. {col}\")" ] }, { "cell_type": "code", "execution_count": 11, "id": "15c05a2d", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The reference guide has been successfully generated as 'NLP_Sightseeing_Ref_Guide_Detailed.txt'.\n" ] } ], "source": [ "import pandas as pd\n", "from collections import Counter\n", "\n", "# 1. Load and Clean the Data\n", "df = pd.read_json('data_sightseeing_ready.json')\n", "\n", "# Fill missing values to prevent errors during mapping\n", "df['main_category'] = df['main_category'].fillna('Unknown')\n", "df['sub_category'] = df['sub_category'].fillna('Unknown')\n", "df['city'] = df['city'].fillna('Unknown')\n", "df['neighborhood'] = df['neighborhood'].fillna('Unknown')\n", "\n", "# 2. Extract and Sort Unique Entities for the Dictionary\n", "main_cats = sorted([str(x) for x in df['main_category'].unique()])\n", "sub_cats = sorted([str(x) for x in df['sub_category'].unique()])\n", "cities = sorted([str(x) for x in df['city'].unique()])\n", "neighborhoods = sorted([str(x) for x in df['neighborhood'].unique()])\n", "\n", "# Flatten the nested tags list to get a clean unique set\n", "all_tags_list = [tag for tags_list in df['tags'] if isinstance(tags_list, list) for tag in tags_list]\n", "unique_tags = sorted(list(set(all_tags_list)))\n", "\n", "# 3. Calculate Numerical Thresholds for NLP Intent Classification\n", "reviews_stats = df['reviewsCount'].describe()\n", "rank_stats = df['rank'].describe() if 'rank' in df.columns else None\n", "score_min = df['totalScore'].min()\n", "score_max = df['totalScore'].max()\n", "score_mean = df['totalScore'].mean()\n", "\n", "# 4. Calculate Frequency Counts to determine entity priority\n", "tag_counts = pd.Series(Counter(all_tags_list)).sort_values(ascending=False).head(20)\n", "\n", "def get_top_20_str(series):\n", " return series.value_counts().head(20).to_string()\n", "\n", "# 5. Generate and Write the NLP Reference Guide (.txt)\n", "with open('NLP_Sightseeing_Ref_Guide_Detailed.txt', 'w', encoding='utf-8') as f:\n", " f.write(\"============================================================\\n\")\n", " f.write(\"EVLIYAPP NLP REFERENCE GUIDE (SIGHTSEEING)\\n\")\n", " f.write(\"============================================================\\n\\n\")\n", " f.write(\">>> GUIDE NOTE: This document outlines the specific keywords and thresholds the NLP model\\n\")\n", " f.write(\">>> (Entity Extraction & Intent Classification) should target in the database.\\n\")\n", " f.write(\">>> The following data points are Case-Sensitive.\\n\\n\")\n", " \n", " f.write(\"------------------------------------------------------------\\n\")\n", " f.write(\"SECTION 1: UNIQUE VALUES (ENTITY DICTIONARY)\\n\")\n", " f.write(\"------------------------------------------------------------\\n\")\n", " f.write(\">>> GUIDE NOTE: This is the exact, universal vocabulary pool the model needs to capture\\n\")\n", " f.write(\">>> when a user asks 'Where should we go?' or 'What should we do?'.\\n\\n\")\n", " \n", " f.write(f\"MAIN CATEGORIES ({len(main_cats)} Items):\\n\")\n", " f.write(\", \".join(main_cats) + \"\\n\\n\")\n", " \n", " f.write(f\"SUB CATEGORIES ({len(sub_cats)} Items):\\n\")\n", " f.write(\", \".join(sub_cats) + \"\\n\\n\")\n", " \n", " f.write(f\"CITIES/DISTRICTS ({len(cities)} Items):\\n\")\n", " f.write(\", \".join(cities) + \"\\n\\n\")\n", " \n", " f.write(f\"NEIGHBORHOODS ({len(neighborhoods)} Items):\\n\")\n", " f.write(\", \".join(neighborhoods) + \"\\n\\n\")\n", " \n", " f.write(f\"ALL TAGS ({len(unique_tags)} Items):\\n\")\n", " f.write(\">>> GUIDE NOTE: Place features in the database are stored within the 'tags' array.\\n\")\n", " f.write(\">>> For example, a search for 'wheelchair' must map to 'Wheelchair Accessible' within these tags.\\n\")\n", " f.write(\", \".join(unique_tags) + \"\\n\\n\")\n", " \n", " f.write(\"------------------------------------------------------------\\n\")\n", " f.write(\"SECTION 2: NUMERICAL THRESHOLDS\\n\")\n", " f.write(\"------------------------------------------------------------\\n\")\n", " f.write(\">>> GUIDE NOTE: These boundaries are designed for the NLP team to encode abstract concepts.\\n\")\n", " f.write(\">>> User queries like 'popular places' or 'best places' are translated into numerical equivalents here.\\n\\n\")\n", " \n", " f.write(\"QUALITY (totalScore):\\n\")\n", " f.write(f\"- Score Interval: {score_min} - {score_max} (System-wide minimum and maximum scores)\\n\")\n", " f.write(f\"- Average Score: {score_mean:.2f} (If a user searches for 'good places', the NLP should filter by > {score_mean:.2f})\\n\\n\")\n", " \n", " f.write(\"POPULARITY (reviewsCount Distribution):\\n\")\n", " f.write(\">>> NLP LOGIC FRAMEWORK:\\n\")\n", " f.write(\">>> If the query is 'quiet, calm, unknown' -> reviewsCount <= Bottom 25%\\n\")\n", " f.write(\">>> If the query is 'famous, well-known, popular' -> reviewsCount >= Top 25%\\n\")\n", " # Added Review Intervals:\n", " f.write(f\"- Review Interval (Min - Max): {int(reviews_stats['min'])} - {int(reviews_stats['max'])} reviews\\n\")\n", " f.write(f\"- Hidden/Quiet (Bottom 25% Threshold): {int(reviews_stats['25%'])} reviews or less\\n\")\n", " f.write(f\"- Average (Median): Around {int(reviews_stats['50%'])} reviews\\n\")\n", " f.write(f\"- Very Popular (Top 25% Threshold): {int(reviews_stats['75%'])} reviews and above\\n\\n\")\n", " \n", " if rank_stats is not None:\n", " f.write(\"SEARCH PRIORITY (Rank):\\n\")\n", " f.write(\">>> GUIDE NOTE: If no sorting is specified by the user, results default to ascending order (ASC) by rank.\\n\")\n", " f.write(f\"- Rank Interval (Min - Max): {int(rank_stats['min'])} - {int(rank_stats['max'])} (Lower number = Higher Priority)\\n\")\n", " f.write(f\"- Highest Priority Place Rank: {int(rank_stats['min'])}\\n\\n\")\n", " \n", " f.write(\"------------------------------------------------------------\\n\")\n", " f.write(\"SECTION 3: FREQUENCY AND PRIORITY STATISTICS\\n\")\n", " f.write(\"------------------------------------------------------------\\n\")\n", " f.write(\">>> GUIDE NOTE: Indicates which words/categories are most prominent in the system.\\n\")\n", " f.write(\">>> In case of keyword collisions, the NLP model should prioritize the entity with the higher frequency.\\n\\n\")\n", " \n", " f.write(f\"TOP MAIN CATEGORIES:\\n{get_top_20_str(df['main_category'])}\\n\\n\")\n", " f.write(f\"TOP SUB CATEGORIES:\\n{get_top_20_str(df['sub_category'])}\\n\\n\")\n", " f.write(f\"TOP NEIGHBORHOODS:\\n{get_top_20_str(df['neighborhood'])}\\n\\n\")\n", " f.write(f\"TOP TAGS:\\n{tag_counts.to_string()}\\n\")\n", "\n", "print(\"The reference guide has been successfully generated as 'NLP_Sightseeing_Ref_Guide_Detailed.txt'.\")" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.13" } }, "nbformat": 4, "nbformat_minor": 5 }