import arcpy
import pandas as pd
from datetime import datetime

# Update the full path to your CSV file here
file_path = "C:/Users/BrianT/RIT/TEACHING/IGME_386_C_drive/Week_12_SDS_Predictive_Models/dev/datasets/traffic_counts/Automated_Traffic_Volume_Counts.csv"

# Load the CSV file in chunks due to large file size
chunksize = 100000  # Adjust based on memory capacity

# Define the output file name with current date and timestamp
output_file = f"C:/temp/Traffic_Volume_Subset_{datetime.now().strftime('%Y%m%d_%H%M%S')}.csv"

# Initialize an empty list to hold chunks that match the criteria
filtered_chunks = []

# Load a small chunk to inspect column names when problem first occured
first_chunk = pd.read_csv(file_path, nrows=5)
print("Column names in the CSV:", first_chunk.columns)

for chunk in pd.read_csv(file_path, chunksize=chunksize):

    # Rename columns for date creation
    chunk = chunk.rename(columns={'Yr': 'year', 'M': 'month', 'D': 'day'})
    
    # Convert to datetime and filter based on criteria
    chunk['date'] = pd.to_datetime(chunk[['year', 'month', 'day']])
    subset = chunk[
        (chunk['Boro'] == 'Manhattan') &  # Where borough is Manhattan
        (chunk['date'].dt.dayofweek == 0) &  # Where day is Monday
        (chunk['HH'].between(18, 23))  # Where hour is between 18 and 23
    ]
    
    # Append the filtered subset to the list
    filtered_chunks.append(subset)

# Concatenate all filtered chunks
filtered_data = pd.concat(filtered_chunks)

# Save the filtered data to CSV
filtered_data.to_csv(output_file, index=False)

print(f"Filtered data saved to {output_file}")
