Accenture_Data_Cleaning.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "zXQcSq6xEZ32"
      },
      "outputs": [],
      "source": [
        "import pandas as pd\n",
        "import numpy as np\n",
        "import matplotlib.pyplot as plt\n",
        "import seaborn as sns"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#import data\n",
        "content_df = pd.read_csv('/content/Content.csv',index_col=0)\n",
        "content_df.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "RDZIrxSGFR5y",
        "outputId": "0dd48fcd-59f5-463c-ee7c-13430a15fb85"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                             Content ID                               User ID  \\\n",
              "0  97522e57-d9ab-4bd6-97bf-c24d952602d2  8d3cd87d-8a31-4935-9a4f-b319bfe05f31   \n",
              "1  9f737e0a-3cdd-4d29-9d24-753f4e3be810  beb1f34e-7870-46d6-9fc7-2e12eb83ce43   \n",
              "2  230c4e4d-70c3-461d-b42c-ec09396efb3f  a5c65404-5894-4b87-82f2-d787cbee86b4   \n",
              "3  356fff80-da4d-4785-9f43-bc1261031dc6  9fb4ce88-fac1-406c-8544-1a899cee7aaf   \n",
              "4  01ab84dd-6364-4236-abbb-3f237db77180  e206e31b-5f85-4964-b6ea-d7ee5324def1   \n",
              "\n",
              "    Type        Category                                                URL  \n",
              "0  photo        Studying  https://socialbuzz.cdn.com/content/storage/975...  \n",
              "1  photo  healthy eating  https://socialbuzz.cdn.com/content/storage/9f7...  \n",
              "2  photo  healthy eating  https://socialbuzz.cdn.com/content/storage/230...  \n",
              "3  photo      technology  https://socialbuzz.cdn.com/content/storage/356...  \n",
              "4  video            food  https://socialbuzz.cdn.com/content/storage/01a...  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-c99089db-2e91-4adc-a4c2-4793a0144b08\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Content ID</th>\n",
              "      <th>User ID</th>\n",
              "      <th>Type</th>\n",
              "      <th>Category</th>\n",
              "      <th>URL</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>97522e57-d9ab-4bd6-97bf-c24d952602d2</td>\n",
              "      <td>8d3cd87d-8a31-4935-9a4f-b319bfe05f31</td>\n",
              "      <td>photo</td>\n",
              "      <td>Studying</td>\n",
              "      <td>https://socialbuzz.cdn.com/content/storage/975...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>9f737e0a-3cdd-4d29-9d24-753f4e3be810</td>\n",
              "      <td>beb1f34e-7870-46d6-9fc7-2e12eb83ce43</td>\n",
              "      <td>photo</td>\n",
              "      <td>healthy eating</td>\n",
              "      <td>https://socialbuzz.cdn.com/content/storage/9f7...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>230c4e4d-70c3-461d-b42c-ec09396efb3f</td>\n",
              "      <td>a5c65404-5894-4b87-82f2-d787cbee86b4</td>\n",
              "      <td>photo</td>\n",
              "      <td>healthy eating</td>\n",
              "      <td>https://socialbuzz.cdn.com/content/storage/230...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>356fff80-da4d-4785-9f43-bc1261031dc6</td>\n",
              "      <td>9fb4ce88-fac1-406c-8544-1a899cee7aaf</td>\n",
              "      <td>photo</td>\n",
              "      <td>technology</td>\n",
              "      <td>https://socialbuzz.cdn.com/content/storage/356...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>01ab84dd-6364-4236-abbb-3f237db77180</td>\n",
              "      <td>e206e31b-5f85-4964-b6ea-d7ee5324def1</td>\n",
              "      <td>video</td>\n",
              "      <td>food</td>\n",
              "      <td>https://socialbuzz.cdn.com/content/storage/01a...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-c99089db-2e91-4adc-a4c2-4793a0144b08')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-c99089db-2e91-4adc-a4c2-4793a0144b08 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-c99089db-2e91-4adc-a4c2-4793a0144b08');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 48
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "content_df.info()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "kj8U9NMGFe1v",
        "outputId": "b8c31db8-8f2f-442f-be0c-a64614403968"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Int64Index: 1000 entries, 0 to 999\n",
            "Data columns (total 5 columns):\n",
            " #   Column      Non-Null Count  Dtype \n",
            "---  ------      --------------  ----- \n",
            " 0   Content ID  1000 non-null   object\n",
            " 1   User ID     1000 non-null   object\n",
            " 2   Type        1000 non-null   object\n",
            " 3   Category    1000 non-null   object\n",
            " 4   URL         801 non-null    object\n",
            "dtypes: object(5)\n",
            "memory usage: 46.9+ KB\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Summary of the data\n",
        "content_df.describe()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 175
        },
        "id": "r0BzCIFIFm_4",
        "outputId": "d67c584a-a53d-4d01-c599-54c8f944cfda"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                  Content ID  \\\n",
              "count                                   1000   \n",
              "unique                                  1000   \n",
              "top     97522e57-d9ab-4bd6-97bf-c24d952602d2   \n",
              "freq                                       1   \n",
              "\n",
              "                                     User ID   Type    Category  \\\n",
              "count                                   1000   1000        1000   \n",
              "unique                                   446      4          41   \n",
              "top     72d2587e-8fae-4626-a73d-352e6465ba0f  photo  technology   \n",
              "freq                                       8    261          71   \n",
              "\n",
              "                                                      URL  \n",
              "count                                                 801  \n",
              "unique                                                801  \n",
              "top     https://socialbuzz.cdn.com/content/storage/975...  \n",
              "freq                                                    1  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-3a556538-3747-41e4-922c-9bd7d6b1d5b6\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Content ID</th>\n",
              "      <th>User ID</th>\n",
              "      <th>Type</th>\n",
              "      <th>Category</th>\n",
              "      <th>URL</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>count</th>\n",
              "      <td>1000</td>\n",
              "      <td>1000</td>\n",
              "      <td>1000</td>\n",
              "      <td>1000</td>\n",
              "      <td>801</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>unique</th>\n",
              "      <td>1000</td>\n",
              "      <td>446</td>\n",
              "      <td>4</td>\n",
              "      <td>41</td>\n",
              "      <td>801</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>top</th>\n",
              "      <td>97522e57-d9ab-4bd6-97bf-c24d952602d2</td>\n",
              "      <td>72d2587e-8fae-4626-a73d-352e6465ba0f</td>\n",
              "      <td>photo</td>\n",
              "      <td>technology</td>\n",
              "      <td>https://socialbuzz.cdn.com/content/storage/975...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>freq</th>\n",
              "      <td>1</td>\n",
              "      <td>8</td>\n",
              "      <td>261</td>\n",
              "      <td>71</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-3a556538-3747-41e4-922c-9bd7d6b1d5b6')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-3a556538-3747-41e4-922c-9bd7d6b1d5b6 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-3a556538-3747-41e4-922c-9bd7d6b1d5b6');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 50
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "* We won't need the User ID or URL columns for our analysis, so let's drop those. Then let's check the values in the category column to make sure they make sense given our data."
      ],
      "metadata": {
        "id": "QW8YuDh_GGa-"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "content_df.drop(labels=['User ID','URL'],axis=1,inplace=True)"
      ],
      "metadata": {
        "id": "XiEZRUGnFpTH"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "content_df.info()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4ymIrUw8GVr9",
        "outputId": "80f210f0-bad2-45e1-93f0-e48130fc7126"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Int64Index: 1000 entries, 0 to 999\n",
            "Data columns (total 3 columns):\n",
            " #   Column      Non-Null Count  Dtype \n",
            "---  ------      --------------  ----- \n",
            " 0   Content ID  1000 non-null   object\n",
            " 1   Type        1000 non-null   object\n",
            " 2   Category    1000 non-null   object\n",
            "dtypes: object(3)\n",
            "memory usage: 31.2+ KB\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Check values in category columns\n",
        "content_df['Category'].value_counts()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "a75haw-9Gcqj",
        "outputId": "9e1bd286-1bfc-4afb-f365-e04bc1b88025"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "technology           71\n",
              "animals              67\n",
              "travel               67\n",
              "culture              63\n",
              "science              63\n",
              "fitness              61\n",
              "food                 61\n",
              "healthy eating       61\n",
              "cooking              60\n",
              "soccer               58\n",
              "tennis               58\n",
              "education            57\n",
              "dogs                 56\n",
              "studying             55\n",
              "veganism             48\n",
              "public speaking      48\n",
              "Fitness               5\n",
              "Animals               4\n",
              "Science               4\n",
              "\"soccer\"              3\n",
              "\"culture\"             3\n",
              "Soccer                3\n",
              "\"dogs\"                2\n",
              "Education             2\n",
              "Studying              2\n",
              "Travel                2\n",
              "Food                  2\n",
              "\"veganism\"            1\n",
              "\"public speaking\"     1\n",
              "Public Speaking       1\n",
              "\"technology\"          1\n",
              "\"cooking\"             1\n",
              "Healthy Eating        1\n",
              "\"studying\"            1\n",
              "\"food\"                1\n",
              "Culture               1\n",
              "\"tennis\"              1\n",
              "Technology            1\n",
              "\"animals\"             1\n",
              "Veganism              1\n",
              "\"science\"             1\n",
              "Name: Category, dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 53
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "* We'll need to clean up these categories by removing punctuation and capitalization."
      ],
      "metadata": {
        "id": "12wZo_t6GtGq"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#strip quote mark and lowercase all values\n",
        "content_df['Category'] = content_df['Category'].apply(lambda x:x.strip('\"').lower())"
      ],
      "metadata": {
        "id": "uIbUGSBzGhC9"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#checking if all categories are in lower case\n",
        "content_df['Category'].value_counts()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "oV23Yd-_HJ7x",
        "outputId": "72158f36-813a-4497-8255-c0b8662d8254"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "technology         73\n",
              "animals            72\n",
              "travel             69\n",
              "science            68\n",
              "culture            67\n",
              "fitness            66\n",
              "food               64\n",
              "soccer             64\n",
              "healthy eating     62\n",
              "cooking            61\n",
              "tennis             59\n",
              "education          59\n",
              "studying           58\n",
              "dogs               58\n",
              "public speaking    50\n",
              "veganism           50\n",
              "Name: Category, dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 55
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#rename content to content_type\n",
        "content_df.rename(columns={'Type':'Content_Type'},inplace=True)\n",
        "content_df['Content_Type'].value_counts()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "J4zuIM3bHNmF",
        "outputId": "720404e1-9ff7-4576-fe13-c7bd5e3c3511"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "photo    261\n",
              "video    259\n",
              "GIF      244\n",
              "audio    236\n",
              "Name: Content_Type, dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 56
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#import 2nd dataset\n",
        "reactions_df = pd.read_csv('/content/Reactions.csv',index_col=0)\n",
        "reactions_df.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "GHTH3wztH5Np",
        "outputId": "3e7614fc-13fa-45a2-ea11-2e90f1b74ee9"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                             Content ID                               User ID  \\\n",
              "0  97522e57-d9ab-4bd6-97bf-c24d952602d2                                   NaN   \n",
              "1  97522e57-d9ab-4bd6-97bf-c24d952602d2  5d454588-283d-459d-915d-c48a2cb4c27f   \n",
              "2  97522e57-d9ab-4bd6-97bf-c24d952602d2  92b87fa5-f271-43e0-af66-84fac21052e6   \n",
              "3  97522e57-d9ab-4bd6-97bf-c24d952602d2  163daa38-8b77-48c9-9af6-37a6c1447ac2   \n",
              "4  97522e57-d9ab-4bd6-97bf-c24d952602d2  34e8add9-0206-47fd-a501-037b994650a2   \n",
              "\n",
              "      Type             Datetime  \n",
              "0      NaN  2021-04-22 15:17:15  \n",
              "1  disgust  2020-11-07 09:43:50  \n",
              "2  dislike  2021-06-17 12:22:51  \n",
              "3   scared  2021-04-18 05:13:58  \n",
              "4  disgust  2021-01-06 19:13:01  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-f13bdac5-068d-4670-ae20-16e8de353cf7\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Content ID</th>\n",
              "      <th>User ID</th>\n",
              "      <th>Type</th>\n",
              "      <th>Datetime</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>97522e57-d9ab-4bd6-97bf-c24d952602d2</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2021-04-22 15:17:15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>97522e57-d9ab-4bd6-97bf-c24d952602d2</td>\n",
              "      <td>5d454588-283d-459d-915d-c48a2cb4c27f</td>\n",
              "      <td>disgust</td>\n",
              "      <td>2020-11-07 09:43:50</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>97522e57-d9ab-4bd6-97bf-c24d952602d2</td>\n",
              "      <td>92b87fa5-f271-43e0-af66-84fac21052e6</td>\n",
              "      <td>dislike</td>\n",
              "      <td>2021-06-17 12:22:51</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>97522e57-d9ab-4bd6-97bf-c24d952602d2</td>\n",
              "      <td>163daa38-8b77-48c9-9af6-37a6c1447ac2</td>\n",
              "      <td>scared</td>\n",
              "      <td>2021-04-18 05:13:58</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>97522e57-d9ab-4bd6-97bf-c24d952602d2</td>\n",
              "      <td>34e8add9-0206-47fd-a501-037b994650a2</td>\n",
              "      <td>disgust</td>\n",
              "      <td>2021-01-06 19:13:01</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-f13bdac5-068d-4670-ae20-16e8de353cf7')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-f13bdac5-068d-4670-ae20-16e8de353cf7 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-f13bdac5-068d-4670-ae20-16e8de353cf7');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 57
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "reactions_df.info()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "xzO8_7sRIgkJ",
        "outputId": "fd8c79bf-f2ce-46ad-8411-79a7f131d272"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Int64Index: 25553 entries, 0 to 25552\n",
            "Data columns (total 4 columns):\n",
            " #   Column      Non-Null Count  Dtype \n",
            "---  ------      --------------  ----- \n",
            " 0   Content ID  25553 non-null  object\n",
            " 1   User ID     22534 non-null  object\n",
            " 2   Type        24573 non-null  object\n",
            " 3   Datetime    25553 non-null  object\n",
            "dtypes: object(4)\n",
            "memory usage: 998.2+ KB\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "reactions_df.describe()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 175
        },
        "id": "PF6g9iSAIoaH",
        "outputId": "1868436b-adb0-48d4-fa02-b81b4d69bace"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                  Content ID  \\\n",
              "count                                  25553   \n",
              "unique                                   980   \n",
              "top     4b2d0fff-3b4f-43ca-a7df-c430479cb9ba   \n",
              "freq                                      49   \n",
              "\n",
              "                                     User ID   Type             Datetime  \n",
              "count                                  22534  24573                25553  \n",
              "unique                                   500     16                25542  \n",
              "top     c76c3393-88e2-47b0-ac37-dc4f2053f5a5  heart  2020-10-29 20:51:08  \n",
              "freq                                      65   1622                    2  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-bd82697f-b3fc-44bd-a53a-179c788ee948\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Content ID</th>\n",
              "      <th>User ID</th>\n",
              "      <th>Type</th>\n",
              "      <th>Datetime</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>count</th>\n",
              "      <td>25553</td>\n",
              "      <td>22534</td>\n",
              "      <td>24573</td>\n",
              "      <td>25553</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>unique</th>\n",
              "      <td>980</td>\n",
              "      <td>500</td>\n",
              "      <td>16</td>\n",
              "      <td>25542</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>top</th>\n",
              "      <td>4b2d0fff-3b4f-43ca-a7df-c430479cb9ba</td>\n",
              "      <td>c76c3393-88e2-47b0-ac37-dc4f2053f5a5</td>\n",
              "      <td>heart</td>\n",
              "      <td>2020-10-29 20:51:08</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>freq</th>\n",
              "      <td>49</td>\n",
              "      <td>65</td>\n",
              "      <td>1622</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-bd82697f-b3fc-44bd-a53a-179c788ee948')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-bd82697f-b3fc-44bd-a53a-179c788ee948 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-bd82697f-b3fc-44bd-a53a-179c788ee948');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 59
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "* Again, we can drop the User ID column. We'll also need to drop rows with missing values, and change the \"Datetime\" column to a datetime data type. Finally, we'll need to change the \"Type\" column name like we did for the Content table.\n",
        "\n"
      ],
      "metadata": {
        "id": "JPuIxSGYIzlt"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# drop user ID column\n",
        "reactions_df.drop(labels = ['User ID'],axis=1,inplace=True)"
      ],
      "metadata": {
        "id": "d2mhuT1xItKF"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# convert dates to datetime\n",
        "reactions_df['Datetime'] = pd.to_datetime(reactions_df['Datetime'])\n"
      ],
      "metadata": {
        "id": "bktKkTBpJWJg"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#drop rows with missing values\n",
        "reactions_df.dropna(inplace=True)"
      ],
      "metadata": {
        "id": "Nvtn_jYvJYFj"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#rename column\n",
        "reactions_df.rename(columns={\"Type\":'Reaction_Type'},inplace=True)"
      ],
      "metadata": {
        "id": "l0e48W1FJd6m"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "reactions_df.info()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Tjq1-75fQN9l",
        "outputId": "4df739c6-653a-4312-ff28-e37e7a1c621a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Int64Index: 24573 entries, 1 to 25552\n",
            "Data columns (total 3 columns):\n",
            " #   Column         Non-Null Count  Dtype         \n",
            "---  ------         --------------  -----         \n",
            " 0   Content ID     24573 non-null  object        \n",
            " 1   Reaction_Type  24573 non-null  object        \n",
            " 2   Datetime       24573 non-null  datetime64[ns]\n",
            "dtypes: datetime64[ns](1), object(2)\n",
            "memory usage: 767.9+ KB\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#import 3rd Dataset\n",
        "reactiontypes_df = pd.read_csv('/content/ReactionTypes.csv',index_col=0)\n",
        "reactiontypes_df.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "mpTvRpMbKbct",
        "outputId": "0905d91d-e7e8-4a5d-fd22-50a8fbec28db"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "         Type Sentiment  Score\n",
              "0       heart  positive     60\n",
              "1        want  positive     70\n",
              "2     disgust  negative      0\n",
              "3        hate  negative      5\n",
              "4  interested  positive     30"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-88d67145-b149-4ce6-af73-12248378cbc9\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Type</th>\n",
              "      <th>Sentiment</th>\n",
              "      <th>Score</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>heart</td>\n",
              "      <td>positive</td>\n",
              "      <td>60</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>want</td>\n",
              "      <td>positive</td>\n",
              "      <td>70</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>disgust</td>\n",
              "      <td>negative</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>hate</td>\n",
              "      <td>negative</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>interested</td>\n",
              "      <td>positive</td>\n",
              "      <td>30</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-88d67145-b149-4ce6-af73-12248378cbc9')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-88d67145-b149-4ce6-af73-12248378cbc9 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-88d67145-b149-4ce6-af73-12248378cbc9');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 71
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "reactiontypes_df.info()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Rm21k0PGKyCb",
        "outputId": "455e47da-dfaf-42a1-87dc-f025b9f30cd2"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Int64Index: 16 entries, 0 to 15\n",
            "Data columns (total 3 columns):\n",
            " #   Column     Non-Null Count  Dtype \n",
            "---  ------     --------------  ----- \n",
            " 0   Type       16 non-null     object\n",
            " 1   Sentiment  16 non-null     object\n",
            " 2   Score      16 non-null     int64 \n",
            "dtypes: int64(1), object(2)\n",
            "memory usage: 512.0+ bytes\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "reactiontypes_df.describe()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 300
        },
        "id": "wKTnrlpTK9X3",
        "outputId": "667b5221-66f9-4b95-afe4-9b12a69dabc2"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "           Score\n",
              "count  16.000000\n",
              "mean   39.625000\n",
              "std    26.901983\n",
              "min     0.000000\n",
              "25%    14.250000\n",
              "50%    40.000000\n",
              "75%    66.250000\n",
              "max    75.000000"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-882fd396-fc58-4acb-816e-b67855caca1a\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Score</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>count</th>\n",
              "      <td>16.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>mean</th>\n",
              "      <td>39.625000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>std</th>\n",
              "      <td>26.901983</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>min</th>\n",
              "      <td>0.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25%</th>\n",
              "      <td>14.250000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>50%</th>\n",
              "      <td>40.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>75%</th>\n",
              "      <td>66.250000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>max</th>\n",
              "      <td>75.000000</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-882fd396-fc58-4acb-816e-b67855caca1a')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-882fd396-fc58-4acb-816e-b67855caca1a button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-882fd396-fc58-4acb-816e-b67855caca1a');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 73
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#rename type column\n",
        "reactiontypes_df.rename(columns={'Type':'Reaction_Type'},inplace=True)\n",
        "reactiontypes_df.info()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "UNz9HAcNLATt",
        "outputId": "6630571e-c4e8-4831-ac12-40e82e775e60"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Int64Index: 16 entries, 0 to 15\n",
            "Data columns (total 3 columns):\n",
            " #   Column         Non-Null Count  Dtype \n",
            "---  ------         --------------  ----- \n",
            " 0   Reaction_Type  16 non-null     object\n",
            " 1   Sentiment      16 non-null     object\n",
            " 2   Score          16 non-null     int64 \n",
            "dtypes: int64(1), object(2)\n",
            "memory usage: 512.0+ bytes\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "reactiontypes_df.value_counts()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "PsYgVMv4Lf1D",
        "outputId": "6fbc02c6-0278-41a5-f3a3-f7653b5254e1"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Reaction_Type  Sentiment  Score\n",
              "adore          positive   72       1\n",
              "cherish        positive   70       1\n",
              "disgust        negative   0        1\n",
              "dislike        negative   10       1\n",
              "hate           negative   5        1\n",
              "heart          positive   60       1\n",
              "indifferent    neutral    20       1\n",
              "interested     positive   30       1\n",
              "intrigued      positive   45       1\n",
              "like           positive   50       1\n",
              "love           positive   65       1\n",
              "peeking        neutral    35       1\n",
              "scared         negative   15       1\n",
              "super love     positive   75       1\n",
              "want           positive   70       1\n",
              "worried        negative   12       1\n",
              "dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 75
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "* It looks like our data is clean, based on the client's specifications (remove rows with missing values, drop unnecessary colunmns, and ensure data types match the column values)."
      ],
      "metadata": {
        "id": "cmYEHBBJL7AW"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Creating a joined table for analysis (Data Modelling)"
      ],
      "metadata": {
        "id": "BNl-A5T_PewT"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#merge tables on content ID columns\n",
        "top_performers = reactions_df.merge(content_df,on='Content ID')\n",
        "top_performers"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 423
        },
        "id": "ZEU1LuQZL2F1",
        "outputId": "b35453d2-0817-42f9-c046-38fe5cc763b4"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                 Content ID Reaction_Type            Datetime  \\\n",
              "0      97522e57-d9ab-4bd6-97bf-c24d952602d2       disgust 2020-11-07 09:43:50   \n",
              "1      97522e57-d9ab-4bd6-97bf-c24d952602d2       dislike 2021-06-17 12:22:51   \n",
              "2      97522e57-d9ab-4bd6-97bf-c24d952602d2        scared 2021-04-18 05:13:58   \n",
              "3      97522e57-d9ab-4bd6-97bf-c24d952602d2       disgust 2021-01-06 19:13:01   \n",
              "4      97522e57-d9ab-4bd6-97bf-c24d952602d2    interested 2020-08-23 12:25:58   \n",
              "...                                     ...           ...                 ...   \n",
              "24568  75d6b589-7fae-4a6d-b0d0-752845150e56       dislike 2020-06-27 09:46:48   \n",
              "24569  75d6b589-7fae-4a6d-b0d0-752845150e56     intrigued 2021-02-16 17:17:02   \n",
              "24570  75d6b589-7fae-4a6d-b0d0-752845150e56    interested 2020-09-12 03:54:58   \n",
              "24571  75d6b589-7fae-4a6d-b0d0-752845150e56       worried 2020-11-04 20:08:31   \n",
              "24572  75d6b589-7fae-4a6d-b0d0-752845150e56       cherish 2021-01-04 04:55:11   \n",
              "\n",
              "      Content_Type    Category  \n",
              "0            photo    studying  \n",
              "1            photo    studying  \n",
              "2            photo    studying  \n",
              "3            photo    studying  \n",
              "4            photo    studying  \n",
              "...            ...         ...  \n",
              "24568        audio  technology  \n",
              "24569        audio  technology  \n",
              "24570        audio  technology  \n",
              "24571        audio  technology  \n",
              "24572        audio  technology  \n",
              "\n",
              "[24573 rows x 5 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-4a7b4327-81d1-4a4f-bccd-100e73d2edec\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Content ID</th>\n",
              "      <th>Reaction_Type</th>\n",
              "      <th>Datetime</th>\n",
              "      <th>Content_Type</th>\n",
              "      <th>Category</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>97522e57-d9ab-4bd6-97bf-c24d952602d2</td>\n",
              "      <td>disgust</td>\n",
              "      <td>2020-11-07 09:43:50</td>\n",
              "      <td>photo</td>\n",
              "      <td>studying</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>97522e57-d9ab-4bd6-97bf-c24d952602d2</td>\n",
              "      <td>dislike</td>\n",
              "      <td>2021-06-17 12:22:51</td>\n",
              "      <td>photo</td>\n",
              "      <td>studying</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>97522e57-d9ab-4bd6-97bf-c24d952602d2</td>\n",
              "      <td>scared</td>\n",
              "      <td>2021-04-18 05:13:58</td>\n",
              "      <td>photo</td>\n",
              "      <td>studying</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>97522e57-d9ab-4bd6-97bf-c24d952602d2</td>\n",
              "      <td>disgust</td>\n",
              "      <td>2021-01-06 19:13:01</td>\n",
              "      <td>photo</td>\n",
              "      <td>studying</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>97522e57-d9ab-4bd6-97bf-c24d952602d2</td>\n",
              "      <td>interested</td>\n",
              "      <td>2020-08-23 12:25:58</td>\n",
              "      <td>photo</td>\n",
              "      <td>studying</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24568</th>\n",
              "      <td>75d6b589-7fae-4a6d-b0d0-752845150e56</td>\n",
              "      <td>dislike</td>\n",
              "      <td>2020-06-27 09:46:48</td>\n",
              "      <td>audio</td>\n",
              "      <td>technology</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24569</th>\n",
              "      <td>75d6b589-7fae-4a6d-b0d0-752845150e56</td>\n",
              "      <td>intrigued</td>\n",
              "      <td>2021-02-16 17:17:02</td>\n",
              "      <td>audio</td>\n",
              "      <td>technology</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24570</th>\n",
              "      <td>75d6b589-7fae-4a6d-b0d0-752845150e56</td>\n",
              "      <td>interested</td>\n",
              "      <td>2020-09-12 03:54:58</td>\n",
              "      <td>audio</td>\n",
              "      <td>technology</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24571</th>\n",
              "      <td>75d6b589-7fae-4a6d-b0d0-752845150e56</td>\n",
              "      <td>worried</td>\n",
              "      <td>2020-11-04 20:08:31</td>\n",
              "      <td>audio</td>\n",
              "      <td>technology</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24572</th>\n",
              "      <td>75d6b589-7fae-4a6d-b0d0-752845150e56</td>\n",
              "      <td>cherish</td>\n",
              "      <td>2021-01-04 04:55:11</td>\n",
              "      <td>audio</td>\n",
              "      <td>technology</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>24573 rows × 5 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-4a7b4327-81d1-4a4f-bccd-100e73d2edec')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-4a7b4327-81d1-4a4f-bccd-100e73d2edec button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-4a7b4327-81d1-4a4f-bccd-100e73d2edec');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 70
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#merge tables on reaction type column\n",
        "top_performers = top_performers.merge(reactiontypes_df,on='Reaction_Type')\n",
        "top_performers"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 423
        },
        "id": "djeL2AcTPFAP",
        "outputId": "23d2db81-548d-4660-fe26-1328ff42780a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                 Content ID Reaction_Type            Datetime  \\\n",
              "0      97522e57-d9ab-4bd6-97bf-c24d952602d2       disgust 2020-11-07 09:43:50   \n",
              "1      97522e57-d9ab-4bd6-97bf-c24d952602d2       disgust 2021-01-06 19:13:01   \n",
              "2      97522e57-d9ab-4bd6-97bf-c24d952602d2       disgust 2021-04-09 02:46:20   \n",
              "3      9f737e0a-3cdd-4d29-9d24-753f4e3be810       disgust 2021-03-28 21:15:26   \n",
              "4      230c4e4d-70c3-461d-b42c-ec09396efb3f       disgust 2020-08-04 05:40:33   \n",
              "...                                     ...           ...                 ...   \n",
              "24568  435007a5-6261-4d8b-b0a4-55fdc189754b         adore 2020-10-04 22:26:33   \n",
              "24569  435007a5-6261-4d8b-b0a4-55fdc189754b         adore 2020-09-18 10:50:50   \n",
              "24570  4e4c9690-c013-4ee7-9e66-943d8cbd27b7         adore 2020-10-31 03:58:44   \n",
              "24571  4e4c9690-c013-4ee7-9e66-943d8cbd27b7         adore 2020-06-25 15:12:29   \n",
              "24572  4e4c9690-c013-4ee7-9e66-943d8cbd27b7         adore 2020-12-17 16:32:57   \n",
              "\n",
              "      Content_Type        Category Sentiment  Score  \n",
              "0            photo        studying  negative      0  \n",
              "1            photo        studying  negative      0  \n",
              "2            photo        studying  negative      0  \n",
              "3            photo  healthy eating  negative      0  \n",
              "4            photo  healthy eating  negative      0  \n",
              "...            ...             ...       ...    ...  \n",
              "24568        audio        veganism  positive     72  \n",
              "24569        audio        veganism  positive     72  \n",
              "24570          GIF         culture  positive     72  \n",
              "24571          GIF         culture  positive     72  \n",
              "24572          GIF         culture  positive     72  \n",
              "\n",
              "[24573 rows x 7 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-0d55b00b-5dc6-4887-a6af-e8e6845a15fb\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Content ID</th>\n",
              "      <th>Reaction_Type</th>\n",
              "      <th>Datetime</th>\n",
              "      <th>Content_Type</th>\n",
              "      <th>Category</th>\n",
              "      <th>Sentiment</th>\n",
              "      <th>Score</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>97522e57-d9ab-4bd6-97bf-c24d952602d2</td>\n",
              "      <td>disgust</td>\n",
              "      <td>2020-11-07 09:43:50</td>\n",
              "      <td>photo</td>\n",
              "      <td>studying</td>\n",
              "      <td>negative</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>97522e57-d9ab-4bd6-97bf-c24d952602d2</td>\n",
              "      <td>disgust</td>\n",
              "      <td>2021-01-06 19:13:01</td>\n",
              "      <td>photo</td>\n",
              "      <td>studying</td>\n",
              "      <td>negative</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>97522e57-d9ab-4bd6-97bf-c24d952602d2</td>\n",
              "      <td>disgust</td>\n",
              "      <td>2021-04-09 02:46:20</td>\n",
              "      <td>photo</td>\n",
              "      <td>studying</td>\n",
              "      <td>negative</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>9f737e0a-3cdd-4d29-9d24-753f4e3be810</td>\n",
              "      <td>disgust</td>\n",
              "      <td>2021-03-28 21:15:26</td>\n",
              "      <td>photo</td>\n",
              "      <td>healthy eating</td>\n",
              "      <td>negative</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>230c4e4d-70c3-461d-b42c-ec09396efb3f</td>\n",
              "      <td>disgust</td>\n",
              "      <td>2020-08-04 05:40:33</td>\n",
              "      <td>photo</td>\n",
              "      <td>healthy eating</td>\n",
              "      <td>negative</td>\n",
              "      <td>0</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",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24568</th>\n",
              "      <td>435007a5-6261-4d8b-b0a4-55fdc189754b</td>\n",
              "      <td>adore</td>\n",
              "      <td>2020-10-04 22:26:33</td>\n",
              "      <td>audio</td>\n",
              "      <td>veganism</td>\n",
              "      <td>positive</td>\n",
              "      <td>72</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24569</th>\n",
              "      <td>435007a5-6261-4d8b-b0a4-55fdc189754b</td>\n",
              "      <td>adore</td>\n",
              "      <td>2020-09-18 10:50:50</td>\n",
              "      <td>audio</td>\n",
              "      <td>veganism</td>\n",
              "      <td>positive</td>\n",
              "      <td>72</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24570</th>\n",
              "      <td>4e4c9690-c013-4ee7-9e66-943d8cbd27b7</td>\n",
              "      <td>adore</td>\n",
              "      <td>2020-10-31 03:58:44</td>\n",
              "      <td>GIF</td>\n",
              "      <td>culture</td>\n",
              "      <td>positive</td>\n",
              "      <td>72</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24571</th>\n",
              "      <td>4e4c9690-c013-4ee7-9e66-943d8cbd27b7</td>\n",
              "      <td>adore</td>\n",
              "      <td>2020-06-25 15:12:29</td>\n",
              "      <td>GIF</td>\n",
              "      <td>culture</td>\n",
              "      <td>positive</td>\n",
              "      <td>72</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24572</th>\n",
              "      <td>4e4c9690-c013-4ee7-9e66-943d8cbd27b7</td>\n",
              "      <td>adore</td>\n",
              "      <td>2020-12-17 16:32:57</td>\n",
              "      <td>GIF</td>\n",
              "      <td>culture</td>\n",
              "      <td>positive</td>\n",
              "      <td>72</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>24573 rows × 7 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-0d55b00b-5dc6-4887-a6af-e8e6845a15fb')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-0d55b00b-5dc6-4887-a6af-e8e6845a15fb button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-0d55b00b-5dc6-4887-a6af-e8e6845a15fb');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 76
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "* We have over 24,573 rows of data. Let's use this table to answer our business question regarding the top five categories with the largest aggregate popularity."
      ],
      "metadata": {
        "id": "93BuxTa7RSxC"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Top Category Analysis"
      ],
      "metadata": {
        "id": "j34_CVJyRgVy"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "* The measure of popularity provided to us by the client is the \"Score\" column. To find the highest aggregate popularity for each category, we simply have to sum this value by category."
      ],
      "metadata": {
        "id": "j2IlXU8bRmtu"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#group by category and calculate total score\n",
        "top_five = top_performers.groupby('Category').sum().sort_values(by='Score',ascending=False)\n",
        "top_five.head(5)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 292
        },
        "id": "lddapwFyRMWn",
        "outputId": "58cdf3ad-24f0-474f-e855-c32afe2d3b12"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "<ipython-input-78-1813259fd6fe>:2: FutureWarning: The default value of numeric_only in DataFrameGroupBy.sum is deprecated. In a future version, numeric_only will default to False. Either specify numeric_only or select only columns which should be valid for the function.\n",
            "  top_five = top_performers.groupby('Category').sum().sort_values(by='Score',ascending=False)\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                Score\n",
              "Category             \n",
              "animals         74965\n",
              "science         71168\n",
              "healthy eating  69339\n",
              "technology      68738\n",
              "food            66676"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-f438cb0f-2d4a-47d0-9f14-5bb3c1468616\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Score</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Category</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>animals</th>\n",
              "      <td>74965</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>science</th>\n",
              "      <td>71168</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>healthy eating</th>\n",
              "      <td>69339</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>technology</th>\n",
              "      <td>68738</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>food</th>\n",
              "      <td>66676</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-f438cb0f-2d4a-47d0-9f14-5bb3c1468616')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-f438cb0f-2d4a-47d0-9f14-5bb3c1468616 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-f438cb0f-2d4a-47d0-9f14-5bb3c1468616');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 78
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "* There's our answer - the top five categories by aggregate popularity are animals, science, healthy eating, technology, and food. We can export this cleaned dataset to a CSV to share with the client.\n",
        "\n"
      ],
      "metadata": {
        "id": "9ps4ByGiStda"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "top_performers.to_csv('client_data.csv')"
      ],
      "metadata": {
        "id": "R6-GT4MzSTWP"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "AW_3Jt2XTXiv"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}