limpa.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime, timedelta\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_base = pd.read_csv('bases/users.csv')\n",
    "task_base = pd.read_csv('bases/tasks.csv')\n",
    "aloc_base = pd.read_csv('bases/alocacao.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "task_base.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "task_base.drop(['starts_at','actual_starts_at'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "aloc_base.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "def gerar_valores_normais(media, desvio_padrao, tamanho):\n",
    "    valores_normais = np.random.normal(loc=media, scale=desvio_padrao, size=tamanho)\n",
    "    return valores_normais"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "task_base.fillna('2026-12-31 00:00:00', inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***RODAR A PARTIR DAKI***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "task_base['delay'] = 0\n",
    "for index in task_base.index:\n",
    "    if type(task_base['actual_ends_at'][index]) == float:\n",
    "        print(\"Error linha: \", index)\n",
    "        break\n",
    "    ends_at = datetime.strptime(task_base['ends_at'][index][:10], '%Y-%m-%d')\n",
    "    actual_end = datetime.strptime(task_base['actual_ends_at'][index][:10], '%Y-%m-%d')\n",
    "\n",
    "    task_base['ends_at'][index] = datetime.strftime(ends_at, '%d-%m-%Y')\n",
    "    task_base['actual_ends_at'][index] = datetime.strftime(actual_end, '%d-%m-%Y')\n",
    "    \n",
    "    delay = (ends_at - actual_end)\n",
    "    task_base['delay'][index] = delay.days\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "task_base['priority'] = 0\n",
    "\n",
    "for index in task_base.index:\n",
    "    priority = round(gerar_valores_normais(2, 1, 1)[0]) % 5\n",
    "    task_base['priority'][index] = priority"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "task_base['priority'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "task_base.rename(columns={'id':'task_id'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2000, 6)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "task_base.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "task_base = pd.merge(task_base, aloc_base, on='task_id', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "task_base"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Converte o uuid para UserX**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "userSet = set(task_base['user_id'])\n",
    "count = 0\n",
    "for user in userSet:\n",
    "    task_base['user_id'].replace(user, f'user{count}', inplace=True)\n",
    "    count += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "task_base"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Cria a performance***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "task_base['performance'] = 0\n",
    "for index in task_base.index:\n",
    "    perc = task_base['percentage'][index]\n",
    "    delay = task_base['delay'][index]\n",
    "\n",
    "    performance = delay * (perc/100)\n",
    "\n",
    "    task_base['performance'][index] = performance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "task_base['performance'].hist(bins=50)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Remove colunas que não serão utilizadas***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['task_id', 'name', 'ends_at', 'actual_ends_at', 'delay', 'priority',\n",
       "       'user_id', 'percentage', 'performance'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "task_base.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "task_base.drop(['task_id', 'name', 'ends_at', 'actual_ends_at', 'delay', 'percentage'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "task_base"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Faz media de performance por user***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_base = pd.DataFrame(columns=['user_id', 'priority', 'performance'])\n",
    "substitui_null = -100\n",
    "for priority in range(5):\n",
    "    for user in task_base['user_id'].unique():\n",
    "        perf_list = task_base.loc[(task_base['user_id'] == user) & (task_base['priority'] == priority), 'performance']\n",
    "        media = perf_list.mean()\n",
    "        if len(perf_list) == 0:\n",
    "            sum = 0\n",
    "            for i in range(5):\n",
    "                perf_list = task_base.loc[(task_base['user_id'] == user) & (task_base['priority'] == i), 'performance']\n",
    "                if len(perf_list) == 0:\n",
    "                    sum += 0\n",
    "                else:\n",
    "                    sum += perf_list.mean()\n",
    "            media = sum/5\n",
    "            \n",
    "        \n",
    "        new_base.loc[len(new_base)] ={'user_id': user, 'priority': priority, 'performance': media}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_base['performance'].hist(bins=100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_base = new_base.sort_values(by=['user_id', 'priority'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_base.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "n = 450\n",
    "size = n\n",
    "if size != -1:\n",
    "    size *= 5\n",
    "else:\n",
    "    size = len(new_base)\n",
    "new_base[0:size].to_csv(f'bases/final_base{n}.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}