Car_Data_Analysis.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "! pip install pandas\n",
        "! pip install matplotlib\n",
        "! pip install ipython-sql psycopg2\n",
        "! pip install sqlalchemy"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "%load_ext sql"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 22,
      "metadata": {
        "id": "a1MPXY5Y6eiE"
      },
      "outputs": [],
      "source": [
        "import pandas as pd\n",
        "import matplotlib.pyplot as plt\n",
        "from sqlalchemy import create_engine"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "**Connect to PostgreSQL Database**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 23,
      "metadata": {},
      "outputs": [],
      "source": [
        "%sql postgresql://postgres:postgres@localhost/carData\n",
        "\n",
        "engine = create_engine('postgresql://postgres:postgres@localhost/carData')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6yn8wZ4gSqtn"
      },
      "source": [
        "**Read CSV file**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "%%sql\n",
        "\n",
        "SELECT * FROM car_data LIMIT 2"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 25,
      "metadata": {
        "id": "pVWUiOTh6ijU"
      },
      "outputs": [],
      "source": [
        "df = pd.read_sql('SELECT * FROM car_data', engine)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "I5z6M3pqS3RM"
      },
      "source": [
        "**Check count of nulls in every column**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 930
        },
        "id": "WKDV7lhASS_c",
        "outputId": "ef844698-2e18-43e3-89b4-0a18ede8f3ab"
      },
      "outputs": [],
      "source": [
        "df.isna().sum()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UH--OZyES89J"
      },
      "source": [
        "**Modify the created and modified column datatype from Object to datetime**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "QQLwi1BnlRJ1",
        "outputId": "59d8c72c-04a5-46f7-c335-cd964299df04"
      },
      "outputs": [],
      "source": [
        "df['created'] = pd.to_datetime(df['created'])\n",
        "df['modified'] = pd.to_datetime(df['modified'])\n",
        "df.info()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EWYVAhvxTtIf"
      },
      "source": [
        "**Display the Maximum MSRP of the cars which have Horsepower more than 7000 for all the years using pandas plot bar**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 28,
      "metadata": {
        "id": "9DixQW63MAL9"
      },
      "outputs": [],
      "source": [
        "plt.style.use('ggplot')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 659
        },
        "id": "tncMt3RvXkAu",
        "outputId": "ee0df4b0-5700-4243-aa55-0bcd58f7335a"
      },
      "outputs": [],
      "source": [
        "df[df['engine_horsepower_rpm'] > 7000] \\\n",
        "      .groupby('make_name')['msrp'] \\\n",
        "      .max() \\\n",
        "      .sort_values(ascending = True) \\\n",
        "      .plot(kind='bar', figsize=(10, 6), color='skyblue').set_title('Maximum MSRP for Cars with HP Greater than 7000')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Up4RmwSvUeOH"
      },
      "source": [
        "**Display the count of the cars which have Horsepower more than 4000 for all the years using pandas plot bar**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 601
        },
        "id": "i4tSaksB2WWU",
        "outputId": "2f04b63b-f66e-46f5-9859-445422b5577d"
      },
      "outputs": [],
      "source": [
        "df[df['engine_horsepower_rpm'] < 4000] \\\n",
        "      .groupby('make_name')['make_id']\\\n",
        "      .count() \\\n",
        "      .sort_values(ascending = True) \\\n",
        "      .plot(kind='bar', figsize=(5, 5), color='yellowgreen').set_title('Count of cars with HP less tha 4000')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BktXTH31ZxRO"
      },
      "source": [
        "**Create a new Dataframe with average msrp of all the makes for the year 2024 and Display Bar chart using Matplotlib**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 31,
      "metadata": {
        "id": "X32qlq0UUbfs"
      },
      "outputs": [],
      "source": [
        "msrp_avg_2024 = df[df['year'] == 2024].groupby('make_name')['msrp'].mean().sort_values(ascending=True).reset_index()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 968
        },
        "id": "7dE_d0IkUgoy",
        "outputId": "668773e5-bb8e-4929-d599-c50f9fb523c5"
      },
      "outputs": [],
      "source": [
        "plt.figure(figsize=(12,10))\n",
        "plt.bar(msrp_avg_2024['make_name'], msrp_avg_2024['msrp'], color='salmon')\n",
        "plt.xlabel('Average MSRP')\n",
        "plt.ylabel('Make Name')\n",
        "plt.title('Average MSRP by Make Name')\n",
        "plt.xticks(rotation=90)\n",
        "plt.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QQhVk5iEa79V"
      },
      "source": [
        "**Display a pie chart to show the number of door types for all the cars**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 429
        },
        "id": "Z_b1_ugCEOU5",
        "outputId": "24686c0a-fb5c-4d62-9b53-35072b2bd661"
      },
      "outputs": [],
      "source": [
        "plt.pie(df['body_doors'].value_counts(), labels = df['body_doors'].unique(), autopct = '%1.1f%%', explode=[0.0, 0.0, 0.0, 0.2])\n",
        "plt.title('Body Doors')\n",
        "plt.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qUXAYBVVbg76"
      },
      "source": [
        "**Calculate Summanry Statistics of the Data**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "id": "iE7-VXKuFOiC",
        "outputId": "38f28abc-0cb8-4bc7-9136-763f2f688256"
      },
      "outputs": [],
      "source": [
        "df.groupby('make_name')['msrp'].agg(\n",
        "    avg_msrp='mean',\n",
        "    median_msrp='median',\n",
        "    stddev_msrp='std',\n",
        "    min_msrp='min',\n",
        "    max_msrp='max'\n",
        ").sort_values(by='avg_msrp', ascending=False)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "w3m4jluPcfhV",
        "outputId": "31d57dd4-28cb-4e0c-b291-6e5231782bf6"
      },
      "outputs": [],
      "source": [
        "df.columns"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "v1h699Z1c2in"
      },
      "source": [
        "**Display Histogram of Combined MPG**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 477
        },
        "id": "SSl-VgkHcPZd",
        "outputId": "0c791a8b-3e4e-4f53-89d6-178b40480dc1"
      },
      "outputs": [],
      "source": [
        "plt.hist(df['mileage_combined_mpg'], bins=20, edgecolor='black', color='darkgreen')\n",
        "plt.xlabel('Mileage')\n",
        "plt.ylabel('Frequency')\n",
        "plt.title('Mileage Distribution')\n",
        "plt.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "X6wVFEGjgwEQ"
      },
      "source": [
        "**Calcluate Depreciation by differentiating the MSRP with invoice for different years adn display line chart as subplots for year 2023 & 2024**\n",
        "\n",
        "***Depreciation meaning : a decrease in the value of a asset over time***"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "2IaYJ5qHgF0H",
        "outputId": "d9000c16-911c-4523-eea6-09e76afbd8d6"
      },
      "outputs": [],
      "source": [
        "df2 = df[((df['invoice'] != 0) & (df['msrp'] != 0))]\n",
        "df2['depreciation'] =   df2['msrp'] - df2['invoice']\n",
        "depreciation_df_1 = df2[df2['year'] == 2023].groupby('make_name')['depreciation'].mean().sort_values(ascending=True)\n",
        "depreciation_df_2 = df2[df2['year'] == 2024].groupby('make_name')['depreciation'].mean().sort_values(ascending=True)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 477
        },
        "id": "2APeVJAmlOQb",
        "outputId": "3b2e65bd-383a-4a5a-f1a5-a4639fc8f9a0"
      },
      "outputs": [],
      "source": [
        "plt.figure(figsize=(15,5))\n",
        "plt.subplot(1, 2, 1)\n",
        "plt.plot(depreciation_df_1.index, depreciation_df_1.values, color='purple', marker='.', linewidth=2)\n",
        "plt.xlabel('Make Name')\n",
        "plt.ylabel('Average Depreciation')\n",
        "plt.title('Average Depreciation for year 2023')\n",
        "plt.xticks(rotation=90)\n",
        "\n",
        "plt.subplot(1, 2, 2)\n",
        "plt.plot(depreciation_df_2.index, depreciation_df_2.values, color='green', marker='.', linewidth=2)\n",
        "plt.xlabel('Make Name')\n",
        "plt.title('Average Depreciation for year 2024')\n",
        "plt.xticks(rotation=90)\n",
        "plt.tight_layout()\n",
        "plt.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fpRN1QkzifVH"
      },
      "source": [
        "**Calculate total invoice for year 2023 & 2024 and display using bar chart**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 39,
      "metadata": {
        "id": "T_2I4CIrIxce"
      },
      "outputs": [],
      "source": [
        "total_invoice_2023 = df2[df2['year'] == 2023].groupby('make_name')['invoice'].sum().sort_values(ascending=True).reset_index()\n",
        "total_invoice_2024 = df2[df2['year'] == 2024].groupby('make_name')['invoice'].sum().sort_values(ascending=True).reset_index()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 477
        },
        "id": "yGkNLXVCSNvv",
        "outputId": "5606a905-afa1-4859-b728-450e0e2a4111"
      },
      "outputs": [],
      "source": [
        "plt.figure(figsize=(15,5))\n",
        "plt.subplot(1, 2, 1)\n",
        "plt.bar(total_invoice_2023['make_name'], total_invoice_2023['invoice'], color='dimgrey')\n",
        "plt.xlabel('Make Name')\n",
        "plt.ylabel('Total Invoice')\n",
        "plt.title('Total Invoice for 2023')\n",
        "plt.xticks(rotation=90)\n",
        "\n",
        "plt.subplot(1, 2, 2)\n",
        "plt.bar(total_invoice_2024['make_name'], total_invoice_2024['invoice'], color='r')\n",
        "plt.xlabel('Make Name')\n",
        "plt.title('Total Invoice for 2024')\n",
        "plt.xticks(rotation=90)\n",
        "plt.tight_layout()\n",
        "plt.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cJqpuQpHldjv"
      },
      "source": [
        "**Displat total cars per month from the dataset**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 41,
      "metadata": {
        "id": "q2iw8goIVeMi"
      },
      "outputs": [],
      "source": [
        "df['MonthYear'] = df['created'].dt.strftime('%Y-%m')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 513
        },
        "id": "CGjrJZvbXxN6",
        "outputId": "0d26704f-dfed-4c59-c8b5-fc4c65282f6b"
      },
      "outputs": [],
      "source": [
        "total_cars_per_month = df.groupby('MonthYear')['msrp'].count().sort_values(ascending=True)\n",
        "plt.bar(total_cars_per_month.index, total_cars_per_month.values, color='orange')\n",
        "plt.xlabel('Month')\n",
        "plt.ylabel('Total Cars')\n",
        "plt.title('Total Cars per Month')\n",
        "plt.xticks(rotation=45)\n",
        "plt.show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 486
        },
        "id": "KzNOQQTLc5rW",
        "outputId": "c15f5559-fb42-4bd0-bc9b-e0d8ee09ddda"
      },
      "outputs": [],
      "source": [
        "df.groupby(['year', 'MonthYear']).size().plot(figsize=(10,5))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "### SQL Queries"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "*Retrieve all records where msrp is greater than 50,000*"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "%%sql\n",
        "select * from car_data where msrp > 50000"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "*Get all records for the year 2024 where the make_name is \"Rolls-Royce\"*"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "%%sql\n",
        "select * from car_data where year = 2023 and make_name = 'Rolls-Royce'"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "*Calculate the average msrp for each make_name*"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "%%sql\n",
        "select make_name,year, avg(msrp) as average_msrp \n",
        "from car_data group by(make_name,year) order by avg(msrp)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "*Find the total invoice amount grouped by year*"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "%%sql\n",
        "select make_name, sum(invoice) as total_invoice\n",
        "from car_data group by(make_name)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "*Add a rank to each car within its make_name group based on msrp in descending order*"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "%%sql\n",
        "select make_name,msrp,year,\n",
        "rank() over(order by msrp desc) msrp_rank\n",
        "from car_data\n",
        "group by(make_name, msrp, year)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "*display the highest msrp totals in desc in a year*"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "%%sql\n",
        "select make_name,count(1) as cars_sold, sum(msrp) as msrp_sum,year,\n",
        "rank() over(partition by year order by sum(msrp) desc) msrp_sum_rank\n",
        "from car_data\n",
        "group by(make_name, year)\n",
        "order by year desc,sum(msrp) desc"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.12.5"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}