revolvos-Vehicle_Specs.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "84e037a8-754b-41a0-9013-cc069434d297",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import boto3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2090f1dd-578a-4daa-8248-da31c9080f3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's use Amazon S3\n",
    "# The Access Key ID and Secret Access Key are in ~/.aws/credentials\n",
    "# Region is set in ~/.aws/config\n",
    "s3 = boto3.resource('s3')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8d740a7f-b713-4ab0-8e87-3ac7987318eb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "athena-query-bucket-testda24\n",
      "aws-glue-assets-936480984004-eu-north-1\n",
      "hacksprint-2023\n",
      "temp-transformation-rs\n",
      "vehicle-bucket-alarm\n"
     ]
    }
   ],
   "source": [
    "# Print out bucket names\n",
    "for bucket in s3.buckets.all():\n",
    "    print(bucket.name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "cadf2fd6-0a27-4390-b25c-7c8e739bad4d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Telematics Data Dictionary 2023_02_14.xlsx\n",
      "Telematics Data Dictionary 2023_02_14.xlsx - SNAP_SHOT_ITEM (1).csv\n",
      "Vehicle_Fault.csv\n",
      "Vehicle_Fault_Alarm.csv\n",
      "Vehicle_Fault_Alarm_Item.csv\n",
      "Vehicle_Location.csv\n",
      "Vehicle_Specs.csv\n"
     ]
    }
   ],
   "source": [
    "# Print the file names in the bucket\n",
    "revolvos_bucket = s3.Bucket('hacksprint-2023')\n",
    "for object in revolvos_bucket.objects.all():\n",
    "    print(object.key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2fe70c70-c0d8-4596-820d-4c8bb1e7d9ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "# Load one of the files into a dataframe\n",
    "obj = s3.Object('hacksprint-2023', 'Vehicle_Specs.csv').get()\n",
    "df = pd.read_csv(obj['Body'], low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "df6b0fba",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1.47 s, sys: 138 ms, total: 1.61 s\n",
      "Wall time: 1.61 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# Load from the local CSV file instead.\n",
    "df = pd.read_csv('../S3 original bucket/Vehicle_Specs (1).csv', low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "3741e7ec-200d-4cbb-9430-877768d226e1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "VEHICLE_ID                int64\n",
      "VEHICLE_TYPE             object\n",
      "VEHICLE_SPEC_ID           int64\n",
      "VEHICLE_MODEL_YEAR        int64\n",
      "VEHICLE_MODEL            object\n",
      "VEHICLE_MAKE             object\n",
      "VEH_VERSION               int64\n",
      "VEH_DESC                 object\n",
      "TGW_SERIAL               object\n",
      "RETAIL_DT                object\n",
      "PRODUCT_TYPE             object\n",
      "PRODUCT_CLASS            object\n",
      "NOTIF_TYPE               object\n",
      "MQ_MESSAGE_PUT_DT        object\n",
      "MODEL_FAMILY             object\n",
      "MAIN_ASSEMBLY_DT         object\n",
      "LAST_UPDATE_DT           object\n",
      "INACTIVE_CD             float64\n",
      "IN_SERVICE_DT            object\n",
      "IN_PROCESS               object\n",
      "IMSI                    float64\n",
      "GROSS_WT_RATING         float64\n",
      "FINAL_ASSEMBLY_DT        object\n",
      "FACTORY_INVOICE_DT       object\n",
      "ENGINE_TYPE              object\n",
      "ENGINE_MAKE              object\n",
      "DISPATCH_DT             float64\n",
      "CREATE_DT                object\n",
      "COUNTRY_OF_OPERATION     object\n",
      "CHASSIS_SERIES           object\n",
      "CHASSIS_NUMBER            int64\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(df.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "85d9be6b-3436-4413-9f39-046184ab61b0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   VEHICLE_ID VEHICLE_TYPE  VEHICLE_SPEC_ID  VEHICLE_MODEL_YEAR VEHICLE_MODEL  \\\n",
      "0     2494778            D          3379851                2009           VNL   \n",
      "1     2472691            D          3379743                2008           VNL   \n",
      "2   100012582            D          3379751                2011         GU713   \n",
      "3   100237223            D          3010560                2012        CHU613   \n",
      "4   100183496            D          3008550                2016        CHU613   \n",
      "\n",
      "  VEHICLE_MAKE  VEH_VERSION               VEH_DESC TGW_SERIAL   RETAIL_DT  \\\n",
      "0        VOLVO           28                Unknown        NaN    6/2/2008   \n",
      "1        VOLVO           32                Unknown   06009825   1/10/2008   \n",
      "2         MACK           15  SELECT AN APPLICATION   10500612   6/25/2010   \n",
      "3         MACK           22                Unknown        NaN  12/31/2011   \n",
      "4         MACK           90     AGRICULTURE ON-HWY   15072839   6/18/2015   \n",
      "\n",
      "   ... GROSS_WT_RATING FINAL_ASSEMBLY_DT FACTORY_INVOICE_DT ENGINE_TYPE  \\\n",
      "0  ...         23746.0         4/21/2008          4/25/2008        US07   \n",
      "1  ...         22838.0        10/19/2007         10/24/2007        US07   \n",
      "2  ...             NaN         5/14/2010           6/2/2010        US10   \n",
      "3  ...             NaN         7/19/2011          7/21/2011        US10   \n",
      "4  ...         22679.0          4/7/2015           1/1/0001        US15   \n",
      "\n",
      "  ENGINE_MAKE DISPATCH_DT              CREATE_DT  COUNTRY_OF_OPERATION  \\\n",
      "0       VOLVO         NaN  9/21/2016 12:21:54 PM                   CAN   \n",
      "1       VOLVO         NaN   9/20/2016 9:00:39 PM                   USA   \n",
      "2       VOLVO         NaN  9/20/2016 10:07:12 PM                   USA   \n",
      "3       VOLVO         NaN   4/4/2016 11:19:08 AM                   USA   \n",
      "4       VOLVO         NaN   4/4/2016 10:43:52 AM                   USA   \n",
      "\n",
      "  CHASSIS_SERIES CHASSIS_NUMBER  \n",
      "0              N         270837  \n",
      "1              N         262332  \n",
      "2           M748           9666  \n",
      "3           M722           9827  \n",
      "4           M722          22451  \n",
      "\n",
      "[5 rows x 31 columns]\n"
     ]
    }
   ],
   "source": [
    "print(df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "9039c2df",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(458948, 31)\n"
     ]
    }
   ],
   "source": [
    "print(df.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8682225c-ed6e-45db-b68b-76c3331390d2",
   "metadata": {
    "tags": []
   },
   "source": [
    "Insert magical cleaning..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "292114a4-6f7e-41b1-b0c9-deb448cab8e1",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "# Send the cleaned dataframe content back to a new CSV file on the S3 bucket\n",
    "csv_clean = df.to_csv(index=False)\n",
    "_ = s3.Object('hacksprint-2023', 'Vehicle_Specs-clean.csv').put(Body=csv_clean)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "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.11.2"
  },
  "vscode": {
   "interpreter": {
    "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}