0.1-crear-dataframes.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "collection = \"tweets_fernandezfernandez\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "xILzsBEsKl-R"
   },
   "outputs": [],
   "source": [
    "import json\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_colwidth', -1)\n",
    "pd.set_option('display.max_rows', 999)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pymongo import MongoClient\n",
    "\n",
    "MONGO_HOST = 'mongodb://localhost/twitterdata'\n",
    "client = MongoClient(MONGO_HOST)\n",
    "db = client.twitterdata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "coll = db[collection]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "115690"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "coll.estimated_document_count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %%time\n",
    "tweets = []\n",
    "users = {}\n",
    "cols_tweet = [\n",
    "    'id',\n",
    "    'created_at',\n",
    "    'text',\n",
    "    'favorited',\n",
    "    'retweeted',\n",
    "]\n",
    "\n",
    "cols_user = [\n",
    "    'screen_name',\n",
    "    'location',\n",
    "    'followers_count',\n",
    "    'statuses_count',\n",
    "    'description',\n",
    "    'friends_count',\n",
    "    'favourites_count',\n",
    "    'created_at',\n",
    "    'id',\n",
    "    'name',             \n",
    "    'verified'\n",
    "]\n",
    "\n",
    "# for t in coll.find().limit(10):\n",
    "for t in coll.find():\n",
    "    uid = t['user']['id'] \n",
    "    if uid not in users:\n",
    "        users[uid] = [t['user'][c] for c in cols_user]\n",
    "        \n",
    "    tr = [t[c] for c in cols_tweet] + [t['user']['name'], t['user']['id']]\n",
    "\n",
    "    mentions = \" \".join([h['screen_name'] for h in t['entities']['user_mentions']])\n",
    "    tr.append(mentions)\n",
    "    \n",
    "    hts = \" \".join([h['text'] for h in t['entities']['hashtags']])\n",
    "    tr.append(hts)\n",
    "\n",
    "    if 'retweeted_status' in t:\n",
    "        t = t['retweeted_status']\n",
    "        \n",
    "    tr.append(t['id'])\n",
    "    if 'extended_tweet' in t:\n",
    "        tr.append(t['extended_tweet']['full_text'])   \n",
    "    else:\n",
    "        tr.append(t['text'])\n",
    "\n",
    "    tr += [t['favorite_count'], t['retweet_count']]\n",
    "\n",
    "        \n",
    "    tweets.append(tr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "tweets_df = pd.DataFrame(tweets, columns=cols_tweet + ['user_name', 'user_id', 'mentions', 'hts', 'original_id', 'full_text', 'favorite_count', 'retweet_count'\n",
    "])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "users_df = pd.DataFrame(users.values(), columns=cols_user)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Guardar datos en formato accesible"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_text = lambda s: s.replace('\\n', ' ').replace('\\r', ' ')\n",
    "\n",
    "tweets_df.full_text = tweets_df.full_text.apply(clean_text)\n",
    "\n",
    "tweets_df.text = tweets_df.text.apply(clean_text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(115690, 13)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>id</th>\n",
       "      <th>created_at</th>\n",
       "      <th>text</th>\n",
       "      <th>favorited</th>\n",
       "      <th>retweeted</th>\n",
       "      <th>user_name</th>\n",
       "      <th>user_id</th>\n",
       "      <th>mentions</th>\n",
       "      <th>hts</th>\n",
       "      <th>original_id</th>\n",
       "      <th>full_text</th>\n",
       "      <th>favorite_count</th>\n",
       "      <th>retweet_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1129796443533905921</td>\n",
       "      <td>Sat May 18 17:10:37 +0000 2019</td>\n",
       "      <td>RT @NuncaMasK2019: ALBERTO FERNÁNDEZ sobre CRISTINA KIRCHNER  Una persona sin valores actúa siguiendo su ambición de poder y dinero.  ¡Dale…</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>silvina</td>\n",
       "      <td>218159646</td>\n",
       "      <td>NuncaMasK2019</td>\n",
       "      <td></td>\n",
       "      <td>1129754910030016515</td>\n",
       "      <td>ALBERTO FERNÁNDEZ sobre CRISTINA KIRCHNER  Una persona sin valores actúa siguiendo su ambición de poder y dinero.  ¡Dale RT para DIFUNDIR!   #ArrugoCristina https://t.co/jeZO9zvSNF</td>\n",
       "      <td>836</td>\n",
       "      <td>951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1129796444729368576</td>\n",
       "      <td>Sat May 18 17:10:37 +0000 2019</td>\n",
       "      <td>RT @felipe_sola: Mi candidatura tenía una única misión: construir una unidad amplia para defender al trabajo, a los humildes y a la clase m…</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Tonga</td>\n",
       "      <td>144904500</td>\n",
       "      <td>felipe_sola</td>\n",
       "      <td></td>\n",
       "      <td>1129761247359057922</td>\n",
       "      <td>Mi candidatura tenía una única misión: construir una unidad amplia para defender al trabajo, a los humildes y a la clase media. Ese objetivo está cumplido.   Celebro la decisión de Cristina. Felicito a Alberto.  #EsConTodos</td>\n",
       "      <td>14194</td>\n",
       "      <td>3065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1129796445991837696</td>\n",
       "      <td>Sat May 18 17:10:37 +0000 2019</td>\n",
       "      <td>180 mil millones de dólares de deuda en cimientos, ahora hay que garparlos Pinedo, contate otro.… https://t.co/3tHk0FIwCf</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Guillermo Sordo</td>\n",
       "      <td>319594285</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1129796445991837696</td>\n",
       "      <td>180 mil millones de dólares de deuda en cimientos, ahora hay que garparlos Pinedo, contate otro. #FernandezFernandez #CristinaSomosTodos</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1129796448718065666</td>\n",
       "      <td>Sat May 18 17:10:38 +0000 2019</td>\n",
       "      <td>RT @peppa1963: @rag_HLVS Se lo van a cargar al pobre títere de Alberto Fernández.... \"Que parezca un accidente......\" #ArrugoCristina #Buen…</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Silvia Berth</td>\n",
       "      <td>837648053968457728</td>\n",
       "      <td>peppa1963 rag_HLVS</td>\n",
       "      <td>ArrugoCristina</td>\n",
       "      <td>1129789421904158721</td>\n",
       "      <td>@rag_HLVS Se lo van a cargar al pobre títere de Alberto Fernández.... \"Que parezca un accidente......\" #ArrugoCristina #BuenSabado</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1129796456632729600</td>\n",
       "      <td>Sat May 18 17:10:40 +0000 2019</td>\n",
       "      <td>RT @ProfetaArgen: Macrista: Cristina tiene que bajarse, que no sea soberbia  Also Macrista: #ArrugoCristina</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Fede Schoeller</td>\n",
       "      <td>302339856</td>\n",
       "      <td>ProfetaArgen</td>\n",
       "      <td>ArrugoCristina</td>\n",
       "      <td>1129764924924878850</td>\n",
       "      <td>Macrista: Cristina tiene que bajarse, que no sea soberbia  Also Macrista: #ArrugoCristina</td>\n",
       "      <td>294</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    id                      created_at  \\\n",
       "0  1129796443533905921  Sat May 18 17:10:37 +0000 2019   \n",
       "1  1129796444729368576  Sat May 18 17:10:37 +0000 2019   \n",
       "2  1129796445991837696  Sat May 18 17:10:37 +0000 2019   \n",
       "3  1129796448718065666  Sat May 18 17:10:38 +0000 2019   \n",
       "4  1129796456632729600  Sat May 18 17:10:40 +0000 2019   \n",
       "\n",
       "                                                                                                                                           text  \\\n",
       "0  RT @NuncaMasK2019: ALBERTO FERNÁNDEZ sobre CRISTINA KIRCHNER  Una persona sin valores actúa siguiendo su ambición de poder y dinero.  ¡Dale…   \n",
       "1  RT @felipe_sola: Mi candidatura tenía una única misión: construir una unidad amplia para defender al trabajo, a los humildes y a la clase m…   \n",
       "2  180 mil millones de dólares de deuda en cimientos, ahora hay que garparlos Pinedo, contate otro.… https://t.co/3tHk0FIwCf                      \n",
       "3  RT @peppa1963: @rag_HLVS Se lo van a cargar al pobre títere de Alberto Fernández.... \"Que parezca un accidente......\" #ArrugoCristina #Buen…   \n",
       "4  RT @ProfetaArgen: Macrista: Cristina tiene que bajarse, que no sea soberbia  Also Macrista: #ArrugoCristina                                    \n",
       "\n",
       "   favorited  retweeted        user_name             user_id  \\\n",
       "0  False      False      silvina          218159646            \n",
       "1  False      False      Tonga            144904500            \n",
       "2  False      False      Guillermo Sordo  319594285            \n",
       "3  False      False      Silvia Berth     837648053968457728   \n",
       "4  False      False      Fede Schoeller   302339856            \n",
       "\n",
       "             mentions             hts          original_id  \\\n",
       "0  NuncaMasK2019                       1129754910030016515   \n",
       "1  felipe_sola                         1129761247359057922   \n",
       "2                                      1129796445991837696   \n",
       "3  peppa1963 rag_HLVS  ArrugoCristina  1129789421904158721   \n",
       "4  ProfetaArgen        ArrugoCristina  1129764924924878850   \n",
       "\n",
       "                                                                                                                                                                                                                         full_text  \\\n",
       "0  ALBERTO FERNÁNDEZ sobre CRISTINA KIRCHNER  Una persona sin valores actúa siguiendo su ambición de poder y dinero.  ¡Dale RT para DIFUNDIR!   #ArrugoCristina https://t.co/jeZO9zvSNF                                              \n",
       "1  Mi candidatura tenía una única misión: construir una unidad amplia para defender al trabajo, a los humildes y a la clase media. Ese objetivo está cumplido.   Celebro la decisión de Cristina. Felicito a Alberto.  #EsConTodos   \n",
       "2  180 mil millones de dólares de deuda en cimientos, ahora hay que garparlos Pinedo, contate otro. #FernandezFernandez #CristinaSomosTodos                                                                                          \n",
       "3  @rag_HLVS Se lo van a cargar al pobre títere de Alberto Fernández.... \"Que parezca un accidente......\" #ArrugoCristina #BuenSabado                                                                                                \n",
       "4  Macrista: Cristina tiene que bajarse, que no sea soberbia  Also Macrista: #ArrugoCristina                                                                                                                                         \n",
       "\n",
       "   favorite_count  retweet_count  \n",
       "0  836             951            \n",
       "1  14194           3065           \n",
       "2  0               0              \n",
       "3  1               1              \n",
       "4  294             74             "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "tweets_df.to_csv('./csvs/%s_tweets.csv' % collection, index=False, encoding='utf8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "users_df.description = users_df.description.astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "users_df.description.fillna(\"\")\n",
    "users_df.description = users_df.description.apply(clean_text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(36858, 11)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>screen_name</th>\n",
       "      <th>location</th>\n",
       "      <th>followers_count</th>\n",
       "      <th>statuses_count</th>\n",
       "      <th>description</th>\n",
       "      <th>friends_count</th>\n",
       "      <th>favourites_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>verified</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>silvis2206</td>\n",
       "      <td>Buenos Aires</td>\n",
       "      <td>89</td>\n",
       "      <td>3129</td>\n",
       "      <td>“Nada hay absoluto, todo se cambia, todo se mueve, todo revoluciona, todo vuela y se va.”</td>\n",
       "      <td>713</td>\n",
       "      <td>8113</td>\n",
       "      <td>Sun Nov 21 15:54:52 +0000 2010</td>\n",
       "      <td>218159646</td>\n",
       "      <td>silvina</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tonga_Arman80</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>393</td>\n",
       "      <td>13491</td>\n",
       "      <td>Lo importante es la familia!!! La patria es el otro!!! #TropaK</td>\n",
       "      <td>497</td>\n",
       "      <td>58295</td>\n",
       "      <td>Mon May 17 16:27:25 +0000 2010</td>\n",
       "      <td>144904500</td>\n",
       "      <td>Tonga</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Tewhok</td>\n",
       "      <td>None</td>\n",
       "      <td>3015</td>\n",
       "      <td>58461</td>\n",
       "      <td>Cenador Vitalicio Decapitalista</td>\n",
       "      <td>4919</td>\n",
       "      <td>7648</td>\n",
       "      <td>Sat Jun 18 11:44:51 +0000 2011</td>\n",
       "      <td>319594285</td>\n",
       "      <td>Guillermo Sordo</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SilviaMam1</td>\n",
       "      <td>None</td>\n",
       "      <td>675</td>\n",
       "      <td>46338</td>\n",
       "      <td>None</td>\n",
       "      <td>708</td>\n",
       "      <td>18632</td>\n",
       "      <td>Fri Mar 03 12:57:13 +0000 2017</td>\n",
       "      <td>837648053968457728</td>\n",
       "      <td>Silvia Berth</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>avaro_schoeller</td>\n",
       "      <td>María Juana y Santa Fe</td>\n",
       "      <td>171</td>\n",
       "      <td>1113</td>\n",
       "      <td>Inconstancia, inconciencia y lealtad.</td>\n",
       "      <td>374</td>\n",
       "      <td>346</td>\n",
       "      <td>Sat May 21 01:08:28 +0000 2011</td>\n",
       "      <td>302339856</td>\n",
       "      <td>Fede Schoeller</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       screen_name                location  followers_count  statuses_count  \\\n",
       "0  silvis2206       Buenos Aires            89               3129             \n",
       "1  Tonga_Arman80    Argentina               393              13491            \n",
       "2  Tewhok           None                    3015             58461            \n",
       "3  SilviaMam1       None                    675              46338            \n",
       "4  avaro_schoeller  María Juana y Santa Fe  171              1113             \n",
       "\n",
       "                                                                                 description  \\\n",
       "0  “Nada hay absoluto, todo se cambia, todo se mueve, todo revoluciona, todo vuela y se va.”   \n",
       "1  Lo importante es la familia!!! La patria es el otro!!! #TropaK                              \n",
       "2  Cenador Vitalicio Decapitalista                                                             \n",
       "3  None                                                                                        \n",
       "4  Inconstancia, inconciencia y lealtad.                                                       \n",
       "\n",
       "   friends_count  favourites_count                      created_at  \\\n",
       "0  713            8113              Sun Nov 21 15:54:52 +0000 2010   \n",
       "1  497            58295             Mon May 17 16:27:25 +0000 2010   \n",
       "2  4919           7648              Sat Jun 18 11:44:51 +0000 2011   \n",
       "3  708            18632             Fri Mar 03 12:57:13 +0000 2017   \n",
       "4  374            346               Sat May 21 01:08:28 +0000 2011   \n",
       "\n",
       "                   id             name  verified  \n",
       "0  218159646           silvina          False     \n",
       "1  144904500           Tonga            False     \n",
       "2  319594285           Guillermo Sordo  False     \n",
       "3  837648053968457728  Silvia Berth     False     \n",
       "4  302339856           Fede Schoeller   False     "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "users_df.to_csv('./csvs/%s_users.csv' % collection, index=False, encoding='utf8')"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "default_view": {},
   "name": "1.0-análisis-tweets-mongo.ipynb",
   "provenance": [],
   "version": "0.3.2",
   "views": {}
  },
  "kernelspec": {
   "display_name": "Python [conda env:escucha-tec-notebooks] *",
   "language": "python",
   "name": "conda-env-escucha-tec-notebooks-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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}