{ "cells": [ { "cell_type": "markdown", "source": [ "## **Assignment: Alternatives to Pandas for Processing Large Datasets (Datatable)**\n", "\n", "**Group 7**\n", "\n", "Group Members: \n", "\n", "\n", "1. Madina Suraya binti Zharin A20EC0203\n", "2. Nur Izzah Mardhiah binti Rashidi A20EC0116\n", "3. Tan Yong Sheng A20EC0157\n", "4. Chloe Racquelmae Kennedy A20EC0026" ], "metadata": { "id": "H6uT3iqxJws1" } }, { "cell_type": "code", "source": [ "from google.colab import drive\n", "drive.mount('/content/drive')" ], "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "pk23EnIwODd8", "outputId": "9f7707bd-1957-4547-f972-4fd0e8529b45" }, "execution_count": null, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "Mounted at /content/drive\n" ] } ] }, { "cell_type": "code", "source": [ "dataset = '/content/drive/MyDrive/Colab Notebooks/Big Data - Asg2/Rate.csv'" ], "metadata": { "id": "05VR3diiOGV-" }, "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "source": [ "## Installing Data Table\n", "\n", "To use the package, we need to install it first" ], "metadata": { "id": "nJu58ywELAfP" } }, { "cell_type": "code", "source": [ "!pip install datatable" ], "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "61jqgal7JlZb", "outputId": "daa42e25-5c9d-4630-ecf8-9a8946a3a468" }, "execution_count": null, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n", "Collecting datatable\n", " Downloading datatable-1.0.0-cp38-cp38-manylinux_2_12_x86_64.whl (96.6 MB)\n", "\u001b[2K \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m96.6/96.6 MB\u001b[0m \u001b[31m11.2 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n", "\u001b[?25hInstalling collected packages: datatable\n", "Successfully installed datatable-1.0.0\n" ] } ] }, { "cell_type": "markdown", "source": [ "After installing, we need to import the package. Then, it is ready to be used" ], "metadata": { "id": "b0T1QS3eLWNO" } }, { "cell_type": "code", "source": [ "import time\n", "import datatable as dt\n", "print(dt.__version__)" ], "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 34 }, "id": "oHvuduA0Jmlb", "outputId": "e2c38200-11fa-4421-ba89-84aa580b90ba" }, "execution_count": null, "outputs": [ { "output_type": "display_data", "data": { "text/plain": [ "" ], "text/html": [ "\n" ] }, "metadata": {} }, { "output_type": "stream", "name": "stdout", "text": [ "1.0.0\n" ] } ] }, { "cell_type": "markdown", "source": [ "## Reading Data" ], "metadata": { "id": "vmPhAKcIBR8R" } }, { "cell_type": "markdown", "source": [ "`fread` is the function to read the files in datatable module. From the documentation,fread provides fast and convenient parsing of text (csv) files." ], "metadata": { "id": "ypPC1sk5Lv2T" } }, { "cell_type": "code", "source": [ "start = time.time()\n", "input_file_name = dataset\n", "dt_df = dt.fread(input_file_name)\n", "end = time.time()\n", "print(end - start)" ], "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "FLioexD5KQSl", "outputId": "ae9d16a7-5290-4bf1-d3f8-7e471ab948c2" }, "execution_count": null, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "13.901981592178345\n" ] } ] }, { "cell_type": "markdown", "source": [ "## Dataset Properties" ], "metadata": { "id": "2FCFNGgPBYNy" } }, { "cell_type": "code", "source": [ "dt_df.head()" ], "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 486 }, "id": "9OJDOgAyBhQb", "outputId": "53dbc6de-7995-4905-ecae-ee046415ff47" }, "execution_count": null, "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ "" ], "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
BusinessYearStateCodeIssuerIdSourceNameVersionNumImportDateIssuerId2FederalTINRateEffectiveDateRateExpirationDate…PrimarySubscriberAndThreeOrMoreDependentsCoupleAndOneDependentCoupleAndTwoDependentsCoupleAndThreeOrMoreDependentsRowNumber
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
02014AK21989HIOS62014-03-19T07:06:492198993-04387722014-01-012014-12-31…NANANANA14
12014AK21989HIOS62014-03-19T07:06:492198993-04387722014-01-012014-12-31…107.61144.56144.56144.5614
22014AK21989HIOS62014-03-19T07:06:492198993-04387722014-01-012014-12-31…107.61144.56144.56144.5615
32014AK21989HIOS62014-03-19T07:06:492198993-04387722014-01-012014-12-31…NANANANA15
42014AK21989HIOS62014-03-19T07:06:492198993-04387722014-01-012014-12-31…NANANANA16
52014AK21989HIOS62014-03-19T07:06:492198993-04387722014-01-012014-12-31…107.61144.56144.56144.5616
62014AK21989HIOS62014-03-19T07:06:492198993-04387722014-01-012014-12-31…94.5126.95126.95126.9517
72014AK21989HIOS62014-03-19T07:06:492198993-04387722014-01-012014-12-31…NANANANA17
82014AK21989HIOS62014-03-19T07:06:492198993-04387722014-01-012014-12-31…NANANANA18
92014AK21989HIOS62014-03-19T07:06:492198993-04387722014-01-012014-12-31…94.5126.95126.95126.9518
\n", "
\n", "
10 rows × 24 columns
\n", "
\n", "
\n" ] }, "metadata": {}, "execution_count": 12 } ] }, { "cell_type": "markdown", "source": [ "It can be observed that, the method fread return datatable Frame object." ], "metadata": { "id": "0Kge46CDL5fP" } }, { "cell_type": "code", "source": [ "type(dt_df)" ], "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "LgVO79CZD_51", "outputId": "f43d3019-daf5-48c5-d4b8-f3f64e1900c2" }, "execution_count": null, "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ "datatable.Frame" ] }, "metadata": {}, "execution_count": 5 } ] }, { "cell_type": "code", "source": [ "dt_df.shape" ], "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "YfNEIAuvF6yX", "outputId": "e8f19c5d-eb8d-46c2-e8b2-7bc18fa3f8f2" }, "execution_count": null, "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ "(12694445, 24)" ] }, "metadata": {}, "execution_count": 13 } ] }, { "cell_type": "code", "source": [ "dt_df.names" ], "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "iDFQ-y9jEcpw", "outputId": "b9e54655-7552-40f0-b179-a599e5fd304a" }, "execution_count": null, "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ "('BusinessYear',\n", " 'StateCode',\n", " 'IssuerId',\n", " 'SourceName',\n", " 'VersionNum',\n", " 'ImportDate',\n", " 'IssuerId2',\n", " 'FederalTIN',\n", " 'RateEffectiveDate',\n", " 'RateExpirationDate',\n", " 'PlanId',\n", " 'RatingAreaId',\n", " 'Tobacco',\n", " 'Age',\n", " 'IndividualRate',\n", " 'IndividualTobaccoRate',\n", " 'Couple',\n", " 'PrimarySubscriberAndOneDependent',\n", " 'PrimarySubscriberAndTwoDependents',\n", " 'PrimarySubscriberAndThreeOrMoreDependents',\n", " 'CoupleAndOneDependent',\n", " 'CoupleAndTwoDependents',\n", " 'CoupleAndThreeOrMoreDependents',\n", " 'RowNumber')" ] }, "metadata": {}, "execution_count": 6 } ] }, { "cell_type": "code", "source": [ "dt_df.stypes" ], "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "CJzId4xhEvs4", "outputId": "92fbbae9-d106-4983-d2b0-9bdb79151fc6" }, "execution_count": null, "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ "(stype.int32,\n", " stype.str32,\n", " stype.int32,\n", " stype.str32,\n", " stype.int32,\n", " stype.time64,\n", " stype.int32,\n", " stype.str32,\n", " stype.date32,\n", " stype.date32,\n", " stype.str32,\n", " stype.str32,\n", " stype.str32,\n", " stype.str32,\n", " stype.float64,\n", " stype.float64,\n", " stype.float64,\n", " stype.float64,\n", " stype.float64,\n", " stype.float64,\n", " stype.float64,\n", " stype.float64,\n", " stype.float64,\n", " stype.int32)" ] }, "metadata": {}, "execution_count": 9 } ] }, { "cell_type": "markdown", "source": [ "Looking at the number of unique values per columns" ], "metadata": { "id": "EY7Qts3PMGud" } }, { "cell_type": "code", "source": [ "dt_df.nunique()" ], "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 120 }, "id": "WGWbVeeFFuGo", "outputId": "564f77f8-9bd9-420e-ee39-a93254478442" }, "execution_count": null, "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ "" ], "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
BusinessYearStateCodeIssuerIdSourceNameVersionNumImportDateIssuerId2FederalTINRateEffectiveDateRateExpirationDate…PrimarySubscriberAndThreeOrMoreDependentsCoupleAndOneDependentCoupleAndTwoDependentsCoupleAndThreeOrMoreDependentsRowNumber
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
03399103232669103351426…763272687733819760444
\n", "
\n", "
1 row × 24 columns
\n", "
\n", "
\n" ] }, "metadata": {}, "execution_count": 14 } ] }, { "cell_type": "markdown", "source": [ "## Data Slicing" ], "metadata": { "id": "zbY1KIh1JWIS" } }, { "cell_type": "code", "source": [ "dt_df[0:10,0:4]" ], "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 293 }, "id": "09axbVguJYb9", "outputId": "16f183b9-37c1-45e5-a91d-14ddb7d82f85" }, "execution_count": null, "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ "" ], "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
BusinessYearStateCodeIssuerIdSourceName
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
02014AK21989HIOS
12014AK21989HIOS
22014AK21989HIOS
32014AK21989HIOS
42014AK21989HIOS
52014AK21989HIOS
62014AK21989HIOS
72014AK21989HIOS
82014AK21989HIOS
92014AK21989HIOS
\n", "
\n", "
10 rows × 4 columns
\n", "
\n", "
\n" ] }, "metadata": {}, "execution_count": 27 } ] }, { "cell_type": "markdown", "source": [ "## Data Filtering" ], "metadata": { "id": "5Jygl2pYHDx-" } }, { "cell_type": "code", "source": [ "from datatable import *" ], "metadata": { "id": "-VKjpVJgGFtG" }, "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "source": [ "Filter out the data where the Business Year data is 2015" ], "metadata": { "id": "lXSUvbhKMT5F" } }, { "cell_type": "code", "source": [ "year = dt_df[f.BusinessYear == 2015,:]\n", "year.head(5)" ], "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 293 }, "id": "4jrPmcR4GX_Q", "outputId": "90f743ff-7fcc-465d-df04-2c0b1e84d0a4" }, "execution_count": null, "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ "" ], "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
BusinessYearStateCodeIssuerIdSourceNameVersionNumImportDateIssuerId2FederalTINRateEffectiveDateRateExpirationDate…PrimarySubscriberAndThreeOrMoreDependentsCoupleAndOneDependentCoupleAndTwoDependentsCoupleAndThreeOrMoreDependentsRowNumber
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
02015AK21989HIOS42014-08-08T08:53:292198993-04387722015-01-012015-12-31…NANANANA14
12015AK21989HIOS42014-08-08T08:53:292198993-04387722015-01-012015-12-31…NANANANA15
22015AK21989HIOS42014-08-08T08:53:292198993-04387722015-01-012015-12-31…NANANANA16
32015AK21989HIOS42014-08-08T08:53:292198993-04387722015-01-012015-12-31…NANANANA17
42015AK21989HIOS42014-08-08T08:53:292198993-04387722015-01-012015-12-31…NANANANA18
\n", "
\n", "
5 rows × 24 columns
\n", "
\n", "
\n" ] }, "metadata": {}, "execution_count": 18 } ] }, { "cell_type": "markdown", "source": [ "## Data Aggregation" ], "metadata": { "id": "AEQBgqQZHGdm" } }, { "cell_type": "markdown", "source": [ "Find the average of Individual Rate based on the Age" ], "metadata": { "id": "YFGrUGITMZlE" } }, { "cell_type": "code", "source": [ "avgIndividualRate = dt_df[:,dt.mean(f.IndividualRate),dt.by(f.Age)]\n", "avgIndividualRate" ], "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 528 }, "id": "nLHfpScVGxAZ", "outputId": "e8e5eb7a-e145-432d-ac30-c3ff0d783c1b" }, "execution_count": null, "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ "" ], "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
AgeIndividualRate
▪▪▪▪▪▪▪▪▪▪▪▪
00-20122.256
1214082.84
2224082.91
3234082.98
4244083.08
5254083.62
6264087.14
7274091.29
8284097.99
9294103.55
10304106.36
11314110.55
12324114.73
13334117.35
14344120.15
⋮⋮⋮
42624409.51
43634423.06
44644431.28
4565 and over4431.41
46Family Option28.5286
\n", "
\n", "
47 rows × 2 columns
\n", "
\n", "
\n" ] }, "metadata": {}, "execution_count": 22 } ] }, { "cell_type": "markdown", "source": [ "## Data Sorting" ], "metadata": { "id": "wBVi00bqIdQi" } }, { "cell_type": "markdown", "source": [ "Sort the data based on Age (increasing)" ], "metadata": { "id": "9S2xllkRMhg5" } }, { "cell_type": "code", "source": [ "sortedData = dt_df[:,:,dt.sort(f.Age)]\n", "sortedData.head(3)" ], "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 215 }, "id": "W-ucvObKHwJ9", "outputId": "63f35b48-c7a7-4acb-c4ad-1f6e5e4ebf11" }, "execution_count": null, "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ "" ], "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
BusinessYearStateCodeIssuerIdSourceNameVersionNumImportDateIssuerId2FederalTINRateEffectiveDateRateExpirationDate…PrimarySubscriberAndThreeOrMoreDependentsCoupleAndOneDependentCoupleAndTwoDependentsCoupleAndThreeOrMoreDependentsRowNumber
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
02014AK21989HIOS62014-03-19T07:06:492198993-04387722014-01-012014-12-31…NANANANA14
12014AK21989HIOS62014-03-19T07:06:492198993-04387722014-01-012014-12-31…NANANANA60
22014AK21989HIOS62014-03-19T07:06:492198993-04387722014-01-012014-12-31…NANANANA106
\n", "
\n", "
3 rows × 24 columns
\n", "
\n", "
\n" ] }, "metadata": {}, "execution_count": 23 } ] }, { "cell_type": "markdown", "source": [ "Sort the data based on Age (decreasing)" ], "metadata": { "id": "iFkSFi3wMj8i" } }, { "cell_type": "code", "source": [ "sortedData = dt_df[:,:,dt.sort(-f.Age)]\n", "sortedData.head(3)" ], "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 215 }, "id": "Df2TnmkNIk3X", "outputId": "61283176-6ca3-416a-8143-ea1d4251cadd" }, "execution_count": null, "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ "" ], "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
BusinessYearStateCodeIssuerIdSourceNameVersionNumImportDateIssuerId2FederalTINRateEffectiveDateRateExpirationDate…PrimarySubscriberAndThreeOrMoreDependentsCoupleAndOneDependentCoupleAndTwoDependentsCoupleAndThreeOrMoreDependentsRowNumber
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
02014AK21989HIOS62014-03-19T07:06:492198993-04387722014-01-012014-12-31…107.61144.56144.56144.5614
12014AK21989HIOS62014-03-19T07:06:492198993-04387722014-01-012014-12-31…107.61144.56144.56144.5615
22014AK21989HIOS62014-03-19T07:06:492198993-04387722014-01-012014-12-31…107.61144.56144.56144.5616
\n", "
\n", "
3 rows × 24 columns
\n", "
\n", "
\n" ] }, "metadata": {}, "execution_count": 24 } ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "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.8.5" }, "colab": { "provenance": [] } }, "nbformat": 4, "nbformat_minor": 0 }