Python ed Excel: Guida Pratica all’Automazione con Pandas e OpenPyXL (Esercizi Reali)

Ammettiamolo: Excel è lo strumento che amiamo odiare. È installato ovunque, tutti lo sanno usare (più o meno), ed è fantastico… finché non lo è più.

Finché il file non diventa così pesante da impiegare tre minuti per aprirsi, o finché non ti ritrovi a copiare e incollare le stesse celle per la centesima volta, sperando di non aver saltato una riga.

Se lavori con i dati, sai che c’è un punto di rottura dove i fogli di calcolo smettono di essere una soluzione e diventano il problema. Qui non parleremo di teoria informatica astratta. Vedremo quattro scenari reali, dal report trimestrale che deve essere ‘bello da vedere’ alla pulizia di dataset sporchi e pieni di errori , dove Python non è solo un’alternativa, ma l’unica via d’uscita per smettere di fare ‘data entry’ e iniziare a fare vera analisi.

Esercizio 1 (Base): Lettura e Statistiche Base

Testo:
Sei un assistente di laboratorio che deve analizzare dati da un file dati_sperimentali.xlsx.

Crea un programma Python che:

Legga il foglio “Misure”
Calcoli media, massimo e minimo della colonna “Temperatura (°C)”
Stampi i risultati formattati

Soluzione

import pandas as pd

# 1. CARICAMENTO DATI
# Pandas legge Excel tramite read_excel, usando come motore openpyxl o xlrd
df = pd.read_excel('dati_sperimentali.xlsx', sheet_name='Misure')

# 2. ESTRAZIONE COLONNA
# Seleziono la colonna specifica usando la notazione df['nome_colonna']
temp_colonna = df['Temperatura (°C)']

# 3. CALCOLO STATISTICHE
# Metodi built-in di pandas Series: mean(), max(), min()
media = temp_colonna.mean()
massimo = temp_colonna.max()
minimo = temp_colonna.min()

# 4. OUTPUT FORMATTATO
print(f"ANALISI DATI SPERIMENTALI")
print(f"Media temperatura: {media:.1f} °C")
print(f"Temperatura massima: {massimo} °C")
print(f"Temperatura minima: {minimo} °C")

# Osservazione: .1f formatta a 1 decimale

ANALISI DATI SPERIMENTALI
Media temperatura: 23.5 °C
Temperatura massima: 29.9 °C
Temperatura minima: 19.7 °C

💡 Osservazioni:

read_excel() carica l’intero foglio in un DataFrame
Le colonne sono accessibili come attributi o con notazione a dizionario
I metodi statistici ignorano automaticamente i valori NaN

Domanda di riflessione: Perché usiamo sheet_name invece del nome predefinito del foglio?

Reality Check:
Nella realtà, i file Excel spesso hanno nomi di colonna con spazi o caratteri speciali che causano errori. Inoltre, i dati potrebbero iniziare dalla riga 5 invece che dalla prima, richiedendo parametri aggiuntivi come skiprows. Il codice “pulito” dell’esercizio funziona solo con dati perfettamente strutturati.

Esercizio 2 (Base-Intermedio): Scrittura e Formattazione

Testo:
Devi creare un report Excel che riassume le vendite trimestrali. Il programma deve:

Creare un DataFrame con 4 prodotti e vendite per 4 trimestri
Aggiungere colonne per “Totale Annuo” e “Media Trimestrale”
Salvare su Excel con formattazione: intestazioni in grassetto e bordi

Soluzione

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, Border, Side

# 1. CREAZIONE DATI
dati = {
    'Prodotto': ['Laptop', 'Smartphone', 'Tablet', 'Monitor'],
    'Q1': [120, 300, 150, 80],
    'Q2': [130, 320, 140, 85],
    'Q3': [115, 310, 160, 90],
    'Q4': [125, 330, 155, 95]
}

df = pd.DataFrame(dati)

# 2. CALCOLO COLONNE AGGIUNTE
# Operazioni vettoriali: somma sulle righe (axis=1)
df['Totale Annuo'] = df[['Q1', 'Q2', 'Q3', 'Q4']].sum(axis=1)
df['Media Trimestrale'] = df['Totale Annuo'] / 4

# 3. SALVATAGGIO BASE
file_path = 'report_vendite.xlsx'
df.to_excel(file_path, index=False, sheet_name='Vendite 2024')

# 4. FORMATTAZIONE CON openpyxl
from openpyxl import load_workbook

wb = load_workbook(file_path)
ws = wb.active

# Stile intestazioni
bold_font = Font(bold=True, size=12)
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)

# Applica a tutte le celle dell'intestazione (riga 1)
for cell in ws[1]:
    cell.font = bold_font
    cell.border = thin_border

