03-load-data-and-run.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "35246e61-9175-4d2d-ba78-ec51058446c0",
   "metadata": {},
   "source": [
    "# Usar LLMs para generar resúmenes de propiedades de `Zonaprop`\n",
    "- https://www.machinelearnear.com/\n",
    "- https://www.youtube.com/@machinelearnear\n",
    "- Webinar real estate: https://www.youtube.com/watch?v=DuujwXarVbY&ab_channel=SantiagoMagnin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f4403c2e-8013-433d-a3c4-3d335d058cca",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "def abrir_zonaprop_csv(fname, processed_folder = './processed'):\n",
    "    # listamos las subcarpetas en la carpeta\n",
    "    subfolders = [f.path for f in os.scandir(processed_folder) if f.is_dir()]\n",
    "    \n",
    "    # si no hay subcarpetas, retornamos\n",
    "    if not subfolders:\n",
    "        print(f\"No hay subcarpetas en {processed_folder}.\")\n",
    "        return None\n",
    "\n",
    "    # imprimimos las subcarpetas para que el usuario elija una\n",
    "    print(\"Subcarpetas disponibles:\")\n",
    "    for idx, folder in enumerate(subfolders):\n",
    "        print(f\"{idx + 1}. {folder}\")\n",
    "\n",
    "    # pedimos al usuario que elija una subcarpeta\n",
    "    choice = int(input(\"\\nSeleccioná el número de la subcarpeta: \"))\n",
    "    selected_folder = subfolders[choice - 1]\n",
    "\n",
    "    # construimos la ruta al archivo csv\n",
    "    csv_file = os.path.join(selected_folder, fname)\n",
    "\n",
    "    # verificamos si el archivo existe\n",
    "    if not os.path.exists(csv_file):\n",
    "        print(f\"No se encontró el archivo '{csv_file}'.\")\n",
    "        return None\n",
    "\n",
    "    # leemos y retornamos el dataframe\n",
    "    return pd.read_csv(csv_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4b8b304d-e222-4542-8395-f700accd862d",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Subcarpetas disponibles:\n",
      "1. ./processed/inmuebles-venta-palermo-colegiales-villa-crespo-chacarita-con-balcon-y-disposicion-frente-mas-30-m2-cubiertos-50000-130000-dolar\n",
      "2. ./processed/.ipynb_checkpoints\n",
      "3. ./processed/inmuebles-venta-barrio-norte-palermo-colegiales-villa-crespo-publicado-hace-menos-de-45-dias-50000-130000-dolar-orden-visitas-descendente\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "\n",
      "Seleccioná el número de la subcarpeta:  3\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>listing_url</th>\n",
       "      <th>asking_price_in_usd</th>\n",
       "      <th>expensas_in_ars</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>google_maps</th>\n",
       "      <th>photos</th>\n",
       "      <th>whatsapp</th>\n",
       "      <th>published_on</th>\n",
       "      <th>disposición</th>\n",
       "      <th>...</th>\n",
       "      <th>cantidaddedepartamentos</th>\n",
       "      <th>saladejuegos</th>\n",
       "      <th>promotiondescription</th>\n",
       "      <th>tipoderubro</th>\n",
       "      <th>cantidaddeascensores</th>\n",
       "      <th>cantidaddelocalescomerciales</th>\n",
       "      <th>user_views</th>\n",
       "      <th>days</th>\n",
       "      <th>views_per_day</th>\n",
       "      <th>usd_per_m2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://www.zonaprop.com.ar/propiedades/clasif...</td>\n",
       "      <td>98000</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>-34.5834184</td>\n",
       "      <td>-58.404087</td>\n",
       "      <td>https://maps.google.com/?q=-34.5834184,-58.404087</td>\n",
       "      <td>['https://imgar.zonapropcdn.com/avisos/1/00/52...</td>\n",
       "      <td>5.491166e+12</td>\n",
       "      <td>24-01-2024</td>\n",
       "      <td>Frente</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>169</td>\n",
       "      <td>15</td>\n",
       "      <td>11</td>\n",
       "      <td>2450.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://www.zonaprop.com.ar/propiedades/clasif...</td>\n",
       "      <td>114000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>-34.5917978</td>\n",
       "      <td>-58.4234218</td>\n",
       "      <td>https://maps.google.com/?q=-34.5917978,-58.423...</td>\n",
       "      <td>['https://imgar.zonapropcdn.com/avisos/resize/...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18-01-2024</td>\n",
       "      <td>Frente</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>170</td>\n",
       "      <td>27</td>\n",
       "      <td>6</td>\n",
       "      <td>2111.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://www.zonaprop.com.ar/propiedades/clasif...</td>\n",
       "      <td>99000</td>\n",
       "      <td>19500.0</td>\n",
       "      <td>-34.57885220000001</td>\n",
       "      <td>-58.4136051</td>\n",
       "      <td>https://maps.google.com/?q=-34.57885220000001,...</td>\n",
       "      <td>['https://imgar.zonapropcdn.com/avisos/1/00/53...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05-02-2024</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>170</td>\n",
       "      <td>8</td>\n",
       "      <td>21</td>\n",
       "      <td>3300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://www.zonaprop.com.ar/propiedades/clasif...</td>\n",
       "      <td>51000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-34.5987945</td>\n",
       "      <td>-58.38669719999996</td>\n",
       "      <td>https://maps.google.com/?q=-34.5987945,-58.386...</td>\n",
       "      <td>['https://imgar.zonapropcdn.com/avisos/1/00/52...</td>\n",
       "      <td>5.491146e+12</td>\n",
       "      <td>09-01-2024</td>\n",
       "      <td>Lateral</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>170</td>\n",
       "      <td>42</td>\n",
       "      <td>5</td>\n",
       "      <td>1758.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://www.zonaprop.com.ar/propiedades/clasif...</td>\n",
       "      <td>75000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-34.5920267</td>\n",
       "      <td>-58.4334159</td>\n",
       "      <td>https://maps.google.com/?q=-34.5920267,-58.433...</td>\n",
       "      <td>['https://imgar.zonapropcdn.com/avisos/1/00/52...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02-01-2024</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>171</td>\n",
       "      <td>37</td>\n",
       "      <td>5</td>\n",
       "      <td>2205.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 52 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         listing_url  asking_price_in_usd  \\\n",
       "0  https://www.zonaprop.com.ar/propiedades/clasif...                98000   \n",
       "1  https://www.zonaprop.com.ar/propiedades/clasif...               114000   \n",
       "2  https://www.zonaprop.com.ar/propiedades/clasif...                99000   \n",
       "3  https://www.zonaprop.com.ar/propiedades/clasif...                51000   \n",
       "4  https://www.zonaprop.com.ar/propiedades/clasif...                75000   \n",
       "\n",
       "   expensas_in_ars            latitude           longitude  \\\n",
       "0          30000.0         -34.5834184          -58.404087   \n",
       "1              1.0         -34.5917978         -58.4234218   \n",
       "2          19500.0  -34.57885220000001         -58.4136051   \n",
       "3              NaN         -34.5987945  -58.38669719999996   \n",
       "4              NaN         -34.5920267         -58.4334159   \n",
       "\n",
       "                                         google_maps  \\\n",
       "0  https://maps.google.com/?q=-34.5834184,-58.404087   \n",
       "1  https://maps.google.com/?q=-34.5917978,-58.423...   \n",
       "2  https://maps.google.com/?q=-34.57885220000001,...   \n",
       "3  https://maps.google.com/?q=-34.5987945,-58.386...   \n",
       "4  https://maps.google.com/?q=-34.5920267,-58.433...   \n",
       "\n",
       "                                              photos      whatsapp  \\\n",
       "0  ['https://imgar.zonapropcdn.com/avisos/1/00/52...  5.491166e+12   \n",
       "1  ['https://imgar.zonapropcdn.com/avisos/resize/...           NaN   \n",
       "2  ['https://imgar.zonapropcdn.com/avisos/1/00/53...           NaN   \n",
       "3  ['https://imgar.zonapropcdn.com/avisos/1/00/52...  5.491146e+12   \n",
       "4  ['https://imgar.zonapropcdn.com/avisos/1/00/52...           NaN   \n",
       "\n",
       "  published_on disposición  ... cantidaddedepartamentos saladejuegos  \\\n",
       "0   24-01-2024      Frente  ...                     NaN          NaN   \n",
       "1   18-01-2024      Frente  ...                     NaN          NaN   \n",
       "2   05-02-2024         NaN  ...                     NaN          NaN   \n",
       "3   09-01-2024     Lateral  ...                     NaN          NaN   \n",
       "4   02-01-2024         NaN  ...                     NaN          NaN   \n",
       "\n",
       "   promotiondescription  tipoderubro  cantidaddeascensores  \\\n",
       "0                   NaN          NaN                   NaN   \n",
       "1                   NaN          NaN                   NaN   \n",
       "2                   NaN          NaN                   NaN   \n",
       "3                   NaN          NaN                   NaN   \n",
       "4                   NaN          NaN                   NaN   \n",
       "\n",
       "   cantidaddelocalescomerciales user_views  days  views_per_day  usd_per_m2  \n",
       "0                           NaN        169    15             11      2450.0  \n",
       "1                           NaN        170    27              6      2111.0  \n",
       "2                           NaN        170     8             21      3300.0  \n",
       "3                           NaN        170    42              5      1758.0  \n",
       "4                           NaN        171    37              5      2205.0  \n",
       "\n",
       "[5 rows x 52 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load data from local\n",
    "zonaprop_listings = abrir_zonaprop_csv('zonaprop_with_userviews.csv')\n",
    "zonaprop_listings.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3f76c0b-8a5f-4f79-a9f8-9527b66b8daf",
   "metadata": {},
   "source": [
    "## Como traemos la información de `Airbnb`?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e4e3e850-95bd-42c9-a33a-9a67fe1b6087",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from sklearn.neighbors import BallTree\n",
    "\n",
    "def find_within_radius(reference_df, target_df, radius_km=1):\n",
    "    # Convert radius from kilometers to radians for BallTree\n",
    "    radius_rad = radius_km / 6371  # Earth's radius in km\n",
    "\n",
    "    # Creating BallTree with target data\n",
    "    tree = BallTree(np.deg2rad(target_df[['latitude', 'longitude']].values), metric='haversine')\n",
    "\n",
    "    # Initialize DataFrame to store results\n",
    "    results_df = pd.DataFrame()\n",
    "\n",
    "    # Iterate over reference DataFrame\n",
    "    for index, row in reference_df.iterrows():\n",
    "        # Query BallTree\n",
    "        indices = tree.query_radius(np.deg2rad([[row['latitude'], row['longitude']]]), r=radius_rad)\n",
    "        # Extract relevant rows from target DataFrame\n",
    "        filtered_df = target_df.iloc[indices[0]]\n",
    "        results_df = pd.concat([results_df, filtered_df])\n",
    "\n",
    "    return results_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a271814b-ceab-47bc-9681-f00afd81b3b0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "airbnb_listings = pd.read_csv('processed/airbnb_listings.csv')\n",
    "# airbnb_reviews = pd.read_csv('processed/airbnb_reviews.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90424124-bf4a-4b4a-b46b-8a4c2d7a57a1",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Encontrar los listings mas cercanos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "09268877-2faa-4741-abfe-d02e8326d9a1",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def find_within_radius(row, airbnb_listings, radius_km):\n",
    "    radius_rad = radius_km / 6371\n",
    "    valid_listings = airbnb_listings.dropna(subset=['latitude', 'longitude'])\n",
    "    tree = BallTree(np.deg2rad(valid_listings[['latitude', 'longitude']].values), metric='haversine')\n",
    "    if pd.notnull(row['latitude']) and pd.notnull(row['longitude']):\n",
    "        indices = tree.query_radius(np.deg2rad([[row['latitude'], row['longitude']]]), r=radius_rad)\n",
    "        return valid_listings.iloc[indices[0]]\n",
    "    return pd.DataFrame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "0123ac2f-84d6-4eef-ba30-5c4ab87d23fe",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from sklearn.neighbors import BallTree\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "def agregar_info_airbnb(listings, airbnb_listings, radius_km):\n",
    "    listings = listings.copy()\n",
    "    listings['latitude'] = pd.to_numeric(listings['latitude'], errors='coerce')\n",
    "    listings['longitude'] = pd.to_numeric(listings['longitude'], errors='coerce')\n",
    "\n",
    "    def find_within_radius(row, radius_km):\n",
    "        radius_rad = radius_km / 6371\n",
    "        valid_listings = airbnb_listings.dropna(subset=['latitude', 'longitude'])\n",
    "        tree = BallTree(np.deg2rad(valid_listings[['latitude', 'longitude']].values), metric='haversine')\n",
    "        if pd.notnull(row['latitude']) and pd.notnull(row['longitude']):\n",
    "            indices = tree.query_radius(np.deg2rad([[row['latitude'], row['longitude']]]), r=radius_rad)\n",
    "            return valid_listings.iloc[indices[0]]\n",
    "        return pd.DataFrame()\n",
    "\n",
    "    for index, row in listings.iterrows():\n",
    "        closest_listings = find_within_radius(row, radius_km)\n",
    "        if closest_listings.empty: continue\n",
    "        filtered = closest_listings.dropna(subset=['estimated_price_per_night_in_USD', 'review_scores_rating', \n",
    "                           'review_scores_location', 'review_scores_value', 'room_type', 'estimated_nights_booked_l30d'])\n",
    "\n",
    "        # calcular la probabilidad de alquiler\n",
    "        booking_counts = filtered['estimated_nights_booked_l30d'].value_counts()\n",
    "        probabilidad_alquiler = 'más probable' if (booking_counts.get('high', 0) > booking_counts.get('low', 0)\n",
    "                                                  ) or (booking_counts.get('high', 0) > booking_counts.get('medium', 0)) else 'menos probable'\n",
    "        listings.at[index, 'airbnb_probabilidad_alquiler'] = probabilidad_alquiler\n",
    "        \n",
    "        # otros numeros grosos\n",
    "        listings.at[index, 'airbnb_avg_price_entire_home'] = int(filtered[filtered['room_type'] == 'Entire home/apt']['estimated_price_per_night_in_USD'].mean())\n",
    "        listings.at[index, 'airbnb_avg_price_private_room'] = int(filtered[filtered['room_type'] == 'Private room']['estimated_price_per_night_in_USD'].mean())\n",
    "        listings.at[index, 'airbnb_avg_review_score_rating'] = round(filtered['review_scores_rating'].mean(),2)\n",
    "        listings.at[index, 'airbnb_avg_review_score_location'] = round(filtered['review_scores_location'].mean(),2)\n",
    "        listings.at[index, 'airbnb_avg_review_score_value'] = round(filtered['review_scores_value'].mean(),2)\n",
    "\n",
    "    return listings"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cedc4407-7362-49e9-8442-976e341de3ad",
   "metadata": {},
   "source": [
    "#### Mostrame un ejemplo che"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "9d4ff600-39d1-443a-8bcb-b1f9d9406b3e",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "top_listings = zonaprop_listings[zonaprop_listings.views_per_day > 60].reset_index(drop=True)\n",
    "top_listings = high_relevance_listings.dropna(axis=1, how='all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "6e8d1716-ab4a-40d8-9dc4-2a90b20d96b4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# suponiendo que `top_listings` y `airbnb_listings` son tus DataFrames\n",
    "a = agregar_info_airbnb(top_listings, airbnb_listings, 0.3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "ec3d4639-d893-4161-9be9-ebe62d0a10fb",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>listing_url</th>\n",
       "      <th>asking_price_in_usd</th>\n",
       "      <th>expensas_in_ars</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>google_maps</th>\n",
       "      <th>photos</th>\n",
       "      <th>whatsapp</th>\n",
       "      <th>published_on</th>\n",
       "      <th>disposición</th>\n",
       "      <th>...</th>\n",
       "      <th>user_views</th>\n",
       "      <th>days</th>\n",
       "      <th>views_per_day</th>\n",
       "      <th>usd_per_m2</th>\n",
       "      <th>airbnb_probabilidad_alquiler</th>\n",
       "      <th>airbnb_avg_price_entire_home</th>\n",
       "      <th>airbnb_avg_price_private_room</th>\n",
       "      <th>airbnb_avg_review_score_rating</th>\n",
       "      <th>airbnb_avg_review_score_location</th>\n",
       "      <th>airbnb_avg_review_score_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://www.zonaprop.com.ar/propiedades/clasif...</td>\n",
       "      <td>95000</td>\n",
       "      <td>57000.0</td>\n",
       "      <td>-34.588879</td>\n",
       "      <td>-58.413855</td>\n",
       "      <td>https://maps.google.com/?q=-34.5888791,-58.413...</td>\n",
       "      <td>['https://imgar.zonapropcdn.com/avisos/1/00/53...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>08-02-2024</td>\n",
       "      <td>Contrafrente</td>\n",
       "      <td>...</td>\n",
       "      <td>66</td>\n",
       "      <td>1</td>\n",
       "      <td>66</td>\n",
       "      <td>1938.0</td>\n",
       "      <td>menos probable</td>\n",
       "      <td>59.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>4.78</td>\n",
       "      <td>4.94</td>\n",
       "      <td>4.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://www.zonaprop.com.ar/propiedades/clasif...</td>\n",
       "      <td>81650</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>-34.582257</td>\n",
       "      <td>-58.453389</td>\n",
       "      <td>https://maps.google.com/?q=-34.5822573,-58.453...</td>\n",
       "      <td>['https://imgar.zonapropcdn.com/avisos/1/00/52...</td>\n",
       "      <td>5.491127e+12</td>\n",
       "      <td>01-02-2024</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1402</td>\n",
       "      <td>22</td>\n",
       "      <td>63</td>\n",
       "      <td>1600.0</td>\n",
       "      <td>más probable</td>\n",
       "      <td>49.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>4.82</td>\n",
       "      <td>4.77</td>\n",
       "      <td>4.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://www.zonaprop.com.ar/propiedades/clasif...</td>\n",
       "      <td>75000</td>\n",
       "      <td>41000.0</td>\n",
       "      <td>-34.589921</td>\n",
       "      <td>-58.423228</td>\n",
       "      <td>https://maps.google.com/?q=-34.5899212,-58.423...</td>\n",
       "      <td>['https://imgar.zonapropcdn.com/avisos/resize/...</td>\n",
       "      <td>5.491150e+12</td>\n",
       "      <td>08-02-2024</td>\n",
       "      <td>Frente</td>\n",
       "      <td>...</td>\n",
       "      <td>948</td>\n",
       "      <td>10</td>\n",
       "      <td>94</td>\n",
       "      <td>2083.0</td>\n",
       "      <td>menos probable</td>\n",
       "      <td>66.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>4.79</td>\n",
       "      <td>4.93</td>\n",
       "      <td>4.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://www.zonaprop.com.ar/propiedades/clasif...</td>\n",
       "      <td>130000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-34.573193</td>\n",
       "      <td>-58.440290</td>\n",
       "      <td>https://maps.google.com/?q=-34.5731928,-58.440...</td>\n",
       "      <td>['https://imgar.zonapropcdn.com/avisos/1/00/52...</td>\n",
       "      <td>5.491166e+12</td>\n",
       "      <td>08-02-2024</td>\n",
       "      <td>Frente</td>\n",
       "      <td>...</td>\n",
       "      <td>610</td>\n",
       "      <td>10</td>\n",
       "      <td>61</td>\n",
       "      <td>1969.0</td>\n",
       "      <td>más probable</td>\n",
       "      <td>52.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>4.73</td>\n",
       "      <td>4.82</td>\n",
       "      <td>4.67</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4 rows × 36 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         listing_url  asking_price_in_usd  \\\n",
       "0  https://www.zonaprop.com.ar/propiedades/clasif...                95000   \n",
       "1  https://www.zonaprop.com.ar/propiedades/clasif...                81650   \n",
       "2  https://www.zonaprop.com.ar/propiedades/clasif...                75000   \n",
       "3  https://www.zonaprop.com.ar/propiedades/clasif...               130000   \n",
       "\n",
       "   expensas_in_ars   latitude  longitude  \\\n",
       "0          57000.0 -34.588879 -58.413855   \n",
       "1           5000.0 -34.582257 -58.453389   \n",
       "2          41000.0 -34.589921 -58.423228   \n",
       "3              0.0 -34.573193 -58.440290   \n",
       "\n",
       "                                         google_maps  \\\n",
       "0  https://maps.google.com/?q=-34.5888791,-58.413...   \n",
       "1  https://maps.google.com/?q=-34.5822573,-58.453...   \n",
       "2  https://maps.google.com/?q=-34.5899212,-58.423...   \n",
       "3  https://maps.google.com/?q=-34.5731928,-58.440...   \n",
       "\n",
       "                                              photos      whatsapp  \\\n",
       "0  ['https://imgar.zonapropcdn.com/avisos/1/00/53...           NaN   \n",
       "1  ['https://imgar.zonapropcdn.com/avisos/1/00/52...  5.491127e+12   \n",
       "2  ['https://imgar.zonapropcdn.com/avisos/resize/...  5.491150e+12   \n",
       "3  ['https://imgar.zonapropcdn.com/avisos/1/00/52...  5.491166e+12   \n",
       "\n",
       "  published_on   disposición  ... user_views days  views_per_day  usd_per_m2  \\\n",
       "0   08-02-2024  Contrafrente  ...         66    1             66      1938.0   \n",
       "1   01-02-2024           NaN  ...       1402   22             63      1600.0   \n",
       "2   08-02-2024        Frente  ...        948   10             94      2083.0   \n",
       "3   08-02-2024        Frente  ...        610   10             61      1969.0   \n",
       "\n",
       "   airbnb_probabilidad_alquiler  airbnb_avg_price_entire_home  \\\n",
       "0                menos probable                          59.0   \n",
       "1                  más probable                          49.0   \n",
       "2                menos probable                          66.0   \n",
       "3                  más probable                          52.0   \n",
       "\n",
       "  airbnb_avg_price_private_room  airbnb_avg_review_score_rating  \\\n",
       "0                          27.0                            4.78   \n",
       "1                          18.0                            4.82   \n",
       "2                          23.0                            4.79   \n",
       "3                          26.0                            4.73   \n",
       "\n",
       "  airbnb_avg_review_score_location airbnb_avg_review_score_value  \n",
       "0                             4.94                          4.73  \n",
       "1                             4.77                          4.73  \n",
       "2                             4.93                          4.73  \n",
       "3                             4.82                          4.67  \n",
       "\n",
       "[4 rows x 36 columns]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d509811-9afb-43c5-a494-ccda09b80ad1",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Generar un resumen con LLM"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a79af881-5990-467c-8c79-577a8bd19ed1",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Primero, configuramos todo\n",
    "- https://github.com/BerriAI/litellm/\n",
    "- https://docs.litellm.ai/docs/providers/togetherai\n",
    "- listado de cuanto salen las API de Mixtral-8x7B: https://twitter.com/chiefaioffice/status/1750622940658299332"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "19cf1743-5e03-4c7e-887f-932fdaabd6e9",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# !pip install litellm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "f2c4660b-1e38-477e-b01b-e611b5c42fdf",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from dotenv import load_dotenv   #for python-dotenv method\n",
    "load_dotenv()                    #for python-dotenv method\n",
    "import os "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "ffb8ca1a-1328-4bc4-b0e4-046d103a7df6",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "system_instructions = \"\"\"Always follow these instructions:\n",
    "- Using the above context only, return a single paragraph summarizing all the information as your output.\n",
    "- Write with strong Argentinian accent in Spanish.\n",
    "- Be descriptive, don't skip words.\n",
    "- For \"Zonaprop\", pay special attention to: 'asking_price_in_usd', 'expensas_in_ars', 'views_per_day', 'usd_per_m2'\n",
    "- For \"Airbnb\", pay special attention to: 'average price', 'review score location', and `probabilidad de alquiler'. All prices are in USD.\n",
    "- Be casual unless otherwise specified\n",
    "- Be accurate, thorough, and descriptive\n",
    "- No need to disclose you're an AI, nor greet me, or say goodbye.\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "b6da6c66-d431-42d9-98ff-8dd637a0659d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def info_del_listing(row):\n",
    "    zonaprop = '\\n'.join(f\"- {col}: {row[col]}\" for col in row.index if col not in \n",
    "                         ['photos', 'listing_url', 'latitude', 'longitude', 'google_maps', 'whatsapp', 'days'] and\n",
    "                         'airbnb_' not in col and pd.notnull(row[col]))\n",
    "    airbnb = '\\n'.join(f\"- {col}: {row[col]}\" for col in row.index if 'airbnb_' in col)\n",
    "    return zonaprop, airbnb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "01df1a73-1112-4d8c-aba2-7aa14c67069e",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "b = info_del_listing(a.iloc[1])\n",
    "prompt = f\"Return a dense summary from the given context:\\n```Zonaprop:{b[0]}\\n----\\nAirbnb listings nearby:\\n{b[1]}```\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "a3920aa6-f071-49cb-a998-f14024f16195",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from litellm import completion \n",
    "\n",
    "messages = [\n",
    "    {\"role\": \"system\", \"content\": system_instructions},\n",
    "    {\"role\": \"user\", \"content\": prompt},\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf9fca05-3a24-458b-bb5f-33a46e494613",
   "metadata": {},
   "source": [
    "### Después, probamos distintos modelos a ver cual nos gusta mas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05eb9303-e8a6-4e94-ace3-10ebc3f928d6",
   "metadata": {},
   "source": [
    "`together_ai/mistralai/Mixtral-8x7B-Instruct-v0.1` @ $0.60/M"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "85724a57-ebd5-43da-8056-1ef1eb9d702d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "chat_completion = completion(\n",
    "    messages=messages,\n",
    "    model=\"together_ai/mistralai/Mixtral-8x7B-Instruct-v0.1\",\n",
    "    max_tokens=1024\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "1cc2e345-9d6b-4e6d-bb52-abc7232f259d",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "La lista de Zonaprop tiene un precio de lista en dólares de 81650, con expensas en pesos argentinos de 5000 y publicada el 01-02-2024. Tiene un tamaño total y cubierto de 51 metros cuadrados, con 3 ambientes, 1 baño, una antigüedad de 5 años, 1 planta, 9 metros cuadrados de superficie semicubierta, 2 dormitorios, y sin frente ni largo de terreno. Ha recibido 1402 visitas de usuarios y 63 visitas por día, con un precio de dólares por metro cuadrado de 1600.\n",
      "\n",
      "En cuanto a los alquileres de Airbnb en las cercanías, la probabilidad de alquiler es más probable, con un precio promedio de 49 dólares para un hogar entero y 18 dólares para una habitación privada. La puntuación promedio de reseñas es de 4.82 para la calidad general, 4.77 para la ubicación y 4.73 para el valor.\n"
     ]
    }
   ],
   "source": [
    "print(chat_completion.choices[0].message.content)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e23545c3-1427-4e4b-bf60-f08607f071da",
   "metadata": {},
   "source": [
    "`NousResearch/Nous-Hermes-2-Mixtral-8x7B-DPO` @ $0.60/M"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "7010dd50-cf15-46a6-89f3-8807af1b5cc3",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "chat_completion = completion(\n",
    "    messages=messages,\n",
    "    model=\"together_ai/NousResearch/Nous-Hermes-2-Mixtral-8x7B-DPO\",\n",
    "    max_tokens=1024\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "3c077424-eefb-44b7-bdf2-d25c2185442f",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "La propiedad en Zonaprop tiene un precio de venta de 81,650 USD, gastos de expensas de 50,00 ARS, y se publicó el 1 de febrero de 2024. Tiene una superficie total y cubierta de 51 m², con 3 ambientes, 1 baño, una antigüedad de 5 años, 1 planta, y una superficie semicubierta de 9 m². Cuenta con 2 dormitorios, y un terreno de 0 m² de frente y largo. La propiedad ha recibido 1,402 visitas y tiene un promedio de 63 visitas por día, con un valor de 1,600 USD por m² de superficie cubierta.\n",
      "En cuanto a los alquileres en Airbnb cercanos, es más probable que se alquilen propiedades enteras por un precio promedio de 49 USD, mientras que las habitaciones privadas tienen un precio promedio de 18 USD. Los anfitriones tienen una puntuación promedio de 4.82, con una puntuación promedio de 4.77 para la ubicación y de 4.73 para el valor.\n"
     ]
    }
   ],
   "source": [
    "print(chat_completion.choices[0].message.content)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa2834a3-8d70-4e62-b411-c72047de1fc7",
   "metadata": {},
   "source": [
    "`teknium/OpenHermes-2p5-Mistral-7B` @ $0.20/M"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "17699d61-f2d7-40ce-8716-210a9be72c78",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "chat_completion = completion(\n",
    "    messages=messages,\n",
    "    model=\"together_ai/teknium/OpenHermes-2p5-Mistral-7B\",\n",
    "    max_tokens=1024\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "d54d4741-327f-48be-b4bc-3d66cba853bc",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Hola amigo, te voy a contar sobre una propiedad que encontré en Zonaprop. La casa tiene un precio de venta de 81.650 dólares, pero ten en cuenta que hay gastos adicionales de 5.000 pesos argentinos. La casa fue publicada el 1 de febrero de 2024 y tiene un tamaño total de 51 metros cuadrados, con 51 metros cuadrados cubiertos. Tiene 3 ambientes, 1 baño, y es de 5 años de antigüedad. La casa tiene 1 piso y 9 metros cuadrados y medio cubiertos. También tiene 2 dormitorios, 0 metros de frente de terreno y 0 metros de largo de terreno. La casa ha sido vista 1.402 veces y es vista en promedio 63 veces al día. El precio es de 1.600 dólares por metro cuadrado.\n",
      "\n",
      "Ahora, cerca de esa casa, hay algunas casas en Airbnb. La probabilidad de alquilar es más probable, y el precio promedio por casa entera es de 49 dólares. El precio promedio por habitación privada es de 18 dólares. Las calificaciones promedio para la ubicación, la calidad y el valor son de 4.77, 4.82 y 4.73, respectivamente.\n",
      "\n",
      "Espero que esta información te haya sido útil. ¡Buen día!\n"
     ]
    }
   ],
   "source": [
    "print(chat_completion.choices[0].message.content)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f25454f-5123-42a3-87d0-56a15a331e7d",
   "metadata": {
    "tags": []
   },
   "source": [
    "`Qwen/Qwen1.5-14B-Chat` @ $0.30/M"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "24b7a4dc-68a0-4391-bd08-d81db696ff31",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "chat_completion = completion(\n",
    "    messages=messages,\n",
    "    model=\"together_ai/Qwen/Qwen1.5-14B-Chat\",\n",
    "    max_tokens=1024\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "03697c53-9dba-4bd0-a809-115ede2603b7",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "En Zonaprop, hay una propiedad en venta con un precio de venta de $81,650 dólares estadounidenses, incluyendo gastos de $5,000 pesos argentinos mensuales. La propiedad cuenta con 51 metros cuadrados de superficie total y cubierta, 3 habitaciones, un baño y una antigüedad de 5 años. Está ubicada en una sola planta y tiene una superficie semi-cubierta de 9 metros cuadrados. Ha recibido 1,402 visitas y promedio de 63 vistas diarias, con un costo de $1,600 dólares por metro cuadrado. Por otro lado, en las listas de Airbnb cercanas, el alquiler es considerado más probable, con precios promedio de $49 por el alojamiento entero y $18 por una habitación privada. Las calificaciones promedio son buenas, con puntajes de 4.82 en valor, 4.77 en ubicación y 4.73 en la calidad del servicio.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(chat_completion.choices[0].message.content)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02df4197-9064-497b-8dbc-71ebec22d3fd",
   "metadata": {},
   "source": [
    "### Lo formateamos en `Markdown` para que quede mas lindo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "id": "d7645580-7e1b-493a-9b96-14641bc11179",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def llm_response(row, model=\"together_ai/NousResearch/Nous-Hermes-2-Mixtral-8x7B-DPO\"):\n",
    "    info = info_del_listing(row)\n",
    "    prompt = f\"Return a dense summary from the given context:\\n```Zonaprop:{info[0]}\\n----\\nAirbnb listings nearby:\\n{info[1]}```\"\n",
    "    messages = [\n",
    "        {\"role\": \"system\", \"content\": system_instructions},\n",
    "        {\"role\": \"user\", \"content\": prompt},\n",
    "    ]\n",
    "    chat_completion = completion(\n",
    "        messages=messages,\n",
    "        model=model,\n",
    "        max_tokens=1024\n",
    "    )\n",
    "    return chat_completion.choices[0].message.content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "id": "d28b9478-701d-4248-a5ac-d9c5993ceed9",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import IPython.display as display\n",
    "from IPython.display import Markdown, HTML\n",
    "\n",
    "def mostrar_resumen_propiedad(row):\n",
    "    # nos traemos la respuesta del llm\n",
    "    llm_summary = llm_response(row)\n",
    "    \n",
    "    # formateamos el nro de wa\n",
    "    if pd.isna(row['whatsapp']):\n",
    "        whatsapp_url = \"https://www.youtube.com/watch?v=dQw4w9WgXcQ\"\n",
    "    else:\n",
    "        whatsapp_url = f\"https://wa.me/{int(row['whatsapp'])}?text=Que%20haces%20che,%20te%20quiero%20comprar%20esto,%20abrazo:%20{row['listing_url']}\"\n",
    " \n",
    "    # traemos el resto de la info en markdown\n",
    "    caracteristicas = '\\n'.join(f\"- {col}: {row[col]}\" for col in row.index if col not in ['photos', 'listing_url'] and 'airbnb_' not in col)\n",
    "\n",
    "    markdown_resumen = f\"\"\"\n",
    "---\n",
    "**[#{row.name}] {row['listing_url']}**\n",
    "\n",
    "- **Mapa de donde está:** {row['google_maps']}\n",
    "- **Mandale un whatsapp:** [apretá acá que se abre solo]({whatsapp_url})\n",
    "- **Resumen:** {llm_summary}\n",
    "\"\"\"\n",
    "\n",
    "    # mostrar el resumen en markdown\n",
    "    display.display(Markdown(markdown_resumen))\n",
    "\n",
    "    # agregar fotos en html con grilla\n",
    "    if row['photos']:\n",
    "        fotos = row['photos'][1:-1].replace(\"'\", \"\").split(', ')\n",
    "        fotos_html = '<div style=\"display: grid; grid-template-columns: repeat(3, 1fr); grid-gap: 5px;\">'\n",
    "        fotos_html += ''.join(f'<div style=\"width: 100%;\"><img src=\"{url.strip()}\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div>' for url in fotos[:6])\n",
    "        fotos_html += '</div>'\n",
    "        display.display(HTML(fotos_html))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "c7f7b44c-0c2e-4bf4-9a5f-a802c2bc9d26",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "\n",
       "---\n",
       "**[#1] https://www.zonaprop.com.ar/propiedades/clasificado/veclphin-venta-depto-tipo-ph-3-amb-baulera-colegiales-52938413.html**\n",
       "\n",
       "- **Mapa de donde está:** https://maps.google.com/?q=-34.5822573,-58.4533889\n",
       "- **Mandale un whatsapp:** [apretá acá que se abre solo](https://wa.me/5491126885078?text=Que%20haces%20che,%20te%20quiero%20comprar%20esto,%20abrazo:%20https://www.zonaprop.com.ar/propiedades/clasificado/veclphin-venta-depto-tipo-ph-3-amb-baulera-colegiales-52938413.html)\n",
       "- **Resumen:** En Zonaprop, se encuentra una propiedad en venta con un precio de 81,650 dólares estadounidenses, gastos comunes de 50,00 pesos argentinos, publicada el 1 de febrero de 2024. La propiedad cuenta con una superficie total y cubierta de 51 metros cuadrados, 3 ambientes, 1 baño, una antigüedad de 5 años, 1 planta, una superficie semicubierta de 9 metros cuadrados, 2 dormitorios, y un terreno de 0 metros cuadrados de frente y largo. La propiedad ha recibido 1,402 visitas y actualmente tiene un promedio de 63 visitas por día. El precio por metro cuadrado es de 1,600 dólares estadounidenses.\n",
       "En cuanto a los alquileres en Airbnb cercanos, es más probable que se alquilen propiedades enteras a un precio promedio de 49 dólares estadounidenses por noche, mientras que las habitaciones privadas tienen un precio promedio de 18 dólares por noche. La propiedad en Airbnb tiene una puntuación promedio de 4.82 en la calificación general, 4.77 en la ubicación y 4.73 en el valor.\n"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div style=\"display: grid; grid-template-columns: repeat(3, 1fr); grid-gap: 5px;\"><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/52/93/84/13/1200x1200/1895287915.jpg?isFirstImage=true\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/52/93/84/13/1200x1200/1895287927.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/52/93/84/13/1200x1200/1895287925.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/52/93/84/13/1200x1200/1895287921.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/52/93/84/13/1200x1200/1895287926.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/52/93/84/13/1200x1200/1895287922.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div></div>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# uso de la función para la primera fila\n",
    "mostrar_resumen_propiedad(a.loc[1])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5bc01141-a9cb-41be-b85d-edca3e1e960e",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Ahora sí, definimos un criterio para filtrar las propiedades"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "2973e2fe-e8a3-453c-8e4d-e19a6f87c23b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "id": "b493008f-a31f-4e16-90c4-69dbb973ff41",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def hacer_analisis(df, min_views=60, max_views=float('inf'), airbnb_listings=airbnb_listings, radius_km=0.3, show_top=10):\n",
    "    listings = df[(df.views_per_day > min_views) & (df.views_per_day <= max_views)].reset_index(drop=True)\n",
    "    listings = listings.head(show_top)\n",
    "    # listings = listings.dropna(axis=1, how='all')\n",
    "    listings = agregar_info_airbnb(listings, airbnb_listings, radius_km)\n",
    "    for index, row in tqdm(listings.iterrows(), desc=\"Llamando APIs ..\"):\n",
    "        mostrar_resumen_propiedad(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "944cdfac-fc64-4c8a-9c1f-8d1c8c3f4d12",
   "metadata": {},
   "source": [
    "Nos vamos a basar en esto que dice el groso de [Santiago Magnin](https://twitter.com/santivende) en este video [*\"GUÍA 2024 para COMPRADORES 💣 Webinar bomba 💣 ¿Cómo comprar con Javier Milei Presidente?\"*](https://www.youtube.com/watch?v=DuujwXarVbY&ab_channel=SantiagoMagnin).\n",
    "\n",
    "En resumen, para `Zonaprop`:\n",
    "- (1) ~60 visitas/dia, se reservan en la primera semana, negociación promedio del 2%\n",
    "- (2) ~30 visitas/dia, se reservan entre 8 y 30 dias, negociación promedio del 5%\n",
    "- (3) ~20 visitas/dia, se reservan entre 31 y 60 dias, negociación promedio del 7%\n",
    "- (4) ~10 visitas/dia, se reservan en más de 60 dias, negociación promedio del 8%"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "id": "f999cdb5-ad8b-46fc-8a97-c33b10f75674",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Llamando APIs ..: 0it [00:00, ?it/s]"
     ]
    },
    {
     "data": {
      "text/markdown": [
       "\n",
       "---\n",
       "**[#0] https://www.zonaprop.com.ar/propiedades/clasificado/veclapin-vidt-y-guemes-oportunidad!-49-m-sup2--a-nuevo!-a-1-53058867.html**\n",
       "\n",
       "- **Mapa de donde está:** https://maps.google.com/?q=-34.5888791,-58.4138553\n",
       "- **Mandale un whatsapp:** [apretá acá que se abre solo](https://www.youtube.com/watch?v=dQw4w9WgXcQ)\n",
       "- **Resumen:** En Zonaprop, se encuentra una propiedad en venta con un precio de 95,000 USD, gastos comunes de 57,00 ARS, publicada el 08-02-2024, ubicada en contrafrente, con una superficie total y cubierta de 49 m², 2 ambientes, 1 baño, 50 años de antigüedad, 1 plantas, y sin superficie semicubierta. La propiedad ha recibido 66 visitas de usuarios y tiene un promedio de 66 visitas por día, con un valor de 1938 USD por m² cubierto.\n",
       "En cuanto a los alquileres en Airbnb cercanos, la probabilidad de alquiler es menos probable. El precio promedio de una casa entera es de 59 USD, mientras que el precio promedio de una habitación privada es de 27 USD. La propiedad tiene una puntuación promedio de 4.78 en valoración general, 4.94 en ubicación y 4.73 en valoración del huésped.\n"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div style=\"display: grid; grid-template-columns: repeat(3, 1fr); grid-gap: 5px;\"><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/53/05/88/67/1200x1200/1897867014.jpg?isFirstImage=true\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/53/05/88/67/1200x1200/1897867006.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/53/05/88/67/1200x1200/1897867000.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/53/05/88/67/1200x1200/1897867017.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/53/05/88/67/1200x1200/1897867013.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/53/05/88/67/1200x1200/1897867016.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div></div>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Llamando APIs ..: 1it [00:07,  7.15s/it]"
     ]
    },
    {
     "data": {
      "text/markdown": [
       "\n",
       "---\n",
       "**[#1] https://www.zonaprop.com.ar/propiedades/clasificado/veclphin-venta-depto-tipo-ph-3-amb-baulera-colegiales-52938413.html**\n",
       "\n",
       "- **Mapa de donde está:** https://maps.google.com/?q=-34.5822573,-58.4533889\n",
       "- **Mandale un whatsapp:** [apretá acá que se abre solo](https://wa.me/5491126885078?text=Que%20haces%20che,%20te%20quiero%20comprar%20esto,%20abrazo:%20https://www.zonaprop.com.ar/propiedades/clasificado/veclphin-venta-depto-tipo-ph-3-amb-baulera-colegiales-52938413.html)\n",
       "- **Resumen:** En Zonaprop, se encuentra una propiedad en venta con un precio de 81,650 dólares estadounidenses, gastos comunes de 50,00 pesos argentinos, publicada el 1 de febrero de 2024. La propiedad cuenta con una superficie total y cubierta de 51 metros cuadrados, 3 ambientes, 1 baño, una antigüedad de 5 años, 1 planta, una superficie semicubierta de 9 metros cuadrados, 2 dormitorios, y un terreno de 0 metros cuadrados de frente y largo. La propiedad ha recibido 1,402 visitas y actualmente tiene un promedio de 63 visitas por día. El precio por metro cuadrado es de 1,600 dólares estadounidenses.\n",
       "En cuanto a los alquileres en Airbnb cercanos, es más probable que se alquilen propiedades enteras a un precio promedio de 49 dólares estadounidenses por noche, mientras que las habitaciones privadas tienen un precio promedio de 18 dólares por noche. La propiedad en Airbnb tiene una puntuación promedio de 4.82 en la calificación general, 4.77 en la ubicación y 4.73 en el valor.\n"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div style=\"display: grid; grid-template-columns: repeat(3, 1fr); grid-gap: 5px;\"><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/52/93/84/13/1200x1200/1895287915.jpg?isFirstImage=true\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/52/93/84/13/1200x1200/1895287927.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/52/93/84/13/1200x1200/1895287925.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/52/93/84/13/1200x1200/1895287921.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/52/93/84/13/1200x1200/1895287926.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/52/93/84/13/1200x1200/1895287922.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div></div>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Llamando APIs ..: 2it [00:15,  7.92s/it]"
     ]
    },
    {
     "data": {
      "text/markdown": [
       "\n",
       "---\n",
       "**[#2] https://www.zonaprop.com.ar/propiedades/clasificado/veclapin-departamento-en-duplex-a-refaccionar-excelente-52996658.html**\n",
       "\n",
       "- **Mapa de donde está:** https://maps.google.com/?q=-34.5899212,-58.4232282\n",
       "- **Mandale un whatsapp:** [apretá acá que se abre solo](https://wa.me/5491149939812?text=Que%20haces%20che,%20te%20quiero%20comprar%20esto,%20abrazo:%20https://www.zonaprop.com.ar/propiedades/clasificado/veclapin-departamento-en-duplex-a-refaccionar-excelente-52996658.html)\n",
       "- **Resumen:** En Zonaprop, se encuentra un departamento en venta con un precio de 75,000 USD, gastos comunes de 41,00 ARS, publicado el 08-02-2024, ubicado en frente y muy luminoso. Tiene una superficie total de 36 m², de los cuales 34 m² son superficie cubierta. Cuenta con 2 ambientes, 1 baño, 5 departamentos por piso y 6 pisos en el edificio. El departamento tiene 50 años de antigüedad, es apto para crédito y tiene 2 plantas. Ha recibido 948 visitas de usuarios y 94 vistas por día. El precio por metro cuadrado es de 2,083.00 USD.\n",
       "En cuanto a Airbnb, en la zona hay alquileres de propiedades completas con un precio promedio de 66 USD, alquileres de habitaciones privadas con un precio promedio de 23 USD, y una puntuación promedio de 4.79 en la calidad general, 4.93 en la ubicación y 4.73 en el valor. La probabilidad de alquiler en la zona es menos probable.\n"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div style=\"display: grid; grid-template-columns: repeat(3, 1fr); grid-gap: 5px;\"><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/resize/1/00/52/99/66/58/1200x1200/1896588321.jpg?isFirstImage=true\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/resize/1/00/52/99/66/58/1200x1200/1896588323.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/resize/1/00/52/99/66/58/1200x1200/1896588327.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/resize/1/00/52/99/66/58/1200x1200/1896588312.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/resize/1/00/52/99/66/58/1200x1200/1896588316.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/resize/1/00/52/99/66/58/1200x1200/1896588329.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div></div>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Llamando APIs ..: 3it [00:22,  7.64s/it]"
     ]
    },
    {
     "data": {
      "text/markdown": [
       "\n",
       "---\n",
       "**[#3] https://www.zonaprop.com.ar/propiedades/clasificado/veclphin-ph-en-colegiales-52997811.html**\n",
       "\n",
       "- **Mapa de donde está:** https://maps.google.com/?q=-34.5731928,-58.4402904\n",
       "- **Mandale un whatsapp:** [apretá acá que se abre solo](https://wa.me/5491165517219?text=Que%20haces%20che,%20te%20quiero%20comprar%20esto,%20abrazo:%20https://www.zonaprop.com.ar/propiedades/clasificado/veclphin-ph-en-colegiales-52997811.html)\n",
       "- **Resumen:** En Zonaprop, se encuentra una propiedad en venta con un precio de 130,00 USD, sin gastos comunes en pesos argentinos. Publicada el 08-02-2024, esta propiedad cuenta con una superficie total y cubierta de 66 m², con 3 ambientes, 1 pisos, 1 baños y 2 dormitorios. La antigüedad es de 70 años y no se proporciona información sobre el terreno. La propiedad ha recibido 610 visitas y 61 visitas por día, con un valor de 1969 USD por m² cubierto.\n",
       "En cuanto a Airbnb, se encuentran alquileres cercanos con una mayor probabilidad de alquiler. El precio promedio para una casa entera es de 52 USD, mientras que para una habitación privada es de 26 USD. La propiedad promedio tiene una puntuación de 4.73 en valoración general, 4.82 en ubicación y 4.67 en valoración del alojamiento.\n"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div style=\"display: grid; grid-template-columns: repeat(3, 1fr); grid-gap: 5px;\"><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/52/99/78/11/1200x1200/1896609836.jpg?isFirstImage=true\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/52/99/78/11/1200x1200/1896609829.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/52/99/78/11/1200x1200/1896609830.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/52/99/78/11/1200x1200/1896609841.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/52/99/78/11/1200x1200/1896609838.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div><div style=\"width: 100%;\"><img src=\"https://imgar.zonapropcdn.com/avisos/1/00/52/99/78/11/1200x1200/1896609848.jpg\" style=\"width: 100%; max-height: 200px; object-fit: cover;\" /></div></div>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Llamando APIs ..: 4it [00:28,  7.06s/it]\n"
     ]
    }
   ],
   "source": [
    "hacer_analisis(zonaprop_listings, show_top=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d1f4409-541c-4939-bae2-6432690d1ff8",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "machinelearnear-zonaprop:Python",
   "language": "python",
   "name": "conda-env-machinelearnear-zonaprop-py"
  },
  "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.9.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}