data-processing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "75a34d89",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "import seaborn as sns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "4d6ce559",
   "metadata": {},
   "outputs": [],
   "source": [
    "#load data\n",
    "content = pd.read_csv('/Users/jiemi/Desktop/accenture project/Content.csv')\n",
    "reactions = pd.read_csv('/Users/jiemi/Desktop/accenture project/Reactions.csv')\n",
    "reaction_types = pd.read_csv('/Users/jiemi/Desktop/accenture project/ReactionTypes.csv')\n",
    "profile= pd.read_csv('/Users/jiemi/Desktop/accenture project/Profile.csv')\n",
    "session = pd.read_csv('/Users/jiemi/Desktop/accenture project/Session.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "d055d12f",
   "metadata": {},
   "outputs": [],
   "source": [
    "content.drop('Unnamed: 0',axis=1,inplace=True)\n",
    "reactions.drop(['Unnamed: 0','User ID'],axis=1,inplace=True)\n",
    "reaction_types.drop('Unnamed: 0',axis=1,inplace=True)\n",
    "profile.drop('Unnamed: 0',axis=1,inplace=True)\n",
    "session.drop('Unnamed: 0',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "a77d02e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = content.merge(profile, how='left', on='User ID')\n",
    "df = df.merge(session, how='left', on='User ID')\n",
    "df = df.merge(reactions, how='left', on='Content ID')\n",
    "df = df.rename(columns={'Type_x':'Cont_Type','Type_y':'Type'})\n",
    "df = df.merge(reaction_types, how='left', on='Type')\n",
    "df = df.rename(columns={'Type':'React_Type'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "4ccc4717",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Content ID</th>\n",
       "      <th>User ID</th>\n",
       "      <th>Cont_Type</th>\n",
       "      <th>Category</th>\n",
       "      <th>URL</th>\n",
       "      <th>Interests</th>\n",
       "      <th>Age</th>\n",
       "      <th>Device</th>\n",
       "      <th>Duration</th>\n",
       "      <th>React_Type</th>\n",
       "      <th>Datetime</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>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",
       "      <td>['education', 'studying']</td>\n",
       "      <td>13</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-04-22 15:17:15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</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",
       "      <td>['education', 'studying']</td>\n",
       "      <td>13</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>10</td>\n",
       "      <td>disgust</td>\n",
       "      <td>2020-11-07 09:43:50</td>\n",
       "      <td>negative</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</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",
       "      <td>['education', 'studying']</td>\n",
       "      <td>13</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>10</td>\n",
       "      <td>dislike</td>\n",
       "      <td>2021-06-17 12:22:51</td>\n",
       "      <td>negative</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</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",
       "      <td>['education', 'studying']</td>\n",
       "      <td>13</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>10</td>\n",
       "      <td>scared</td>\n",
       "      <td>2021-04-18 05:13:58</td>\n",
       "      <td>negative</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</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",
       "      <td>['education', 'studying']</td>\n",
       "      <td>13</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>10</td>\n",
       "      <td>disgust</td>\n",
       "      <td>2021-01-06 19:13:01</td>\n",
       "      <td>negative</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             Content ID                               User ID  \\\n",
       "0  97522e57-d9ab-4bd6-97bf-c24d952602d2  8d3cd87d-8a31-4935-9a4f-b319bfe05f31   \n",
       "1  97522e57-d9ab-4bd6-97bf-c24d952602d2  8d3cd87d-8a31-4935-9a4f-b319bfe05f31   \n",
       "2  97522e57-d9ab-4bd6-97bf-c24d952602d2  8d3cd87d-8a31-4935-9a4f-b319bfe05f31   \n",
       "3  97522e57-d9ab-4bd6-97bf-c24d952602d2  8d3cd87d-8a31-4935-9a4f-b319bfe05f31   \n",
       "4  97522e57-d9ab-4bd6-97bf-c24d952602d2  8d3cd87d-8a31-4935-9a4f-b319bfe05f31   \n",
       "\n",
       "  Cont_Type  Category                                                URL  \\\n",
       "0     photo  Studying  https://socialbuzz.cdn.com/content/storage/975...   \n",
       "1     photo  Studying  https://socialbuzz.cdn.com/content/storage/975...   \n",
       "2     photo  Studying  https://socialbuzz.cdn.com/content/storage/975...   \n",
       "3     photo  Studying  https://socialbuzz.cdn.com/content/storage/975...   \n",
       "4     photo  Studying  https://socialbuzz.cdn.com/content/storage/975...   \n",
       "\n",
       "                   Interests  Age     Device  Duration React_Type  \\\n",
       "0  ['education', 'studying']   13  Microsoft        10        NaN   \n",
       "1  ['education', 'studying']   13  Microsoft        10    disgust   \n",
       "2  ['education', 'studying']   13  Microsoft        10    dislike   \n",
       "3  ['education', 'studying']   13  Microsoft        10     scared   \n",
       "4  ['education', 'studying']   13  Microsoft        10    disgust   \n",
       "\n",
       "              Datetime Sentiment  Score  \n",
       "0  2021-04-22 15:17:15       NaN    NaN  \n",
       "1  2020-11-07 09:43:50  negative    0.0  \n",
       "2  2021-06-17 12:22:51  negative   10.0  \n",
       "3  2021-04-18 05:13:58  negative   15.0  \n",
       "4  2021-01-06 19:13:01  negative    0.0  "
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "9f0de265",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 25573 entries, 0 to 25572\n",
      "Data columns (total 13 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   Content ID  25573 non-null  object \n",
      " 1   User ID     25573 non-null  object \n",
      " 2   Cont_Type   25573 non-null  object \n",
      " 3   Category    25573 non-null  object \n",
      " 4   URL         20857 non-null  object \n",
      " 5   Interests   25573 non-null  object \n",
      " 6   Age         25573 non-null  int64  \n",
      " 7   Device      25573 non-null  object \n",
      " 8   Duration    25573 non-null  int64  \n",
      " 9   React_Type  24573 non-null  object \n",
      " 10  Datetime    25553 non-null  object \n",
      " 11  Sentiment   24573 non-null  object \n",
      " 12  Score       24573 non-null  float64\n",
      "dtypes: float64(1), int64(2), object(10)\n",
      "memory usage: 2.7+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "da22a287",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Content ID       0\n",
       "User ID          0\n",
       "Cont_Type        0\n",
       "Category         0\n",
       "URL           4716\n",
       "Interests        0\n",
       "Age              0\n",
       "Device           0\n",
       "Duration         0\n",
       "React_Type    1000\n",
       "Datetime        20\n",
       "Sentiment     1000\n",
       "Score         1000\n",
       "dtype: int64"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "abea6786",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(['URL','Interests'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "a3e45aa6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Content ID</th>\n",
       "      <th>User ID</th>\n",
       "      <th>Cont_Type</th>\n",
       "      <th>Category</th>\n",
       "      <th>Age</th>\n",
       "      <th>Device</th>\n",
       "      <th>Duration</th>\n",
       "      <th>React_Type</th>\n",
       "      <th>Datetime</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>8d3cd87d-8a31-4935-9a4f-b319bfe05f31</td>\n",
       "      <td>photo</td>\n",
       "      <td>Studying</td>\n",
       "      <td>13</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-04-22 15:17:15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</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>13</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>10</td>\n",
       "      <td>disgust</td>\n",
       "      <td>2020-11-07 09:43:50</td>\n",
       "      <td>negative</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</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>13</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>10</td>\n",
       "      <td>dislike</td>\n",
       "      <td>2021-06-17 12:22:51</td>\n",
       "      <td>negative</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</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>13</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>10</td>\n",
       "      <td>scared</td>\n",
       "      <td>2021-04-18 05:13:58</td>\n",
       "      <td>negative</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</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>13</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>10</td>\n",
       "      <td>disgust</td>\n",
       "      <td>2021-01-06 19:13:01</td>\n",
       "      <td>negative</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             Content ID                               User ID  \\\n",
       "0  97522e57-d9ab-4bd6-97bf-c24d952602d2  8d3cd87d-8a31-4935-9a4f-b319bfe05f31   \n",
       "1  97522e57-d9ab-4bd6-97bf-c24d952602d2  8d3cd87d-8a31-4935-9a4f-b319bfe05f31   \n",
       "2  97522e57-d9ab-4bd6-97bf-c24d952602d2  8d3cd87d-8a31-4935-9a4f-b319bfe05f31   \n",
       "3  97522e57-d9ab-4bd6-97bf-c24d952602d2  8d3cd87d-8a31-4935-9a4f-b319bfe05f31   \n",
       "4  97522e57-d9ab-4bd6-97bf-c24d952602d2  8d3cd87d-8a31-4935-9a4f-b319bfe05f31   \n",
       "\n",
       "  Cont_Type  Category  Age     Device  Duration React_Type  \\\n",
       "0     photo  Studying   13  Microsoft        10        NaN   \n",
       "1     photo  Studying   13  Microsoft        10    disgust   \n",
       "2     photo  Studying   13  Microsoft        10    dislike   \n",
       "3     photo  Studying   13  Microsoft        10     scared   \n",
       "4     photo  Studying   13  Microsoft        10    disgust   \n",
       "\n",
       "              Datetime Sentiment  Score  \n",
       "0  2021-04-22 15:17:15       NaN    NaN  \n",
       "1  2020-11-07 09:43:50  negative    0.0  \n",
       "2  2021-06-17 12:22:51  negative   10.0  \n",
       "3  2021-04-18 05:13:58  negative   15.0  \n",
       "4  2021-01-06 19:13:01  negative    0.0  "
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "c2254aca",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "3cd26696",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 24573 entries, 1 to 25572\n",
      "Data columns (total 11 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   Content ID  24573 non-null  object \n",
      " 1   User ID     24573 non-null  object \n",
      " 2   Cont_Type   24573 non-null  object \n",
      " 3   Category    24573 non-null  object \n",
      " 4   Age         24573 non-null  int64  \n",
      " 5   Device      24573 non-null  object \n",
      " 6   Duration    24573 non-null  int64  \n",
      " 7   React_Type  24573 non-null  object \n",
      " 8   Datetime    24573 non-null  object \n",
      " 9   Sentiment   24573 non-null  object \n",
      " 10  Score       24573 non-null  float64\n",
      "dtypes: float64(1), int64(2), object(8)\n",
      "memory usage: 2.2+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "79b34799",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Content ID</th>\n",
       "      <th>User ID</th>\n",
       "      <th>Cont_Type</th>\n",
       "      <th>Category</th>\n",
       "      <th>Age</th>\n",
       "      <th>Device</th>\n",
       "      <th>Duration</th>\n",
       "      <th>React_Type</th>\n",
       "      <th>Datetime</th>\n",
       "      <th>Sentiment</th>\n",
       "      <th>Score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</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>13</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>10</td>\n",
       "      <td>disgust</td>\n",
       "      <td>2020-11-07 09:43:50</td>\n",
       "      <td>negative</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</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>13</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>10</td>\n",
       "      <td>dislike</td>\n",
       "      <td>2021-06-17 12:22:51</td>\n",
       "      <td>negative</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</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>13</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>10</td>\n",
       "      <td>scared</td>\n",
       "      <td>2021-04-18 05:13:58</td>\n",
       "      <td>negative</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</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>13</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>10</td>\n",
       "      <td>disgust</td>\n",
       "      <td>2021-01-06 19:13:01</td>\n",
       "      <td>negative</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</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>13</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>10</td>\n",
       "      <td>interested</td>\n",
       "      <td>2020-08-23 12:25:58</td>\n",
       "      <td>positive</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             Content ID                               User ID  \\\n",
       "1  97522e57-d9ab-4bd6-97bf-c24d952602d2  8d3cd87d-8a31-4935-9a4f-b319bfe05f31   \n",
       "2  97522e57-d9ab-4bd6-97bf-c24d952602d2  8d3cd87d-8a31-4935-9a4f-b319bfe05f31   \n",
       "3  97522e57-d9ab-4bd6-97bf-c24d952602d2  8d3cd87d-8a31-4935-9a4f-b319bfe05f31   \n",
       "4  97522e57-d9ab-4bd6-97bf-c24d952602d2  8d3cd87d-8a31-4935-9a4f-b319bfe05f31   \n",
       "5  97522e57-d9ab-4bd6-97bf-c24d952602d2  8d3cd87d-8a31-4935-9a4f-b319bfe05f31   \n",
       "\n",
       "  Cont_Type  Category  Age     Device  Duration  React_Type  \\\n",
       "1     photo  Studying   13  Microsoft        10     disgust   \n",
       "2     photo  Studying   13  Microsoft        10     dislike   \n",
       "3     photo  Studying   13  Microsoft        10      scared   \n",
       "4     photo  Studying   13  Microsoft        10     disgust   \n",
       "5     photo  Studying   13  Microsoft        10  interested   \n",
       "\n",
       "              Datetime Sentiment  Score  \n",
       "1  2020-11-07 09:43:50  negative    0.0  \n",
       "2  2021-06-17 12:22:51  negative   10.0  \n",
       "3  2021-04-18 05:13:58  negative   15.0  \n",
       "4  2021-01-06 19:13:01  negative    0.0  \n",
       "5  2020-08-23 12:25:58  positive   30.0  "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "60fe0420",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Studying', 'healthy eating', 'technology', 'food', 'cooking',\n",
       "       'dogs', 'soccer', 'public speaking', 'science', 'tennis', 'travel',\n",
       "       'fitness', 'education', 'studying', 'veganism', 'Animals',\n",
       "       'animals', 'culture', '\"culture\"', 'Fitness', '\"studying\"',\n",
       "       'Veganism', '\"animals\"', 'Travel', '\"soccer\"', 'Education',\n",
       "       '\"dogs\"', 'Technology', 'Soccer', 'Culture', '\"food\"', 'Food',\n",
       "       '\"technology\"', 'Healthy Eating', '\"cooking\"', 'Science',\n",
       "       '\"public speaking\"', '\"veganism\"', 'Public Speaking', '\"science\"'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Category'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "495f9f18",
   "metadata": {},
   "outputs": [],
   "source": [
    "#address the reapted column (especially for categorical varibale)!!\n",
    "df['Category']= df['Category'].replace('\"', '', regex=True)\n",
    "df['Category']=df['Category'].str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "77f4d282",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['studying', 'healthy eating', 'technology', 'food', 'cooking',\n",
       "       'dogs', 'soccer', 'public speaking', 'science', 'tennis', 'travel',\n",
       "       'fitness', 'education', 'veganism', 'animals', 'culture'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Category'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "7ea5b26b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('data.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "e8d6bcac",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Category</th>\n",
       "      <th>React_Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>animals</td>\n",
       "      <td>1897</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>science</td>\n",
       "      <td>1796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>healthy eating</td>\n",
       "      <td>1717</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>food</td>\n",
       "      <td>1699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>technology</td>\n",
       "      <td>1698</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Category  React_Type\n",
       "0          animals        1897\n",
       "9          science        1796\n",
       "7   healthy eating        1717\n",
       "6             food        1699\n",
       "12      technology        1698"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('Category').agg({\"React_Type\" : \"count\"}).reset_index().sort_values(by='React_Type', ascending=False).head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "c85b9855",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Category</th>\n",
       "      <th>Score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>animals</td>\n",
       "      <td>74965.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>science</td>\n",
       "      <td>71168.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>healthy eating</td>\n",
       "      <td>69339.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>technology</td>\n",
       "      <td>68738.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>food</td>\n",
       "      <td>66676.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Category    Score\n",
       "0          animals  74965.0\n",
       "9          science  71168.0\n",
       "7   healthy eating  69339.0\n",
       "12      technology  68738.0\n",
       "6             food  66676.0"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('Category').agg({\"Score\" : \"sum\"}).reset_index().sort_values(by='Score', ascending=False).head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}