Risolvere i duplicati e i valori nulli

I problemi di qualità dei dati, ad esempio i record duplicati e i valori Null, possono compromettere i processi di analisi e downstream. Se i duplicati derivano da caricamenti simultanei di dati o valori Null vengono visualizzati a causa di dati di origine incompleti, la risoluzione di questi problemi è una responsabilità fondamentale per i data engineer che lavorano in Azure Databricks.

In questa unità si apprenderà come identificare e risolvere i valori duplicati, mancanti e Null usando gli approcci SQL e PySpark.

Identificare i record duplicati

Prima di poter rimuovere i duplicati, è necessario trovarli. I duplicati si verificano in genere quando lo stesso record viene visualizzato più volte in una tabella, spesso a causa di problemi di integrazione dei dati, inserimento ripetuto o operazioni di scrittura simultanee.

Usare GROUP BY e HAVING per trovare duplicati

Il modo più semplice per identificare i duplicati consiste nel raggruppamento di record e nel conteggio delle occorrenze. La HAVING clausola filtra i risultati raggruppati per visualizzare solo i valori visualizzati più volte.

SELECT 
    customer_id, 
    email, 
    COUNT(*) AS occurrence_count
FROM sales.customers
GROUP BY customer_id, email
HAVING COUNT(*) > 1
ORDER BY occurrence_count DESC;

Questa query restituisce tutte le combinazioni di customer_id e email che vengono visualizzate più volte nella tabella, consentendo di comprendere l'ambito della duplicazione.

Usare QUALIFY con le funzioni finestra

La QUALIFY clausola fornisce un approccio più semplice filtrando direttamente i risultati delle funzioni di finestra, senza richiedere sottoquery. Questo approccio è particolarmente utile quando è necessario visualizzare le righe duplicate effettive, non solo i conteggi.

SELECT *
FROM sales.customers
QUALIFY COUNT(*) OVER (PARTITION BY customer_id, email) > 1;

Questa query potrebbe restituire risultati simili ai seguenti:

Con questo modello si ottengono tutte le colonne dalle righe duplicate, semplificando l'analisi dell'origine della duplicazione.

Trovare duplicati con PySpark

In PySpark è possibile usare funzioni finestra combinate con il filtro per identificare i record duplicati:

from pyspark.sql.functions import count
from pyspark.sql.window import Window

window_spec = Window.partitionBy("customer_id", "email")

duplicates_df = (
    df.withColumn("row_count", count("*").over(window_spec))
    .filter("row_count > 1")
    .drop("row_count")
)

display(duplicates_df)

In alternativa, usare il exceptAll() metodo per confrontare il dataframe originale con una versione deduplicata:

df_duplicates = df.exceptAll(df.dropDuplicates(["customer_id", "email"]))
display(df_duplicates)

Il exceptAll() metodo esegue una differenza di set che mantiene i duplicati. Restituisce tutte le righe del primo DataFrame che non si trovano nel secondo DataFrame, mantenendo i duplicati. Ecco come funziona:

1. df.dropDuplicates(["customer_id", "email"]) crea un dataframe con una riga per combinazione univoca
2. exceptAll() confronta il dataframe originale con questa versione deduplicata
3. Per ogni combinazione univoca, un'occorrenza viene "abbinata" e rimossa, lasciando solo i duplicati aggiuntivi

Ad esempio, se il cliente 1001 viene visualizzato tre volte nel dataframe originale e una volta nella versione deduplicata, exceptAll() restituisce due righe (le occorrenze aggiuntive).

Rimuovere i record duplicati

Dopo aver identificato i duplicati, è possibile scegliere una strategia di risoluzione in base ai requisiti dei dati.

Rimuovere i duplicati in PySpark

Il dropDuplicates() metodo rimuove le righe duplicate in base alle colonne specificate e mantiene una riga per ogni combinazione. Quando le righe differiscono in altre colonne, la riga che rimane non è garantita:

df_clean = df.dropDuplicates(["customer_id", "email"])

Per la deduplicazione completa delle righe in tutte le colonne, usare distinct():

df_unique = df.distinct()

Mantenere righe specifiche usando ROW_NUMBER

Quando è necessario controllare il duplicato da conservare, usare ROW_NUMBER() con QUALIFY per selezionare i record in base a criteri come l'aggiornamento più recente:

SELECT *
FROM sales.customers
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY customer_id 
    ORDER BY updated_at DESC
) = 1;

Questa query partiziona i record in base a customer_id, li ordina in base a updated_at in ordine decrescente, e mantiene solo il record aggiornato più di recente per ogni cliente.

Gestire valori null e mancanti

I valori Null rappresentano dati mancanti o sconosciuti. A seconda dei requisiti di analisi, potrebbe essere necessario rimuovere righe con valori Null, sostituirle con valori predefiniti o usare l'imputazione statistica.

Identificare i valori Null

Usare la isnull() funzione o IS NULL operatore per trovare i record con valori mancanti.

SELECT *
FROM sales.transactions
WHERE amount IS NULL 
   OR customer_id IS NULL;

Per contare le occorrenze null per colonna:

SELECT 
    COUNT(*) AS total_rows,
    COUNT(*) - COUNT(amount) AS null_amount_count,
    COUNT(*) - COUNT(customer_id) AS null_customer_count
FROM sales.transactions;

Eliminare righe con valori Null

In PySpark il dropna() metodo rimuove le righe contenenti valori Null:

# Drop rows where any column is null
df_clean = df.dropna()

# Drop rows where all columns are null
df_clean = df.dropna(how="all")

# Drop rows where specific columns are null
df_clean = df.dropna(subset=["customer_id", "amount"])

Il how parametro controlla il comportamento: "any" elimina le righe con almeno un valore Null (impostazione predefinita), mentre "all" elimina le righe solo quando tutte le colonne specificate sono Null.

Compilare valori null con valori predefiniti

Il fillna() metodo sostituisce i valori Null con le impostazioni predefinite specificate:

# Fill null values with a single value (applies only to columns whose data type matches)
df_filled = df.fillna(0)

# Fill specific columns with different values
df_filled = df.fillna({
    "amount": 0,
    "status": "Unknown",
    "quantity": 1
})

In SQL usare la COALESCE funzione per specificare i valori predefiniti:

SELECT 
    customer_id,
    COALESCE(amount, 0) AS amount,
    COALESCE(status, 'Unknown') AS status
FROM sales.transactions;

Scegliere la strategia giusta

La strategia di gestione dei valori Null appropriata dipende dal contesto dei dati:

Informazioni su come identificare e risolvere questi problemi di qualità dei dati prepara la creazione di pipeline di dati più affidabili. Con dati puliti, le trasformazioni downstream e l'analisi producono risultati affidabili.