[Python] Python Script Business Intelligence ETL cleaning [closed]

I made this python script to clean data inside a CSV file, generating an output cleaned file, but I have the problem that it cancels all the rows in the moment it finds a bad column with a lot of NaN, meanwhile it should just clean the columns. How can I fix it?

from pathlib import Path # To handle file paths in a cross-platform way
import pandas as pd # For reading, manipulating, and writing DataFrames/CSV

# Grouping columns for missing values analysis by groups (leave [] if not needed)
GROUP_COLS = []
# Key columns for identifying duplicate records (leave [] if not needed)
KEY_COLS = []

# Paths and filenames
INPUT_DIR = Path('In') # input folder
OUTPUT_DIR = Path('Out') # output folder
INPUT_FILENAME = 'dati.csv' # original CSV file name
OUTPUT_FILENAME = 'dati_puliti.csv' # resulting CSV file name

def clean_data(df: pd.DataFrame) -> pd.DataFrame:
"""
Robust pipeline:
1) Removes columns with 100% missing
2) (Optional) Removes columns with >80% missing
3) Removes only rows with missing in critical columns
4) Identifies and removes duplicates
5) Removes numeric values <=0
6) Removes outliers beyond 3 sigma
"""
logs = []

# 1. Remove completely empty columns
missing_pct = df.isnull().mean() * 100
print("Percentage of missing values per column:")
for col, pct in missing_pct.items():
print(f" - {col}: {pct:.2f}%")
fully_empty = missing_pct[missing_pct == 100].index.tolist()
if fully_empty:
print(f"\nRemoving completely empty columns: {fully_empty}")
logs.append(f"Columns removed (100% empty) -> {fully_empty}")
df = df.drop(columns=fully_empty)
else:
print("\nNo completely empty columns to remove.")

# 2. (OPTIONAL) Remove columns with >80% missing (adjust threshold as desired)
threshold_missing = 80 # percentage
too_empty = missing_pct[(missing_pct > threshold_missing) & (missing_pct < 100)].index.tolist()
if too_empty:
print(f"Removing columns with >{threshold_missing}% NaN (but not 100%): {too_empty}")
logs.append(f"Columns removed (>80% missing): {too_empty}")
df = df.drop(columns=too_empty)
else:
print(f"No columns with >{threshold_missing}% NaN to remove.")

# 3. Remove only rows with missing in critical columns (modify here according to your data!)
# By default, consider all remaining columns as critical.
critical_columns = df.columns.tolist()
missing_idx = df[df[critical_columns].isnull().any(axis=1)].index
for idx in missing_idx:
nan_cols = df.loc[idx, critical_columns][df.loc[idx, critical_columns].isnull()].index.tolist()
logs.append(f"Row {idx} removed -> missing in critical columns {nan_cols}")
df = df.drop(index=missing_idx)
print(f"\nRemoved {len(missing_idx)} rows with missing in critical columns.\n")

# 4. Identification and removal of duplicates
if KEY_COLS:
dup_mask = df.duplicated(subset=KEY_COLS, keep=False)
idx_dup = df[dup_mask].index
for idx in idx_dup:
key_vals = df.loc[idx, KEY_COLS].to_dict()
logs.append(f"Row {idx} removed -> duplicate keys {key_vals}")
df = df.drop_duplicates(subset=KEY_COLS, keep='first')
print(f"Removed {len(idx_dup)} duplicate rows based on {KEY_COLS}.\n")
else:
print("KEY_COLS empty: no duplicate removal.\n")

# 5. Remove rows with numeric values <= 0
num_cols = df.select_dtypes(include='number').columns.tolist()
mask_nonpos = (df[num_cols] <= 0).any(axis=1)
idx_nonpos = df[mask_nonpos].index
for idx in idx_nonpos:
bad_cols = df.loc[idx, num_cols][df.loc[idx, num_cols] <= 0].index.tolist()
logs.append(f"Row {idx} removed -> values <= 0 in columns {bad_cols}")
df = df[~mask_nonpos]
print(f"Removed {len(idx_nonpos)} rows with values <= 0.\n")

# 6. Outliers (>3 sigma)
outlier_idx = set()
for col in num_cols:
mu = df[col].mean()
sigma = df[col].std(ddof=0)
mask_out = (df[col] < mu - 3*sigma) | (df[col] > mu + 3*sigma)
idx_out = df[mask_out].index
for idx in idx_out:
val = df.at[idx, col]
logs.append(f"Row {idx} removed -> outlier in '{col}' (value={val:.2f})")
outlier_idx.update(idx_out)
df = df.drop(index=list(outlier_idx))
print(f"Removed {len(outlier_idx)} outlier rows (>3 sigma).\n")

print("=== Detailed log of changes ===")
for entry in logs:
print(entry)
print("=== End of log ===\n")
return df

def main():
"""
Script entry point:
1) Check existence of folders and files
2) Read input CSV
3) Advanced data cleaning
4) Save cleaned CSV output
"""
# 1) Check existence of input folder 'In'
if not INPUT_DIR.exists():
print(f"Error: input folder '{INPUT_DIR}' does not exist.")
return

# 1a) Check existence of input CSV file
input_path = INPUT_DIR / INPUT_FILENAME
if not input_path.exists():
print(f"Error: file '{input_path}' not found.")
return

# 1b) Create output folder if missing
if not OUTPUT_DIR.exists():
OUTPUT_DIR.mkdir(parents=True, exist_ok=True)

# 2) Read data from CSV
print(f"Reading data from: {input_path}")
df = pd.read_csv(input_path, encoding='utf-8', sep=',')

# 3) Advanced cleaning with Data Quality Assessment
print("Starting advanced data cleaning...")
df_clean = clean_data(df)

# 4) Save cleaned CSV
output_path = OUTPUT_DIR / OUTPUT_FILENAME
print(f"Saving cleaned data to: {output_path}")
df_clean.to_csv(output_path, index=False, encoding='utf-8')

print("Pipeline completed successfully!")

if __name__ == "__main__":
main()


The main problem is that it searches for the Nan in the columns, but then instead of deleting the column it deletes the full row, making it easy to get a fully deleted csv as output from the starting file.

Continue reading...