egapro_python/egapro.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import psycopg2\n",
    "from dotenv import load_dotenv\n",
    "\n",
    "load_dotenv()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Establish a connection to the database\n",
    "# Replace 'database_name', 'user', and 'password' with your database details\n",
    "conn = psycopg2.connect(\n",
    "    dbname='dsn', \n",
    "    user=os.getenv('USERNAME_DB'),\n",
    "    password=os.getenv('PASSWORD_DB'), \n",
    "    host='10.0.0.1'\n",
    ")\n",
    "\n",
    "# Create a cursor object\n",
    "cur = conn.cursor()\n",
    "\n",
    "# Execute a query\n",
    "cur.execute(\"\"\"\n",
    "    select\n",
    "\tEXTRACT(MONTH FROM ddadtsalaire_base.date_debut_paie) as MONTH,\n",
    "\tEXTRACT(YEAR FROM ddadtsalaire_base.date_debut_paie) as YEAR,\n",
    "  ddadtassure.id,\n",
    "\tddadtcontrat.num_contrat,\n",
    "  LEFT(ddadtassure.num_immatriculation,1) as sexe_nir,\n",
    "  ddadtassure.sexe,\n",
    "  EXTRACT(YEAR FROM age(NOW(), TO_DATE(ddadtassure.date_naissance, 'DDMMYYYY'))) as age,\n",
    "  ddadtcontrat.unite_mesure,\n",
    "  ddadtcontrat.quotite,\n",
    "  ddadtcontrat.quotite_categorie,\n",
    "  ddadtcontrat.pcs_ese,\n",
    "\tddadtcontrat.statut_conventionnel, -- CSP\n",
    "\tddadtcontrat.dispositif_politique, -- type contrat (pour exclusions)\n",
    "  ddadtcontrat.nature_contrat, -- S21.G00.40.007\n",
    "  ddadtsalaire_base.montant as SALAIRE_BRUT\n",
    "  from  ddadtsalaire_base\n",
    "\tleft join ddadtversement on ddadtversement.id=ddadtsalaire_base.id_versement\n",
    "  left join ddadtemployeur_assure on ddadtemployeur_assure.id=ddadtversement.id_employeur_assure\n",
    "  left join ddadtassure on ddadtassure.id=ddadtemployeur_assure.id_assure\n",
    "  left join ddadtcontrat on ddadtcontrat.id_employeur_assure=ddadtemployeur_assure.id and ddadtsalaire_base.numero_contrat=ddadtcontrat.num_contrat\n",
    "  where ddadtemployeur_assure.id_employeur=1069987;\"\"\")\n",
    "\n",
    "# Fetch the results\n",
    "records = cur.fetchall()\n",
    "\n",
    "df_salaires = pd.DataFrame(records, columns=['month', 'year', 'id_assure', 'num_contrat', 'sexe_nir', 'sexe', 'age', 'unite_mesure',\n",
    "       'quotite', 'quotite_categorie', 'pcs_ese', 'statut_conventionnel', 'dispositif_politique',\n",
    "       'nature_contrat', 'salaire_brut'])\n",
    "\n",
    "# tous les congés maternité qui ont repris en 2022\n",
    "cur.execute(\"\"\"\n",
    "\n",
    "select\n",
    "\tddadtemployeur_assure.id_assure,\n",
    "  id_contrat,motif_arret,\n",
    "  date_dernier_jour_travaille,\n",
    "  max(date_reprise) as MAX_REPRISE,\n",
    "\n",
    "\t-- salaire au dernier jour travaillé\n",
    "  (\n",
    "    SELECT SUM(montant)\n",
    "    from  ddadtsalaire_base\n",
    "    left join ddadtversement on ddadtversement.id=ddadtsalaire_base.id_versement\n",
    "    left join ddadtemployeur_assure ddadtemployeur_assure2  on ddadtemployeur_assure2.id=ddadtversement.id_employeur_assure\n",
    "    where ddadtemployeur_assure2.id_employeur=1069987 and  ddadtemployeur_assure2.id_assure=ddadtemployeur_assure.id_assure and EXTRACT(MONTH from date_debut_paie)=EXTRACT(MONTH from max(date_dernier_jour_travaille)) and EXTRACT(YEAR from date_debut_paie)=EXTRACT(YEAR from max(date_dernier_jour_travaille))\n",
    "  ) as SALAIRE1,\n",
    "\n",
    "  -- salaire à la reprise\n",
    "  (\n",
    "    SELECT SUM(montant)\n",
    "    from  ddadtsalaire_base\n",
    "    left join ddadtversement on ddadtversement.id=ddadtsalaire_base.id_versement\n",
    "    left join ddadtemployeur_assure ddadtemployeur_assure2  on ddadtemployeur_assure2.id=ddadtversement.id_employeur_assure\n",
    "    where ddadtemployeur_assure2.id_employeur=1069987 and  ddadtemployeur_assure2.id_assure=ddadtemployeur_assure.id_assure and EXTRACT(MONTH from date_debut_paie)=EXTRACT(MONTH from max(date_reprise)) and EXTRACT(YEAR from date_debut_paie)=EXTRACT(YEAR from max(date_reprise))\n",
    "  ) as SALAIRE2\n",
    "\n",
    "\n",
    " from ddadtarret_trav\n",
    "left join ddadtcontrat on ddadtcontrat.id=ddadtarret_trav.id_contrat\n",
    "left join ddadtemployeur_assure on ddadtemployeur_assure.id=ddadtcontrat.id_employeur_assure\n",
    "where ddadtemployeur_assure.id_employeur=1069987 and motif_arret='02' and EXTRACT(YEAR from date_reprise)=2022\n",
    "group by ddadtemployeur_assure.id_assure,id_contrat,motif_arret,date_dernier_jour_travaille\n",
    "\n",
    "\"\"\")\n",
    "\n",
    "records = cur.fetchall()\n",
    "\n",
    "df_maternites = pd.DataFrame(records, columns=[\"id_assure\", \"id_contrat\", \"motif_arret\", \"date_dernier_jour_travaille\", \"max_reprise\", \"salaire1\", \"salaire2\"])\n",
    "\n",
    "# Close the cursor and connection\n",
    "cur.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fais le mapping CSP\n",
    "mapping = {\n",
    "    \"01\": 'ouvriers',\n",
    "    \"02\": 'ouvriers',\n",
    "    \"03\": 'ingénieurs et cadres',\n",
    "    \"04\": 'ingénieurs et cadres',\n",
    "    \"05\": 'techniciens et agents de maitrise',\n",
    "    \"06\": 'employés',\n",
    "    \"07\": 'ouvriers',\n",
    "}\n",
    "df_salaires[\"categorie_csp\"] = df_salaires[\"statut_conventionnel\"].map(mapping)\n",
    "\n",
    "mapping_sexe = {\n",
    "    '1':'H',\n",
    "    '2':'F'\n",
    "}\n",
    "df_salaires[\"sexe_nir\"] = df_salaires[\"sexe_nir\"].map(mapping_sexe)\n",
    "df_salaires[\"sexe\"] = df_salaires[\"sexe\"].fillna(df_salaires[\"sexe_nir\"])\n",
    "\n",
    "def categorize_age(age):\n",
    "    if age < 30:\n",
    "        return 'moins de 30 ans'\n",
    "    elif 30 <= age < 40:\n",
    "        return 'de 30 à 39 ans'\n",
    "    elif 40 <= age < 50:\n",
    "        return 'de 40 à 49 ans'\n",
    "    else:\n",
    "        return '50 ans et plus'\n",
    "\n",
    "# Apply the function to create the AgeCategory column\n",
    "df_salaires['categorie_age'] = df_salaires['age'].apply(categorize_age)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filtering data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calcul du taux ETP\n",
    "df_salaires[\"equiv_etp\"] = df_salaires[\"quotite\"] / df_salaires[\"quotite_categorie\"]\n",
    "df_salaires[\"equiv_etp\"].apply(lambda x: min(x, 1))\n",
    "\n",
    "# Selection des personnes ayant un contrat de bonne nature légale, exclusion des apprentis et des contrats pro\n",
    "etp_annee = df_salaires.loc[(~df_salaires[\"nature_contrat\"].isin([\"08\", \"20\", \"21\", \"29\", \"50\", \"51\",\"52\", \"54\", \"70\", \"81\", \"90\"]))\n",
    "                            & (~df_salaires[\"dispositif_politique\"].isin([\"61\", \"64\", \"65\"]))\n",
    "                            & (df_salaires[\"year\"] == 2022), [\"id_assure\", \"equiv_etp\"]].groupby(by=\"id_assure\").sum()\n",
    "personnes_eligibles = etp_annee[etp_annee[\"equiv_etp\"] >= 6].index.values\n",
    "\n",
    "df_salaires_filtered = df_salaires[(df_salaires[\"year\"] == 2022) & (df_salaires[\"id_assure\"].isin(personnes_eligibles))].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Indicateur 1 (rémunérations)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "df_salaires_filtered[\"salaire_proratise\"] = df_salaires_filtered[\"salaire_brut\"] * df_salaires_filtered[\"equiv_etp\"]\n",
    "df = df_salaires_filtered.groupby(by=[\"id_assure\", \"sexe\", \"categorie_age\", \"categorie_csp\"], as_index=False).aggregate({\"salaire_proratise\":\"sum\", \"equiv_etp\":\"sum\"})\n",
    "df[\"salaire_moyen\"] = df[\"salaire_proratise\"] / df[\"equiv_etp\"]\n",
    "\n",
    "df = df.groupby(by=[\"sexe\", \"categorie_age\", \"categorie_csp\"]).aggregate({\"salaire_moyen\": \"mean\", \"id_assure\": \"count\"})\n",
    "json_structure = {}\n",
    "\n",
    "for (sexe, age, csp), row in df.iterrows() :\n",
    "    key_base = f\"index . écart rémunérations . {csp} . {age}\"\n",
    "    \n",
    "    if sexe == \"H\":\n",
    "        sex_key = \"hommes\"\n",
    "    else:\n",
    "        sex_key = \"femmes\"\n",
    "        \n",
    "    json_structure[f\"{key_base} . remunération annuelle brute moyenne par EQTP . {sex_key}\"] = row['salaire_moyen']\n",
    "    json_structure[f\"{key_base} . nombre salariés . {sex_key}\"] = row['id_assure']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Indicateurs 2 et 3 (Augmentations et Promotions)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_salaires_filtered2 = df_salaires[((df_salaires[\"year\"] == 2022) | ((df_salaires[\"year\"] == 2021) & (df_salaires[\"month\"] == 12))) & (df_salaires[\"id_assure\"].isin(personnes_eligibles))].copy()\n",
    "df_salaires_filtered2[\"salaire_proratise\"] = df_salaires_filtered2[\"salaire_brut\"] * df_salaires_filtered2[\"equiv_etp\"]\n",
    "df = df_salaires_filtered2.groupby(by=[\"id_assure\", \"sexe\", \"categorie_csp\", \"statut_conventionnel\", \"pcs_ese\", \"month\", \"year\" ], as_index=False).agg({\"equiv_etp\":\"sum\", \"salaire_proratise\":\"sum\"})\n",
    "df[\"salaire_mensuel\"] = df[\"salaire_proratise\"] / df[\"equiv_etp\"].replace(0, 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.sort_values(by=['year', 'month'])\n",
    "df[\"is_augmented\"] = False\n",
    "\n",
    "for individu in personnes_eligibles:\n",
    "   try:\n",
    "      df.loc[df[\"id_assure\"] == individu, \"is_augmented\"] = (df[df[\"id_assure\"] == individu].iloc[0][\"salaire_mensuel\"] < df[df[\"id_assure\"] == individu].iloc[-1][\"salaire_mensuel\"])\n",
    "   except:\n",
    "      print(individu) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_per_assure = df.groupby(['id_assure', 'categorie_csp', 'sexe'], as_index=False).aggregate({\"is_augmented\": 'max'})\n",
    "\n",
    "proportion_augmented = df_per_assure.groupby(['categorie_csp', 'sexe']).apply(\n",
    "    lambda x: x['is_augmented'].mean()\n",
    ").reset_index(name='proportion_augmented')\n",
    "\n",
    "for (index,row) in proportion_augmented.iterrows() :\n",
    "    key_base = f\"index . écart augmentations . {row['categorie_csp']}\"\n",
    "    \n",
    "    if row[\"sexe\"] == \"H\":\n",
    "        sex_key = \"hommes\"\n",
    "    else:\n",
    "        sex_key = \"femmes\"\n",
    "        \n",
    "    json_structure[f\"{key_base} . {sex_key}\"] = round(row['proportion_augmented']*100,3)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"is_promoted\"] = False\n",
    "\n",
    "for individu in personnes_eligibles:\n",
    "    augmentation = (df[df[\"id_assure\"] == individu].iloc[0][\"salaire_mensuel\"] < df[df[\"id_assure\"] == individu].iloc[-1][\"salaire_mensuel\"])\n",
    "    changement_statut = (df[df[\"id_assure\"] == individu].iloc[0][\"statut_conventionnel\"] != df[df[\"id_assure\"] == individu].iloc[-1][\"statut_conventionnel\"])\n",
    "    changement_pcs = (df[df[\"id_assure\"] == individu].iloc[0][\"pcs_ese\"] != df[df[\"id_assure\"] == individu].iloc[-1][\"pcs_ese\"])\n",
    "    \n",
    "    df.loc[df[\"id_assure\"] == individu, \"is_promoted\"] = augmentation & (changement_statut | changement_pcs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_per_assure = df.groupby(['id_assure', 'categorie_csp', 'sexe'], as_index=False).aggregate({\"is_promoted\": 'max'})\n",
    "\n",
    "proportion_promoted = df_per_assure.groupby(['categorie_csp', 'sexe']).apply(\n",
    "    lambda x: x['is_promoted'].mean()\n",
    ").reset_index(name='proportion_promoted')\n",
    "\n",
    "for (index,row) in proportion_promoted.iterrows() :\n",
    "    key_base = f\"index . écart promotions . {row['categorie_csp']}\"\n",
    "    \n",
    "    if row[\"sexe\"] == \"H\":\n",
    "        sex_key = \"hommes\"\n",
    "    else:\n",
    "        sex_key = \"femmes\"\n",
    "        \n",
    "    json_structure[f\"{key_base} . {sex_key}\"] = round(row['proportion_promoted']*100,3)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Indicateur 4 (maternité)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "json_structure[f\"index . maternité . nombre total\"] = len(df_maternites)\n",
    "json_structure[f\"index . maternité . nombre augmentés\"] = (df_maternites[\"salaire1\"]<df_maternites[\"salaire2\"]).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Indicateur 5 (hautes rémunérations)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# extraire top salaire par personne sur l'année, selectionner le top 10\n",
    "df_salaires_filtered[\"salaire_proratise\"] = df_salaires_filtered[\"salaire_brut\"] * df_salaires_filtered[\"equiv_etp\"]\n",
    "df = df_salaires_filtered.groupby(by=[\"id_assure\", \"sexe\", \"month\", \"year\"], as_index=False).agg({\"equiv_etp\":\"sum\", \"salaire_proratise\":\"sum\"})\n",
    "df[\"salaire_mensuel\"] = df[\"salaire_proratise\"] / df[\"equiv_etp\"].replace(0, 1)\n",
    "df_rem = df.groupby(by=[\"sexe\",\"id_assure\"], as_index=False).agg({\"salaire_mensuel\":\"max\"})\n",
    "df_rem = df_rem.sort_values(by=\"salaire_mensuel\", ascending=False)\n",
    "repartition = df_rem.iloc[:10][\"sexe\"].value_counts()\n",
    "json_structure[f\"index . hautes rémunérations . femmes\"] = repartition.get(\"F\")\n",
    "json_structure[f\"index . hautes rémunérations . hommes\"] = repartition.get(\"H\")"
   ]
  }
 ],
 "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}