Exercise 001_09

Cahier des charges⚓

1
# 001_09 — Export et manipulation avancée avec pandas
2
3
## Objectifs
4
- Lire un fichier Excel existant (001_05.xlsx)
5
- Effectuer des transformations complexes avec pandas
6
- Démontrer les opérations avancées : pivot, merge, groupby
7
- Générer un rapport consolidé multi-sources
8
- Ajouter des formules Excel dans les cellules
9
10
## Structure des feuilles
11
12
### Feuille "do_source"
13
Données lues depuis 001_05.xlsx (feuille "do")
14
15
### Feuille "do_pivot"
16
Table pivot avec pandas.pivot_table() :
17
- Index : Classe de ttf(i)
18
- Colonnes : Type de métrique
19
- Values : Statistiques agrégées (count, mean, sum)
20
21
### Feuille "do_merged"
22
Fusion de plusieurs DataFrames :
23
- Jointure entre données et classes
24
- Ajout d'informations calculées
25
- Utilisation de pd.merge()
26
27
### Feuille "do_grouped"
28
Agrégations avec groupby() :
29
- Groupement par classe
30
- Calculs : somme, moyenne, min, max
31
- Plusieurs niveaux de groupement
32
33
### Feuille "do_formulas"
34
Cellules avec formules Excel natives :
35
- Formules de somme : =SUM(B2:B11)
36
- Formules conditionnelles : =IF(C2>500, "Long", "Court")
37
- Références entre feuilles : =do_source!A2
38
- Formules statistiques : =AVERAGE(), =STDEV()
39
40
### Feuille "do_report"
41
Rapport consolidé formaté :
42
- En-têtes mis en forme
43
- Cellules fusionnées
44
- Formats conditionnels (via xlsxwriter)
45
- Résumé multi-sources
46
47
## Opérations pandas démontrées
48
49
### Lecture Excel
50
```python
51
df = pd.read_excel('001_do/001_05.xlsx', sheet_name='do')
52
```
53
54
### Pivot table
55
```python
56
pivot = df.pivot_table(
57
    values='N(i)',
58
    index='classe',
59
    columns='type',
60
    aggfunc=['sum', 'mean', 'count']
61
)
62
```
63
64
### Merge (jointure)
65
```python
66
df_merged = pd.merge(
67
    df_left, 
68
    df_right, 
69
    on='i', 
70
    how='inner'
71
)
72
```
73
74
### GroupBy (agrégation)
75
```python
76
grouped = df.groupby('classe').agg({
77
    'N(i)': ['sum', 'mean', 'min', 'max'],
78
    'ttf(i)': ['mean', 'std']
79
})
80
```
81
82
### Apply (fonction personnalisée)
83
```python
84
df['categorie'] = df['ttf(i)'].apply(
85
    lambda x: 'Court' if x < 500 else 'Long'
86
)
87
```
88
89
## Formules Excel avec xlsxwriter
90
91
### Écriture de formules
92
```python
93
worksheet.write_formula('D2', '=B2+C2')
94
worksheet.write_formula('E2', '=SUM(B2:D2)')
95
worksheet.write_formula('F2', '=IF(E2>1000, "OK", "KO")')
96
```
97
98
### Références entre feuilles
99
```python
100
worksheet.write_formula('A1', '=do_source!B5')
101
```
102
103
### Formules statistiques
104
```python
105
worksheet.write_formula('B12', '=AVERAGE(B2:B11)')
106
worksheet.write_formula('B13', '=STDEV.S(B2:B11)')
107
```
108
109
## Formatage xlsxwriter
110
111
### Formats de cellules
112
```python
113
bold = workbook.add_format({'bold': True})
114
money = workbook.add_format({'num_format': '#,##0.00'})
115
percent = workbook.add_format({'num_format': '0.0%'})
116
```
117
118
### Fusion de cellules
119
```python
120
worksheet.merge_range('A1:D1', 'Titre du rapport', title_format)
121
```
122
123
### Largeur de colonnes
124
```python
125
worksheet.set_column('A:A', 15)  # Colonne A = 15 caractères
126
```
127
128
## Utilisation
129
Lit 001_05.xlsx et génère 001_09.xlsx avec analyses avancées
130
Code Python⚓
1
# TD_EDC_01_001_09.py
2
# Résumé : Manipulation avancée pandas - pivot, merge, groupby, formules Excel
3
# Lit 001_05.xlsx et génère des analyses avancées
4
from pathlib import Path
5
import subprocess
6
import pandas as pd
7
import xlsxwriter
8
9
10
def build_paths() -> tuple:
11
    """Calcule les chemins des fichiers source et destination."""
