# Chargement des tables
PJC_s <- spark_read_parquet(sc,path = "hdfs:///dataset/MiDAS_v7/FNA/pjc.parquet",memory=FALSE)
PRM_s <- spark_read_parquet(sc,path = "hdfs:///dataset/MiDAS_v7/FNA/prm.parquet",memory=FALSE)
ODD_s <- spark_read_parquet(sc,path = "hdfs:///dataset/MiDAS_v7/FNA/odd.parquet",memory=FALSE)
#### Période étudiée ####
date_fin <- as.Date("2024-12-31")
date_deb <- as.Date("2024-12-01")
##### Allocations agrégées et régimes d'indemnisation #####
codes_pjc <- PJC_s %>%
mutate(code_alloc = case_when(KCALF %in% c("01","21","22","27","40","43","47","54","64","67","82","AC","BB","BK","CJ","DM","EF","EW","FL","GV","HF","HI") ~ "ARE", # Allocation d'aide au retour à l'emploi
KCALF == "CN" ~ "ARE-I", # ARE intermittent
KCALF %in% c("BP","CQ","HU") ~ "ARED", # ARE différée
KCALF == "HF" ~ "CFD", # Allocation de complément de fin de droit
KCALF %in% c("33","34","35","48","49","55","65","83","AD","BC","BN","CK","DQ","EL","EX","FO","GZ","HO") ~ "AREF", # ARE formation
KCALF == "CO" ~ "AREF-I", # AREF intermittent
KCALF %in% c("BQ","CR","HV") ~ "AREF-D", # AREF différée
KCALF == "HG" ~ "CFF", # Allocation de complément de fin de formation
KCALF %in% c("FM","HR") ~ "ARER", # ARE rattrapage
KCALF %in% c("FP","HS") ~ "ARER-F" , # ARER formation
KCALF %in% c("CT","DT","EN") ~ "ARER-I", # ARER intermittent
KCALF %in% c("CU","DU","EO") ~ "ARER-FI", # ARER formation intermittent
KCALF %in% c("EA","GI") ~ "ATI", # Allocation des travailleurs indépendants
KCALF %in% c("EB","GJ") ~ "ATIF", # ATI formation
KCALF %in% c("EC","GS","HD") ~ "ADM", # Allocation des démissionnaires
KCALF %in% c("EP","GT","HE") ~ "ADM-F", # ADM formation
KCALF %in% c("AM","AO","AQ","BU","BV","BW","BX","BY","FR","FS","FT","FU","FV","FW","FX") ~ "ASP", # Allocation de sécurisation professionnelle
KCALF %in% c("AK","GK") ~ "ASP-ARE",
KCALF %in% c("AN","AP","AR","BZ","CA","CB","CC","GC","GD","GE","GF","GG","GH") ~ "ASP-F", # ASP formation
KCALF %in% c("AL","GL") ~ "ASP-AREF",
KCALF %in% c("AG","AS","CD","FY") ~ "IDR-CSP", # Indemnité différentielle de reclassement - Contrat de sécurisation professionnel
KCALF == "61" ~ "ASR", # Allocation spécifique de reclassement
KCALF == "62" ~ "ASR-ARE",
KCALF == "84" ~ "ASR-F", # ASR formation
KCALF == "85" ~ "ASR-AREF",
KCALF %in% c("70","88","91") ~ "ATP", # Allocation de transition professionnelle
KCALF %in% c("66","86","AG") ~ "ADR", # Aide différentielle de reclassement
KCALF %in% c("68","87","AH","AZ","DR","ED","EH","GX") ~ "ARCE", # Aide à la reprise ou à la création d'entreprise
KCALF == "CP" ~ "ARCE-I", # ARCE intermittent
KCALF %in% c("AW","BL","CS","CV","DN","EG","GW") ~ "DC", # Allocation décès
KCALF == "57" ~ "AFPS", # Allocation du Fonds de professionnalisation et de solidarité
KCALF == "58" ~ "AFPS-F", # AFPS formation
KCALF == "59" ~ "AFT", # Allocation du Fonds transitoire
KCALF == "60" ~ "AFT-F", # AFT formation
KCALF %in% c("23","28","31","32") ~ "AFD-RAC", # Allocation de fin de droit
KCALF %in% c("CH","CW","ES","ET","EV","EU","FA","FB","FC","FD","FE","FF","FG","FI","FJ","FK","GM","GN","GP","GQ","GR","HQ") ~ "RAC-PRM", # Autres aides et primes
KCALF %in% c("25","30","44","56","BD") ~ "ASS", # Allocation de solidarité spécifique
KCALF == "51" ~ "ASS-F", # ASS formation
KCALF == "24" ~ "AI", # Allocation d'insertion
KCALF %in% c("73","74","90") ~ "ATA", # Allocation temporaire d'attente
KCALF %in% c("52","53","93","94","97","98") ~ "AER", # Allocation équivalent retraite
KCALF %in% c("AT","AV","BE","BF","BS","BT") ~ "ATS", # Allocation transitoire de solidarité
KCALF %in% c("04","63") ~ "AF", # Allocation forfaitaire
KCALF %in% c("71","75","DV") ~ "APS", # Allocation de professionnalisation et de solidarité
KCALF %in% c("72","76","DX") ~ "APS-F", # APS formation
KCALF %in% c("77","DW") ~ "AFD", # Allocation de fin de droit
KCALF %in% c("78","DY") ~ "AFD-F", # AFD formation
KCALF == "96" ~ "AEPE", # Alocation exceptionnelle de retour à l'emploi
KCALF %in% c("99","AY","BG","BJ","BR","CI","CX","DZ","EE") ~ "RFF-RSN", # Rémunération de fin de formation (régime Etat)
KCALF == "39" ~ "RSP", # Rémunération des stagiaires du régime public
KCALF %in% c("92","95")~ "AFDEF", # Allocation en faveur des demandeurs d'emploi en formation
KCALF == "29" ~ "ASC", # Allocation spécifique de conversion
KCALF %in% c("41","50","FA") ~ "AFF", # Allocation de fin de formation
KCALF == "45" ~ "ACCRE-ASS", # Aide aux chômeurs créateurs d'entreprise
KCALF %in% c("AE","BH","BM","CL","DO","EI","EY","FN","GY","HL") ~ "ARE-CG", # ARE-convention de gestion
KCALF %in% c("AF","BI","BO","CM","DR","EM","EZ","FQ","HA","HP") ~ "AREF-CG",
KCALF == "81" ~ "RFFT", # Rémunération formation France Travail
KCALF %in% c("EQ","ER","FH","GU","HH","HT") ~ "RFF", # Rémunération de fin de formation
KCALF %in% c("0C","0E","15") ~ "ASFNE", # Allocation conventionnelle du Fonds national de l'emploi (pré-retraite)
KCALF %in% c("11","17","18") ~ "PRP", # Pré-retraite progressive
KCALF == "26" ~ "ACO", # Allocation complémentaire
KCALF == "42" ~ "ARPE", # Allocation de remplacement pour l'emploi
KCALF %in% c("CY","CZ","DA","DB","DC","DD","DE","DF","DG","DH","DI","DJ","DK","DL") ~ "AAP", # Allocation d'accompagnement personnalisé
KCALF == "AI" ~ "ADR-CG", # Aide différentielle de reclassement convention de gestion
KCALF %in% c("AJ","BA","CP","DS","EK","HC","HN") ~ "ARCE-CG",
KCALF %in% c("DP","EJ","HB") ~ "DC-CG",
T ~ "NA"))
pjc_fm <- codes_pjc %>%
select(id_midas,KROD3,KDDPJ,KDFPJ,code_alloc,KCPJC) %>% # On conserve les variables d'intérêt
filter(KDDPJ <= date_fin & KDFPJ >= date_fin) # On se restreint aux indemnisables à fin décembre
prm_fm <- PRM_s %>%
select(id_midas,KROD7,KDDTA,KDFTA,KCDPR) %>%
mutate(prime = case_when(KCDPR %in% c("AAAH","AAAH16") ~ "CW", # RAC-PRM
KCDPR == "ACEJ" ~ "FG", # RAC-PRM
KCDPR == "ADR11" ~ "AG", # IDR-CSP
KCDPR == "AES-S22" ~ "GP", # RAC-PRM (GO correspond à la même aide)
KCDPR == "AGDE" ~ "FB", # RAC-PRM
KCDPR == "AFD" ~ "77", # AFD
KCDPR == "AFFORM" ~ "FA", # RAC-PRM
KCDPR == "AIJ20" ~ "ET", # RAC-PRM
KCDPR == "AIJA20" ~ "EV", # RAC-PRM
KCDPR == "AJD20" ~ "EU", # RAC-PRM
KCDPR == "APJE" ~ "FK", # RAC-PRM
KCDPR == "ARCE06" ~ "68", # ARCE
KCDPR == "ARCE12" ~ "AZ", # ARCE
KCDPR == "ARCE12CG" ~ "BA", # ARCE-CG
KCDPR == "ARCE17" ~ "DR", # ARCE
KCDPR == "ARCE19" ~ "EH", # ARCE
KCDPR == "ARCE23" ~ "GX", # ARCE
KCDPR == "ARCE24" ~ "HK", # ARCE
KCDPR == "ARCE23CG" ~ "HC", # ARCECG
KCDPR == "ARCE24CG" ~ "HN", # ARCECG
KCDPR == "ARCEA816" ~ "CP", # ARCECG
KCDPR == "ARCECG17" ~ "DS", # ARCECG
KCDPR == "ARCECG19" ~ "EK", # ARCECG
KCDPR == "ARE" ~ "AC", # ARE
KCDPR == "ARE-CG" ~ "AE", # ARE-CG
KCDPR == "ARE03" ~ "54", # ARE
KCDPR == "AREG" ~ "GQ", # RAC-PRM
KCDPR == "ASP15" ~ "BU", # ASP
KCDPR == "ASP21" ~ "FR", # ASP
KCDPR == "ASP21DC" ~ "FW", # ASP
KCDPR == "ASS04" ~ "56", # ASS
KCDPR == "CSPPR15" ~ "CE", # ASP
KCDPR == "CSPPR15R" ~ "CG", # ASP
KCDPR == "CSPPR21" ~ "FZ", # ASP
KCDPR == "CSPPR21E" ~ "GA",# ASP
KCDPR %in% c("DC/ARE06","DC/ARE09","DC/ARE11","DC/ARE14","DC/ARE17","DC/ARE19","DC/ARE23") ~ "GW", # DC
KCDPR == "DC/ARECG" ~ "HB", # DC-CG
KCDPR == "DCA816" ~ "CS", # DC
KCDPR == "DELD" ~ "FJ", # RAC-PRM
KCDPR == "FSSAACRE" ~ "FC", # RAC-PRM
KCDPR == "FSSAAFAM" ~ "FE", # RAC-PRM
KCDPR == "FSSAAMOB" ~ "FD", # RAC-PRM
KCDPR == "FSSAMASP" ~ "GM", # RAC-PRM
KCDPR == "FSSAMRAN" ~ "GN", # RAC-PRM
KCDPR == "FSSAPREC" ~ "FF", # RAC-PRM
KCDPR == "FSSARTRI" ~ "GR", # RAC-PRM
KCDPR == "IDR" ~ "AS", # IDR-CSP
KCDPR == "IDRCSP15" ~ "CD", # IDR-CSP
KCDPR == "IDRCSP21" ~ "FY", # IDR
KCDPR == "INFL21" ~ "FI", # # RAC-PRM
KCDPR == "JO24" ~ "HQ", # RAC-PRM
KCDPR == "PEP20" ~ "ES", # RAC-PRM
KCDPR == "PTS15" ~ "CH", # RAC-PRM
T ~ "NA"),
code_prime = case_when(prime %in% c("54","AC") ~ "ARE",
prime %in% c("BU","CE","CG","FR","FW","FZ","GA") ~ "ASP",
prime %in% c("AG","AS","CD","FY") ~ "IDR-CSP",
prime == "77" ~ "AFD",
prime %in% c("68","AH","AZ","DR","ED","EH","GX","HK") ~ "ARCE",
prime %in% c("AW","BL","CS","CV","DN","EG","GW") ~ "DC",
prime %in% c("CH","CW","ES","ET","EV","EU","FA","FB","FC","FD","FE","FF","FG","FI","FJ","FK","GM","GN","GP","GQ","GR","HQ") ~ "RAC-PRM", # Autres aides et primes
prime == "56" ~ "ASS",
prime == "AE" ~ "ARE-CG",
prime %in% c("AJ","BA","CP","DS","EK","HC","HN") ~ "ARCE-CG",
prime %in% c("DP","EJ","HB") ~ "DC-CG",
T ~ "NA")) %>%
filter(KDDTA <= date_fin & KDFTA >= date_fin) # On conserve les aides et primes versées sur la période étudiée
# Sélection des variables d'intérêt dans la table ODD
odd_fm <- ODD_s %>%
select(id_midas,KROD1,KCRD)
alloc_fm <- pjc_fm %>%
left_join(odd_fm, by = c("id_midas","KROD1")) %>%
full_join(prm_fm, by = "id_midas") %>%
mutate(alloc = if_else(!is.na(code_alloc),code_alloc,code_prime),
regime = case_when(alloc %in% c("ARE","ARE-I","ARED","CFD","AREF","AREF-I","AREF-D","CFF","ARER","ARER-F","ARER-I","ARER-FI",
"ATI","ATIF","ADM","ADM-F","ASP","ASP-ARE","ASP-F","ASP-AREF","IDR-CSP","ASR","ASR-ARE","ASR-F","ASR-AREF",
"ATP","ADR","ARCE","ARCE-I","DC","AFPS","AFPS-F","AFT","AFT-F","AFD-RAC","RAC-PRM") ~ "1", # Régime de l'assurance-chômage (Unédic)
alloc %in% c("ASS","ASS-F","AI","ATA","AER","ATS","AF","APS","APS-F","AFD","AFD-F","AEPE","RFF-RSN","RSP","AFDEF",
"ASC","AFF","ACCRE-ASS") ~ "2", # Régime de solidarité national (Etat)
alloc %in% c("ARE-CG","AREF-CG","RFFT","RFF","ASFNE","PRP","ACO","ARPE","AAP","ADR-CG","ARCE-CG","DC-CG") ~ "3", # Régime de France Travail
T ~ "NA")) %>%
sdf_register(name = "alloc_fm_cache")
tbl_cache(sc, name = "alloc_fm_cache")Allocations agrégées et régimes d’indemnisation
Date de mise à jour : septembre 2025
Cette fiche regroupe les modalités qui renseignent sur les allocations, aides et primes perçues par les allocataires de l’assurance-chômage. De nouvelles modalités apparaissent avec l’ajout de nouveaux dispositifs d’indemnisation et la modification des conventions d’assurance-chômage. A chaque nouvelle vague de MiDAS, cette fiche sera donc mise à jour dès que possible pour les intégrer.
Les codes allocations, renseignés dans la variable KCALF (segment PJC) et la variable KCAAJ (segment ODD), sont identiques. Les aides et primes sont renseignées dans la variable KCDPR (segment PRM).
Cette fiche regroupe l’ensemble des allocations, aides et primes versées par régimes d’indemnisation puis par groupes d’allocation. Elle fournit également un descriptif des trois régimes d’indemnisation ainsi qu’un script pour les reconstituer.
Régime de l’assurance-chômage
L’assurance-chômage garantit une allocation, pendant une durée limitée, aux salariés involontairement privés d’emploi et ayant suffisamment contribué. L’allocation d’aide au retour à l’emploi (ARE) est la principale allocation d’assurance-chômage, puisqu’elle représente 90 % des dépenses. Elle est versée aux personnes involontairement privées de leur dernier emploi ou qui démissionnent pour des raisons légitimes. En cas de formation, elle est remplacée par l’ARE-formation (Aref), de même montant que l’ARE. Le régime de l’assurance-chômage comprend également l’allocation de sécurisation professionnelle (ASP) ou encore l’aide à la reprise ou à la création d’entreprise (Arce).
Régime de l’État
Le régime de solidarité prend en charge les personnes n’ayant pas ou plus de droit à l’assurance-chômage et pouvant justifier d’une durée d’activité de 5 ans dans les 10 années précédant la fin du contrat de travail. L’allocation de solidarité spécifique (ASS) est la principale allocation d’État. Les autres allocations sont : l’ASS-formation, l’aide aux chômeurs et créateurs d’entreprise (Accre-ASS), l’allocation équivalent retraite (AER), devenue l’allocation transitoire de solidarité (ATS), l’allocation temporaire d’attente (ATA), ainsi que les allocations du Fonds de professionnalisation et de solidarité (AFPS), qui regroupent l’allocation de professionnalisation et de solidarité (APS) et l’allocation de fin de droits (AFD).
Régime de France Travail
Le régime de France Travail concerne les personnes prises en charge par l’opérateur au titre d’une convention de gestion (qui permet aux employeurs publics de confier la gestion administrative de l’indemnisation à France Travail) ou d’une formation : il peut s’agir d’un projet de formation cofinancé par France Travail, qui donne droit à une rémunération de formation de France Travail (RFFT, anciennement RFPE), ou bien d’une rémunération de fin de formation (RFF), si l’allocation de formation perçue par le demandeur d’emploi se termine avant la fin de sa formation.
Reconstituer les régimes d’indemnisation
On peut reconstituer les régimes d’indemnisation en appariant les tables PJC, ODD et PRM.
# Chargement des tables
PJC_s = spark.read.parquet("hdfs:///dataset/MiDAS_v6/FNA/pjc.parquet")
PRM_s = spark.read.parquet("hdfs:///dataset/MiDAS_v6/FNA/prm.parquet")
ODD_s = spark.read.parquet("hdfs:///dataset/MiDAS_v6/FNA/odd.parquet")
# Période étudiée
date_fin = "2024-12-31"
codes_pjc = (
PJC_s
.withColumnRenamed("KROD3", "KROD1") # Renommage pour appariement avec ODD
.filter((F.col("KDDPJ") <= date_fin) & (F.col("KDFPJ") >= date_fin)) # Filtrage des périodes actives à fin décembre
.withColumn("alloc", F.when(F.col("KCALF").isin(
"01","21","22","27","40","43","47","54","64","67","82","AC","BB","BK","CJ","DM","EF","EW","FL","GV","HF"), "01") # ARE
.when(F.col("KCALF") == "CN", "02") # ARE-I
.when(F.col("KCALF").isin("BP","CQ","HD"), "03") # ARED
.when(F.col("KCALF").isin("33","34","35","48","49","55","65","83","AD","BC","BN","CK","DQ","EL","EX","FO","GZ","HG"), "04") # AREF-I
.when(F.col("KCALF") == "CO", "05") # AREF-I
.when(F.col("KCALF").isin("BQ","CR","HE"), "06") # AREF-D
.when(F.col("KCALF") == "FM", "07") # ARER
.when(F.col("KCALF").isin("CT","DT","EN"), "08") # ARER-I
.when(F.col("KCALF").isin("CU","DU","EO"), "09") # ARER-FI
.when(F.col("KCALF") == "FP", "10") # AREFR
.when(F.col("KCALF").isin("EA","GI"), "11") # ATI
.when(F.col("KCALF").isin("EB","GJ"), "12") # ATIF
.when(F.col("KCALF").isin("EC","GS"), "13") # ADM
.when(F.col("KCALF").isin("EP","GT"), "14") # ADM-F
.when(F.col("KCALF").isin("AM","AO","AQ","BU","BV","BW","BX","BY","FR","FS","FT","FU","FV","FW","FX"), "15") # ASP
.when(F.col("KCALF").isin("AN","AP","AR","BZ","CA","CB","CC","GC","GD","GE","GF","GG","GH"), "16") # ASP-F
.when(F.col("KCALF").isin("AK","GK"), "17") # ASP-ARE
.when(F.col("KCALF").isin("AL","GL"), "18") # ASP-AREF
.when(F.col("KCALF") == "61", "20") # ASR
.when(F.col("KCALF") == "84", "21") # ASRF
.when(F.col("KCALF") == "62", "22") # ASR-ARE
.when(F.col("KCALF") == "85", "23") # ASR-AREF
.when(F.col("KCALF").isin("70","88","91"), "24") # ATP
.when(F.col("KCALF").isin("66","86","AG"), "25") # ADR
.when(F.col("KCALF") == "CP", "27") # ARCE-I
.when(F.col("KCALF") == "57", "29") # AFPS
.when(F.col("KCALF") == "58", "30") # AFPSF
.when(F.col("KCALF") == "59", "31") # AFT
.when(F.col("KCALF") == "60", "32") # AFTF
.when(F.col("KCALF").isin("23","28","31","32"), "33") # AFD-RAC
.when(F.col("KCALF").isin("25","30","44","56","BD"), "34") # ASS
.when(F.col("KCALF") == "51", "35") # ASS-F
.when(F.col("KCALF") == "24", "36") # AI
.when(F.col("KCALF").isin("73","74","90"), "37") # ATA
.when(F.col("KCALF").isin("52","53","93","94","97","98"), "38") # AER
.when(F.col("KCALF").isin("AT","AV","BE","BF","BS","BT"), "39") # ATS
.when(F.col("KCALF").isin("04","63"), "40") # AF
.when(F.col("KCALF").isin("71","75","DV"), "41") # APS
.when(F.col("KCALF").isin("72","76","DX"), "42") # APS-F
.when(F.col("KCALF").isin("77","DW"), "43") # AFD
.when(F.col("KCALF").isin("78","DY"), "44") # AFD-F
.when(F.col("KCALF") == "96", "45") # AEPE
.when(F.col("KCALF").isin("99","AY","BG","BJ","BR","CI","CX","DZ","EE"), "46") # RFF
.when(F.col("KCALF") == "39", "47") # RSP
.when(F.col("KCALF").isin("92","95"), "48") # AFDEF
.when(F.col("KCALF") == "29", "49") # ASC
.when(F.col("KCALF").isin("41","50"), "50") # AFF
.when(F.col("KCALF") == "45", "51") # ACCRE-ASS
.when(F.col("KCALF").isin("AE","BH","BM","CL","DO","EI","EY","FN","GY"), "52") # ARE-CG
.when(F.col("KCALF").isin("AF","BI","BO","CM","DR","EM","EZ","FQ","HA"), "53") # AREF-CG
.when(F.col("KCALF") == "81", "54") # RFPE
.when(F.col("KCALF").isin("EQ","ER","FH","GU","HH"), "55") # RFF
.when(F.col("KCALF").isin("0C","0E","15"), "56") # ASFNE
.when(F.col("KCALF").isin("11","17","18"), "57") # PRP
.when(F.col("KCALF") == "26", "58") # ACO
.when(F.col("KCALF") == "42", "59") # ARPE
.when(F.col("KCALF").isin("CY","CZ","DA","DB","DC","DD","DE","DF","DG","DH","DI","DJ","DK","DL"), "60") # AAP
.when(F.col("KCALF") == "AI", "61") # ADR-CG
.otherwise("NA") # Valeur par défaut
)
.withColumn("regime_alloc", F.when(F.col("alloc").isin(
"01","02","03","04","05","06","07","08","09","10","11","12","13","14","15",
"16","17","18","20","21","22","23","24","25","27","29","30","31","32","33","RAC-PRM"), "1") # Régime de l'assurance-chômage
.when(F.col("alloc").isin(
"34","35","36","37","38","39","40","41","42","43","44","45","46","47","48","49","50","51"), "2") # Régime de l'État
.when(F.col("alloc").isin(
"52","53","54","55","56","57","58","59","60","61"), "3") # Régime de France Travail
.otherwise("NA")
)
)
prm_fm = (
PRM_s
.select("id_midas", "KROD7", "KDDTA", "KDFTA", "KCDPR")
.withColumn("prime", F.when(F.col("KCDPR").isin("AAAH", "AAAH16"), "CW") # RAC-PRM
.when(F.col("KCDPR") == "ACEJ", "FG") # RAC-PRM
.when(F.col("KCDPR") == "ADR11", "AG") # IDR-CSP
.when(F.col("KCDPR") == "AES-S22", "GP") # RAC-PRM (GO correspond à la même aide)
.when(F.col("KCDPR") == "AFFORM", "FA") # RAC-PRM
.when(F.col("KCDPR") == "AIJ20", "ET") # RAC-PRM
.when(F.col("KCDPR") == "AIJA20", "EV") # RAC-PRM
.when(F.col("KCDPR") == "AJD20", "EU") # RAC-PRM
.when(F.col("KCDPR") == "APJE", "FK") # RAC-PRM
.when(F.col("KCDPR") == "ARCE06", "68") # ARCE
.when(F.col("KCDPR") == "ARCE12", "AZ") # ARCE
.when(F.col("KCDPR") == "ARCE12CG", "BA") # ARCE-CG
.when(F.col("KCDPR") == "ARCE17", "DR") # ARCE
.when(F.col("KCDPR") == "ARCE19", "EH") # ARCE
.when(F.col("KCDPR") == "ARCE23", "GX") # ARCE
.when(F.col("KCDPR") == "ARCE23CG", "HC") # ARCECG
.when(F.col("KCDPR") == "ARCEA816", "CP") # ARCECG
.when(F.col("KCDPR") == "ARCECG17", "DS") # ARCECG
.when(F.col("KCDPR") == "ARCECG19", "EK") # ARCECG
.when(F.col("KCDPR") == "ARE", "AC") # ARE
.when(F.col("KCDPR") == "ARE-CG", "AE") # ARE-CG
.when(F.col("KCDPR") == "ARE03", "54") # ARE
.when(F.col("KCDPR") == "AREG", "GQ") # RAC-PRM
.when(F.col("KCDPR") == "ASP15", "BU") # ASP
.when(F.col("KCDPR") == "ASP21", "FR") # ASP
.when(F.col("KCDPR") == "ASP21DC", "FW") # ASP
.when(F.col("KCDPR") == "ASS04", "56") # ASS
.when(F.col("KCDPR") == "CSPPR15", "CE") # ASP
.when(F.col("KCDPR") == "CSPPR15R", "CG") # ASP
.when(F.col("KCDPR") == "CSPPR21", "FZ") # ASP
.when(F.col("KCDPR") == "CSPPR21E", "GA") # ASP
.when(F.col("KCDPR").isin("DCARE06","DCARE09","DCARE11","DCARE14","DCARE17","DCARE19","DCARE23"), "GW") # DC
.when(F.col("KCDPR") == "DCARECG", "HB") # DC-CG
.when(F.col("KCDPR") == "DCA816", "CS") # DC
.when(F.col("KCDPR") == "DELD", "FJ") # RAC-PRM
.when(F.col("KCDPR") == "FSSAACRE", "FC") # RAC-PRM
.when(F.col("KCDPR") == "FSSAAFAM", "FE") # RAC-PRM
.when(F.col("KCDPR") == "FSSAAMOB", "FD") # RAC-PRM
.when(F.col("KCDPR") == "FSSAMASP", "GM") # RAC-PRM
.when(F.col("KCDPR") == "FSSAMRAN", "GN") # RAC-PRM
.when(F.col("KCDPR") == "FSSAPREC", "FF") # RAC-PRM
.when(F.col("KCDPR") == "FSSARTRI", "GR") # RAC-PRM
.when(F.col("KCDPR") == "IDR", "AS") # IDR-CSP
.when(F.col("KCDPR") == "IDRCSP15", "CD") # IDR-CSP
.when(F.col("KCDPR") == "IDRCSP21", "FY") # IDR
.when(F.col("KCDPR") == "INFL21", "FI") # à définir
.when(F.col("KCDPR") == "JO2024", "") # à définir
.when(F.col("KCDPR") == "PEP20", "ES") # RAC-PRM
.when(F.col("KCDPR") == "PTS15", "CH") # RAC-PRM
.otherwise("NA")
)
.withColumn("prime_alloc", F.when(F.col("prime").isin("54", "AC"), "01") # ARE
.when(F.col("prime").isin("BU","CE","CG","FR","FW","FZ","GA"), "15") # ASP
.when(F.col("prime").isin("AG","AS","CD","FY"), "19") # IDR-CSP
.when(F.col("prime").isin("68","87","AH","AZ","DR","ED","EH","GX"), "26") # ARCE
.when(F.col("prime").isin("AW","BL","CS","CV","DN","EG","GW"), "28") # DC
.when(F.col("prime").isin("CH","CW","ES","ET","EV","EU","FA","FC","FD","FE","FF","FG","FJ","FK","GM","GN","GP","GQ","GR"), "RAC-PRM") # Autres aides
.when(F.col("prime") == "56", "34") # ASS
.when(F.col("prime") == "AE", "52") # ARE-CG
.when(F.col("prime").isin("AJ","BA","CP","DS","EK","HC"), "62") # ARCE-CG
.when(F.col("prime").isin("DP","EJ","HB"), "63") # DC-CG
.otherwise("NA")
)
.filter((F.col("KDDTA") <= date_fin) & (F.col("KDFTA") >= date_fin)) # Filtrage des aides versées sur la période étudiée
)
alloc_fm = (
pjc_fm
.join(ODD_s, on=["id_midas", "KROD1"], how="left")
.join(prm_fm, on="id_midas", how="left")
)
alloc_fm.registerTempTable("alloc_fm_cache")
spark.catalog.cacheTable("alloc_fm_cache")