# Import the csv module
import csv

# --- Example 1: Reading a CSV file --- #
# Create a dummy CSV file for reading demonstration
csv_data_read = [
    ['ID', 'Name', 'City'],
    ['1', 'Alice', 'New York'],
    ['2', 'Bob', 'London'],
    ['3', 'Charlie', 'Paris']
]

with open('example_read.csv', 'w', newline='', encoding='utf-8') as file:
    writer = csv.writer(file)
    writer.writerows(csv_data_read)

print("\n--- Reading 'example_read.csv' with csv.reader ---")
with open('example_read.csv', 'r', newline='', encoding='utf-8') as file:
    reader = csv.reader(file)
    for row in reader:
        print(row)

print("\n--- Reading 'example_read.csv' with csv.DictReader ---")
with open('example_read.csv', 'r', newline='', encoding='utf-8') as file:
    dict_reader = csv.DictReader(file)
    for row in dict_reader:
        print(row) # Each row is an OrderedDict

# --- Example 2: Writing to a CSV file --- #
# Data to write
csv_data_write = [
    ['Product', 'Price', 'Quantity'],
    ['Laptop', 1200, 10],
    ['Mouse', 25, 50],
    ['Keyboard', 75, 20]
]

print("\n--- Writing to 'example_write.csv' with csv.writer ---")
with open('example_write.csv', 'w', newline='', encoding='utf-8') as file:
    writer = csv.writer(file)
    writer.writerow(['Product', 'Price', 'Quantity']) # Write header
    writer.writerows(csv_data_write[1:]) # Write data rows (excluding internal header)

print("Successfully wrote 'example_write.csv'")

# --- Example 3: Writing with csv.DictWriter --- #
# Data to write as dictionaries
csv_data_dict_write = [
    {'Fruit': 'Apple', 'Color': 'Red', 'Taste': 'Sweet'},
    {'Fruit': 'Banana', 'Color': 'Yellow', 'Taste': 'Creamy'},
    {'Fruit': 'Orange', 'Color': 'Orange', 'Taste': 'Citrus'}
]

fieldnames = ['Fruit', 'Color', 'Taste']

print("\n--- Writing to 'example_dict_write.csv' with csv.DictWriter ---")
with open('example_dict_write.csv', 'w', newline='', encoding='utf-8') as file:
    writer = csv.DictWriter(file, fieldnames=fieldnames)
    writer.writeheader() # Write the header row using fieldnames
    writer.writerows(csv_data_dict_write) # Write the data rows

print("Successfully wrote 'example_dict_write.csv'")

# Import the pandas library
import pandas as pd
import numpy as np

# --- Example 1: Basic CSV reading with pandas --- #
# Create a dummy CSV for pandas demonstration
csv_data_pandas = [
    'EmployeeID,FirstName,LastName,Department,Salary',
    '101,John,Doe,Engineering,75000',
    '102,Jane,Smith,HR,60000',
    '103,Peter,Jones,Engineering,80000',
    '104,Alice,Williams,Marketing,65000'
]

with open('employees.csv', 'w', encoding='utf-8') as file:
    file.write('\n'.join(csv_data_pandas))

print("\n--- Reading 'employees.csv' with pd.read_csv ---")
df_employees = pd.read_csv('employees.csv')
print(df_employees.head())

# --- Example 2: Advanced CSV reading with parameters --- #
# Simulate a CSV with different delimiter, missing values, and specific columns
complex_csv_data = [
    'Transaction_ID|Item|Quantity|Price|Date|Status',
    'T001|Laptop|2|1200|2023-01-15|Completed',
    'T002|Mouse|1||2023-01-16|Pending',
    'T003|Keyboard|3|75|2023-01-17|Completed',
    'T004|Monitor||250|2023-01-18|Failed'
]

with open('complex_transactions.csv', 'w', encoding='utf-8') as file:
    file.write('\n'.join(complex_csv_data))

print("\n--- Reading 'complex_transactions.csv' with custom parameters ---")
# Parameters:
# sep='|': Specifies the pipe delimiter
# na_values=['']: Treats empty strings as NaN
# usecols=['Item', 'Quantity', 'Price']: Reads only these columns
# dtype={'Quantity': 'Int64', 'Price': 'float64'}: Explicitly set data types for efficiency
# parse_dates=['Date']: Automatically parses the 'Date' column
df_transactions = pd.read_csv(
    'complex_transactions.csv',
    sep='|',
    na_values=[''],
    usecols=['Item', 'Quantity', 'Price', 'Date'],
    dtype={'Quantity': 'Int64', 'Price': 'float64'},
    parse_dates=['Date']
)
print(df_transactions.info())
print(df_transactions.head())