12
    script = Path(__file__).resolve()
13
    root = script.parent.parent
14
    stem = script.stem
15
    base_dir_name = script.parent.name
16
    xlsx_dir = root / f"{base_dir_name}_do"
17
    
18
    source_path = xlsx_dir / "001_05.xlsx"
19
    dest_path = (xlsx_dir / f"{stem}.xlsx").resolve()
20
    
21
    return source_path, dest_path
22
23
24
def delete_if_exists(file_path: Path) -> None:
25
    """Supprime le fichier s'il existe déjà."""
26
    if file_path.exists():
27
        file_path.unlink()
28
29
30
def read_source_data(source_path: Path) -> pd.DataFrame:
31
    """Lit les données depuis 001_05.xlsx."""
32
    if not source_path.exists():
33
        raise FileNotFoundError(f"Le fichier source {source_path} n'existe pas. Exécutez d'abord 001_05.py")
34
    
35
    df = pd.read_excel(source_path, sheet_name='do')
36
    # Supprimer la colonne 'classe' si elle existe (type Interval non supporté)
37
    if 'classe' in df.columns:
38
        df = df.drop('classe', axis=1)
39
    
40
    return df
41
42
43
def create_pivot_table(df: pd.DataFrame) -> pd.DataFrame:
44
    """Crée une table pivot avec agrégations."""
45
    # Créer une catégorie ttf pour le pivot
46
    df['categorie_ttf'] = pd.cut(df['ttf(i)'], bins=3, labels=['Court', 'Moyen', 'Long'])
47
    
48
    pivot = df.pivot_table(
49
        values=['N(i)', 'dN(i)'],
50
        index='categorie_ttf',
51
        aggfunc={'N(i)': ['sum', 'mean', 'min', 'max'], 'dN(i)': 'sum'}
52
    )
53
    
54
    # Aplatir les colonnes multi-niveaux
55
    pivot.columns = ['_'.join(col).strip() for col in pivot.columns.values]
56
    pivot = pivot.reset_index()
57
    
58
    return pivot
59
60
61
def create_grouped_data(df: pd.DataFrame) -> pd.DataFrame:
62
    """Crée des agrégations avec groupby."""
63
    df['categorie_ttf'] = pd.cut(df['ttf(i)'], bins=3, labels=['Court', 'Moyen', 'Long'])
64
    
65
    grouped = df.groupby('categorie_ttf').agg({
66
        'ttf(i)': ['mean', 'std', 'min', 'max'],
67
        'N(i)': ['sum', 'mean'],
68
        'dN(i)': 'sum'
69
    })
70
    
71
    # Aplatir les colonnes
72
    grouped.columns = ['_'.join(col).strip() for col in grouped.columns.values]
73
    grouped = grouped.reset_index()
74
    
75
    return grouped
76
77
78
def create_merged_data(df: pd.DataFrame) -> pd.DataFrame:
79
    """Crée un merge avec des données calculées."""
80
    # Créer un DataFrame auxiliaire avec des statistiques
81
    df_stats = pd.DataFrame({
82
        'i': df['i'],
83
        'cumul_dN': df['dN(i)'].cumsum(),
84
        'taux_defaillance': (df['dN(i)'] / df['N(i)'].shift(1)).fillna(0)
85
    })
86
    
87
    # Merge
88
    df_merged = pd.merge(df, df_stats, on='i', how='inner')
89
    
90
    return df_merged
91
92
93
def write_to_excel_with_formulas(dest_path: Path, df_source: pd.DataFrame,
94
                                   df_pivot: pd.DataFrame, df_merged: pd.DataFrame,
95
                                   df_grouped: pd.DataFrame) -> None:
96
    """Écrit dans Excel avec formules et formatage."""
97
    workbook = xlsxwriter.Workbook(str(dest_path))
98
    
99
    # Format bold pour en-têtes
100
    bold = workbook.add_format({'bold': True, 'bg_color': '#D3D3D3'})
101
    number_format = workbook.add_format({'num_format': '0.00'})
102
    
103
    # Feuille 1 : Données source
104
    ws_source = workbook.add_worksheet("do_source")
