03_match_respuestas_inmobiliarias_round_1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Codigo para clasificar mensajes recibidos + matchear\n",
    "#Ultima actualizacion: 04/Ago/2022\n",
    "\n",
    "# Inputs: \n",
    "#       (A) sent_mails_conf_envio_[FECHA] (del codigo anterior), que tiene las respuestas que se enviaron efectivamente y \n",
    "#       (B) base de mails con respuestas de inmobiliarias (puede haber de properati, no es problema).\n",
    "# Outputs: \n",
    "#       (A) respuestas_inmobiliarias_[FECHA], que agrega TODAS las publicaciones y respuestas, matcheadas o no (despues se puede filtrar por lo que queramos), y \n",
    "#       (B) resultados_matching_rtas_[FECHA] que muestra, para cada mail, la cantidad de respuestas matcheadas y los % de match.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importar paquetes necesarios para la clasificacion\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import datetime\n",
    "import matching_v1 as matching\n",
    "import tools\n",
    "\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.options.mode.chained_assignment = None  # default='warn'\n",
    "\n",
    "#Establecer rutas de entrada y salida para las bases (si se cambian, comentar las que no se usen)\n",
    "\n",
    "# Ronda y fecha de la ronda\n",
    "ronda = '1'\n",
    "fecha = '2022_04_19'\n",
    "ronda2 = '2'\n",
    "fecha2 = '2022_05_04'\n",
    "\n",
    "fecha_descarga = '2022_05_19'\n",
    "\n",
    "#Rutas Nico\n",
    "path_dropbox = r\"C:\\Users\\Nico\\Dropbox\"\n",
    "\n",
    "#Rutas Juli\n",
    "# path_data = r\"C:\\Users\\Nico\\Dropbox\\BID-LGBTQ+\\campo\"\n",
    "\n",
    "#Rutas Luis\n",
    "# path_data = r\"C:\\Users\\Nico\\Dropbox\\BID-LGBTQ+\\campo\"\n",
    "\n",
    "#Rutas generadas:\n",
    "path_data = path_dropbox + r\"\\BID-LGBTQ+\\campo\\data\"\n",
    "path_datain = path_data + r\"\\mails_matches\"\n",
    "path_dataout = path_data + fr\"\\mails_matches\\matched\\respuestas_inmobiliarias\\round{ronda}\"\n",
    "path_deleted = path_data + r\"\\mails_matches\\deleted\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Importar base de mails recibidos (todos)\n",
    "rtas_full = pd.read_excel(path_datain + fr\"\\to_match\\mails_total_2022_05_19.xlsx\") # 2022_05_19 Ultima descarga de rtas de gmail\n",
    "\n",
    "#Importar base de mails con confirmación de envios\n",
    "enviados = pd.read_excel(path_datain + fr\"\\matched\\confirmaciones_de_envio\\sent_mails_conf_envio_round_{ronda}_2022_08_04.xlsx\")\n",
    "enviados = enviados.drop(columns=['Unnamed: 0.1','_merge'])\n",
    "enviados = enviados[-enviados.body.str.contains(\"miberniel\")] # Elimino las observaciones que la propiedad de Inés"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "both          3633\n",
      "right_only    3595\n",
      "left_only        0\n",
      "Name: _merge, dtype: int64\n",
      "Hay valores diferentes en las columans inmobiliaria_y y inmobiliaria_x\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>inmobiliaria_x</th>\n",
       "      <th>inmobiliaria_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>duchini propiedades</td>\n",
       "      <td>palchevich propiedades</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>conin propiedades</td>\n",
       "      <td>botto nunez propiedades</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          inmobiliaria_x           inmobiliaria_y\n",
       "82   duchini propiedades   palchevich propiedades\n",
       "136    conin propiedades  botto nunez propiedades"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Hay valores diferentes en las columans fecha_y y fecha_x\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>fecha_x</th>\n",
       "      <th>fecha_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>2022-02-02</td>\n",
       "      <td>2022-04-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>2022-03-08</td>\n",
       "      <td>2022-03-16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       fecha_x    fecha_y\n",
       "82  2022-02-02 2022-04-11\n",
       "136 2022-03-08 2022-03-16"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Hay valores diferentes en las columans p_m2_above_mdn_y y p_m2_above_mdn_x\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>p_m2_above_mdn_x</th>\n",
       "      <th>p_m2_above_mdn_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     p_m2_above_mdn_x  p_m2_above_mdn_y\n",
       "136               1.0               0.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Hay valores diferentes en las columans n_adds_y y n_adds_x\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>n_adds_x</th>\n",
       "      <th>n_adds_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    n_adds_x  n_adds_y\n",
       "82       8.0       3.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Hay valores diferentes en las columans common_name_y y common_name_x\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>common_name_x</th>\n",
       "      <th>common_name_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    common_name_x  common_name_y\n",
       "82            2.0            1.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Importo bases de seleccion (tienen la variable nombre, n_adds_abv_mun y algunas otras)\n",
    "files = tools.crea_lista_de_archivos(path_data + fr\"\\scrape\\selection\\round1\", \"dta\")\n",
    "files = [f for f in files if 'scrape_select' in f]\n",
    "\n",
    "df_concat = []\n",
    "for file in files:\n",
    "    df_temp = pd.read_stata(file)\n",
    "    df_concat += [df_temp]\n",
    "    \n",
    "select_full = pd.concat(df_concat)\n",
    "select_full = select_full.reset_index(drop=True)\n",
    "select_full = select_full.drop_duplicates(subset=['link','id'], keep='first')\n",
    "\n",
    "#Formateo variable 'cocheras'\n",
    "enviados['cocheras'] = pd.to_numeric(enviados.cocheras, errors='coerce')\n",
    "select_full['cocheras'] = pd.to_numeric(select_full.cocheras, errors='coerce')\n",
    "\n",
    "#Agrego variable \"nombre\"\n",
    "enviados = enviados.merge(select_full, on = ['link','id'], how  = 'outer', indicator=True)\n",
    "print(enviados._merge.value_counts())\n",
    "\n",
    "# Como left_only == 0 y both=3633 (observaciones que tenía en confirmadas en enviados), está todo ok! (nota: las right_only son observaciones de la ronda 2!!!)\n",
    "# Hago el inner join asi vuelo las que no son de esta ronda (dropeo las que no son both)\n",
    "enviados = enviados[enviados['_merge']=='both']\n",
    "enviados = tools.normaliza_columnas_post_merge(enviados, suffixes=('_x','_y'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "enviados = tools.normaliza_columnas_post_merge(enviados, suffixes=('_x','_y'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "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>link</th>\n",
       "      <th>id</th>\n",
       "      <th>treatment</th>\n",
       "      <th>ses</th>\n",
       "      <th>male_name</th>\n",
       "      <th>mail</th>\n",
       "      <th>male_couple</th>\n",
       "      <th>female_couple</th>\n",
       "      <th>script</th>\n",
       "      <th>script_string</th>\n",
       "      <th>script_string_2</th>\n",
       "      <th>script_string_3</th>\n",
       "      <th>randomization_seed</th>\n",
       "      <th>index_rtas</th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>subject</th>\n",
       "      <th>from</th>\n",
       "      <th>to</th>\n",
       "      <th>body</th>\n",
       "      <th>date</th>\n",
       "      <th>cc</th>\n",
       "      <th>thread_id</th>\n",
       "      <th>mimeType</th>\n",
       "      <th>conf_envio</th>\n",
       "      <th>date_clean</th>\n",
       "      <th>index_scrap</th>\n",
       "      <th>match_string</th>\n",
       "      <th>match2_string</th>\n",
       "      <th>pais</th>\n",
       "      <th>Unnamed01</th>\n",
       "      <th>Unnamed011</th>\n",
       "      <th>_merge</th>\n",
       "      <th>match_confiab</th>\n",
       "      <th>match_step</th>\n",
       "      <th>nombre</th>\n",
       "      <th>inmobiliaria</th>\n",
       "      <th>fecha</th>\n",
       "      <th>municipio</th>\n",
       "      <th>ciudad</th>\n",
       "      <th>descripcion</th>\n",
       "      <th>nombre_2</th>\n",
       "      <th>precio</th>\n",
       "      <th>moneda</th>\n",
       "      <th>caracteristicas</th>\n",
       "      <th>ambientes</th>\n",
       "      <th>habitaciones</th>\n",
       "      <th>banos</th>\n",
       "      <th>m2_totales</th>\n",
       "      <th>m2_cubiertos</th>\n",
       "      <th>cocheras</th>\n",
       "      <th>profesional</th>\n",
       "      <th>vivienda</th>\n",
       "      <th>precio_local</th>\n",
       "      <th>p_m2</th>\n",
       "      <th>p_m2_above_mdn</th>\n",
       "      <th>n_adds</th>\n",
       "      <th>n_adds_above_mdn</th>\n",
       "      <th>n_adds_muni</th>\n",
       "      <th>n_adds_muni_above_mdn</th>\n",
       "      <th>m2_above_mdn</th>\n",
       "      <th>common_name</th>\n",
       "      <th>capital</th>\n",
       "      <th>random</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>properati.com.ar/detalle/49c41_alquiler_depart...</td>\n",
       "      <td>dch dch 325</td>\n",
       "      <td>homo</td>\n",
       "      <td>neutral</td>\n",
       "      <td>Pablo</td>\n",
       "      <td>fh.pablo.gomez@gmail.com</td>\n",
       "      <td>Juan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Buenos días, mi nombre es Pablo, junto a mi pa...</td>\n",
       "      <td>Pablo y Juan.</td>\n",
       "      <td>Código dch dch 325</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>772.0</td>\n",
       "      <td>Hiciste una consulta por la propiedad Loria Al...</td>\n",
       "      <td>Properati &lt;contacts@mailing.properati.com.ar&gt;</td>\n",
       "      <td>fh.pablo.gomez@gmail.com</td>\n",
       "      <td>[&lt;p&gt;-- SOLICITUD DE ENTREVISTA --\\r\\n\\r\\n*DATO...</td>\n",
       "      <td>Mon, 25 Apr 2022 16:25:21 +0000 (UTC)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>180618ab8706bb12</td>\n",
       "      <td>multipart/alternative</td>\n",
       "      <td>True</td>\n",
       "      <td>2022-04-25</td>\n",
       "      <td>89.0</td>\n",
       "      <td>[Match(start=1426, end=1441, dist=0, matched='...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>arg</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>both</td>\n",
       "      <td>100.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>departamento en alquiler en uruguay 900. entre...</td>\n",
       "      <td>duchini propiedades</td>\n",
       "      <td>2022-02-02</td>\n",
       "      <td>caba</td>\n",
       "      <td>buenos aires ampliado</td>\n",
       "      <td>departamento  2 ambientes , contrafrente con p...</td>\n",
       "      <td>departamento en alquiler en loria al 100</td>\n",
       "      <td>28000.0</td>\n",
       "      <td>$</td>\n",
       "      <td>['2ambientes', '1bano']</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.6544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>properati.com.ar/detalle/4cz6e_alquiler_depart...</td>\n",
       "      <td>617935</td>\n",
       "      <td>homo</td>\n",
       "      <td>neutral</td>\n",
       "      <td>Manuel</td>\n",
       "      <td>jcmanuel.rodriguez@gmail.com</td>\n",
       "      <td>Luis</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Buenos días, mi nombre es Manuel, junto a mi p...</td>\n",
       "      <td>Manuel y Luis.</td>\n",
       "      <td>Código 617935</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2488.0</td>\n",
       "      <td>Hiciste una consulta por la propiedad Esmerald...</td>\n",
       "      <td>Properati &lt;contacts@mailing.properati.com.ar&gt;</td>\n",
       "      <td>jcmanuel.rodriguez@gmail.com</td>\n",
       "      <td>[&lt;p&gt;-- SOLICITUD DE ENTREVISTA --\\r\\n\\r\\n*DATO...</td>\n",
       "      <td>Mon, 25 Apr 2022 16:24:27 +0000 (UTC)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1806189e2fa979f7</td>\n",
       "      <td>multipart/alternative</td>\n",
       "      <td>True</td>\n",
       "      <td>2022-04-25</td>\n",
       "      <td>85.0</td>\n",
       "      <td>[Match(start=1364, end=1376, dist=0, matched='...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>arg</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>both</td>\n",
       "      <td>100.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>departamento en alquiler en esmeralda  900</td>\n",
       "      <td>conin propiedades</td>\n",
       "      <td>2022-03-08</td>\n",
       "      <td>caba</td>\n",
       "      <td>buenos aires ampliado</td>\n",
       "      <td>departamento en alquiler ubicado en esmeralda ...</td>\n",
       "      <td>departamento en alquiler en esmeralda  900</td>\n",
       "      <td>49000.0</td>\n",
       "      <td>$</td>\n",
       "      <td>['2habitaciones', '1bano', '42 m²totales']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>49000.0</td>\n",
       "      <td>1166.6666</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.8712</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  link           id treatment  \\\n",
       "82   properati.com.ar/detalle/49c41_alquiler_depart...  dch dch 325      homo   \n",
       "136  properati.com.ar/detalle/4cz6e_alquiler_depart...       617935      homo   \n",
       "\n",
       "         ses male_name                          mail male_couple  \\\n",
       "82   neutral     Pablo      fh.pablo.gomez@gmail.com        Juan   \n",
       "136  neutral    Manuel  jcmanuel.rodriguez@gmail.com        Luis   \n",
       "\n",
       "    female_couple  script                                      script_string  \\\n",
       "82            NaN     1.0  Buenos días, mi nombre es Pablo, junto a mi pa...   \n",
       "136           NaN     1.0  Buenos días, mi nombre es Manuel, junto a mi p...   \n",
       "\n",
       "    script_string_2     script_string_3  randomization_seed  index_rtas  \\\n",
       "82    Pablo y Juan.  Código dch dch 325                20.0         0.0   \n",
       "136  Manuel y Luis.       Código 617935                20.0         1.0   \n",
       "\n",
       "     Unnamed: 0                                            subject  \\\n",
       "82        772.0  Hiciste una consulta por la propiedad Loria Al...   \n",
       "136      2488.0  Hiciste una consulta por la propiedad Esmerald...   \n",
       "\n",
       "                                              from  \\\n",
       "82   Properati <contacts@mailing.properati.com.ar>   \n",
       "136  Properati <contacts@mailing.properati.com.ar>   \n",
       "\n",
       "                               to  \\\n",
       "82       fh.pablo.gomez@gmail.com   \n",
       "136  jcmanuel.rodriguez@gmail.com   \n",
       "\n",
       "                                                  body  \\\n",
       "82   [<p>-- SOLICITUD DE ENTREVISTA --\\r\\n\\r\\n*DATO...   \n",
       "136  [<p>-- SOLICITUD DE ENTREVISTA --\\r\\n\\r\\n*DATO...   \n",
       "\n",
       "                                      date  cc         thread_id  \\\n",
       "82   Mon, 25 Apr 2022 16:25:21 +0000 (UTC) NaN  180618ab8706bb12   \n",
       "136  Mon, 25 Apr 2022 16:24:27 +0000 (UTC) NaN  1806189e2fa979f7   \n",
       "\n",
       "                  mimeType conf_envio date_clean  index_scrap  \\\n",
       "82   multipart/alternative       True 2022-04-25         89.0   \n",
       "136  multipart/alternative       True 2022-04-25         85.0   \n",
       "\n",
       "                                          match_string  match2_string pais  \\\n",
       "82   [Match(start=1426, end=1441, dist=0, matched='...            NaN  arg   \n",
       "136  [Match(start=1364, end=1376, dist=0, matched='...            NaN  arg   \n",
       "\n",
       "     Unnamed01  Unnamed011 _merge  match_confiab  match_step  \\\n",
       "82         NaN         NaN   both          100.0         1.0   \n",
       "136        NaN         NaN   both          100.0         1.0   \n",
       "\n",
       "                                                nombre         inmobiliaria  \\\n",
       "82   departamento en alquiler en uruguay 900. entre...  duchini propiedades   \n",
       "136         departamento en alquiler en esmeralda  900    conin propiedades   \n",
       "\n",
       "         fecha municipio                 ciudad  \\\n",
       "82  2022-02-02      caba  buenos aires ampliado   \n",
       "136 2022-03-08      caba  buenos aires ampliado   \n",
       "\n",
       "                                           descripcion  \\\n",
       "82   departamento  2 ambientes , contrafrente con p...   \n",
       "136  departamento en alquiler ubicado en esmeralda ...   \n",
       "\n",
       "                                       nombre_2   precio moneda  \\\n",
       "82     departamento en alquiler en loria al 100  28000.0      $   \n",
       "136  departamento en alquiler en esmeralda  900  49000.0      $   \n",
       "\n",
       "                                caracteristicas  ambientes  habitaciones  \\\n",
       "82                      ['2ambientes', '1bano']        2.0           NaN   \n",
       "136  ['2habitaciones', '1bano', '42 m²totales']        NaN           2.0   \n",
       "\n",
       "     banos  m2_totales  m2_cubiertos  cocheras  profesional  vivienda  \\\n",
       "82     1.0         NaN           NaN       NaN          0.0       0.0   \n",
       "136    1.0        42.0           NaN       NaN          0.0       0.0   \n",
       "\n",
       "     precio_local       p_m2  p_m2_above_mdn  n_adds  n_adds_above_mdn  \\\n",
       "82        28000.0        NaN             NaN     8.0               1.0   \n",
       "136       49000.0  1166.6666             1.0     1.0               0.0   \n",
       "\n",
       "     n_adds_muni  n_adds_muni_above_mdn  m2_above_mdn  common_name  capital  \\\n",
       "82           3.0                    1.0           NaN          2.0      1.0   \n",
       "136          1.0                    0.0           0.0          1.0      1.0   \n",
       "\n",
       "     random  \n",
       "82   0.6544  \n",
       "136  0.8712  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "enviados.loc[[82,136],:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Se dropearon 423 observaciones.\n",
      "Se dropearon 221 observaciones.\n",
      "Se dropearon 146 observaciones.\n",
      "Se dropearon 656 observaciones.\n"
     ]
    }
   ],
   "source": [
    "#### Elimino observaciones que se corresponden a mails de prueba o de Google\n",
    "\n",
    "# Dropeo observaciones que tienen Google en el mail / user\n",
    "prev = len(rtas_full)\n",
    "rtas_full_out = rtas_full[rtas_full['from'].str.contains(\"Google\", na=False) == True]\n",
    "rtas_full =     rtas_full[rtas_full['from'].str.contains(\"Google\", na=False) == False]\n",
    "print(\"Se dropearon\", prev - len(rtas_full), \"observaciones.\")\n",
    "\n",
    "# Dropeo observaciones de prueba de Juli\n",
    "prev = len(rtas_full) \n",
    "to_concat = rtas_full[rtas_full['from'].str.contains(\"Julian test\", na=False) == True]\n",
    "rtas_full = rtas_full[rtas_full['from'].str.contains(\"Julian test\", na=False) == False]\n",
    "rtas_full_out = pd.concat([rtas_full_out,rtas_full[rtas_full['from'].str.contains(\"Julian test\", na=False) == True]])\n",
    "\n",
    "print(\"Se dropearon\", prev - len(rtas_full), \"observaciones.\")\n",
    "\n",
    "# Dropeo observaciones de prueba\n",
    "prev = len(rtas_full) \n",
    "to_concat = rtas_full[rtas_full['subject'].str.contains(\"prueba\", na=False) == True]\n",
    "rtas_full = rtas_full[rtas_full['subject'].str.contains(\"prueba\", na=False) == False]\n",
    "rtas_full_out = pd.concat([rtas_full_out,to_concat])\n",
    "print(\"Se dropearon\", prev - len(rtas_full), \"observaciones.\")\n",
    "\n",
    "# Base de correos desde los que enviamos\n",
    "mails_df = pd.read_excel(path_dropbox + r\"\\BID-LGBTQ+\\campo\\0_instrucciones\\2-Emails para Intervención.xlsx\")\n",
    "mails_df['Usuario_clean'] = mails_df.Usuario.apply(matching.clean_mail)\n",
    "users = mails_df['Usuario_clean'].to_list()\n",
    "\n",
    "# Dropeo observaciones de prueba (from == to, ¡nos los autoenviamos!)\n",
    "prev = len(rtas_full) \n",
    "for user in users:\n",
    "    to_concat = rtas_full[rtas_full['from'].apply(matching.clean_mail).str.contains(user)==True ]\n",
    "    rtas_full = rtas_full[rtas_full['from'].apply(matching.clean_mail).str.contains(user)==False]\n",
    "    rtas_full_out = pd.concat([rtas_full_out,to_concat])\n",
    "print(\"Se dropearon\", prev - len(rtas_full), \"observaciones.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Se dropearon 20 observaciones.\n",
      "Match confirmaciones realizado.\n",
      "Resultados:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True     7281\n",
       "False    4931\n",
       "Name: conf_envio, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##### Identifica mails que vienen de properati 'contacts@mailing.properati.com'\n",
    "\n",
    "# Filtro nulls\n",
    "before = len(rtas_full)\n",
    "rtas_full = rtas_full.dropna(subset=['from','body'])\n",
    "after = len(rtas_full)\n",
    "print(\"Se dropearon\", before-after, \"observaciones.\")\n",
    "\n",
    "# Creo binaria con True para los mails de properati\n",
    "mails_properati = ['contacts@mailing.properati.com']\n",
    "\n",
    "rtas_full['conf_envio'] = rtas_full['from'].apply(matching.match_string_con_lista, lista_matchs=mails_properati)\n",
    "print(\"Match confirmaciones realizado.\\nResultados:\")\n",
    "\n",
    "# Reporta los resultados\n",
    "rtas_full['conf_envio'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "enviados = enviados[enviados.mail.notnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "##### Limpio las fechas\n",
    "rtas_full['date_clean'] = rtas_full['date'].str.replace(\".+,\\s\",\"\",regex=True)\n",
    "rtas_full['date_clean'] = rtas_full['date_clean'].str.replace(\"\\-|\\+|(0\\d00)(.*)\",\"\",regex=True)\n",
    "rtas_full['date_clean'] = rtas_full['date_clean'].str.replace(\"GMT\",\"\")\n",
    "rtas_full['date_clean'] = rtas_full['date_clean'].str.lstrip(' ').str.rstrip(' ')\n",
    "rtas_full['date_clean'] = rtas_full['date_clean'].str.zfill(20)\n",
    "rtas_full['date_clean'] = pd.to_datetime(rtas_full['date_clean'], format=\"%d %b %Y %H:%M:%S\")\n",
    "rtas_full['date_clean'] = rtas_full['date_clean'].dt.date  # Solo me quedo dia-mes-año"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "###########################################################\n",
      "Cantidad de Respuestas: 2402 \n",
      "###########################################################\n",
      "Limpio nombre de inmobiliarias, precios y calles:\n",
      "palabras eliminadas: ['en', 'alquiler', 'departamento', 'al', '', 'de', 'y', 'entre', 'arriendo', 'apartamento', 'av.']\n",
      "palabras eliminadas: []\n",
      "palabras eliminadas: ['propiedades', 'inmobiliaria', 'inmobiliarios', '', 'bienes', 'negocios', 'raices', 'inmobiliario']\n",
      "Despues de la limpieza... ¿hay repetidas?\n",
      "False    2732\n",
      "True      901\n",
      "Name: inmobiliaria_c, dtype: int64\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 192/192 [09:44<00:00,  3.05s/it]\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Nico\\Dropbox\\BID-LGBTQ+\\campo\\data\\mails_matches\\matched\\respuestas_inmobiliarias\\round1\\resultados_matching_rtas_2022_08_11.xlsx creado\n",
      "C:\\Users\\Nico\\Dropbox\\BID-LGBTQ+\\campo\\data\\mails_matches\\matched\\respuestas_inmobiliarias\\round1\\respuestas_inmobiliarias_2022_08_11_matched.xlsx creado\n",
      "end of script\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>Matchs</th>\n",
       "      <th>Porcentaje</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>rodriguezmanuel.w@gmail.com</th>\n",
       "      <td>9</td>\n",
       "      <td>0.409091</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rodriguezmanuelw@gmail.com</th>\n",
       "      <td>11</td>\n",
       "      <td>0.687500</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>gomez.pablo.u@gmail.com</th>\n",
       "      <td>17</td>\n",
       "      <td>0.653846</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fh.pablo.gomez@gmail.com</th>\n",
       "      <td>21</td>\n",
       "      <td>0.512195</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>gomez.pablou@gmail.com</th>\n",
       "      <td>10</td>\n",
       "      <td>0.454545</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bflores.danielj@gmail.com</th>\n",
       "      <td>4</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bdominguezalejandroc@gmail.com</th>\n",
       "      <td>3</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bflores.daniel.j@gmail.com</th>\n",
       "      <td>3</td>\n",
       "      <td>0.600000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bdominguez.alejandro.c@gmail.com</th>\n",
       "      <td>1</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bfloresdanielj@gmail.com</th>\n",
       "      <td>2</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>192 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  Matchs  Porcentaje  Total\n",
       "rodriguezmanuel.w@gmail.com            9    0.409091     22\n",
       "rodriguezmanuelw@gmail.com            11    0.687500     16\n",
       "gomez.pablo.u@gmail.com               17    0.653846     26\n",
       "fh.pablo.gomez@gmail.com              21    0.512195     41\n",
       "gomez.pablou@gmail.com                10    0.454545     22\n",
       "...                                  ...         ...    ...\n",
       "bflores.danielj@gmail.com              4    0.500000      8\n",
       "bdominguezalejandroc@gmail.com         3    0.750000      4\n",
       "bflores.daniel.j@gmail.com             3    0.600000      5\n",
       "bdominguez.alejandro.c@gmail.com       1    0.250000      4\n",
       "bfloresdanielj@gmail.com               2    0.333333      6\n",
       "\n",
       "[192 rows x 3 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##### Matcheo respuestas (ya mergeadas con @properati) con scraps\n",
    "\n",
    "# lista_paises=[]\n",
    "fin_ronda_1 = pd.to_datetime(\"2022-05-03\").date()\n",
    "rtas_inmob = rtas_full.loc[(rtas_full['date_clean']<fin_ronda_1) & (rtas_full['conf_envio']==False)]\n",
    "\n",
    "today = datetime.now()\n",
    "today = today.strftime(\"%Y_%m_%d\")\n",
    "nombre = f'mails_total_matched_round_{ronda}_{today}'\n",
    "\n",
    "rtas_inmob['index_scrap'] = np.nan\n",
    "rtas_inmob['match_confiab'] = np.nan\n",
    "rtas_inmob['match_step'] = np.nan\n",
    "rtas_inmob['match_string'] = np.nan\n",
    "rtas_inmob['match2_string'] = np.nan\n",
    "enviados['index_rtas'] = np.nan\n",
    "enviados['match_confiab'] = np.nan\n",
    "enviados['match_step'] = np.nan\n",
    "\n",
    "matching.full_proceso(path=path_dataout, rtas_full=rtas_inmob, scrap_full=enviados)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Importo todos los outs y mergeo en una única\n",
    "\n",
    "# rtas_ARG = pd.read_excel(path_datain + fr'\\mails_ARG_round_1_{fecha_rtas}.xlsx')\n",
    "# rtas_COL = pd.read_excel(path_datain + fr'\\mails_COL_round_1_{fecha_rtas}.xlsx')\n",
    "# rtas_ECU = pd.read_excel(path_datain + fr'\\mails_ECU_round_1_{fecha_rtas}.xlsx')\n",
    "# rtas_PER = pd.read_excel(path_datain + fr'\\mails_PER_round_1_{fecha_rtas}.xlsx')\n",
    "\n",
    "# rtas_full = pd.concat([rtas_ARG,rtas_COL,rtas_ECU,rtas_PER])\n",
    "\n",
    "# #Importar base de scraps \n",
    "# fecha_scrap = '2020_04_11'\n",
    "# scrap_ARG = pd.read_excel(path_datain + '\\inmobiliarias_ARG_{fecha_scrap}.xlsx')\n",
    "# scrap_COL = pd.read_excel(path_datain + '\\inmobiliarias_COL_{fecha_scrap}.xlsx')\n",
    "# scrap_ECU = pd.read_excel(path_datain + '\\inmobiliarias_ECU_{fecha_scrap}.xlsx')\n",
    "# scrap_PER = pd.read_excel(path_datain + '\\inmobiliarias_PER_{fecha_scrap}.xlsx')\n",
    "\n",
    "# scrap_full = pd.concat([scrap_ARG,scrap_COL,scrap_ECU,scrap_PER])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.0 64-bit",
   "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.10.0"
  },
  "vscode": {
   "interpreter": {
    "hash": "89e0abca33cf4a51fed40e84035a9d07b9b47798c9ddd5755346d755ba15d037"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}