readme.md

🚦 US Traffic Accidents: ETL & PostgreSQL Analysis Pipeline

A complete data engineering and analytics project using the US Accidents dataset, covering extraction, transformation, and loading into a PostgreSQL database, followed by SQL-based analysis and Python-powered visualizations.

---

📁 Project Structure

TrafficAccidents-ETL-Analysis/
├── data/                      # Raw dataset (ignored in Git)
├── database/
│   ├── create_schema.sql      # Table schema for PostgreSQL
│   └── insert_data.py         # CSV-to-Postgres script
├── scripts/
│   └── clean_transform.py     # ETL transformation script
├── notebooks/
│   ├── exploratory_analysis.ipynb  # All 10 visual plots
│   └── advanced_queries.ipynb     # Raw SQL insights
├── visualizations/            # Saved plots
├── main.py                    # Runs the full ETL pipeline
├── requirements.txt           # Python dependencies
├── .env                       # DB credentials (excluded from Git)
└── README.md                  # Project overview

---

🗃️ Dataset

• Source: Kaggle: US Accidents (2016–2023)

• Size: 7+ million rows

• Fields include:

  • Start/End Time
  • Severity
  • City, State
  • Weather Condition
  • Visibility, Temperature, Wind Speed
  • Accident Duration (calculated)

---

🧪 ETL Pipeline

• 📥 Extract: CSV loaded using Pandas
• 🧹 Transform: Cleaned nulls, calculated accident duration, converted timestamps
• 🏗️ Load: Inserted into PostgreSQL using SQLAlchemy

Run the full pipeline:

python main.py

---

📊 Exploratory Visualizations

Stored in the notebooks/visualizations/ folder and generated via Jupyter:

1. Accidents by State

   

2. Severity Distribution

   

3. Accident Duration Histogram

   

4. Top Cities by Accidents

   

5. Weather Conditions by State

   

6. Hourly Accident Distribution

   

7. Weekday vs Weekend Accidents

   

8. Monthly Average Duration

   

9. Severity by Weather Condition

   

10. Wind Speed vs Severity

    

---

🧠 Deeper SQL Queries

Found in notebooks/advanced_queries.ipynb:

• Top cities with most accidents
• Average duration per state
• Severity × Weather correlation
• Time-based aggregations (hour, weekday, month)
• Weather/visibility/wind effect on severity

---

🛠️ Tech Stack

• Language: Python 3.10+
• Database: PostgreSQL
• Libraries: Pandas, SQLAlchemy, Matplotlib, Seaborn, Dotenv
• Notebook: Jupyter

---

📦 Setup Instructions

1. Clone repo:

   git clone https://github.com/ChaiiShree/sql.git
   cd TrafficAccidents-ETL-Analysis

2. Create .env file:

   DATABASE_URL=postgresql://username:password@localhost:5432/dbname
   

3. Set up environment:

   python -m venv venv
   venv/Scripts/activate  # On Windows
   pip install -r requirements.txt

4. Download dataset from Kaggle into data/

5. Run ETL:

   python main.py

6. Launch notebooks:

   jupyter notebook

📄 License

This project is licensed under the MIT License. Dataset credit: © Kaggle – US Accidents