Data_Manipulation_with_Pandas-Part_1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Memanggil Library Pandas\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "DataFrame & Series\n",
    "Di Pandas terdapat 2 kelas data baru yang digunakan sebagai struktur dari spreadsheet:\n",
    "\n",
    "1. Series: satu kolom bagian dari tabel dataframe yang merupakan 1 dimensional numpy array sebagai basis data nya, terdiri dari 1 tipe data (integer, string, float, dll).\n",
    "\n",
    "2. DataFrame: gabungan dari Series, berbentuk rectangular data yang merupakan tabel spreadsheet itu sendiri (karena dibentuk dari banyak Series, tiap Series biasanya punya 1 tipe data, yang artinya 1 dataframe bisa memiliki banyak tipe data)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Series:\n",
      "0    1\n",
      "1    2\n",
      "2    3\n",
      "3    4\n",
      "4    5\n",
      "5    6\n",
      "dtype: int64\n",
      "DataFrame:\n",
      "   0  1  2\n",
      "0  1  2  3\n",
      "1  a  b  c\n",
      "2  3  4  5\n",
      "3  d  4  6\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# Series\n",
    "number_list = pd.Series([1,2,3,4,5,6])\n",
    "print(\"Series:\")\n",
    "print(number_list)\n",
    "# DataFrame\n",
    "matrix = [[1,2,3],\n",
    "          ['a','b','c'],\n",
    "          [3,4,5],\n",
    "          ['d',4,6]]\n",
    "matrix_list = pd.DataFrame(matrix)\n",
    "print(\"DataFrame:\")\n",
    "print(matrix_list)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Atribut DataFrame & Series - Part 1\n",
    "Dataframe dan Series memiliki sangat banyak atribut yang digunakan untuk transformasi data, tetapi ada beberapa attribute yang sering dipakai. Di sini series number_list dan data frame matrix_list pada subbab sebelumnya digunakan kembali.\n",
    "\n",
    "1. Attribute .info()\n",
    "\n",
    "Attribute .info() digunakan untuk mengecek kolom apa yang membentuk dataframe itu, data types, berapa yang non null, dll. Attribute ini tidak dapat digunakan pada series, hanya pada data frame saja.\n",
    "\n",
    "2. Attribute .shape\n",
    "\n",
    "Attribute .shape digunakan untuk mengetahui berapa baris dan kolom, hasilnya dalam format tuple (baris, kolom).\n",
    "\n",
    "3. Attribute .dtypes\n",
    "\n",
    "Attribute .dtypes digunakan untuk mengetahui tipe data di tiap kolom. Tipe data object: kombinasi untuk berbagai tipe data (number & text, etc).\n",
    "\n",
    "4. Attribute .astype(nama_tipe_data)\n",
    "\n",
    "Attribute .astype(nama_tipe_data) untuk convert tipe data berdasarkan tipe data seperti: float, int, str, numpy.float, numpy.int ataupun numpy.datetime."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1] attribute .info()\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4 entries, 0 to 3\n",
      "Data columns (total 3 columns):\n",
      " #   Column  Non-Null Count  Dtype \n",
      "---  ------  --------------  ----- \n",
      " 0   0       4 non-null      object\n",
      " 1   1       4 non-null      object\n",
      " 2   2       4 non-null      object\n",
      "dtypes: object(3)\n",
      "memory usage: 224.0+ bytes\n",
      "None\n",
      "\n",
      "[2] attribute .shape\n",
      "    Shape dari number_list: (6,)\n",
      "    Shape dari matrix_list: (4, 3)\n",
      "\n",
      "[3] attribute .dtypes\n",
      "    Tipe data number_list: int64\n",
      "    Tipe data matrix_list: 0    object\n",
      "1    object\n",
      "2    object\n",
      "dtype: object\n",
      "\n",
      "[4] attribute .astype()\n",
      "    Konversi number_list ke str: 0    1\n",
      "1    2\n",
      "2    3\n",
      "3    4\n",
      "4    5\n",
      "5    6\n",
      "dtype: object\n",
      "    Konversi matrix_list ke str:    0  1  2\n",
      "0  1  2  3\n",
      "1  a  b  c\n",
      "2  3  4  5\n",
      "3  d  4  6\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# Series\n",
    "number_list = pd.Series([1,2,3,4,5,6])\n",
    "# DataFrame\n",
    "matrix_list = pd.DataFrame([[1,2,3],\n",
    "\t\t\t\t            ['a','b','c'],\n",
    "\t\t\t\t            [3,4,5],\n",
    "\t\t\t\t            ['d',4,6]])\n",
    "# [1] attribute .info()\n",
    "print(\"[1] attribute .info()\")\n",
    "print(matrix_list.info())\n",
    "# [2] attribute .shape\n",
    "print(\"\\n[2] attribute .shape\")\n",
    "print(\"    Shape dari number_list:\", number_list.shape)\n",
    "print(\"    Shape dari matrix_list:\", matrix_list.shape)\n",
    "# [3] attribute .dtypes\n",
    "print(\"\\n[3] attribute .dtypes\")\n",
    "print(\"    Tipe data number_list:\", number_list.dtypes)\n",
    "print(\"    Tipe data matrix_list:\", matrix_list.dtypes)\n",
    "# [4] attribute .astype()\n",
    "print(\"\\n[4] attribute .astype()\")\n",
    "print(\"    Konversi number_list ke str:\", number_list.astype(\"str\"))\n",
    "print(\"    Konversi matrix_list ke str:\", matrix_list.astype(\"str\"))"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Atribut DataFrame & Series - Part 2\n",
    "Dataframe dan Series memiliki sangat banyak atribut yang digunakan untuk transformasi data, tetapi ada beberapa attribute yang sering dipakai. Di sini series number_list dan data frame matrix_list digunakan kembali.\n",
    "\n",
    "5. Attribute .copy()\n",
    "\n",
    "Attribute .copy() digunakan melakukan duplikat, untuk disimpan di variable yang berbeda mungkin supaya tidak loading data lagi.\n",
    "\n",
    "6. Attribute .to_list()\n",
    "\n",
    "Attribute .to_list() digunakan untuk mengubah series menjadi list dan tidak dapat digunakan untuk dataframe.\n",
    "\n",
    "\n",
    "7. Attribute .unique()\n",
    "\n",
    "Attribute .unique() digunakan menghasilkan nilai unik dari suatu kolom, hasilnya dalam bentuk numpy array. Attribute ini hanya digunakan pada series saja."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[5] attribute .copy()\n",
      "    Copy number_list ke num_list: 0    1\n",
      "1    2\n",
      "2    3\n",
      "3    4\n",
      "4    5\n",
      "5    6\n",
      "dtype: int64\n",
      "    Copy matrix_list ke mtr_list:    0  1  2\n",
      "0  1  2  3\n",
      "1  a  b  c\n",
      "2  3  4  5\n",
      "3  d  4  6\n",
      "[6] attribute .to_list()\n",
      "[1, 2, 3, 4, 5, 6]\n",
      "[7] attribute .unique()\n",
      "[1 2 3 4 5 6]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# Series\n",
    "number_list = pd.Series([1,2,3,4,5,6])\n",
    "# DataFrame\n",
    "matrix_list = pd.DataFrame([[1,2,3],\n",
    "\t\t\t\t            ['a','b','c'],\n",
    "\t\t\t\t            [3,4,5],\n",
    "\t\t\t\t            ['d',4,6]])\n",
    "# [5] attribute .copy()\n",
    "print(\"[5] attribute .copy()\")\n",
    "num_list = number_list.copy()\n",
    "print(\"    Copy number_list ke num_list:\", num_list)\n",
    "mtr_list = matrix_list.copy()\n",
    "print(\"    Copy matrix_list ke mtr_list:\", mtr_list)\t\n",
    "# [6] attribute .to_list()\n",
    "print(\"[6] attribute .to_list()\")\n",
    "print(number_list.to_list())\n",
    "# [7] attribute .unique()\n",
    "print(\"[7] attribute .unique()\")\n",
    "print(number_list.unique())"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Atribut DataFrame & Series - Part 3\n",
    "Dataframe dan Series memiliki sangat banyak atribut yang digunakan untuk transformasi data, tetapi ada beberapa attribute yang sering dipakai. Di sini series number_list dan data frame matrix_list pada subbab sebelumnya digunakan kembali.\n",
    "\n",
    "8. Attribute .index\n",
    "\n",
    "Attribute .index digunakan untuk mencari index/key dari Series atau Dataframe.\n",
    "\n",
    "9. Attribute .columns\n",
    "\n",
    "Attribute .columns digunakan untuk mengetahui apa saja kolom yang tersedia di dataframe tersebut (hanya digunakan untuk dataframe saja). \n",
    "\n",
    "10. Attribute .loc\n",
    "\n",
    "Attribute .loc digunakan slice dataframe atau series berdasarkan nama kolom dan/atau nama index.\n",
    "\n",
    "11. Attribute .iloc\n",
    "\n",
    "Attribute .iloc digunakan untuk slice dataframe atau series berdasarkan index kolom dan/atau index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[8] attribute .index\n",
      "    Index number_list: RangeIndex(start=0, stop=6, step=1)\n",
      "    Index matrix_list: RangeIndex(start=0, stop=4, step=1)\n",
      "[9] attribute .columns\n",
      "    Column matrix_list: RangeIndex(start=0, stop=3, step=1)\n",
      "[10] attribute .loc\n",
      "    .loc[0:1] pada number_list: 0    1\n",
      "1    2\n",
      "dtype: int64\n",
      "    .loc[0:1] pada matrix_list:    0  1  2\n",
      "0  1  2  3\n",
      "1  a  b  c\n",
      "[11] attribute .iloc\n",
      "    iloc[0:1] pada number_list: 0    1\n",
      "dtype: int64\n",
      "    iloc[0:1] pada matrix_list:    0  1  2\n",
      "0  1  2  3\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# Series\n",
    "number_list = pd.Series([1,2,3,4,5,6])\n",
    "# DataFrame\n",
    "matrix_list = pd.DataFrame([[1,2,3],\n",
    "\t\t\t\t            ['a','b','c'],\n",
    "\t\t\t\t            [3,4,5],\n",
    "\t\t\t\t            ['d',4,6]])\n",
    "# [8] attribute .index\n",
    "print(\"[8] attribute .index\")\n",
    "print(\"    Index number_list:\", number_list.index)\n",
    "print(\"    Index matrix_list:\", matrix_list.index)\t\n",
    "# [9] attribute .columns\n",
    "print(\"[9] attribute .columns\")\n",
    "print(\"    Column matrix_list:\", matrix_list.columns)\n",
    "# [10] attribute .loc\n",
    "print(\"[10] attribute .loc\")\n",
    "print(\"    .loc[0:1] pada number_list:\", number_list.loc[0:1])\n",
    "print(\"    .loc[0:1] pada matrix_list:\", matrix_list.loc[0:1])\n",
    "# [11] attribute .iloc\n",
    "print(\"[11] attribute .iloc\")\n",
    "print(\"    iloc[0:1] pada number_list:\", number_list.iloc[0:1])\n",
    "print(\"    iloc[0:1] pada matrix_list:\", matrix_list.iloc[0:1])\t"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Creating Series & Dataframe from List\n",
    "Untuk membuat Series atau Dataframe bisa dari berbagai macam tipe data container/mapping di python, seperti list dan dictionary, maupun dari numpy array."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    a\n",
      "1    1\n",
      "2    3\n",
      "3    5\n",
      "4    c\n",
      "5    d\n",
      "dtype: object\n",
      "     float char   obj char\n",
      "dq     1.0    a     b    c\n",
      "lab    2.5    d     e    f\n",
      "kar    5.0    g     h    i\n",
      "lan    7.5    j  10.5    l\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# Creating series from list\n",
    "ex_list = ['a',1,3,5,'c','d']\n",
    "ex_series = pd.Series(ex_list)\n",
    "print(ex_series)\n",
    "# Creating dataframe from list of list\n",
    "ex_list_of_list = [[1, 'a','b','c'],\n",
    "                   [2.5, 'd','e','f'],\n",
    "\t\t           [5,'g','h','i'],\n",
    "\t\t           [7.5,'j',10.5,'l']]\n",
    "index = ['dq','lab','kar','lan']\n",
    "cols = ['float','char','obj','char']\n",
    "ex_df = pd.DataFrame(ex_list_of_list, index=index, columns=cols)\n",
    "print(ex_df)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Creating Series & Dataframe from Dictionary\n",
    "Untuk membuat Series atau Dataframe bisa dari berbagai macam tipe data container/mapping di python, seperti list dan dictionary, maupun dari numpy array."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1    a\n",
      "2    b\n",
      "3    c\n",
      "dtype: object\n",
      "   1  2  4\n",
      "0  a  b  2\n",
      "1  b  c  3\n",
      "2  c  d  z\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# Creating series from dictionary\n",
    "dict_series = {'1':'a',\n",
    "\t\t\t   '2':'b',\n",
    "\t\t\t   '3':'c'}\n",
    "ex_series = pd.Series(dict_series)\n",
    "print(ex_series)\n",
    "# Creating dataframe from dictionary\n",
    "df_series = {'1':['a','b','c'],\n",
    "             '2':['b','c','d'],\n",
    "             '4':[2,3,'z']}\n",
    "ex_df = pd.DataFrame(df_series)\n",
    "print(ex_df)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Creating Series & Dataframe from Numpy Array\n",
    "Untuk membuat Series atau Dataframe bisa dari berbagai macam tipe data container/mapping di python, \n",
    "seperti list dan dictionary, maupun dari numpy array."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    1\n",
      "1    2\n",
      "2    3\n",
      "3    4\n",
      "4    5\n",
      "5    6\n",
      "6    6\n",
      "7    7\n",
      "dtype: int32\n",
      "   0  1  2   3\n",
      "0  1  2  3   5\n",
      "1  5  6  7   8\n",
      "2  a  b  c  10\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "# Creating series from numpy array (1D)\n",
    "arr_series = np.array([1,2,3,4,5,6,6,7])\n",
    "ex_series = pd.Series(arr_series)\n",
    "print(ex_series)\n",
    "# Creating dataframe from numpy array (2D)\n",
    "arr_df = np.array([[1,2,3,5],\n",
    "                   [5,6,7,8],\n",
    "                   ['a','b','c',10]])\n",
    "ex_df = pd.DataFrame(arr_df)\n",
    "print(ex_df)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Read Dataset - CSV dan TSV\n",
    "CSV dan TSV pada hakikatnya adalah tipe data text dengan perbedaan terletak pada pemisah antar data dalam satu baris. Pada file CSV, antar data dalam satu baris dipisahkan oleh comma, \",\". Namun, pada file TSV antar data dalam satu baris dipisahkan oleh \"Tab\".\n",
    "\n",
    "Fungsi .read_csv() digunakan untuk membaca file yang value nya dipisahkan oleh comma (default), terkadang pemisah value nya bisa di set ‘\\t’ untuk file tsv (tab separated values)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   order_id  order_date  customer_id             city     province product_id  \\\n",
      "0   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P0648   \n",
      "1   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P3826   \n",
      "2   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P1508   \n",
      "\n",
      "     brand  quantity  item_price  \n",
      "0  BRAND_C         4     1934000  \n",
      "1  BRAND_V         8      604000  \n",
      "2  BRAND_G        12      747000  \n",
      "   order_id  order_date  customer_id             city     province product_id  \\\n",
      "0   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P0648   \n",
      "1   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P3826   \n",
      "2   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P1508   \n",
      "\n",
      "     brand  quantity  item_price  \n",
      "0  BRAND_C         4     1934000  \n",
      "1  BRAND_V         8      604000  \n",
      "2  BRAND_G        12      747000  \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# File CSV\n",
    "df_csv = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/sample_csv.csv\")\n",
    "print(df_csv.head(3)) # Menampilkan 3 data teratas\n",
    "# File TSV\n",
    "df_tsv = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/sample_tsv.tsv\", sep='\\t')\n",
    "print(df_tsv.head(3)) # Menampilkan 3 data teratas"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Read Dataset - Excel\n",
    "File Excel dengan ekstensi *.xls atau *.xlsx cukup banyak digunakan dalam menyimpan data. Pandas juga memiliki fitur untuk membaca file excel."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   order_id  order_date  customer_id             city     province product_id  \\\n",
      "0   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P0648   \n",
      "1   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P3826   \n",
      "2   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P1508   \n",
      "3   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P0520   \n",
      "\n",
      "     brand  quantity  item_price  \n",
      "0  BRAND_C         4     1934000  \n",
      "1  BRAND_V         8      604000  \n",
      "2  BRAND_G        12      747000  \n",
      "3  BRAND_B        12      450000  \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# File xlsx dengan data di sheet \"test\"\n",
    "df_excel = pd.read_excel(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/sample_excel.xlsx\", sheet_name=\"test\")\n",
    "print(df_excel.head(4)) # Menampilkan 4 data teratas"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    " Read Dataset - JSON\n",
    "Method .read_json() digunakan untuk membaca URL API yang formatnya JSON dan merubahnya menjadi dataframe pandas. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                                data          dt          ts\n",
      "0  {'location': 'US', 'confirmed': 3363056, 'deat...  07-14-2020  1594684800\n",
      "1  {'location': 'Brazil', 'confirmed': 1884967, '...  07-14-2020  1594684800\n",
      "2  {'location': 'India', 'confirmed': 906752, 'de...  07-14-2020  1594684800\n",
      "3  {'location': 'Russia', 'confirmed': 732547, 'd...  07-14-2020  1594684800\n",
      "4  {'location': 'Peru', 'confirmed': 330123, 'dea...  07-14-2020  1594684800\n",
      "5  {'location': 'Chile', 'confirmed': 317657, 'de...  07-14-2020  1594684800\n",
      "6  {'location': 'Mexico', 'confirmed': 304435, 'd...  07-14-2020  1594684800\n",
      "7  {'location': 'United Kingdom', 'confirmed': 29...  07-14-2020  1594684800\n",
      "8  {'location': 'South Africa', 'confirmed': 2877...  07-14-2020  1594684800\n",
      "9  {'location': 'Iran', 'confirmed': 259652, 'dea...  07-14-2020  1594684800\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# File JSON\n",
    "url = \"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/covid2019-api-herokuapp-v2.json\"\n",
    "df_json = pd.read_json(url)\n",
    "print(df_json.head(10)) # Menampilkan 10 data teratas"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Head & Tail\n",
    "Method .head ditujukan untuk membatasi tampilan jumlah baris teratas dari dataset. Sementara itu, method .tail ditujukan untuk membatasi jumlah baris terbawah dari dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Tiga data teratas:\n",
      "    order_id  order_date  customer_id             city     province product_id  \\\n",
      "0   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P0648   \n",
      "1   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P3826   \n",
      "2   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P1508   \n",
      "\n",
      "     brand  quantity  item_price  \n",
      "0  BRAND_C         4     1934000  \n",
      "1  BRAND_V         8      604000  \n",
      "2  BRAND_G        12      747000  \n",
      "Tiga data terbawah:\n",
      "      order_id  order_date  customer_id      city          province product_id  \\\n",
      "98    1612390  2019-01-01        12681  Makassar  Sulawesi Selatan      P3354   \n",
      "99    1612390  2019-01-01        12681  Makassar  Sulawesi Selatan      P3357   \n",
      "100   1612390  2019-01-01        12681  Makassar  Sulawesi Selatan      P0422   \n",
      "\n",
      "       brand  quantity  item_price  \n",
      "98   BRAND_S        24      450000  \n",
      "99   BRAND_S        24      450000  \n",
      "100  BRAND_B         4     1325000  \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# Baca file sample_csv.csv\n",
    "df = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/sample_csv.csv\")\n",
    "# Tampilkan 3 data teratas\n",
    "print(\"Tiga data teratas:\\n\", df.head(3))\n",
    "# Tampilkan 3 data terbawah\n",
    "print(\"Tiga data terbawah:\\n\", df.tail(3))"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Indexing - Part 1\n",
    "Index merupakan key identifier dari tiap row/column untuk Series atau Dataframe (sifatnya tidak mutable untuk masing-masing value tapi bisa diganti untuk semua value sekaligus).\n",
    "\n",
    "Jika tidak disediakan, pandas akan membuat kolom index default secara otomatis sebagai bilangan bulat (integer) dari 0 sampai range jumlah baris data tersebut.\n",
    "\n",
    "Kolom index dapat terdiri dari\n",
    "\n",
    "satu kolom (single index), atau\n",
    "multiple kolom (disebut dengan hierarchical indexing).\n",
    "Index dengan multiple kolom ini terjadi karena unique identifier tidak dapat dicapai hanya dengan set index di 1 kolom saja sehingga membutuhkan beberapa kolom yang menjadikan tiap row menjadi unique."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Indexing - Part 2\n",
    "Pada sub bab ini akan mencetak index dan kolom yang dimiliki oleh file \"sample_csv.csv\". Untuk menentukan index dan kolom yang dimiliki oleh dataset yang telah dinyatakan sebagai sebuah dataframe pandas dapat dilakukan dengan menggunakan attribut .index dan .columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index: RangeIndex(start=0, stop=101, step=1)\n",
      "Columns: Index(['order_id', 'order_date', 'customer_id', 'city', 'province',\n",
      "       'product_id', 'brand', 'quantity', 'item_price'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# Baca file TSV sample_tsv.tsv\n",
    "df = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/sample_tsv.tsv\", sep=\"\\t\")\n",
    "# Index dari df\n",
    "print(\"Index:\", df.index)\n",
    "# Column dari df\n",
    "print(\"Columns:\",df.columns)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Indexing - Part 3\n",
    "\n",
    "Untuk membuat multi index (hierarchical indexing) dengan pandas diperlukan kolom-kolom mana saja yang perlu disusun agar index dari data frame menjadi sebuah hirarki yang kemudian dapat dikenali."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "order_date : Index(['2019-01-01'], dtype='object', name='order_date')\n",
      "city : Index(['Bogor', 'Jakarta Pusat', 'Jakarta Selatan', 'Jakarta Utara',\n",
      "       'Makassar', 'Malang', 'Surabaya', 'Tangerang'],\n",
      "      dtype='object', name='city')\n",
      "customer_id : Int64Index([12681, 13963, 15649, 17091, 17228, 17450, 17470, 17511, 17616,\n",
      "            18055],\n",
      "           dtype='int64', name='customer_id')\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# Baca file TSV sample_tsv.tsv\n",
    "df = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/sample_tsv.tsv\", sep=\"\\t\")\n",
    "# Set multi index df\n",
    "df_x = df.set_index(['order_date', 'city', 'customer_id'])\n",
    "# Print nama dan level dari multi index\n",
    "for name, level in zip(df_x.index.names, df_x.index.levels):\n",
    "    print(name,':',level)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Indexing - Part 4\n",
    "Terdapat beberapa cara untuk membuat index, salah satunya adalah seperti yang telah dilakukan pada sub bab sebelumnya dengan menggunakan method .set_index().\n",
    "\n",
    "Di sub bab ini akan menggunakan assignment untuk menset index dari suatu data frame. Untuk itu file \"sample_excel.xlsx\" yang digunakan."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataframe awal:\n",
      "    order_id  order_date  customer_id             city     province product_id  \\\n",
      "0   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P0648   \n",
      "1   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P3826   \n",
      "2   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P1508   \n",
      "3   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P0520   \n",
      "4   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P1513   \n",
      "5   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P3911   \n",
      "6   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P1780   \n",
      "7   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P3132   \n",
      "8   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P1342   \n",
      "9   1612339  2019-01-01        18055  Jakarta Selatan  DKI Jakarta      P2556   \n",
      "\n",
      "     brand  quantity  item_price  \n",
      "0  BRAND_C         4     1934000  \n",
      "1  BRAND_V         8      604000  \n",
      "2  BRAND_G        12      747000  \n",
      "3  BRAND_B        12      450000  \n",
      "4  BRAND_G         3     1500000  \n",
      "5  BRAND_V         3     2095000  \n",
      "6  BRAND_H         3     2095000  \n",
      "7  BRAND_S         3     1745000  \n",
      "8  BRAND_F         6     1045000  \n",
      "9  BRAND_P         6     1045000  \n",
      "Dataframe dengan index baru:\n",
      "                order_id  order_date  customer_id             city  \\\n",
      "Pesanan ke-1    1612339  2019-01-01        18055  Jakarta Selatan   \n",
      "Pesanan ke-2    1612339  2019-01-01        18055  Jakarta Selatan   \n",
      "Pesanan ke-3    1612339  2019-01-01        18055  Jakarta Selatan   \n",
      "Pesanan ke-4    1612339  2019-01-01        18055  Jakarta Selatan   \n",
      "Pesanan ke-5    1612339  2019-01-01        18055  Jakarta Selatan   \n",
      "Pesanan ke-6    1612339  2019-01-01        18055  Jakarta Selatan   \n",
      "Pesanan ke-7    1612339  2019-01-01        18055  Jakarta Selatan   \n",
      "Pesanan ke-8    1612339  2019-01-01        18055  Jakarta Selatan   \n",
      "Pesanan ke-9    1612339  2019-01-01        18055  Jakarta Selatan   \n",
      "Pesanan ke-10   1612339  2019-01-01        18055  Jakarta Selatan   \n",
      "\n",
      "                  province product_id    brand  quantity  item_price  \n",
      "Pesanan ke-1   DKI Jakarta      P0648  BRAND_C         4     1934000  \n",
      "Pesanan ke-2   DKI Jakarta      P3826  BRAND_V         8      604000  \n",
      "Pesanan ke-3   DKI Jakarta      P1508  BRAND_G        12      747000  \n",
      "Pesanan ke-4   DKI Jakarta      P0520  BRAND_B        12      450000  \n",
      "Pesanan ke-5   DKI Jakarta      P1513  BRAND_G         3     1500000  \n",
      "Pesanan ke-6   DKI Jakarta      P3911  BRAND_V         3     2095000  \n",
      "Pesanan ke-7   DKI Jakarta      P1780  BRAND_H         3     2095000  \n",
      "Pesanan ke-8   DKI Jakarta      P3132  BRAND_S         3     1745000  \n",
      "Pesanan ke-9   DKI Jakarta      P1342  BRAND_F         6     1045000  \n",
      "Pesanan ke-10  DKI Jakarta      P2556  BRAND_P         6     1045000  \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# Baca file sample_tsv.tsv untuk 10 baris pertama saja\n",
    "df = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/sample_tsv.tsv\", sep=\"\\t\", nrows=10)\n",
    "# Cetak data frame awal\n",
    "print(\"Dataframe awal:\\n\", df)\n",
    "# Set index baru\n",
    "df.index = [\"Pesanan ke-\" + str(i) for i in range(1,11)]\n",
    "# Cetak data frame dengan index baru\n",
    "print(\"Dataframe dengan index baru:\\n\",df)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Indexing - Part 5\n",
    "Jika file yang akan dibaca melalu penggunaan library pandas dapat dipreview terlebih dahulu struktur datanya maka melalui fungsi yang ditujukan untuk membaca file dapat diset mana kolom yang akan dijadikan index.\n",
    "\n",
    "Fitur ini telah dimiliki oleh setiap fungsi yang digunakan dalam membaca data dengan pandas, yaitu penggunaan argumen index_col pada fungsi yang dimaksud."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataframe:\n",
      "                      customer_id             city     province product_id  \\\n",
      "order_date order_id                                                         \n",
      "2019-01-01 1612339         18055  Jakarta Selatan  DKI Jakarta      P0648   \n",
      "           1612339         18055  Jakarta Selatan  DKI Jakarta      P3826   \n",
      "           1612339         18055  Jakarta Selatan  DKI Jakarta      P1508   \n",
      "           1612339         18055  Jakarta Selatan  DKI Jakarta      P0520   \n",
      "           1612339         18055  Jakarta Selatan  DKI Jakarta      P1513   \n",
      "           1612339         18055  Jakarta Selatan  DKI Jakarta      P3911   \n",
      "           1612339         18055  Jakarta Selatan  DKI Jakarta      P1780   \n",
      "           1612339         18055  Jakarta Selatan  DKI Jakarta      P3132   \n",
      "\n",
      "                       brand  quantity  item_price  \n",
      "order_date order_id                                 \n",
      "2019-01-01 1612339   BRAND_C         4     1934000  \n",
      "           1612339   BRAND_V         8      604000  \n",
      "           1612339   BRAND_G        12      747000  \n",
      "           1612339   BRAND_B        12      450000  \n",
      "           1612339   BRAND_G         3     1500000  \n",
      "           1612339   BRAND_V         3     2095000  \n",
      "           1612339   BRAND_H         3     2095000  \n",
      "           1612339   BRAND_S         3     1745000  \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# Baca file sample_tsv.tsv dan set lah index_col sesuai instruksi\n",
    "df = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/sample_tsv.tsv\", sep=\"\\t\", index_col=[\"order_date\",\"order_id\"])\n",
    "# Cetak data frame untuk 8 data teratas\n",
    "print(\"Dataframe:\\n\", df.head(8))"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Slicing - Part 1\n",
    "Seperti artinya slicing adalah cara untuk melakukan filter ke dataframe/series berdasarkan kriteria tertentu dari nilai kolomnya ataupun kriteria index-nya.\n",
    "\n",
    "Terdapat 2 cara paling terkenal untuk slicing dataframe, yaitu dengan menggunakan method .loc dan .iloc pada variabel bertipe pandas DataFrame/Series. Method .iloc ditujukan untuk proses slicing berdasarkan index berupa nilai integer tertentu. Akan tetapi akan lebih sering menggunakan dengan method .loc karena lebih fleksibel. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Slice langsung berdasarkan kolom:\n",
      " Empty DataFrame\n",
      "Columns: [order_id, order_date, customer_id, city, province, product_id, brand, quantity, item_price]\n",
      "Index: []\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Fachriezal\\anaconda3\\envs\\py38\\lib\\site-packages\\pandas\\core\\ops\\array_ops.py:253: FutureWarning: elementwise comparison failed; returning scalar instead, but in the future will perform elementwise comparison\n",
      "  res_values = method(rvalues)\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# Baca file sample_csv.csv\n",
    "df = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/sample_csv.csv\")\n",
    "# Slice langsung berdasarkan kolom\n",
    "df_slice = df.loc[(df[\"customer_id\"] == \"18055\") &\n",
    "\t\t          (df[\"product_id\"].isin([\"P0029\", \"P0040\", \"P0041\", \"P0116\",\"P0117\"]))\n",
    "\t\t\t\t ]\n",
    "print(\"Slice langsung berdasarkan kolom:\\n\", df_slice)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Slicing - Part 2\n",
    "Dalam sub bab sebelumnya telah mempelajari bagaimana menslicing/filtering dataset dengan menggunakan method .loc pada kolom dataset.\n",
    "\n",
    "Sekarang, menerapkan berdasarkan index. Tentu syaratnya adalah dataset sudah dilakukan indexing terlebih dahulu melalui penerapan method .set_index "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Slice df:\n",
      "                                 customer_id             city     province  \\\n",
      "order_date order_id product_id                                              \n",
      "2019-01-01 1612339  P2154             18055  Jakarta Selatan  DKI Jakarta   \n",
      "                    P2159             18055  Jakarta Selatan  DKI Jakarta   \n",
      "\n",
      "                                  brand  quantity  item_price  \n",
      "order_date order_id product_id                                 \n",
      "2019-01-01 1612339  P2154       BRAND_M         4     1745000  \n",
      "                    P2159       BRAND_M        24      310000  \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# Baca file sample_csv.csv\n",
    "df = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/sample_csv.csv\")\n",
    "# Set index dari df sesuai instruksi\n",
    "df = df.set_index([\"order_date\",\"order_id\",\"product_id\"])\n",
    "# Slice sesuai intruksi\n",
    "df_slice = df.loc[(\"2019-01-01\",1612339,[\"P2154\",\"P2159\"]),:]\n",
    "print(\"Slice df:\\n\", df_slice)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Transforming - Part 1\n",
    "Transform adalah ketika mengubah dataset yang ada menjadi entitas baru, dapat dilakukan dengan\n",
    "\n",
    "konversi dari satu data type ke data type yang lain,transpose dataframe atau yang lainnya."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Tipe data df:\n",
      " order_id        int64\n",
      "order_date     object\n",
      "customer_id     int64\n",
      "city           object\n",
      "province       object\n",
      "product_id     object\n",
      "brand          object\n",
      "quantity        int64\n",
      "item_price      int64\n",
      "dtype: object\n",
      "\n",
      "Tipe data df setelah transformasi:\n",
      " order_id                int64\n",
      "order_date     datetime64[ns]\n",
      "customer_id             int64\n",
      "city                   object\n",
      "province               object\n",
      "product_id             object\n",
      "brand                  object\n",
      "quantity                int64\n",
      "item_price              int64\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# Baca file sample_csv.csv\n",
    "df = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/sample_csv.csv\")\n",
    "# Tampilkan tipe data\n",
    "print(\"Tipe data df:\\n\", df.dtypes)\n",
    "# Ubah tipe data kolom order_date menjadi datetime\n",
    "df[\"order_date\"] = pd.to_datetime(df[\"order_date\"])\n",
    "# Tampilkan tipe data df setelah transformasi\n",
    "print(\"\\nTipe data df setelah transformasi:\\n\", df.dtypes)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Transforming - Part 2\n",
    "Pada sub bab ini akan mengubah tipe data pada kolom dataframe yang telah dibaca menjadi tipe data float (kolom quantity) dan tipe categori (kolom city)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Tipe data df:\n",
      " order_id        int64\n",
      "order_date     object\n",
      "customer_id     int64\n",
      "city           object\n",
      "province       object\n",
      "product_id     object\n",
      "brand          object\n",
      "quantity        int64\n",
      "item_price      int64\n",
      "dtype: object\n",
      "\n",
      "Tipe data df setelah transformasi:\n",
      " order_id          int64\n",
      "order_date       object\n",
      "customer_id       int64\n",
      "city           category\n",
      "province         object\n",
      "product_id       object\n",
      "brand            object\n",
      "quantity        float32\n",
      "item_price        int64\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# Baca file sample_csv.csv\n",
    "df = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/sample_csv.csv\")\n",
    "# Tampilkan tipe data\n",
    "print(\"Tipe data df:\\n\", df.dtypes)\n",
    "# Ubah tipe data kolom quantity menjadi tipe data numerik float\n",
    "df[\"quantity\"] = pd.to_numeric(df[\"quantity\"],downcast=\"float\")\n",
    "# Ubah tipe data kolom city menjadi tipe data category\n",
    "df[\"city\"] = df[\"city\"].astype(\"category\")\n",
    "# Tampilkan tipe data df setelah transformasi\n",
    "print(\"\\nTipe data df setelah transformasi:\\n\", df.dtypes)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Transforming - Part 3\n",
    "Sekarang akan mempelajari teknik/cara berikutnya dalam proses transformasi suatu dataframe. Di sub bab ini akan memakai method .apply() dan .map() pada suatu dataframe.\n",
    "\n",
    "Method .apply() digunakan untuk menerapkan suatu fungsi python (yang dibuat dengan def atau anonymous dengan lambda) pada dataframe/series atau hanya kolom tertentu dari dataframe. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Kolom brand awal:\n",
      " 0    BRAND_C\n",
      "1    BRAND_V\n",
      "2    BRAND_G\n",
      "3    BRAND_B\n",
      "4    BRAND_G\n",
      "Name: brand, dtype: object\n",
      "Kolom brand setelah apply:\n",
      " 0    brand_c\n",
      "1    brand_v\n",
      "2    brand_g\n",
      "3    brand_b\n",
      "4    brand_g\n",
      "Name: brand, dtype: object\n",
      "Kolom brand setelah map:\n",
      " 0    c\n",
      "1    v\n",
      "2    g\n",
      "3    b\n",
      "4    g\n",
      "Name: brand, dtype: object\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# Baca file sample_csv.csv\n",
    "df = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/sample_csv.csv\")\n",
    "# Cetak 5 baris teratas kolom brand\n",
    "print(\"Kolom brand awal:\\n\", df[\"brand\"].head())\n",
    "# Gunakan method apply untuk merubah isi kolom menjadi lower case\n",
    "df[\"brand\"] = df[\"brand\"].apply(lambda x: x.lower())\n",
    "# Cetak 5 baris teratas kolom brand\n",
    "print(\"Kolom brand setelah apply:\\n\", df[\"brand\"].head())\n",
    "# Gunakan method map untuk mengambil kode brand yaitu karakter terakhirnya\n",
    "df[\"brand\"] = df[\"brand\"].map(lambda x: x[-1])\n",
    "# Cetak 5 baris teratas kolom brand\n",
    "print(\"Kolom brand setelah map:\\n\", df[\"brand\"].head())"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Transforming - Part 4\n",
    "Di sub bab sebelumnya sudah mengetahui bahwa map hanya dapat digunakan untuk pandas series. Pada sub bab ini akan menggunakan method .applymap pada dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataframe:\n",
      "           0         1         2         3\n",
      "0  0.191519  0.622109  0.437728  0.785359\n",
      "1  0.779976  0.272593  0.276464  0.801872\n",
      "2  0.958139  0.875933  0.357817  0.500995\n",
      "\n",
      "Dataframe - cara 1:\n",
      "           0         1         2         3\n",
      "0  2.611238  4.253346  3.504789  4.972864\n",
      "1  4.948290  2.892085  2.905825  5.048616\n",
      "2  5.792449  5.395056  3.201485  3.753981\n",
      "\n",
      "Dataframe - cara 2:\n",
      "           0         1         2         3\n",
      "0  2.611238  4.253346  3.504789  4.972864\n",
      "1  4.948290  2.892085  2.905825  5.048616\n",
      "2  5.792449  5.395056  3.201485  3.753981\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "# number generator, set angka seed menjadi suatu angka, bisa semua angka, supaya hasil random nya selalu sama ketika kita run\n",
    "np.random.seed(1234)\n",
    "# create dataframe 3 baris dan 4 kolom dengan angka random\n",
    "df_tr = pd.DataFrame(np.random.rand(3,4))\n",
    "# Cetak dataframe\n",
    "print(\"Dataframe:\\n\", df_tr)\n",
    "# Cara 1 dengan tanpa define function awalnya, langsung pake fungsi anonymous lambda x\n",
    "df_tr1 = df_tr.applymap(lambda x: x**2 + 3*x + 2)\n",
    "print(\"\\nDataframe - cara 1:\\n\", df_tr1)\n",
    "# Cara 2 dengan define function \n",
    "def qudratic_fun(x):\n",
    "\treturn x**2 + 3*x + 2\n",
    "df_tr2 = df_tr.applymap(qudratic_fun)\n",
    "print(\"\\nDataframe - cara 2:\\n\", df_tr2)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Inspeksi Missing Value\n",
    "Value yang hilang/tidak lengkap dari dataframe akan membuat analisis atau model prediksi yang dibuat menjadi tidak akurat dan mengakibatkan keputusan salah yang diambil. Terdapat beberapa cara untuk mengatasi data yang hilang/tidak lengkap tersebut.\n",
    "\n",
    "Di pandas data yang hilang umumnya direpresentasikan dengan NaN."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1000 entries, 0 to 999\n",
      "Data columns (total 13 columns):\n",
      " #   Column          Non-Null Count  Dtype  \n",
      "---  ------          --------------  -----  \n",
      " 0   province_state  960 non-null    object \n",
      " 1   country_region  1000 non-null   object \n",
      " 2   date            1000 non-null   object \n",
      " 3   latitude        874 non-null    float64\n",
      " 4   longitude       874 non-null    float64\n",
      " 5   location_geom   874 non-null    object \n",
      " 6   confirmed       1000 non-null   int64  \n",
      " 7   deaths          999 non-null    float64\n",
      " 8   recovered       999 non-null    float64\n",
      " 9   active          949 non-null    float64\n",
      " 10  fips            949 non-null    float64\n",
      " 11  admin2          842 non-null    object \n",
      " 12  combined_key    0 non-null      float64\n",
      "dtypes: float64(7), int64(1), object(5)\n",
      "memory usage: 101.7+ KB\n",
      "None\n",
      "\n",
      "Jumlah missing value per kolom:\n",
      " province_state      40\n",
      "country_region       0\n",
      "date                 0\n",
      "latitude           126\n",
      "longitude          126\n",
      "location_geom      126\n",
      "confirmed            0\n",
      "deaths               1\n",
      "recovered            1\n",
      "active              51\n",
      "fips                51\n",
      "admin2             158\n",
      "combined_key      1000\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# Baca file \"public data covid19 jhu csse eu.csv\"\n",
    "df = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/CHAPTER+4+-+missing+value+-+public+data+covid19+.csv\")\n",
    "# Cetak info dari df\n",
    "print(df.info())\n",
    "# Cetak jumlah missing value di setiap kolom\n",
    "mv= df.isna().sum()\n",
    "print(\"\\nJumlah missing value per kolom:\\n\", mv)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Treatment untuk Missing Value - Part 2\n",
    "Sekarang dapat menerapkan dua aksi yaitu\n",
    "Membiarkannya saja\n",
    "Mengahapus kolom\n",
    " \n",
    "Treatment pertama (membiarkannya saja) seperti pada kolom confirmed, death, dan recovered. Akan tetapi jika tidak ada yang terkonfirmasi, meninggal dan sembuh sebenarnya dapat menukar value ini dengan angka nol. Meskipun ini lebih make sense dalam representasi datanya, tetapi untuk sub bab ini ketiga kolom tersebut diasumsikan dibiarkan memiliki nilai missing value.\n",
    "\n",
    "Treatment kedua yaitu dengan menghapus kolom, yang mana ini digunakan jika seluruh kolom dari dataset yang dipunyai semua barisnya adalah missing value. Untuk itu dapat menerapkan method .dropna() pada dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Ukuran awal df: 1000 baris, 13 kolom.\n",
      "Ukuran df setelah buang kolom dengan seluruh data missing: 1000 baris, 12 kolom.\n",
      "Ukuran df setelah dibuang baris yang memiliki sekurangnya 1 missing value: 746 baris, 12 kolom.\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# Baca file \"public data covid19 jhu csse eu.csv\"\n",
    "df = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/CHAPTER+4+-+missing+value+-+public+data+covid19+.csv\")\n",
    "# Cetak ukuran awal dataframe\n",
    "print(\"Ukuran awal df: %d baris, %d kolom.\" % df.shape)\n",
    "# Drop kolom yang seluruhnya missing value dan cetak ukurannya\n",
    "df = df.dropna(axis=1, how=\"all\")\n",
    "print(\"Ukuran df setelah buang kolom dengan seluruh data missing: %d baris, %d kolom.\" % df.shape)\n",
    "# Drop baris jika ada satu saja data yang missing dan cetak ukurannya\n",
    "df = df.dropna(axis=0, how=\"any\")\n",
    "print(\"Ukuran df setelah dibuang baris yang memiliki sekurangnya 1 missing value: %d baris, %d kolom.\" % df.shape)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Treatment untuk Missing Value - Part 3\n",
    "Sekarang, akan melakukan treatment ketiga untuk menghandle missing value pada dataframe. Treatment ini dilakukan dengan cara mengisi missing value dengan nilai lain, yang dapat berupa :\n",
    "\n",
    "nilai statistik seperti mean atau median\n",
    "interpolasi data\n",
    "text tertentu"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique value awal:\n",
      " [nan 'US' 'Guam' 'Iowa']\n",
      "Unique value setelah fillna:\n",
      " ['unknown_province_state' 'US' 'Guam' 'Iowa']\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# Baca file \"public data covid19 jhu csse eu.csv\"\n",
    "df = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/CHAPTER+4+-+missing+value+-+public+data+covid19+.csv\")\n",
    "# Cetak ukuran awal dataframe\n",
    "# Cetak unique value pada kolom province_state\n",
    "print(\"Unique value awal:\\n\", df[\"province_state\"].unique())\n",
    "# Ganti missing value dengan string \"unknown_province_state\"\n",
    "df[\"province_state\"] = df[\"province_state\"].fillna(\"unknown_province_state\")\n",
    "# Cetak kembali unique value pada kolom province_state\n",
    "print(\"Unique value setelah fillna:\\n\",  df[\"province_state\"].unique())"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Treatment untuk Missing Value - Part 4\n",
    "Masih melanjutkan bagaimana menghandle missing value tentunya dengan jalan mengganti missing value dengan nilai lainnya. Pada bab sebelumnya telah mengganti kolom bertipe objek dengan sesuatu string/teks.\n",
    "\n",
    "Dalam sub bab ini akan mengganti missing value dengan nilai statistik kolom bersangkutan, baik median atau mean (nilai rata-rata). Misalnya akan menggunakan kolom active. Dengan mengabaikan terlebih dahulu sebaran berdasarkan negara (univariate), jika mengisi dengan nilai rata-rata maka harus melihat terlebih dahulu data apakah memiliki ouliers atau tidak. Jika ada outliers dari data maka menggunakan nilai tengah (median) data adalah cara yang lebih safe.\n",
    "\n",
    "Untuk itu diputuskan dengan mengecek nilai median dan nilai mean kolom active juga nilai min dan max-nya. Jika data pada kolom active terdistribusi normal maka nilai mean dan median akan hampir sama."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Awal: mean = 192.571128, median = 41.000000.\n",
      "Fillna median: mean = 184.841000, median = 41.000000.\n",
      "Fillna mean: mean = 192.571128, median = 49.000000.\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# Baca file \"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/CHAPTER+4+-+missing+value+-+public+data+covid19+.csv\"\n",
    "df = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/CHAPTER+4+-+missing+value+-+public+data+covid19+.csv\")\n",
    "# Cetak nilai mean dan median awal\n",
    "print(\"Awal: mean = %f, median = %f.\" % (df[\"active\"].mean(), df[\"active\"].median()))\n",
    "# Isi missing value kolom active dengan median\n",
    "df_median = df[\"active\"].fillna(df[\"active\"].median())\n",
    "# Cetak nilai mean dan median awal setelah diisi dengan median\n",
    "print(\"Fillna median: mean = %f, median = %f.\" % (df_median.mean(), df_median.median()))\n",
    "# Isi missing value kolom active dengan mean\n",
    "df_mean = df[\"active\"].fillna(df[\"active\"].mean())\n",
    "# Cetak nilai mean dan median awal setelah diisi dengan mean\n",
    "print(\"Fillna mean: mean = %f, median = %f.\" % (df_mean.mean(), df_mean.median()))"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Treatment untuk Missing Value - Part 5\n",
    "Di bagian ini akan menggunakan teknik interpolasi dalam mengisi nilai missing value pada suatu dataset.\n",
    "\n",
    "Data yang menggunakan interpolasi untuk mengisi data yang hilang adalah time series data, yang secara default akan diisi dengan interpolasi linear."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Setelah diisi missing valuenya:\n",
      " 2020-01-01     9.0\n",
      "2020-01-02    14.0\n",
      "2020-01-05    19.0\n",
      "2020-01-07    24.0\n",
      "2020-01-10    27.0\n",
      "2020-01-12    30.0\n",
      "2020-01-15    33.0\n",
      "2020-01-17    36.5\n",
      "2020-01-16    40.0\n",
      "2020-01-20    45.0\n",
      "2020-01-22    52.0\n",
      "2020-01-25    75.0\n",
      "2020-01-28    75.0\n",
      "2020-01-30    75.0\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "# Data\n",
    "ts = pd.Series({\n",
    "   \"2020-01-01\":9,\n",
    "   \"2020-01-02\":np.nan,\n",
    "   \"2020-01-05\":np.nan,\n",
    "   \"2020-01-07\":24,\n",
    "   \"2020-01-10\":np.nan,\n",
    "   \"2020-01-12\":np.nan,\n",
    "   \"2020-01-15\":33,\n",
    "   \"2020-01-17\":np.nan,\n",
    "   \"2020-01-16\":40,\n",
    "   \"2020-01-20\":45,\n",
    "   \"2020-01-22\":52,\n",
    "   \"2020-01-25\":75,\n",
    "   \"2020-01-28\":np.nan,\n",
    "   \"2020-01-30\":np.nan\n",
    "})\n",
    "# Isi missing value menggunakan interpolasi linier\n",
    "ts = ts.interpolate()\n",
    "# Cetak time series setelah interpolasi linier\n",
    "print(\"Setelah diisi missing valuenya:\\n\", ts)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Project dari Andra\n",
    "Berikut adalah isi email yang ditugaskan oleh Andra:\n",
    "Diberikan dataset ‘retail_raw_test.csv’\n",
    "\n",
    "1. Baca dataset\n",
    "\n",
    "2. Tipe data diubah menjadi tipe yang seharusnya\n",
    "    customer_id dari string ke int64,\n",
    "    quantity dari string ke int64,\n",
    "    item_price dari string ke int64\n",
    "    \n",
    "3. transform product_value supaya bentuknya seragam dengan format PXXXX, assign ke kolom baru \"product_id\", dan drop kolom \"product_value\", jika terdapat nan gantilah dengan \"unknown\".\n",
    "\n",
    "4. tranform order_date menjadi value dengan format YYYY-mm-\n",
    "\n",
    "5. cek data hilang dari tiap kolom dan kemudian isi missing value\n",
    "    di brand dengan \"no_brand\", dan\n",
    "    cek dulu bagaimana missing value di city & province - isi missing value di city dan province dengan \"unknown\"\n",
    "    \n",
    "6. create column city/province dari gabungan city & province\n",
    "\n",
    "7. membuat index berdasarkan city_provice, order_date, customer_id, order_id, product_id (cek index)\n",
    "\n",
    "8. membuat kolom \"total_price\" sebagai hasil perkalian quantity dengan item_price\n",
    "slice data hanya untuk Jan 2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1] BACA DATASET\n",
      "    Dataset:\n",
      "    order_id    order_date customer_id           city     province    brand  \\\n",
      "0   1730350  Dec 11, 2019      '13447      Surakarta  Jawa Tengah  BRAND_F   \n",
      "1   1677490  Jul 31, 2019          '0            NaN          NaN  BRAND_F   \n",
      "2   1704211  Oct 18, 2019      '16128  Jakarta Pusat  DKI Jakarta  BRAND_H   \n",
      "3   1679695  Aug 07, 2019      '16225     Yogyakarta   Yogyakarta  BRAND_H   \n",
      "4   1679080  Aug 05, 2019          '0            NaN          NaN  BRAND_E   \n",
      "\n",
      "  quantity item_price  product_value  \n",
      "0      '24    '113000         1374.0  \n",
      "1       '1   '1164000         1370.0  \n",
      "2      '12    '747000         1679.0  \n",
      "3       '6    '590000         1708.0  \n",
      "4       '2    '740000         1201.0  \n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5000 entries, 0 to 4999\n",
      "Data columns (total 9 columns):\n",
      " #   Column         Non-Null Count  Dtype  \n",
      "---  ------         --------------  -----  \n",
      " 0   order_id       5000 non-null   int64  \n",
      " 1   order_date     5000 non-null   object \n",
      " 2   customer_id    5000 non-null   object \n",
      " 3   city           3802 non-null   object \n",
      " 4   province       3802 non-null   object \n",
      " 5   brand          4995 non-null   object \n",
      " 6   quantity       5000 non-null   object \n",
      " 7   item_price     5000 non-null   object \n",
      " 8   product_value  4995 non-null   float64\n",
      "dtypes: float64(1), int64(1), object(7)\n",
      "memory usage: 351.7+ KB\n",
      "    Info:\n",
      " None\n",
      "\n",
      "[2] UBAH TIPE DATA\n",
      " Tipe data:\n",
      " order_id           int64\n",
      "order_date        object\n",
      "customer_id        int64\n",
      "city              object\n",
      "province          object\n",
      "brand             object\n",
      "quantity           int64\n",
      "item_price         int64\n",
      "product_value    float64\n",
      "dtype: object\n",
      "\n",
      "[3] TRANSFORM product_value MENJADI product_id\n",
      "   order_id    order_date  customer_id           city     province    brand  \\\n",
      "0   1730350  Dec 11, 2019        13447      Surakarta  Jawa Tengah  BRAND_F   \n",
      "1   1677490  Jul 31, 2019            0            NaN          NaN  BRAND_F   \n",
      "2   1704211  Oct 18, 2019        16128  Jakarta Pusat  DKI Jakarta  BRAND_H   \n",
      "3   1679695  Aug 07, 2019        16225     Yogyakarta   Yogyakarta  BRAND_H   \n",
      "4   1679080  Aug 05, 2019            0            NaN          NaN  BRAND_E   \n",
      "\n",
      "   quantity  item_price product_id  \n",
      "0        24      113000      P1374  \n",
      "1         1     1164000      P1370  \n",
      "2        12      747000      P1679  \n",
      "3         6      590000      P1708  \n",
      "4         2      740000      P1201  \n",
      "\n",
      "[4] TRANSFORM order_date MENJADI FORMAT YYYY-mm-dd\n",
      " Tipe data:\n",
      " order_id                int64\n",
      "order_date     datetime64[ns]\n",
      "customer_id             int64\n",
      "city                   object\n",
      "province               object\n",
      "brand                  object\n",
      "quantity                int64\n",
      "item_price              int64\n",
      "product_id             object\n",
      "dtype: object\n",
      "\n",
      "[5] HANDLING MISSING VALUE\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5000 entries, 0 to 4999\n",
      "Data columns (total 9 columns):\n",
      " #   Column       Non-Null Count  Dtype         \n",
      "---  ------       --------------  -----         \n",
      " 0   order_id     5000 non-null   int64         \n",
      " 1   order_date   5000 non-null   datetime64[ns]\n",
      " 2   customer_id  5000 non-null   int64         \n",
      " 3   city         5000 non-null   object        \n",
      " 4   province     5000 non-null   object        \n",
      " 5   brand        5000 non-null   object        \n",
      " 6   quantity     5000 non-null   int64         \n",
      " 7   item_price   5000 non-null   int64         \n",
      " 8   product_id   5000 non-null   object        \n",
      "dtypes: datetime64[ns](1), int64(4), object(4)\n",
      "memory usage: 351.7+ KB\n",
      " Info:\n",
      " None\n",
      "\n",
      "[6] MEMBUAT KOLOM BARU city/province\n",
      "   order_id order_date  customer_id    brand  quantity  item_price product_id  \\\n",
      "0   1730350 2019-12-11        13447  BRAND_F        24      113000      P1374   \n",
      "1   1677490 2019-07-31            0  BRAND_F         1     1164000      P1370   \n",
      "2   1704211 2019-10-18        16128  BRAND_H        12      747000      P1679   \n",
      "3   1679695 2019-08-07        16225  BRAND_H         6      590000      P1708   \n",
      "4   1679080 2019-08-05            0  BRAND_E         2      740000      P1201   \n",
      "\n",
      "               city/province  \n",
      "0      Surakarta/Jawa Tengah  \n",
      "1            unknown/unknown  \n",
      "2  Jakarta Pusat/DKI Jakarta  \n",
      "3      Yogyakarta/Yogyakarta  \n",
      "4            unknown/unknown  \n",
      "\n",
      "[7] MEMBUAT HIERACHICAL INDEX\n",
      "                                                                     brand  \\\n",
      "city/province          order_date customer_id order_id product_id            \n",
      "Banda Aceh/Aceh        2019-04-17 12818       1642480  P1936       BRAND_K   \n",
      "                       2019-11-12 12360       1715116  P0758       BRAND_C   \n",
      "                                                       P3042       BRAND_R   \n",
      "                       2019-12-09 12374       1729036  P1660       BRAND_G   \n",
      "Bandar Lampung/Lampung 2019-01-15 12515       1619257  P0628       BRAND_C   \n",
      "\n",
      "                                                                   quantity  \\\n",
      "city/province          order_date customer_id order_id product_id             \n",
      "Banda Aceh/Aceh        2019-04-17 12818       1642480  P1936             24   \n",
      "                       2019-11-12 12360       1715116  P0758              8   \n",
      "                                                       P3042             12   \n",
      "                       2019-12-09 12374       1729036  P1660              4   \n",
      "Bandar Lampung/Lampung 2019-01-15 12515       1619257  P0628             12   \n",
      "\n",
      "                                                                   item_price  \n",
      "city/province          order_date customer_id order_id product_id              \n",
      "Banda Aceh/Aceh        2019-04-17 12818       1642480  P1936           450000  \n",
      "                       2019-11-12 12360       1715116  P0758           695000  \n",
      "                                                       P3042           310000  \n",
      "                       2019-12-09 12374       1729036  P1660          2795000  \n",
      "Bandar Lampung/Lampung 2019-01-15 12515       1619257  P0628           695000  \n",
      "\n",
      "[8] MEMBUAT KOLOM total_price\n",
      "                                                                     brand  \\\n",
      "city/province          order_date customer_id order_id product_id            \n",
      "Banda Aceh/Aceh        2019-04-17 12818       1642480  P1936       BRAND_K   \n",
      "                       2019-11-12 12360       1715116  P0758       BRAND_C   \n",
      "                                                       P3042       BRAND_R   \n",
      "                       2019-12-09 12374       1729036  P1660       BRAND_G   \n",
      "Bandar Lampung/Lampung 2019-01-15 12515       1619257  P0628       BRAND_C   \n",
      "\n",
      "                                                                   quantity  \\\n",
      "city/province          order_date customer_id order_id product_id             \n",
      "Banda Aceh/Aceh        2019-04-17 12818       1642480  P1936             24   \n",
      "                       2019-11-12 12360       1715116  P0758              8   \n",
      "                                                       P3042             12   \n",
      "                       2019-12-09 12374       1729036  P1660              4   \n",
      "Bandar Lampung/Lampung 2019-01-15 12515       1619257  P0628             12   \n",
      "\n",
      "                                                                   item_price  \\\n",
      "city/province          order_date customer_id order_id product_id               \n",
      "Banda Aceh/Aceh        2019-04-17 12818       1642480  P1936           450000   \n",
      "                       2019-11-12 12360       1715116  P0758           695000   \n",
      "                                                       P3042           310000   \n",
      "                       2019-12-09 12374       1729036  P1660          2795000   \n",
      "Bandar Lampung/Lampung 2019-01-15 12515       1619257  P0628           695000   \n",
      "\n",
      "                                                                   total_price  \n",
      "city/province          order_date customer_id order_id product_id               \n",
      "Banda Aceh/Aceh        2019-04-17 12818       1642480  P1936          10800000  \n",
      "                       2019-11-12 12360       1715116  P0758           5560000  \n",
      "                                                       P3042           3720000  \n",
      "                       2019-12-09 12374       1729036  P1660          11180000  \n",
      "Bandar Lampung/Lampung 2019-01-15 12515       1619257  P0628           8340000  \n",
      "\n",
      "[9] SLICE DATASET UNTUK BULAN JANUARI 2019 SAJA\n",
      "Dataset akhir:\n",
      "                                                                      brand  \\\n",
      "city/province          order_date customer_id order_id product_id            \n",
      "Bandar Lampung/Lampung 2019-01-15 12515       1619257  P0628       BRAND_C   \n",
      "Bandung/Jawa Barat     2019-01-09 16134       1617055  P1597       BRAND_G   \n",
      "                       2019-01-10 17392       1617952  P2137       BRAND_M   \n",
      "                       2019-01-14 15527       1618828  P3115       BRAND_S   \n",
      "                       2019-01-29 13253       1620289  P0099       BRAND_A   \n",
      "...                                                                    ...   \n",
      "unknown/unknown        2019-01-30 0           1620766  P3070       BRAND_R   \n",
      "                                                       P3483       BRAND_S   \n",
      "                       2019-01-31 0           1621057  P1298       BRAND_F   \n",
      "                                                       P1773       BRAND_H   \n",
      "                                                       P2877       BRAND_R   \n",
      "\n",
      "                                                                   quantity  \\\n",
      "city/province          order_date customer_id order_id product_id             \n",
      "Bandar Lampung/Lampung 2019-01-15 12515       1619257  P0628             12   \n",
      "Bandung/Jawa Barat     2019-01-09 16134       1617055  P1597              9   \n",
      "                       2019-01-10 17392       1617952  P2137              2   \n",
      "                       2019-01-14 15527       1618828  P3115              1   \n",
      "                       2019-01-29 13253       1620289  P0099             12   \n",
      "...                                                                     ...   \n",
      "unknown/unknown        2019-01-30 0           1620766  P3070              1   \n",
      "                                                       P3483              3   \n",
      "                       2019-01-31 0           1621057  P1298              1   \n",
      "                                                       P1773              5   \n",
      "                                                       P2877              1   \n",
      "\n",
      "                                                                   item_price  \\\n",
      "city/province          order_date customer_id order_id product_id               \n",
      "Bandar Lampung/Lampung 2019-01-15 12515       1619257  P0628           695000   \n",
      "Bandung/Jawa Barat     2019-01-09 16134       1617055  P1597           520000   \n",
      "                       2019-01-10 17392       1617952  P2137          1062000   \n",
      "                       2019-01-14 15527       1618828  P3115          1045000   \n",
      "                       2019-01-29 13253       1620289  P0099           450000   \n",
      "...                                                                       ...   \n",
      "unknown/unknown        2019-01-30 0           1620766  P3070           593000   \n",
      "                                                       P3483           593000   \n",
      "                       2019-01-31 0           1621057  P1298           296000   \n",
      "                                                       P1773           593000   \n",
      "                                                       P2877          1486000   \n",
      "\n",
      "                                                                   total_price  \n",
      "city/province          order_date customer_id order_id product_id               \n",
      "Bandar Lampung/Lampung 2019-01-15 12515       1619257  P0628           8340000  \n",
      "Bandung/Jawa Barat     2019-01-09 16134       1617055  P1597           4680000  \n",
      "                       2019-01-10 17392       1617952  P2137           2124000  \n",
      "                       2019-01-14 15527       1618828  P3115           1045000  \n",
      "                       2019-01-29 13253       1620289  P0099           5400000  \n",
      "...                                                                        ...  \n",
      "unknown/unknown        2019-01-30 0           1620766  P3070            593000  \n",
      "                                                       P3483           1779000  \n",
      "                       2019-01-31 0           1621057  P1298            296000  \n",
      "                                                       P1773           2965000  \n",
      "                                                       P2877           1486000  \n",
      "\n",
      "[334 rows x 4 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 1. Baca dataset\n",
    "print(\"[1] BACA DATASET\")\n",
    "df = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/retail_raw_test.csv\", low_memory=False)\n",
    "print(\"    Dataset:\\n\", df.head())\n",
    "print(\"    Info:\\n\", df.info())\n",
    "\n",
    "# 2. Ubah tipe data\n",
    "print(\"\\n[2] UBAH TIPE DATA\")\n",
    "df[\"customer_id\"] = df[\"customer_id\"].apply(lambda x: x.split(\"'\")[1]).astype(\"int64\")\n",
    "df[\"quantity\"] = df[\"quantity\"].apply(lambda x: x.split(\"'\")[1]).astype(\"int64\")\n",
    "df[\"item_price\"] = df[\"item_price\"].apply(lambda x: x.split(\"'\")[1]).astype(\"int64\")\n",
    "print(\" Tipe data:\\n\", df.dtypes)\n",
    "\n",
    "# 3. Transform \"product_value\" supaya bentuknya seragam dengan format \"PXXXX\", assign ke kolom baru \"product_id\", dan drop kolom \"product_value\", jika terdapat nan gantilah dengan \"unknown\"\n",
    "print(\"\\n[3] TRANSFORM product_value MENJADI product_id\")\n",
    "# Buat fungsi\n",
    "import math\n",
    "def impute_product_value(val):\n",
    "    if math.isnan(val):\n",
    "        return \"unknown\"\n",
    "    else:\n",
    "        return 'P' + '{:0>4}'.format(str(val).split('.')[0])\n",
    "# Buat kolom \"product_id\"\n",
    "df[\"product_id\"] = df[\"product_value\"].apply(lambda x: impute_product_value(x))\n",
    "# Hapus kolom \"product_value\"\n",
    "df.drop([\"product_value\"], axis=1, inplace=True)\n",
    "# Cetak 5 data teratas\n",
    "print(df.head())\n",
    "\n",
    "# 4. Tranform order_date menjadi value dengan format \"YYYY-mm-dd\"\n",
    "print(\"\\n[4] TRANSFORM order_date MENJADI FORMAT YYYY-mm-dd\")\n",
    "months_dict = {\n",
    "   \"Jan\":\"01\",\n",
    "   \"Feb\":\"02\",\n",
    "   \"Mar\":\"03\",\n",
    "   \"Apr\":\"04\",\n",
    "   \"May\":\"05\",\n",
    "   \"Jun\":\"06\",\n",
    "   \"Jul\":\"07\",\n",
    "   \"Aug\":\"08\",\n",
    "   \"Sep\":\"09\",\n",
    "   \"Oct\":\"10\",\n",
    "   \"Nov\":\"11\",\n",
    "   \"Dec\":\"12\"\n",
    "}\n",
    "df[\"order_date\"] = pd.to_datetime(df[\"order_date\"].apply(lambda x: str(x)[-4:] + \"-\" + months_dict[str(x)[:3]] + \"-\" + str(x)[4:7]))\n",
    "print(\" Tipe data:\\n\", df.dtypes)\n",
    "# 5. Mengatasi data yang hilang di beberapa kolom\n",
    "print(\"\\n[5] HANDLING MISSING VALUE\")\n",
    "# Kolom \"city\" dan \"province\" masih memiliki missing value, nilai yang hilang di kedua kolom ini diisi saja dengan \"unknown\"\n",
    "df[[\"city\",\"province\"]] = df[[\"city\",\"province\"]].fillna(\"unknown\")\n",
    "# Kolom brand juga masih memiliki missing value, Ganti value NaN menjadi \"no_brand\"\n",
    "df[\"brand\"] = df[\"brand\"].fillna(\"no_brand\")\n",
    "# Cek apakah masih terdapat missing value di seluruh kolom \n",
    "print(\" Info:\\n\", df.info())\n",
    "\n",
    "# 6. Membuat kolom baru \"city/province\" dengan menggabungkan kolom \"city\" dan kolom \"province\" dan delete kolom asalnya\n",
    "print(\"\\n[6] MEMBUAT KOLOM BARU city/province\")\n",
    "df[\"city/province\"] = df[\"city\"] + \"/\" + df[\"province\"]\n",
    "# drop kolom \"city\" dan \"province\" karena telah digabungkan\n",
    "df.drop([\"city\",\"province\"], axis=1, inplace=True)\n",
    "# Cetak 5 data teratas\n",
    "print(df.head())\n",
    "# 7. Membuat hierarchical index yang terdiri dari kolom \"city/province\", \"order_date\", \"customer_id\", \"order_id\", \"product_id\"\n",
    "print(\"\\n[7] MEMBUAT HIERACHICAL INDEX\")\n",
    "df = df.set_index([\"city/province\",\"order_date\",\"customer_id\",\"order_id\",\"product_id\"])\n",
    "# urutkanlah berdasarkan index yang baru\n",
    "df = df.sort_index()\n",
    "# Cetak 5 data teratas\n",
    "print(df.head())\n",
    "# 8. Membuat kolom \"total_price\" yang formula nya perkalian antara kolom \"quantity\" dan kolom \"item_price\"\n",
    "print(\"\\n[8] MEMBUAT KOLOM total_price\")\n",
    "df[\"total_price\"] = df[\"quantity\"] * df[\"item_price\"]\n",
    "# Cetak 5 data teratas\n",
    "print(df.head())\n",
    "\n",
    "# 9. Slice dataset agar hanya terdapat data bulan Januari 2019\n",
    "print(\"\\n[9] SLICE DATASET UNTUK BULAN JANUARI 2019 SAJA\")\n",
    "idx = pd.IndexSlice\n",
    "df_jan2019 = df.loc[idx[:, \"2019-01-01\":\"2019-01-31\"],:]\n",
    "print(\"Dataset akhir:\\n\", df_jan2019)\n",
    "# END OF PROJECT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}