# Larghezza colonne automatica
for column in ws.columns:
    max_length = 0
    column_letter = column[0].column_letter
    for cell in column:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            pass
    adjusted_width = min(max_length + 2, 30)
    ws.column_dimensions[column_letter].width = adjusted_width

wb.save(file_path)
print(f"File salvato come {file_path}")

# Osservazione: openpyxl permette formattazione avanzata

💡 Osservazioni:

to_excel() di pandas crea il file base
openpyxl modifica la formattazione post-salvataggio
Le operazioni vettoriali (sum(axis=1)) sono più efficienti dei loop

Forse potrebbe interessarti anche: Excel: Le variabili qualitative. Usiamo la funzione CONTA.SE

Domanda di riflessione: Cosa significa axis=1 e cosa cambierebbe con axis=0?

Reality Check:
Nelle aziende reali, i report Excel richiedono formattazione complessa: celle colorate in base al valore, formule Excel incorporate, fogli multipli collegati. Spesso si usano template preesistenti che il programma deve solo popolare, non creare da zero. La formattazione dinamica (come le larghezze colonne) può rompersi con dati imprevisti.

Esercizio 3 (Intermedio): Pulizia e Ottimizzazione Dati

Testo:
Hai un file Excel dati_clienti.xlsx con problemi: duplicati, valori mancanti, formati inconsistenti. Scrivi uno script che:

Rimuova duplicati basati su “ID_Cliente”
Sostituisca i NaN nella colonna “Età” con la mediana
Converta “Data_Registrazione” in formato datetime
Filtri i clienti con età < 18 o > 100 come “dati anomali”

Soluzione

import pandas as pd
import numpy as np

# 1. LETTURA CON CONTROLLO ERRORI
try:
    df = pd.read_excel('dati_clienti.xlsx')
    print(f"Letti {len(df)} record")
except FileNotFoundError:
    print("File non trovato!")
    exit()

# 2. RIMOZIONE DUPLICATI
# keep='first' mantiene la prima occorrenza
duplicati_pre = len(df)
df = df.drop_duplicates(subset='ID_Cliente', keep='first')
duplicati_post = len(df)
print(f"Rimossi {duplicati_pre - duplicati_post} duplicati")

# 3. GESTIONE VALORI MANCANTI
# isna() identifica valori NaN, fillna() li sostituisce
if 'Età' in df.columns:
    # Calcolo mediana PRIMA della sostituzione
    mediana_eta = df['Età'].median()
    print(f"Mediana età: {mediana_eta}")
    
    # Sostituzione
    df['Età'] = df['Età'].fillna(mediana_eta)
    
    # Conversione a interi (se appropriato)
    df['Età'] = df['Età'].astype(int)

# 4. CONVERSIONE FORMATO DATA
# errors='coerce' trasforma errori in NaT (Not a Time)
if 'Data_Registrazione' in df.columns:
    df['Data_Registrazione'] = pd.to_datetime(
        df['Data_Registrazione'], 
        errors='coerce'
    )
    # Controllo conversioni fallite
    date_fallite = df['Data_Registrazione'].isna().sum()
    print(f"Conversioni date fallite: {date_fallite}")

# 5. IDENTIFICAZIONE ANOMALIE
# Creazione maschera booleana per filtrare
if 'Età' in df.columns:
    maschera_anomali = (df['Età'] < 18) | (df['Età'] > 100)
    dati_anomali = df[maschera_anomali]
    dati_puliti = df[~maschera_anomali]
    
    print(f"\nDATI ANOMALI (età <18 o >100): {len(dati_anomali)} record")
    print(dati_anomali[['ID_Cliente', 'Età']].head())
    
    # Salvataggio separato
    dati_puliti.to_excel('clienti_puliti.xlsx', index=False)
    dati_anomali.to_excel('clienti_anomali.xlsx', index=False)
    
    print(f"\nSalvati {len(dati_puliti)} record puliti")
else:
    print("Colonna 'Età' non trovata")

# Osservazione: il simbolo ~ inverte la maschera booleana

Letti 48 record
Rimossi 3 duplicati
Mediana età: 44.0
Conversioni date fallite: 26

DATI ANOMALI (età <18 o >100): 3 record
   ID_Cliente  Età
6        C026    3
21       C006  150
43       C013   -5

Salvati 42 record puliti

💡 Osservazioni:

drop_duplicates() è più efficiente della rimozione manuale
fillna() con mediana è robusto agli outliers
Le maschere booleane sono fondamentali per il filtraggio

Domanda di riflessione: Perché usare la mediana invece della media per sostituire valori mancanti?

