|
from typing import List |
|
import pandas as pd |
|
|
|
|
|
def merge_clients(data): |
|
mnr_df = data['MNR_Client'] |
|
mnr_df['Prestataire'] = 'MNR' |
|
|
|
stm_df = data['STM_Client'] |
|
stm_df['Prestataire'] = 'STM' |
|
|
|
valm_df = data['VALM_Client'] |
|
valm_df['Prestataire'] = 'VALM' |
|
|
|
data['Clients'] = pd.concat([mnr_df, stm_df, valm_df], ignore_index=True) |
|
data['Clients'].rename(columns=dict(zip(data['Clients'].columns,['Code client', 'Type client', 'Nom client', 'Dist STE', 'Duration STE', 'Dist VAL', 'Duration VAL', 'Prestataire'])), inplace=True) |
|
data['Clients'] = data['Clients'][data['Clients']['Type client'] == 'Client'] |
|
|
|
data['Clients']['Dist STE'] = data['Clients']['Dist STE'].fillna(0) |
|
data['Clients']['Duration STE'] = data['Clients']['Duration STE'].fillna(0) |
|
data['Clients']['Dist VAL'] = data['Clients']['Dist VAL'].fillna(0) |
|
data['Clients']['Duration VAL'] = data['Clients']['Duration VAL'].fillna(0) |
|
|
|
del data['MNR_Client'] |
|
del data['STM_Client'] |
|
del data['VALM_Client'] |
|
|
|
|
|
def add_motifs_absences(df: pd.DataFrame, motifs: List[str]): |
|
filtered_df = df[df['Nom client'].isin(['SECMI'])] |
|
intervenants = df['Code intervenant (Identifiant du document de référence -> Document de vente)'].unique() |
|
|
|
for intervenant in intervenants: |
|
rows = [] |
|
for motif in motifs['Libellé GTP2023'].tolist(): |
|
rows.append({ |
|
'Code intervenant (Identifiant du document de référence -> Document de vente)': intervenant, |
|
'Code client': filtered_df.iloc[0]['Code client'] if filtered_df.shape[0] > 0 else 'SECMI', |
|
'Nom client': filtered_df.iloc[0]['Nom client'] if filtered_df.shape[0] > 0 else 'SECMI', |
|
'Code chantier': '000000', |
|
'Libellé': motif |
|
|
|
}) |
|
df = pd.concat([df, pd.DataFrame(rows)], ignore_index=True) |
|
return df |
|
|
|
|
|
def merge_affaires(data): |
|
mnr_df = data['MNR_Affaire'] |
|
mnr_df = add_motifs_absences(mnr_df, data['absences']) |
|
mnr_df['Prestataire'] = 'MNR' |
|
|
|
stm_df = data['STM_Affaire'] |
|
stm_df = add_motifs_absences(stm_df, data['absences']) |
|
stm_df['Prestataire'] = 'STM' |
|
|
|
valm_df = data['VALM_Affaire'] |
|
valm_df = add_motifs_absences(valm_df, data['absences']) |
|
valm_df['Prestataire'] = 'VALM' |
|
|
|
data['affaire'] = pd.concat([mnr_df, stm_df, valm_df], ignore_index=True) |
|
data['affaire']['Code affaire'] = data['affaire']['Code chantier'] + ' - ' + data['affaire']['Libellé'] |
|
del data['affaire']['Code chantier'] |
|
del data['affaire']['Libellé'] |
|
data['affaire'].rename(columns=dict(zip(data['affaire'].columns,['Code intervenant', 'Code client', 'Nom client', 'prestataire', 'Code affaire'])), inplace=True) |
|
del data['MNR_Affaire'] |
|
del data['STM_Affaire'] |
|
del data['VALM_Affaire'] |
|
|
|
def merge_intervenants_affaires(data): |
|
data['all'] = data['affaire'].merge(data['intervenants'], on='Code intervenant', how='left') |
|
data['all'].rename(columns=dict(zip(['Code intervenant', 'Code affaire', 'Nom client', 'Code secteur géographique', 'Veh_Affecte', 'Durée Hebdo', 'Coût horaire'],['intervenant', 'affaire', 'client', 'secteur', 'vehicule affecte', 'contrat heures', 'cout horaire'])), inplace=True) |
|
new_row = pd.DataFrame([['-'] * (data['all'].shape[1])], columns=data['all'].columns) |
|
data['all'] = pd.concat([new_row, data['all']], ignore_index=True) |
|
data['all']['secteur'] = data['all']['secteur'].fillna('STE') |
|
data['all']['contrat heures'] = data['all']['contrat heures'].fillna(0) |
|
data['all']['cout horaire'] = data['all']['cout horaire'].fillna(0) |
|
data['all'] = data['all'].dropna(subset=['intervenant', 'client', 'affaire']) |
|
|
|
def complete_vehicules(data): |
|
data['vehicules']['vehicules'] = 'Collectif - ' + data['vehicules']['IMMAT'] + ' - ' + data['vehicules']['Description'] |
|
data['vehicules'].loc[len(data['vehicules'])] = [None, None, 'Perso'] |
|
data['vehicules'].loc[len(data['vehicules'])] = [None, None, 'Service affecté'] |
|
|
|
def complete_supplements(data): |
|
data['supplements'].rename(columns=dict(zip(data['supplements'].columns,['supplements'])), inplace=True) |
|
data['supplements']['supplements'] = data['supplements']['supplements'].str.replace('_x000D_', ' ') |
|
new_row = pd.DataFrame([['-'] * (data['supplements'].shape[1])], columns=data['supplements'].columns) |
|
data['supplements'] = pd.concat([new_row, data['supplements']], ignore_index=True) |
|
data['supplements'] = data['supplements'][~data['supplements']['supplements'].isin([' H Peinture ', ' H Qualif. Électrique ', ' H Soudure '])] |
|
|
|
def complete_affaires(data): |
|
data['affaire'].rename(columns=dict(zip(['Code intervenant', 'Code affaire', 'Nom client'],['intervenant', 'affaire', 'client'])), inplace=True) |
|
new_row = pd.DataFrame([['-'] * (data['affaire'].shape[1])], columns=data['affaire'].columns) |
|
data['affaire'] = pd.concat([new_row, data['affaire']], ignore_index=True) |
|
|
|
def complete_intervenants(data): |
|
data['intervenants'].rename(columns=dict(zip(['Code intervenant', 'Code secteur géographique', 'Veh_Affecte', 'Durée Hebdo', 'Coût horaire'],['intervenant', 'secteur', 'vehicule affecte', 'contrat heures', 'cout horaire'])), inplace=True) |
|
data['intervenants']['Employeur'] = data['intervenants']['Employeur'].apply(lambda x: x.split()[1] if pd.notna(x) else None) |
|
data['intervenants']['Employeur'] = data['intervenants']['Employeur'].fillna('SECMI') |
|
new_row = pd.DataFrame([['-'] * (data['intervenants'].shape[1])], columns=data['intervenants'].columns) |
|
data['intervenants'] = pd.concat([new_row, data['intervenants']], ignore_index=True) |
|
|
|
data['intervenants']['contrat heures'] = data['intervenants']['contrat heures'].fillna(35) |
|
|
|
def complete_arrets(data): |
|
data['arrets'].rename(columns=dict(zip(data['arrets'].columns,['code_absence', 'H-35.0', 'H-37.0', 'H-37.5', 'H-39.0'])), inplace=True) |
|
data['arrets']['code_absence'] = data['arrets']['code_absence'].str.replace('_x000D_', ' ') |
|
data['arrets'].fillna(0, inplace=True) |
|
|
|
def cast_specifics_to_str(data): |
|
data['all'] = data['all'].astype({'intervenant': str, 'affaire': str, 'client': str, 'prestataire': str}) |
|
data['vehicules'] = data['vehicules'].astype({'IMMAT': str, 'Description': str}) |
|
|
|
data['affaire'] = data['affaire'].astype({'intervenant': str, 'affaire': str, 'client': str, 'prestataire': str}) |
|
data['intervenants'] = data['intervenants'].astype({'intervenant': str, 'secteur': str, 'vehicule affecte': str}) |