105
    for col_num, col_name in enumerate(df_source.columns):
106
        ws_source.write(0, col_num, col_name, bold)
107
    for row_num, row_data in enumerate(df_source.values, start=1):
108
        for col_num, value in enumerate(row_data):
109
            ws_source.write(row_num, col_num, value)
110
    print(f"✓ Feuille 'do_source' créée")
111
    
112
    # Feuille 2 : Table pivot
113
    ws_pivot = workbook.add_worksheet("do_pivot")
114
    for col_num, col_name in enumerate(df_pivot.columns):
115
        ws_pivot.write(0, col_num, col_name, bold)
116
    for row_num, row_data in enumerate(df_pivot.values, start=1):
117
        for col_num, value in enumerate(row_data):
118
            if isinstance(value, (int, float)):
119
                ws_pivot.write(row_num, col_num, value, number_format)
120
            else:
121
                ws_pivot.write(row_num, col_num, value)
122
    print(f"✓ Feuille 'do_pivot' créée")
123
    
124
    # Feuille 3 : Données fusionnées
125
    ws_merged = workbook.add_worksheet("do_merged")
126
    for col_num, col_name in enumerate(df_merged.columns):
127
        ws_merged.write(0, col_num, col_name, bold)
128
    for row_num, row_data in enumerate(df_merged.values, start=1):
129
        for col_num, value in enumerate(row_data):
130
            if isinstance(value, (int, float)):
131
                ws_merged.write(row_num, col_num, value, number_format)
132
            else:
133
                ws_merged.write(row_num, col_num, value)
134
    print(f"✓ Feuille 'do_merged' créée")
135
    
136
    # Feuille 4 : Données groupées
137
    ws_grouped = workbook.add_worksheet("do_grouped")
138
    for col_num, col_name in enumerate(df_grouped.columns):
139
        ws_grouped.write(0, col_num, col_name, bold)
140
    for row_num, row_data in enumerate(df_grouped.values, start=1):
141
        for col_num, value in enumerate(row_data):
142
            if isinstance(value, (int, float)):
143
                ws_grouped.write(row_num, col_num, value, number_format)
144
            else:
145
                ws_grouped.write(row_num, col_num, value)
146
    print(f"✓ Feuille 'do_grouped' créée")
147
    
148
    # Feuille 5 : Formules Excel
149
    ws_formulas = workbook.add_worksheet("do_formulas")
150
    
151
    # En-têtes
152
    ws_formulas.write('A1', 'Description', bold)
153
    ws_formulas.write('B1', 'Formule', bold)
154
    ws_formulas.write('C1', 'Résultat', bold)
155
    
156
    # Formules diverses
157
    ws_formulas.write('A2', 'Somme N(i)')
158
    ws_formulas.write_formula('B2', '=SUM(do_source!D2:D11)')
159
    
160
    ws_formulas.write('A3', 'Moyenne ttf(i)')
161
    ws_formulas.write_formula('B3', '=AVERAGE(do_source!B2:B11)')
162
    
163
    ws_formulas.write('A4', 'Écart-type ttf(i)')
164
    ws_formulas.write_formula('B4', '=STDEV.S(do_source!B2:B11)')
165
    
166
    ws_formulas.write('A5', 'Max N(i)')
167
    ws_formulas.write_formula('B5', '=MAX(do_source!D2:D11)')
168
    
169
    ws_formulas.write('A6', 'Min N(i)')
170
    ws_formulas.write_formula('B6', '=MIN(do_source!D2:D11)')
171
    
172
    ws_formulas.write('A7', 'Référence croisée')
173
    ws_formulas.write_formula('B7', '=do_source!B5')
174
    
175
    ws_formulas.write('A8', 'Condition')
176
    ws_formulas.write_formula('B8', '=IF(do_source!B5>500, "Long", "Court")')
177
    
178
    ws_formulas.set_column('A:A', 25)
179
    ws_formulas.set_column('B:B', 35)
180
    ws_formulas.set_column('C:C', 15)
181
    
182
    print(f"✓ Feuille 'do_formulas' créée avec formules Excel")
183
    
184
    # Feuille 6 : Rapport consolidé
185
    ws_report = workbook.add_worksheet("do_report")
186
    
187
    title_format = workbook.add_format({
188
        'bold': True,
189
        'font_size': 14,
190
        'align': 'center',
191
        'valign': 'vcenter',
192
        'bg_color': '#4472C4',
193
        'font_color': 'white'
194
    })