Reality Check:
Nella realtà aziendale, non si possono eliminare dati “anomali” senza approvazione. Un cliente di 102 anni potrebbe essere reale! Inoltre, i duplicati spesso non sono esatti: stesso cliente con nome leggermente diverso (“Mario Rossi” vs “M. Rossi”), richiedendo algoritmi fuzzy matching. La pulizia dati è un processo iterativo che richiede validazione umana.

Esercizio 4 (Intermedio-Avanzato): Elaborazione Large Dataset

Testo:
Devi analizzare un file Excel di 50.000 righe (sensori_grandi.xlsx). Leggilo in modo efficiente, campiona il 10% dei dati, calcola statistiche per gruppo e salva il riepilogo.

Soluzione

import pandas as pd
import numpy as np
import time

# --- CONFIGURAZIONE OTTIMIZZAZIONE ---
FILE_NAME = 'sensori_grandi.xlsx'

# Mappa dei tipi di dato per risparmiare memoria durante la lettura.
# Usare 'float32' invece di 'float64' dimezza lo spazio per i numeri.
dtype_config = {
    'ID_Sensore': 'str',
    'Temperatura': 'float32',
    'Umidità': 'float32',
    'Tipo_Sensore': 'category' # 'category' è drasticamente più leggero per stringhe ripetute
}

# Colonne strettamente necessarie (ignoriamo 'Note' e 'Data_Rilevazione' per velocità)
cols_to_use = ['ID_Sensore', 'Tipo_Sensore', 'Temperatura', 'Umidità']

def main():
    start_time = time.time()
    print(f"--- Inizio elaborazione di {FILE_NAME} ---")

    # 1. LETTURA OTTIMIZZATA
    try:
        df = pd.read_excel(
            FILE_NAME,
            dtype=dtype_config,
            usecols=cols_to_use
            # engine='openpyxl' # Opzionale: specificare l'engine se necessario
        )
    except FileNotFoundError:
        print("Errore: File non trovato. Esegui prima lo script generatore.")
        return

    # Info sulle prestazioni
    mem_usage = df.memory_usage(deep=True).sum() / 1024 ** 2
    print(f"Dati caricati in {time.time() - start_time:.2f} secondi.")
    print(f"Righe totali: {len(df)}")
    print(f"Memoria utilizzata (ottimizzata): {mem_usage:.2f} MB")

    # 2. CAMPIONAMENTO (Sampling)
    # Per analisi rapide su big data, spesso basta il 10% dei dati rappresentativi
    df_sample = df.sample(frac=0.1, random_state=42)
    print(f"Campione creato: {len(df_sample)} righe per l'analisi statistica.")

    # 3. ANALISI STATISTICA PER GRUPPO
    # Raggruppiamo per tipo sensore e calcoliamo metriche diverse per temperatura e umidità
    print("\nCalcolo statistiche per tipo di sensore...")
    
    stats = df_sample.groupby('Tipo_Sensore', observed=True).agg({
        'Temperatura': ['mean', 'max', 'min'],
        'Umidità': ['mean']
    }).round(2)
    
    print(stats)

    # 4. RILEVAMENTO OUTLIERS (Metodo IQR)
    # Identifichiamo temperature anomale (es. sensori rotti)
    Q1 = df['Temperatura'].quantile(0.25)
    Q3 = df['Temperatura'].quantile(0.75)
    IQR = Q3 - Q1
    
    lower_bound = Q1 - 1.5 * IQR
    upper_bound = Q3 + 1.5 * IQR
    
    # Filtriamo i valori fuori dai limiti
    outliers = df[(df['Temperatura'] < lower_bound) | (df['Temperatura'] > upper_bound)]
    print(f"\nOutliers rilevati (Temperatura anomala): {len(outliers)} record.")

    # 5. SALVATAGGIO REPORT MULTI-FOGLIO
    # Usiamo ExcelWriter per scrivere su più fogli nello stesso file
    output_file = 'report_sensori_avanzato.xlsx'
    print(f"Salvataggio report in '{output_file}'...")

    with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
        # Foglio 1: Statistiche aggregate
        stats.to_excel(writer, sheet_name='Statistiche')
        
        # Foglio 2: Elenco anomalie (solo le prime 100 per non appesantire)
        outliers.head(100).to_excel(writer, sheet_name='Outliers_Top100', index=False)
        
        # Foglio 3: Info di log
        log_df = pd.DataFrame({
            'Metrica': ['Totale Righe', 'Memoria Usata (MB)', 'Soglia Outlier Max'],
            'Valore': [len(df), f"{mem_usage:.2f}", f"{upper_bound:.2f}"]
        })
        log_df.to_excel(writer, sheet_name='Log_Elaborazione', index=False)

    total_time = time.time() - start_time
    print(f"\n--- Finito in {total_time:.2f} secondi ---")