# --- Example 3: Writing a DataFrame to CSV --- #
print("\n--- Writing DataFrame to 'processed_employees.csv' ---")
# Exclude index, use a specific delimiter, handle missing values
df_employees['Bonus'] = df_employees['Salary'] * 0.10 # Add a new column
df_employees.to_csv(
    'processed_employees.csv',
    index=False, # Do not write DataFrame index
    sep=';',     # Use semicolon as delimiter
    na_rep='N/A' # Representation for NaN values
)
print("Successfully wrote 'processed_employees.csv'")

# --- Example 4: Handling large files with chunksize --- #
# Simulate a large CSV file (e.g., 100,000 rows)
large_data = []
for i in range(100000):
    large_data.append(f'{i},Item_{i%10},Category_{i%5},{np.random.randint(10, 100)}')

with open('large_data.csv', 'w', encoding='utf-8') as f:
    f.write('ID,Product,Category,Value\n')
    f.write('\n'.join(large_data))

print("\n--- Processing 'large_data.csv' in chunks ---")
chunk_size = 10000
processed_chunks = []
for chunk in pd.read_csv('large_data.csv', chunksize=chunk_size):
    # Perform some processing on each chunk, e.g., filter or aggregate
    filtered_chunk = chunk[chunk['Value'] > 50]
    processed_chunks.append(filtered_chunk)
    print(f"Processed a chunk of {len(chunk)} rows. Filtered to {len(filtered_chunk)} rows.")

final_df_large = pd.concat(processed_chunks)
print(f"Total filtered rows from large file: {len(final_df_large)}")

# Import pandas
import pandas as pd

# We need an engine to read/write Excel files. 'openpyxl' is common for .xlsx.
# You might need to install it: pip install openpyxl
# For older .xls files, 'xlrd' and 'xlwt' might be needed: pip install xlrd xlwt

# --- Example 1: Reading a single Excel sheet --- #
# Create a dummy Excel file for reading demonstration
excel_data_single_sheet = {
    'Product': ['Widget A', 'Widget B', 'Widget C'],
    'Price': [10.50, 22.75, 5.99],
    'Stock': [100, 50, 200]
}
df_excel_single = pd.DataFrame(excel_data_single_sheet)

# Create a Pandas ExcelWriter object
with pd.ExcelWriter('products.xlsx', engine='openpyxl') as writer:
    df_excel_single.to_excel(writer, sheet_name='Inventory', index=False)
print("Created 'products.xlsx' for reading demo.")

print("\n--- Reading 'products.xlsx' (Inventory sheet) ---")
# Read a specific sheet from the Excel file
df_inventory = pd.read_excel('products.xlsx', sheet_name='Inventory')
print(df_inventory.head())

# --- Example 2: Reading multiple Excel sheets --- #
# Create another sheet for demonstration
excel_data_multi_sheet_sales = {
    'Date': ['2023-01-01', '2023-01-02', '2023-01-03'],
    'Product': ['Widget A', 'Widget C', 'Widget B'],
    'Quantity Sold': [5, 10, 3]
}
df_excel_multi_sales = pd.DataFrame(excel_data_multi_sheet_sales)

# Write both DataFrames to the same Excel file, different sheets
with pd.ExcelWriter('multi_sheet_report.xlsx', engine='openpyxl') as writer:
    df_excel_single.to_excel(writer, sheet_name='Inventory', index=False)
    df_excel_multi_sales.to_excel(writer, sheet_name='Sales', index=False)
print("Created 'multi_sheet_report.xlsx' with multiple sheets.")

print("\n--- Reading 'multi_sheet_report.xlsx' (multiple sheets) ---")
# To read all sheets, set sheet_name=None. This returns a dictionary of DataFrames.
excel_sheets = pd.read_excel('multi_sheet_report.xlsx', sheet_name=None)

for sheet_name, df in excel_sheets.items():
    print(f"\nSheet: {sheet_name}")
    print(df.head())

# --- Example 3: Writing multiple DataFrames to a single Excel file --- #
# Assume df_inventory and df_transactions (from previous section) exist
# For simplicity, let's create a new df_transactions here if not available.
df_transactions_example = pd.DataFrame({
    'ID': ['T1', 'T2'], 'Amount': [100, 200], 'Currency': ['USD', 'EUR']
})

