wrangle_act_v2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Project: Wrangle and Analyze WeRateDogs Twitter Account\n",
    "## Udactiy Data Analyst Nanodegree Program - Project 8 / Part 1: Data Wrangling\n",
    "##### Esra Arı\n",
    "##### 08'18\n",
    "\n",
    "\n",
    "\n",
    "## Table of Contents\n",
    "<ul>\n",
    "<li><a href=\"#intro\">Introduction</a></li>\n",
    "<li><a href=\"#gathering\">Gathering Data</a></li>\n",
    "<li><a href=\"#assessing\">Assessing Data</a></li>\n",
    "<li><a href=\"#cleaning\">Cleaning Data</a></li>\n",
    "<li><a href=\"#storing\">Storing/Exporting Data</a></li>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='intro'></a>\n",
    "## Introduction\n",
    "\n",
    "This analysis is 8th project of the Udacity Data Analysis Nanodegree program.  Project goal is wrangling WeRateDogs twitter data to create interesting and trustworthy visualizations, both exploratory and predictive analysis trying different machine learning algorithms. \n",
    "\n",
    "Due to complexity of project, there are 2 different jupyter notebook project named as wrangle_act and act_report in **bold** at below.\n",
    "\n",
    "Methodology in this project is given following:\n",
    "- Data wrangling, which consists of: **(wrangle_act - Data Wrangling)**\n",
    "  - Gathering data\n",
    "  - Assessing data \n",
    "  -\tCleaning data\n",
    "  - Storing/Exporting data\n",
    "  \n",
    "  \n",
    "  \n",
    "- Exploratory Data Analysis **(act_report - Exploratory and Predictive Analyses)**\n",
    "  - Analyzing data \n",
    "  - Visualizing data\n",
    "  \n",
    "  \n",
    "  \n",
    "- Predictive Data Analysis **(Word documentation which includes all process in summary.)**\n",
    " - Editing Metadata\n",
    " - Missing Value Treatment\n",
    " - Feature Extraction / Feature Hashing\n",
    " - Dimension Reduction / Principle Component Analysis\n",
    " - Using different sampling techniques such as oversampling \n",
    " - Data splitting\n",
    " - Trying different supervised machine learning algorithms with different parameters. (Random forest and boosted decision tree algorithms were applied for this project on Azure network)\n",
    " - Reporting on 1) data wrangling efforts and 2) data analyses and visualizations 3) prediction methodology in an executive way with Microsoft word."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='gathering'></a>\n",
    "## Gathering Data\n",
    "\n",
    "The three pieces of data gathered for this project as described below.\n",
    "- The WeRateDogs Twitter archive.\n",
    "- The tweet image predictions, i.e., what breed of dog (or other object, animal, etc.) is present in each tweet according to a neural network. This file (image_predictions.tsv) is hosted on Udacity's servers.\n",
    "- Each tweet's retweet count and favorite (\"like\") count at minimum, and any additional data. Using the tweet IDs in the WeRateDogs Twitter archive, query the Twitter API which is mentioned in Twitter API section detailed for each tweet's JSON data using Python's Tweepy library and store each tweet's entire set of JSON data in a file called tweet_json.txt file. Each tweet's JSON data should be written to its own line. Then read this .txt file line by line into a pandas DataFrame with (at minimum) tweet ID, retweet count, and favorite count. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Necessary libraries are dowloaded as following."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import requests as rq\n",
    "import json as js\n",
    "import tweepy as tp\n",
    "import re"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Firstly, WeRateDogs twitter arciheve is imported as df1. To gather number of retweet, favoite information belongs to these tweet id's, I am using tweepy API in the following section. It is important to keep in mind that these tweet_id's will be using following section to gather more information. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>tweet_id</th>\n",
       "      <th>in_reply_to_status_id</th>\n",
       "      <th>in_reply_to_user_id</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>source</th>\n",
       "      <th>text</th>\n",
       "      <th>retweeted_status_id</th>\n",
       "      <th>retweeted_status_user_id</th>\n",
       "      <th>retweeted_status_timestamp</th>\n",
       "      <th>expanded_urls</th>\n",
       "      <th>rating_numerator</th>\n",
       "      <th>rating_denominator</th>\n",
       "      <th>name</th>\n",
       "      <th>doggo</th>\n",
       "      <th>floofer</th>\n",
       "      <th>pupper</th>\n",
       "      <th>puppo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>876120275196170240</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-06-17 16:52:05 +0000</td>\n",
       "      <td>&lt;a href=\"http://twitter.com/download/iphone\" r...</td>\n",
       "      <td>Meet Venti, a seemingly caffeinated puppoccino...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://twitter.com/dog_rates/status/876120275...</td>\n",
       "      <td>13</td>\n",
       "      <td>10</td>\n",
       "      <td>Venti</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              tweet_id  in_reply_to_status_id  in_reply_to_user_id  \\\n",
       "85  876120275196170240                    NaN                  NaN   \n",
       "\n",
       "                    timestamp  \\\n",
       "85  2017-06-17 16:52:05 +0000   \n",
       "\n",
       "                                               source  \\\n",
       "85  <a href=\"http://twitter.com/download/iphone\" r...   \n",
       "\n",
       "                                                 text  retweeted_status_id  \\\n",
       "85  Meet Venti, a seemingly caffeinated puppoccino...                  NaN   \n",
       "\n",
       "    retweeted_status_user_id retweeted_status_timestamp  \\\n",
       "85                       NaN                        NaN   \n",
       "\n",
       "                                        expanded_urls  rating_numerator  \\\n",
       "85  https://twitter.com/dog_rates/status/876120275...                13   \n",
       "\n",
       "    rating_denominator   name doggo floofer pupper puppo  \n",
       "85                  10  Venti  None    None   None  None  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_csv(\"twitter-archive-enhanced.csv\")\n",
    "df1.sample()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The tweet image predictions data imported as df2."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>tweet_id</th>\n",
       "      <th>jpg_url</th>\n",
       "      <th>img_num</th>\n",
       "      <th>p1</th>\n",
       "      <th>p1_conf</th>\n",
       "      <th>p1_dog</th>\n",
       "      <th>p2</th>\n",
       "      <th>p2_conf</th>\n",
       "      <th>p2_dog</th>\n",
       "      <th>p3</th>\n",
       "      <th>p3_conf</th>\n",
       "      <th>p3_dog</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>613</th>\n",
       "      <td>680145970311643136</td>\n",
       "      <td>https://pbs.twimg.com/media/CXBdJxLUsAAWql2.jpg</td>\n",
       "      <td>1</td>\n",
       "      <td>miniature_poodle</td>\n",
       "      <td>0.457117</td>\n",
       "      <td>True</td>\n",
       "      <td>toy_poodle</td>\n",
       "      <td>0.226481</td>\n",
       "      <td>True</td>\n",
       "      <td>Maltese_dog</td>\n",
       "      <td>0.067682</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               tweet_id                                          jpg_url  \\\n",
       "613  680145970311643136  https://pbs.twimg.com/media/CXBdJxLUsAAWql2.jpg   \n",
       "\n",
       "     img_num                p1   p1_conf  p1_dog          p2   p2_conf  \\\n",
       "613        1  miniature_poodle  0.457117    True  toy_poodle  0.226481   \n",
       "\n",
       "     p2_dog           p3   p3_conf  p3_dog  \n",
       "613    True  Maltese_dog  0.067682    True  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.read_csv(\"image-predictions.tsv\", delimiter='\\t',encoding='utf-8')\n",
    "df2.sample()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I created my own account on tweepy API, got following credentials to access API."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "consumer_key = 'bL23pGKR1iCxzLupjZhCHXrLO'\n",
    "consumer_secret = 'llfHsX4dIX4x1j5k0mBVLXRoJfqqoZRFim3mESz10OJhAJEJTR'\n",
    "access_token = '345309863-rhfxAY4CWg78PmAp5PssNslsU691WbhkqxvpEnb3'\n",
    "access_secret = '5jRaAuyCOLyg3K77csYTlYgqePEgbfXe6sVGdWR39VIPJ'\n",
    "\n",
    "auth = tp.OAuthHandler(consumer_key, consumer_secret)\n",
    "auth.set_access_token(access_token, access_secret)\n",
    "\n",
    "api = tp.API(auth, wait_on_rate_limit = True, wait_on_rate_limit_notify = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I created a loop which adds each available tweet to a new line of tweet_json.txt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Rate limit reached. Sleeping for: 375\n",
      "Rate limit reached. Sleeping for: 440\n",
      "Rate limit reached. Sleeping for: 445\n"
     ]
    }
   ],
   "source": [
    "with open('tweet_json.txt', 'a', encoding='utf8') as f:\n",
    "    for tweet_id in df1['tweet_id']:\n",
    "        try:\n",
    "            tweet = api.get_status(tweet_id, tweet_mode='extended')\n",
    "            js.dump(tweet._json, f)\n",
    "            f.write('\\n')\n",
    "        except:\n",
    "            continue"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, I have dowloaded tweet_json.txt file. However, it needs to be mapped and converted to pandas dataframe for further analysis.Following code's loop helps to append each tweet into a list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "tweets_data = []\n",
    "\n",
    "tweet_file = open('tweet_json.txt', \"r\")\n",
    "\n",
    "for line in tweet_file:\n",
    "    try:\n",
    "        tweet = js.loads(line)\n",
    "        tweets_data.append(tweet)\n",
    "    except:\n",
    "        continue\n",
    "        \n",
    "tweet_file.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = pd.DataFrame()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the help of https://jsoneditoronline.org/ site, json object which belongs to the just one id can be investigated an accoding to that demanded information can be mapped to the list appended into pandas dataframe in the following. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3['id'] = list(map(lambda tweet: tweet['id'], tweets_data))\n",
    "df3['retweet_count'] = list(map(lambda tweet: tweet['retweet_count'], tweets_data))\n",
    "df3['favorite_count'] = list(map(lambda tweet: tweet['favorite_count'], tweets_data))\n",
    "df3['retweeted'] = list(map(lambda tweet: tweet['retweeted'], tweets_data))\n",
    "df3['lang'] = list(map(lambda tweet: tweet['lang'], tweets_data))\n",
    "df3['created_at'] = list(map(lambda tweet: tweet['created_at'], tweets_data))\n",
    "df3['contributors'] = list(map(lambda tweet: tweet['contributors'], tweets_data))\n",
    "df3['favorited'] = list(map(lambda tweet: tweet['favorited'], tweets_data))\n",
    "df3['coordinates'] = list(map(lambda tweet: tweet['coordinates'], tweets_data))\n",
    "df3['place'] = list(map(lambda tweet: tweet['place'], tweets_data))\n",
    "df3['geo'] = list(map(lambda tweet: tweet['geo'], tweets_data))\n",
    "df3['place'] = list(map(lambda tweet: tweet['place'], tweets_data))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3715 entries, 0 to 3714\n",
      "Data columns (total 11 columns):\n",
      "id                3715 non-null int64\n",
      "retweet_count     3715 non-null int64\n",
      "favorite_count    3715 non-null int64\n",
      "retweeted         3715 non-null bool\n",
      "lang              3715 non-null object\n",
      "created_at        3715 non-null object\n",
      "contributors      0 non-null object\n",
      "favorited         3715 non-null bool\n",
      "coordinates       0 non-null object\n",
      "place             1 non-null object\n",
      "geo               0 non-null object\n",
      "dtypes: bool(2), int64(3), object(6)\n",
      "memory usage: 268.5+ KB\n"
     ]
    }
   ],
   "source": [
    "df3.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='assessing'></a>\n",
    "## Assessing Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this section, each three pandas dataframe gathered previous section will be investigated.  Assesing data will be done both **visiualy**(scrolling through the data in your preferred software application) and **programmatically**(using code to view specific portions and summaries of the data). Both quality and tiddiness issue will be noted end of this section. Also, sources of low quality /dirty and messy/untidy data will be mentioned shortly. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Sources of Dirty Data**\n",
    "\n",
    "***Dirty data = low quality data = content issues***\n",
    "\n",
    "There are lots of sources of dirty data. Basically, anytime humans are involved, there's going to be dirty data. There are lots of ways in which we touch data we work with.\n",
    "\n",
    "- user entry errors\n",
    "- no data coding standards, or having standards poorly applied, causing problems in the resulting data\n",
    "- integrated data where different schemas have been used for the same type of item\n",
    "- legacy data systems, where data wasn't coded when disc and memory constraints were much more restrictive than they are now. Over time systems evolve. Needs change, and data changes\n",
    "- no unique identifiers it should\n",
    "- lost in transformation from one format to another\n",
    "- programmer error\n",
    "- corrupted in transmission or storage by cosmic rays or other physical phenomenon\n",
    "\n",
    "**Sources of Messy Data**\n",
    "\n",
    "***Messy data = untidy data = structural issues***\n",
    "\n",
    "Messy data is usually the result of poor data planning. Or a lack of awareness of the benefits of tidy data. Fortunately, messy data is usually much more easily addressable than most of the sources of dirty data mentioned above."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I wanted to move easy to hard. Therefore, I prefered to start with df2 dataframe which includes image-prediction dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2075 entries, 0 to 2074\n",
      "Data columns (total 12 columns):\n",
      "tweet_id    2075 non-null int64\n",
      "jpg_url     2075 non-null object\n",
      "img_num     2075 non-null int64\n",
      "p1          2075 non-null object\n",
      "p1_conf     2075 non-null float64\n",
      "p1_dog      2075 non-null bool\n",
      "p2          2075 non-null object\n",
      "p2_conf     2075 non-null float64\n",
      "p2_dog      2075 non-null bool\n",
      "p3          2075 non-null object\n",
      "p3_conf     2075 non-null float64\n",
      "p3_dog      2075 non-null bool\n",
      "dtypes: bool(3), float64(3), int64(2), object(4)\n",
      "memory usage: 152.1+ KB\n"
     ]
    }
   ],
   "source": [
    "df2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>tweet_id</th>\n",
       "      <th>jpg_url</th>\n",
       "      <th>img_num</th>\n",
       "      <th>p1</th>\n",
       "      <th>p1_conf</th>\n",
       "      <th>p1_dog</th>\n",
       "      <th>p2</th>\n",
       "      <th>p2_conf</th>\n",
       "      <th>p2_dog</th>\n",
       "      <th>p3</th>\n",
       "      <th>p3_conf</th>\n",
       "      <th>p3_dog</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>666020888022790149</td>\n",
       "      <td>https://pbs.twimg.com/media/CT4udn0WwAA0aMy.jpg</td>\n",
       "      <td>1</td>\n",
       "      <td>Welsh_springer_spaniel</td>\n",
       "      <td>0.465074</td>\n",
       "      <td>True</td>\n",
       "      <td>collie</td>\n",
       "      <td>0.156665</td>\n",
       "      <td>True</td>\n",
       "      <td>Shetland_sheepdog</td>\n",
       "      <td>0.061428</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>666029285002620928</td>\n",
       "      <td>https://pbs.twimg.com/media/CT42GRgUYAA5iDo.jpg</td>\n",
       "      <td>1</td>\n",
       "      <td>redbone</td>\n",
       "      <td>0.506826</td>\n",
       "      <td>True</td>\n",
       "      <td>miniature_pinscher</td>\n",
       "      <td>0.074192</td>\n",
       "      <td>True</td>\n",
       "      <td>Rhodesian_ridgeback</td>\n",
       "      <td>0.072010</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>666033412701032449</td>\n",
       "      <td>https://pbs.twimg.com/media/CT4521TWwAEvMyu.jpg</td>\n",
       "      <td>1</td>\n",
       "      <td>German_shepherd</td>\n",
       "      <td>0.596461</td>\n",
       "      <td>True</td>\n",
       "      <td>malinois</td>\n",
       "      <td>0.138584</td>\n",
       "      <td>True</td>\n",
       "      <td>bloodhound</td>\n",
       "      <td>0.116197</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>666044226329800704</td>\n",
       "      <td>https://pbs.twimg.com/media/CT5Dr8HUEAA-lEu.jpg</td>\n",
       "      <td>1</td>\n",
       "      <td>Rhodesian_ridgeback</td>\n",
       "      <td>0.408143</td>\n",
       "      <td>True</td>\n",
       "      <td>redbone</td>\n",
       "      <td>0.360687</td>\n",
       "      <td>True</td>\n",
       "      <td>miniature_pinscher</td>\n",
       "      <td>0.222752</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>666049248165822465</td>\n",
       "      <td>https://pbs.twimg.com/media/CT5IQmsXIAAKY4A.jpg</td>\n",
       "      <td>1</td>\n",
       "      <td>miniature_pinscher</td>\n",
       "      <td>0.560311</td>\n",
       "      <td>True</td>\n",
       "      <td>Rottweiler</td>\n",
       "      <td>0.243682</td>\n",
       "      <td>True</td>\n",
       "      <td>Doberman</td>\n",
       "      <td>0.154629</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             tweet_id                                          jpg_url  \\\n",
       "0  666020888022790149  https://pbs.twimg.com/media/CT4udn0WwAA0aMy.jpg   \n",
       "1  666029285002620928  https://pbs.twimg.com/media/CT42GRgUYAA5iDo.jpg   \n",
       "2  666033412701032449  https://pbs.twimg.com/media/CT4521TWwAEvMyu.jpg   \n",
       "3  666044226329800704  https://pbs.twimg.com/media/CT5Dr8HUEAA-lEu.jpg   \n",
       "4  666049248165822465  https://pbs.twimg.com/media/CT5IQmsXIAAKY4A.jpg   \n",
       "\n",
       "   img_num                      p1   p1_conf  p1_dog                  p2  \\\n",
       "0        1  Welsh_springer_spaniel  0.465074    True              collie   \n",
       "1        1                 redbone  0.506826    True  miniature_pinscher   \n",
       "2        1         German_shepherd  0.596461    True            malinois   \n",
       "3        1     Rhodesian_ridgeback  0.408143    True             redbone   \n",
       "4        1      miniature_pinscher  0.560311    True          Rottweiler   \n",
       "\n",
       "    p2_conf  p2_dog                   p3   p3_conf  p3_dog  \n",
       "0  0.156665    True    Shetland_sheepdog  0.061428    True  \n",
       "1  0.074192    True  Rhodesian_ridgeback  0.072010    True  \n",
       "2  0.138584    True           bloodhound  0.116197    True  \n",
       "3  0.360687    True   miniature_pinscher  0.222752    True  \n",
       "4  0.243682    True             Doberman  0.154629    True  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>tweet_id</th>\n",
       "      <th>jpg_url</th>\n",
       "      <th>img_num</th>\n",
       "      <th>p1</th>\n",
       "      <th>p1_conf</th>\n",
       "      <th>p1_dog</th>\n",
       "      <th>p2</th>\n",
       "      <th>p2_conf</th>\n",
       "      <th>p2_dog</th>\n",
       "      <th>p3</th>\n",
       "      <th>p3_conf</th>\n",
       "      <th>p3_dog</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2070</th>\n",
       "      <td>891327558926688256</td>\n",
       "      <td>https://pbs.twimg.com/media/DF6hr6BUMAAzZgT.jpg</td>\n",
       "      <td>2</td>\n",
       "      <td>basset</td>\n",
       "      <td>0.555712</td>\n",
       "      <td>True</td>\n",
       "      <td>English_springer</td>\n",
       "      <td>0.225770</td>\n",
       "      <td>True</td>\n",
       "      <td>German_short-haired_pointer</td>\n",
       "      <td>0.175219</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2071</th>\n",
       "      <td>891689557279858688</td>\n",
       "      <td>https://pbs.twimg.com/media/DF_q7IAWsAEuuN8.jpg</td>\n",
       "      <td>1</td>\n",
       "      <td>paper_towel</td>\n",
       "      <td>0.170278</td>\n",
       "      <td>False</td>\n",
       "      <td>Labrador_retriever</td>\n",
       "      <td>0.168086</td>\n",
       "      <td>True</td>\n",
       "      <td>spatula</td>\n",
       "      <td>0.040836</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2072</th>\n",
       "      <td>891815181378084864</td>\n",
       "      <td>https://pbs.twimg.com/media/DGBdLU1WsAANxJ9.jpg</td>\n",
       "      <td>1</td>\n",
       "      <td>Chihuahua</td>\n",
       "      <td>0.716012</td>\n",
       "      <td>True</td>\n",
       "      <td>malamute</td>\n",
       "      <td>0.078253</td>\n",
       "      <td>True</td>\n",
       "      <td>kelpie</td>\n",
       "      <td>0.031379</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2073</th>\n",
       "      <td>892177421306343426</td>\n",
       "      <td>https://pbs.twimg.com/media/DGGmoV4XsAAUL6n.jpg</td>\n",
       "      <td>1</td>\n",
       "      <td>Chihuahua</td>\n",
       "      <td>0.323581</td>\n",
       "      <td>True</td>\n",
       "      <td>Pekinese</td>\n",
       "      <td>0.090647</td>\n",
       "      <td>True</td>\n",
       "      <td>papillon</td>\n",
       "      <td>0.068957</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2074</th>\n",
       "      <td>892420643555336193</td>\n",
       "      <td>https://pbs.twimg.com/media/DGKD1-bXoAAIAUK.jpg</td>\n",
       "      <td>1</td>\n",
       "      <td>orange</td>\n",
       "      <td>0.097049</td>\n",
       "      <td>False</td>\n",
       "      <td>bagel</td>\n",
       "      <td>0.085851</td>\n",
       "      <td>False</td>\n",
       "      <td>banana</td>\n",
       "      <td>0.076110</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                tweet_id                                          jpg_url  \\\n",
       "2070  891327558926688256  https://pbs.twimg.com/media/DF6hr6BUMAAzZgT.jpg   \n",
       "2071  891689557279858688  https://pbs.twimg.com/media/DF_q7IAWsAEuuN8.jpg   \n",
       "2072  891815181378084864  https://pbs.twimg.com/media/DGBdLU1WsAANxJ9.jpg   \n",
       "2073  892177421306343426  https://pbs.twimg.com/media/DGGmoV4XsAAUL6n.jpg   \n",
       "2074  892420643555336193  https://pbs.twimg.com/media/DGKD1-bXoAAIAUK.jpg   \n",
       "\n",
       "      img_num           p1   p1_conf  p1_dog                  p2   p2_conf  \\\n",
       "2070        2       basset  0.555712    True    English_springer  0.225770   \n",
       "2071        1  paper_towel  0.170278   False  Labrador_retriever  0.168086   \n",
       "2072        1    Chihuahua  0.716012    True            malamute  0.078253   \n",
       "2073        1    Chihuahua  0.323581    True            Pekinese  0.090647   \n",
       "2074        1       orange  0.097049   False               bagel  0.085851   \n",
       "\n",
       "      p2_dog                           p3   p3_conf  p3_dog  \n",
       "2070    True  German_short-haired_pointer  0.175219    True  \n",
       "2071    True                      spatula  0.040836   False  \n",
       "2072    True                       kelpie  0.031379    True  \n",
       "2073    True                     papillon  0.068957    True  \n",
       "2074   False                       banana  0.076110   False  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.tail()"
   ]
  },
  {
   "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>tweet_id</th>\n",
       "      <th>jpg_url</th>\n",
       "      <th>img_num</th>\n",
       "      <th>p1</th>\n",
       "      <th>p1_conf</th>\n",
       "      <th>p1_dog</th>\n",
       "      <th>p2</th>\n",
       "      <th>p2_conf</th>\n",
       "      <th>p2_dog</th>\n",
       "      <th>p3</th>\n",
       "      <th>p3_conf</th>\n",
       "      <th>p3_dog</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>924</th>\n",
       "      <td>702321140488925184</td>\n",
       "      <td>https://pbs.twimg.com/media/Cb8lWafWEAA2q93.jpg</td>\n",
       "      <td>3</td>\n",
       "      <td>West_Highland_white_terrier</td>\n",
       "      <td>0.769159</td>\n",
       "      <td>True</td>\n",
       "      <td>Scotch_terrier</td>\n",
       "      <td>0.064369</td>\n",
       "      <td>True</td>\n",
       "      <td>Old_English_sheepdog</td>\n",
       "      <td>0.043763</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1912</th>\n",
       "      <td>853760880890318849</td>\n",
       "      <td>https://pbs.twimg.com/media/C9kq_bbVwAAuRZd.jpg</td>\n",
       "      <td>1</td>\n",
       "      <td>miniature_pinscher</td>\n",
       "      <td>0.292519</td>\n",
       "      <td>True</td>\n",
       "      <td>Chihuahua</td>\n",
       "      <td>0.120946</td>\n",
       "      <td>True</td>\n",
       "      <td>Rottweiler</td>\n",
       "      <td>0.119490</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <td>877316821321428993</td>\n",
       "      <td>https://pbs.twimg.com/media/DCza_vtXkAQXGpC.jpg</td>\n",
       "      <td>1</td>\n",
       "      <td>Saluki</td>\n",
       "      <td>0.509967</td>\n",
       "      <td>True</td>\n",
       "      <td>Italian_greyhound</td>\n",
       "      <td>0.090497</td>\n",
       "      <td>True</td>\n",
       "      <td>golden_retriever</td>\n",
       "      <td>0.079406</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1346</th>\n",
       "      <td>759197388317847553</td>\n",
       "      <td>https://pbs.twimg.com/media/Cok1_sjXgAU3xpp.jpg</td>\n",
       "      <td>1</td>\n",
       "      <td>kuvasz</td>\n",
       "      <td>0.511341</td>\n",
       "      <td>True</td>\n",
       "      <td>golden_retriever</td>\n",
       "      <td>0.076899</td>\n",
       "      <td>True</td>\n",
       "      <td>white_wolf</td>\n",
       "      <td>0.063269</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>797</th>\n",
       "      <td>691090071332753408</td>\n",
       "      <td>https://pbs.twimg.com/media/CZc-u7IXEAQHV1N.jpg</td>\n",
       "      <td>1</td>\n",
       "      <td>barrow</td>\n",
       "      <td>0.241637</td>\n",
       "      <td>False</td>\n",
       "      <td>tub</td>\n",
       "      <td>0.238450</td>\n",
       "      <td>False</td>\n",
       "      <td>bathtub</td>\n",
       "      <td>0.167285</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                tweet_id                                          jpg_url  \\\n",
       "924   702321140488925184  https://pbs.twimg.com/media/Cb8lWafWEAA2q93.jpg   \n",
       "1912  853760880890318849  https://pbs.twimg.com/media/C9kq_bbVwAAuRZd.jpg   \n",
       "2004  877316821321428993  https://pbs.twimg.com/media/DCza_vtXkAQXGpC.jpg   \n",
       "1346  759197388317847553  https://pbs.twimg.com/media/Cok1_sjXgAU3xpp.jpg   \n",
       "797   691090071332753408  https://pbs.twimg.com/media/CZc-u7IXEAQHV1N.jpg   \n",
       "\n",
       "      img_num                           p1   p1_conf  p1_dog  \\\n",
       "924         3  West_Highland_white_terrier  0.769159    True   \n",
       "1912        1           miniature_pinscher  0.292519    True   \n",
       "2004        1                       Saluki  0.509967    True   \n",
       "1346        1                       kuvasz  0.511341    True   \n",
       "797         1                       barrow  0.241637   False   \n",
       "\n",
       "                     p2   p2_conf  p2_dog                    p3   p3_conf  \\\n",
       "924      Scotch_terrier  0.064369    True  Old_English_sheepdog  0.043763   \n",
       "1912          Chihuahua  0.120946    True            Rottweiler  0.119490   \n",
       "2004  Italian_greyhound  0.090497    True      golden_retriever  0.079406   \n",
       "1346   golden_retriever  0.076899    True            white_wolf  0.063269   \n",
       "797                 tub  0.238450   False               bathtub  0.167285   \n",
       "\n",
       "      p3_dog  \n",
       "924     True  \n",
       "1912    True  \n",
       "2004    True  \n",
       "1346   False  \n",
       "797    False  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "golden_retriever      150\n",
       "Labrador_retriever    100\n",
       "Pembroke               89\n",
       "Chihuahua              83\n",
       "pug                    57\n",
       "Name: p1, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.p1.value_counts(). head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Labrador_retriever    104\n",
       "golden_retriever       92\n",
       "Cardigan               73\n",
       "Chihuahua              44\n",
       "Pomeranian             42\n",
       "Name: p2, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.p2.value_counts().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>tweet_id</th>\n",
       "      <th>img_num</th>\n",
       "      <th>p1_conf</th>\n",
       "      <th>p2_conf</th>\n",
       "      <th>p3_conf</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2.075000e+03</td>\n",
       "      <td>2075.000000</td>\n",
       "      <td>2075.000000</td>\n",
       "      <td>2.075000e+03</td>\n",
       "      <td>2.075000e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>7.384514e+17</td>\n",
       "      <td>1.203855</td>\n",
       "      <td>0.594548</td>\n",
       "      <td>1.345886e-01</td>\n",
       "      <td>6.032417e-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>6.785203e+16</td>\n",
       "      <td>0.561875</td>\n",
       "      <td>0.271174</td>\n",
       "      <td>1.006657e-01</td>\n",
       "      <td>5.090593e-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>6.660209e+17</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.044333</td>\n",
       "      <td>1.011300e-08</td>\n",
       "      <td>1.740170e-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>6.764835e+17</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.364412</td>\n",
       "      <td>5.388625e-02</td>\n",
       "      <td>1.622240e-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>7.119988e+17</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.588230</td>\n",
       "      <td>1.181810e-01</td>\n",
       "      <td>4.944380e-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>7.932034e+17</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.843855</td>\n",
       "      <td>1.955655e-01</td>\n",
       "      <td>9.180755e-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>8.924206e+17</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>4.880140e-01</td>\n",
       "      <td>2.734190e-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           tweet_id      img_num      p1_conf       p2_conf       p3_conf\n",
       "count  2.075000e+03  2075.000000  2075.000000  2.075000e+03  2.075000e+03\n",
       "mean   7.384514e+17     1.203855     0.594548  1.345886e-01  6.032417e-02\n",
       "std    6.785203e+16     0.561875     0.271174  1.006657e-01  5.090593e-02\n",
       "min    6.660209e+17     1.000000     0.044333  1.011300e-08  1.740170e-10\n",
       "25%    6.764835e+17     1.000000     0.364412  5.388625e-02  1.622240e-02\n",
       "50%    7.119988e+17     1.000000     0.588230  1.181810e-01  4.944380e-02\n",
       "75%    7.932034e+17     1.000000     0.843855  1.955655e-01  9.180755e-02\n",
       "max    8.924206e+17     4.000000     1.000000  4.880140e-01  2.734190e-01"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "https://pbs.twimg.com/media/CtVAvX-WIAAcGTf.jpg    2\n",
       "https://pbs.twimg.com/media/Cwx99rpW8AMk_Ie.jpg    2\n",
       "https://pbs.twimg.com/media/CwS4aqZXUAAe3IO.jpg    2\n",
       "https://pbs.twimg.com/media/ChK1tdBWwAQ1flD.jpg    2\n",
       "https://pbs.twimg.com/media/CU1zsMSUAAAS0qW.jpg    2\n",
       "Name: jpg_url, dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2['jpg_url'].value_counts(). head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "685532292383666176    1\n",
       "826598365270007810    1\n",
       "692158366030913536    1\n",
       "714606013974974464    1\n",
       "715696743237730304    1\n",
       "Name: tweet_id, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2['tweet_id'].value_counts().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3715 entries, 0 to 3714\n",
      "Data columns (total 11 columns):\n",
      "id                3715 non-null int64\n",
      "retweet_count     3715 non-null int64\n",
      "favorite_count    3715 non-null int64\n",
      "retweeted         3715 non-null bool\n",
      "lang              3715 non-null object\n",
      "created_at        3715 non-null object\n",
      "contributors      0 non-null object\n",
      "favorited         3715 non-null bool\n",
      "coordinates       0 non-null object\n",
      "place             1 non-null object\n",
      "geo               0 non-null object\n",
      "dtypes: bool(2), int64(3), object(6)\n",
      "memory usage: 268.5+ KB\n"
     ]
    }
   ],
   "source": [
    "df3.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "666337882303524864    4\n",
       "666020888022790149    4\n",
       "666421158376562688    4\n",
       "666396247373291520    4\n",
       "666353288456101888    4\n",
       "Name: id, dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3[df3.id.duplicated()].id.value_counts().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>retweet_count</th>\n",
       "      <th>favorite_count</th>\n",
       "      <th>retweeted</th>\n",
       "      <th>lang</th>\n",
       "      <th>created_at</th>\n",
       "      <th>contributors</th>\n",
       "      <th>favorited</th>\n",
       "      <th>coordinates</th>\n",
       "      <th>place</th>\n",
       "      <th>geo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>666337882303524864</td>\n",
       "      <td>91</td>\n",
       "      <td>199</td>\n",
       "      <td>False</td>\n",
       "      <td>en</td>\n",
       "      <td>Mon Nov 16 19:31:45 +0000 2015</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>666337882303524864</td>\n",
       "      <td>91</td>\n",
       "      <td>199</td>\n",
       "      <td>False</td>\n",
       "      <td>en</td>\n",
       "      <td>Mon Nov 16 19:31:45 +0000 2015</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>666337882303524864</td>\n",
       "      <td>91</td>\n",
       "      <td>199</td>\n",
       "      <td>False</td>\n",
       "      <td>en</td>\n",
       "      <td>Mon Nov 16 19:31:45 +0000 2015</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>666337882303524864</td>\n",
       "      <td>91</td>\n",
       "      <td>199</td>\n",
       "      <td>False</td>\n",
       "      <td>en</td>\n",
       "      <td>Mon Nov 16 19:31:45 +0000 2015</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3692</th>\n",
       "      <td>666337882303524864</td>\n",
       "      <td>91</td>\n",
       "      <td>199</td>\n",
       "      <td>False</td>\n",
       "      <td>en</td>\n",
       "      <td>Mon Nov 16 19:31:45 +0000 2015</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      id  retweet_count  favorite_count  retweeted lang  \\\n",
       "22    666337882303524864             91             199      False   en   \n",
       "72    666337882303524864             91             199      False   en   \n",
       "122   666337882303524864             91             199      False   en   \n",
       "172   666337882303524864             91             199      False   en   \n",
       "3692  666337882303524864             91             199      False   en   \n",
       "\n",
       "                          created_at contributors  favorited coordinates  \\\n",
       "22    Mon Nov 16 19:31:45 +0000 2015         None      False        None   \n",
       "72    Mon Nov 16 19:31:45 +0000 2015         None      False        None   \n",
       "122   Mon Nov 16 19:31:45 +0000 2015         None      False        None   \n",
       "172   Mon Nov 16 19:31:45 +0000 2015         None      False        None   \n",
       "3692  Mon Nov 16 19:31:45 +0000 2015         None      False        None   \n",
       "\n",
       "     place   geo  \n",
       "22    None  None  \n",
       "72    None  None  \n",
       "122   None  None  \n",
       "172   None  None  \n",
       "3692  None  None  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.loc[df3[\"id\"] == 666337882303524864 , ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "en     3688\n",
       "und       9\n",
       "nl        6\n",
       "in        5\n",
       "et        2\n",
       "ro        2\n",
       "tl        1\n",
       "eu        1\n",
       "es        1\n",
       "Name: lang, dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.lang.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>retweet_count</th>\n",
       "      <th>favorite_count</th>\n",
       "      <th>retweeted</th>\n",
       "      <th>lang</th>\n",
       "      <th>created_at</th>\n",
       "      <th>contributors</th>\n",
       "      <th>favorited</th>\n",
       "      <th>coordinates</th>\n",
       "      <th>place</th>\n",
       "      <th>geo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3548</th>\n",
       "      <td>668967877119254528</td>\n",
       "      <td>25</td>\n",
       "      <td>151</td>\n",
       "      <td>False</td>\n",
       "      <td>tl</td>\n",
       "      <td>Tue Nov 24 01:42:25 +0000 2015</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      id  retweet_count  favorite_count  retweeted lang  \\\n",
       "3548  668967877119254528             25             151      False   tl   \n",
       "\n",
       "                          created_at contributors  favorited coordinates  \\\n",
       "3548  Tue Nov 24 01:42:25 +0000 2015         None      False        None   \n",
       "\n",
       "     place   geo  \n",
       "3548  None  None  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.loc[df3[\"lang\"] == \"tl\" , ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2189    12/10 good shit Bubka\\n@wane15\n",
       "Name: text, dtype: object"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.loc[df1[\"tweet_id\"] == 668967877119254528 , ].text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    3715\n",
       "Name: favorited, dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.favorited.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    3715\n",
       "Name: retweeted, dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.retweeted.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>retweet_count</th>\n",
       "      <th>favorite_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>3.715000e+03</td>\n",
       "      <td>3715.000000</td>\n",
       "      <td>3715.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>7.355399e+17</td>\n",
       "      <td>2722.948048</td>\n",
       "      <td>7765.391655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>7.414148e+16</td>\n",
       "      <td>4645.440655</td>\n",
       "      <td>12323.785630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>6.660209e+17</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>6.733512e+17</td>\n",
       "      <td>456.500000</td>\n",
       "      <td>975.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>6.936227e+17</td>\n",
       "      <td>1138.000000</td>\n",
       "      <td>2871.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>8.022126e+17</td>\n",
       "      <td>3278.500000</td>\n",
       "      <td>9510.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>8.924206e+17</td>\n",
       "      <td>76893.000000</td>\n",
       "      <td>142654.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 id  retweet_count  favorite_count\n",
       "count  3.715000e+03    3715.000000     3715.000000\n",
       "mean   7.355399e+17    2722.948048     7765.391655\n",
       "std    7.414148e+16    4645.440655    12323.785630\n",
       "min    6.660209e+17       0.000000        0.000000\n",
       "25%    6.733512e+17     456.500000      975.500000\n",
       "50%    6.936227e+17    1138.000000     2871.000000\n",
       "75%    8.022126e+17    3278.500000     9510.500000\n",
       "max    8.924206e+17   76893.000000   142654.000000"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2356 entries, 0 to 2355\n",
      "Data columns (total 17 columns):\n",
      "tweet_id                      2356 non-null int64\n",
      "in_reply_to_status_id         78 non-null float64\n",
      "in_reply_to_user_id           78 non-null float64\n",
      "timestamp                     2356 non-null object\n",
      "source                        2356 non-null object\n",
      "text                          2356 non-null object\n",
      "retweeted_status_id           181 non-null float64\n",
      "retweeted_status_user_id      181 non-null float64\n",
      "retweeted_status_timestamp    181 non-null object\n",
      "expanded_urls                 2297 non-null object\n",
      "rating_numerator              2356 non-null int64\n",
      "rating_denominator            2356 non-null int64\n",
      "name                          2356 non-null object\n",
      "doggo                         2356 non-null object\n",
      "floofer                       2356 non-null object\n",
      "pupper                        2356 non-null object\n",
      "puppo                         2356 non-null object\n",
      "dtypes: float64(4), int64(3), object(10)\n",
      "memory usage: 313.0+ KB\n"
     ]
    }
   ],
   "source": [
    "df1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>tweet_id</th>\n",
       "      <th>in_reply_to_status_id</th>\n",
       "      <th>in_reply_to_user_id</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>source</th>\n",
       "      <th>text</th>\n",
       "      <th>retweeted_status_id</th>\n",
       "      <th>retweeted_status_user_id</th>\n",
       "      <th>retweeted_status_timestamp</th>\n",
       "      <th>expanded_urls</th>\n",
       "      <th>rating_numerator</th>\n",
       "      <th>rating_denominator</th>\n",
       "      <th>name</th>\n",
       "      <th>doggo</th>\n",
       "      <th>floofer</th>\n",
       "      <th>pupper</th>\n",
       "      <th>puppo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [tweet_id, in_reply_to_status_id, in_reply_to_user_id, timestamp, source, text, retweeted_status_id, retweeted_status_user_id, retweeted_status_timestamp, expanded_urls, rating_numerator, rating_denominator, name, doggo, floofer, pupper, puppo]\n",
       "Index: []"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1[df1.tweet_id.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<a href=\"http://twitter.com/download/iphone\" rel=\"nofollow\">Twitter for iPhone</a>     2221\n",
      "<a href=\"http://vine.co\" rel=\"nofollow\">Vine - Make a Scene</a>                          91\n",
      "<a href=\"http://twitter.com\" rel=\"nofollow\">Twitter Web Client</a>                       33\n",
      "<a href=\"https://about.twitter.com/products/tweetdeck\" rel=\"nofollow\">TweetDeck</a>      11\n",
      "Name: source, dtype: int64\n",
      "None       745\n",
      "a           55\n",
      "Charlie     12\n",
      "Oliver      11\n",
      "Cooper      11\n",
      "Name: name, dtype: int64\n",
      "None     2259\n",
      "doggo      97\n",
      "Name: doggo, dtype: int64\n",
      "None       2346\n",
      "floofer      10\n",
      "Name: floofer, dtype: int64\n",
      "None      2099\n",
      "pupper     257\n",
      "Name: pupper, dtype: int64\n",
      "None     2326\n",
      "puppo      30\n",
      "Name: puppo, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(df1.source.value_counts().head())\n",
    "print(df1.name.value_counts().head())\n",
    "print(df1.doggo.value_counts().head())\n",
    "print(df1.floofer.value_counts().head())\n",
    "print(df1.pupper.value_counts().head())\n",
    "print(df1.puppo.value_counts().head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1035     Abby\n",
       "1021     Abby\n",
       "938       Ace\n",
       "1933     Acro\n",
       "1327    Adele\n",
       "Name: name, dtype: object"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.name.sort_values().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>tweet_id</th>\n",
       "      <th>in_reply_to_status_id</th>\n",
       "      <th>in_reply_to_user_id</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>source</th>\n",
       "      <th>text</th>\n",
       "      <th>retweeted_status_id</th>\n",
       "      <th>retweeted_status_user_id</th>\n",
       "      <th>retweeted_status_timestamp</th>\n",
       "      <th>expanded_urls</th>\n",
       "      <th>rating_numerator</th>\n",
       "      <th>rating_denominator</th>\n",
       "      <th>name</th>\n",
       "      <th>doggo</th>\n",
       "      <th>floofer</th>\n",
       "      <th>pupper</th>\n",
       "      <th>puppo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1853</th>\n",
       "      <td>675706639471788032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2015-12-12 15:59:51 +0000</td>\n",
       "      <td>&lt;a href=\"http://twitter.com/download/iphone\" r...</td>\n",
       "      <td>This is a Sizzlin Menorah spaniel from Brookly...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://twitter.com/dog_rates/status/675706639...</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>a</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1955</th>\n",
       "      <td>673636718965334016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2015-12-06 22:54:44 +0000</td>\n",
       "      <td>&lt;a href=\"http://twitter.com/download/iphone\" r...</td>\n",
       "      <td>This is a Lofted Aphrodisiac Terrier named Kip...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://twitter.com/dog_rates/status/673636718...</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>a</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2034</th>\n",
       "      <td>671743150407421952</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2015-12-01 17:30:22 +0000</td>\n",
       "      <td>&lt;a href=\"http://twitter.com/download/iphone\" r...</td>\n",
       "      <td>This is a Tuscaloosa Alcatraz named Jacob (Yac...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://twitter.com/dog_rates/status/671743150...</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>a</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2066</th>\n",
       "      <td>671147085991960577</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2015-11-30 02:01:49 +0000</td>\n",
       "      <td>&lt;a href=\"http://twitter.com/download/iphone\" r...</td>\n",
       "      <td>This is a Helvetica Listerine named Rufus. Thi...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://twitter.com/dog_rates/status/671147085...</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>a</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2116</th>\n",
       "      <td>670427002554466305</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2015-11-28 02:20:27 +0000</td>\n",
       "      <td>&lt;a href=\"http://twitter.com/download/iphone\" r...</td>\n",
       "      <td>This is a Deciduous Trimester mix named Spork....</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://twitter.com/dog_rates/status/670427002...</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>a</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                tweet_id  in_reply_to_status_id  in_reply_to_user_id  \\\n",
       "1853  675706639471788032                    NaN                  NaN   \n",
       "1955  673636718965334016                    NaN                  NaN   \n",
       "2034  671743150407421952                    NaN                  NaN   \n",
       "2066  671147085991960577                    NaN                  NaN   \n",
       "2116  670427002554466305                    NaN                  NaN   \n",
       "\n",
       "                      timestamp  \\\n",
       "1853  2015-12-12 15:59:51 +0000   \n",
       "1955  2015-12-06 22:54:44 +0000   \n",
       "2034  2015-12-01 17:30:22 +0000   \n",
       "2066  2015-11-30 02:01:49 +0000   \n",
       "2116  2015-11-28 02:20:27 +0000   \n",
       "\n",
       "                                                 source  \\\n",
       "1853  <a href=\"http://twitter.com/download/iphone\" r...   \n",
       "1955  <a href=\"http://twitter.com/download/iphone\" r...   \n",
       "2034  <a href=\"http://twitter.com/download/iphone\" r...   \n",
       "2066  <a href=\"http://twitter.com/download/iphone\" r...   \n",
       "2116  <a href=\"http://twitter.com/download/iphone\" r...   \n",
       "\n",
       "                                                   text  retweeted_status_id  \\\n",
       "1853  This is a Sizzlin Menorah spaniel from Brookly...                  NaN   \n",
       "1955  This is a Lofted Aphrodisiac Terrier named Kip...                  NaN   \n",
       "2034  This is a Tuscaloosa Alcatraz named Jacob (Yac...                  NaN   \n",
       "2066  This is a Helvetica Listerine named Rufus. Thi...                  NaN   \n",
       "2116  This is a Deciduous Trimester mix named Spork....                  NaN   \n",
       "\n",
       "      retweeted_status_user_id retweeted_status_timestamp  \\\n",
       "1853                       NaN                        NaN   \n",
       "1955                       NaN                        NaN   \n",
       "2034                       NaN                        NaN   \n",
       "2066                       NaN                        NaN   \n",
       "2116                       NaN                        NaN   \n",
       "\n",
       "                                          expanded_urls  rating_numerator  \\\n",
       "1853  https://twitter.com/dog_rates/status/675706639...                10   \n",
       "1955  https://twitter.com/dog_rates/status/673636718...                10   \n",
       "2034  https://twitter.com/dog_rates/status/671743150...                11   \n",
       "2066  https://twitter.com/dog_rates/status/671147085...                 9   \n",
       "2116  https://twitter.com/dog_rates/status/670427002...                 9   \n",
       "\n",
       "      rating_denominator name doggo floofer pupper puppo  \n",
       "1853                  10    a  None    None   None  None  \n",
       "1955                  10    a  None    None   None  None  \n",
       "2034                  10    a  None    None   None  None  \n",
       "2066                  10    a  None    None   None  None  \n",
       "2116                  10    a  None    None   None  None  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.loc[(df1['name'].str.islower()) & (df1['text'].str.contains('named'))].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>tweet_id</th>\n",
       "      <th>in_reply_to_status_id</th>\n",
       "      <th>in_reply_to_user_id</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>source</th>\n",
       "      <th>text</th>\n",
       "      <th>retweeted_status_id</th>\n",
       "      <th>retweeted_status_user_id</th>\n",
       "      <th>retweeted_status_timestamp</th>\n",
       "      <th>expanded_urls</th>\n",
       "      <th>rating_numerator</th>\n",
       "      <th>rating_denominator</th>\n",
       "      <th>name</th>\n",
       "      <th>doggo</th>\n",
       "      <th>floofer</th>\n",
       "      <th>pupper</th>\n",
       "      <th>puppo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>262</th>\n",
       "      <td>842765311967449089</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-03-17 15:51:22 +0000</td>\n",
       "      <td>&lt;a href=\"http://twitter.com/download/iphone\" r...</td>\n",
       "      <td>Meet Indie. She's not a fan of baths but she's...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.gofundme.com/get-indie-home/,https...</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>Indie</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>273</th>\n",
       "      <td>840728873075638272</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-03-12 00:59:17 +0000</td>\n",
       "      <td>&lt;a href=\"http://twitter.com/download/iphone\" r...</td>\n",
       "      <td>RT @dog_rates: This is Pipsy. He is a fluffbal...</td>\n",
       "      <td>6.671522e+17</td>\n",
       "      <td>4.196984e+09</td>\n",
       "      <td>2015-11-19 01:27:25 +0000</td>\n",
       "      <td>https://twitter.com/dog_rates/status/667152164...</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>Pipsy</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320</th>\n",
       "      <td>834458053273591808</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-02-22 17:41:18 +0000</td>\n",
       "      <td>&lt;a href=\"http://twitter.com/download/iphone\" r...</td>\n",
       "      <td>Meet Chester (bottom) &amp;amp; Harold (top). They...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://twitter.com/dog_rates/status/834458053...</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>Chester</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>461</th>\n",
       "      <td>817536400337801217</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-01-07 01:00:41 +0000</td>\n",
       "      <td>&lt;a href=\"http://twitter.com/download/iphone\" r...</td>\n",
       "      <td>Say hello to Eugene &amp;amp; Patti Melt. No matte...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://twitter.com/dog_rates/status/817536400...</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>Eugene</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>485</th>\n",
       "      <td>814578408554463233</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-12-29 21:06:41 +0000</td>\n",
       "      <td>&lt;a href=\"http://twitter.com/download/iphone\" r...</td>\n",
       "      <td>RT @dog_rates: Meet Beau &amp;amp; Wilbur. Wilbur ...</td>\n",
       "      <td>6.981954e+17</td>\n",
       "      <td>4.196984e+09</td>\n",
       "      <td>2016-02-12 17:22:12 +0000</td>\n",
       "      <td>https://twitter.com/dog_rates/status/698195409...</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>Beau</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               tweet_id  in_reply_to_status_id  in_reply_to_user_id  \\\n",
       "262  842765311967449089                    NaN                  NaN   \n",
       "273  840728873075638272                    NaN                  NaN   \n",
       "320  834458053273591808                    NaN                  NaN   \n",
       "461  817536400337801217                    NaN                  NaN   \n",
       "485  814578408554463233                    NaN                  NaN   \n",
       "\n",
       "                     timestamp  \\\n",
       "262  2017-03-17 15:51:22 +0000   \n",
       "273  2017-03-12 00:59:17 +0000   \n",
       "320  2017-02-22 17:41:18 +0000   \n",
       "461  2017-01-07 01:00:41 +0000   \n",
       "485  2016-12-29 21:06:41 +0000   \n",
       "\n",
       "                                                source  \\\n",
       "262  <a href=\"http://twitter.com/download/iphone\" r...   \n",
       "273  <a href=\"http://twitter.com/download/iphone\" r...   \n",
       "320  <a href=\"http://twitter.com/download/iphone\" r...   \n",
       "461  <a href=\"http://twitter.com/download/iphone\" r...   \n",
       "485  <a href=\"http://twitter.com/download/iphone\" r...   \n",
       "\n",
       "                                                  text  retweeted_status_id  \\\n",
       "262  Meet Indie. She's not a fan of baths but she's...                  NaN   \n",
       "273  RT @dog_rates: This is Pipsy. He is a fluffbal...         6.671522e+17   \n",
       "320  Meet Chester (bottom) &amp; Harold (top). They...                  NaN   \n",
       "461  Say hello to Eugene &amp; Patti Melt. No matte...                  NaN   \n",
       "485  RT @dog_rates: Meet Beau &amp; Wilbur. Wilbur ...         6.981954e+17   \n",
       "\n",
       "     retweeted_status_user_id retweeted_status_timestamp  \\\n",
       "262                       NaN                        NaN   \n",
       "273              4.196984e+09  2015-11-19 01:27:25 +0000   \n",
       "320                       NaN                        NaN   \n",
       "461                       NaN                        NaN   \n",
       "485              4.196984e+09  2016-02-12 17:22:12 +0000   \n",
       "\n",
       "                                         expanded_urls  rating_numerator  \\\n",
       "262  https://www.gofundme.com/get-indie-home/,https...                12   \n",
       "273  https://twitter.com/dog_rates/status/667152164...                12   \n",
       "320  https://twitter.com/dog_rates/status/834458053...                12   \n",
       "461  https://twitter.com/dog_rates/status/817536400...                12   \n",
       "485  https://twitter.com/dog_rates/status/698195409...                 9   \n",
       "\n",
       "     rating_denominator     name doggo floofer pupper puppo  \n",
       "262                  10    Indie  None    None   None  None  \n",
       "273                  10    Pipsy  None    None   None  None  \n",
       "320                  10  Chester  None    None   None  None  \n",
       "461                  10   Eugene  None    None   None  None  \n",
       "485                  10     Beau  None    None   None  None  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1[df1.text.str.contains('&amp;')].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12    558\n",
       "11    464\n",
       "10    461\n",
       "13    351\n",
       "9     158\n",
       "Name: rating_numerator, dtype: int64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.rating_numerator.value_counts().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10    2333\n",
       "11       3\n",
       "50       3\n",
       "80       2\n",
       "20       2\n",
       "Name: rating_denominator, dtype: int64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.rating_denominator.value_counts().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>tweet_id</th>\n",
       "      <th>in_reply_to_status_id</th>\n",
       "      <th>in_reply_to_user_id</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>source</th>\n",
       "      <th>text</th>\n",
       "      <th>retweeted_status_id</th>\n",
       "      <th>retweeted_status_user_id</th>\n",
       "      <th>retweeted_status_timestamp</th>\n",
       "      <th>expanded_urls</th>\n",
       "      <th>rating_numerator</th>\n",
       "      <th>rating_denominator</th>\n",
       "      <th>name</th>\n",
       "      <th>doggo</th>\n",
       "      <th>floofer</th>\n",
       "      <th>pupper</th>\n",
       "      <th>puppo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>313</th>\n",
       "      <td>835246439529840640</td>\n",
       "      <td>8.352460e+17</td>\n",
       "      <td>26259576.0</td>\n",
       "      <td>2017-02-24 21:54:03 +0000</td>\n",
       "      <td>&lt;a href=\"http://twitter.com/download/iphone\" r...</td>\n",
       "      <td>@jonnysun @Lin_Manuel ok jomny I know you're e...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>960</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>342</th>\n",
       "      <td>832088576586297345</td>\n",
       "      <td>8.320875e+17</td>\n",
       "      <td>30582082.0</td>\n",
       "      <td>2017-02-16 04:45:50 +0000</td>\n",
       "      <td>&lt;a href=\"http://twitter.com/download/iphone\" r...</td>\n",
       "      <td>@docmisterio account started on 11/15/15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11</td>\n",
       "      <td>15</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>433</th>\n",
       "      <td>820690176645140481</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-01-15 17:52:40 +0000</td>\n",
       "      <td>&lt;a href=\"http://twitter.com/download/iphone\" r...</td>\n",
       "      <td>The floofs have been released I repeat the flo...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://twitter.com/dog_rates/status/820690176...</td>\n",
       "      <td>84</td>\n",
       "      <td>70</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>516</th>\n",
       "      <td>810984652412424192</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-12-19 23:06:23 +0000</td>\n",
       "      <td>&lt;a href=\"http://twitter.com/download/iphone\" r...</td>\n",
       "      <td>Meet Sam. She smiles 24/7 &amp;amp; secretly aspir...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.gofundme.com/sams-smile,https://tw...</td>\n",
       "      <td>24</td>\n",
       "      <td>7</td>\n",
       "      <td>Sam</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>784</th>\n",
       "      <td>775096608509886464</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-11 22:20:06 +0000</td>\n",
       "      <td>&lt;a href=\"http://twitter.com/download/iphone\" r...</td>\n",
       "      <td>RT @dog_rates: After so many requests, this is...</td>\n",
       "      <td>7.403732e+17</td>\n",
       "      <td>4.196984e+09</td>\n",
       "      <td>2016-06-08 02:41:38 +0000</td>\n",
       "      <td>https://twitter.com/dog_rates/status/740373189...</td>\n",
       "      <td>9</td>\n",
       "      <td>11</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               tweet_id  in_reply_to_status_id  in_reply_to_user_id  \\\n",
       "313  835246439529840640           8.352460e+17           26259576.0   \n",
       "342  832088576586297345           8.320875e+17           30582082.0   \n",
       "433  820690176645140481                    NaN                  NaN   \n",
       "516  810984652412424192                    NaN                  NaN   \n",
       "784  775096608509886464                    NaN                  NaN   \n",
       "\n",
       "                     timestamp  \\\n",
       "313  2017-02-24 21:54:03 +0000   \n",
       "342  2017-02-16 04:45:50 +0000   \n",
       "433  2017-01-15 17:52:40 +0000   \n",
       "516  2016-12-19 23:06:23 +0000   \n",
       "784  2016-09-11 22:20:06 +0000   \n",
       "\n",
       "                                                source  \\\n",
       "313  <a href=\"http://twitter.com/download/iphone\" r...   \n",
       "342  <a href=\"http://twitter.com/download/iphone\" r...   \n",
       "433  <a href=\"http://twitter.com/download/iphone\" r...   \n",
       "516  <a href=\"http://twitter.com/download/iphone\" r...   \n",
       "784  <a href=\"http://twitter.com/download/iphone\" r...   \n",
       "\n",
       "                                                  text  retweeted_status_id  \\\n",
       "313  @jonnysun @Lin_Manuel ok jomny I know you're e...                  NaN   \n",
       "342           @docmisterio account started on 11/15/15                  NaN   \n",
       "433  The floofs have been released I repeat the flo...                  NaN   \n",
       "516  Meet Sam. She smiles 24/7 &amp; secretly aspir...                  NaN   \n",
       "784  RT @dog_rates: After so many requests, this is...         7.403732e+17   \n",
       "\n",
       "     retweeted_status_user_id retweeted_status_timestamp  \\\n",
       "313                       NaN                        NaN   \n",
       "342                       NaN                        NaN   \n",
       "433                       NaN                        NaN   \n",
       "516                       NaN                        NaN   \n",
       "784              4.196984e+09  2016-06-08 02:41:38 +0000   \n",
       "\n",
       "                                         expanded_urls  rating_numerator  \\\n",
       "313                                                NaN               960   \n",
       "342                                                NaN                11   \n",
       "433  https://twitter.com/dog_rates/status/820690176...                84   \n",
       "516  https://www.gofundme.com/sams-smile,https://tw...                24   \n",
       "784  https://twitter.com/dog_rates/status/740373189...                 9   \n",
       "\n",
       "     rating_denominator  name doggo floofer pupper puppo  \n",
       "313                   0  None  None    None   None  None  \n",
       "342                  15  None  None    None   None  None  \n",
       "433                  70  None  None    None   None  None  \n",
       "516                   7   Sam  None    None   None  None  \n",
       "784                  11  None  None    None   None  None  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1[df1.rating_denominator != 10].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## df1 : WeRateDogs Twitter Dataset \n",
    "> It includes 2356 entries and 17 columns. \n",
    "\n",
    "***Quality***\n",
    "- Names column should be cleaned, there is unvalid records like a, the, an, the , very, unacceptable which is start with lowercase.\n",
    "- timestamp,retweeted_status_timestamp column type should be date instead of object.\n",
    "- text includes \"'&amp;\" instead of \"&\".\n",
    "- invalid rating_denominator (different than 10). However, I checked them manually and they are true denominators, so there is no problematic extraction from text.\n",
    "- Tweets_ids with no images however this problem will be solved when I joined with image prediction dataset. Because, I amnot expecting the prediction which dnot have any image.\n",
    "- Missing values expressed as \"none\". (name, duppo, flopper, etc.)\n",
    "- in_reply_to_status_id, in_reply_to_user_id, retweeted_status_id, retweeted_status_user_id data types should be intergs instead of float.\n",
    "- We only want original ratings (no retweets) that have images\n",
    "- From the source column, via which channel users connected to twitter. Therefore, column should be cleaned.\n",
    "- excluding any tweet that is a retweet.\n",
    "\n",
    "    \n",
    "***Tidiness***\n",
    "- joining with tables df2 and df3.\n",
    "- getting together stages in one column.\n",
    "- adding new features like gender, etc.\n",
    "\n",
    "\n",
    "## df2 : Image Prediction Dataset\n",
    "> Great dataset which has 2075 entries and 12 columns without any missing values. \n",
    "\n",
    "***Quality***\n",
    "- Missed ID's exists in the dataset compare to d1\n",
    "- Dublicated jpg_url\n",
    "- p1, p2, p3 columns should be standardized as all lowercase and \"-\" expression should be removed.\n",
    "\n",
    "\n",
    "***Tidiness***\n",
    "- joining with tables df3 and df1.\n",
    "- creating final dog prediction\n",
    "\n",
    "\n",
    "\n",
    "## df3 : Tweepy API Dataset \n",
    "> It includes 3715 entries and 11 columns. \n",
    "\n",
    "***Quality***\n",
    "- contributors, coordinates, place and geo features should be excluded due to high missing ratio.\n",
    "- 1222  number of id variable is dublicated\n",
    "- id=666337882303524864 exits 4 times in the dataset with same results.\n",
    "- lang indicates that the language of tweet. I wondered how \"tl\" lang is texted. Then, I realized id=668967877119254528 is problematic input.\n",
    "\n",
    "\n",
    "***Tidiness***\n",
    "- joining with tables df2 and df1.\n",
    "- favorited, retweeted columns includes always false inputs, therefore it should be excluded."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='cleaning'></a>\n",
    "## Cleaning Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this section, I am going to define the problem which are listed end of assesing data section, code the solution then test the code sequentialy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1_clean = df1.copy()\n",
    "df2_clean = df2.copy()\n",
    "df3_clean = df3.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Define (1)***\n",
    "\n",
    "Excluding any tweet that is a retweet from df1 dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Code ***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1_clean.drop(df1_clean[df1_clean['retweeted_status_id'].notnull()== True].index,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Test***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Series([], Name: retweeted_status_id, dtype: int64)"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1_clean.retweeted_status_id.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1275   NaN\n",
       "1177   NaN\n",
       "2038   NaN\n",
       "324    NaN\n",
       "29     NaN\n",
       "Name: retweeted_status_id, dtype: float64"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1_clean.retweeted_status_id.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Define (2)***\n",
    "\n",
    "Cleaning all duplicated id's from df3 and duplicated urls from df2."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Code***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1_clean = df1_clean.drop_duplicates('tweet_id', keep = 'last')\n",
    "df3_clean = df3_clean.drop_duplicates('id', keep = 'last')\n",
    "df2_clean = df2_clean.drop_duplicates('jpg_url', keep = 'last')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Test***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2356\n",
      "2175\n"
     ]
    }
   ],
   "source": [
    "print(len(df1)) \n",
    "print(len(df1_clean)) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3715\n",
      "2343\n"
     ]
    }
   ],
   "source": [
    "print(len(df3)) \n",
    "print(len(df3_clean)) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2075\n",
      "2009\n"
     ]
    }
   ],
   "source": [
    "print(len(df2)) \n",
    "print(len(df2_clean)) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Define (3)***\n",
    "\n",
    "Joining all tables which are almost completed their important quality issues (duplication, retweets, etc.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Code***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all  = pd.merge(left=df1_clean, right=df2_clean, left_on='tweet_id', right_on='tweet_id', how='left')\n",
    "df_all  = pd.merge(left=df3_clean, right=df_all, left_on='id', right_on='tweet_id', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Test***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2343 entries, 0 to 2342\n",
      "Data columns (total 39 columns):\n",
      "id                            2343 non-null int64\n",
      "retweet_count                 2343 non-null int64\n",
      "favorite_count                2343 non-null int64\n",
      "retweeted                     2343 non-null bool\n",
      "lang                          2343 non-null object\n",
      "created_at                    2343 non-null object\n",
      "contributors                  0 non-null object\n",
      "favorited                     2343 non-null bool\n",
      "coordinates                   0 non-null object\n",
      "place                         1 non-null object\n",
      "geo                           0 non-null object\n",
      "tweet_id                      2174 non-null float64\n",
      "in_reply_to_status_id         78 non-null float64\n",
      "in_reply_to_user_id           78 non-null float64\n",
      "timestamp                     2174 non-null object\n",
      "source                        2174 non-null object\n",
      "text                          2174 non-null object\n",
      "retweeted_status_id           0 non-null float64\n",
      "retweeted_status_user_id      0 non-null float64\n",
      "retweeted_status_timestamp    0 non-null object\n",
      "expanded_urls                 2116 non-null object\n",
      "rating_numerator              2174 non-null float64\n",
      "rating_denominator            2174 non-null float64\n",
      "name                          2174 non-null object\n",
      "doggo                         2174 non-null object\n",
      "floofer                       2174 non-null object\n",
      "pupper                        2174 non-null object\n",
      "puppo                         2174 non-null object\n",
      "jpg_url                       1927 non-null object\n",
      "img_num                       1927 non-null float64\n",
      "p1                            1927 non-null object\n",
      "p1_conf                       1927 non-null float64\n",
      "p1_dog                        1927 non-null object\n",
      "p2                            1927 non-null object\n",
      "p2_conf                       1927 non-null float64\n",
      "p2_dog                        1927 non-null object\n",
      "p3                            1927 non-null object\n",
      "p3_conf                       1927 non-null float64\n",
      "p3_dog                        1927 non-null object\n",
      "dtypes: bool(2), float64(11), int64(3), object(23)\n",
      "memory usage: 700.2+ KB\n"
     ]
    }
   ],
   "source": [
    "df_all.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Define (4)***\n",
    "\n",
    "Drop uncessary columns and make unique "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Code***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all  = df_all.drop(['id', 'retweeted_status_id', 'retweeted_status_user_id', \n",
    "                       'retweeted_status_timestamp','in_reply_to_status_id',\n",
    "                       'in_reply_to_user_id','favorited', 'retweeted','contributors', 'coordinates',\n",
    "                      'place', 'geo'], axis=1)\n",
    "\n",
    "df_all = df_all.drop_duplicates('tweet_id', keep = 'last')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Test***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2175 entries, 0 to 2342\n",
      "Data columns (total 27 columns):\n",
      "retweet_count         2175 non-null int64\n",
      "favorite_count        2175 non-null int64\n",
      "lang                  2175 non-null object\n",
      "created_at            2175 non-null object\n",
      "tweet_id              2174 non-null float64\n",
      "timestamp             2174 non-null object\n",
      "source                2174 non-null object\n",
      "text                  2174 non-null object\n",
      "expanded_urls         2116 non-null object\n",
      "rating_numerator      2174 non-null float64\n",
      "rating_denominator    2174 non-null float64\n",
      "name                  2174 non-null object\n",
      "doggo                 2174 non-null object\n",
      "floofer               2174 non-null object\n",
      "pupper                2174 non-null object\n",
      "puppo                 2174 non-null object\n",
      "jpg_url               1927 non-null object\n",
      "img_num               1927 non-null float64\n",
      "p1                    1927 non-null object\n",
      "p1_conf               1927 non-null float64\n",
      "p1_dog                1927 non-null object\n",
      "p2                    1927 non-null object\n",
      "p2_conf               1927 non-null float64\n",
      "p2_dog                1927 non-null object\n",
      "p3                    1927 non-null object\n",
      "p3_conf               1927 non-null float64\n",
      "p3_dog                1927 non-null object\n",
      "dtypes: float64(7), int64(2), object(18)\n",
      "memory usage: 475.8+ KB\n"
     ]
    }
   ],
   "source": [
    "df_all.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Define (5)***\n",
    "\n",
    "Creating final prediction of image prediction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "p_final = []\n",
    "p_final_conf = []\n",
    "\n",
    "def final_prediction(table) :\n",
    "    if table['p1_dog'] == True:\n",
    "        p_final.append(table['p1'])\n",
    "        p_final_conf.append(table['p1_conf'])\n",
    "    elif table['p2_dog'] == True:\n",
    "        p_final.append(table['p2'])\n",
    "        p_final_conf.append(table['p2_conf'])\n",
    "    elif table['p3_dog'] == True:\n",
    "        p_final.append(table['p3'])\n",
    "        p_final_conf.append(table['p3_conf'])\n",
    "    else:\n",
    "        p_final.append('NaN')\n",
    "        p_final_conf.append(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Code ***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all.apply(final_prediction, axis=1)\n",
    "df_all['final_prediction'] = p_final\n",
    "df_all['final_prediction_conf'] = p_final_conf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Test***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "NaN                   550\n",
       "golden_retriever      143\n",
       "Labrador_retriever    103\n",
       "Pembroke               94\n",
       "Chihuahua              87\n",
       "Name: final_prediction, dtype: int64"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all.final_prediction.value_counts().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.000000    550\n",
       "0.873233      2\n",
       "0.786089      2\n",
       "0.240602      2\n",
       "0.335692      1\n",
       "Name: final_prediction_conf, dtype: int64"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all.final_prediction_conf.value_counts().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Define (6)***\n",
    "\n",
    "From the source column, via which channel users connected to twitter. Therefore, column should be cleaned."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Code***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all['source'] = df_all['source'].astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all['source'] = df_all['source'].apply(lambda x: re.findall(r'>(.*)<', x)[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Test***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Twitter for iPhone     2041\n",
       "Vine - Make a Scene      91\n",
       "Twitter Web Client       31\n",
       "TweetDeck                11\n",
       "Name: source, dtype: int64"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all['source'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Define (7)***\n",
    "\n",
    "Names column should be cleaned, there is unvalid records like a, the, an, the , very, unacceptable which is start with lowercase."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Code***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all['text'] = df_all['text'].astype(object)\n",
    "\n",
    "new_dog_names = []\n",
    "\n",
    "for line in df_all.text:\n",
    "    # Start with 'This is ' and the fisrt letter of the name is uppercase\n",
    "    if str(line).startswith('This is ') and re.match(r'[A-Z].*', line.split()[2]):\n",
    "        new_dog_names.append(line.split()[2].strip(',').strip('.'))\n",
    "    # Start with 'Meet ' and the fisrt letter of the name is uppercase\n",
    "    elif str(line).startswith('Meet ') and re.match(r'[A-Z].*', line.split()[1]):\n",
    "        new_dog_names.append(line.split()[1].strip(',').strip('.'))\n",
    "    # Start with 'Say hello to ' and the fisrt letter of the name is uppercase\n",
    "    elif str(line).startswith('Say hello to ') and re.match(r'[A-Z].*', line.split()[3]):\n",
    "        new_dog_names.append(line.split()[3].strip(',').strip('.'))\n",
    "    # Start with 'Here we have ' and the fisrt letter of the name is uppercase\n",
    "    elif str(line).startswith('Here we have ') and re.match(r'[A-Z].*', line.split()[3]):\n",
    "        new_dog_names.append(line.split()[3].strip(',').strip('.'))\n",
    "    # Contain 'named' and the fisrt letter of the name is uppercase\n",
    "    elif 'named' in str(line) and re.match(r'[A-Z].*', line.split()[line.split().index('named') + 1]):\n",
    "        new_dog_names.append(line.split()[line.split().index('named') + 1].strip(',').strip('.'))\n",
    "    # No name specified or other style\n",
    "    else:\n",
    "        new_dog_names.append('NaN')\n",
    "        \n",
    "df_all['new_dog_names'] = new_dog_names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Test***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "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>new_dog_names</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>365</th>\n",
       "      <td>Beebop</td>\n",
       "      <td>Beebop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>725</th>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2032</th>\n",
       "      <td>Jax</td>\n",
       "      <td>Jax</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2221</th>\n",
       "      <td>Skittles</td>\n",
       "      <td>Skittles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>542</th>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2143</th>\n",
       "      <td>Skye</td>\n",
       "      <td>Skye</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>271</th>\n",
       "      <td>Monty</td>\n",
       "      <td>Monty</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1266</th>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>305</th>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>970</th>\n",
       "      <td>George</td>\n",
       "      <td>George</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     new_dog_names      name\n",
       "365         Beebop    Beebop\n",
       "725            NaN      None\n",
       "2032           Jax       Jax\n",
       "2221      Skittles  Skittles\n",
       "542            NaN      None\n",
       "2143          Skye      Skye\n",
       "271          Monty     Monty\n",
       "1266           NaN      None\n",
       "305            NaN      None\n",
       "970         George    George"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all[['new_dog_names', 'name']].sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Define (8)***\n",
    "\n",
    "Creating gender feature"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Code***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "male = ['He', 'he', 'him', 'his', \"he's\", 'himself']\n",
    "female = ['She', 'she', 'her', 'hers', 'herself', \"she's\"]\n",
    "\n",
    "dog_gender = []\n",
    "\n",
    "for text in df_all['text']:\n",
    "    # Male\n",
    "    if any(map(lambda v:v in male, str(text).split())):\n",
    "        dog_gender.append('male')\n",
    "    # Female\n",
    "    elif any(map(lambda v:v in female, str(text).split())):\n",
    "        dog_gender.append('female')\n",
    "    # If group or not specified\n",
    "    else:\n",
    "        dog_gender.append('NaN')\n",
    "        \n",
    "df_all['dog_gender'] = dog_gender"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Test***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "NaN       1275\n",
       "male       667\n",
       "female     233\n",
       "Name: dog_gender, dtype: int64"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all.dog_gender.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Define (9)***\n",
    "\n",
    "Converting null values to None correcting data types and removing null value on tweet_id coloumn."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Code***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2175 entries, 0 to 2342\n",
      "Data columns (total 31 columns):\n",
      "retweet_count            2175 non-null int64\n",
      "favorite_count           2175 non-null int64\n",
      "lang                     2175 non-null object\n",
      "created_at               2175 non-null object\n",
      "tweet_id                 2174 non-null float64\n",
      "timestamp                2174 non-null object\n",
      "source                   2174 non-null category\n",
      "text                     2174 non-null object\n",
      "expanded_urls            2116 non-null object\n",
      "rating_numerator         2174 non-null float64\n",
      "rating_denominator       2174 non-null float64\n",
      "name                     2174 non-null object\n",
      "doggo                    2174 non-null object\n",
      "floofer                  2174 non-null object\n",
      "pupper                   2174 non-null object\n",
      "puppo                    2174 non-null object\n",
      "jpg_url                  1927 non-null object\n",
      "img_num                  1927 non-null float64\n",
      "p1                       1927 non-null object\n",
      "p1_conf                  1927 non-null float64\n",
      "p1_dog                   1927 non-null object\n",
      "p2                       1927 non-null object\n",
      "p2_conf                  1927 non-null float64\n",
      "p2_dog                   1927 non-null object\n",
      "p3                       1927 non-null object\n",
      "p3_conf                  1927 non-null float64\n",
      "p3_dog                   1927 non-null object\n",
      "final_prediction         2175 non-null object\n",
      "final_prediction_conf    2175 non-null float64\n",
      "new_dog_names            2175 non-null object\n",
      "dog_gender               2175 non-null object\n",
      "dtypes: category(1), float64(8), int64(2), object(20)\n",
      "memory usage: 529.1+ KB\n"
     ]
    }
   ],
   "source": [
    "df_all.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all = df_all.dropna(subset = ['tweet_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all.loc[df_all['jpg_url'] == 'NaN', 'jpg_url'] = None\n",
    "df_all.loc[df_all['expanded_urls'] == 'NaN', 'expanded_urls'] = None\n",
    "df_all.loc[df_all['p1'] == 'NaN', 'p1'] = None\n",
    "df_all.loc[df_all['p2'] == 'NaN', 'p2'] = None\n",
    "df_all.loc[df_all['p3'] == 'NaN', 'p3'] = None\n",
    "df_all.loc[df_all['final_prediction'] == 'NaN', 'final_prediction'] = None\n",
    "df_all.loc[df_all['p1_dog'] == 'NaN', 'p1_dog'] = None\n",
    "df_all.loc[df_all['p2_dog'] == 'NaN', 'p2_dog'] = None\n",
    "df_all.loc[df_all['p3_dog'] == 'NaN', 'p3_dog'] = None\n",
    "\n",
    "\n",
    "df_all['timestamp'] = pd.to_datetime(df_all.timestamp)\n",
    "df_all['source'] = df_all['source'].astype('category')\n",
    "df_all['rating_numerator'] = df_all['rating_numerator'].astype(float)\n",
    "df_all['rating_denominator'] = df_all['rating_denominator'].astype(float)\n",
    "df_all['dog_gender'] = df_all['dog_gender'].astype('category')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Test***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2174 entries, 0 to 2342\n",
      "Data columns (total 31 columns):\n",
      "retweet_count            2174 non-null int64\n",
      "favorite_count           2174 non-null int64\n",
      "lang                     2174 non-null object\n",
      "created_at               2174 non-null object\n",
      "tweet_id                 2174 non-null float64\n",
      "timestamp                2174 non-null datetime64[ns]\n",
      "source                   2174 non-null category\n",
      "text                     2174 non-null object\n",
      "expanded_urls            2116 non-null object\n",
      "rating_numerator         2174 non-null float64\n",
      "rating_denominator       2174 non-null float64\n",
      "name                     2174 non-null object\n",
      "doggo                    2174 non-null object\n",
      "floofer                  2174 non-null object\n",
      "pupper                   2174 non-null object\n",
      "puppo                    2174 non-null object\n",
      "jpg_url                  1927 non-null object\n",
      "img_num                  1927 non-null float64\n",
      "p1                       1927 non-null object\n",
      "p1_conf                  1927 non-null float64\n",
      "p1_dog                   1927 non-null object\n",
      "p2                       1927 non-null object\n",
      "p2_conf                  1927 non-null float64\n",
      "p2_dog                   1927 non-null object\n",
      "p3                       1927 non-null object\n",
      "p3_conf                  1927 non-null float64\n",
      "p3_dog                   1927 non-null object\n",
      "final_prediction         1625 non-null object\n",
      "final_prediction_conf    2174 non-null float64\n",
      "new_dog_names            2174 non-null object\n",
      "dog_gender               2174 non-null category\n",
      "dtypes: category(2), datetime64[ns](1), float64(8), int64(2), object(18)\n",
      "memory usage: 514.1+ KB\n"
     ]
    }
   ],
   "source": [
    "df_all.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Define (10)***\n",
    "\n",
    "Creating date and time from timestamp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Code***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all['date'] = df_all['timestamp'].apply(lambda time: time.strftime('%m-%d-%Y'))\n",
    "df_all['time'] = df_all['timestamp'].apply(lambda time: time.strftime('%H:%M'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Test***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "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>time</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1075</th>\n",
       "      <td>02:00</td>\n",
       "      <td>06-01-2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>16:25</td>\n",
       "      <td>07-27-2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1381</th>\n",
       "      <td>17:01</td>\n",
       "      <td>02-17-2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2238</th>\n",
       "      <td>20:47</td>\n",
       "      <td>11-20-2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2209</th>\n",
       "      <td>16:31</td>\n",
       "      <td>11-22-2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>722</th>\n",
       "      <td>16:03</td>\n",
       "      <td>09-29-2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2134</th>\n",
       "      <td>05:42</td>\n",
       "      <td>11-26-2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1418</th>\n",
       "      <td>20:23</td>\n",
       "      <td>02-10-2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2048</th>\n",
       "      <td>03:18</td>\n",
       "      <td>11-30-2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>568</th>\n",
       "      <td>17:23</td>\n",
       "      <td>11-21-2016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       time        date\n",
       "1075  02:00  06-01-2016\n",
       "8     16:25  07-27-2017\n",
       "1381  17:01  02-17-2016\n",
       "2238  20:47  11-20-2015\n",
       "2209  16:31  11-22-2015\n",
       "722   16:03  09-29-2016\n",
       "2134  05:42  11-26-2015\n",
       "1418  20:23  02-10-2016\n",
       "2048  03:18  11-30-2015\n",
       "568   17:23  11-21-2016"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all[['time', 'date']].sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Define (11)***\n",
    "\n",
    "Creating a stage coloumn combining dog stages features."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Code ***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all['stage'] = df_all[['doggo', 'floofer','pupper','puppo']].apply(lambda x: ''.join(x), axis=1)\n",
    "\n",
    "df_all['stage'].replace(\"NoneNoneNoneNone\",\"None \", inplace=True)\n",
    "df_all['stage'].replace(\"doggoNoneNoneNone\",\"doggo\", inplace=True)\n",
    "df_all['stage'].replace(\"NoneflooferNoneNone\",\"floofer\", inplace=True)\n",
    "df_all['stage'].replace(\"NoneNonepupperNone\",\"pupper\", inplace=True)\n",
    "df_all['stage'].replace(\"NoneNoneNonepuppo\",\"puppo\", inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Test ***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "None                    1830\n",
       "pupper                   224\n",
       "doggo                     75\n",
       "puppo                     24\n",
       "doggoNonepupperNone       10\n",
       "floofer                    9\n",
       "doggoNoneNonepuppo         1\n",
       "doggoflooferNoneNone       1\n",
       "Name: stage, dtype: int64"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all['stage'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Define (12)***\n",
    "\n",
    "Replacing underscore with space."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Code ***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all['p1'] = df_all['p1'].str.replace('_', ' ')\n",
    "df_all['p1'] = df_all['p1'].map(lambda x: x if type(x)!=str else x.lower())\n",
    "df_all['p2'] = df_all['p2'].str.replace('_', ' ')\n",
    "df_all['p2'] = df_all['p2'].map(lambda x: x if type(x)!=str else x.lower())\n",
    "df_all['p3'] = df_all['p3'].str.replace('_', ' ')\n",
    "df_all['p3'] = df_all['p3'].map(lambda x: x if type(x)!=str else x.lower())\n",
    "df_all['final_prediction'] = df_all['final_prediction'].str.replace('_', ' ')\n",
    "df_all['final_prediction'] = df_all['final_prediction'].map(lambda x: x if type(x)!=str else x.lower())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Test ***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "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>p1</th>\n",
       "      <th>p2</th>\n",
       "      <th>p3</th>\n",
       "      <th>final_prediction</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>846</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1874</th>\n",
       "      <td>miniature poodle</td>\n",
       "      <td>toy poodle</td>\n",
       "      <td>maltese dog</td>\n",
       "      <td>miniature poodle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>720</th>\n",
       "      <td>pembroke</td>\n",
       "      <td>golden retriever</td>\n",
       "      <td>collie</td>\n",
       "      <td>pembroke</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>890</th>\n",
       "      <td>labrador retriever</td>\n",
       "      <td>chihuahua</td>\n",
       "      <td>american staffordshire terrier</td>\n",
       "      <td>labrador retriever</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>717</th>\n",
       "      <td>briard</td>\n",
       "      <td>soft-coated wheaten terrier</td>\n",
       "      <td>lhasa</td>\n",
       "      <td>briard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>842</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>689</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1413</th>\n",
       "      <td>washbasin</td>\n",
       "      <td>paper towel</td>\n",
       "      <td>toilet tissue</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1197</th>\n",
       "      <td>golden retriever</td>\n",
       "      <td>labrador retriever</td>\n",
       "      <td>chow</td>\n",
       "      <td>golden retriever</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1488</th>\n",
       "      <td>toy poodle</td>\n",
       "      <td>teddy</td>\n",
       "      <td>miniature poodle</td>\n",
       "      <td>toy poodle</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      p1                           p2  \\\n",
       "846                  NaN                          NaN   \n",
       "1874    miniature poodle                   toy poodle   \n",
       "720             pembroke             golden retriever   \n",
       "890   labrador retriever                    chihuahua   \n",
       "717               briard  soft-coated wheaten terrier   \n",
       "842                  NaN                          NaN   \n",
       "689                  NaN                          NaN   \n",
       "1413           washbasin                  paper towel   \n",
       "1197    golden retriever           labrador retriever   \n",
       "1488          toy poodle                        teddy   \n",
       "\n",
       "                                  p3    final_prediction  \n",
       "846                              NaN                None  \n",
       "1874                     maltese dog    miniature poodle  \n",
       "720                           collie            pembroke  \n",
       "890   american staffordshire terrier  labrador retriever  \n",
       "717                            lhasa              briard  \n",
       "842                              NaN                None  \n",
       "689                              NaN                None  \n",
       "1413                   toilet tissue                None  \n",
       "1197                            chow    golden retriever  \n",
       "1488                miniature poodle          toy poodle  "
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all[['p1','p2','p3','final_prediction']].sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2174 entries, 0 to 2342\n",
      "Data columns (total 34 columns):\n",
      "retweet_count            2174 non-null int64\n",
      "favorite_count           2174 non-null int64\n",
      "lang                     2174 non-null object\n",
      "created_at               2174 non-null object\n",
      "tweet_id                 2174 non-null float64\n",
      "timestamp                2174 non-null datetime64[ns]\n",
      "source                   2174 non-null category\n",
      "text                     2174 non-null object\n",
      "expanded_urls            2116 non-null object\n",
      "rating_numerator         2174 non-null float64\n",
      "rating_denominator       2174 non-null float64\n",
      "name                     2174 non-null object\n",
      "doggo                    2174 non-null object\n",
      "floofer                  2174 non-null object\n",
      "pupper                   2174 non-null object\n",
      "puppo                    2174 non-null object\n",
      "jpg_url                  1927 non-null object\n",
      "img_num                  1927 non-null float64\n",
      "p1                       1927 non-null object\n",
      "p1_conf                  1927 non-null float64\n",
      "p1_dog                   1927 non-null object\n",
      "p2                       1927 non-null object\n",
      "p2_conf                  1927 non-null float64\n",
      "p2_dog                   1927 non-null object\n",
      "p3                       1927 non-null object\n",
      "p3_conf                  1927 non-null float64\n",
      "p3_dog                   1927 non-null object\n",
      "final_prediction         1625 non-null object\n",
      "final_prediction_conf    2174 non-null float64\n",
      "new_dog_names            2174 non-null object\n",
      "dog_gender               2174 non-null category\n",
      "date                     2174 non-null object\n",
      "time                     2174 non-null object\n",
      "stage                    2174 non-null object\n",
      "dtypes: category(2), datetime64[ns](1), float64(8), int64(2), object(21)\n",
      "memory usage: 565.0+ KB\n"
     ]
    }
   ],
   "source": [
    "df_all.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Define (13)***\n",
    "\n",
    "Excluding the null values from final_prediction dataset because I will use final_prediction feature as predictive variable."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Code***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "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>p1</th>\n",
       "      <th>p1_conf</th>\n",
       "      <th>p1_dog</th>\n",
       "      <th>p2</th>\n",
       "      <th>p2_conf</th>\n",
       "      <th>p2_dog</th>\n",
       "      <th>p3</th>\n",
       "      <th>p3_conf</th>\n",
       "      <th>p3_dog</th>\n",
       "      <th>final_prediction</th>\n",
       "      <th>final_prediction_conf</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1253</th>\n",
       "      <td>chihuahua</td>\n",
       "      <td>0.945629</td>\n",
       "      <td>True</td>\n",
       "      <td>pomeranian</td>\n",
       "      <td>0.019204</td>\n",
       "      <td>True</td>\n",
       "      <td>west highland white terrier</td>\n",
       "      <td>0.010134</td>\n",
       "      <td>True</td>\n",
       "      <td>chihuahua</td>\n",
       "      <td>0.945629</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>greater swiss mountain dog</td>\n",
       "      <td>0.622752</td>\n",
       "      <td>True</td>\n",
       "      <td>appenzeller</td>\n",
       "      <td>0.158463</td>\n",
       "      <td>True</td>\n",
       "      <td>entlebucher</td>\n",
       "      <td>0.148115</td>\n",
       "      <td>True</td>\n",
       "      <td>greater swiss mountain dog</td>\n",
       "      <td>0.622752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2248</th>\n",
       "      <td>electric fan</td>\n",
       "      <td>0.984377</td>\n",
       "      <td>False</td>\n",
       "      <td>spotlight</td>\n",
       "      <td>0.007737</td>\n",
       "      <td>False</td>\n",
       "      <td>lampshade</td>\n",
       "      <td>0.001901</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1659</th>\n",
       "      <td>curly-coated retriever</td>\n",
       "      <td>0.482288</td>\n",
       "      <td>True</td>\n",
       "      <td>flat-coated retriever</td>\n",
       "      <td>0.315286</td>\n",
       "      <td>True</td>\n",
       "      <td>great dane</td>\n",
       "      <td>0.062179</td>\n",
       "      <td>True</td>\n",
       "      <td>curly-coated retriever</td>\n",
       "      <td>0.482288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1465</th>\n",
       "      <td>dining table</td>\n",
       "      <td>0.383448</td>\n",
       "      <td>False</td>\n",
       "      <td>grey fox</td>\n",
       "      <td>0.103191</td>\n",
       "      <td>False</td>\n",
       "      <td>siamese cat</td>\n",
       "      <td>0.098256</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>677</th>\n",
       "      <td>beach wagon</td>\n",
       "      <td>0.362925</td>\n",
       "      <td>False</td>\n",
       "      <td>minivan</td>\n",
       "      <td>0.304759</td>\n",
       "      <td>False</td>\n",
       "      <td>limousine</td>\n",
       "      <td>0.101702</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1089</th>\n",
       "      <td>studio couch</td>\n",
       "      <td>0.944692</td>\n",
       "      <td>False</td>\n",
       "      <td>four-poster</td>\n",
       "      <td>0.007942</td>\n",
       "      <td>False</td>\n",
       "      <td>quilt</td>\n",
       "      <td>0.006302</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1470</th>\n",
       "      <td>golden retriever</td>\n",
       "      <td>0.989333</td>\n",
       "      <td>True</td>\n",
       "      <td>labrador retriever</td>\n",
       "      <td>0.007946</td>\n",
       "      <td>True</td>\n",
       "      <td>kuvasz</td>\n",
       "      <td>0.000749</td>\n",
       "      <td>True</td>\n",
       "      <td>golden retriever</td>\n",
       "      <td>0.989333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>547</th>\n",
       "      <td>labrador retriever</td>\n",
       "      <td>0.372776</td>\n",
       "      <td>True</td>\n",
       "      <td>golden retriever</td>\n",
       "      <td>0.343666</td>\n",
       "      <td>True</td>\n",
       "      <td>great pyrenees</td>\n",
       "      <td>0.067242</td>\n",
       "      <td>True</td>\n",
       "      <td>labrador retriever</td>\n",
       "      <td>0.372776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1293</th>\n",
       "      <td>golden retriever</td>\n",
       "      <td>0.637225</td>\n",
       "      <td>True</td>\n",
       "      <td>bloodhound</td>\n",
       "      <td>0.094542</td>\n",
       "      <td>True</td>\n",
       "      <td>cocker spaniel</td>\n",
       "      <td>0.069797</td>\n",
       "      <td>True</td>\n",
       "      <td>golden retriever</td>\n",
       "      <td>0.637225</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              p1   p1_conf p1_dog                     p2  \\\n",
       "1253                   chihuahua  0.945629   True             pomeranian   \n",
       "114   greater swiss mountain dog  0.622752   True            appenzeller   \n",
       "2248                electric fan  0.984377  False              spotlight   \n",
       "1659      curly-coated retriever  0.482288   True  flat-coated retriever   \n",
       "1465                dining table  0.383448  False               grey fox   \n",
       "677                  beach wagon  0.362925  False                minivan   \n",
       "1089                studio couch  0.944692  False            four-poster   \n",
       "1470            golden retriever  0.989333   True     labrador retriever   \n",
       "547           labrador retriever  0.372776   True       golden retriever   \n",
       "1293            golden retriever  0.637225   True             bloodhound   \n",
       "\n",
       "       p2_conf p2_dog                           p3   p3_conf p3_dog  \\\n",
       "1253  0.019204   True  west highland white terrier  0.010134   True   \n",
       "114   0.158463   True                  entlebucher  0.148115   True   \n",
       "2248  0.007737  False                    lampshade  0.001901  False   \n",
       "1659  0.315286   True                   great dane  0.062179   True   \n",
       "1465  0.103191  False                  siamese cat  0.098256  False   \n",
       "677   0.304759  False                    limousine  0.101702  False   \n",
       "1089  0.007942  False                        quilt  0.006302  False   \n",
       "1470  0.007946   True                       kuvasz  0.000749   True   \n",
       "547   0.343666   True               great pyrenees  0.067242   True   \n",
       "1293  0.094542   True               cocker spaniel  0.069797   True   \n",
       "\n",
       "                final_prediction  final_prediction_conf  \n",
       "1253                   chihuahua               0.945629  \n",
       "114   greater swiss mountain dog               0.622752  \n",
       "2248                        None               0.000000  \n",
       "1659      curly-coated retriever               0.482288  \n",
       "1465                        None               0.000000  \n",
       "677                         None               0.000000  \n",
       "1089                        None               0.000000  \n",
       "1470            golden retriever               0.989333  \n",
       "547           labrador retriever               0.372776  \n",
       "1293            golden retriever               0.637225  "
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all[[\"p1\",\"p1_conf\", \"p1_dog\",\"p2\",\"p2_conf\", \"p2_dog\",\"p3\",\"p3_conf\",  \"p3_dog\",\"final_prediction\", \"final_prediction_conf\"]].sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all = df_all.dropna(subset = ['final_prediction'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Test***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1625 entries, 1 to 2342\n",
      "Data columns (total 34 columns):\n",
      "retweet_count            1625 non-null int64\n",
      "favorite_count           1625 non-null int64\n",
      "lang                     1625 non-null object\n",
      "created_at               1625 non-null object\n",
      "tweet_id                 1625 non-null float64\n",
      "timestamp                1625 non-null datetime64[ns]\n",
      "source                   1625 non-null category\n",
      "text                     1625 non-null object\n",
      "expanded_urls            1625 non-null object\n",
      "rating_numerator         1625 non-null float64\n",
      "rating_denominator       1625 non-null float64\n",
      "name                     1625 non-null object\n",
      "doggo                    1625 non-null object\n",
      "floofer                  1625 non-null object\n",
      "pupper                   1625 non-null object\n",
      "puppo                    1625 non-null object\n",
      "jpg_url                  1625 non-null object\n",
      "img_num                  1625 non-null float64\n",
      "p1                       1625 non-null object\n",
      "p1_conf                  1625 non-null float64\n",
      "p1_dog                   1625 non-null object\n",
      "p2                       1625 non-null object\n",
      "p2_conf                  1625 non-null float64\n",
      "p2_dog                   1625 non-null object\n",
      "p3                       1625 non-null object\n",
      "p3_conf                  1625 non-null float64\n",
      "p3_dog                   1625 non-null object\n",
      "final_prediction         1625 non-null object\n",
      "final_prediction_conf    1625 non-null float64\n",
      "new_dog_names            1625 non-null object\n",
      "dog_gender               1625 non-null category\n",
      "date                     1625 non-null object\n",
      "time                     1625 non-null object\n",
      "stage                    1625 non-null object\n",
      "dtypes: category(2), datetime64[ns](1), float64(8), int64(2), object(21)\n",
      "memory usage: 422.4+ KB\n"
     ]
    }
   ],
   "source": [
    "df_all.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Define (14)***\n",
    "\n",
    "Cleaning rating numerators"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Code ***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "ratings = df_all.text.str.extract('((?:\\d+\\.)?\\d+)\\/(\\d+)', expand=True)\n",
    "df_all['new_numerator'] = ratings[0]\n",
    "df_all['new_denominator'] = ratings[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*** Test ***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "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>retweet_count</th>\n",
       "      <th>favorite_count</th>\n",
       "      <th>lang</th>\n",
       "      <th>created_at</th>\n",
       "      <th>tweet_id</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>source</th>\n",
       "      <th>text</th>\n",
       "      <th>expanded_urls</th>\n",
       "      <th>rating_numerator</th>\n",
       "      <th>...</th>\n",
       "      <th>p3_dog</th>\n",
       "      <th>final_prediction</th>\n",
       "      <th>final_prediction_conf</th>\n",
       "      <th>new_dog_names</th>\n",
       "      <th>dog_gender</th>\n",
       "      <th>date</th>\n",
       "      <th>time</th>\n",
       "      <th>stage</th>\n",
       "      <th>new_numerator</th>\n",
       "      <th>new_denominator</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>685</th>\n",
       "      <td>6814</td>\n",
       "      <td>19796</td>\n",
       "      <td>en</td>\n",
       "      <td>Thu Oct 13 23:23:56 +0000 2016</td>\n",
       "      <td>7.867091e+17</td>\n",
       "      <td>2016-10-13 23:23:56</td>\n",
       "      <td>Twitter for iPhone</td>\n",
       "      <td>This is Logan, the Chow who lived. He solemnly...</td>\n",
       "      <td>https://twitter.com/dog_rates/status/786709082...</td>\n",
       "      <td>75.0</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>pomeranian</td>\n",
       "      <td>0.467321</td>\n",
       "      <td>Logan</td>\n",
       "      <td>male</td>\n",
       "      <td>10-13-2016</td>\n",
       "      <td>23:23</td>\n",
       "      <td>None</td>\n",
       "      <td>9.75</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 36 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     retweet_count  favorite_count lang                      created_at  \\\n",
       "685           6814           19796   en  Thu Oct 13 23:23:56 +0000 2016   \n",
       "\n",
       "         tweet_id           timestamp              source  \\\n",
       "685  7.867091e+17 2016-10-13 23:23:56  Twitter for iPhone   \n",
       "\n",
       "                                                  text  \\\n",
       "685  This is Logan, the Chow who lived. He solemnly...   \n",
       "\n",
       "                                         expanded_urls  rating_numerator  \\\n",
       "685  https://twitter.com/dog_rates/status/786709082...              75.0   \n",
       "\n",
       "          ...         p3_dog final_prediction final_prediction_conf  \\\n",
       "685       ...           True       pomeranian              0.467321   \n",
       "\n",
       "    new_dog_names dog_gender        date   time  stage new_numerator  \\\n",
       "685         Logan       male  10-13-2016  23:23  None           9.75   \n",
       "\n",
       "     new_denominator  \n",
       "685               10  \n",
       "\n",
       "[1 rows x 36 columns]"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all[df_all['tweet_id'] == 786709082849828864]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='storing'></a>\n",
    "## Storing/Exporting Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this section, I am going to save the cleaned for further both exploratory and predictive data analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all.to_csv('twitter_archive_master_v2.csv', index=False, encoding = 'utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1625 entries, 0 to 1624\n",
      "Data columns (total 36 columns):\n",
      "retweet_count            1625 non-null int64\n",
      "favorite_count           1625 non-null int64\n",
      "lang                     1625 non-null object\n",
      "created_at               1625 non-null object\n",
      "tweet_id                 1625 non-null float64\n",
      "timestamp                1625 non-null object\n",
      "source                   1625 non-null object\n",
      "text                     1625 non-null object\n",
      "expanded_urls            1625 non-null object\n",
      "rating_numerator         1625 non-null float64\n",
      "rating_denominator       1625 non-null float64\n",
      "name                     1625 non-null object\n",
      "doggo                    1625 non-null object\n",
      "floofer                  1625 non-null object\n",
      "pupper                   1625 non-null object\n",
      "puppo                    1625 non-null object\n",
      "jpg_url                  1625 non-null object\n",
      "img_num                  1625 non-null float64\n",
      "p1                       1625 non-null object\n",
      "p1_conf                  1625 non-null float64\n",
      "p1_dog                   1625 non-null bool\n",
      "p2                       1625 non-null object\n",
      "p2_conf                  1625 non-null float64\n",
      "p2_dog                   1625 non-null bool\n",
      "p3                       1625 non-null object\n",
      "p3_conf                  1625 non-null float64\n",
      "p3_dog                   1625 non-null bool\n",
      "final_prediction         1625 non-null object\n",
      "final_prediction_conf    1625 non-null float64\n",
      "new_dog_names            1158 non-null object\n",
      "dog_gender               727 non-null object\n",
      "date                     1625 non-null object\n",
      "time                     1625 non-null object\n",
      "stage                    1625 non-null object\n",
      "new_numerator            1625 non-null float64\n",
      "new_denominator          1625 non-null int64\n",
      "dtypes: bool(3), float64(9), int64(3), object(21)\n",
      "memory usage: 423.8+ KB\n"
     ]
    }
   ],
   "source": [
    "df_all_test = pd.read_csv('twitter_archive_master_v2.csv')\n",
    "df_all_test.info()"
   ]
  }
 ],
 "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.5.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}