195
    
196
    ws_report.merge_range('A1:D1', 'Rapport d\'Analyse - Fiabilité', title_format)
197
    
198
    ws_report.write('A3', 'Source des données:', bold)
199
    ws_report.write('B3', '001_05.xlsx')
200
    
201
    ws_report.write('A4', 'Nombre d\'observations:', bold)
202
    ws_report.write_formula('B4', '=COUNTA(do_source!A2:A11)')
203
    
204
    ws_report.write('A5', 'MTBF (heures):', bold)
205
    ws_report.write_formula('B5', '=AVERAGE(do_source!B2:B11)')
206
    
207
    ws_report.write('A6', 'Taux de défaillance:', bold)
208
    ws_report.write_formula('B6', '=SUM(do_source!C2:C11)/SUM(do_source!B2:B11)')
209
    
210
    ws_report.set_column('A:A', 25)
211
    ws_report.set_column('B:B', 20)
212
    
213
    print(f"✓ Feuille 'do_report' créée avec rapport consolidé")
214
    
215
    workbook.close()
216
217
218
def open_in_excel(xlsx_path: Path) -> None:
219
    """Ouvre le classeur généré dans Excel."""
220
    subprocess.Popen(["start", "excel", str(xlsx_path)], shell=True)
221
222
223
def main() -> None:
224
    """Pipeline : lecture, transformations avancées, formules."""
225
    source_path, dest_path = build_paths()
226
    
227
    print(f"Lecture du fichier source : {source_path}")
228
    df_source = read_source_data(source_path)
229
    print(f"✓ {len(df_source)} lignes lues")
230
    
231
    print("\nCréation de la table pivot...")
232
    df_pivot = create_pivot_table(df_source.copy())
233
    print(df_pivot)
234
    
235
    print("\nCréation des données groupées...")
236
    df_grouped = create_grouped_data(df_source.copy())
237
    print(df_grouped)
238
    
239
    print("\nCréation des données fusionnées...")
240
    df_merged = create_merged_data(df_source.copy())
241
    print(df_merged.head())
242
    
243
    print("\nÉcriture dans Excel avec formules...")
244
    delete_if_exists(dest_path)
245
    write_to_excel_with_formulas(dest_path, df_source, df_pivot, df_merged, df_grouped)
246
    
247
    print(f"\nFichier créé : {dest_path}")
248
    print("6 feuilles générées avec pivot, merge, groupby, formules et rapport")
249
    open_in_excel(dest_path)
250
251
252
if __name__ == "__main__":
253
    main()
254
Documentation⚓
1
# 001_09.py — Manipulation avancée pandas
2
3
Ce script Python illustre des manipulations avancées de données avec pandas : pivot, merge, groupby et intégration de formules Excel.
4
5
## Objectifs pédagogiques
6
- Maîtriser les opérations pandas avancées (pivot, merge, groupby)
7
- Lire et transformer des fichiers Excel existants
8
- Intégrer des formules Excel dans les exports
9
- Créer des tableaux croisés dynamiques
10
11
## Fonctionnement
12
1. **Lecture de données sources**
13
   - Chargement du fichier 001_05.xlsx
14
   - Vérification et nettoyage des données
15
16
2. **Transformations pandas**
17
   - `pivot_table()` : tableaux croisés
18
   - `merge()` : fusion de DataFrames
19
   - `groupby()` : agrégations groupées
20
   - Calculs de statistiques par catégorie
21
22
3. **Formules Excel**
23
   - Insertion de formules dans les cellules
24
   - Références entre cellules et feuilles
25
   - Formules de calcul automatique (SUM, AVERAGE, etc.)
26
27
4. **Export structuré**
28
   - Plusieurs feuilles avec analyses différentes
29
   - Mise en forme conditionnelle
30
   - Tableaux de synthèse avec formules
31
32
## Points techniques
33
- Lecture Excel multi-feuilles avec pandas
34
- Opérations de transformation de données
35
- Export avec formules Excel actives
36
- Intégration xlsxwriter + pandas
37
38
## Utilisation
39
Ce script sert de référence pour effectuer des analyses de données complexes en combinant pandas et Excel, avec conservation des formules de calcul.
40
41
---
42
*Fichier généré automatiquement par GitHub Copilot (GPT-4.1) — 11/12/2025*
43