Copy_of_RinadHassan_stc_TV_T1.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "**Rinad Haasan Almjishai**"
      ],
      "metadata": {
        "id": "zhvM8xhPJXZk"
      },
      "id": "zhvM8xhPJXZk"
    },
    {
      "cell_type": "markdown",
      "source": [
        "#**Goals:**\n",
        "\n",
        "1. *Describe the statistical values for the given data set, including:*\n",
        "  - *Arithmetic mean*\n",
        "  - *Standard deviation*\n",
        "  - *Maximum and minimum values*\n",
        "\n",
        "2. *Classify and analyze viewer categories according to the program type (Program Class) - whether it’s a movie or a series.*\n",
        "3. *Study the viewing patterns of users and identify the category that watches stc TV in standard definition (SD) quality versus the category that watches it in high definition (HD).*\n"
      ],
      "metadata": {
        "id": "Y0uHS71OplN8"
      },
      "id": "Y0uHS71OplN8"
    },
    {
      "cell_type": "markdown",
      "id": "d66c1c6c",
      "metadata": {
        "id": "d66c1c6c"
      },
      "source": [
        "# STC Jawwy"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "- installing & importing the libraries\n",
        "\n",
        "---\n",
        "\n"
      ],
      "metadata": {
        "id": "zMEcnYZoKqJ2"
      },
      "id": "zMEcnYZoKqJ2"
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "d3bcd889",
      "metadata": {
        "id": "d3bcd889"
      },
      "outputs": [],
      "source": [
        "!pip install pyxlsb\n",
        "import pandas as pd\n",
        "import pyxlsb\n",
        "import numpy as np\n",
        "import matplotlib.pyplot as plt\n",
        "import plotly.express as px\n",
        "import plotly.graph_objects as go"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "df7dab59",
      "metadata": {
        "id": "df7dab59"
      },
      "outputs": [],
      "source": [
        "# Import the required libraries\n",
        "\"\"\"\n",
        "Please feel free to import any required libraries as per your needs\n",
        "\"\"\"\n",
        "import pandas as pd     # provides high-performance, easy to use structures and data analysis tools\n",
        "import pyxlsb           # Excel extention to read xlsb files (the input file)\n",
        "import numpy as np      # provides fast mathematical computation on arrays and matrices"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "8679536d",
      "metadata": {
        "id": "8679536d"
      },
      "source": [
        "# Jawwy dataset\n",
        "The dataset consists of meta details about the movies and tv shows as genre.\n",
        "Also details about Users activities, spent duration and if watching in High definition or standard definition.\n",
        "You have to analyse this dataset to find top insights, findings and to solve the four tasks assigned to you."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "1bcc5b5b",
      "metadata": {
        "id": "1bcc5b5b",
        "collapsed": true
      },
      "outputs": [],
      "source": [
        "dataframe = pd.read_excel(\"stc TV Data Set_T1.xlsb\",sheet_name=\"Final_Dataset\")\n",
        "# Please make a copy of dataset if you are going to work directly and make changes on the dataset\n",
        "# you can use\n",
        "df=dataframe.copy()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "fc78b44d",
      "metadata": {
        "id": "fc78b44d"
      },
      "outputs": [],
      "source": [
        "# check the data shape\n",
        "df.shape"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "e2983d5e",
      "metadata": {
        "id": "e2983d5e"
      },
      "outputs": [],
      "source": [
        "# display the first 5 rows\n",
        "df.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "27c4c4e9",
      "metadata": {
        "id": "27c4c4e9"
      },
      "outputs": [],
      "source": [
        "# Dropping the index column , i'm assuming this is the PK\n",
        "df = df.drop(columns=['Column1'])\n",
        "# Trimming spaces in program names\n",
        "df['program_name'] = df['program_name'].str.strip()\n",
        "# Converting date column to datetime\n",
        "df['date_'] = pd.to_datetime(df['date_'], infer_datetime_format=True, errors='coerce')\n",
        "# Converting specific columns to numeric data types\n",
        "numeric_cols = ['duration_seconds', 'season', 'episode', 'series_title', 'hd']\n",
        "df[numeric_cols] = df[numeric_cols].apply(pd.to_numeric)\n",
        "# Converting specific columns to string data types\n",
        "string_cols = ['user_id_maped', 'program_name', 'program_class', 'program_desc', 'program_genre', 'original_name']\n",
        "df[string_cols] = df[string_cols].astype(str)\n",
        "# Display the dataset after applying data types\n",
        "print(df.head())\n",
        "# Checking for null values\n",
        "print(df.isnull().any())\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "693f89af",
      "metadata": {
        "id": "693f89af"
      },
      "outputs": [],
      "source": [
        "# display the dataset after applying data types\n",
        "df.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Statistical Summary** of Numeric Columns"
      ],
      "metadata": {
        "id": "CKGht7ggn3Cs"
      },
      "id": "CKGht7ggn3Cs"
    },
    {
      "cell_type": "code",
      "source": [
        "stat_summary = df.describe()\n",
        "arithmetic_mean = stat_summary.loc['mean']\n",
        "standard_deviation = stat_summary.loc['std']\n",
        "maximum_values = stat_summary.loc['max']\n",
        "minimum_values = stat_summary.loc['min']\n",
        "print(\"\\nStatistical Summary:\")\n",
        "print(\"Arithmetic Mean:\\n\", arithmetic_mean)\n",
        "print(\"\\nStandard Deviation:\\n\", standard_deviation)\n",
        "print(\"\\nMaximum Values:\\n\", maximum_values)\n",
        "print(\"\\nMinimum Values:\\n\", minimum_values)"
      ],
      "metadata": {
        "id": "wN9BwhNEnlvC"
      },
      "id": "wN9BwhNEnlvC",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f71db3ad",
      "metadata": {
        "id": "f71db3ad"
      },
      "outputs": [],
      "source": [
        "# check if any column has null value in the dataset\n",
        "df.isnull().any() # already did , but im leaving this cell in its place"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "4913b7a9",
      "metadata": {
        "id": "4913b7a9"
      },
      "source": [
        "# Task 1\n",
        "##### You are required to work on task one to study and HD flag for available dataset"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "dc60d1cb",
      "metadata": {
        "id": "dc60d1cb"
      },
      "outputs": [],
      "source": [
        "# make a copy of the dataframe for working on task 1\n",
        "df=dataframe.copy()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "c93eace9",
      "metadata": {
        "id": "c93eace9"
      },
      "outputs": [],
      "source": [
        "# Here we try to get the most watched movies (Total Views / Total Users Views / Total watch time)\n",
        "# For series we concatenated the Session episode to differentiate between episodes\n",
        "grouped=df.copy()\n",
        "grouped.loc[grouped['program_class'] == 'SERIES/EPISODES', 'program_name'] = grouped['program_name']+'_SE'+grouped['season'].astype(str)+'_EP'+grouped['episode'].astype(str)\n",
        "grouped = grouped.groupby(['program_name','program_class'])\\\n",
        ".agg({'user_id_maped': [('co1', 'nunique'),('co2', 'count')],\\\n",
        "      'duration_seconds': [('co3', 'sum')] }).reset_index()\n",
        "grouped.columns = ['program_name','program_class','No of Users who Watched', 'No of watches', 'Total watch time in seconds']\n",
        "grouped['Total watch time in houres']=grouped['Total watch time in seconds']/3600\n",
        "grouped = grouped.drop(columns=['Total watch time in seconds'])\n",
        "grouped = grouped.sort_values(by=['Total watch time in houres', 'No of watches','No of Users who Watched'], ascending=False).reset_index(drop=True)\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "eec3926e",
      "metadata": {
        "id": "eec3926e"
      },
      "outputs": [],
      "source": [
        "# show the result\n",
        "grouped.head(35)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "b9d9d726",
      "metadata": {
        "id": "b9d9d726"
      },
      "outputs": [],
      "source": [
        "# we import Visualization libraries\n",
        "# you can ignore and use any other graphing libraries\n",
        "import matplotlib.pyplot as plt # a comprehensive library for creating static, animated, and interactive visualizations\n",
        "import plotly #a graphing library makes interactive, publication-quality graphs. Examples of how to make line plots, scatter plots, area charts, bar charts, error bars, box plots, histograms, heatmaps, subplots, multiple-axes, polar charts, and bubble charts.\n",
        "import plotly.express as px\n",
        "import plotly.graph_objects as go\n",
        "from plotly.subplots import make_subplots"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "5528722b",
      "metadata": {
        "id": "5528722b"
      },
      "outputs": [],
      "source": [
        "# plot top 10 Programs\n",
        "fig = px.pie(grouped.head(10), values='Total watch time in houres', names='program_name',\\\n",
        "             hover_data=['program_class'],title='top 10 programs in total watch time in houres')\n",
        "fig.show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "ec87fef6",
      "metadata": {
        "id": "ec87fef6"
      },
      "outputs": [],
      "source": [
        "# Here we try to study the customer experience against Program class\n",
        "grouped=df.copy()\n",
        "grouped = grouped.groupby('program_class')\\\n",
        ".agg({'user_id_maped': [('co1', 'nunique'),('co2', 'count')],\\\n",
        "      'duration_seconds': [('co3', 'sum')] }).reset_index()\n",
        "grouped.columns = ['program_class','No of Users who Watched', 'No of watches', 'Total watch time in seconds']\n",
        "grouped['Total watch time in houres']=grouped['Total watch time in seconds']/3600\n",
        "grouped = grouped.drop(columns=['Total watch time in seconds'])\n",
        "grouped = grouped.sort_values(by=['Total watch time in houres', 'No of watches','No of Users who Watched'], ascending=False).reset_index(drop=True)\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "159cab9a",
      "metadata": {
        "id": "159cab9a"
      },
      "outputs": [],
      "source": [
        "# show the result\n",
        "grouped.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "e569c07c",
      "metadata": {
        "id": "e569c07c"
      },
      "outputs": [],
      "source": [
        "# plot the total watch time against total number of users and report your findings\n",
        "fig = px.pie(grouped, values='Total watch time in houres', names='program_class',\\\n",
        "             hover_data=['program_class'],title='Total duration spent by program_class')\n",
        "fig2 = px.pie(grouped, values='No of Users who Watched', names='program_class',\\\n",
        "             hover_data=['program_class'],title='Total Users watching by program_class')\n",
        "\n",
        "fig.update_traces(sort=False)\n",
        "fig2.update_traces(sort=False)\n",
        "fig.show()\n",
        "fig2.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "------\n",
        "# My Work"
      ],
      "metadata": {
        "id": "NDSGlwIKirsg"
      },
      "id": "NDSGlwIKirsg"
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Task 1:** Classify and Analyze Viewer Categories (Movies vs Series)"
      ],
      "metadata": {
        "id": "92eWQrpTnFZ6"
      },
      "id": "92eWQrpTnFZ6"
    },
    {
      "cell_type": "code",
      "source": [
        "# Classify and analyze viewer categories\n",
        "movie_count = df[df['program_class'] == 'MOVIE'].shape[0]\n",
        "series_count = df[df['program_class'] == 'SERIES/EPISODES'].shape[0]\n",
        "\n",
        "print(\"\\nViewer category analysis:\")\n",
        "print(\"Number of movies:\", movie_count)\n",
        "print(\"Number of series:\", series_count)\n"
      ],
      "metadata": {
        "id": "Jw90bhX3iw11"
      },
      "id": "Jw90bhX3iw11",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Task 2:** Study Viewing Patterns by HD Flag"
      ],
      "metadata": {
        "id": "ia1reiiSnKzx"
      },
      "id": "ia1reiiSnKzx"
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "0f204d57",
      "metadata": {
        "id": "0f204d57"
      },
      "outputs": [],
      "source": [
        "# Grouping data by HD flag\n",
        "grouped_hd = df.groupby('hd').agg({\n",
        "    'user_id_maped': 'nunique',  # Number of unique users\n",
        "    'duration_seconds': 'sum'    # Total watch time in seconds\n",
        "}).reset_index()\n",
        "\n",
        "grouped_hd.columns = ['HD Flag', 'Number of Unique Users', 'Total Watch Time in Seconds']\n",
        "\n",
        "# Converting total watch time from seconds to hours\n",
        "grouped_hd['Total Watch Time in Hours'] = grouped_hd['Total Watch Time in Seconds'] / 3600\n",
        "\n",
        "# Dropping the seconds column as it's no longer needed\n",
        "grouped_hd = grouped_hd.drop(columns=['Total Watch Time in Seconds'])\n",
        "print(grouped_hd)\n"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "hd_flag_data = {\n",
        "    'HD Flag': ['SD', 'HD'],\n",
        "    'Number of Unique Users': [6728, 11000],\n",
        "    'Total Watch Time in Hours': [268364.372778, 90177.560278]\n",
        "}\n",
        "\n",
        "df_hd_flag = pd.DataFrame(hd_flag_data)\n",
        "\n",
        "# Print summarized findings\n",
        "print(\"Summary of Findings:\\n\")\n",
        "print(df_hd_flag)\n",
        "\n",
        "# Plotting the results\n",
        "fig = make_subplots(rows=1, cols=2, subplot_titles=(\"Number of Unique Users by HD Flag\", \"Total Watch Time in Hours by HD Flag\"))\n",
        "\n",
        "# Plot 1: Number of Unique Users by HD Flag\n",
        "fig.add_trace(\n",
        "    go.Bar(x=df_hd_flag['HD Flag'], y=df_hd_flag['Number of Unique Users'], name=\"Number of Unique Users\"),\n",
        "    row=1, col=1\n",
        ")\n",
        "\n",
        "# Plot 2: Total Watch Time by HD Flag\n",
        "fig.add_trace(\n",
        "    go.Bar(x=df_hd_flag['HD Flag'], y=df_hd_flag['Total Watch Time in Hours'], name=\"Total Watch Time (Hours)\"),\n",
        "    row=1, col=2\n",
        ")\n",
        "\n",
        "fig.update_layout(\n",
        "    title_text=\"Analysis of User Behavior Against HD Flag\",\n",
        "    xaxis=dict(title='HD Flag'),\n",
        "    yaxis=dict(title='Count or Hours')\n",
        "\n",
        ")\n",
        "\n",
        "# Display the plots\n",
        "fig.show()"
      ],
      "metadata": {
        "id": "XCseWNj6h_rP"
      },
      "id": "XCseWNj6h_rP",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# **Findings:**"
      ],
      "metadata": {
        "id": "RtossDmxn8V8"
      },
      "id": "RtossDmxn8V8"
    },
    {
      "cell_type": "markdown",
      "source": [
        "1-####\n"
      ],
      "metadata": {
        "id": "07UXgoH4oAAS"
      },
      "id": "07UXgoH4oAAS"
    },
    {
      "cell_type": "markdown",
      "source": [
        "2-####\n"
      ],
      "metadata": {
        "id": "9mfb32BOoQtC"
      },
      "id": "9mfb32BOoQtC"
    },
    {
      "cell_type": "markdown",
      "source": [
        "# **Possible Explanations:**"
      ],
      "metadata": {
        "id": "5eN2F8LYoV5H"
      },
      "id": "5eN2F8LYoV5H"
    },
    {
      "cell_type": "markdown",
      "source": [
        "- SD content might be more accessible to users with slower internet connections or those using devices that do not support HD.\n",
        "- The type of content available in SD versus HD might differ. For example, longer-form content or older content might only be available in SD, which could explain the higher total watch time for SD."
      ],
      "metadata": {
        "id": "qDm_Gbcwodog"
      },
      "id": "qDm_Gbcwodog"
    },
    {
      "cell_type": "markdown",
      "source": [
        "# **Recommendations:**"
      ],
      "metadata": {
        "id": "7q1jeoBNouFq"
      },
      "id": "7q1jeoBNouFq"
    },
    {
      "cell_type": "markdown",
      "source": [
        "1- Increase HD Content: Given the higher number of unique users for HD, consider expanding the HD content library to cater to this larger audience.\n",
        "\n",
        "2- Enhance SD Offerings: Maintain and possibly enhance the SD content library to keep the highly engaged users who prefer SD content."
      ],
      "metadata": {
        "id": "71zZCXI6o1MS"
      },
      "id": "71zZCXI6o1MS"
    },
    {
      "cell_type": "markdown",
      "source": [
        "3-Enhance Streaming Quality: Enhance the quality of streaming services by minimizing buffering and optimizing HD content for extended viewing periods.\n",
        "\n",
        "4-Adaptive Streaming Capabilities: Introduce adaptive streaming technologies that enable users to effortlessly switch between HD and SD formats, ensuring a seamless viewing experience tailored to their internet connection speeds."
      ],
      "metadata": {
        "id": "dtNG5i3Do_5V"
      },
      "id": "dtNG5i3Do_5V"
    },
    {
      "cell_type": "markdown",
      "source": [
        "# ***Thank you for reading, Rinad Hassan***"
      ],
      "metadata": {
        "id": "vbMd3sCopPAy"
      },
      "id": "vbMd3sCopPAy"
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3 (ipykernel)",
      "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"
    },
    "colab": {
      "provenance": []
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}