CSV Data Cleaner

# Preview file structure
head -5 data.csv

# Count rows (excluding header)
wc -l data.csv

# Check encoding
file -i data.csv                   # Linux/Mac
# Windows PowerShell:
[System.IO.File]::ReadAllBytes("data.csv")[0..2]   # check BOM

# Count columns (assuming comma delimiter)
head -1 data.csv | awk -F',' '{print NF}'

# Check for inconsistent column counts
awk -F',' '{print NF}' data.csv | sort | uniq -c

# Convert to UTF-8 from unknown encoding
iconv -f ISO-8859-1 -t UTF-8 input.csv > output.csv

# Remove BOM (byte order mark)
sed '1s/^\xEF\xBB\xBF//' input.csv > output.csv

# Fix Windows line endings (CRLF -> LF)
sed 's/\r$//' input.csv > output.csv
# Or:
dos2unix input.csv

# Python (handles any encoding)
python3 -c "
import csv, codecs
with codecs.open('input.csv','r','latin-1') as f:
    data = f.read()
with codecs.open('output.csv','w','utf-8') as f:
    f.write(data)
"

# Remove exact duplicate rows (keeps first occurrence)
awk '!seen[$0]++' data.csv > deduped.csv

# Remove duplicates based on specific column (column 1)
awk -F',' '!seen[$1]++' data.csv > deduped.csv

# Python: deduplicate with more control
python3 << 'EOF'
import csv
seen = set()
with open('data.csv') as f, open('deduped.csv', 'w', newline='') as out:
    reader = csv.reader(f)
    writer = csv.writer(out)
    header = next(reader)
    writer.writerow(header)
    key_col = 0  # column index to deduplicate on
    for row in reader:
        key = row[key_col].strip().lower()  # normalize before checking
        if key not in seen:
            seen.add(key)
            writer.writerow(row)
EOF

# Trim whitespace from all fields
python3 -c "
import csv, sys
reader = csv.reader(open('data.csv'))
writer = csv.writer(sys.stdout)
for row in reader:
    writer.writerow([cell.strip() for cell in row])
" > cleaned.csv

# Python: normalize specific columns
import csv
with open('data.csv') as f, open('out.csv', 'w', newline='') as out:
    reader = csv.DictReader(f)
    writer = csv.DictWriter(out, fieldnames=reader.fieldnames)
    writer.writeheader()
    for row in reader:
        row['email'] = row['email'].strip().lower()
        row['name'] = row['name'].strip().title()
        row['state'] = row['state'].strip().upper()
        writer.writerow(row)

from datetime import datetime
import csv

formats_to_try = ['%m/%d/%Y', '%d-%m-%Y', '%Y-%m-%d', '%B %d, %Y', '%m/%d/%y']

def normalize_date(value, target_format='%Y-%m-%d'):
    for fmt in formats_to_try:
        try:
            return datetime.strptime(value.strip(), fmt).strftime(target_format)
        except ValueError:
            continue
    return value  # return original if no format matches

# Apply to column index 3
with open('data.csv') as f, open('out.csv', 'w', newline='') as out:
    reader = csv.reader(f)
    writer = csv.writer(out)
    writer.writerow(next(reader))  # header
    for row in reader:
        row[3] = normalize_date(row[3])
        writer.writerow(row)

import re, csv

def normalize_phone(phone):
    digits = re.sub(r'\D', '', phone)
    if len(digits) == 11 and digits[0] == '1':
        digits = digits[1:]
    if len(digits) == 10:
        return f"({digits[:3]}) {digits[3:6]}-{digits[6:]}"
    return phone  # return original if unexpected format

# Stack files with same columns (skip header on 2nd+ files)
head -1 file1.csv > merged.csv
tail -n +2 -q file1.csv file2.csv file3.csv >> merged.csv

# Python: merge/join on a key column
python3 << 'EOF'
import csv

# Load lookup data
lookup = {}
with open('lookup.csv') as f:
    for row in csv.DictReader(f):
        lookup[row['id']] = row

# Merge with main data
with open('main.csv') as f, open('merged.csv', 'w', newline='') as out:
    reader = csv.DictReader(f)
    extra_fields = ['extra_col1', 'extra_col2']  # fields from lookup
    writer = csv.DictWriter(out, fieldnames=reader.fieldnames + extra_fields)
    writer.writeheader()
    for row in reader:
        match = lookup.get(row['id'], {})
        for field in extra_fields:
            row[field] = match.get(field, '')
        writer.writerow(row)
EOF

awk -F',' 'NF && $0 !~ /^[,\s]*$/' data.csv > cleaned.csv

import csv
default_values = {'status': 'unknown', 'count': '0', 'category': 'other'}

with open('data.csv') as f, open('filled.csv', 'w', newline='') as out:
    reader = csv.DictReader(f)
    writer = csv.DictWriter(out, fieldnames=reader.fieldnames)
    writer.writeheader()
    for row in reader:
        for field, default in default_values.items():
            if not row.get(field, '').strip():
                row[field] = default
        writer.writerow(row)

# Split "Full Name" into "First" and "Last"
import csv
with open('data.csv') as f, open('out.csv', 'w', newline='') as out:
    reader = csv.DictReader(f)
    fields = [fn for fn in reader.fieldnames if fn != 'full_name'] + ['first_name', 'last_name']
    writer = csv.DictWriter(out, fieldnames=fields)
    writer.writeheader()
    for row in reader:
        parts = row.pop('full_name', '').strip().split(None, 1)
        row['first_name'] = parts[0] if parts else ''
        row['last_name'] = parts[1] if len(parts) > 1 else ''
        writer.writerow(row)

# Find rows with wrong column count
expected=5
awk -F',' -v exp="$expected" 'NF != exp {print NR": "NF" cols - "$0}' data.csv

# Find rows with empty required fields (column 1 and 3)
awk -F',' '$1=="" || $3=="" {print NR": "$0}' data.csv

# Summary statistics for a numeric column (column 2)
awk -F',' 'NR>1 {sum+=$2; count++; if($2>max||NR==2)max=$2; if($2<min||NR==2)min=$2} END {print "count:"count, "sum:"sum, "avg:"sum/count, "min:"min, "max:"max}' data.csv

# csvkit essentials
install the package via pip csvkit
csvlook data.csv              # pretty print
csvstat data.csv              # column statistics
csvsort -c 2 data.csv         # sort by column 2
csvgrep -c 3 -m "value" data.csv  # filter rows
csvjoin -c id file1.csv file2.csv  # join files