# **Programmes R de remplacement de la chaîne SAS d’imputation**

Ce petit doc permet de récapituler les pgm faits/en cours de dev et les questions en suspens.
Les développements (notamment les parties imports et d’export) sont faits sur la branche dev_sndi !

Les programmes doivent être lancés dans cet ordre (cf pgm Tests_import_données.R qui centralisent l’ensemble des lancements) ⇒ Une fois les traitements validés et opérationnels, les fonctionnalités de ce pgm seront réintégrées dans les pgm Lancement.R et Imputations_complètes.R pour pouvoir s’enchaîner de manière automatisée.

##########################################################################
#### Definition_sql_init.R
###########################

Ce pgm récapitule les 4 proc sql permettant d'importer les données des tables PostGre sous R :

◦ les carac de l'année N :

sql_table_carac_N <- paste0("SELECT ocs1.annee_daaaa, ocs1.siren,ocs1.ent_cre_daaaammjj, ocs1.eff_etp, 
ocs1.eff_tra, ocs1.esane_etat, ocs1.esane_ch_appart_ind, ocs1.lanc_moa,ocs1.ape_tab, b.liasse_etat, b.liasse_id,
b.orbi4
FROM ocs_ta_unite_ocsane ocs1 
LEFT OUTER JOIN ieg_ta_gestion b ON ocs1.siren = b.siren and ocs1.annee_daaaa = b.annee_daaaa  
WHERE ocs1.annee_daaaa = '",an,"'
AND (esane_ch_appart_ind = 'O') 
AND (liasse_etat in (1, 2, 7) 
     or (liasse_etat = 11 and esane_etat not in ('7', '8')) or liasse_etat is null) 
AND (liasse_id = (select cast(max(to_number(g.liasse_id, '9999999999')) as varchar) from ieg_ta_gestion g  
                  where g.liasse_etat in (1, 2, 7, 11) and g.annee_daaaa = ocs1.annee_daaaa and g.siren = ocs1.siren)  
                  or liasse_id is null) ORDER BY ocs1.siren;
")

Liasse_etat : (N = 2022)
| | Frequency | Percent | Valid Percent |
|-------|-----------|-----------|---------------|
| 1 | 2452647 | 4.441e+01 | 44.4075 |
| 2 | 31872 | 5.771e-01 | 0.5771 |
| 7 | 2957738 | 5.355e+01 | 53.5527 |
| 11 | 80785 | 1.463e+00 | 1.4627 |
| Total | 5523042 | 1.000e+02 | 100.000 |

◦ les carac de l'année N-1 :

sql_table_carac_N1 <- paste0("SELECT ocs2.annee_daaaa, ocs2.siren,ocs2.ent_cre_daaaammjj, ocs2.eff_etp, 
ocs2.eff_tra, ocs2.esane_etat, ocs2.esane_ch_appart_ind, ocs2.lanc_moa,ocs2.ape_tab, b.liasse_etat, b.liasse_id,  
b.orbi4
FROM ocs_ta_unite_ocsane ocs2 
LEFT OUTER JOIN ieg_ta_gestion b ON ocs2.siren = b.siren and ocs2.annee_daaaa = b.annee_daaaa  
WHERE ocs2.annee_daaaa ='", an-1,"' 
AND (esane_ch_appart_ind = 'O') 
AND (liasse_etat in (1, 2, 7) 
     or (liasse_etat = 11 and esane_etat not in ('7', '8')) 
     or liasse_etat is null) 
AND (liasse_id = (select cast(max(to_number(g.liasse_id, '9999999999')) as varchar) from ieg_ta_gestion g  
                  where g.liasse_etat in (1, 2, 7, 11) and g.annee_daaaa = ocs2.annee_daaaa and g.siren = ocs2.siren)  
                  or liasse_id is null) ORDER BY ocs2.siren;
")

Liasse_etat : (pour 2021)

| | Frequency | Percent | Valid Percent |
|-------|-----------|----------|---------------|
| 1 | 2896203 | 61.5513 | 61.5513 |
| 2 | 46691 | 0.9923 | 0.9923 |
| 7 | 1747892 | 37.1469 | 37.1469 |
| 11 | 14562 | 0.3095 | 0.3095 |
| Total | 4705348 | 100.0000 | 100.000 |

Pour 2021, on a tout de même 265 307 unités avec liasse_etat et liasse_id à vide.

**Question :
⇒ Est-ce qu’il faut, pour éliminer les liasse_id à vide :
• rajouter un filtre dans la proc sql
• ou filtrer ces unités au moment de la création des tables N et N-1 (cf pg IMPORT_INIT_N_N1.R)
• ou rajouter une condition sur liasse_etat à 1,2,7,11 dans le pgm ID_POP_IMPUTEES pour filtrer dans la table N-1 ces unités et éviter de les avoir dans le bloc de traitement par N-1**

**reponse LK : La 1ere étape semble la mieux**

** OK Unités filtrées **

◦ la variable de durée d’exercice

# Import de la durée d'exercice des unités en N et N-1 
####################################################-#
sql_table_exe_dur <- paste0(" SELECT ocs3.siren, exe_dur,ocs3.annee_daaaa, bmr, liasse_id
    FROM ocs_ta_unite_ocsane ocs3
    LEFT JOIN (SELECT siren, exe_dur, liasse_id,bmr,annee_daaaa FROM ieg_ta_socle_caracteristiques 
    WHERE annee_daaaa in ('",an-1,"','",an,"') and bmr = 'r') soc
    ON ocs3.siren = soc.siren and ocs3.annee_daaaa = soc.annee_daaaa
    WHERE ocs3.annee_daaaa in ('",an-1,"','",an,"') and ocs3.siren is not NULL and substr(ocs3.siren,1,1) not in ('E','P');
    ")

◦ les variables d’évolution (notamment r310) :

#### Import des variables de calcul d'évolution des unités en N et N-1
####################################################################-#
sql_table_evo <- paste0(" SELECT evo1.siren,", var_calc_evo,",evo1.annee_daaaa, bmr, liasse_id
    FROM ocs_ta_unite_ocsane evo1
    LEFT JOIN (SELECT siren, r310, liasse_id, bmr, annee_daaaa FROM ieg_ta_socle WHERE annee_daaaa in ('",an-1,"','",an,"')
    and bmr = 'r') soc
    ON evo1.siren = soc.siren and evo1.annee_daaaa = soc.annee_daaaa
    WHERE evo1.annee_daaaa in ('",an-1,"','",an,"') and evo1.siren is not NULL and substr(evo1.siren,1,1) not in ('E','P');
    ")

Avec ces 2 requêtes, on importe 17 753 191 unités dont 13 241 894 où liasse_id n’est pas vide.
Dans ces 2 tables, on retrouve bien toutes nos unités avec liasse_etat non vide.

**Pouquoi ne pas importer uniquement ces unités ? Cela rajoute des filtres/jointures dans la proc sql et au final, importer les 17 millions de lignes est (beaucoup) plus rapide sans surcharger réellement la mémoire (seulement 5 variables dans nos 2 tables sachant qu’elles sont supprimées dès l’étape suivante après la création des tables N et N-1.**

**reponse LK : je ne comprends pas la question**

• IMPORT_INIT_N_N1.R
Ce pgm :
◦ parallélise les imports des 4 tables définies par les proc sql précédentes
• Environ 700-800s (environ 2 fois de temps que si on importait les 4 tables les unes après les autres)

◦ créée 2 tables (N et N-1) contenant les caractéristiques des unités, les variables d’évolution et les durées d’exercice par fusion des imports
• Environ 200s ⇒ voir si on filtre ou pas les liasse_etat = NA (cf plus haut)

◦ nettoie l’environnement de travail (sql, cluster, les 4 imports...)

On a ainsi 2 tables (pour le moment, nommées df_N et df_N1) :
◦ df_N : 5 523 042 obs avec 14 var
◦ df_N1 : 4 705 348 obs avec 14 var

-------------------------------------------------------------------------------------------------------------------

##########################################################################
#### CREATION_EFF_TRA_RATIO.R
##############################

En entrée de ce pgm, on peut utiliser directement les tables df_N et df_N1 importées.

**Une question en suspens :
En fait, dès le début (à priori ligne 36 dans la dernière version sous git), tu fais un inner_join entre les tables n et n-1. En conséquence, toutes les unités non présentes en N-1 se retrouvent donc, en sortie de pgm, avec un eff_tra_ratio à NA (environ 1.6 millions d'unités). Le problème, c'est que, pour le coup, tu utilises, eff_tra_ratio dans la création de tes classes (stratégie d'aggrégation) pour les imputations par médiane de classe (donc, par exemple, sur les unités créées en N, qui, par définition, ne sont pas présentes en N-1 et auront donc eff_tra_ratio à NA :s**

**réponse LK : Effectivement, j'ai modifié pour mettre un jointure externe à gauche**

Ok, avec la correction précédente, on obtient la répartition suivante pour **eff_tra_ratio** (sur table_entre)

| Modalités| Fréquence | Répart |
| -------- | -------- | -------- |
| 0 | 1167569 | 21.140 |
| 1 | 4091242 | 74.076 |
| 2 | 251061 | 4.546 |
| 3 | 12156 | 0.220 |
| 4 | 1014 | 0.018 |
| Total | 5523042 | 100.00 |

#########################################################################
#### ID_POP_IMPUTEES.R
#######################

◦ Il faudra rajouter, dans ce programme, les liasses_id dans les variables que l’on conserve dans les tables N et N-1. En effet, ces variables vont servir dans les imports des 489 variables à imputer (notamment pour récupérer les valeurs de ces variables en N-1 et les valeurs des liasses 1 et 2 en N pour les créations et entrées de champs).

◦ Attention, rajouter ici une condition sur liasse_etat à 1,2,7,11 dans le pgm ID_POP_IMPUTEES pour filtrer dans la table N-1 ces unités et éviter de les avoir dans le bloc de traitement par N-1 si les liasse_etat = NA n’ont pas été filtré fait auparavant.

⇒ Environ 1 min pour exécuter les 2 fonctions

En sortie de ce programme (avec les liasse_etat à vide filtrées lors de la création des table df_N et df_N1), on obtient une liste de 3 tables à imputer :
• par N-1 : 724 845 unités
• par médiane de strates (créations d’entreprises) : 206 375 unités
• par médiane de strates (entrées de champ) : 16 063 unités

Soit 947 283 unités à imputer.

-------------------------------------------------------------------------------------------------------------------
##########################################################################
#### IMPORT_VAR_IMPUT.R
########################
Ce pgm est à insérer après le pgm ID_POP_IMPUTEES.R.

Il a pour objectif de récupérer les valeurs des variables présents dans le fichier tmp :
◦ pour les unités N-1 présentes en N (c’est à dire les unités de la table id_pop[[1]]
◦ pour les unités N avec des liasses etat à 1 et 2 qui sont des créations de l’année N ou des entrées de champ de l’année N (ce sont les unités permettant de calculer les médianes de strates pour imputer les unités des tables id_pop[[2]] et id_pop[[3]]

Pour les créations servant au calcul des médianes de strates, on utilise les entreprises suivantes :
df_N %>% filter(substr(ent_cre_daaaammjj,1,4) == as.character(an) & liasse_etat %in% c('1', '2')))$siren
⇒ 79 563 unités : à priori, pas beaucoup par rapport au stock d’unités à imputer

Pour les entrées de champs servant au calcul des médianes de strates :

**À voir le code utilisé dans les pgm d’imputation (pour le moment, ce sont les créations qui sont doublonnées ici pour avoir 12 imports en parallèle)**

Pour ce faire, 12 imports sont effectués en parallèle :
◦ 10 pour les unités N-1 (selon le dernier chiffre du SIREN
◦ 1 pour les 2 autres méthodes d’imputation (créations et entrées de champ)

⇒ Environ 2min-2min30

Résultat de l’import :
On obtient ainsi une liste de 12 tables importées (result)
• Les 10ers éléments de la liste, une fois concaténées, permettent d’obtenir une table de 77 097 unités. Parmi celles-ci, on retrouve bien nos 880 435 combinaisons siren-liasse_id(pour l’année N-1) de la table id_pop[[1]] à imputer.

Par contre, pourquoi certains siren ont plusieurs liasses_id pour une année donnée dans la table ieg_ta_socle ? (cf réponse de Louise ci-dessous)
Si une entreprise envoie deux fois ses impôts et donc deux liasses, nous en chargerons deux. Ensuite, un des enjeux d'IEG est de n’en garder qu’une considérée comme « active » (liasse_etat à 1 ou 2)

• Le 11ème élément de la liste est notre table des unités créées contenant nos 79 563 unités.

• Le 12ème élément est notre table des unités entrantes dans le champ contenant nos 74 483 unités.

-------------------------------------------------------------------------------------------------------------------
##########################################################################
#### Les programmes d’imputation par N-1
#########################################

L’import des 489 variables uniquement sur les données « utiles » à l’imputation nécessite d’adapter certains bouts de codes des fonctions de cette partie de l’imputation :

◦ Ajouter un paramètre supplémentaire dans la fonction principale (la liste de 12 tables importées ci-dessus ou au moins les 10ers éléments)

  imputation_n1_complet <- IMPUTATIONS_N_1_COMPLET(
    table_e_N = table_entre,
    table_e_N1 = table_e_N1,
    var_calc_evol = var_calc_evol,
    tableau_types_imputation = tableau_types_imputation,
    table_e_aimputer = id_pop$n1,
    table_var_debut_var_fin = table_var_debut_var_fin,
    liste_import_n1 = result[1:10]
    
  )

◦ Par ricochet, cela entraîne de modifier également la fonction PREPARATION_IMPUTATIONS_N_1

Ajout d’un paramètre

  aimputer <- PREPARATION_IMPUTATIONS_N_1(
    table_e_N = table_e_N,
    table_e_N1 = table_e_N1,
    table_e_aimputer = table_e_aimputer,
    var_calc_evol = var_calc_evol, 
    liste_var_aimputer = liste_var_aimputer, 
    liste_import_n1 = liste_import_n1)

Changement dans la formule de création de aimputer_n1 (ligne 57)
`aimputer_n1 <- dplyr::select(table_e_N1, siren, exe_dur, var_calc_evol, all_of(liste_var_aimputer))`

remplacer par :

  aimputer_n1 <- table_e_N1 %>%
    select(siren,liasse_id,exe_dur,all_of(var_calc_evol)) %>%
    inner_join(do.call(rbind,liste_import_n1) %>%
                 select(-all_of(var_calc_evol)) %>%
                 filter(!is.na(liasse_id)),
               by = c("siren","liasse_id")) %>% 
    select(-liasse_id)

Changement des index de renommage des variables n-1 (présence de liasse_id dorénavant dans les tables)
~~`colnames(aimputer_n1)[4:(i+3)] <- paste0("z", liste_var_aimputer)`~~
**En fait, on supprime la variable liasse_id dans l'étape de création de aimputer_n1 car elle correspond au liasse_id de l'année n-1 et non à celui de l'année n (donc variable inutile pour la suite).
En conséquence, il n'est pas utile de décaler les index commet mis auparavant et on conserve la ligne de code initial:
`colnames(aimputer_n1)[3:(i+2)] <- paste0("z", liste_var_aimputer)`**

A priori, j’ai également l’impression qu’il y a une coquille sur la création de la table data_e où, dans le filtre, on a liasse_etat = 1 & liasse_etat = 2 au lieu de « ou »

Sauf erreur, les autres fonctions d’imputation par N-1 ne sont pas impactées par ces changements

-------------------------------------------------------------------------------------------------------------------
##########################################################################
#### Les programmes d’imputation pour les entreprises créées en N
###################################################################

## **Début de conclusion**
Le programme test_import_donnees.R allant du début de l'import jusqu'à l'export des 10 tables temporaires (inclus) en passant par toutes les imputations tourne d'un seul tenant et met environ 42 minutes.

Le travail restant à réaliser:
- Passer la dernière modification métier sur la branch SNDI (estimation : 5min)
- ajouter sur la branche SNDI le remplissage de orbi4
- Mutualiser les fonctions métier écrites par l'informatique et celles du métier
- Réalisation des imputations spécifiques et des imputations à 0 dans les imputations N1 (estimation : 1h)
- Préparer un jeu de test (estimation : 1h)
- Réaliser plusieurs tests unitaires côté métier (estimation une journée)
- Vérifier que le programme fournit un résultat cohérent (comptage, comparaison avec la production)
- réalisation de la documentation
- rattacher tout le code pour le lancer d'une traite
- export de table pour les medianes de strate

S'il reste du temps :
- paralléliser la partie métier pour gagner en temps de traitement
- optimisation de la partie médiane par strate

Remarque à ne pas oublier :
- séparer les foncions dont la charge est uniquement sur l'informatique de celle du métier pour moins perdre de nouveaux arrivants.