desafio_engenharia_dados_solvimm.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "desafio_engenharia_dados_solvimm.ipynb",
      "provenance": [],
      "authorship_tag": "ABX9TyPN0Ua6tmPAiBUnH+yrjLGU",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/caio-emiliano/desafio_solvimm/blob/main/desafio_engenharia_dados_solvimm.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Cd2dkqJFrFj4"
      },
      "source": [
        "#Análise dos Dados 5GFlix"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PkXD0EYprpmq"
      },
      "source": [
        "Para o estudo de mercado do aplicativo 5GFlix, nosso objetivo será responder algumas questões e obter *insights* através dos dados fornecidos pelo *database*.\n",
        "\n",
        "Para isso, deveremos ser capazes de responder as seguintes perguntas:\n",
        "\n",
        "\n",
        "\n",
        "1.   Quantos filmes estão disponíveis no dataset?\n",
        "2.   Qual é o nome dos 5 filmes com melhor média de avaliação?\n",
        "3.   Quais os 5 anos com menos lançamentos de filmes?\n",
        "4.   Quantos filmes que possuem avaliação maior ou igual a 4.7, considerando apenas os filmes avaliados na última data de avaliação do dataset?\n",
        "5.  Dos filmes encontrados na questão anterior, quais são os 10 filmes com as piores notas e quais as notas?\n",
        "6.  Quais os id's dos 5 customer que mais avaliaram filmes e quantas avaliações cada um fez?\n",
        "\n",
        "Obs: Este notebook se resolve sozinho. Deste que as bibliotecas importadas estejam instaladas, o código rodará sem que seja necessário criar pastas ou editar arquivos."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "j0gsgwgu7MaG"
      },
      "source": [
        "#Importar as bibliotecas necessárias para o código\n",
        "\n",
        "import pandas as pd"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iEnVtNtC7v45"
      },
      "source": [
        "#Importar os arquivos CSVs deste cenário. Upei os arquivos no Google Drive e os leio via link para que o código seja executável por qualquer um (apenas por simplicidade)\n",
        "\n",
        "movies_path = \"https://www.googleapis.com/drive/v3/files/1tenHUdbV7Zy2ggauVAK-6PzHHIrbYcR8?alt=media&key=AIzaSyDbIgqySdMFMP085-zVCVPNHFIoJpuhVRo\"\n",
        "df_movies = pd.read_csv(movies_path, sep=';', names=['movie_id', 'name-year']) #Leitura do arquivo csv. O parâmetro sep é para indicar que o delimitador deste csv é o ';'. O parâmetro names indica o nome das colunas \n",
        "                                                                                                                                                                            #que serão criadas no dataset\n",
        "\n",
        "customersRating_path = \"https://www.googleapis.com/drive/v3/files/1jYxQa6gGzUksidziQWLTAou3KXwFlPvy?alt=media&key=AIzaSyDbIgqySdMFMP085-zVCVPNHFIoJpuhVRo\"\n",
        "df_customers = pd.read_csv(customersRating_path, sep=';') #Leitura do arquivo csv. O parâmetro sep é para indicar que o delimitador deste csv é o ';'."
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        },
        "id": "mChwzIFl-Exh",
        "outputId": "6d8914aa-d461-4bbb-fca0-28bf5d757e73"
      },
      "source": [
        "df_movies"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>movie_id</th>\n",
              "      <th>name-year</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>(Dinosaur Planet, 2003)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2</td>\n",
              "      <td>(Isle of Man TT 2004 Review, 2004)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3</td>\n",
              "      <td>(Character, 1997)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4</td>\n",
              "      <td>(Paula Abdul's Get Up &amp; Dance, 1994)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5</td>\n",
              "      <td>(The Rise and Fall of ECW, 2004)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4494</th>\n",
              "      <td>4495</td>\n",
              "      <td>(Clifford: Happy Birthday Clifford / Puppy Lov...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4495</th>\n",
              "      <td>4496</td>\n",
              "      <td>(Farewell My Concubine, 1993)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4496</th>\n",
              "      <td>4497</td>\n",
              "      <td>(Texasville, 1990)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4497</th>\n",
              "      <td>4498</td>\n",
              "      <td>(Gonin, 2000)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4498</th>\n",
              "      <td>4499</td>\n",
              "      <td>(In My Skin, 2002)</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>4499 rows × 2 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "      movie_id                                          name-year\n",
              "0            1                            (Dinosaur Planet, 2003)\n",
              "1            2                 (Isle of Man TT 2004 Review, 2004)\n",
              "2            3                                  (Character, 1997)\n",
              "3            4               (Paula Abdul's Get Up & Dance, 1994)\n",
              "4            5                   (The Rise and Fall of ECW, 2004)\n",
              "...        ...                                                ...\n",
              "4494      4495  (Clifford: Happy Birthday Clifford / Puppy Lov...\n",
              "4495      4496                      (Farewell My Concubine, 1993)\n",
              "4496      4497                                 (Texasville, 1990)\n",
              "4497      4498                                      (Gonin, 2000)\n",
              "4498      4499                                 (In My Skin, 2002)\n",
              "\n",
              "[4499 rows x 2 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 3
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gBdvHbtB-IV9"
      },
      "source": [
        "Se observarmos a base de dados acima, perceberemos que nome e ano encontram-se juntos. Precisaremos separar essa informação e dividi-la em 2 colunas.\n",
        "\n",
        "Para isso, usaremos o comando split para separarmos na ocorrência de vírgula, e criaremos 2 novas colunas para o dataframe receber essa informação."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        },
        "id": "N8YgvGve-oTq",
        "outputId": "92e0f049-279e-4495-9c2b-3199cc7c0538"
      },
      "source": [
        "df_movies[['name', 'year']] = df_movies['name-year'].str.split(',', n=1, expand=True)\n",
        "df_movies"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>movie_id</th>\n",
              "      <th>name-year</th>\n",
              "      <th>name</th>\n",
              "      <th>year</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>(Dinosaur Planet, 2003)</td>\n",
              "      <td>(Dinosaur Planet</td>\n",
              "      <td>2003)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2</td>\n",
              "      <td>(Isle of Man TT 2004 Review, 2004)</td>\n",
              "      <td>(Isle of Man TT 2004 Review</td>\n",
              "      <td>2004)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3</td>\n",
              "      <td>(Character, 1997)</td>\n",
              "      <td>(Character</td>\n",
              "      <td>1997)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4</td>\n",
              "      <td>(Paula Abdul's Get Up &amp; Dance, 1994)</td>\n",
              "      <td>(Paula Abdul's Get Up &amp; Dance</td>\n",
              "      <td>1994)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5</td>\n",
              "      <td>(The Rise and Fall of ECW, 2004)</td>\n",
              "      <td>(The Rise and Fall of ECW</td>\n",
              "      <td>2004)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4494</th>\n",
              "      <td>4495</td>\n",
              "      <td>(Clifford: Happy Birthday Clifford / Puppy Lov...</td>\n",
              "      <td>(Clifford: Happy Birthday Clifford / Puppy Love</td>\n",
              "      <td>2002)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4495</th>\n",
              "      <td>4496</td>\n",
              "      <td>(Farewell My Concubine, 1993)</td>\n",
              "      <td>(Farewell My Concubine</td>\n",
              "      <td>1993)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4496</th>\n",
              "      <td>4497</td>\n",
              "      <td>(Texasville, 1990)</td>\n",
              "      <td>(Texasville</td>\n",
              "      <td>1990)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4497</th>\n",
              "      <td>4498</td>\n",
              "      <td>(Gonin, 2000)</td>\n",
              "      <td>(Gonin</td>\n",
              "      <td>2000)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4498</th>\n",
              "      <td>4499</td>\n",
              "      <td>(In My Skin, 2002)</td>\n",
              "      <td>(In My Skin</td>\n",
              "      <td>2002)</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>4499 rows × 4 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "      movie_id  ...    year\n",
              "0            1  ...   2003)\n",
              "1            2  ...   2004)\n",
              "2            3  ...   1997)\n",
              "3            4  ...   1994)\n",
              "4            5  ...   2004)\n",
              "...        ...  ...     ...\n",
              "4494      4495  ...   2002)\n",
              "4495      4496  ...   1993)\n",
              "4496      4497  ...   1990)\n",
              "4497      4498  ...   2000)\n",
              "4498      4499  ...   2002)\n",
              "\n",
              "[4499 rows x 4 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 4
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NidkxNco-ucL"
      },
      "source": [
        "Agora que os dados estão formatados corretamente, podemos excluir a coluna name-year."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XZRSxtVJ-uPB"
      },
      "source": [
        "df_movies.drop('name-year', axis=1, inplace=True)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "A4Oy-t7XtgXn"
      },
      "source": [
        "###Análise Exploratória dos dados"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nX67cLjruN9W"
      },
      "source": [
        "Neste cenário teremos 2 datasets diferentes. O primeiro - df_movies - contém o id de cada filme, nome e ano de lançamento. Já o segundo dataset - df_customers - contém o Id do cliente que fez a avaliação, a nota de avaliação, a data da avaliação feita e o Id do filme avaliado.\n",
        "\n",
        "Assim, irei analisar abaixo as dimensões dos dataframes."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "kapuEgl0uAsa",
        "outputId": "b6bdc7e6-436d-4709-f49f-20a5d16134b3"
      },
      "source": [
        "#Dimensões dos Dados:\n",
        "print(\"DIMENSÕES DO DATAFRAME MOVIES:\")\n",
        "print(\"Linhas:\\t\\t{}\".format(df_movies.shape[0]))\n",
        "print(\"Colunas:\\t{}\".format(df_movies.shape[1]), \"\\n\")\n",
        "\n",
        "print(\"DIMENSÕES DO DATAFRAME CUSTOMERS:\")\n",
        "print(\"Linhas:\\t\\t{}\".format(df_customers.shape[0]))\n",
        "print(\"Colunas:\\t{}\".format(df_customers.shape[1]))"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "DIMENSÕES DO DATAFRAME MOVIES:\n",
            "Linhas:\t\t4499\n",
            "Colunas:\t3 \n",
            "\n",
            "DIMENSÕES DO DATAFRAME CUSTOMERS:\n",
            "Linhas:\t\t24053764\n",
            "Colunas:\t4\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "f1ZFA02_vq-v"
      },
      "source": [
        "Ao olharmos para os dados, podemos perceber que cada linha do dataframe Movie representa um filme diferente. Logo, como a base de dados possui 4499 linhas **podemos responder a pergunta nº 1: existem 4499 filmes no dataset.**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        },
        "id": "sI5rYcH6z5Ft",
        "outputId": "edd0ca46-841f-4a17-e036-7b9ea76d7b95"
      },
      "source": [
        "df_movies"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>movie_id</th>\n",
              "      <th>name</th>\n",
              "      <th>year</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>(Dinosaur Planet</td>\n",
              "      <td>2003)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2</td>\n",
              "      <td>(Isle of Man TT 2004 Review</td>\n",
              "      <td>2004)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3</td>\n",
              "      <td>(Character</td>\n",
              "      <td>1997)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4</td>\n",
              "      <td>(Paula Abdul's Get Up &amp; Dance</td>\n",
              "      <td>1994)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5</td>\n",
              "      <td>(The Rise and Fall of ECW</td>\n",
              "      <td>2004)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4494</th>\n",
              "      <td>4495</td>\n",
              "      <td>(Clifford: Happy Birthday Clifford / Puppy Love</td>\n",
              "      <td>2002)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4495</th>\n",
              "      <td>4496</td>\n",
              "      <td>(Farewell My Concubine</td>\n",
              "      <td>1993)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4496</th>\n",
              "      <td>4497</td>\n",
              "      <td>(Texasville</td>\n",
              "      <td>1990)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4497</th>\n",
              "      <td>4498</td>\n",
              "      <td>(Gonin</td>\n",
              "      <td>2000)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4498</th>\n",
              "      <td>4499</td>\n",
              "      <td>(In My Skin</td>\n",
              "      <td>2002)</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>4499 rows × 3 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "      movie_id                                             name    year\n",
              "0            1                                 (Dinosaur Planet   2003)\n",
              "1            2                      (Isle of Man TT 2004 Review   2004)\n",
              "2            3                                       (Character   1997)\n",
              "3            4                    (Paula Abdul's Get Up & Dance   1994)\n",
              "4            5                        (The Rise and Fall of ECW   2004)\n",
              "...        ...                                              ...     ...\n",
              "4494      4495  (Clifford: Happy Birthday Clifford / Puppy Love   2002)\n",
              "4495      4496                           (Farewell My Concubine   1993)\n",
              "4496      4497                                      (Texasville   1990)\n",
              "4497      4498                                           (Gonin   2000)\n",
              "4498      4499                                      (In My Skin   2002)\n",
              "\n",
              "[4499 rows x 3 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wbCzJUUo6fis"
      },
      "source": [
        "**Para respondermos a pergunta nº 2**, primeiramente precisamos obter a média de avaliação de cada filme. Para isso, irei agrupar as informações em df_customers por filme e então realizar a média dos valores.\n",
        "\n",
        "Após feita a média, ordenarei os valores em ordem decrescente de média de avaliação. Assim os 5 primeiros valores representarão os filmes com maior média de avaliação.\n",
        "\n",
        "Por fim, filtraremos a tabela por id do filme e buscaremos o nome no dataset df_movie.\n",
        "\n",
        "**O resultado me retornará o nome dos 5 filmes com maior média de avaliação**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "id": "r57ufoIzmNzq",
        "outputId": "07fb805e-0b44-4a7a-b8be-78f4a2424bc6"
      },
      "source": [
        "ratingMean = df_customers.groupby('Movie_Id').mean().reset_index() #Agrupo o dataset por filme (comando groupby) e faço a média de avaliação de cada filme (mean).\n",
        "\n",
        "ratingMean.drop('Cust_Id', axis=1, inplace=True) #Como realizamos a média, não sentido esta ser calculado nos Ids dos clientes. Portanto, podemos eliminar essa coluna do dataset\n",
        "\n",
        "bestRatingMovies = ratingMean.sort_values(by='Rating', ascending=False).head()['Movie_Id'] #Ordeno a tabela por ordem decrescente de média, assim os 5 primeiros valores serão os filmes com a maior média de avaliação. Como estamos interessados no id desses filmes, filtro pela coluna movie_id\n",
        "\n",
        "df_movies[df_movies['movie_id'].isin(bestRatingMovies)]"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>movie_id</th>\n",
              "      <th>name</th>\n",
              "      <th>year</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>13</td>\n",
              "      <td>(Lord of the Rings: The Return of the King: Ex...</td>\n",
              "      <td>2003)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2101</th>\n",
              "      <td>2102</td>\n",
              "      <td>(The Simpsons: Season 6</td>\n",
              "      <td>1994)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3032</th>\n",
              "      <td>3033</td>\n",
              "      <td>(Ghost in the Shell: Stand Alone Complex: 2nd Gig</td>\n",
              "      <td>2005)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3455</th>\n",
              "      <td>3456</td>\n",
              "      <td>(Lost: Season 1</td>\n",
              "      <td>2004)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4237</th>\n",
              "      <td>4238</td>\n",
              "      <td>(Inu-Yasha</td>\n",
              "      <td>2000)</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "      movie_id                                               name    year\n",
              "12          13  (Lord of the Rings: The Return of the King: Ex...   2003)\n",
              "2101      2102                            (The Simpsons: Season 6   1994)\n",
              "3032      3033  (Ghost in the Shell: Stand Alone Complex: 2nd Gig   2005)\n",
              "3455      3456                                    (Lost: Season 1   2004)\n",
              "4237      4238                                         (Inu-Yasha   2000)"
            ]
          },
          "metadata": {},
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RKhcXUkgE97c"
      },
      "source": [
        "**Para a questão nº 3**, preciso saber a quantidade de filmes lançados por ano. Para isso, agrupei o dataset movies por ano e contei os valores de filmes. Assim, tenho o número de filmes lançados em cada ano.\n",
        "\n",
        "Depois disso, ordenei os valores em ordem crescente. Logo, **os primeiros 5 resultados são os anos que possuem menos lançamentos**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 235
        },
        "id": "JCHeSZsz9loK",
        "outputId": "1c807f6b-f3a2-4998-8548-4c2494ab3155"
      },
      "source": [
        "movies_byYear = df_movies.groupby('year').count().drop('name', axis=1).rename(columns={'movie_id':'releases'}) #Agrupo a tabela por ano e conto quantos filmes foram lançados em cada ano. Também usei o comando rename para renomear a \n",
        "                                                                                                                                                                                   #coluna de filmes contados para release\n",
        "                                                                                                                                                                                   \n",
        "movies_byYear.sort_values(by='releases').head() #Ordeno os valores de contagem em ordem crescente e mostro os 5 primeiros (que serão os 5 anos com menos lançamentos)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>releases</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>year</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>1915)</th>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1917)</th>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1922)</th>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1926)</th>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1916)</th>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "        releases\n",
              "year            \n",
              " 1915)         1\n",
              " 1917)         1\n",
              " 1922)         1\n",
              " 1926)         1\n",
              " 1916)         2"
            ]
          },
          "metadata": {},
          "execution_count": 52
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QKOwrRRpInR2"
      },
      "source": [
        "**Para a questão 4**, precisarei saber qual foi a última data de avaliação do Dataset. Para isso, basta ordenarmos os dados por data, em ordem decrescente. Logo, o primeiro resultado será a última data de avaliação.\n",
        "\n",
        "Feito isso, precisaremos saber todos os filmes com notas maiores ou iguais a 4.7 e que foram avaliados nesta data. "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        },
        "id": "tEwRlC0b9_TV",
        "outputId": "2c3c96e8-04ff-435c-acaf-86d9971949de"
      },
      "source": [
        "df_customers.sort_values(by='Date', ascending=False) #Ordenação do dataset por data em ordem decrescente"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Cust_Id</th>\n",
              "      <th>Rating</th>\n",
              "      <th>Date</th>\n",
              "      <th>Movie_Id</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>19662630</th>\n",
              "      <td>349234</td>\n",
              "      <td>2.0</td>\n",
              "      <td>2005-12-31</td>\n",
              "      <td>3756</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9003586</th>\n",
              "      <td>1808587</td>\n",
              "      <td>4.0</td>\n",
              "      <td>2005-12-31</td>\n",
              "      <td>1798</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4762203</th>\n",
              "      <td>183702</td>\n",
              "      <td>4.0</td>\n",
              "      <td>2005-12-31</td>\n",
              "      <td>954</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>22225850</th>\n",
              "      <td>1673594</td>\n",
              "      <td>2.0</td>\n",
              "      <td>2005-12-31</td>\n",
              "      <td>4216</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13578622</th>\n",
              "      <td>1922435</td>\n",
              "      <td>4.0</td>\n",
              "      <td>2005-12-31</td>\n",
              "      <td>2590</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>20393918</th>\n",
              "      <td>510180</td>\n",
              "      <td>2.0</td>\n",
              "      <td>1999-11-11</td>\n",
              "      <td>3870</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6901473</th>\n",
              "      <td>510180</td>\n",
              "      <td>5.0</td>\n",
              "      <td>1999-11-11</td>\n",
              "      <td>1367</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9056171</th>\n",
              "      <td>510180</td>\n",
              "      <td>5.0</td>\n",
              "      <td>1999-11-11</td>\n",
              "      <td>1798</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14892677</th>\n",
              "      <td>510180</td>\n",
              "      <td>3.0</td>\n",
              "      <td>1999-11-11</td>\n",
              "      <td>2866</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>19585852</th>\n",
              "      <td>510180</td>\n",
              "      <td>4.0</td>\n",
              "      <td>1999-11-11</td>\n",
              "      <td>3730</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>24053764 rows × 4 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "          Cust_Id  Rating        Date  Movie_Id\n",
              "19662630   349234     2.0  2005-12-31      3756\n",
              "9003586   1808587     4.0  2005-12-31      1798\n",
              "4762203    183702     4.0  2005-12-31       954\n",
              "22225850  1673594     2.0  2005-12-31      4216\n",
              "13578622  1922435     4.0  2005-12-31      2590\n",
              "...           ...     ...         ...       ...\n",
              "20393918   510180     2.0  1999-11-11      3870\n",
              "6901473    510180     5.0  1999-11-11      1367\n",
              "9056171    510180     5.0  1999-11-11      1798\n",
              "14892677   510180     3.0  1999-11-11      2866\n",
              "19585852   510180     4.0  1999-11-11      3730\n",
              "\n",
              "[24053764 rows x 4 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "yXA2uxRPWhyz",
        "outputId": "a48678bb-a9df-4174-acbc-fe78f812e323"
      },
      "source": [
        "rated47Movies = df_customers[ df_customers['Rating'] >= 4.7] #Filtro todos os filmes que possuem nota maior ou igual a 4.7\n",
        "\n",
        "#considerar apenas os avaliados no ultimo dia de avaliação (31/12/2005)\n",
        "result = rated47Movies[ rated47Movies['Date'] == \"2005-12-31\"].drop_duplicates(subset=\"Movie_Id\") #removi os dados duplicados para que a contagem (etapa seguinte) fosse feita corretamente\n",
        "\n",
        "result['Movie_Id'].count() #contar quantos filmes são"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "780"
            ]
          },
          "metadata": {},
          "execution_count": 30
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        },
        "id": "wVj2gzRkaYSm",
        "outputId": "1ef17184-b895-4233-f59f-91452f376679"
      },
      "source": [
        "result.sort_values(by='Rating', ascending=True) #ordenando os filmes que satisfazem as condições da questao 4 em ordem crescente de nota de avaliação"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Cust_Id</th>\n",
              "      <th>Rating</th>\n",
              "      <th>Date</th>\n",
              "      <th>Movie_Id</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>18187</th>\n",
              "      <td>329442</td>\n",
              "      <td>5.0</td>\n",
              "      <td>2005-12-31</td>\n",
              "      <td>8</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15629469</th>\n",
              "      <td>332079</td>\n",
              "      <td>5.0</td>\n",
              "      <td>2005-12-31</td>\n",
              "      <td>3025</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15636453</th>\n",
              "      <td>2226028</td>\n",
              "      <td>5.0</td>\n",
              "      <td>2005-12-31</td>\n",
              "      <td>3030</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15639905</th>\n",
              "      <td>2005761</td>\n",
              "      <td>5.0</td>\n",
              "      <td>2005-12-31</td>\n",
              "      <td>3033</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15650436</th>\n",
              "      <td>1258694</td>\n",
              "      <td>5.0</td>\n",
              "      <td>2005-12-31</td>\n",
              "      <td>3046</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8139426</th>\n",
              "      <td>1963479</td>\n",
              "      <td>5.0</td>\n",
              "      <td>2005-12-31</td>\n",
              "      <td>1631</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8184406</th>\n",
              "      <td>2068826</td>\n",
              "      <td>5.0</td>\n",
              "      <td>2005-12-31</td>\n",
              "      <td>1642</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8265688</th>\n",
              "      <td>588193</td>\n",
              "      <td>5.0</td>\n",
              "      <td>2005-12-31</td>\n",
              "      <td>1650</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7329244</th>\n",
              "      <td>2254964</td>\n",
              "      <td>5.0</td>\n",
              "      <td>2005-12-31</td>\n",
              "      <td>1470</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24043825</th>\n",
              "      <td>23421</td>\n",
              "      <td>5.0</td>\n",
              "      <td>2005-12-31</td>\n",
              "      <td>4496</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>780 rows × 4 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "          Cust_Id  Rating        Date  Movie_Id\n",
              "18187      329442     5.0  2005-12-31         8\n",
              "15629469   332079     5.0  2005-12-31      3025\n",
              "15636453  2226028     5.0  2005-12-31      3030\n",
              "15639905  2005761     5.0  2005-12-31      3033\n",
              "15650436  1258694     5.0  2005-12-31      3046\n",
              "...           ...     ...         ...       ...\n",
              "8139426   1963479     5.0  2005-12-31      1631\n",
              "8184406   2068826     5.0  2005-12-31      1642\n",
              "8265688    588193     5.0  2005-12-31      1650\n",
              "7329244   2254964     5.0  2005-12-31      1470\n",
              "24043825    23421     5.0  2005-12-31      4496\n",
              "\n",
              "[780 rows x 4 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 32
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CRONrdhHSbLM"
      },
      "source": [
        "Olhando a tabela acima, cheguei a conclusão que todos os filmes com nota maior ou igual a 4.7 e lançados na última data avaliada do dataset (31/12/2005) já possuem nota 5 (já que a ordenação crescente retorna 5 logo no primeiro valor). **Portanto, respondendo as questões 4 e 5, existem 780 filmes e todos estes avaliados com nota 5.**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_M5dy12Dawlv"
      },
      "source": [
        "**Na questão nº 6**, queremos encontrar quantas avaliações cada clientes fez, para então saber os clientes que mais avaliaram filmes. \n",
        "\n",
        "Para isso, agrupei os dados por cliente e contei o número de vezes que cada cliente avaliou um filme. Eliminei as colunas 'Date' e 'Movie_Id' para facilitar a visualização da contagem.\n",
        "\n",
        "Logo em seguida, ordenei os valores por ordem decrescente, de modo que **os resultados do comando head (5 primeiros) são os clientes que mais avaliaram filmes e também resposta da questão 6.**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 235
        },
        "id": "LymPWYCKaxJa",
        "outputId": "4e99df5f-b957-44fa-ad31-d9d252eed7e8"
      },
      "source": [
        "numberOfRatingPerClient = df_customers.groupby(by='Cust_Id').count().drop(['Date', 'Movie_Id'], axis=1)\n",
        "\n",
        "numberOfRatingPerClient.sort_values(by='Rating', ascending=False).head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Rating</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Cust_Id</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>305344</th>\n",
              "      <td>4467</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>387418</th>\n",
              "      <td>4422</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2439493</th>\n",
              "      <td>4195</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1664010</th>\n",
              "      <td>4019</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2118461</th>\n",
              "      <td>3769</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "         Rating\n",
              "Cust_Id        \n",
              "305344     4467\n",
              "387418     4422\n",
              "2439493    4195\n",
              "1664010    4019\n",
              "2118461    3769"
            ]
          },
          "metadata": {},
          "execution_count": 56
        }
      ]
    }
  ]
}