anotacoes-emeron-moodle.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "def clean_data(df):\n",
    "    # Alterar o tipo para string\n",
    "    df['idPessoa'] = df['idPessoa'].astype(str).str.zfill(11)\n",
    "    return df\n",
    "\n",
    "# Variável carregada 'df' do URI: /home/maicon/git/esjud/integracao-emeron-moodle/_SELECT_Inscricao_idInscricao_InscricaoAluno_idPessoa_Pessoa_nom_202405201404.csv\n",
    "df = pd.read_csv(r'dataset/_SELECT_Inscricao_idInscricao_InscricaoAluno_idPessoa_Pessoa_nom_202405201404.csv')\n",
    "\n",
    "df_clean = clean_data(df.copy())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_data_mdl_user(df):\n",
    "    # Filtrar linhas com base na coluna: 'username'\n",
    "    df = df.set_index('username')\n",
    "    df = df.filter(regex=\"(^\\\\d+)\",axis=0)\n",
    "    return df\n",
    "\n",
    "# Variável carregada 'df' do URI: /home/maicon/git/esjud/integracao-emeron-moodle/mdl_user.csv\n",
    "df = pd.read_csv(r'dataset/mdl_user-v2.csv')\n",
    "\n",
    "df_mdl_user = clean_data_mdl_user(df.copy()).reset_index()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_clean['username'] = df_clean['idPessoa']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merged = df_clean.merge(df_mdl_user,on='username', how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['idInscricao', 'idPessoa', 'nome', 'celular01', 'nome.1', 'flInscrito',\n",
       "       'inscricaoData', 'inscricaoHora', 'idFormacao', 'flCertMoodle',\n",
       "       'certMoodleId', 'username', 'id', 'auth', 'confirmed', 'policyagreed',\n",
       "       'deleted', 'suspended', 'mnethostid', 'password', 'idnumber',\n",
       "       'firstname', 'lastname', 'email', 'emailstop', 'phone1', 'phone2',\n",
       "       'institution', 'department', 'address', 'city', 'country', 'lang',\n",
       "       'calendartype', 'theme', 'timezone', 'firstaccess', 'lastaccess',\n",
       "       'lastlogin', 'currentlogin', 'lastip', 'secret', 'picture',\n",
       "       'description', 'descriptionformat', 'mailformat', 'maildigest',\n",
       "       'maildisplay', 'autosubscribe', 'trackforums', 'timecreated',\n",
       "       'timemodified', 'trustbitmask', 'imagealt', 'lastnamephonetic',\n",
       "       'firstnamephonetic', 'middlename', 'alternatename', 'moodlenetprofile'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Faço uma cópia com as colunas ['idPessoa','username','firstname', 'email', 'auth', 'firstaccess] e removo os duplicados\n",
    "- com a coluna 'firstaccess' eu consigo ver três situações:\n",
    "- não cadastrados no emeron\n",
    "- não cadastrados no moodle\n",
    "- usuários que nunca acessaram (que estão com valor 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_analise = df_merged[['idPessoa','username','firstname', 'email', 'auth', 'firstaccess']].copy().drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Função para converter as colunas de timestamp para datetime, os valores não reconhecidos como data serão ignorados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "def timestamp_to_datetime_series(serie:pd.Series):\n",
    "    return pd.to_datetime(serie, unit='s', errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "# converte uma nova coluna\n",
    "df_analise['firstaccess_datetime'] = timestamp_to_datetime_series(df_analise['firstaccess'].copy())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['idPessoa', 'username', 'firstname', 'email', 'auth', 'firstaccess',\n",
       "       'firstaccess_datetime'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_analise.columns\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_analise = df_analise.drop('firstaccess', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_analise = pd.concat([df_analise, df_merged[['lastaccess','lastlogin']]], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_analise['lastaccess'] = timestamp_to_datetime_series(df_analise['lastaccess'].copy())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_analise['lastlogin'] = timestamp_to_datetime_series(df_analise['lastlogin'].copy())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merged = df_merged[df_merged['nome.1'].str.contains(\"Artifi\", regex=False, na=False)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_nulos_no_emeron = df_merged[df_merged['idPessoa'].isnull() == True]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_nulos_no_emeron"
   ]
  }
 ],
 "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.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}