if __name__ == "__main__":
    main()

--- Inizio elaborazione di sensori_grandi.xlsx ---
Dati caricati in 6.13 secondi.
Righe totali: 50000
Memoria utilizzata (ottimizzata): 3.15 MB
Campione creato: 5000 righe per l'analisi statistica.

Calcolo statistiche per tipo di sensore...
             Temperatura                       Umidità
                    mean         max    min       mean
Tipo_Sensore                                          
HUM            22.540001  257.220001  15.01  49.509998
LIGHT          22.520000   34.990002  10.01  50.660000
MOTION         22.860001   34.919998  10.04  49.669998
PRESS          22.139999   35.000000  10.01  50.040001
TEMP           20.170000  232.309998   4.56  50.610001

Outliers rilevati (Temperatura anomala): 43 record.
Salvataggio report in 'report_sensori_avanzato.xlsx'...

--- Finito in 6.17 secondi ---

💡 Punti Chiave dell’Ottimizzazione:

1. usecols: Sembra banale, ma caricare solo le colonne che servono è il modo più rapido per velocizzare la lettura. Se il file ha 50 colonne e te ne servono 4, stai risparmiando il 90% del tempo di I/O.
2. dtype='category': Invece di memorizzare la parola “TEMPERATURA” 10.000 volte (che occupa molta memoria), Pandas memorizza un numero (es. 1) e una piccola tabella di ricerca. Su file grandi, questo riduce l’uso della RAM in modo massiccio.
3. float32 vs float64: Di default Pandas usa la massima precisione (64 bit). Per temperature o dati aziendali normali, 32 bit sono più che sufficienti e occupano metà spazio.
4. ExcelWriter: Fondamentale per creare report professionali. Permette di non sovrascrivere il file ma di aggiungere tab diversi (es. uno per i dati, uno per i grafici, uno per i log).

Forse potrebbe interessarti anche: Excel e Python: Perché l'integrazione (e non la scelta) è il futuro della Data Analysis

Domanda di riflessione: Abbiamo visto che ottimizzare i tipi di dato (dtype) e usare il campionamento ci permette di gestire 50.000 righe senza sforzo. Ma cosa succederebbe se il file Excel superasse il milione di righe o pesasse diversi Gigabyte? Qual è il limite fisico di Pandas con i file Excel e quando dovremmo smettere di usare Excel come database?

Reality Check:
Per file Excel veramente grandi (oltre 100MB), spesso pandas fallisce per limiti di memoria. In produzione si usano: lettura a chunk (chunksize), database SQL, formati parquet, o strumenti specializzati (Dask, Spark). L’ottimizzazione presentata è utile ma insufficiente per Big Data reali.

RISPOSTE ALLE DOMANDE DI RIFLESSIONE

Esercizio 1: Usiamo sheet_name per specificare esplicitamente quale foglio leggere. Se non lo specifichiamo, pandas legge il primo foglio per default, ma se l’utente riordina i fogli, lo script legge quello sbagliato.

Esercizio 2: axis=1 indica operazione sulle colonne (orizzontale), axis=0 indica operazione sulle righe (verticale). Con axis=1 sommo Q1+Q2+Q3+Q4 per ogni riga; con axis=0 sommerei tutti i valori di Q1, poi tutti i valori di Q2, ecc.

Esercizio 3: Usiamo la mediana perché è robusta agli outliers. Se ci sono valori estremi (es. età 150 per errore), la media sarebbe distorta, mentre la mediana rappresenta meglio il valore “tipico”.

Esercizio 4: Questa domanda è il “momento della verità” per chiunque aspiri a lavorare seriamente con i dati. Ecco i punti chiave che l’esercizio e la riflessione portano alla luce:

I Limiti di Excel: Excel ha un limite invalicabile di 1.048.576 righe. Spesso, i dati generati da sensori (come quelli dell’esercizio) superano questa soglia in pochi giorni. Insegnare a un utente come gestire questi volumi in Python significa fornirgli i mezzi per superare i limiti del software da ufficio tradizionale.
Il collo di bottiglia del formato .xlsx: Leggere un file Excel è molto più lento rispetto a leggere un file CSV o un database SQL, perché Python deve “decomprimere” la struttura XML di Excel. L’esercizio è interessante perché introduce lo studente all’efficienza computazionale: non conta solo il risultato, ma quanto tempo e quanta RAM servono per ottenerlo.
Transizione verso Database/Parquet: La riflessione prepara il terreno per concetti avanzati. Se i dati sono troppi, la soluzione non è “un computer più potente”, ma cambiare formato (usando ad esempio Parquet, che è un formato di archiviazione colonnare molto più leggero) o passare a un database SQL.