Working with CSV, Excel, and JSON Files

import pandas as pd

# Basic CSV read
# Assuming file: data.csv
# name,email,age
# John,john@example.com,25
df_csv = pd.read_csv('data.csv')

# Common CSV Problems and Solutions:

# 1. Different delimiter (e.g., TSV - tab separated)
df_tsv = pd.read_csv('data.tsv', sep='\t')

# 2. No header row in the file
df_no_head = pd.read_csv('data.csv', header=None, names=['Name', 'Email', 'Age'])

# 3. Encoding errors (e.g., UnicodeDecodeError)
# Use 'utf-8', 'latin1', 'iso-8859-1', or 'cp1252'
df_enc = pd.read_csv('data.csv', encoding='latin1')

# 4. Large files (read in chunks)
chunk_iter = pd.read_csv('huge_data.csv', chunksize=10000)
for chunk in chunk_iter:
    # process chunk
    pass

# 5. Skipping bad lines (rows with too many commas)
df_bad = pd.read_csv('messy.csv', on_bad_lines='skip')

# Note: Requires openpyxl installed (pip install openpyxl)

# Basic Excel read (reads first sheet by default)
df_xl = pd.read_excel('report.xlsx')

# 1. Read a specific sheet by name
df_sheet = pd.read_excel('report.xlsx', sheet_name='Sales_2023')

# 2. Read all sheets into a dictionary of DataFrames
all_sheets = pd.read_excel('report.xlsx', sheet_name=None)
print(all_sheets.keys()) # Shows sheet names

# 3. Skip rows (e.g., title and empty rows at the top)
df_skip = pd.read_excel('report.xlsx', skiprows=3)

# 4. Specify NA values (e.g., Excel uses "N/A", "Missing", "-")
df_na = pd.read_excel('report.xlsx', na_values=['N/A', 'Missing', '-', '#DIV/0!'])

# Basic JSON read (flat list of objects)
df_json = pd.read_json('data.json')

# Reading nested JSON (requires json_normalize)
import json
from pandas import json_normalize

nested_json = {
    "users": [
        {"id": 1, "info": {"name": "Alice", "age": 25}},
        {"id": 2, "info": {"name": "Bob", "age": 30}}
    ]
}

# Flatten the nested dictionaries
df_flat = json_normalize(nested_json['users'])
print(df_flat)
# Output:
#    id info.name  info.age
# 0   1     Alice        25
# 1   2       Bob        30

# Export to CSV
# index=False prevents pandas from writing the row numbers to the file
df.to_csv('cleaned_data.csv', index=False)

# Export to Excel
df.to_excel('cleaned_data.xlsx', sheet_name='Cleaned', index=False)

# Export to JSON
# orient='records' creates a standard list of objects
df.to_json('cleaned_data.json', orient='records', indent=4)

import pandas as pd

def clean_csv_file(input_path, output_path):
    print(f"Loading {input_path}...")
    try:
        # Try UTF-8 first, fallback to latin1 if it fails
        try:
            df = pd.read_csv(input_path, encoding='utf-8')
        except UnicodeDecodeError:
            print("UTF-8 failed, trying latin1 encoding...")
            df = pd.read_csv(input_path, encoding='latin1')
        
        print(f"Original shape: {df.shape}")
        
        # 1. Standardize column names (lowercase, replace spaces with underscores)
        df.columns = df.columns.str.strip().str.lower().str.replace(' ', '_')
        
        # 2. Drop completely empty rows
        df = df.dropna(how='all')
        
        # 3. Drop exact duplicate rows
        df = df.drop_duplicates()
        
        print(f"Cleaned shape: {df.shape}")
        
        # Export
        df.to_csv(output_path, index=False)
        print(f"Success! Saved to {output_path}")
        
    except Exception as e:
        print(f"Error processing file: {e}")

# Usage:
# clean_csv_file('messy_input.csv', 'clean_output.csv')