01. Preprocesado.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](http://www.upm.es/sfs/Rectorado/Gabinete%20del%20Rector/Logos/UPM/EscPolitecnica/EscUpmPolit_p.gif \"UPM\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Trabajo final SITC\n",
    "## Análisis de sentimientos en Twitter"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Departamento de Ingeniería de Sistemas Telemáticos. Universidad Politécnica de Madrid."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Realizado por:\n",
    "- Juan Bermudo Mera\n",
    "- Margarita Bolívar Jiménez\n",
    "- Lourdes Fernández Nieto\n",
    "- Ramón Pérez Hernández\n",
    "\n",
    "© 2017"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preprocesado del Corpus y del fichero de tweets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tabla de contenidos\n",
    "\n",
    "* [Procesado del Corpus anotado TASS](#1.-Procesado-del-Corpus-anotado-TASS)\n",
    "\t* [Conversión del fichero XML a dataframe](#Conversión-del-fichero-XML-a-dataframe)\n",
    "    * [Procesado del dataframe](#Procesado-del-dataframe)\n",
    "    * [Binarización del sentimiento](#Binarización-del-sentimiento)\n",
    "* [Procesado de los tweets con geolocalización](#2.-Procesado-de-los-tweets-con-geolocalización)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Se importan todas las librerías necesarias (Algunas necesitan instalación con: pip install <nombre_paquete>)\n",
    "\n",
    "# Detección del lenguaje \n",
    "import langid\n",
    "from langdetect import detect\n",
    "import textblob\n",
    "\n",
    "# Procesado del fichero xml\n",
    "import xml.etree.ElementTree as ET\n",
    "from lxml import etree\n",
    "import pandas as pd\n",
    "import re\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Procesado del Corpus anotado TASS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* ### Conversión del fichero XML a dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Lectura del fichero xml con el corpus anotado TASS\n",
    "xml_data = 'ficheros/Raw/general-tweets-train-tagged.xml'\n",
    "\n",
    "# Conversión del fichero xml a un dataframe\n",
    "def xml2df(xml_data):\n",
    "    parser = ET.XMLParser(encoding='utf-8')\n",
    "    tree = ET.parse(xml_data, parser)\n",
    "    root = tree.getroot()\n",
    "    all_records = []\n",
    "    headers = ['tweetid','user','content','date','lang','topic','polarity_value','polarity_type']\n",
    "\n",
    "    for tweet in root.iter('tweet'):\n",
    "        record = []\n",
    "        record.append(tweet.find('tweetid').text)\n",
    "        record.append(tweet.find('user').text)\n",
    "        record.append(tweet.find('content').text)\n",
    "        record.append(tweet.find('date').text)\n",
    "        record.append(tweet.find('lang').text)\n",
    "        \n",
    "        t = \"\"\n",
    "        for topics in tweet.iter('topics'):\n",
    "            for topic in topics.iter('topic'):\n",
    "                t = t+\" \"+topic.text\n",
    "        record.append(t)\n",
    "\n",
    "        for sentiments in tweet.iter('sentiments'):\n",
    "            #pol_ent = \"\"\n",
    "            pol_val = \"\"\n",
    "            pol_type = \"\"\n",
    "            for polarity in sentiments.iter('polarity'):\n",
    "                #pol_ent = pol_ent+\" \"+polarity.find('entity').text\n",
    "                pol_val = pol_val+\" \"+polarity.find('value').text\n",
    "                pol_type = pol_type+\" \"+polarity.find('type').text\n",
    "            #record.append(pol_ent)\n",
    "            record.append(pol_val)\n",
    "            record.append(pol_type)\n",
    "                \n",
    "        all_records.append(record)\n",
    "    return pd.DataFrame(all_records, columns=headers)\n",
    "\n",
    "tweets_df = xml2df(xml_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tweetid</th>\n",
       "      <th>user</th>\n",
       "      <th>content</th>\n",
       "      <th>date</th>\n",
       "      <th>lang</th>\n",
       "      <th>topic</th>\n",
       "      <th>polarity_value</th>\n",
       "      <th>polarity_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>142389495503925248</td>\n",
       "      <td>ccifuentes</td>\n",
       "      <td>\\n Salgo de #VeoTV , que día más largoooooo... \\n</td>\n",
       "      <td>2011-12-02T00:47:55</td>\n",
       "      <td>es</td>\n",
       "      <td>otros</td>\n",
       "      <td>NONE</td>\n",
       "      <td>AGREEMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>142389933619945473</td>\n",
       "      <td>CarmendelRiego</td>\n",
       "      <td>\\n\\n@PauladeLasHeras No te libraras de ayudar ...</td>\n",
       "      <td>2011-12-02T00:49:40</td>\n",
       "      <td>es</td>\n",
       "      <td>otros</td>\n",
       "      <td>NEU NEU</td>\n",
       "      <td>DISAGREEMENT DISAGREEMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>142391947707940864</td>\n",
       "      <td>CarmendelRiego</td>\n",
       "      <td>\\n @marodriguezb Gracias MAR \\n</td>\n",
       "      <td>2011-12-02T00:57:40</td>\n",
       "      <td>es</td>\n",
       "      <td>otros</td>\n",
       "      <td>P P</td>\n",
       "      <td>AGREEMENT AGREEMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>142416095012339712</td>\n",
       "      <td>mgilguerrero</td>\n",
       "      <td>\\n\\nOff pensando en el regalito Sinde, la que ...</td>\n",
       "      <td>2011-12-02T02:33:37</td>\n",
       "      <td>es</td>\n",
       "      <td>política economía</td>\n",
       "      <td>N+ N+ N+</td>\n",
       "      <td>AGREEMENT AGREEMENT AGREEMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>142422495721562112</td>\n",
       "      <td>paurubio</td>\n",
       "      <td>\\n\\nConozco a alguien q es adicto al drama! Ja...</td>\n",
       "      <td>2011-12-02T02:59:03</td>\n",
       "      <td>es</td>\n",
       "      <td>otros</td>\n",
       "      <td>P+</td>\n",
       "      <td>AGREEMENT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              tweetid            user  \\\n",
       "0  142389495503925248      ccifuentes   \n",
       "1  142389933619945473  CarmendelRiego   \n",
       "2  142391947707940864  CarmendelRiego   \n",
       "3  142416095012339712    mgilguerrero   \n",
       "4  142422495721562112        paurubio   \n",
       "\n",
       "                                             content                 date  \\\n",
       "0  \\n Salgo de #VeoTV , que día más largoooooo... \\n  2011-12-02T00:47:55   \n",
       "1  \\n\\n@PauladeLasHeras No te libraras de ayudar ...  2011-12-02T00:49:40   \n",
       "2                    \\n @marodriguezb Gracias MAR \\n  2011-12-02T00:57:40   \n",
       "3  \\n\\nOff pensando en el regalito Sinde, la que ...  2011-12-02T02:33:37   \n",
       "4  \\n\\nConozco a alguien q es adicto al drama! Ja...  2011-12-02T02:59:03   \n",
       "\n",
       "  lang               topic polarity_value                   polarity_type  \n",
       "0   es               otros           NONE                       AGREEMENT  \n",
       "1   es               otros        NEU NEU       DISAGREEMENT DISAGREEMENT  \n",
       "2   es               otros            P P             AGREEMENT AGREEMENT  \n",
       "3   es   política economía       N+ N+ N+   AGREEMENT AGREEMENT AGREEMENT  \n",
       "4   es               otros             P+                       AGREEMENT  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7219, 8)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se observa que hay 7219 tweets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tweetid           object\n",
       "user              object\n",
       "content           object\n",
       "date              object\n",
       "lang              object\n",
       "topic             object\n",
       "polarity_value    object\n",
       "polarity_type     object\n",
       "dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets_df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* ### Procesado del dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Nos quedamos solo con las columnas que nos interesan para el análisis de sentimiento\n",
    "tweets_df = tweets_df[['content','polarity_value','polarity_type']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Los campos polarity_value y polarity_type pueden tener más de un valor (ya que el primero es el general, \n",
    "# y el resto están referidos a entidades que aparecen en el texto). Por eso, nos quedaremos solo con el \n",
    "# primer valor que es el general\n",
    "\n",
    "for i in range(0,tweets_df.shape[0]):\n",
    "    tweets_df.loc[i,'polarity_value'] = tweets_df.loc[i,'polarity_value'].split()[0] \n",
    "    tweets_df.loc[i,'polarity_type'] = tweets_df.loc[i,'polarity_type'].split()[0] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Se eliminan el salto de línea (caracter '\\n'), los espacios en los extremos del tweet, las URLs y los emojis\n",
    "car=['\\n']\n",
    "\n",
    "def borrar_caracteres(texto):\n",
    "    for caracter in car:\n",
    "        texto=texto.replace(caracter,\"\")\n",
    "    return texto\n",
    "\n",
    "emoji_pattern = re.compile(u'['\n",
    "     u'\\U0001F300-\\U0001F64F'\n",
    "     u'\\U0001F680-\\U0001F6FF'\n",
    "     u'\\u2600-\\u26FF\\u2700-\\u27BF]+', \n",
    "     re.UNICODE)\n",
    "\n",
    "for i in range(0,tweets_df.shape[0]):\n",
    "    tweets_df.content[i]=borrar_caracteres(tweets_df.content[i])\n",
    "    tweets_df.content[i]=tweets_df.content[i].strip()\n",
    "    tweets_df.content[i]=re.sub(r'\\w+:\\/{2}[\\d\\w-]+(\\.[\\d\\w-]+)*(?:(?:\\/[^\\s/]*))*', '', tweets_df.content[i])\n",
    "    tweets_df.content[i]=emoji_pattern.sub(r'', tweets_df.content[i])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Nos quedamos con aquellos tweets que tienen polaridad asignada y aquellos en los que hay acuerdo \n",
    "# (agreement) en el sentimiento presente en el tweet\n",
    "tweets_df = tweets_df.query('polarity_type != \"DISAGREEMENT\" and polarity_value != \"NONE\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    5012\n",
       "True        1\n",
       "Name: content, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se comprueba que se han eliminado correctamente los links (reseteamos los índices para usar los correctos)\n",
    "tweets_df = tweets_df.reset_index(drop=True)\n",
    "tw_with_links = tweets_df.content.str.contains('http.*$')\n",
    "tw_with_links.value_counts(normalize=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(array([1359]),)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se consultará qué tweet es el que tiene enlace, supuestamente.\n",
    "np.where(tw_with_links == True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['RT @elviswarrior: Con el carro de Manolo Escobar? “@mariviromero: ¿Dónde fue a parar el dinero de los ERE?  - http:/ ...']"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets_df.loc[[1359]].content.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['RT @elviswarrior: Con el carro de Manolo Escobar? “@mariviromero: ¿Dónde fue a parar el dinero de los ERE?']"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Este tweet se modificará manualmente\n",
    "tweets_df.set_value(1359,'content',\"RT @elviswarrior: Con el carro de Manolo Escobar? “@mariviromero: ¿Dónde fue a parar el dinero de los ERE?\")\n",
    "tweets_df.loc[[1359]].content.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    5013\n",
       "Name: content, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Ahora, ya no hay tweets con enlaces\n",
    "tw_with_links = tweets_df.content.str.contains('http.*$')\n",
    "tw_with_links.value_counts(normalize=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>content</th>\n",
       "      <th>polarity_value</th>\n",
       "      <th>polarity_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>@marodriguezb Gracias MAR</td>\n",
       "      <td>P</td>\n",
       "      <td>AGREEMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Off pensando en el regalito Sinde, la que se v...</td>\n",
       "      <td>N+</td>\n",
       "      <td>AGREEMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Conozco a alguien q es adicto al drama! Ja ja ...</td>\n",
       "      <td>P+</td>\n",
       "      <td>AGREEMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Toca @crackoviadeTV3 . Grabación dl especial N...</td>\n",
       "      <td>P+</td>\n",
       "      <td>AGREEMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Buen día todos! Lo primero mandar un abrazo gr...</td>\n",
       "      <td>P+</td>\n",
       "      <td>AGREEMENT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             content polarity_value  \\\n",
       "0                          @marodriguezb Gracias MAR              P   \n",
       "1  Off pensando en el regalito Sinde, la que se v...             N+   \n",
       "2  Conozco a alguien q es adicto al drama! Ja ja ...             P+   \n",
       "3  Toca @crackoviadeTV3 . Grabación dl especial N...             P+   \n",
       "4  Buen día todos! Lo primero mandar un abrazo gr...             P+   \n",
       "\n",
       "  polarity_type  \n",
       "0     AGREEMENT  \n",
       "1     AGREEMENT  \n",
       "2     AGREEMENT  \n",
       "3     AGREEMENT  \n",
       "4     AGREEMENT  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5013, 3)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* ### Binarización del sentimiento"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/monphdez/anaconda/lib/python3.6/site-packages/ipykernel/__main__.py:6: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "1    0.577447\n",
       "0    0.422553\n",
       "Name: polarity_bin, dtype: float64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se eliminan los tweets con polaridad neutra\n",
    "tweets_df = tweets_df[tweets_df.polarity_value != 'NEU']\n",
    "\n",
    "# Se añade una nueva columna con los valores de polaridad 1 si ésta es positiva ó 0 si ésta es negativa\n",
    "tweets_df['polarity_bin'] = 0\n",
    "tweets_df.polarity_bin[tweets_df.polarity_value.isin(['P', 'P+'])] = 1\n",
    "tweets_df.polarity_bin.value_counts(normalize=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4700, 4)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>content</th>\n",
       "      <th>polarity_value</th>\n",
       "      <th>polarity_type</th>\n",
       "      <th>polarity_bin</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>@marodriguezb Gracias MAR</td>\n",
       "      <td>P</td>\n",
       "      <td>AGREEMENT</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Off pensando en el regalito Sinde, la que se v...</td>\n",
       "      <td>N+</td>\n",
       "      <td>AGREEMENT</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Conozco a alguien q es adicto al drama! Ja ja ...</td>\n",
       "      <td>P+</td>\n",
       "      <td>AGREEMENT</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Toca @crackoviadeTV3 . Grabación dl especial N...</td>\n",
       "      <td>P+</td>\n",
       "      <td>AGREEMENT</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Buen día todos! Lo primero mandar un abrazo gr...</td>\n",
       "      <td>P+</td>\n",
       "      <td>AGREEMENT</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             content polarity_value  \\\n",
       "0                          @marodriguezb Gracias MAR              P   \n",
       "1  Off pensando en el regalito Sinde, la que se v...             N+   \n",
       "2  Conozco a alguien q es adicto al drama! Ja ja ...             P+   \n",
       "3  Toca @crackoviadeTV3 . Grabación dl especial N...             P+   \n",
       "4  Buen día todos! Lo primero mandar un abrazo gr...             P+   \n",
       "\n",
       "  polarity_type  polarity_bin  \n",
       "0     AGREEMENT             1  \n",
       "1     AGREEMENT             0  \n",
       "2     AGREEMENT             1  \n",
       "3     AGREEMENT             1  \n",
       "4     AGREEMENT             1  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Se exportan los tweets preprocesados a un fichero Excel\n",
    "tweets_df[['content','polarity_bin']].to_excel('ficheros/Preprocesados/tweets_corpus_header.xlsx', header=True, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Procesado de los tweets con geolocalización"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(205789, 19)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Lectura del fichero de tweets\n",
    "tweets = pd.read_excel('ficheros/Raw/spain_geocode.xlsx', header=0, encoding='iso8859_15')\n",
    "tweets.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Tweet Id</th>\n",
       "      <th>Date</th>\n",
       "      <th>Hour</th>\n",
       "      <th>User Name</th>\n",
       "      <th>Nickname</th>\n",
       "      <th>Bio</th>\n",
       "      <th>Tweet content</th>\n",
       "      <th>Favs</th>\n",
       "      <th>RTs</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Country</th>\n",
       "      <th>Place (as appears on Bio)</th>\n",
       "      <th>Profile picture</th>\n",
       "      <th>Followers</th>\n",
       "      <th>Following</th>\n",
       "      <th>Listed</th>\n",
       "      <th>Tweet language (ISO 639-1)</th>\n",
       "      <th>Tweet Url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>723496204018040836</td>\n",
       "      <td>2016-04-22</td>\n",
       "      <td>12:58</td>\n",
       "      <td>Andoni Merino</td>\n",
       "      <td>AndoniMerino</td>\n",
       "      <td>NaN</td>\n",
       "      <td>San Francisco será la primera gran ciudad en e...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43.26271</td>\n",
       "      <td>-2.92528</td>\n",
       "      <td>ES</td>\n",
       "      <td>Bilbao</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/1669281616...</td>\n",
       "      <td>60.0</td>\n",
       "      <td>283.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>es</td>\n",
       "      <td>http://www.twitter.com/AndoniMerino/status/723...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>723496203875430400</td>\n",
       "      <td>2016-04-22</td>\n",
       "      <td>12:58</td>\n",
       "      <td>antonio silva</td>\n",
       "      <td>antoniosilvatt</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://t.co/LDpaxOi2C2</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>http://pbs.twimg.com/profile_images/6321183806...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>es</td>\n",
       "      <td>http://www.twitter.com/antoniosilvatt/status/7...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>723496203518881792</td>\n",
       "      <td>2016-04-22</td>\n",
       "      <td>12:58</td>\n",
       "      <td>Ayuntamiento de Rota</td>\n",
       "      <td>RotaAyto</td>\n",
       "      <td>Twitter oficial del Ayuntamiento de Rota. Sígu...</td>\n",
       "      <td>@jjaviruiz preside el acto de homenaje a los f...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36.62545</td>\n",
       "      <td>-6.36220</td>\n",
       "      <td>ES</td>\n",
       "      <td>Rota, Cádiz</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/6901508342...</td>\n",
       "      <td>2169.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>es</td>\n",
       "      <td>http://www.twitter.com/RotaAyto/status/7234962...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>723496203493756928</td>\n",
       "      <td>2016-04-22</td>\n",
       "      <td>12:58</td>\n",
       "      <td>Carlos F. Valledor</td>\n",
       "      <td>cfv1961</td>\n",
       "      <td>Todos somos muy ignorantes. Lo que ocurre es q...</td>\n",
       "      <td>Regalo para socios: 'Talento a la fuga', el li...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.41600</td>\n",
       "      <td>-3.70300</td>\n",
       "      <td>ES</td>\n",
       "      <td>Madrid, España</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/7145772157...</td>\n",
       "      <td>375.0</td>\n",
       "      <td>319.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>es</td>\n",
       "      <td>http://www.twitter.com/cfv1961/status/72349620...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>723496203464413184</td>\n",
       "      <td>2016-04-22</td>\n",
       "      <td>12:58</td>\n",
       "      <td>Showman</td>\n",
       "      <td>TheShowmanEU</td>\n",
       "      <td>Estoy en YouTube por dinero•Twitch:https://t.c...</td>\n",
       "      <td>@muzikplumz 😘</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.41600</td>\n",
       "      <td>-3.70300</td>\n",
       "      <td>ES</td>\n",
       "      <td>Madrid, Comunidad de Madrid</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/6783568295...</td>\n",
       "      <td>262.0</td>\n",
       "      <td>241.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>es</td>\n",
       "      <td>http://www.twitter.com/TheShowmanEU/status/723...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Tweet Id        Date   Hour             User Name  \\\n",
       "0  723496204018040836  2016-04-22  12:58         Andoni Merino   \n",
       "1  723496203875430400  2016-04-22  12:58         antonio silva   \n",
       "2  723496203518881792  2016-04-22  12:58  Ayuntamiento de Rota   \n",
       "3  723496203493756928  2016-04-22  12:58    Carlos F. Valledor   \n",
       "4  723496203464413184  2016-04-22  12:58               Showman   \n",
       "\n",
       "         Nickname                                                Bio  \\\n",
       "0    AndoniMerino                                                NaN   \n",
       "1  antoniosilvatt                                                NaN   \n",
       "2        RotaAyto  Twitter oficial del Ayuntamiento de Rota. Sígu...   \n",
       "3         cfv1961  Todos somos muy ignorantes. Lo que ocurre es q...   \n",
       "4    TheShowmanEU  Estoy en YouTube por dinero•Twitch:https://t.c...   \n",
       "\n",
       "                                       Tweet content  Favs  RTs  Latitude  \\\n",
       "0  San Francisco será la primera gran ciudad en e...   NaN  NaN  43.26271   \n",
       "1                            https://t.co/LDpaxOi2C2   NaN  NaN       NaN   \n",
       "2  @jjaviruiz preside el acto de homenaje a los f...   NaN  NaN  36.62545   \n",
       "3  Regalo para socios: 'Talento a la fuga', el li...   NaN  NaN  40.41600   \n",
       "4                                      @muzikplumz 😘   NaN  NaN  40.41600   \n",
       "\n",
       "   Longitude Country    Place (as appears on Bio)  \\\n",
       "0   -2.92528      ES                       Bilbao   \n",
       "1        NaN     NaN                          NaN   \n",
       "2   -6.36220      ES                  Rota, Cádiz   \n",
       "3   -3.70300      ES               Madrid, España   \n",
       "4   -3.70300      ES  Madrid, Comunidad de Madrid   \n",
       "\n",
       "                                     Profile picture  Followers  Following  \\\n",
       "0  http://pbs.twimg.com/profile_images/1669281616...       60.0      283.0   \n",
       "1  http://pbs.twimg.com/profile_images/6321183806...        3.0       16.0   \n",
       "2  http://pbs.twimg.com/profile_images/6901508342...     2169.0       52.0   \n",
       "3  http://pbs.twimg.com/profile_images/7145772157...      375.0      319.0   \n",
       "4  http://pbs.twimg.com/profile_images/6783568295...      262.0      241.0   \n",
       "\n",
       "   Listed Tweet language (ISO 639-1)  \\\n",
       "0     5.0                         es   \n",
       "1     NaN                         es   \n",
       "2    34.0                         es   \n",
       "3    94.0                         es   \n",
       "4     3.0                         es   \n",
       "\n",
       "                                           Tweet Url  \n",
       "0  http://www.twitter.com/AndoniMerino/status/723...  \n",
       "1  http://www.twitter.com/antoniosilvatt/status/7...  \n",
       "2  http://www.twitter.com/RotaAyto/status/7234962...  \n",
       "3  http://www.twitter.com/cfv1961/status/72349620...  \n",
       "4  http://www.twitter.com/TheShowmanEU/status/723...  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Nos quedamos solo con las columnas que nos interesan\n",
    "tweets = tweets[['User Name','Tweet content','Latitude','Longitude','Tweet language (ISO 639-1)']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Cambiamos el nombre de algunas columnas\n",
    "tweets.rename(columns={'User Name':'user', 'Tweet content':'content','Tweet language (ISO 639-1)':'lan_excel'}, inplace = True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(40516, 5)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Filtramos los tweets y solo nos quedamos solo con los de la Comunidad de Madrid\n",
    "min_lon = -4.585707\n",
    "max_lon = -3.036635\n",
    "min_lat = 39.865278\n",
    "max_lat = 41.192925\n",
    "\n",
    "tweets = tweets[(tweets.Latitude.notnull()) & (tweets.Longitude.notnull())]\n",
    "\n",
    "tweets = tweets[(tweets.Longitude > min_lon) & (tweets.Longitude < max_lon) & (tweets.Latitude > min_lat) & (tweets.Latitude < max_lat)]\n",
    "tweets.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(33204, 5)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Filtramos los tweets y nos quedamos solo con aquellos que estén en español\n",
    "tweets = tweets[(tweets.lan_excel == 'es')]\n",
    "tweets.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Funciones para detectar el lenguaje y comprobar que, efectivamente, todos están en español\n",
    "def langid_safe(tweet):\n",
    "    try:\n",
    "        return langid.classify(tweet)[0]\n",
    "    except Exception as e:\n",
    "        pass\n",
    "        \n",
    "def langdetect_safe(tweet):\n",
    "    try:\n",
    "        return detect(tweet)\n",
    "    except Exception as e:\n",
    "        pass\n",
    "\n",
    "def textblob_safe(tweet):\n",
    "    try:\n",
    "        return textblob.TextBlob(tweet).detect_language()\n",
    "    except Exception as e:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Detectamos los tweets en español (sólo se usará una función para reducir el tiempo de ejecución)\n",
    "\n",
    "tweets['lang_langid'] = tweets.content.apply(langid_safe)\n",
    "#tweets['lang_langdetect'] = tweets.content.apply(langdetect_safe)\n",
    "#tweets['lang_textblob'] = tweets.content.apply(textblob_safe)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(26363, 6)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Filtramos los tweets y nos quedamos solo con aquellos que estén en español\n",
    "#tweets = tweets.query(''' lang_langdetect == 'es' or lang_langid == 'es' or lang_textblob == 'es'  ''')\n",
    "tweets = tweets[(tweets.lang_langid == 'es')]\n",
    "tweets.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>content</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>lan_excel</th>\n",
       "      <th>lang_langid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Carlos F. Valledor</td>\n",
       "      <td>Regalo para socios: 'Talento a la fuga', el li...</td>\n",
       "      <td>40.416</td>\n",
       "      <td>-3.703</td>\n",
       "      <td>es</td>\n",
       "      <td>es</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Miguel Ángel Valero</td>\n",
       "      <td>La recuperación del ‘ladrillo’ dispara el 144,...</td>\n",
       "      <td>40.416</td>\n",
       "      <td>-3.703</td>\n",
       "      <td>es</td>\n",
       "      <td>es</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Carlos F. Valledor</td>\n",
       "      <td>TELEVISIÓN SERIES - Cuenta atrás para conocer ...</td>\n",
       "      <td>40.416</td>\n",
       "      <td>-3.703</td>\n",
       "      <td>es</td>\n",
       "      <td>es</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Centro Tangram</td>\n",
       "      <td>Nuestra taza de hoy es un homenaje a uno de lo...</td>\n",
       "      <td>40.416</td>\n",
       "      <td>-3.703</td>\n",
       "      <td>es</td>\n",
       "      <td>es</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>↯  ↯  ↯</td>\n",
       "      <td>@MCadepe nos vemos el 19 de mayooo!!!😍😍😍😘😘😘</td>\n",
       "      <td>40.416</td>\n",
       "      <td>-3.703</td>\n",
       "      <td>es</td>\n",
       "      <td>es</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   user                                            content  \\\n",
       "3    Carlos F. Valledor  Regalo para socios: 'Talento a la fuga', el li...   \n",
       "5   Miguel Ángel Valero  La recuperación del ‘ladrillo’ dispara el 144,...   \n",
       "6    Carlos F. Valledor  TELEVISIÓN SERIES - Cuenta atrás para conocer ...   \n",
       "22       Centro Tangram  Nuestra taza de hoy es un homenaje a uno de lo...   \n",
       "25              ↯  ↯  ↯        @MCadepe nos vemos el 19 de mayooo!!!😍😍😍😘😘😘   \n",
       "\n",
       "    Latitude  Longitude lan_excel lang_langid  \n",
       "3     40.416     -3.703        es          es  \n",
       "5     40.416     -3.703        es          es  \n",
       "6     40.416     -3.703        es          es  \n",
       "22    40.416     -3.703        es          es  \n",
       "25    40.416     -3.703        es          es  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Descartamos las columnas del idioma\n",
    "tweets = tweets[['user','content','Latitude','Longitude']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/monphdez/anaconda/lib/python3.6/site-packages/ipykernel/__main__.py:6: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/Users/monphdez/anaconda/lib/python3.6/site-packages/ipykernel/__main__.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(26363, 4)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se eliminan las URLs y los emoji de los tweets\n",
    "for i in range(0,tweets.shape[0]):\n",
    "    x = tweets.index[i]\n",
    "    tweets.content[x]=re.sub(r'\\w+:\\/{2}[\\d\\w-]+(\\.[\\d\\w-]+)*(?:(?:\\/[^\\s/]*))*', '', tweets.content[x])\n",
    "    # El patrón de emojis se usó en el Corpus\n",
    "    tweets.content[x]=emoji_pattern.sub(r'', tweets.content[x])\n",
    "        \n",
    "tweets.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>content</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Carlos F. Valledor</td>\n",
       "      <td>Regalo para socios: 'Talento a la fuga', el li...</td>\n",
       "      <td>40.416</td>\n",
       "      <td>-3.703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Miguel Ángel Valero</td>\n",
       "      <td>La recuperación del ‘ladrillo’ dispara el 144,...</td>\n",
       "      <td>40.416</td>\n",
       "      <td>-3.703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Carlos F. Valledor</td>\n",
       "      <td>TELEVISIÓN SERIES - Cuenta atrás para conocer ...</td>\n",
       "      <td>40.416</td>\n",
       "      <td>-3.703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Centro Tangram</td>\n",
       "      <td>Nuestra taza de hoy es un homenaje a uno de lo...</td>\n",
       "      <td>40.416</td>\n",
       "      <td>-3.703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>↯  ↯  ↯</td>\n",
       "      <td>@MCadepe nos vemos el 19 de mayooo!!!</td>\n",
       "      <td>40.416</td>\n",
       "      <td>-3.703</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   user                                            content  \\\n",
       "3    Carlos F. Valledor  Regalo para socios: 'Talento a la fuga', el li...   \n",
       "5   Miguel Ángel Valero  La recuperación del ‘ladrillo’ dispara el 144,...   \n",
       "6    Carlos F. Valledor  TELEVISIÓN SERIES - Cuenta atrás para conocer ...   \n",
       "22       Centro Tangram  Nuestra taza de hoy es un homenaje a uno de lo...   \n",
       "25              ↯  ↯  ↯              @MCadepe nos vemos el 19 de mayooo!!!   \n",
       "\n",
       "    Latitude  Longitude  \n",
       "3     40.416     -3.703  \n",
       "5     40.416     -3.703  \n",
       "6     40.416     -3.703  \n",
       "22    40.416     -3.703  \n",
       "25    40.416     -3.703  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    26360\n",
       "True         3\n",
       "Name: content, dtype: int64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se comprueba que se han eliminado correctamente los links (reseteamos los índices para \n",
    "# usar los correctos)\n",
    "tweets = tweets.reset_index(drop=True)\n",
    "tw_with_links = tweets.content.str.contains('http.*$')\n",
    "tw_with_links.value_counts(normalize=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(array([ 4143, 11710, 18874]),)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se consultará qué tweets son los que tienen enlace, supuestamente.\n",
    "np.where(tw_with_links == True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Si tienes una boda este fin de semana...No te lo pienses más. \\nhttp: //www.asos.com/es/Top-de-tirante ... ']\n",
      "['#elpríncipefinalhttp es ahora una tendencia en Spain ']\n",
      "['Una bonita mañana celebrando el día mundial del circo. MADPAC.https//vimeo.com/163159793']\n"
     ]
    }
   ],
   "source": [
    "# El primero y el tercero tienen enlace (el segundo es de un hashtag)\n",
    "print(tweets.loc[[4143]].content.tolist())\n",
    "print(tweets.loc[[11710]].content.tolist())\n",
    "print(tweets.loc[[18874]].content.tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Si tienes una boda este fin de semana...No te lo pienses más.']\n",
      "['Una bonita mañana celebrando el día mundial del circo. MADPAC.']\n"
     ]
    }
   ],
   "source": [
    "# Estos tweets se modificarán manualmente\n",
    "tweets.set_value(4143,'content',\"Si tienes una boda este fin de semana...No te lo pienses más.\")\n",
    "tweets.set_value(18874,'content',\"Una bonita mañana celebrando el día mundial del circo. MADPAC.\")\n",
    "print(tweets.loc[[4143]].content.tolist())\n",
    "print(tweets.loc[[18874]].content.tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    26362\n",
       "True         1\n",
       "Name: content, dtype: int64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Ahora, sólo queda 1 tweet que cumple la condición, pero se vio que no tenía enlace, así que terminado\n",
    "tw_with_links = tweets.content.str.contains('http.*$')\n",
    "tw_with_links.value_counts(normalize=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Se exportan los tweets preprocesados a un fichero Excel\n",
    "tweets[['content','Latitude','Longitude']].to_excel('ficheros/Preprocesados/tweets_spainGeo.xlsx', header=True, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Licencia"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "El notebook está licenciado libremente bajo la licencia [Creative Commons Attribution Share-Alike](https://creativecommons.org/licenses/by/2.0/).\n",
    "\n",
    "La base del código empleado procede del trabajo de Manuel Garrido llamado [Cómo hacer Análisis de Sentimiento en español](http://pybonacci.org/2015/11/24/como-hacer-analisis-de-sentimiento-en-espanol-2/).\n",
    "\n",
    "© 2017 - Juan Bermudo Mera, Margarita Bolívar Jiménez, Lourdes Fernández Nieto, Ramón Pérez Hernández.\n",
    "\n",
    "Universidad Politécnica de Madrid."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}