quesitos_minimos.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install -U pandasql plotly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!conda install -c plotly python-kaleido plotly-orca -y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from os import listdir\n",
    "from pandasql import sqldf\n",
    "import matplotlib as plt\n",
    "import plotly.express as px"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#definindo o diretorio\n",
    "directory = 'data/'\n",
    "files = listdir(directory)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#selecionando apenas as variaveis importantes para esta analise\n",
    "col_names = ['vendor_id', 'pickup_datetime','passenger_count', 'trip_distance','payment_type', 'tip_amount', 'total_amount']\n",
    "\n",
    "#setando os tipos para as variaveis, para que o pandas não precise adivinhar os dados e consumir mais RAM\n",
    "dtypes = {\n",
    "    \"vendor_id\": \"category\",\n",
    "    \"pickup_datetime\": \"datetime64\",\n",
    "    \"passenger_count\": \"int\",\n",
    "    \"trip_distance\": \"float\",\n",
    "    \"payment_type\": \"category\",\n",
    "    \"tip_amount\":\"float\",\n",
    "    \"total_amount\": \"float\"\n",
    "}\n",
    "\n",
    "#criando um dataframe vazio apenas para as colunas. Este dataframe servira para concatenar todos os datasets\n",
    "#posteriormente\n",
    "df = pd.DataFrame(columns=col_names)\n",
    "\n",
    "#um for que le apenas os .json\n",
    "for file_name in files:\n",
    "    if '.json' in file_name:\n",
    "        #le o primeiro json tranformando-o em dataframe do pandas\n",
    "        df_new = pd.read_json(directory+file_name, lines=True, dtype=dtypes)\n",
    "        #pega apenas as colunas que serao importantes para as analises e sobreescreve o dataframe\n",
    "        df_new = df_new[col_names]\n",
    "\n",
    "        #converte os tipos de pagamentos em letras minusculas\n",
    "        df_new['payment_type'] = df_new['payment_type'].apply(lambda string: string.lower())\n",
    "\n",
    "        #concatena o dataframe antigo com o novo\n",
    "        df = pd.concat([df, df_new])\n",
    "\n",
    "df = df.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#verificando missing data\n",
    "print(df.isna().sum())\n",
    "print(\"\\n -------------- \\n\")\n",
    "print(df.isnull().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#sqldf para usar o pandas sql\n",
    "pysqldf = lambda q: sqldf(q, globals())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1 - Distância média percorrida por viagens com no máximo 2 passageiros"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "from scipy import stats\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#pegando corridas com ate 2 passageiros juntamente com a distancia percorrida\n",
    "#df_max_2_passenger = pysqldf(\"SELECT trip_distance FROM df WHERE passenger_count <= 2\")\n",
    "df_max_2_passenger = df[df['passenger_count'] <= 2]['trip_distance']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#verificando a presença de outliers\n",
    "#Ao verificar a presença de outliers com boxplot, percebemos que a maioria dos dados \n",
    "#esta entre 0 e 10\n",
    "plt.boxplot(df_max_2_passenger)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#calculando o z-score para fazer o tratamento de outliers\n",
    "z = np.abs(stats.zscore(df_max_2_passenger))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#usando threshold de 3 para pegar index de outliers abaixo de z-score 3\n",
    "normal_indexes = np.where(z < 3)[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#se pegarmos o percentil 99 podemos observar que o valor esta muito proximo do que vimos no bloxplot\n",
    "#o que quer dizer nossos outliers realmente sairam do dataset\n",
    "df_max_2_passenger.iloc[normal_indexes].quantile(.99)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#aqui conseguimos ver que o valor maximo ja na esta tao alto, o que valida mais uma vez nossa\n",
    "#remocao de outliers. vale notar tambem que o max esta muito proximo do percentil 99\n",
    "df_max_2_passenger.iloc[normal_indexes].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_max_2_passenger = df_max_2_passenger.iloc[normal_indexes]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#pysqldf(\"SELECT avg(trip_distance) trip_distance FROM df where passenger_count <= 2;\")\n",
    "#multiplicando por 1.60 para obtermos o valor em kilometros temos a media da distancia percorrida\n",
    "#com no maximo 2 passageiros\n",
    "df_max_2_passenger.mean() * 1.609344"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#analisando a diferença entre 1 e 2 passageiros\n",
    "df_1_passenger = df[df['passenger_count'] == 1]['trip_distance']\n",
    "z = np.abs(stats.zscore(df_1_passenger))\n",
    "normal_indexes = np.where(z < 3)[0]\n",
    "df_1_passenger.iloc[normal_indexes].mean() * 1.609344"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2 - Os 3 maiores vendors em quantidade total de dinheiro arrecadado"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#media de dinheiro arrecadado\n",
    "df['total_amount'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#por questoes de performance, esta linha esta comentada, mas tambem pode ser excutado com pandasql\n",
    "#top3_vendor = pysqldf(\"SELECT ROUND(sum(total_amount), 2) total_amount, vendor_id FROM df GROUP BY vendor_id ORDER BY total_amount DESC LIMIT 3;\")\n",
    "top3_vendor = df[['vendor_id', 'total_amount']].groupby(['vendor_id']).sum().round().sort_values(by='total_amount', ascending=False).reset_index().head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#criando barplot\n",
    "fig = px.bar(\n",
    "    top3_vendor, \n",
    "    x='vendor_id', \n",
    "    y='total_amount', \n",
    "    title=\"3 maiores vendors em quantidade total de dinheiro arrecadado\",\n",
    "    text='total_amount',\n",
    "    color=['CMT', 'VTS', 'DDS'],\n",
    "    color_discrete_map = {'CMT': '#ffda99', 'VTS': '#99fffa', 'DDS': '#a16efa'}\n",
    ")\n",
    "\n",
    "#definindo as labels para os eixos x e y\n",
    "fig.update_layout(\n",
    "    xaxis_title=\"Vendor\",\n",
    "    yaxis_title=\"Total Arrecadado\",\n",
    "    legend_title=\"Vendor\",\n",
    "    title={\n",
    "        'y':0.9,\n",
    "        'x':0.5,\n",
    "        'xanchor': 'center',\n",
    "        'yanchor': 'top'\n",
    "    },\n",
    "    height=650\n",
    ")\n",
    "\n",
    "#cria os texto fora das barras\n",
    "fig.update_traces(\n",
    "    texttemplate='%{text:.2s}', \n",
    "    textposition='outside',\n",
    "    hovertemplate='Total Arrecadado: %{y:.4s} <br> Vendor: %{x}',\n",
    ")\n",
    "fig.show()\n",
    "#fig.write_image(\"graficos/top3_vendor.jpeg\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3 - Histograma da distribuição mensal de corridas pagas em dinheiro por ano"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#transformando os indexes em datetime para facilitar o trabalho com datas\n",
    "df.index = pd.DatetimeIndex(df['pickup_datetime'])\n",
    "\n",
    "#reordena o dataframe em ordem crescente\n",
    "df = df.sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#porcentagem de cada tipo de pagamento\n",
    "df.groupby('payment_type')[\"vendor_id\"].count().apply(lambda x: (x / df.shape[0]) * 100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#plotando histogramas por ano\n",
    "for ano in [2009, 2010,2011,2012]:\n",
    "    dataHist = df[(df['payment_type'] == 'cash') & (df.index.year == ano)]['pickup_datetime'].dt.month\n",
    "    \n",
    "    fig = px.histogram(dataHist, \n",
    "                   title=f'Distribuição mensal de corridas pagas em dinheiro no ano de {ano}',\n",
    "                   nbins=12,\n",
    "                  )\n",
    "    fig.update_layout(\n",
    "        showlegend=False,\n",
    "        xaxis_title=\"Meses\",\n",
    "        yaxis_title=\"Quantidade\",\n",
    "        title={\n",
    "            'y':0.9,\n",
    "            'x':0.5,\n",
    "            'xanchor': 'center',\n",
    "            'yanchor': 'top'\n",
    "        },\n",
    "    )\n",
    "    fig.update_traces(\n",
    "        hovertemplate='Quantidade: %{y:.4s} <br> Mes: %{x}',\n",
    "    )\n",
    "    fig.show()\n",
    "    #fig.write_image(f'graficos/hist_{ano}.jpeg')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#histograma de distribuicao unico para os 4 anos\n",
    "dataHist = df[df['payment_type'] == 'cash']['pickup_datetime'].dt.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = px.histogram(dataHist, \n",
    "                   color=dataHist.index.year, \n",
    "                   title='Distribuição mensal de corridas pagas em dinheiro por ano',\n",
    "                   color_discrete_map = {\n",
    "                       '2009': '#ffda99', \n",
    "                       '2010': '#99fffa', \n",
    "                       '2011': '#e0ff99',\n",
    "                       '2012': '#b199ff',\n",
    "                   },\n",
    "                   nbins=12,\n",
    "                  )\n",
    "fig.update_layout(\n",
    "    xaxis_title=\"Meses\",\n",
    "    yaxis_title=\"Quantidade\",\n",
    "    legend_title=\"Ano\",\n",
    "    title={\n",
    "        'y':0.9,\n",
    "        'x':0.5,\n",
    "        'xanchor': 'center',\n",
    "        'yanchor': 'top'\n",
    "    },\n",
    ")\n",
    "fig.update_traces(\n",
    "    hovertemplate='Quantidade: %{y:.4s} <br> Mes: %{x}',\n",
    ")\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4 - Serie temporal da quantidade de gorjetas de cada dia, nos ultimos 3 meses de 2012"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Pegando os ultimos 3 meses de 2012 disponiveis no dataset, nao possuimos os meses de Novembro e dezembro no\n",
    "#dataset, portanto, teremos que pegar valores de Agosto - Outubro\n",
    "last_3_months = df[(df.index.year == 2012) & (df['tip_amount'] > 0)].last(\"3M\")['tip_amount'].reset_index()\n",
    "\n",
    "#contando a quantidade de gorjetas recebidas em cada dia\n",
    "last_3_months = last_3_months.groupby(last_3_months['pickup_datetime'].dt.date).size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = px.line(\n",
    "    df, \n",
    "    x=last_3_months.index, \n",
    "    y=last_3_months,\n",
    "    title='Quantidade de gorjetas de cada dia, dos ultimos 3 meses de 2012',\n",
    ")\n",
    "fig.update_layout(\n",
    "    xaxis_title=\"Data\",\n",
    "    yaxis_title=\"Quantidade de gorjetas\",\n",
    "    title={\n",
    "        'y':0.9,\n",
    "        'x':0.5,\n",
    "        'xanchor': 'center',\n",
    "        'yanchor': 'top'\n",
    "    },\n",
    "    height=650\n",
    ")\n",
    "fig.update_traces(\n",
    "    hovertemplate='Quantidade: %{y:.4s} <br> Data: %{x}',\n",
    ")\n",
    "fig.show()\n",
    "#fig.write_image(\"graficos/time_series.jpeg\")"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}