Data Manipulation with Pandas - Part 1.ipynb

{"nbformat":4,"nbformat_minor":0,"metadata":{"colab":{"name":"Data Manipulation with Pandas - Part 1.ipynb","provenance":[],"collapsed_sections":[],"authorship_tag":"ABX9TyMCAH+Mr8ODxK7JlXJ2KGrN"},"kernelspec":{"name":"python3","display_name":"Python 3"}},"cells":[{"cell_type":"markdown","metadata":{"id":"T0mPF8vHPliK","colab_type":"text"},"source":["### **Introduction to Pandas**"]},{"cell_type":"markdown","metadata":{"id":"TF1eIFvcPyRP","colab_type":"text"},"source":["**Memanggil Library Pandas**"]},{"cell_type":"markdown","metadata":{"id":"Bavdj5neP4bJ","colab_type":"text"},"source":["Pandas adalah library python open source yang biasanya digunakan untuk kebutuhan data analisis. Pandas membuat Python supaya dapat bekerja dengan data yang berbentuk tabular sepert spreadsheet dengan cara pemuatan data yang tepat, manipulasi data, menggabungkan data, serta ada berbagai fungsi yang lain"]},{"cell_type":"code","metadata":{"id":"ipcpJYkBPZ8P","colab_type":"code","colab":{}},"source":["import pandas as pd\n","import numpy as np"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"TfxZQhFaQmoP","colab_type":"text"},"source":["**DataFrame & Series**"]},{"cell_type":"code","metadata":{"id":"uETwKqZjOxf9","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":255},"executionInfo":{"status":"ok","timestamp":1595524961848,"user_tz":-420,"elapsed":908,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"00d98886-3f08-4d93-a88b-ed23d804a88a"},"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","\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)"],"execution_count":null,"outputs":[{"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"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"MC6B1x2KRhTT","colab_type":"text"},"source":["**Atribut DataFrame & Series - Part 1**"]},{"cell_type":"code","metadata":{"id":"4QiYGsNOPZ-o","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":595},"executionInfo":{"status":"ok","timestamp":1595524965504,"user_tz":-420,"elapsed":855,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"2788d390-3b3e-41b1-8d61-20595b3b13b3"},"source":["import pandas as pd\n","\n","# Series\n","number_list = pd.Series([1,2,3,4,5,6])\n","\n","# DataFrame\n","matrix_list = pd.DataFrame([[1,2,3],\n","                            ['a','b','c'],\n","                            [3,4,5],\n","                            ['d',4,6]])\n","\n","# [1] attribute .info()\n","print(\"[1] attribute .info()\")\n","print(matrix_list.info())\n","\n","# [2] attribute .shape\n","print(\"[2] attribute .shape\")\n","print(\"Shape dari number_list:\", number_list.shape)\n","print(\"Shape dari matrix_list:\", matrix_list.shape)\n","\n","# [3] attribute .dtypes\n","print(\"attribute .dtypes\")\n","print(\"Tipe data dari number_list:\", number_list.dtypes)\n","print(\"Tipe data dari matrix_list:\", matrix_list.dtypes)\n","\n","# [4] attribute .astype()\n","print(\"attribute .astype()\")\n","print(\"Konversi number_list ke str:\", number_list.astype(\"str\"))\n","print(\"Konversi matrix_list ke str:\", matrix_list.astype(\"str\"))"],"execution_count":null,"outputs":[{"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","[2] attribute .shape\n","Shape dari number_list: (6,)\n","Shape dari matrix_list: (4, 3)\n","attribute .dtypes\n","Tipe data dari number_list: int64\n","Tipe data dari matrix_list: 0    object\n","1    object\n","2    object\n","dtype: object\n","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"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"-R24kOGdT6YM","colab_type":"text"},"source":["**Atribut DataFrame & Series - Part 2**"]},{"cell_type":"code","metadata":{"id":"1tIaxgUhPaBG","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":306},"executionInfo":{"status":"ok","timestamp":1595524970498,"user_tz":-420,"elapsed":861,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"052d3dd4-88f7-4ff8-b592-3c05e266d4a0"},"source":["import pandas as pd\n","\n","# Series\n","number_list = pd.Series([1,2,3,4,5,6])\n","\n","# DataFrame\n","matrix_list = pd.DataFrame([[1,2,3],\n","                            ['a','b','c'],\n","                            [3,4,5],\n","                            ['d',4,6]])\n","\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 number_list ke num_list:\", mtr_list)\n","\n","# [6] attribute .to_list()\n","print(\"[6] attribute .to_list()\")\n","print(number_list.to_list())\n","\n","# [7] attribute .unique()\n","print(\"[7] attribute .unique()\")\n","print(number_list.unique())"],"execution_count":null,"outputs":[{"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 number_list ke num_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"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"t5gq1eJDU-G4","colab_type":"text"},"source":["**Atribut DataFrame & Series - Part 3**"]},{"cell_type":"code","metadata":{"id":"INyYumwZPaDh","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":306},"executionInfo":{"status":"ok","timestamp":1595524975812,"user_tz":-420,"elapsed":1036,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"738d378d-8f77-4cb1-dd91-05ee0aeb9704"},"source":["import pandas as pd\n","\n","# Series\n","number_list = pd.Series([1,2,3,4,5,6])\n","\n","# DataFrame\n","matrix_list = pd.DataFrame([[1,2,3],\n","                            ['a','b','c'],\n","                            [3,4,5],\n","                            ['d',4,6]])\n","\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)\n","\n","# [9] attribute .columns\n","print(\"[9] attribute .columns\")\n","print(\"Columns matrix_list:\", matrix_list.columns)\n","\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","\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])"],"execution_count":null,"outputs":[{"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","Columns 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"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"MfVUTKVwWvuv","colab_type":"text"},"source":["**Quiz**"]},{"cell_type":"code","metadata":{"id":"lsQ40xpVPaFq","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":34},"executionInfo":{"status":"ok","timestamp":1595524980864,"user_tz":-420,"elapsed":904,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"c661178e-3897-498b-beb2-d23c36d6f9da"},"source":["matrix = [[1,2,3],\n","          ['a','b','c'],\n","          [3,4,5],\n","          ['d',4,6]]\n","matrix_list = pd.DataFrame(matrix)\n","\n","matrix_list.iloc[0:2,2].to_list()"],"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/plain":["[3, 'c']"]},"metadata":{"tags":[]},"execution_count":6}]},{"cell_type":"markdown","metadata":{"id":"pNpEf9lrW5Ys","colab_type":"text"},"source":["**Creating Series & DataFrame from List**"]},{"cell_type":"code","metadata":{"id":"9vmDwB1BPaH4","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":221},"executionInfo":{"status":"ok","timestamp":1595524983509,"user_tz":-420,"elapsed":844,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"852d44b1-e6d1-4363-f244-49ecd1fbdb5a"},"source":["import pandas as pd\n","\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","\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)"],"execution_count":null,"outputs":[{"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"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"aXuZV_LCXQqh","colab_type":"text"},"source":["**Creating Series & DataFrame from Dictionary**"]},{"cell_type":"code","metadata":{"id":"jruCk-2aPaKP","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":153},"executionInfo":{"status":"ok","timestamp":1595524994911,"user_tz":-420,"elapsed":853,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"d0fe3afd-e0b0-498f-8093-9de72933d2cf"},"source":["import pandas as pd\n","\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","\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)"],"execution_count":null,"outputs":[{"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"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"4ExY4K_AXem3","colab_type":"text"},"source":["**Creating Series & DataFrame fron Numpy Array**"]},{"cell_type":"code","metadata":{"id":"prut0QyuPaOj","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":238},"executionInfo":{"status":"ok","timestamp":1595524997874,"user_tz":-420,"elapsed":1034,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"c6f1d8fb-4140-4359-d919-b4ba8875e828"},"source":["import pandas as pd\n","import numpy as np\n","\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","\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)"],"execution_count":null,"outputs":[{"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: int64\n","   0  1  2   3\n","0  1  2  3   5\n","1  5  6  7   8\n","2  a  b  c  10\n"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"mTn5f_PYX37M","colab_type":"text"},"source":["### **Dataset I/O**"]},{"cell_type":"markdown","metadata":{"id":"Vg1mkHzkX_dY","colab_type":"text"},"source":["**Read Dataset - CSV dan TSV**"]},{"cell_type":"code","metadata":{"id":"RkBx7BUbPaRC","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":221},"executionInfo":{"status":"ok","timestamp":1595525005078,"user_tz":-420,"elapsed":2744,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"f6882049-38eb-4073-df08-d62343dcca46"},"source":["import pandas as pd\n","\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","\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"],"execution_count":null,"outputs":[{"output_type":"stream","text":["   order_id  order_date  customer_id  ...    brand quantity item_price\n","0   1612339  2019-01-01        18055  ...  BRAND_C        4    1934000\n","1   1612339  2019-01-01        18055  ...  BRAND_V        8     604000\n","2   1612339  2019-01-01        18055  ...  BRAND_G       12     747000\n","\n","[3 rows x 9 columns]\n","   order_id  order_date  customer_id  ...    brand quantity item_price\n","0   1612339  2019-01-01        18055  ...  BRAND_C        4    1934000\n","1   1612339  2019-01-01        18055  ...  BRAND_V        8     604000\n","2   1612339  2019-01-01        18055  ...  BRAND_G       12     747000\n","\n","[3 rows x 9 columns]\n"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"qdhh5Y3oYR_2","colab_type":"text"},"source":["**Read Dataset - Excel**"]},{"cell_type":"code","metadata":{"id":"6TSp--aQYViD","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":136},"executionInfo":{"status":"ok","timestamp":1595525010580,"user_tz":-420,"elapsed":1854,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"2faa1f2f-c997-4051-8df3-8fcb445aca02"},"source":["import pandas as pd\n","\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"],"execution_count":null,"outputs":[{"output_type":"stream","text":["   order_id  order_date  customer_id  ...    brand quantity item_price\n","0   1612339  2019-01-01        18055  ...  BRAND_C        4    1934000\n","1   1612339  2019-01-01        18055  ...  BRAND_V        8     604000\n","2   1612339  2019-01-01        18055  ...  BRAND_G       12     747000\n","3   1612339  2019-01-01        18055  ...  BRAND_B       12     450000\n","\n","[4 rows x 9 columns]\n"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"sQP4K1P0YdV4","colab_type":"text"},"source":["**Read Dataset - JSON**"]},{"cell_type":"code","metadata":{"id":"Ki-NsoQwPaTV","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":204},"executionInfo":{"status":"ok","timestamp":1595525014743,"user_tz":-420,"elapsed":1991,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"87da456f-3be4-4367-9024-3fb07a0a538a"},"source":["import pandas as pd\n","\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"],"execution_count":null,"outputs":[{"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"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"MwQ6-38VY792","colab_type":"text"},"source":["**Head & Tail**"]},{"cell_type":"code","metadata":{"id":"haobjSJKPaVq","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":255},"executionInfo":{"status":"ok","timestamp":1595525018396,"user_tz":-420,"elapsed":1867,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"3f6ad2fb-a479-41e4-bb85-33d8c5885ecb"},"source":["import pandas as pd\n","\n","# Baca file sample_csv.csv\n","df = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/sample_csv.csv\")\n","\n","# Tampilkan 3 data teratas\n","print(\"Tiga data teratas:\")\n","print(df.head(3))\n","\n","# Tampilkan 3 data terbawah\n","print(\"Tiga data terbawah:\")\n","print(df.tail(3))"],"execution_count":null,"outputs":[{"output_type":"stream","text":["Tiga data teratas:\n","   order_id  order_date  customer_id  ...    brand quantity item_price\n","0   1612339  2019-01-01        18055  ...  BRAND_C        4    1934000\n","1   1612339  2019-01-01        18055  ...  BRAND_V        8     604000\n","2   1612339  2019-01-01        18055  ...  BRAND_G       12     747000\n","\n","[3 rows x 9 columns]\n","Tiga data terbawah:\n","     order_id  order_date  customer_id  ...    brand quantity item_price\n","98    1612390  2019-01-01        12681  ...  BRAND_S       24     450000\n","99    1612390  2019-01-01        12681  ...  BRAND_S       24     450000\n","100   1612390  2019-01-01        12681  ...  BRAND_B        4    1325000\n","\n","[3 rows x 9 columns]\n"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"nAD49oG-ZOJp","colab_type":"text"},"source":["### **Indexing, Slicing, dan Transforming**"]},{"cell_type":"markdown","metadata":{"id":"AITM2HIOZX1l","colab_type":"text"},"source":["**Indexing - Part 2**"]},{"cell_type":"code","metadata":{"id":"1eOaSp3gPaYD","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":85},"executionInfo":{"status":"ok","timestamp":1595525022153,"user_tz":-420,"elapsed":1730,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"8482b24e-c5ec-42c8-bb2c-9ff09eef9351"},"source":["import pandas as pd\n","\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","\n","# Index dari df\n","print(\"Index:\", df.index)\n","\n","# Column dari df\n","print(\"Columns:\", df.columns)"],"execution_count":null,"outputs":[{"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"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"u9zPG6gpZ-fy","colab_type":"text"},"source":[""]},{"cell_type":"markdown","metadata":{"id":"Ii8htFp4Z-x_","colab_type":"text"},"source":["**Indexing - Part 3**"]},{"cell_type":"code","metadata":{"id":"R2Ko1NxyPaah","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":136},"executionInfo":{"status":"ok","timestamp":1595525026798,"user_tz":-420,"elapsed":1746,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"a8fd2f68-44f0-4ce9-b56b-7ae363b8bdcd"},"source":["import pandas as pd\n","\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","\n","# Set multi index df\n","df_x = df.set_index(['order_date', 'city', 'customer_id'])\n","\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)\n"],"execution_count":null,"outputs":[{"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"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"Bws8hEypaZrY","colab_type":"text"},"source":["**Indexing - Part 4**"]},{"cell_type":"code","metadata":{"id":"6UVC9KBiPac0","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":493},"executionInfo":{"status":"ok","timestamp":1595525030107,"user_tz":-420,"elapsed":1765,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"8c118532-db8c-476a-d045-f6aa5cfb2d83"},"source":["import pandas as pd\n","\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","\n","# Cetak data frame awal\n","print(\"Dataframe awal:\")\n","print(df)\n","\n","# Set index baru\n","df.index = [\"Pesanan ke-\" + str(i) for i in range(1,11)]\n","\n","# Cetak data frame dengan index baru\n","print(\"Dataframe dengan index baru:\")\n","print(df)"],"execution_count":null,"outputs":[{"output_type":"stream","text":["Dataframe awal:\n","   order_id  order_date  customer_id  ...    brand quantity item_price\n","0   1612339  2019-01-01        18055  ...  BRAND_C        4    1934000\n","1   1612339  2019-01-01        18055  ...  BRAND_V        8     604000\n","2   1612339  2019-01-01        18055  ...  BRAND_G       12     747000\n","3   1612339  2019-01-01        18055  ...  BRAND_B       12     450000\n","4   1612339  2019-01-01        18055  ...  BRAND_G        3    1500000\n","5   1612339  2019-01-01        18055  ...  BRAND_V        3    2095000\n","6   1612339  2019-01-01        18055  ...  BRAND_H        3    2095000\n","7   1612339  2019-01-01        18055  ...  BRAND_S        3    1745000\n","8   1612339  2019-01-01        18055  ...  BRAND_F        6    1045000\n","9   1612339  2019-01-01        18055  ...  BRAND_P        6    1045000\n","\n","[10 rows x 9 columns]\n","Dataframe dengan index baru:\n","               order_id  order_date  customer_id  ...    brand quantity item_price\n","Pesanan ke-1    1612339  2019-01-01        18055  ...  BRAND_C        4    1934000\n","Pesanan ke-2    1612339  2019-01-01        18055  ...  BRAND_V        8     604000\n","Pesanan ke-3    1612339  2019-01-01        18055  ...  BRAND_G       12     747000\n","Pesanan ke-4    1612339  2019-01-01        18055  ...  BRAND_B       12     450000\n","Pesanan ke-5    1612339  2019-01-01        18055  ...  BRAND_G        3    1500000\n","Pesanan ke-6    1612339  2019-01-01        18055  ...  BRAND_V        3    2095000\n","Pesanan ke-7    1612339  2019-01-01        18055  ...  BRAND_H        3    2095000\n","Pesanan ke-8    1612339  2019-01-01        18055  ...  BRAND_S        3    1745000\n","Pesanan ke-9    1612339  2019-01-01        18055  ...  BRAND_F        6    1045000\n","Pesanan ke-10   1612339  2019-01-01        18055  ...  BRAND_P        6    1045000\n","\n","[10 rows x 9 columns]\n"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"ySg_ZqL2a0YH","colab_type":"text"},"source":["**Indexing Part - 5**"]},{"cell_type":"code","metadata":{"id":"hqRMcb9ga3bB","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":238},"executionInfo":{"status":"ok","timestamp":1595525034527,"user_tz":-420,"elapsed":1691,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"85334df6-2bbd-4880-a154-a6a1b429bb2d"},"source":["import pandas as pd\n","\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","\n","# Cetak data frame untuk 8 data teratas\n","print(\"Dataframe:\")\n","print(df.head(8))"],"execution_count":null,"outputs":[{"output_type":"stream","text":["Dataframe:\n","                     customer_id             city  ... quantity item_price\n","order_date order_id                                ...                    \n","2019-01-01 1612339         18055  Jakarta Selatan  ...        4    1934000\n","           1612339         18055  Jakarta Selatan  ...        8     604000\n","           1612339         18055  Jakarta Selatan  ...       12     747000\n","           1612339         18055  Jakarta Selatan  ...       12     450000\n","           1612339         18055  Jakarta Selatan  ...        3    1500000\n","           1612339         18055  Jakarta Selatan  ...        3    2095000\n","           1612339         18055  Jakarta Selatan  ...        3    2095000\n","           1612339         18055  Jakarta Selatan  ...        3    1745000\n","\n","[8 rows x 7 columns]\n"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"JCGfB9iJbOca","colab_type":"text"},"source":["**Slicing - Part 1**"]},{"cell_type":"code","metadata":{"id":"h_IM2m9rbQ03","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":139},"executionInfo":{"status":"ok","timestamp":1595525037951,"user_tz":-420,"elapsed":1726,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"f598a563-27b0-4dc3-e78f-a83a2efd3eb1"},"source":["import pandas as pd\n","\n","# Baca file sample_csv.csv\n","df = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/sample_csv.csv\")\n","\n","# Slice langsung berdasarkan kolom\n","df_slice = df.loc[(df[\"customer_id\"] == \"18055\") & (df[\"product_id\"].isin([\"P0029\",\"P0040\",\"P0041\",\"P0116\",\"P0117\"]))]\n","print(\"Slice langsung berdasarkan kolom:\")\n","print(df_slice)"],"execution_count":null,"outputs":[{"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":"stdout"},{"output_type":"stream","text":["/usr/local/lib/python3.6/dist-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"],"name":"stderr"}]},{"cell_type":"markdown","metadata":{"id":"11hjbXLPbr4m","colab_type":"text"},"source":["**Sliicng - Part 2**"]},{"cell_type":"code","metadata":{"id":"FIRpsBNgbubx","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":136},"executionInfo":{"status":"ok","timestamp":1595525041630,"user_tz":-420,"elapsed":1817,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"ca570f66-0c71-4903-c417-69c127a81ebb"},"source":["import pandas as pd\n","\n","# Baca file sample_csv.csv\n","df = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/sample_csv.csv\")\n","\n","# Set index dari df sesuai instruksi\n","df = df.set_index([\"order_date\",\"order_id\",\"product_id\"])\n","\n","# Slice sesuai intruksi\n","df_slice = df.loc[(\"2019-01-01\",1612339,[\"P2154\",\"P2159\"]),:]\n","print(\"Slice df:\")\n","print(df_slice)"],"execution_count":null,"outputs":[{"output_type":"stream","text":["Slice df:\n","                                customer_id  ... item_price\n","order_date order_id product_id               ...           \n","2019-01-01 1612339  P2154             18055  ...    1745000\n","                    P2159             18055  ...     310000\n","\n","[2 rows x 6 columns]\n"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"CPE-ZjsZcY2s","colab_type":"text"},"source":["**Transforming - Part 1**"]},{"cell_type":"code","metadata":{"id":"WsHdaFIMca6c","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":408},"executionInfo":{"status":"ok","timestamp":1595525045185,"user_tz":-420,"elapsed":1739,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"fd4c060d-e79a-4121-f243-5f23ab546d48"},"source":["import pandas as pd\n","\n","# Baca file sample_csv.csv\n","df = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/sample_csv.csv\")\n","\n","# Tampilkan tipe data\n","print(\"Tipe data df:\")\n","print(df.dtypes)\n","\n","# Ubah tipe data kolom order_date menjadi datetime\n","df[\"order_date\"] = pd.to_datetime(df[\"order_date\"])\n","\n","# Tampilkan tipe data df setelah transformasi\n","print(\"\\nTipe data df setelah transformasi:\")\n","print(df.dtypes)"],"execution_count":null,"outputs":[{"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"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"3N10BXUnc0un","colab_type":"text"},"source":["**Transforming - Part 2**"]},{"cell_type":"code","metadata":{"id":"A01vxvToPafB","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":408},"executionInfo":{"status":"ok","timestamp":1595525048458,"user_tz":-420,"elapsed":1739,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"b8792b31-57d1-4143-acb8-29252d3bf1b0"},"source":["import pandas as pd\n","\n","# Baca file sample_csv.csv\n","df = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/sample_csv.csv\")\n","\n","# Tampilkan tipe data\n","print(\"Tipe data df:\")\n","print(df.dtypes)\n","\n","# Ubah tipe data kolom quantity menjadi tipe data numerik float\n","df[\"quantity\"] = pd.to_numeric(df[\"quantity\"], downcast=\"float\")\n","\n","# Ubah tipe data kolom city menjadi tipe data category\n","df[\"city\"] = df[\"city\"].astype(\"category\")\n","\n","# Tampilkan tipe data df setelah transformasi\n","print(\"\\nTipe data df setelah transformasi:\")\n","print(df.dtypes)"],"execution_count":null,"outputs":[{"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"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"980qNicjdIUZ","colab_type":"text"},"source":["**Transforming - Part 3**"]},{"cell_type":"code","metadata":{"id":"hdWpvGDDPaht","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":374},"executionInfo":{"status":"ok","timestamp":1595525051924,"user_tz":-420,"elapsed":1819,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"d83a91ca-e003-4b6e-c013-c6646370bb02"},"source":["import pandas as pd\n","\n","# Baca file sample_csv.csv\n","df = pd.read_csv(\"https://dqlab-dataset.s3-ap-southeast-1.amazonaws.com/sample_csv.csv\")\n","\n","# Cetak 5 baris teratas kolom brand\n","print(\"Kolom brand awal:\")\n","print(df[\"brand\"].head())\n","\n","# Gunakan method apply untuk merubah isi kolom menjadi lower case\n","df[\"brand\"] = df[\"brand\"].apply(lambda x: x.lower())\n","\n","# Cetak 5 baris teratas kolom brand\n","print(\"Kolom brand setelah apply:\")\n","print(df[\"brand\"].head())\n","\n","# Gunakan method map untuk mengambil kode brand yaitu karakter terakhirnya\n","df[\"brand\"] = df[\"brand\"].map(lambda x: x[-1])\n","\n","# Cetak 5 baris teratas kolom brand\n","print(\"Kolom brand setelah map:\")\n","print(df[\"brand\"].head())"],"execution_count":null,"outputs":[{"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"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"s5bRmdpFdpIv","colab_type":"text"},"source":["**Transforming - Part 4**"]},{"cell_type":"code","metadata":{"id":"hMEFrT1zdmNU","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":306},"executionInfo":{"status":"ok","timestamp":1595525054999,"user_tz":-420,"elapsed":828,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"6edbc12b-79d2-4937-d914-0ab23cc7736d"},"source":["import numpy as np\n","import pandas as pd\n","\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","\n","# create dataframe 3 baris dan 4 kolom dengan angka random\n","df_tr = pd.DataFrame(np.random.rand(3,4))\n","\n","# Cetak dataframe\n","print(\"Dataframe:\")\n","print(df_tr)\n","\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","print(df_tr1)\n","\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","print(df_tr2)"],"execution_count":null,"outputs":[{"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"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"7GbO5WxmeRz0","colab_type":"text"},"source":["### **Handling Missing Values**"]},{"cell_type":"markdown","metadata":{"id":"UZ6-2BbMeXbw","colab_type":"text"},"source":["**Inspeksi Missing Value**"]},{"cell_type":"code","metadata":{"id":"lIAe6ggUdmQR","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":646},"executionInfo":{"status":"ok","timestamp":1595525060471,"user_tz":-420,"elapsed":2212,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"9254e8c2-ac4b-4dda-f332-0050c17598be"},"source":["import pandas as pd\n","\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","\n","# Cetak info dari df\n","print(df.info())\n","\n","# Cetak jumlah missing value di setiap kolom\n","mv = df.isna().sum()\n","print(\"\\nJumlah missing value per kolom:\")\n","print(mv)"],"execution_count":null,"outputs":[{"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"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"jGS7S-Tge4Se","colab_type":"text"},"source":["**Treatment untuk Missing Value - Part 2**"]},{"cell_type":"code","metadata":{"id":"toc23UObdmVm","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":68},"executionInfo":{"status":"ok","timestamp":1595525064424,"user_tz":-420,"elapsed":2198,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"2cbf5466-66fa-4b4e-c28e-55373b88fa2f"},"source":["import pandas as pd\n","\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","\n","# Cetak ukuran awal dataframe\n","print(\"Ukuran awal df: %d baris, %d kolom.\" % df.shape)\n","\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","\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)"],"execution_count":null,"outputs":[{"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"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"e8NMiK4KfRqK","colab_type":"text"},"source":["**Treatment untuk Missing Value - Part 3**"]},{"cell_type":"code","metadata":{"id":"kwoz8ic9fSas","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":85},"executionInfo":{"status":"ok","timestamp":1595525069158,"user_tz":-420,"elapsed":2285,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"81e5dc63-8dd9-441a-cde0-3e227beaaea6"},"source":["import pandas as pd\n","\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","\n","# Cetak unique value pada kolom province_state\n","print(\"Unique value awal:\")\n","print(df[\"province_state\"].unique())\n","\n","# Ganti missing value dengan string \"unknown_province_state\"\n","df[\"province_state\"] = df[\"province_state\"].fillna(\"unknown_province_state\")\n","\n","# Cetak kembali unique value pada kolom province_state\n","print(\"Unique value setelah fillna:\")\n","print(df[\"province_state\"].unique())"],"execution_count":null,"outputs":[{"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"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"9mpAAJIifoNa","colab_type":"text"},"source":["**Treatment untuk Missing Value - Part 4**"]},{"cell_type":"code","metadata":{"id":"3T_dEoBHfo32","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":68},"executionInfo":{"status":"ok","timestamp":1595525074273,"user_tz":-420,"elapsed":2175,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"240815ff-55e9-43f6-c5e0-1be81db2493f"},"source":["import pandas as pd\n","\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","\n","# Cetak nilai mean dan median awal\n","print(\"Awal: mean = %f, median = %f.\" % (df[\"active\"].mean(), df[\"active\"].median()))\n","\n","# Isi missing value kolom active dengan median\n","df_median = df[\"active\"].fillna(df[\"active\"].median())\n","\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","\n","# Isi missing value kolom active dengan mean\n","df_mean = df[\"active\"].fillna(df[\"active\"].mean())\n","\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()))"],"execution_count":null,"outputs":[{"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"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"LkJZ_7Quf32b","colab_type":"text"},"source":["**Treatment untuk Missing Value - Part 5**"]},{"cell_type":"code","metadata":{"id":"WGLzAvdxf405","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":289},"executionInfo":{"status":"ok","timestamp":1595525076469,"user_tz":-420,"elapsed":774,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"d4ae1477-07ff-41b1-baaf-bce8841c233c"},"source":["import numpy as np\n","import pandas as pd\n","\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","\n","# Isi missing value menggunakan interpolasi linier\n","ts = ts.interpolate()\n","\n","# Cetak time series setelah interpolasi linier\n","print(\"Setelah diisi missing valuenya:\")\n","print(ts)"],"execution_count":null,"outputs":[{"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"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"R4vVMZQMgITP","colab_type":"text"},"source":["### **Mini Project**"]},{"cell_type":"code","metadata":{"id":"W-TPpZKJdmdV","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":1000},"executionInfo":{"status":"ok","timestamp":1595525090048,"user_tz":-420,"elapsed":2906,"user":{"displayName":"Sermon Paskah Zagoto","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GiVlpSEE7CTptyyLOF0HBAyiJig8SaCaXCH-pm1kw=s64","userId":"07646999100105069157"}},"outputId":"b61d4dd1-1129-4eda-c9cd-694095b113d0"},"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","\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","\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","\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","\n","# END OF PROJECT"],"execution_count":null,"outputs":[{"output_type":"stream","text":["[1] BACA DATASET\n","    Dataset:\n","    order_id    order_date customer_id  ... quantity item_price product_value\n","0   1730350  Dec 11, 2019      '13447  ...      '24    '113000        1374.0\n","1   1677490  Jul 31, 2019          '0  ...       '1   '1164000        1370.0\n","2   1704211  Oct 18, 2019      '16128  ...      '12    '747000        1679.0\n","3   1679695  Aug 07, 2019      '16225  ...       '6    '590000        1708.0\n","4   1679080  Aug 05, 2019          '0  ...       '2    '740000        1201.0\n","\n","[5 rows x 9 columns]\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  ... quantity item_price product_id\n","0   1730350  Dec 11, 2019        13447  ...       24     113000      P1374\n","1   1677490  Jul 31, 2019            0  ...        1    1164000      P1370\n","2   1704211  Oct 18, 2019        16128  ...       12     747000      P1679\n","3   1679695  Aug 07, 2019        16225  ...        6     590000      P1708\n","4   1679080  Aug 05, 2019            0  ...        2     740000      P1201\n","\n","[5 rows x 9 columns]\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  ...  product_id              city/province\n","0   1730350 2019-12-11  ...       P1374      Surakarta/Jawa Tengah\n","1   1677490 2019-07-31  ...       P1370            unknown/unknown\n","2   1704211 2019-10-18  ...       P1679  Jakarta Pusat/DKI Jakarta\n","3   1679695 2019-08-07  ...       P1708      Yogyakarta/Yogyakarta\n","4   1679080 2019-08-05  ...       P1201            unknown/unknown\n","\n","[5 rows x 8 columns]\n","\n","[7] MEMBUAT HIERACHICAL INDEX\n","                                                                     brand  ...  item_price\n","city/province          order_date customer_id order_id product_id           ...            \n","Banda Aceh/Aceh        2019-04-17 12818       1642480  P1936       BRAND_K  ...      450000\n","                       2019-11-12 12360       1715116  P0758       BRAND_C  ...      695000\n","                                                       P3042       BRAND_R  ...      310000\n","                       2019-12-09 12374       1729036  P1660       BRAND_G  ...     2795000\n","Bandar Lampung/Lampung 2019-01-15 12515       1619257  P0628       BRAND_C  ...      695000\n","\n","[5 rows x 3 columns]\n","\n","[8] MEMBUAT KOLOM total_price\n","                                                                     brand  ...  total_price\n","city/province          order_date customer_id order_id product_id           ...             \n","Banda Aceh/Aceh        2019-04-17 12818       1642480  P1936       BRAND_K  ...     10800000\n","                       2019-11-12 12360       1715116  P0758       BRAND_C  ...      5560000\n","                                                       P3042       BRAND_R  ...      3720000\n","                       2019-12-09 12374       1729036  P1660       BRAND_G  ...     11180000\n","Bandar Lampung/Lampung 2019-01-15 12515       1619257  P0628       BRAND_C  ...      8340000\n","\n","[5 rows x 4 columns]\n","\n","[9] SLICE DATASET UNTUK BULAN JANUARI 2019 SAJA\n","Dataset akhir:\n","                                                                      brand  ...  total_price\n","city/province          order_date customer_id order_id product_id           ...             \n","Bandar Lampung/Lampung 2019-01-15 12515       1619257  P0628       BRAND_C  ...      8340000\n","Bandung/Jawa Barat     2019-01-09 16134       1617055  P1597       BRAND_G  ...      4680000\n","                       2019-01-10 17392       1617952  P2137       BRAND_M  ...      2124000\n","                       2019-01-14 15527       1618828  P3115       BRAND_S  ...      1045000\n","                       2019-01-29 13253       1620289  P0099       BRAND_A  ...      5400000\n","...                                                                    ...  ...          ...\n","unknown/unknown        2019-01-30 0           1620766  P3070       BRAND_R  ...       593000\n","                                                       P3483       BRAND_S  ...      1779000\n","                       2019-01-31 0           1621057  P1298       BRAND_F  ...       296000\n","                                                       P1773       BRAND_H  ...      2965000\n","                                                       P2877       BRAND_R  ...      1486000\n","\n","[334 rows x 4 columns]\n"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"eG8j0f3G0SFF","colab_type":"text"},"source":["**Kesimpulan**"]},{"cell_type":"markdown","metadata":{"id":"j7KmGkAM0T-G","colab_type":"text"},"source":["1. Memahami library Pandas dan interaksinya dengan numpy\n","2. Memahami dan mempraktekkkan bagaimana membuat series dan dataframe pada pandas dari berbagai tipe data seperti list, list of list, dict, ataupun numpy array\n","3. Memahami dan mempraktekkkan bagaimana membaca dataset dari berbagai format standar seperti csv, tsc, excel, json, sql sehingga dapat dijadikan pandas dataframe/series serta bagaimana cara menyimpannya ke format standar dataset.\n","4. Mampu memahami dan mempraktekkan proses indexing, transformasi dan slicing pada dataframe\n","5. Mampu memahami dan mempraktekkan bagaimana cara menghandle missing value pada suatu dataframe\n","6. Latihan dalam mengerjakan project bisnis sederhana menggunakan pandas"]}]}