social_media_engagement.ipynb

{
 "metadata": {
  "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.8.3"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3.8.3 64-bit ('base': conda)",
   "metadata": {
    "interpreter": {
     "hash": "dca0ade3e726a953b501b15e8e990130d2b7799f14cfd9f4271676035ebe5511"
    }
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pymssql\n",
    "from fuzzywuzzy import fuzz"
   ]
  },
  {
   "source": [
    "### Import Data"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('./meme_cleaning.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "      Tweet_ID                              Posted_By   Twitter_Handle  \\\n",
       "0            1                              furry_irl       rFurry_Irl   \n",
       "1            2                         SidenoteSuplex   SidenoteSuplex   \n",
       "2            3                            FunnyThings  funny_things127   \n",
       "3            4                           ????????????         chic_aya   \n",
       "4            5               Tell Tales Entertainment  TellTalesOnline   \n",
       "...        ...                                    ...              ...   \n",
       "1324      1325                  Dank Meme Dealer ????    DankRedditBot   \n",
       "1325      1326  Daily dose of memes to make you laugh     MemesUniteUs   \n",
       "1326      1327  Daily dose of memes to make you laugh     MemesUniteUs   \n",
       "1327      1328  Daily dose of memes to make you laugh     MemesUniteUs   \n",
       "1328      1329  Daily dose of memes to make you laugh     MemesUniteUs   \n",
       "\n",
       "                                                  Tweet  \\\n",
       "0     Furry_irl | https://t.co/vPgHv1R6W6 #memes #fu...   \n",
       "1     The Speculation continues...#AEW #AEWRevolutio...   \n",
       "2     When you’re dead inside but your best friend n...   \n",
       "3     POV: Your black cat has a mid '9 lives' crisis...   \n",
       "4     We hate being so savage?? #meme #memes #kimkar...   \n",
       "...                                                 ...   \n",
       "1324  No way it's nearly been 5 years #dankmemes #me...   \n",
       "1325  ik im late but no horny\\n#memes #memesdaily ht...   \n",
       "1326  belt go snap\\n#memes #memesdaily https://t.co/...   \n",
       "1327  health: 8/100\\n#memes #memesdaily https://t.co...   \n",
       "1328  FEBRURARY 2021 MOGUS OF THE MONTH — ROUND 2! V...   \n",
       "\n",
       "                   Created_At  No_of_Likes  No_of_Retweets  No_of_Followers  \\\n",
       "0     2021-03-06 07:13:12.000            0               0              501   \n",
       "1     2021-03-06 07:06:44.000            0               0              233   \n",
       "2     2021-03-06 07:06:31.000            0               0             1508   \n",
       "3     2021-03-06 07:03:05.000            1               0              138   \n",
       "4     2021-03-06 07:00:19.000            2               0              410   \n",
       "...                       ...          ...             ...              ...   \n",
       "1324  2021-03-04 08:50:28.000            7               1             9420   \n",
       "1325  2021-03-04 08:50:27.000            0               0              689   \n",
       "1326  2021-03-04 08:50:25.000            0               0              689   \n",
       "1327  2021-03-04 08:50:23.000            1               0              689   \n",
       "1328  2021-03-04 08:50:21.000            0               0              689   \n",
       "\n",
       "                     User_Location  \\\n",
       "0                              NaN   \n",
       "1                              NaN   \n",
       "2                              NaN   \n",
       "3                              NaN   \n",
       "4                              NaN   \n",
       "...                            ...   \n",
       "1324                 YourMomsHouse   \n",
       "1325  USM - United States of Memes   \n",
       "1326  USM - United States of Memes   \n",
       "1327  USM - United States of Memes   \n",
       "1328  USM - United States of Memes   \n",
       "\n",
       "                                              Image_URL  \\\n",
       "0        http://pbs.twimg.com/media/Evx1QhLWYAArjHu.jpg   \n",
       "1        http://pbs.twimg.com/media/EvxzdHqXcAchlkN.jpg   \n",
       "2        http://pbs.twimg.com/media/EvxzubJXEAAd4_-.jpg   \n",
       "3     http://pbs.twimg.com/tweet_video_thumb/Evxygjw...   \n",
       "4        http://pbs.twimg.com/media/EvxyTy6WgAALDAb.png   \n",
       "...                                                 ...   \n",
       "1324     http://pbs.twimg.com/media/Evn4V3OXEAg4YZE.jpg   \n",
       "1325     http://pbs.twimg.com/media/Evn4VnMXEBoJHKh.png   \n",
       "1326     http://pbs.twimg.com/media/Evn4VHuWgAAHpxW.jpg   \n",
       "1327     http://pbs.twimg.com/media/Evn4Ul3WgAANwbE.jpg   \n",
       "1328     http://pbs.twimg.com/media/Evn4UNhXcAEybxW.jpg   \n",
       "\n",
       "                                               hashtags  \n",
       "0                                      [#memes, #furry]  \n",
       "1     [#aew, #aewrevolution, #memes, #wrestlingtwitt...  \n",
       "2                                              [#memes]  \n",
       "3                   [#memes, #caturday, #catsoftwitter]  \n",
       "4                       [#meme, #memes, #kimkardashian]  \n",
       "...                                                 ...  \n",
       "1324                  [#dankmemes, #memes, #memesdaily]  \n",
       "1325                              [#memes, #memesdaily]  \n",
       "1326                              [#memes, #memesdaily]  \n",
       "1327                              [#memes, #memesdaily]  \n",
       "1328                              [#memes, #memesdaily]  \n",
       "\n",
       "[1329 rows x 11 columns]"
      ],
      "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>Posted_By</th>\n      <th>Twitter_Handle</th>\n      <th>Tweet</th>\n      <th>Created_At</th>\n      <th>No_of_Likes</th>\n      <th>No_of_Retweets</th>\n      <th>No_of_Followers</th>\n      <th>User_Location</th>\n      <th>Image_URL</th>\n      <th>hashtags</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>furry_irl</td>\n      <td>rFurry_Irl</td>\n      <td>Furry_irl | https://t.co/vPgHv1R6W6 #memes #fu...</td>\n      <td>2021-03-06 07:13:12.000</td>\n      <td>0</td>\n      <td>0</td>\n      <td>501</td>\n      <td>NaN</td>\n      <td>http://pbs.twimg.com/media/Evx1QhLWYAArjHu.jpg</td>\n      <td>[#memes, #furry]</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2</td>\n      <td>SidenoteSuplex</td>\n      <td>SidenoteSuplex</td>\n      <td>The Speculation continues...#AEW #AEWRevolutio...</td>\n      <td>2021-03-06 07:06:44.000</td>\n      <td>0</td>\n      <td>0</td>\n      <td>233</td>\n      <td>NaN</td>\n      <td>http://pbs.twimg.com/media/EvxzdHqXcAchlkN.jpg</td>\n      <td>[#aew, #aewrevolution, #memes, #wrestlingtwitt...</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>3</td>\n      <td>FunnyThings</td>\n      <td>funny_things127</td>\n      <td>When you’re dead inside but your best friend n...</td>\n      <td>2021-03-06 07:06:31.000</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1508</td>\n      <td>NaN</td>\n      <td>http://pbs.twimg.com/media/EvxzubJXEAAd4_-.jpg</td>\n      <td>[#memes]</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>4</td>\n      <td>????????????</td>\n      <td>chic_aya</td>\n      <td>POV: Your black cat has a mid '9 lives' crisis...</td>\n      <td>2021-03-06 07:03:05.000</td>\n      <td>1</td>\n      <td>0</td>\n      <td>138</td>\n      <td>NaN</td>\n      <td>http://pbs.twimg.com/tweet_video_thumb/Evxygjw...</td>\n      <td>[#memes, #caturday, #catsoftwitter]</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>5</td>\n      <td>Tell Tales Entertainment</td>\n      <td>TellTalesOnline</td>\n      <td>We hate being so savage?? #meme #memes #kimkar...</td>\n      <td>2021-03-06 07:00:19.000</td>\n      <td>2</td>\n      <td>0</td>\n      <td>410</td>\n      <td>NaN</td>\n      <td>http://pbs.twimg.com/media/EvxyTy6WgAALDAb.png</td>\n      <td>[#meme, #memes, #kimkardashian]</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>1324</th>\n      <td>1325</td>\n      <td>Dank Meme Dealer ????</td>\n      <td>DankRedditBot</td>\n      <td>No way it's nearly been 5 years #dankmemes #me...</td>\n      <td>2021-03-04 08:50:28.000</td>\n      <td>7</td>\n      <td>1</td>\n      <td>9420</td>\n      <td>YourMomsHouse</td>\n      <td>http://pbs.twimg.com/media/Evn4V3OXEAg4YZE.jpg</td>\n      <td>[#dankmemes, #memes, #memesdaily]</td>\n    </tr>\n    <tr>\n      <th>1325</th>\n      <td>1326</td>\n      <td>Daily dose of memes to make you laugh</td>\n      <td>MemesUniteUs</td>\n      <td>ik im late but no horny\\n#memes #memesdaily ht...</td>\n      <td>2021-03-04 08:50:27.000</td>\n      <td>0</td>\n      <td>0</td>\n      <td>689</td>\n      <td>USM - United States of Memes</td>\n      <td>http://pbs.twimg.com/media/Evn4VnMXEBoJHKh.png</td>\n      <td>[#memes, #memesdaily]</td>\n    </tr>\n    <tr>\n      <th>1326</th>\n      <td>1327</td>\n      <td>Daily dose of memes to make you laugh</td>\n      <td>MemesUniteUs</td>\n      <td>belt go snap\\n#memes #memesdaily https://t.co/...</td>\n      <td>2021-03-04 08:50:25.000</td>\n      <td>0</td>\n      <td>0</td>\n      <td>689</td>\n      <td>USM - United States of Memes</td>\n      <td>http://pbs.twimg.com/media/Evn4VHuWgAAHpxW.jpg</td>\n      <td>[#memes, #memesdaily]</td>\n    </tr>\n    <tr>\n      <th>1327</th>\n      <td>1328</td>\n      <td>Daily dose of memes to make you laugh</td>\n      <td>MemesUniteUs</td>\n      <td>health: 8/100\\n#memes #memesdaily https://t.co...</td>\n      <td>2021-03-04 08:50:23.000</td>\n      <td>1</td>\n      <td>0</td>\n      <td>689</td>\n      <td>USM - United States of Memes</td>\n      <td>http://pbs.twimg.com/media/Evn4Ul3WgAANwbE.jpg</td>\n      <td>[#memes, #memesdaily]</td>\n    </tr>\n    <tr>\n      <th>1328</th>\n      <td>1329</td>\n      <td>Daily dose of memes to make you laugh</td>\n      <td>MemesUniteUs</td>\n      <td>FEBRURARY 2021 MOGUS OF THE MONTH — ROUND 2! V...</td>\n      <td>2021-03-04 08:50:21.000</td>\n      <td>0</td>\n      <td>0</td>\n      <td>689</td>\n      <td>USM - United States of Memes</td>\n      <td>http://pbs.twimg.com/media/Evn4UNhXcAEybxW.jpg</td>\n      <td>[#memes, #memesdaily]</td>\n    </tr>\n  </tbody>\n</table>\n<p>1329 rows × 11 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 29
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "131     #WandaVision spoilers!!!\\n-\\n-\\n-\\n-\\n-\\n-\\n-\\...\n",
       "177     Wanda when the Westview residents start asking...\n",
       "414     Is this how memes work? #WandaVision #Spoilers...\n",
       "729     I'm not ready for tomorrow! Make sure to #foll...\n",
       "842     Has this ever happened to you? It has to me. #...\n",
       "1033    Is it \\n\\n#MEMES #exammemes #WandaVisionFinale...\n",
       "Name: Tweet, dtype: object"
      ]
     },
     "metadata": {},
     "execution_count": 25
    }
   ],
   "source": [
    "df.Tweet[df.Tweet.str.contains('#Wanda')]"
   ]
  },
  {
   "source": [
    "### Getting Hashtags to Play Nice"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Extract all words that begin with # and turn the results into a dataframe\n",
    "temp = df['Tweet'].str.lower().str.extractall(r\"(#\\w+)\")\n",
    "temp.columns = ['unnamed']\n",
    "# Convert the multiple hashtag values into a list\n",
    "temp = temp.groupby(level = 0)['unnamed'].apply(list)\n",
    "# Save the result as a feature in the original dataset\n",
    "df['hashtags'] = temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# search through our new hashtag feature and find unique hashtags; place them into the dictionary in the form {hashtag: number of instances}\n",
    "uniques_count = {}\n",
    "for value in df['hashtags']:\n",
    "    for word in value:\n",
    "        # If the hashtag is already in the dictionary, increment the value by 1\n",
    "        if word in uniques_count:\n",
    "            uniques_count[word] += 1\n",
    "        else:\n",
    "            uniques_count[word] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "#memes         1333\n",
       "#memesdaily     186\n",
       "#dankmemes      134\n",
       "#furry          120\n",
       "#funny          103\n",
       "dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "source": [
    "pd.Series(uniques_count).sort_values(ascending=False).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                   hashs\n",
       "0                 #memes\n",
       "1                 #furry\n",
       "2                   #aew\n",
       "3         #aewrevolution\n",
       "4      #wrestlingtwitter\n",
       "..                   ...\n",
       "833          #macncheese\n",
       "834               #balls\n",
       "835       #rickrollmemes\n",
       "836  #pursuitofhappiness\n",
       "837             #funpost\n",
       "\n",
       "[838 rows x 1 columns]"
      ],
      "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>hashs</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>#memes</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>#furry</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>#aew</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>#aewrevolution</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>#wrestlingtwitter</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>833</th>\n      <td>#macncheese</td>\n    </tr>\n    <tr>\n      <th>834</th>\n      <td>#balls</td>\n    </tr>\n    <tr>\n      <th>835</th>\n      <td>#rickrollmemes</td>\n    </tr>\n    <tr>\n      <th>836</th>\n      <td>#pursuitofhappiness</td>\n    </tr>\n    <tr>\n      <th>837</th>\n      <td>#funpost</td>\n    </tr>\n  </tbody>\n</table>\n<p>838 rows × 1 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 6
    }
   ],
   "source": [
    "hashtags = pd.DataFrame(uniques_count.keys(), columns=['hashs'])\n",
    "hashtags"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "838"
      ]
     },
     "metadata": {},
     "execution_count": 7
    }
   ],
   "source": [
    "len(uniques_count.keys())"
   ]
  },
  {
   "source": [
    "### Topics"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "trends = pd.read_csv('./trends.csv')\n",
    "trends_ = list(set(pd.concat((trends.iloc[:,1], trends.iloc[:,2]), axis = 0)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# def apply_list(rowval):\n",
    "#     trendlist = []\n",
    "#     for word in rowval:\n",
    "#         for trend in trends_:\n",
    "#             if fuzz.ratio(word, trend) > 60:\n",
    "#                 trendlist.append(trend)\n",
    "#     return trendlist\n",
    "\n",
    "# df['Tweet'].apply(apply_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "source": [
    "### Sentiment"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "sent = pd.read_csv('./sentiments.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "sent['Labels'].fillna('None', inplace = True)\n",
    "collect = sent['Labels'].str.lower().str.split()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "uniques_count = {}\n",
    "for value in collect:\n",
    "    for word in value:\n",
    "        # If the hashtag is already in the dictionary, increment the value by 1\n",
    "        if word in uniques_count:\n",
    "            uniques_count[word] += 1\n",
    "        else:\n",
    "            uniques_count[word] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "       labels count\n",
       "7        font   855\n",
       "1     gesture   328\n",
       "3       happy   305\n",
       "5         art   283\n",
       "84    cartoon   272\n",
       "77   organism   261\n",
       "82      human   210\n",
       "15    product   210\n",
       "155    facial   202\n",
       "79      event   183"
      ],
      "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>labels</th>\n      <th>count</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>7</th>\n      <td>font</td>\n      <td>855</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>gesture</td>\n      <td>328</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>happy</td>\n      <td>305</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>art</td>\n      <td>283</td>\n    </tr>\n    <tr>\n      <th>84</th>\n      <td>cartoon</td>\n      <td>272</td>\n    </tr>\n    <tr>\n      <th>77</th>\n      <td>organism</td>\n      <td>261</td>\n    </tr>\n    <tr>\n      <th>82</th>\n      <td>human</td>\n      <td>210</td>\n    </tr>\n    <tr>\n      <th>15</th>\n      <td>product</td>\n      <td>210</td>\n    </tr>\n    <tr>\n      <th>155</th>\n      <td>facial</td>\n      <td>202</td>\n    </tr>\n    <tr>\n      <th>79</th>\n      <td>event</td>\n      <td>183</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 13
    }
   ],
   "source": [
    "count = pd.DataFrame([uniques_count.keys(), uniques_count.values()]).T\n",
    "count.columns = ['labels','count']\n",
    "count.sort_values(by = 'count', ascending = False).head(10)"
   ]
  },
  {
   "source": [
    "## Insert into database"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# server = 'imt563-winter21.database.windows.net'\n",
    "# database = 'Team1'\n",
    "# username = 'imt563'\n",
    "# password = 'SuperSafePassword1234'\n",
    "\n",
    "# conn = pymssql.connect(server, username, password, database)\n",
    "# cursor = conn.cursor()"
   ]
  },
  {
   "source": [
    "### hashtag"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# for index, row in hashtags.iterrows():\n",
    "#     cursor.execute(\"INSERT INTO dbo.hashtag(hashtag_text) values(%s)\" % (\"\\'\" + row[0] + \"\\'\"))\n",
    "#     print(index, row[0])\n",
    "# conn.commit()\n",
    "# conn.close()"
   ]
  },
  {
   "source": [
    "### user_profile"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_profile = df[['Twitter_Handle','No_of_Followers']].drop_duplicates(subset = ['Twitter_Handle'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "error",
     "ename": "NameError",
     "evalue": "name 'cursor' is not defined",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-17-8fab2967e61a>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mindex\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mrow\u001b[0m \u001b[0;32min\u001b[0m \u001b[0muser_profile\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0miterrows\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m     \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"INSERT INTO dbo.user_profile(profile_name, follower_count) values(%s, %d)\"\u001b[0m \u001b[0;34m%\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0;34m\"\\'\"\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0mrow\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mTwitter_Handle\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0;34m\"\\'\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mrow\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mNo_of_Followers\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      3\u001b[0m     \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mindex\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mrow\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mTwitter_Handle\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mrow\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mNo_of_Followers\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcommit\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mclose\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mNameError\u001b[0m: name 'cursor' is not defined"
     ]
    }
   ],
   "source": [
    "for index, row in user_profile.iterrows():\n",
    "    cursor.execute(\"INSERT INTO dbo.user_profile(profile_name, follower_count) values(%s, %d)\" % (\"\\'\" + row.Twitter_Handle + \"\\'\",row.No_of_Followers))\n",
    "    print(index, row.Twitter_Handle, row.No_of_Followers)\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "source": [
    "### photo"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "photo = sent[['Image_URL','vaderSentimentScores','vaderSentiment']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "outputPrepend"
    ]
   },
   "outputs": [],
   "source": [
    "# for index, row in photo.iterrows():\n",
    "#     cursor.execute(\"INSERT INTO dbo.photo(photo_url, vader_score, vader_sentiment) values(%s, %f, %s)\" % (\"\\'\" + row[0] + \"\\'\", row[1],\"\\'\" + row[2] + \"\\'\"))\n",
    "#     print(index, row[0])\n",
    "# conn.commit()\n",
    "# conn.close()"
   ]
  },
  {
   "source": [
    "### sentiment"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sentiment = pd.DataFrame(uniques_count.keys())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in sentiment.iterrows():\n",
    "    cursor.execute(\"INSERT INTO dbo.sentiment(sentiment_name) values(%s)\" % (\"\\'\" + row[0] + \"\\'\"))\n",
    "    print(index, row[0])\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "source": [
    "### temp - Focus on interaction between profiles, photos, and post"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = df.iloc[:,2:10].drop(['User_Location','No_of_Followers'], axis = 1)\n",
    "temp['Created_At'] = pd.to_datetime(temp['Created_At']).dt.date.astype(str)\n",
    "temp['Tweet'] = temp['Tweet'].str.replace(\"'\",'').str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "for index, row in temp.iterrows():\n",
    "    cursor.execute(\"INSERT INTO dbo.temp(profile_name, post_date, photo_url, tweet_text, like_count, retweet_count) values(%s, %s, %s, %s, %d, %d)\" % (\"\\'\" + row[0] + \"\\'\", row[2], \"\\'\" + row[5] + \"\\'\", \"\\'\" + row[1] + \"\\'\", row[3], row[4]))\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "source": [
    "### temp - Focus on interaction between post and hashtag"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = df[['Tweet','hashtags']]\n",
    "temp['Tweet'] = temp['Tweet'].str.replace(\"'\",'').str.lower()\n",
    "temp = temp.explode('hashtags')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "for index, row in temp.iterrows():\n",
    "    cursor.execute(\"INSERT INTO dbo.temp(tweet, hashtag) values(%s, %s)\" % (\"\\'\" + row[0] + \"\\'\",\"\\'\" + row[1] + \"\\'\"))\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "source": [
    "### temp - Focus on interaction between sentiments and photos"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = sent[['Image_URL','Labels']]\n",
    "temp['Labels'] = temp['Labels'].str.lower().str.split()\n",
    "temp = temp.explode('Labels')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in temp.iterrows():\n",
    "    cursor.execute(\"INSERT INTO dbo.temp(photo_url, label) values(%s, %s)\" % (\"\\'\" + row[0] + \"\\'\",\"\\'\" + row[1] + \"\\'\"))\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "source": [
    "# EDA"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "Let's take a quick look at our data:"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.isna().sum().sum()"
   ]
  },
  {
   "source": [
    "There are a little over 1300 observations and 11 features. I found 575 missing values, but they are all concentrated in the 'User_Location' feature, which will not be overly helpful in predicting engagement."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "X = df[['hashtags','Image_URL']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "y = (df['No_of_Retweets']+1)/(df['No_of_Followers']+1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ]
}