print("\n--- Writing multiple DataFrames to 'combined_data.xlsx' ---")
with pd.ExcelWriter('combined_data.xlsx', engine='openpyxl') as writer:
    df_inventory.to_excel(writer, sheet_name='ProductInventory', index=False, startrow=0, startcol=0)
    # Example: Writing another DataFrame below the first one on the same sheet
    # This is more complex and often better handled by writing to separate sheets or concatenating first.
    # For this example, let's just stick to separate sheets.
    df_transactions_example.to_excel(writer, sheet_name='TransactionDetails', index=False)
    # It's possible to apply some basic formatting after writing, but pandas doesn't offer extensive cell styling
    # for `to_excel` directly without using libraries like `openpyxl` more deeply.

print("Successfully wrote 'combined_data.xlsx' with ProductInventory and TransactionDetails sheets.")

# Import pandas and necessary modules
import pandas as pd
import os

# --- Example 1: Robust File Reading with Error Handling and Resource Management --- #
# Simulate a scenario where a file might not exist or has permission issues

# Create a dummy CSV file that will be read successfully
csv_for_error_handling = [
    'ID,Value',
    '1,100',
    '2,200'
]
with open('valid_data.csv', 'w', encoding='utf-8') as f:
    f.write('\n'.join(csv_for_error_handling))

print("\n--- Demonstrating error handling for file operations ---")
file_paths = ['valid_data.csv', 'non_existent.csv']

for file_path in file_paths:
    try:
        # The 'with' statement ensures the file is properly closed even if errors occur.
        with open(file_path, 'r', encoding='utf-8') as file:
            df = pd.read_csv(file) # pandas can read directly from a file object
            print(f"Successfully processed {file_path}. Head:\n{df.head()}")
    except FileNotFoundError:
        print(f"Error: The file '{file_path}' was not found. Please check the path.")
    except pd.errors.EmptyDataError:
        print(f"Error: The file '{file_path}' is empty or has no columns.")
    except UnicodeDecodeError:
        print(f"Error: Failed to decode '{file_path}'. Try a different encoding (e.g., 'latin1').")
    except Exception as e:
        print(f"An unexpected error occurred while processing {file_path}: {e}")

# Clean up dummy file
os.remove('valid_data.csv')

# --- Example 2: Memory Optimization with dtype specification for large datasets --- #
# Simulate a large dataset with mixed types
large_data_mem = []
for i in range(500000): # 500k rows
    large_data_mem.append(f'{i},{i%100},{np.random.rand():.2f},{bool(i%2)},Category_{i%5}')

with open('large_mem_data.csv', 'w', encoding='utf-8') as f:
    f.write('Index,SmallInt,FloatVal,BooleanVal,Category\n')
    f.write('\n'.join(large_data_mem))

print("\n--- Demonstrating memory optimization with dtype ---")

# Without specific dtypes (pandas infers, often using larger types than necessary)
print("Reading without dtypes...")
initial_memory = pd.read_csv('large_mem_data.csv')
print(f"Memory usage without dtypes: {initial_memory.memory_usage(deep=True).sum() / (1024**2):.2f} MB")

# With specific dtypes (e.g., 'Int16' for SmallInt, 'category' for Category)
specified_dtypes = {
    'Index': 'Int32',
    'SmallInt': 'Int16',  # Can save significant memory compared to 'int64'
    'FloatVal': 'float32', # float32 instead of float64
    'BooleanVal': 'bool', # bool instead of object
    'Category': 'category' # 'category' type is highly memory-efficient for repetitive strings
}

print("Reading with dtypes...")
optimized_memory = pd.read_csv('large_mem_data.csv', dtype=specified_dtypes)
print(f"Memory usage with dtypes: {optimized_memory.memory_usage(deep=True).sum() / (1024**2):.2f} MB")

# Clean up dummy file
os.remove('large_mem_data.csv')

# --- Example 3: Data Validation Snippet (Post-Load) --- #
print("\n--- Demonstrating post-load data validation ---")
validation_df = pd.DataFrame({
    'ID': [1, 2, 3, 4],
    'Value': [100, 200, -50, 300],
    'Status': ['Active', 'Pending', 'Active', 'Completed']
})

# Validate 'Value' column to be positive
if (validation_df['Value'] < 0).any():
    print("Warning: Negative values found in 'Value' column. Investigate records:")
    print(validation_df[validation_df['Value'] < 0])
    # Example: Correcting or flagging such data
    validation_df.loc[validation_df['Value'] < 0, 'Value'] = np.nan # Replace with NaN

# Validate 'Status' column against allowed values
allowed_statuses = ['Active', 'Pending', 'Completed']
if not validation_df['Status'].isin(allowed_statuses).all():
    print("Warning: Invalid status values found. Investigate records:")
    print(validation_df[~validation_df['Status'].isin(allowed_statuses)])

print("\nValidation complete. Potentially corrected DataFrame:")
print(validation_df)