{ "cells": [ { "cell_type": "markdown", "id": "36fae451", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "# Reading and Cleaning Excel Files\n", "\n", "Feng Li\n", "\n", "School of Statistics and Mathematics\n", "\n", "Central University of Finance and Economics\n", "\n", "[feng.li@cufe.edu.cn](mailto:feng.li@cufe.edu.cn)\n", "\n", "[https://feng.li/python](https://feng.li/python)" ] }, { "cell_type": "markdown", "id": "b6782d1b", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "A company’s general ledger is rich with information: revenues, expenses, balances, adjustments are all recorded in the GL. However, as rich as it is with information, the general ledger is often tricky to work with: its format and organization make turning records into insights far more complicated than it needs to be.\n", "\n", "This section shows you how to use the `pandas` tools we’ve covered so far to clean and reformat a general ledger exported from QuickBooks. At the end, you’ll have a clean general ledger DataFrame that is easy to slice, filter, or handle in any way." ] }, { "cell_type": "markdown", "id": "engaged-capability", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "\n", "### Reading and writing Excel files" ] }, { "cell_type": "code", "execution_count": 1, "id": "indirect-deviation", "metadata": { "slideshow": { "slide_type": "fragment" }, "tags": [] }, "outputs": [], "source": [ "import pandas as pd" ] }, { "cell_type": "code", "execution_count": 2, "id": "bb35551b", "metadata": { "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "data": { "text/plain": [ "['January', 'February', 'March']" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df_info = pd.ExcelFile('data/Q1Sales.xlsx')\n", "ledger_df_info.sheet_names" ] }, { "cell_type": "code", "execution_count": 3, "id": "orange-conference", "metadata": { "slideshow": { "slide_type": "fragment" }, "tags": [] }, "outputs": [], "source": [ "ledger_df = pd.read_excel('data/Q1Sales.xlsx') # read the first sheet" ] }, { "cell_type": "code", "execution_count": 4, "id": "wooden-winner", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
InvoiceNoChannelProduct NameProductIDAccountAccountNoDateDeadlineCurrencyUnit PriceQuantityTotal
01532Shoppe.comCannon Water Bomb Balloons 100 PackT&G/CAN-97509Sales50042020-01-0111/23/19USD20.1114281.54
11533WalcartLEGO Ninja Turtles Stealth Shell in Pursuit 79102T&G/LEG-37777Sales50042020-01-0106/15/20USD6.7016.70
21534BullseyeNaNT&G/PET-14209Sales50042020-01-0105/07/20USD11.67558.35
31535BullseyeTransformers Age of Extinction Generations Del...T&G/TRA-20170Sales50042020-01-0112/22/19USD13.46680.76
41535BullseyeTransformers Age of Extinction Generations Del...T&G/TRA-20170Sales50042020-01-0112/22/19USD13.46680.76
.......................................
1404915581BullseyeAC Adapter/Power Supply&Cord for Lenovo 3000 G...E/AC-63975Sales50042020-01-31February 23 2020USD28.728229.76
1405015582BullseyeCisco Systems Gigabit VPN Router (RV320K9NA)E/CIS-74992Sales50042020-01-31January 21 2020USD33.39133.39
1405115583Understock.comPhilips AJ3116M/37 Digital Tuning Clock Radio ...E/PHI-08100Sales50042020-01-31March 22 2020USD4.1814.18
1405215584iBay.comNaNE/POL-61164Sales50042020-01-31June 25 2020USD4.7825119.50
1405315585Understock.comSirius Satellite Radio XADH2 Home Access Kit f...E/SIR-83381Sales50042020-01-31February 01 2020USD33.16266.32
\n", "

14054 rows × 12 columns

\n", "
" ], "text/plain": [ " InvoiceNo Channel \\\n", "0 1532 Shoppe.com \n", "1 1533 Walcart \n", "2 1534 Bullseye \n", "3 1535 Bullseye \n", "4 1535 Bullseye \n", "... ... ... \n", "14049 15581 Bullseye \n", "14050 15582 Bullseye \n", "14051 15583 Understock.com \n", "14052 15584 iBay.com \n", "14053 15585 Understock.com \n", "\n", " Product Name ProductID \\\n", "0 Cannon Water Bomb Balloons 100 Pack T&G/CAN-97509 \n", "1 LEGO Ninja Turtles Stealth Shell in Pursuit 79102 T&G/LEG-37777 \n", "2 NaN T&G/PET-14209 \n", "3 Transformers Age of Extinction Generations Del... T&G/TRA-20170 \n", "4 Transformers Age of Extinction Generations Del... T&G/TRA-20170 \n", "... ... ... \n", "14049 AC Adapter/Power Supply&Cord for Lenovo 3000 G... E/AC-63975 \n", "14050 Cisco Systems Gigabit VPN Router (RV320K9NA) E/CIS-74992 \n", "14051 Philips AJ3116M/37 Digital Tuning Clock Radio ... E/PHI-08100 \n", "14052 NaN E/POL-61164 \n", "14053 Sirius Satellite Radio XADH2 Home Access Kit f... E/SIR-83381 \n", "\n", " Account AccountNo Date Deadline Currency Unit Price \\\n", "0 Sales 5004 2020-01-01 11/23/19 USD 20.11 \n", "1 Sales 5004 2020-01-01 06/15/20 USD 6.70 \n", "2 Sales 5004 2020-01-01 05/07/20 USD 11.67 \n", "3 Sales 5004 2020-01-01 12/22/19 USD 13.46 \n", "4 Sales 5004 2020-01-01 12/22/19 USD 13.46 \n", "... ... ... ... ... ... ... \n", "14049 Sales 5004 2020-01-31 February 23 2020 USD 28.72 \n", "14050 Sales 5004 2020-01-31 January 21 2020 USD 33.39 \n", "14051 Sales 5004 2020-01-31 March 22 2020 USD 4.18 \n", "14052 Sales 5004 2020-01-31 June 25 2020 USD 4.78 \n", "14053 Sales 5004 2020-01-31 February 01 2020 USD 33.16 \n", "\n", " Quantity Total \n", "0 14 281.54 \n", "1 1 6.70 \n", "2 5 58.35 \n", "3 6 80.76 \n", "4 6 80.76 \n", "... ... ... \n", "14049 8 229.76 \n", "14050 1 33.39 \n", "14051 1 4.18 \n", "14052 25 119.50 \n", "14053 2 66.32 \n", "\n", "[14054 rows x 12 columns]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df" ] }, { "cell_type": "markdown", "id": "8492e863", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "- We could read a specified sheet of the Excel file" ] }, { "cell_type": "code", "execution_count": 5, "id": "corrected-intent", "metadata": { "slideshow": { "slide_type": "fragment" }, "tags": [] }, "outputs": [], "source": [ "ledger_df = pd.read_excel('data/Q1Sales.xlsx', sheet_name='March')" ] }, { "cell_type": "code", "execution_count": 6, "id": "suited-iraqi", "metadata": { "slideshow": { "slide_type": "fragment" }, "tags": [] }, "outputs": [], "source": [ "jan_ledger_df = pd.read_excel('data/Q1Sales.xlsx')\n", "feb_ledger_df = pd.read_excel('data/Q1Sales.xlsx', sheet_name='February')\n", "mar_ledger_df = pd.read_excel('data/Q1Sales.xlsx', sheet_name='March')" ] }, { "cell_type": "code", "execution_count": 9, "id": "boxed-polish", "metadata": { "slideshow": { "slide_type": "fragment" }, "tags": [] }, "outputs": [], "source": [ "ledger_df = pd.read_excel('data/Q1Sales.xlsx', sheet_name=0)" ] }, { "cell_type": "markdown", "id": "classical-reducing", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "- Let's inspect the data" ] }, { "cell_type": "code", "execution_count": 10, "id": "sensitive-rover", "metadata": { "slideshow": { "slide_type": "fragment" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
InvoiceNoChannelProduct NameProductIDAccountAccountNoDateDeadlineCurrencyUnit PriceQuantityTotal
01532Shoppe.comCannon Water Bomb Balloons 100 PackT&G/CAN-97509Sales50042020-01-0111/23/19USD20.1114281.54
11533WalcartLEGO Ninja Turtles Stealth Shell in Pursuit 79102T&G/LEG-37777Sales50042020-01-0106/15/20USD6.7016.70
21534BullseyeNaNT&G/PET-14209Sales50042020-01-0105/07/20USD11.67558.35
31535BullseyeTransformers Age of Extinction Generations Del...T&G/TRA-20170Sales50042020-01-0112/22/19USD13.46680.76
41535BullseyeTransformers Age of Extinction Generations Del...T&G/TRA-20170Sales50042020-01-0112/22/19USD13.46680.76
.......................................
1404915581BullseyeAC Adapter/Power Supply&Cord for Lenovo 3000 G...E/AC-63975Sales50042020-01-31February 23 2020USD28.728229.76
1405015582BullseyeCisco Systems Gigabit VPN Router (RV320K9NA)E/CIS-74992Sales50042020-01-31January 21 2020USD33.39133.39
1405115583Understock.comPhilips AJ3116M/37 Digital Tuning Clock Radio ...E/PHI-08100Sales50042020-01-31March 22 2020USD4.1814.18
1405215584iBay.comNaNE/POL-61164Sales50042020-01-31June 25 2020USD4.7825119.50
1405315585Understock.comSirius Satellite Radio XADH2 Home Access Kit f...E/SIR-83381Sales50042020-01-31February 01 2020USD33.16266.32
\n", "

14054 rows × 12 columns

\n", "
" ], "text/plain": [ " InvoiceNo Channel \\\n", "0 1532 Shoppe.com \n", "1 1533 Walcart \n", "2 1534 Bullseye \n", "3 1535 Bullseye \n", "4 1535 Bullseye \n", "... ... ... \n", "14049 15581 Bullseye \n", "14050 15582 Bullseye \n", "14051 15583 Understock.com \n", "14052 15584 iBay.com \n", "14053 15585 Understock.com \n", "\n", " Product Name ProductID \\\n", "0 Cannon Water Bomb Balloons 100 Pack T&G/CAN-97509 \n", "1 LEGO Ninja Turtles Stealth Shell in Pursuit 79102 T&G/LEG-37777 \n", "2 NaN T&G/PET-14209 \n", "3 Transformers Age of Extinction Generations Del... T&G/TRA-20170 \n", "4 Transformers Age of Extinction Generations Del... T&G/TRA-20170 \n", "... ... ... \n", "14049 AC Adapter/Power Supply&Cord for Lenovo 3000 G... E/AC-63975 \n", "14050 Cisco Systems Gigabit VPN Router (RV320K9NA) E/CIS-74992 \n", "14051 Philips AJ3116M/37 Digital Tuning Clock Radio ... E/PHI-08100 \n", "14052 NaN E/POL-61164 \n", "14053 Sirius Satellite Radio XADH2 Home Access Kit f... E/SIR-83381 \n", "\n", " Account AccountNo Date Deadline Currency Unit Price \\\n", "0 Sales 5004 2020-01-01 11/23/19 USD 20.11 \n", "1 Sales 5004 2020-01-01 06/15/20 USD 6.70 \n", "2 Sales 5004 2020-01-01 05/07/20 USD 11.67 \n", "3 Sales 5004 2020-01-01 12/22/19 USD 13.46 \n", "4 Sales 5004 2020-01-01 12/22/19 USD 13.46 \n", "... ... ... ... ... ... ... \n", "14049 Sales 5004 2020-01-31 February 23 2020 USD 28.72 \n", "14050 Sales 5004 2020-01-31 January 21 2020 USD 33.39 \n", "14051 Sales 5004 2020-01-31 March 22 2020 USD 4.18 \n", "14052 Sales 5004 2020-01-31 June 25 2020 USD 4.78 \n", "14053 Sales 5004 2020-01-31 February 01 2020 USD 33.16 \n", "\n", " Quantity Total \n", "0 14 281.54 \n", "1 1 6.70 \n", "2 5 58.35 \n", "3 6 80.76 \n", "4 6 80.76 \n", "... ... ... \n", "14049 8 229.76 \n", "14050 1 33.39 \n", "14051 1 4.18 \n", "14052 25 119.50 \n", "14053 2 66.32 \n", "\n", "[14054 rows x 12 columns]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df" ] }, { "cell_type": "code", "execution_count": 11, "id": "consolidated-print", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
InvoiceNoChannelProduct NameProductIDAccountAccountNoDateDeadlineCurrencyUnit PriceQuantityTotal
01532Shoppe.comCannon Water Bomb Balloons 100 PackT&G/CAN-97509Sales50042020-01-0111/23/19USD20.1114281.54
11533WalcartLEGO Ninja Turtles Stealth Shell in Pursuit 79102T&G/LEG-37777Sales50042020-01-0106/15/20USD6.7016.70
21534BullseyeNaNT&G/PET-14209Sales50042020-01-0105/07/20USD11.67558.35
\n", "
" ], "text/plain": [ " InvoiceNo Channel Product Name \\\n", "0 1532 Shoppe.com Cannon Water Bomb Balloons 100 Pack \n", "1 1533 Walcart LEGO Ninja Turtles Stealth Shell in Pursuit 79102 \n", "2 1534 Bullseye NaN \n", "\n", " ProductID Account AccountNo Date Deadline Currency Unit Price \\\n", "0 T&G/CAN-97509 Sales 5004 2020-01-01 11/23/19 USD 20.11 \n", "1 T&G/LEG-37777 Sales 5004 2020-01-01 06/15/20 USD 6.70 \n", "2 T&G/PET-14209 Sales 5004 2020-01-01 05/07/20 USD 11.67 \n", "\n", " Quantity Total \n", "0 14 281.54 \n", "1 1 6.70 \n", "2 5 58.35 " ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df.head(3)" ] }, { "cell_type": "code", "execution_count": 12, "id": "metallic-nightlife", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
InvoiceNoChannelProduct NameProductIDAccountAccountNoDateDeadlineCurrencyUnit PriceQuantityTotal
1405115583Understock.comPhilips AJ3116M/37 Digital Tuning Clock Radio ...E/PHI-08100Sales50042020-01-31March 22 2020USD4.1814.18
1405215584iBay.comNaNE/POL-61164Sales50042020-01-31June 25 2020USD4.7825119.50
1405315585Understock.comSirius Satellite Radio XADH2 Home Access Kit f...E/SIR-83381Sales50042020-01-31February 01 2020USD33.16266.32
\n", "
" ], "text/plain": [ " InvoiceNo Channel \\\n", "14051 15583 Understock.com \n", "14052 15584 iBay.com \n", "14053 15585 Understock.com \n", "\n", " Product Name ProductID Account \\\n", "14051 Philips AJ3116M/37 Digital Tuning Clock Radio ... E/PHI-08100 Sales \n", "14052 NaN E/POL-61164 Sales \n", "14053 Sirius Satellite Radio XADH2 Home Access Kit f... E/SIR-83381 Sales \n", "\n", " AccountNo Date Deadline Currency Unit Price Quantity \\\n", "14051 5004 2020-01-31 March 22 2020 USD 4.18 1 \n", "14052 5004 2020-01-31 June 25 2020 USD 4.78 25 \n", "14053 5004 2020-01-31 February 01 2020 USD 33.16 2 \n", "\n", " Total \n", "14051 4.18 \n", "14052 119.50 \n", "14053 66.32 " ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df.tail(3)" ] }, { "cell_type": "code", "execution_count": 13, "id": "preliminary-increase", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "RangeIndex: 14054 entries, 0 to 14053\n", "Data columns (total 12 columns):\n", " # Column Non-Null Count Dtype \n", "--- ------ -------------- ----- \n", " 0 InvoiceNo 14054 non-null int64 \n", " 1 Channel 14054 non-null object \n", " 2 Product Name 12362 non-null object \n", " 3 ProductID 14054 non-null object \n", " 4 Account 14054 non-null object \n", " 5 AccountNo 14054 non-null int64 \n", " 6 Date 14054 non-null datetime64[ns]\n", " 7 Deadline 14054 non-null object \n", " 8 Currency 14054 non-null object \n", " 9 Unit Price 14054 non-null float64 \n", " 10 Quantity 14054 non-null int64 \n", " 11 Total 14054 non-null float64 \n", "dtypes: datetime64[ns](1), float64(2), int64(3), object(6)\n", "memory usage: 1.3+ MB\n" ] } ], "source": [ "ledger_df.info()" ] }, { "cell_type": "code", "execution_count": 14, "id": "controlling-depth", "metadata": { "slideshow": { "slide_type": "fragment" }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "(14054, 12)" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df.shape" ] }, { "cell_type": "markdown", "id": "alert-science", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "## Work with Tables" ] }, { "cell_type": "code", "execution_count": 15, "id": "experimental-letters", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "Index(['InvoiceNo', 'Channel', 'Product Name', 'ProductID', 'Account',\n", " 'AccountNo', 'Date', 'Deadline', 'Currency', 'Unit Price', 'Quantity',\n", " 'Total'],\n", " dtype='object')" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df.columns" ] }, { "cell_type": "code", "execution_count": 16, "id": "major-technology", "metadata": { "slideshow": { "slide_type": "fragment" }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "0 Cannon Water Bomb Balloons 100 Pack\n", "1 LEGO Ninja Turtles Stealth Shell in Pursuit 79102\n", "2 NaN\n", "3 Transformers Age of Extinction Generations Del...\n", "4 Transformers Age of Extinction Generations Del...\n", " ... \n", "14049 AC Adapter/Power Supply&Cord for Lenovo 3000 G...\n", "14050 Cisco Systems Gigabit VPN Router (RV320K9NA)\n", "14051 Philips AJ3116M/37 Digital Tuning Clock Radio ...\n", "14052 NaN\n", "14053 Sirius Satellite Radio XADH2 Home Access Kit f...\n", "Name: Product Name, Length: 14054, dtype: object" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df['Product Name']" ] }, { "cell_type": "code", "execution_count": 17, "id": "pointed-client", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "0 20.11\n", "1 6.70\n", "2 11.67\n", "3 13.46\n", "4 13.46\n", " ... \n", "14049 28.72\n", "14050 33.39\n", "14051 4.18\n", "14052 4.78\n", "14053 33.16\n", "Name: Unit Price, Length: 14054, dtype: float64" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df['Unit Price']" ] }, { "cell_type": "code", "execution_count": 18, "id": "yellow-routine", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [ "allowerror" ] }, "outputs": [ { "data": { "text/plain": [ "0 20.11\n", "1 6.70\n", "2 11.67\n", "3 13.46\n", "4 13.46\n", " ... \n", "14049 28.72\n", "14050 33.39\n", "14051 4.18\n", "14052 4.78\n", "14053 33.16\n", "Name: Unit Price, Length: 14054, dtype: float64" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df['Unit Price']" ] }, { "cell_type": "code", "execution_count": 19, "id": "iraqi-nursery", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "10.22" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df['Unit Price'].median()" ] }, { "cell_type": "code", "execution_count": 20, "id": "caroline-momentum", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ProductIDProduct NameUnit PriceTotal
0T&G/CAN-97509Cannon Water Bomb Balloons 100 Pack20.11281.54
1T&G/LEG-37777LEGO Ninja Turtles Stealth Shell in Pursuit 791026.706.70
2T&G/PET-14209NaN11.6758.35
3T&G/TRA-20170Transformers Age of Extinction Generations Del...13.4680.76
4T&G/TRA-20170Transformers Age of Extinction Generations Del...13.4680.76
...............
14049E/AC-63975AC Adapter/Power Supply&Cord for Lenovo 3000 G...28.72229.76
14050E/CIS-74992Cisco Systems Gigabit VPN Router (RV320K9NA)33.3933.39
14051E/PHI-08100Philips AJ3116M/37 Digital Tuning Clock Radio ...4.184.18
14052E/POL-61164NaN4.78119.50
14053E/SIR-83381Sirius Satellite Radio XADH2 Home Access Kit f...33.1666.32
\n", "

14054 rows × 4 columns

\n", "
" ], "text/plain": [ " ProductID Product Name \\\n", "0 T&G/CAN-97509 Cannon Water Bomb Balloons 100 Pack \n", "1 T&G/LEG-37777 LEGO Ninja Turtles Stealth Shell in Pursuit 79102 \n", "2 T&G/PET-14209 NaN \n", "3 T&G/TRA-20170 Transformers Age of Extinction Generations Del... \n", "4 T&G/TRA-20170 Transformers Age of Extinction Generations Del... \n", "... ... ... \n", "14049 E/AC-63975 AC Adapter/Power Supply&Cord for Lenovo 3000 G... \n", "14050 E/CIS-74992 Cisco Systems Gigabit VPN Router (RV320K9NA) \n", "14051 E/PHI-08100 Philips AJ3116M/37 Digital Tuning Clock Radio ... \n", "14052 E/POL-61164 NaN \n", "14053 E/SIR-83381 Sirius Satellite Radio XADH2 Home Access Kit f... \n", "\n", " Unit Price Total \n", "0 20.11 281.54 \n", "1 6.70 6.70 \n", "2 11.67 58.35 \n", "3 13.46 80.76 \n", "4 13.46 80.76 \n", "... ... ... \n", "14049 28.72 229.76 \n", "14050 33.39 33.39 \n", "14051 4.18 4.18 \n", "14052 4.78 119.50 \n", "14053 33.16 66.32 \n", "\n", "[14054 rows x 4 columns]" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df[['ProductID', 'Product Name', 'Unit Price', 'Total']]" ] }, { "cell_type": "code", "execution_count": 21, "id": "rural-straight", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ProductIDProduct NameUnit PriceTotal
0T&G/CAN-97509Cannon Water Bomb Balloons 100 Pack20.11281.54
1T&G/LEG-37777LEGO Ninja Turtles Stealth Shell in Pursuit 791026.706.70
2T&G/PET-14209NaN11.6758.35
3T&G/TRA-20170Transformers Age of Extinction Generations Del...13.4680.76
4T&G/TRA-20170Transformers Age of Extinction Generations Del...13.4680.76
...............
14049E/AC-63975AC Adapter/Power Supply&Cord for Lenovo 3000 G...28.72229.76
14050E/CIS-74992Cisco Systems Gigabit VPN Router (RV320K9NA)33.3933.39
14051E/PHI-08100Philips AJ3116M/37 Digital Tuning Clock Radio ...4.184.18
14052E/POL-61164NaN4.78119.50
14053E/SIR-83381Sirius Satellite Radio XADH2 Home Access Kit f...33.1666.32
\n", "

14054 rows × 4 columns

\n", "
" ], "text/plain": [ " ProductID Product Name \\\n", "0 T&G/CAN-97509 Cannon Water Bomb Balloons 100 Pack \n", "1 T&G/LEG-37777 LEGO Ninja Turtles Stealth Shell in Pursuit 79102 \n", "2 T&G/PET-14209 NaN \n", "3 T&G/TRA-20170 Transformers Age of Extinction Generations Del... \n", "4 T&G/TRA-20170 Transformers Age of Extinction Generations Del... \n", "... ... ... \n", "14049 E/AC-63975 AC Adapter/Power Supply&Cord for Lenovo 3000 G... \n", "14050 E/CIS-74992 Cisco Systems Gigabit VPN Router (RV320K9NA) \n", "14051 E/PHI-08100 Philips AJ3116M/37 Digital Tuning Clock Radio ... \n", "14052 E/POL-61164 NaN \n", "14053 E/SIR-83381 Sirius Satellite Radio XADH2 Home Access Kit f... \n", "\n", " Unit Price Total \n", "0 20.11 281.54 \n", "1 6.70 6.70 \n", "2 11.67 58.35 \n", "3 13.46 80.76 \n", "4 13.46 80.76 \n", "... ... ... \n", "14049 28.72 229.76 \n", "14050 33.39 33.39 \n", "14051 4.18 4.18 \n", "14052 4.78 119.50 \n", "14053 33.16 66.32 \n", "\n", "[14054 rows x 4 columns]" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "column_names = ['ProductID', 'Product Name', 'Unit Price', 'Total']\n", "\n", "ledger_df[column_names]" ] }, { "cell_type": "code", "execution_count": 22, "id": "enclosed-enhancement", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ProductIDProduct NameUnit PriceTotal
0T&G/CAN-97509Cannon Water Bomb Balloons 100 Pack20.11281.54
1T&G/LEG-37777LEGO Ninja Turtles Stealth Shell in Pursuit 791026.706.70
2T&G/PET-14209NaN11.6758.35
3T&G/TRA-20170Transformers Age of Extinction Generations Del...13.4680.76
4T&G/TRA-20170Transformers Age of Extinction Generations Del...13.4680.76
...............
14049E/AC-63975AC Adapter/Power Supply&Cord for Lenovo 3000 G...28.72229.76
14050E/CIS-74992Cisco Systems Gigabit VPN Router (RV320K9NA)33.3933.39
14051E/PHI-08100Philips AJ3116M/37 Digital Tuning Clock Radio ...4.184.18
14052E/POL-61164NaN4.78119.50
14053E/SIR-83381Sirius Satellite Radio XADH2 Home Access Kit f...33.1666.32
\n", "

14054 rows × 4 columns

\n", "
" ], "text/plain": [ " ProductID Product Name \\\n", "0 T&G/CAN-97509 Cannon Water Bomb Balloons 100 Pack \n", "1 T&G/LEG-37777 LEGO Ninja Turtles Stealth Shell in Pursuit 79102 \n", "2 T&G/PET-14209 NaN \n", "3 T&G/TRA-20170 Transformers Age of Extinction Generations Del... \n", "4 T&G/TRA-20170 Transformers Age of Extinction Generations Del... \n", "... ... ... \n", "14049 E/AC-63975 AC Adapter/Power Supply&Cord for Lenovo 3000 G... \n", "14050 E/CIS-74992 Cisco Systems Gigabit VPN Router (RV320K9NA) \n", "14051 E/PHI-08100 Philips AJ3116M/37 Digital Tuning Clock Radio ... \n", "14052 E/POL-61164 NaN \n", "14053 E/SIR-83381 Sirius Satellite Radio XADH2 Home Access Kit f... \n", "\n", " Unit Price Total \n", "0 20.11 281.54 \n", "1 6.70 6.70 \n", "2 11.67 58.35 \n", "3 13.46 80.76 \n", "4 13.46 80.76 \n", "... ... ... \n", "14049 28.72 229.76 \n", "14050 33.39 33.39 \n", "14051 4.18 4.18 \n", "14052 4.78 119.50 \n", "14053 33.16 66.32 \n", "\n", "[14054 rows x 4 columns]" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "products_df = ledger_df[['ProductID', 'Product Name', 'Unit Price', 'Total']]\n", "\n", "products_df" ] }, { "cell_type": "code", "execution_count": 23, "id": "accessory-camping", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Product NameProductID
0Cannon Water Bomb Balloons 100 PackT&G/CAN-97509
1LEGO Ninja Turtles Stealth Shell in Pursuit 79102T&G/LEG-37777
2NaNT&G/PET-14209
3Transformers Age of Extinction Generations Del...T&G/TRA-20170
4Transformers Age of Extinction Generations Del...T&G/TRA-20170
.........
14049AC Adapter/Power Supply&Cord for Lenovo 3000 G...E/AC-63975
14050Cisco Systems Gigabit VPN Router (RV320K9NA)E/CIS-74992
14051Philips AJ3116M/37 Digital Tuning Clock Radio ...E/PHI-08100
14052NaNE/POL-61164
14053Sirius Satellite Radio XADH2 Home Access Kit f...E/SIR-83381
\n", "

14054 rows × 2 columns

\n", "
" ], "text/plain": [ " Product Name ProductID\n", "0 Cannon Water Bomb Balloons 100 Pack T&G/CAN-97509\n", "1 LEGO Ninja Turtles Stealth Shell in Pursuit 79102 T&G/LEG-37777\n", "2 NaN T&G/PET-14209\n", "3 Transformers Age of Extinction Generations Del... T&G/TRA-20170\n", "4 Transformers Age of Extinction Generations Del... T&G/TRA-20170\n", "... ... ...\n", "14049 AC Adapter/Power Supply&Cord for Lenovo 3000 G... E/AC-63975\n", "14050 Cisco Systems Gigabit VPN Router (RV320K9NA) E/CIS-74992\n", "14051 Philips AJ3116M/37 Digital Tuning Clock Radio ... E/PHI-08100\n", "14052 NaN E/POL-61164\n", "14053 Sirius Satellite Radio XADH2 Home Access Kit f... E/SIR-83381\n", "\n", "[14054 rows x 2 columns]" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df[[name for name in ledger_df.columns if 'Product' in name]]" ] }, { "cell_type": "markdown", "id": "dbdf576f", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "- We could remove a column from the table (Not the Excel)" ] }, { "cell_type": "code", "execution_count": 24, "id": "removable-stick", "metadata": { "slideshow": { "slide_type": "fragment" }, "tags": [] }, "outputs": [], "source": [ "products_df = ledger_df[['ProductID', 'Product Name', 'Unit Price', 'Total']]" ] }, { "cell_type": "code", "execution_count": 25, "id": "educational-camel", "metadata": { "slideshow": { "slide_type": "fragment" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ChannelProduct NameProductIDDateDeadlineUnit PriceQuantityTotal
0Shoppe.comCannon Water Bomb Balloons 100 PackT&G/CAN-975092020-01-0111/23/1920.1114281.54
1WalcartLEGO Ninja Turtles Stealth Shell in Pursuit 79102T&G/LEG-377772020-01-0106/15/206.7016.70
2BullseyeNaNT&G/PET-142092020-01-0105/07/2011.67558.35
3BullseyeTransformers Age of Extinction Generations Del...T&G/TRA-201702020-01-0112/22/1913.46680.76
4BullseyeTransformers Age of Extinction Generations Del...T&G/TRA-201702020-01-0112/22/1913.46680.76
...........................
14049BullseyeAC Adapter/Power Supply&Cord for Lenovo 3000 G...E/AC-639752020-01-31February 23 202028.728229.76
14050BullseyeCisco Systems Gigabit VPN Router (RV320K9NA)E/CIS-749922020-01-31January 21 202033.39133.39
14051Understock.comPhilips AJ3116M/37 Digital Tuning Clock Radio ...E/PHI-081002020-01-31March 22 20204.1814.18
14052iBay.comNaNE/POL-611642020-01-31June 25 20204.7825119.50
14053Understock.comSirius Satellite Radio XADH2 Home Access Kit f...E/SIR-833812020-01-31February 01 202033.16266.32
\n", "

14054 rows × 8 columns

\n", "
" ], "text/plain": [ " Channel Product Name \\\n", "0 Shoppe.com Cannon Water Bomb Balloons 100 Pack \n", "1 Walcart LEGO Ninja Turtles Stealth Shell in Pursuit 79102 \n", "2 Bullseye NaN \n", "3 Bullseye Transformers Age of Extinction Generations Del... \n", "4 Bullseye Transformers Age of Extinction Generations Del... \n", "... ... ... \n", "14049 Bullseye AC Adapter/Power Supply&Cord for Lenovo 3000 G... \n", "14050 Bullseye Cisco Systems Gigabit VPN Router (RV320K9NA) \n", "14051 Understock.com Philips AJ3116M/37 Digital Tuning Clock Radio ... \n", "14052 iBay.com NaN \n", "14053 Understock.com Sirius Satellite Radio XADH2 Home Access Kit f... \n", "\n", " ProductID Date Deadline Unit Price Quantity \\\n", "0 T&G/CAN-97509 2020-01-01 11/23/19 20.11 14 \n", "1 T&G/LEG-37777 2020-01-01 06/15/20 6.70 1 \n", "2 T&G/PET-14209 2020-01-01 05/07/20 11.67 5 \n", "3 T&G/TRA-20170 2020-01-01 12/22/19 13.46 6 \n", "4 T&G/TRA-20170 2020-01-01 12/22/19 13.46 6 \n", "... ... ... ... ... ... \n", "14049 E/AC-63975 2020-01-31 February 23 2020 28.72 8 \n", "14050 E/CIS-74992 2020-01-31 January 21 2020 33.39 1 \n", "14051 E/PHI-08100 2020-01-31 March 22 2020 4.18 1 \n", "14052 E/POL-61164 2020-01-31 June 25 2020 4.78 25 \n", "14053 E/SIR-83381 2020-01-31 February 01 2020 33.16 2 \n", "\n", " Total \n", "0 281.54 \n", "1 6.70 \n", "2 58.35 \n", "3 80.76 \n", "4 80.76 \n", "... ... \n", "14049 229.76 \n", "14050 33.39 \n", "14051 4.18 \n", "14052 119.50 \n", "14053 66.32 \n", "\n", "[14054 rows x 8 columns]" ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "columns_to_remove = ['InvoiceNo', 'Account', 'AccountNo', 'Currency']\n", "ledger_df.drop(columns_to_remove, axis='columns')" ] }, { "cell_type": "markdown", "id": "fatty-header", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "- Let's filter the data with given condition" ] }, { "cell_type": "code", "execution_count": 26, "id": "eastern-inspiration", "metadata": { "slideshow": { "slide_type": "fragment" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
InvoiceNoChannelProduct NameProductIDAccountAccountNoDateDeadlineCurrencyUnit PriceQuantityTotal
11533WalcartLEGO Ninja Turtles Stealth Shell in Pursuit 79102T&G/LEG-37777Sales50042020-01-0106/15/20USD6.7016.70
71539WalcartZen-Ray ED3 8x43 BinocularsC&P/ZEN-80993Sales50042020-01-016-28-20USD28.29128.29
101542WalcartLogisys Red 5 LED Lazer Light, 12V DC Input Vo...MI/LOG-93214Sales50042020-01-0105/25/20USD19.49119.49
111543WalcartMagline GMK81UA4 Gemini Sr Convertible Hand Tr...I&S/MAG-94877Sales50042020-01-0105/06/20USD18.42473.68
151547WalcartTotally Bamboo 20-8551 3-Tiered Salt BoxK&D/TOT-99233Sales50042020-01-01March 14 2020USD4.2514.25
.......................................
1401215408WalcartOXO Good Grips Large Silicone Drying MatH&K/OXO-69937Sales50042020-01-312020/02/09USD10.10110.10
1402215427WalcartApplied Nutrition Dual Action Cleanse Kit with...H&PC/APP-41898Sales50042020-01-31Wed May 13 00:00:00 2020USD7.7217.72
1402815459WalcartUpdate International WPP-1236 Rubber Wood Pizz...K&D/UPD-02237Sales50042020-01-31June 13 2020USD3.9113.91
1403415471WalcartKikkerland Biodegradable Paper Straws, Gray an...K&D/KIK-89065Sales50042020-01-31February 22 2020USD17.1816274.88
1403915499WalcartAnchor Hocking 4-Piece Palladian Cherry Window...K&D/ANC-42628Sales50042020-01-31April 22 2020USD7.5644332.64
\n", "

1851 rows × 12 columns

\n", "
" ], "text/plain": [ " InvoiceNo Channel Product Name \\\n", "1 1533 Walcart LEGO Ninja Turtles Stealth Shell in Pursuit 79102 \n", "7 1539 Walcart Zen-Ray ED3 8x43 Binoculars \n", "10 1542 Walcart Logisys Red 5 LED Lazer Light, 12V DC Input Vo... \n", "11 1543 Walcart Magline GMK81UA4 Gemini Sr Convertible Hand Tr... \n", "15 1547 Walcart Totally Bamboo 20-8551 3-Tiered Salt Box \n", "... ... ... ... \n", "14012 15408 Walcart OXO Good Grips Large Silicone Drying Mat \n", "14022 15427 Walcart Applied Nutrition Dual Action Cleanse Kit with... \n", "14028 15459 Walcart Update International WPP-1236 Rubber Wood Pizz... \n", "14034 15471 Walcart Kikkerland Biodegradable Paper Straws, Gray an... \n", "14039 15499 Walcart Anchor Hocking 4-Piece Palladian Cherry Window... \n", "\n", " ProductID Account AccountNo Date Deadline \\\n", "1 T&G/LEG-37777 Sales 5004 2020-01-01 06/15/20 \n", "7 C&P/ZEN-80993 Sales 5004 2020-01-01 6-28-20 \n", "10 MI/LOG-93214 Sales 5004 2020-01-01 05/25/20 \n", "11 I&S/MAG-94877 Sales 5004 2020-01-01 05/06/20 \n", "15 K&D/TOT-99233 Sales 5004 2020-01-01 March 14 2020 \n", "... ... ... ... ... ... \n", "14012 H&K/OXO-69937 Sales 5004 2020-01-31 2020/02/09 \n", "14022 H&PC/APP-41898 Sales 5004 2020-01-31 Wed May 13 00:00:00 2020 \n", "14028 K&D/UPD-02237 Sales 5004 2020-01-31 June 13 2020 \n", "14034 K&D/KIK-89065 Sales 5004 2020-01-31 February 22 2020 \n", "14039 K&D/ANC-42628 Sales 5004 2020-01-31 April 22 2020 \n", "\n", " Currency Unit Price Quantity Total \n", "1 USD 6.70 1 6.70 \n", "7 USD 28.29 1 28.29 \n", "10 USD 19.49 1 19.49 \n", "11 USD 18.42 4 73.68 \n", "15 USD 4.25 1 4.25 \n", "... ... ... ... ... \n", "14012 USD 10.10 1 10.10 \n", "14022 USD 7.72 1 7.72 \n", "14028 USD 3.91 1 3.91 \n", "14034 USD 17.18 16 274.88 \n", "14039 USD 7.56 44 332.64 \n", "\n", "[1851 rows x 12 columns]" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df[ledger_df['Channel'] == 'Walcart']" ] }, { "cell_type": "code", "execution_count": 27, "id": "driven-machinery", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
InvoiceNoChannelProduct NameProductIDAccountAccountNoDateDeadlineCurrencyUnit PriceQuantityTotal
281560WalcartConntek 14422 RV Pigtail Adapter 15 Amp Male P...PL&G/CON-18732Sales50042020-01-0103/26/20USD4.4727120.69
1441676Walcart[ Strip of 6 ] Energizer A76/LR44 (A76BP), SR4...I&S/[ S-49751Sales50042020-01-0102/04/20USD11.8361721.63
2131604WalcartConntek 14422 RV Pigtail Adapter 15 Amp Male P...PL&G/CON-18732Sales50042020-01-0103/26/20USD4.4727120.69
2371769WalcartChild Construction Hats - 12 Pack - YellowT&G/CHI-38293Sales50042020-01-013-31-20USD19.6843846.24
2921824WalcartUltima Replenisher Lemonade 90 - Servings , 1...H&PC/ULT-64807Sales50042020-01-01Thu Feb 27 00:00:00 2020USD13.3462827.08
.......................................
1375115144WalcartAnchor Hocking 4-Piece Palladian Cherry Window...K&D/ANC-42628Sales50042020-01-31April 22 2020USD7.5644332.64
1375915291WalcartAC Adapter/battery charger for GateWay Liteon ...E/AC-44106Sales50042020-01-312020/01/05USD14.4968985.32
1382715359WalcartAKG Pro Audio K99 Perception Over-Ear Semi-Ope...MI/AKG-26798Sales50042020-01-3102/16/20USD8.1031251.10
1389715429WalcartNaNH&PC/ULT-64807Sales50042020-01-31Wed Jun 24 00:00:00 2020USD13.34831107.22
1403915499WalcartAnchor Hocking 4-Piece Palladian Cherry Window...K&D/ANC-42628Sales50042020-01-31April 22 2020USD7.5644332.64
\n", "

86 rows × 12 columns

\n", "
" ], "text/plain": [ " InvoiceNo Channel Product Name \\\n", "28 1560 Walcart Conntek 14422 RV Pigtail Adapter 15 Amp Male P... \n", "144 1676 Walcart [ Strip of 6 ] Energizer A76/LR44 (A76BP), SR4... \n", "213 1604 Walcart Conntek 14422 RV Pigtail Adapter 15 Amp Male P... \n", "237 1769 Walcart Child Construction Hats - 12 Pack - Yellow \n", "292 1824 Walcart Ultima Replenisher Lemonade 90 - Servings , 1... \n", "... ... ... ... \n", "13751 15144 Walcart Anchor Hocking 4-Piece Palladian Cherry Window... \n", "13759 15291 Walcart AC Adapter/battery charger for GateWay Liteon ... \n", "13827 15359 Walcart AKG Pro Audio K99 Perception Over-Ear Semi-Ope... \n", "13897 15429 Walcart NaN \n", "14039 15499 Walcart Anchor Hocking 4-Piece Palladian Cherry Window... \n", "\n", " ProductID Account AccountNo Date Deadline \\\n", "28 PL&G/CON-18732 Sales 5004 2020-01-01 03/26/20 \n", "144 I&S/[ S-49751 Sales 5004 2020-01-01 02/04/20 \n", "213 PL&G/CON-18732 Sales 5004 2020-01-01 03/26/20 \n", "237 T&G/CHI-38293 Sales 5004 2020-01-01 3-31-20 \n", "292 H&PC/ULT-64807 Sales 5004 2020-01-01 Thu Feb 27 00:00:00 2020 \n", "... ... ... ... ... ... \n", "13751 K&D/ANC-42628 Sales 5004 2020-01-31 April 22 2020 \n", "13759 E/AC-44106 Sales 5004 2020-01-31 2020/01/05 \n", "13827 MI/AKG-26798 Sales 5004 2020-01-31 02/16/20 \n", "13897 H&PC/ULT-64807 Sales 5004 2020-01-31 Wed Jun 24 00:00:00 2020 \n", "14039 K&D/ANC-42628 Sales 5004 2020-01-31 April 22 2020 \n", "\n", " Currency Unit Price Quantity Total \n", "28 USD 4.47 27 120.69 \n", "144 USD 11.83 61 721.63 \n", "213 USD 4.47 27 120.69 \n", "237 USD 19.68 43 846.24 \n", "292 USD 13.34 62 827.08 \n", "... ... ... ... ... \n", "13751 USD 7.56 44 332.64 \n", "13759 USD 14.49 68 985.32 \n", "13827 USD 8.10 31 251.10 \n", "13897 USD 13.34 83 1107.22 \n", "14039 USD 7.56 44 332.64 \n", "\n", "[86 rows x 12 columns]" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df[\n", " (ledger_df['Channel'] == 'Walcart') &\n", " (ledger_df['Quantity'] > 25)\n", "]" ] }, { "cell_type": "code", "execution_count": 28, "id": "located-situation", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "0 True\n", "1 False\n", "2 False\n", "3 False\n", "4 False\n", " ... \n", "14049 False\n", "14050 False\n", "14051 True\n", "14052 True\n", "14053 True\n", "Name: Channel, Length: 14054, dtype: bool" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df['Channel'].isin(['Understock.com', 'iBay.com', 'Shoppe.com'])" ] }, { "cell_type": "code", "execution_count": 29, "id": "gross-aspect", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "0 True\n", "1 False\n", "2 False\n", "3 False\n", "4 False\n", " ... \n", "14049 False\n", "14050 False\n", "14051 False\n", "14052 True\n", "14053 False\n", "Name: Quantity, Length: 14054, dtype: bool" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df['Quantity'].between(10, 100)" ] }, { "cell_type": "code", "execution_count": 30, "id": "divided-relation", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ChannelQuantityTotal
1Walcart16.70
7Walcart128.29
10Walcart119.49
11Walcart473.68
15Walcart14.25
............
14012Walcart110.10
14022Walcart17.72
14028Walcart13.91
14034Walcart16274.88
14039Walcart44332.64
\n", "

1851 rows × 3 columns

\n", "
" ], "text/plain": [ " Channel Quantity Total\n", "1 Walcart 1 6.70\n", "7 Walcart 1 28.29\n", "10 Walcart 1 19.49\n", "11 Walcart 4 73.68\n", "15 Walcart 1 4.25\n", "... ... ... ...\n", "14012 Walcart 1 10.10\n", "14022 Walcart 1 7.72\n", "14028 Walcart 1 3.91\n", "14034 Walcart 16 274.88\n", "14039 Walcart 44 332.64\n", "\n", "[1851 rows x 3 columns]" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df.loc[\n", " ledger_df['Channel'] == 'Walcart', \n", " ['Channel', 'Quantity', 'Total']\n", "]" ] }, { "cell_type": "markdown", "id": "included-writer", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "- Sorting data" ] }, { "cell_type": "code", "execution_count": 31, "id": "clinical-adventure", "metadata": { "slideshow": { "slide_type": "fragment" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
InvoiceNoChannelProduct NameProductIDAccountAccountNoDateDeadlineCurrencyUnit PriceQuantityTotal
76139145iBay.comMagic: the Gathering - Hydra Broodmaster (126/...T&G/MAG-22549Sales50042020-01-165-21-20USD0.06150.90
1287714409Understock.comMagic: the Gathering - Hydra Broodmaster (126/...T&G/MAG-22549Sales50042020-01-294-13-20USD0.06171.02
78109342iBay.comMagic: the Gathering - Hydra Broodmaster (126/...T&G/MAG-22549Sales50042020-01-173-22-20USD0.06261.56
78129270iBay.comMagic: the Gathering - Hydra Broodmaster (126/...T&G/MAG-22549Sales50042020-01-173-22-20USD0.06261.56
982211354Understock.comUrban Rebounding Workout DVD, Compilation 1M&T/URB-83617Sales50042020-01-22June 23 2020USD1.6911.69
.......................................
47576162Understock.comAC Adapter/battery charger for GateWay Liteon ...E/AC-44106Sales50042020-01-102020/02/05USD14.882263362.88
61637526iBay.comLarge Display Digital Thermometer LumiscopeH&PC/LAR-98606Sales50042020-01-13Sun Jun 7 00:00:00 2020USD64.15563592.40
61417673iBay.comLarge Display Digital Thermometer LumiscopeH&PC/LAR-98606Sales50042020-01-13Sun Jun 7 00:00:00 2020USD64.15563592.40
80069538iBay.comLarge Display Digital Thermometer LumiscopeH&PC/LAR-98606Sales50042020-01-17Fri Feb 14 00:00:00 2020USD64.15613913.15
52126744iBay.comLarge Display Digital Thermometer LumiscopeH&PC/LAR-98606Sales50042020-01-11Tue Apr 28 00:00:00 2020USD64.15684362.20
\n", "

14054 rows × 12 columns

\n", "
" ], "text/plain": [ " InvoiceNo Channel \\\n", "7613 9145 iBay.com \n", "12877 14409 Understock.com \n", "7810 9342 iBay.com \n", "7812 9270 iBay.com \n", "9822 11354 Understock.com \n", "... ... ... \n", "4757 6162 Understock.com \n", "6163 7526 iBay.com \n", "6141 7673 iBay.com \n", "8006 9538 iBay.com \n", "5212 6744 iBay.com \n", "\n", " Product Name ProductID \\\n", "7613 Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 \n", "12877 Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 \n", "7810 Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 \n", "7812 Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 \n", "9822 Urban Rebounding Workout DVD, Compilation 1 M&T/URB-83617 \n", "... ... ... \n", "4757 AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 \n", "6163 Large Display Digital Thermometer Lumiscope H&PC/LAR-98606 \n", "6141 Large Display Digital Thermometer Lumiscope H&PC/LAR-98606 \n", "8006 Large Display Digital Thermometer Lumiscope H&PC/LAR-98606 \n", "5212 Large Display Digital Thermometer Lumiscope H&PC/LAR-98606 \n", "\n", " Account AccountNo Date Deadline Currency \\\n", "7613 Sales 5004 2020-01-16 5-21-20 USD \n", "12877 Sales 5004 2020-01-29 4-13-20 USD \n", "7810 Sales 5004 2020-01-17 3-22-20 USD \n", "7812 Sales 5004 2020-01-17 3-22-20 USD \n", "9822 Sales 5004 2020-01-22 June 23 2020 USD \n", "... ... ... ... ... ... \n", "4757 Sales 5004 2020-01-10 2020/02/05 USD \n", "6163 Sales 5004 2020-01-13 Sun Jun 7 00:00:00 2020 USD \n", "6141 Sales 5004 2020-01-13 Sun Jun 7 00:00:00 2020 USD \n", "8006 Sales 5004 2020-01-17 Fri Feb 14 00:00:00 2020 USD \n", "5212 Sales 5004 2020-01-11 Tue Apr 28 00:00:00 2020 USD \n", "\n", " Unit Price Quantity Total \n", "7613 0.06 15 0.90 \n", "12877 0.06 17 1.02 \n", "7810 0.06 26 1.56 \n", "7812 0.06 26 1.56 \n", "9822 1.69 1 1.69 \n", "... ... ... ... \n", "4757 14.88 226 3362.88 \n", "6163 64.15 56 3592.40 \n", "6141 64.15 56 3592.40 \n", "8006 64.15 61 3913.15 \n", "5212 64.15 68 4362.20 \n", "\n", "[14054 rows x 12 columns]" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df.sort_values(by='Total')" ] }, { "cell_type": "code", "execution_count": 32, "id": "specialized-fabric", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
InvoiceNoChannelProduct NameProductIDAccountAccountNoDateDeadlineCurrencyUnit PriceQuantityTotal
52126744iBay.comLarge Display Digital Thermometer LumiscopeH&PC/LAR-98606Sales50042020-01-11Tue Apr 28 00:00:00 2020USD64.15684362.20
80069538iBay.comLarge Display Digital Thermometer LumiscopeH&PC/LAR-98606Sales50042020-01-17Fri Feb 14 00:00:00 2020USD64.15613913.15
61417673iBay.comLarge Display Digital Thermometer LumiscopeH&PC/LAR-98606Sales50042020-01-13Sun Jun 7 00:00:00 2020USD64.15563592.40
61637526iBay.comLarge Display Digital Thermometer LumiscopeH&PC/LAR-98606Sales50042020-01-13Sun Jun 7 00:00:00 2020USD64.15563592.40
879710329Understock.comAC Adapter/battery charger for GateWay Liteon ...E/AC-44106Sales50042020-01-192020/01/06USD14.882263362.88
.......................................
982211354Understock.comUrban Rebounding Workout DVD, Compilation 1M&T/URB-83617Sales50042020-01-22June 23 2020USD1.6911.69
78109342iBay.comMagic: the Gathering - Hydra Broodmaster (126/...T&G/MAG-22549Sales50042020-01-173-22-20USD0.06261.56
78129270iBay.comMagic: the Gathering - Hydra Broodmaster (126/...T&G/MAG-22549Sales50042020-01-173-22-20USD0.06261.56
1287714409Understock.comMagic: the Gathering - Hydra Broodmaster (126/...T&G/MAG-22549Sales50042020-01-294-13-20USD0.06171.02
76139145iBay.comMagic: the Gathering - Hydra Broodmaster (126/...T&G/MAG-22549Sales50042020-01-165-21-20USD0.06150.90
\n", "

14054 rows × 12 columns

\n", "
" ], "text/plain": [ " InvoiceNo Channel \\\n", "5212 6744 iBay.com \n", "8006 9538 iBay.com \n", "6141 7673 iBay.com \n", "6163 7526 iBay.com \n", "8797 10329 Understock.com \n", "... ... ... \n", "9822 11354 Understock.com \n", "7810 9342 iBay.com \n", "7812 9270 iBay.com \n", "12877 14409 Understock.com \n", "7613 9145 iBay.com \n", "\n", " Product Name ProductID \\\n", "5212 Large Display Digital Thermometer Lumiscope H&PC/LAR-98606 \n", "8006 Large Display Digital Thermometer Lumiscope H&PC/LAR-98606 \n", "6141 Large Display Digital Thermometer Lumiscope H&PC/LAR-98606 \n", "6163 Large Display Digital Thermometer Lumiscope H&PC/LAR-98606 \n", "8797 AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 \n", "... ... ... \n", "9822 Urban Rebounding Workout DVD, Compilation 1 M&T/URB-83617 \n", "7810 Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 \n", "7812 Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 \n", "12877 Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 \n", "7613 Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 \n", "\n", " Account AccountNo Date Deadline Currency \\\n", "5212 Sales 5004 2020-01-11 Tue Apr 28 00:00:00 2020 USD \n", "8006 Sales 5004 2020-01-17 Fri Feb 14 00:00:00 2020 USD \n", "6141 Sales 5004 2020-01-13 Sun Jun 7 00:00:00 2020 USD \n", "6163 Sales 5004 2020-01-13 Sun Jun 7 00:00:00 2020 USD \n", "8797 Sales 5004 2020-01-19 2020/01/06 USD \n", "... ... ... ... ... ... \n", "9822 Sales 5004 2020-01-22 June 23 2020 USD \n", "7810 Sales 5004 2020-01-17 3-22-20 USD \n", "7812 Sales 5004 2020-01-17 3-22-20 USD \n", "12877 Sales 5004 2020-01-29 4-13-20 USD \n", "7613 Sales 5004 2020-01-16 5-21-20 USD \n", "\n", " Unit Price Quantity Total \n", "5212 64.15 68 4362.20 \n", "8006 64.15 61 3913.15 \n", "6141 64.15 56 3592.40 \n", "6163 64.15 56 3592.40 \n", "8797 14.88 226 3362.88 \n", "... ... ... ... \n", "9822 1.69 1 1.69 \n", "7810 0.06 26 1.56 \n", "7812 0.06 26 1.56 \n", "12877 0.06 17 1.02 \n", "7613 0.06 15 0.90 \n", "\n", "[14054 rows x 12 columns]" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df.sort_values(by='Total', ascending=False)" ] }, { "cell_type": "code", "execution_count": 33, "id": "simplified-circumstances", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
InvoiceNoChannelProduct NameProductIDAccountAccountNoDateDeadlineCurrencyUnit PriceQuantityTotal
47476279Understock.comAC Adapter/battery charger for GateWay Liteon ...E/AC-44106Sales50042020-01-102020/02/05USD14.882263362.88
47576162Understock.comAC Adapter/battery charger for GateWay Liteon ...E/AC-44106Sales50042020-01-102020/02/05USD14.882263362.88
49696397Understock.comAC Adapter/battery charger for GateWay Liteon ...E/AC-44106Sales50042020-01-102020/02/05USD14.882263362.88
879710329Understock.comAC Adapter/battery charger for GateWay Liteon ...E/AC-44106Sales50042020-01-192020/01/06USD14.882263362.88
54997031iBay.comNaNH&PC/ULT-64807Sales50042020-01-12Mon Nov 25 00:00:00 2019USD13.281842443.52
.......................................
6162148Understock.comKodak EasyShare Z990 12 MP Digital Camera with...C&P/KOD-32137Sales50042020-01-0211-23-19USD166.301166.30
42485780Understock.comKodak EasyShare Z990 12 MP Digital Camera with...C&P/KOD-32137Sales50042020-01-092-28-20USD166.301166.30
44855839Understock.comKodak EasyShare Z990 12 MP Digital Camera with...C&P/KOD-32137Sales50042020-01-092-28-20USD166.301166.30
61527684Understock.comKodak EasyShare Z990 12 MP Digital Camera with...C&P/KOD-32137Sales50042020-01-134-02-20USD166.301166.30
61647531Understock.comKodak EasyShare Z990 12 MP Digital Camera with...C&P/KOD-32137Sales50042020-01-134-02-20USD166.301166.30
\n", "

14054 rows × 12 columns

\n", "
" ], "text/plain": [ " InvoiceNo Channel \\\n", "4747 6279 Understock.com \n", "4757 6162 Understock.com \n", "4969 6397 Understock.com \n", "8797 10329 Understock.com \n", "5499 7031 iBay.com \n", "... ... ... \n", "616 2148 Understock.com \n", "4248 5780 Understock.com \n", "4485 5839 Understock.com \n", "6152 7684 Understock.com \n", "6164 7531 Understock.com \n", "\n", " Product Name ProductID \\\n", "4747 AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 \n", "4757 AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 \n", "4969 AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 \n", "8797 AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 \n", "5499 NaN H&PC/ULT-64807 \n", "... ... ... \n", "616 Kodak EasyShare Z990 12 MP Digital Camera with... C&P/KOD-32137 \n", "4248 Kodak EasyShare Z990 12 MP Digital Camera with... C&P/KOD-32137 \n", "4485 Kodak EasyShare Z990 12 MP Digital Camera with... C&P/KOD-32137 \n", "6152 Kodak EasyShare Z990 12 MP Digital Camera with... C&P/KOD-32137 \n", "6164 Kodak EasyShare Z990 12 MP Digital Camera with... C&P/KOD-32137 \n", "\n", " Account AccountNo Date Deadline Currency \\\n", "4747 Sales 5004 2020-01-10 2020/02/05 USD \n", "4757 Sales 5004 2020-01-10 2020/02/05 USD \n", "4969 Sales 5004 2020-01-10 2020/02/05 USD \n", "8797 Sales 5004 2020-01-19 2020/01/06 USD \n", "5499 Sales 5004 2020-01-12 Mon Nov 25 00:00:00 2019 USD \n", "... ... ... ... ... ... \n", "616 Sales 5004 2020-01-02 11-23-19 USD \n", "4248 Sales 5004 2020-01-09 2-28-20 USD \n", "4485 Sales 5004 2020-01-09 2-28-20 USD \n", "6152 Sales 5004 2020-01-13 4-02-20 USD \n", "6164 Sales 5004 2020-01-13 4-02-20 USD \n", "\n", " Unit Price Quantity Total \n", "4747 14.88 226 3362.88 \n", "4757 14.88 226 3362.88 \n", "4969 14.88 226 3362.88 \n", "8797 14.88 226 3362.88 \n", "5499 13.28 184 2443.52 \n", "... ... ... ... \n", "616 166.30 1 166.30 \n", "4248 166.30 1 166.30 \n", "4485 166.30 1 166.30 \n", "6152 166.30 1 166.30 \n", "6164 166.30 1 166.30 \n", "\n", "[14054 rows x 12 columns]" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df.sort_values(by=['Quantity', 'Total'], ascending=[False, True])" ] }, { "cell_type": "markdown", "id": "buried-purse", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "\n", "## Cleaning data\n", "\n", "- Dealing with missing values" ] }, { "cell_type": "code", "execution_count": 34, "id": "raised-heart", "metadata": { "slideshow": { "slide_type": "fragment" }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "nan" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df.iloc[2, 2]" ] }, { "cell_type": "markdown", "id": "cognitive-memorabilia", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "- Let's find missing values" ] }, { "cell_type": "code", "execution_count": 35, "id": "viral-wayne", "metadata": { "slideshow": { "slide_type": "fragment" }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "0 False\n", "1 False\n", "2 True\n", "3 False\n", "4 False\n", " ... \n", "14049 False\n", "14050 False\n", "14051 False\n", "14052 True\n", "14053 False\n", "Name: Product Name, Length: 14054, dtype: bool" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df['Product Name'].isna()" ] }, { "cell_type": "code", "execution_count": 36, "id": "willing-intent", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
InvoiceNoChannelProduct NameProductIDAccountAccountNoDateDeadlineCurrencyUnit PriceQuantityTotal
21534BullseyeNaNT&G/PET-14209Sales50042020-01-0105/07/20USD11.67558.35
61538Understock.comNaNS&O/TEX-91494Sales50042020-01-01Thu Oct 31 00:00:00 2019USD31.369282.24
161548iBay.comNaNK&D/RES-27315Sales50042020-01-01November 29 2019USD8.07540.35
351567Understock.comNaNT&G/(10-74529Sales50042020-01-016-03-20USD12.30561.50
361568WalcartNaNH&K/WAL-83087Sales50042020-01-012020/05/06USD3.5213.52
.......................................
1402115553iBay.comNaNH&K/GIF-60463Sales50042020-01-312020/07/23USD17.2823397.44
1402515443Understock.comNaNCP&A/VXI-50842Sales50042020-01-31Fri May 8 00:00:00 2020USD16.491031698.47
1403615495BullseyeNaNM&T/THE-90996Sales50042020-01-31January 07 2020USD14.859133.65
1404015572iBay.comNaNMI/AQU-84157Sales50042020-01-3101/24/20USD16.766100.56
1405215584iBay.comNaNE/POL-61164Sales50042020-01-31June 25 2020USD4.7825119.50
\n", "

1692 rows × 12 columns

\n", "
" ], "text/plain": [ " InvoiceNo Channel Product Name ProductID Account \\\n", "2 1534 Bullseye NaN T&G/PET-14209 Sales \n", "6 1538 Understock.com NaN S&O/TEX-91494 Sales \n", "16 1548 iBay.com NaN K&D/RES-27315 Sales \n", "35 1567 Understock.com NaN T&G/(10-74529 Sales \n", "36 1568 Walcart NaN H&K/WAL-83087 Sales \n", "... ... ... ... ... ... \n", "14021 15553 iBay.com NaN H&K/GIF-60463 Sales \n", "14025 15443 Understock.com NaN CP&A/VXI-50842 Sales \n", "14036 15495 Bullseye NaN M&T/THE-90996 Sales \n", "14040 15572 iBay.com NaN MI/AQU-84157 Sales \n", "14052 15584 iBay.com NaN E/POL-61164 Sales \n", "\n", " AccountNo Date Deadline Currency Unit Price \\\n", "2 5004 2020-01-01 05/07/20 USD 11.67 \n", "6 5004 2020-01-01 Thu Oct 31 00:00:00 2019 USD 31.36 \n", "16 5004 2020-01-01 November 29 2019 USD 8.07 \n", "35 5004 2020-01-01 6-03-20 USD 12.30 \n", "36 5004 2020-01-01 2020/05/06 USD 3.52 \n", "... ... ... ... ... ... \n", "14021 5004 2020-01-31 2020/07/23 USD 17.28 \n", "14025 5004 2020-01-31 Fri May 8 00:00:00 2020 USD 16.49 \n", "14036 5004 2020-01-31 January 07 2020 USD 14.85 \n", "14040 5004 2020-01-31 01/24/20 USD 16.76 \n", "14052 5004 2020-01-31 June 25 2020 USD 4.78 \n", "\n", " Quantity Total \n", "2 5 58.35 \n", "6 9 282.24 \n", "16 5 40.35 \n", "35 5 61.50 \n", "36 1 3.52 \n", "... ... ... \n", "14021 23 397.44 \n", "14025 103 1698.47 \n", "14036 9 133.65 \n", "14040 6 100.56 \n", "14052 25 119.50 \n", "\n", "[1692 rows x 12 columns]" ] }, "execution_count": 36, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df[ledger_df['Product Name'].isna()]" ] }, { "cell_type": "code", "execution_count": 37, "id": "psychological-library", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "1692" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df['Product Name'].isna().sum()" ] }, { "cell_type": "code", "execution_count": 38, "id": "rising-length", "metadata": { "slideshow": { "slide_type": "fragment" }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "InvoiceNo 0\n", "Channel 0\n", "Product Name 1692\n", "ProductID 0\n", "Account 0\n", "AccountNo 0\n", "Date 0\n", "Deadline 0\n", "Currency 0\n", "Unit Price 0\n", "Quantity 0\n", "Total 0\n", "dtype: int64" ] }, "execution_count": 38, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df.isna().sum()" ] }, { "cell_type": "markdown", "id": "impaired-renewal", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "- Discarding missing values" ] }, { "cell_type": "code", "execution_count": 39, "id": "southwest-latest", "metadata": { "slideshow": { "slide_type": "fragment" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
InvoiceNoChannelProduct NameProductIDAccountAccountNoDateDeadlineCurrencyUnit PriceQuantityTotal
01532Shoppe.comCannon Water Bomb Balloons 100 PackT&G/CAN-97509Sales50042020-01-0111/23/19USD20.1114281.54
11533WalcartLEGO Ninja Turtles Stealth Shell in Pursuit 79102T&G/LEG-37777Sales50042020-01-0106/15/20USD6.7016.70
31535BullseyeTransformers Age of Extinction Generations Del...T&G/TRA-20170Sales50042020-01-0112/22/19USD13.46680.76
41535BullseyeTransformers Age of Extinction Generations Del...T&G/TRA-20170Sales50042020-01-0112/22/19USD13.46680.76
51537Bullseye3x Anti-Spy Privacy Screen Protector Compatibl...CP&A/3X-00445Sales50042020-01-01Mon Jan 20 00:00:00 2020USD7.39859.12
.......................................
1404815580iBay.comLauri Toddler ToteT&G/LAU-88048Sales50042020-01-312-14-20USD14.46114.46
1404915581BullseyeAC Adapter/Power Supply&Cord for Lenovo 3000 G...E/AC-63975Sales50042020-01-31February 23 2020USD28.728229.76
1405015582BullseyeCisco Systems Gigabit VPN Router (RV320K9NA)E/CIS-74992Sales50042020-01-31January 21 2020USD33.39133.39
1405115583Understock.comPhilips AJ3116M/37 Digital Tuning Clock Radio ...E/PHI-08100Sales50042020-01-31March 22 2020USD4.1814.18
1405315585Understock.comSirius Satellite Radio XADH2 Home Access Kit f...E/SIR-83381Sales50042020-01-31February 01 2020USD33.16266.32
\n", "

12362 rows × 12 columns

\n", "
" ], "text/plain": [ " InvoiceNo Channel \\\n", "0 1532 Shoppe.com \n", "1 1533 Walcart \n", "3 1535 Bullseye \n", "4 1535 Bullseye \n", "5 1537 Bullseye \n", "... ... ... \n", "14048 15580 iBay.com \n", "14049 15581 Bullseye \n", "14050 15582 Bullseye \n", "14051 15583 Understock.com \n", "14053 15585 Understock.com \n", "\n", " Product Name ProductID \\\n", "0 Cannon Water Bomb Balloons 100 Pack T&G/CAN-97509 \n", "1 LEGO Ninja Turtles Stealth Shell in Pursuit 79102 T&G/LEG-37777 \n", "3 Transformers Age of Extinction Generations Del... T&G/TRA-20170 \n", "4 Transformers Age of Extinction Generations Del... T&G/TRA-20170 \n", "5 3x Anti-Spy Privacy Screen Protector Compatibl... CP&A/3X-00445 \n", "... ... ... \n", "14048 Lauri Toddler Tote T&G/LAU-88048 \n", "14049 AC Adapter/Power Supply&Cord for Lenovo 3000 G... E/AC-63975 \n", "14050 Cisco Systems Gigabit VPN Router (RV320K9NA) E/CIS-74992 \n", "14051 Philips AJ3116M/37 Digital Tuning Clock Radio ... E/PHI-08100 \n", "14053 Sirius Satellite Radio XADH2 Home Access Kit f... E/SIR-83381 \n", "\n", " Account AccountNo Date Deadline Currency \\\n", "0 Sales 5004 2020-01-01 11/23/19 USD \n", "1 Sales 5004 2020-01-01 06/15/20 USD \n", "3 Sales 5004 2020-01-01 12/22/19 USD \n", "4 Sales 5004 2020-01-01 12/22/19 USD \n", "5 Sales 5004 2020-01-01 Mon Jan 20 00:00:00 2020 USD \n", "... ... ... ... ... ... \n", "14048 Sales 5004 2020-01-31 2-14-20 USD \n", "14049 Sales 5004 2020-01-31 February 23 2020 USD \n", "14050 Sales 5004 2020-01-31 January 21 2020 USD \n", "14051 Sales 5004 2020-01-31 March 22 2020 USD \n", "14053 Sales 5004 2020-01-31 February 01 2020 USD \n", "\n", " Unit Price Quantity Total \n", "0 20.11 14 281.54 \n", "1 6.70 1 6.70 \n", "3 13.46 6 80.76 \n", "4 13.46 6 80.76 \n", "5 7.39 8 59.12 \n", "... ... ... ... \n", "14048 14.46 1 14.46 \n", "14049 28.72 8 229.76 \n", "14050 33.39 1 33.39 \n", "14051 4.18 1 4.18 \n", "14053 33.16 2 66.32 \n", "\n", "[12362 rows x 12 columns]" ] }, "execution_count": 39, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df[ledger_df['Product Name'].notna()]" ] }, { "cell_type": "code", "execution_count": 40, "id": "italic-archive", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
InvoiceNoChannelProduct NameProductIDAccountAccountNoDateDeadlineCurrencyUnit PriceQuantityTotal
01532Shoppe.comCannon Water Bomb Balloons 100 PackT&G/CAN-97509Sales50042020-01-0111/23/19USD20.1114281.54
11533WalcartLEGO Ninja Turtles Stealth Shell in Pursuit 79102T&G/LEG-37777Sales50042020-01-0106/15/20USD6.7016.70
31535BullseyeTransformers Age of Extinction Generations Del...T&G/TRA-20170Sales50042020-01-0112/22/19USD13.46680.76
41535BullseyeTransformers Age of Extinction Generations Del...T&G/TRA-20170Sales50042020-01-0112/22/19USD13.46680.76
51537Bullseye3x Anti-Spy Privacy Screen Protector Compatibl...CP&A/3X-00445Sales50042020-01-01Mon Jan 20 00:00:00 2020USD7.39859.12
.......................................
1404815580iBay.comLauri Toddler ToteT&G/LAU-88048Sales50042020-01-312-14-20USD14.46114.46
1404915581BullseyeAC Adapter/Power Supply&Cord for Lenovo 3000 G...E/AC-63975Sales50042020-01-31February 23 2020USD28.728229.76
1405015582BullseyeCisco Systems Gigabit VPN Router (RV320K9NA)E/CIS-74992Sales50042020-01-31January 21 2020USD33.39133.39
1405115583Understock.comPhilips AJ3116M/37 Digital Tuning Clock Radio ...E/PHI-08100Sales50042020-01-31March 22 2020USD4.1814.18
1405315585Understock.comSirius Satellite Radio XADH2 Home Access Kit f...E/SIR-83381Sales50042020-01-31February 01 2020USD33.16266.32
\n", "

12362 rows × 12 columns

\n", "
" ], "text/plain": [ " InvoiceNo Channel \\\n", "0 1532 Shoppe.com \n", "1 1533 Walcart \n", "3 1535 Bullseye \n", "4 1535 Bullseye \n", "5 1537 Bullseye \n", "... ... ... \n", "14048 15580 iBay.com \n", "14049 15581 Bullseye \n", "14050 15582 Bullseye \n", "14051 15583 Understock.com \n", "14053 15585 Understock.com \n", "\n", " Product Name ProductID \\\n", "0 Cannon Water Bomb Balloons 100 Pack T&G/CAN-97509 \n", "1 LEGO Ninja Turtles Stealth Shell in Pursuit 79102 T&G/LEG-37777 \n", "3 Transformers Age of Extinction Generations Del... T&G/TRA-20170 \n", "4 Transformers Age of Extinction Generations Del... T&G/TRA-20170 \n", "5 3x Anti-Spy Privacy Screen Protector Compatibl... CP&A/3X-00445 \n", "... ... ... \n", "14048 Lauri Toddler Tote T&G/LAU-88048 \n", "14049 AC Adapter/Power Supply&Cord for Lenovo 3000 G... E/AC-63975 \n", "14050 Cisco Systems Gigabit VPN Router (RV320K9NA) E/CIS-74992 \n", "14051 Philips AJ3116M/37 Digital Tuning Clock Radio ... E/PHI-08100 \n", "14053 Sirius Satellite Radio XADH2 Home Access Kit f... E/SIR-83381 \n", "\n", " Account AccountNo Date Deadline Currency \\\n", "0 Sales 5004 2020-01-01 11/23/19 USD \n", "1 Sales 5004 2020-01-01 06/15/20 USD \n", "3 Sales 5004 2020-01-01 12/22/19 USD \n", "4 Sales 5004 2020-01-01 12/22/19 USD \n", "5 Sales 5004 2020-01-01 Mon Jan 20 00:00:00 2020 USD \n", "... ... ... ... ... ... \n", "14048 Sales 5004 2020-01-31 2-14-20 USD \n", "14049 Sales 5004 2020-01-31 February 23 2020 USD \n", "14050 Sales 5004 2020-01-31 January 21 2020 USD \n", "14051 Sales 5004 2020-01-31 March 22 2020 USD \n", "14053 Sales 5004 2020-01-31 February 01 2020 USD \n", "\n", " Unit Price Quantity Total \n", "0 20.11 14 281.54 \n", "1 6.70 1 6.70 \n", "3 13.46 6 80.76 \n", "4 13.46 6 80.76 \n", "5 7.39 8 59.12 \n", "... ... ... ... \n", "14048 14.46 1 14.46 \n", "14049 28.72 8 229.76 \n", "14050 33.39 1 33.39 \n", "14051 4.18 1 4.18 \n", "14053 33.16 2 66.32 \n", "\n", "[12362 rows x 12 columns]" ] }, "execution_count": 40, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df.dropna(subset=['Product Name', 'ProductID'])" ] }, { "cell_type": "code", "execution_count": 41, "id": "improved-decade", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
InvoiceNoChannelProduct NameProductIDAccountAccountNoDateDeadlineCurrencyUnit PriceQuantityTotal
01532Shoppe.comCannon Water Bomb Balloons 100 PackT&G/CAN-97509Sales50042020-01-0111/23/19USD20.1114281.54
11533WalcartLEGO Ninja Turtles Stealth Shell in Pursuit 79102T&G/LEG-37777Sales50042020-01-0106/15/20USD6.7016.70
21534BullseyeNaNT&G/PET-14209Sales50042020-01-0105/07/20USD11.67558.35
31535BullseyeTransformers Age of Extinction Generations Del...T&G/TRA-20170Sales50042020-01-0112/22/19USD13.46680.76
41535BullseyeTransformers Age of Extinction Generations Del...T&G/TRA-20170Sales50042020-01-0112/22/19USD13.46680.76
.......................................
1404915581BullseyeAC Adapter/Power Supply&Cord for Lenovo 3000 G...E/AC-63975Sales50042020-01-31February 23 2020USD28.728229.76
1405015582BullseyeCisco Systems Gigabit VPN Router (RV320K9NA)E/CIS-74992Sales50042020-01-31January 21 2020USD33.39133.39
1405115583Understock.comPhilips AJ3116M/37 Digital Tuning Clock Radio ...E/PHI-08100Sales50042020-01-31March 22 2020USD4.1814.18
1405215584iBay.comNaNE/POL-61164Sales50042020-01-31June 25 2020USD4.7825119.50
1405315585Understock.comSirius Satellite Radio XADH2 Home Access Kit f...E/SIR-83381Sales50042020-01-31February 01 2020USD33.16266.32
\n", "

14054 rows × 12 columns

\n", "
" ], "text/plain": [ " InvoiceNo Channel \\\n", "0 1532 Shoppe.com \n", "1 1533 Walcart \n", "2 1534 Bullseye \n", "3 1535 Bullseye \n", "4 1535 Bullseye \n", "... ... ... \n", "14049 15581 Bullseye \n", "14050 15582 Bullseye \n", "14051 15583 Understock.com \n", "14052 15584 iBay.com \n", "14053 15585 Understock.com \n", "\n", " Product Name ProductID \\\n", "0 Cannon Water Bomb Balloons 100 Pack T&G/CAN-97509 \n", "1 LEGO Ninja Turtles Stealth Shell in Pursuit 79102 T&G/LEG-37777 \n", "2 NaN T&G/PET-14209 \n", "3 Transformers Age of Extinction Generations Del... T&G/TRA-20170 \n", "4 Transformers Age of Extinction Generations Del... T&G/TRA-20170 \n", "... ... ... \n", "14049 AC Adapter/Power Supply&Cord for Lenovo 3000 G... E/AC-63975 \n", "14050 Cisco Systems Gigabit VPN Router (RV320K9NA) E/CIS-74992 \n", "14051 Philips AJ3116M/37 Digital Tuning Clock Radio ... E/PHI-08100 \n", "14052 NaN E/POL-61164 \n", "14053 Sirius Satellite Radio XADH2 Home Access Kit f... E/SIR-83381 \n", "\n", " Account AccountNo Date Deadline Currency Unit Price \\\n", "0 Sales 5004 2020-01-01 11/23/19 USD 20.11 \n", "1 Sales 5004 2020-01-01 06/15/20 USD 6.70 \n", "2 Sales 5004 2020-01-01 05/07/20 USD 11.67 \n", "3 Sales 5004 2020-01-01 12/22/19 USD 13.46 \n", "4 Sales 5004 2020-01-01 12/22/19 USD 13.46 \n", "... ... ... ... ... ... ... \n", "14049 Sales 5004 2020-01-31 February 23 2020 USD 28.72 \n", "14050 Sales 5004 2020-01-31 January 21 2020 USD 33.39 \n", "14051 Sales 5004 2020-01-31 March 22 2020 USD 4.18 \n", "14052 Sales 5004 2020-01-31 June 25 2020 USD 4.78 \n", "14053 Sales 5004 2020-01-31 February 01 2020 USD 33.16 \n", "\n", " Quantity Total \n", "0 14 281.54 \n", "1 1 6.70 \n", "2 5 58.35 \n", "3 6 80.76 \n", "4 6 80.76 \n", "... ... ... \n", "14049 8 229.76 \n", "14050 1 33.39 \n", "14051 1 4.18 \n", "14052 25 119.50 \n", "14053 2 66.32 \n", "\n", "[14054 rows x 12 columns]" ] }, "execution_count": 41, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df.dropna(how='all')" ] }, { "cell_type": "code", "execution_count": 42, "id": "animated-penny", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
InvoiceNoChannelProductIDAccountAccountNoDateDeadlineCurrencyUnit PriceQuantityTotal
01532Shoppe.comT&G/CAN-97509Sales50042020-01-0111/23/19USD20.1114281.54
11533WalcartT&G/LEG-37777Sales50042020-01-0106/15/20USD6.7016.70
21534BullseyeT&G/PET-14209Sales50042020-01-0105/07/20USD11.67558.35
31535BullseyeT&G/TRA-20170Sales50042020-01-0112/22/19USD13.46680.76
41535BullseyeT&G/TRA-20170Sales50042020-01-0112/22/19USD13.46680.76
....................................
1404915581BullseyeE/AC-63975Sales50042020-01-31February 23 2020USD28.728229.76
1405015582BullseyeE/CIS-74992Sales50042020-01-31January 21 2020USD33.39133.39
1405115583Understock.comE/PHI-08100Sales50042020-01-31March 22 2020USD4.1814.18
1405215584iBay.comE/POL-61164Sales50042020-01-31June 25 2020USD4.7825119.50
1405315585Understock.comE/SIR-83381Sales50042020-01-31February 01 2020USD33.16266.32
\n", "

14054 rows × 11 columns

\n", "
" ], "text/plain": [ " InvoiceNo Channel ProductID Account AccountNo Date \\\n", "0 1532 Shoppe.com T&G/CAN-97509 Sales 5004 2020-01-01 \n", "1 1533 Walcart T&G/LEG-37777 Sales 5004 2020-01-01 \n", "2 1534 Bullseye T&G/PET-14209 Sales 5004 2020-01-01 \n", "3 1535 Bullseye T&G/TRA-20170 Sales 5004 2020-01-01 \n", "4 1535 Bullseye T&G/TRA-20170 Sales 5004 2020-01-01 \n", "... ... ... ... ... ... ... \n", "14049 15581 Bullseye E/AC-63975 Sales 5004 2020-01-31 \n", "14050 15582 Bullseye E/CIS-74992 Sales 5004 2020-01-31 \n", "14051 15583 Understock.com E/PHI-08100 Sales 5004 2020-01-31 \n", "14052 15584 iBay.com E/POL-61164 Sales 5004 2020-01-31 \n", "14053 15585 Understock.com E/SIR-83381 Sales 5004 2020-01-31 \n", "\n", " Deadline Currency Unit Price Quantity Total \n", "0 11/23/19 USD 20.11 14 281.54 \n", "1 06/15/20 USD 6.70 1 6.70 \n", "2 05/07/20 USD 11.67 5 58.35 \n", "3 12/22/19 USD 13.46 6 80.76 \n", "4 12/22/19 USD 13.46 6 80.76 \n", "... ... ... ... ... ... \n", "14049 February 23 2020 USD 28.72 8 229.76 \n", "14050 January 21 2020 USD 33.39 1 33.39 \n", "14051 March 22 2020 USD 4.18 1 4.18 \n", "14052 June 25 2020 USD 4.78 25 119.50 \n", "14053 February 01 2020 USD 33.16 2 66.32 \n", "\n", "[14054 rows x 11 columns]" ] }, "execution_count": 42, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df.dropna(how='any', axis='columns')" ] }, { "cell_type": "markdown", "id": "revolutionary-night", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "- Filling missing values" ] }, { "cell_type": "code", "execution_count": 43, "id": "first-genesis", "metadata": { "slideshow": { "slide_type": "fragment" }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "0 Cannon Water Bomb Balloons 100 Pack\n", "1 LEGO Ninja Turtles Stealth Shell in Pursuit 79102\n", "2 MISSING\n", "3 Transformers Age of Extinction Generations Del...\n", "4 Transformers Age of Extinction Generations Del...\n", " ... \n", "14049 AC Adapter/Power Supply&Cord for Lenovo 3000 G...\n", "14050 Cisco Systems Gigabit VPN Router (RV320K9NA)\n", "14051 Philips AJ3116M/37 Digital Tuning Clock Radio ...\n", "14052 MISSING\n", "14053 Sirius Satellite Radio XADH2 Home Access Kit f...\n", "Name: Product Name, Length: 14054, dtype: object" ] }, "execution_count": 43, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df['Product Name'].fillna('MISSING')" ] }, { "cell_type": "code", "execution_count": 44, "id": "continent-birth", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "0 Cannon Water Bomb Balloons 100 Pack\n", "1 LEGO Ninja Turtles Stealth Shell in Pursuit 79102\n", "2 LEGO Ninja Turtles Stealth Shell in Pursuit 79102\n", "3 Transformers Age of Extinction Generations Del...\n", "4 Transformers Age of Extinction Generations Del...\n", " ... \n", "14049 AC Adapter/Power Supply&Cord for Lenovo 3000 G...\n", "14050 Cisco Systems Gigabit VPN Router (RV320K9NA)\n", "14051 Philips AJ3116M/37 Digital Tuning Clock Radio ...\n", "14052 Philips AJ3116M/37 Digital Tuning Clock Radio ...\n", "14053 Sirius Satellite Radio XADH2 Home Access Kit f...\n", "Name: Product Name, Length: 14054, dtype: object" ] }, "execution_count": 44, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df['Product Name'].fillna(method='ffill')" ] }, { "cell_type": "code", "execution_count": 45, "id": "lonely-swedish", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "0 Cannon Water Bomb Balloons 100 Pack\n", "1 LEGO Ninja Turtles Stealth Shell in Pursuit 79102\n", "2 Transformers Age of Extinction Generations Del...\n", "3 Transformers Age of Extinction Generations Del...\n", "4 Transformers Age of Extinction Generations Del...\n", " ... \n", "14049 AC Adapter/Power Supply&Cord for Lenovo 3000 G...\n", "14050 Cisco Systems Gigabit VPN Router (RV320K9NA)\n", "14051 Philips AJ3116M/37 Digital Tuning Clock Radio ...\n", "14052 Sirius Satellite Radio XADH2 Home Access Kit f...\n", "14053 Sirius Satellite Radio XADH2 Home Access Kit f...\n", "Name: Product Name, Length: 14054, dtype: object" ] }, "execution_count": 45, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df['Product Name'].fillna(method='bfill')" ] }, { "cell_type": "markdown", "id": "significant-exercise", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "- Dealing with duplicate rows" ] }, { "cell_type": "code", "execution_count": 46, "id": "optimum-ownership", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "0 False\n", "1 False\n", "2 False\n", "3 False\n", "4 True\n", " ... \n", "14049 False\n", "14050 False\n", "14051 False\n", "14052 False\n", "14053 False\n", "Length: 14054, dtype: bool" ] }, "execution_count": 46, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df.duplicated()" ] }, { "cell_type": "code", "execution_count": 47, "id": "geological-martin", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "0 False\n", "1 False\n", "2 False\n", "3 True\n", "4 True\n", " ... \n", "14049 False\n", "14050 False\n", "14051 False\n", "14052 False\n", "14053 False\n", "Length: 14054, dtype: bool" ] }, "execution_count": 47, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df.duplicated(keep=False)" ] }, { "cell_type": "code", "execution_count": 48, "id": "roman-childhood", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
InvoiceNoChannelProduct NameProductIDAccountAccountNoDateDeadlineCurrencyUnit PriceQuantityTotal
01532Shoppe.comCannon Water Bomb Balloons 100 PackT&G/CAN-97509Sales50042020-01-0111/23/19USD20.1114281.54
11533WalcartLEGO Ninja Turtles Stealth Shell in Pursuit 79102T&G/LEG-37777Sales50042020-01-0106/15/20USD6.7016.70
21534BullseyeNaNT&G/PET-14209Sales50042020-01-0105/07/20USD11.67558.35
31535BullseyeTransformers Age of Extinction Generations Del...T&G/TRA-20170Sales50042020-01-0112/22/19USD13.46680.76
51537Bullseye3x Anti-Spy Privacy Screen Protector Compatibl...CP&A/3X-00445Sales50042020-01-01Mon Jan 20 00:00:00 2020USD7.39859.12
.......................................
1404915581BullseyeAC Adapter/Power Supply&Cord for Lenovo 3000 G...E/AC-63975Sales50042020-01-31February 23 2020USD28.728229.76
1405015582BullseyeCisco Systems Gigabit VPN Router (RV320K9NA)E/CIS-74992Sales50042020-01-31January 21 2020USD33.39133.39
1405115583Understock.comPhilips AJ3116M/37 Digital Tuning Clock Radio ...E/PHI-08100Sales50042020-01-31March 22 2020USD4.1814.18
1405215584iBay.comNaNE/POL-61164Sales50042020-01-31June 25 2020USD4.7825119.50
1405315585Understock.comSirius Satellite Radio XADH2 Home Access Kit f...E/SIR-83381Sales50042020-01-31February 01 2020USD33.16266.32
\n", "

13987 rows × 12 columns

\n", "
" ], "text/plain": [ " InvoiceNo Channel \\\n", "0 1532 Shoppe.com \n", "1 1533 Walcart \n", "2 1534 Bullseye \n", "3 1535 Bullseye \n", "5 1537 Bullseye \n", "... ... ... \n", "14049 15581 Bullseye \n", "14050 15582 Bullseye \n", "14051 15583 Understock.com \n", "14052 15584 iBay.com \n", "14053 15585 Understock.com \n", "\n", " Product Name ProductID \\\n", "0 Cannon Water Bomb Balloons 100 Pack T&G/CAN-97509 \n", "1 LEGO Ninja Turtles Stealth Shell in Pursuit 79102 T&G/LEG-37777 \n", "2 NaN T&G/PET-14209 \n", "3 Transformers Age of Extinction Generations Del... T&G/TRA-20170 \n", "5 3x Anti-Spy Privacy Screen Protector Compatibl... CP&A/3X-00445 \n", "... ... ... \n", "14049 AC Adapter/Power Supply&Cord for Lenovo 3000 G... E/AC-63975 \n", "14050 Cisco Systems Gigabit VPN Router (RV320K9NA) E/CIS-74992 \n", "14051 Philips AJ3116M/37 Digital Tuning Clock Radio ... E/PHI-08100 \n", "14052 NaN E/POL-61164 \n", "14053 Sirius Satellite Radio XADH2 Home Access Kit f... E/SIR-83381 \n", "\n", " Account AccountNo Date Deadline Currency \\\n", "0 Sales 5004 2020-01-01 11/23/19 USD \n", "1 Sales 5004 2020-01-01 06/15/20 USD \n", "2 Sales 5004 2020-01-01 05/07/20 USD \n", "3 Sales 5004 2020-01-01 12/22/19 USD \n", "5 Sales 5004 2020-01-01 Mon Jan 20 00:00:00 2020 USD \n", "... ... ... ... ... ... \n", "14049 Sales 5004 2020-01-31 February 23 2020 USD \n", "14050 Sales 5004 2020-01-31 January 21 2020 USD \n", "14051 Sales 5004 2020-01-31 March 22 2020 USD \n", "14052 Sales 5004 2020-01-31 June 25 2020 USD \n", "14053 Sales 5004 2020-01-31 February 01 2020 USD \n", "\n", " Unit Price Quantity Total \n", "0 20.11 14 281.54 \n", "1 6.70 1 6.70 \n", "2 11.67 5 58.35 \n", "3 13.46 6 80.76 \n", "5 7.39 8 59.12 \n", "... ... ... ... \n", "14049 28.72 8 229.76 \n", "14050 33.39 1 33.39 \n", "14051 4.18 1 4.18 \n", "14052 4.78 25 119.50 \n", "14053 33.16 2 66.32 \n", "\n", "[13987 rows x 12 columns]" ] }, "execution_count": 48, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df.drop_duplicates()" ] }, { "cell_type": "code", "execution_count": 49, "id": "following-deployment", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
InvoiceNoChannelProduct NameProductIDAccountAccountNoDateDeadlineCurrencyUnit PriceQuantityTotal
01532Shoppe.comCannon Water Bomb Balloons 100 PackT&G/CAN-97509Sales50042020-01-0111/23/19USD20.1114281.54
11533WalcartLEGO Ninja Turtles Stealth Shell in Pursuit 79102T&G/LEG-37777Sales50042020-01-0106/15/20USD6.7016.70
21534BullseyeNaNT&G/PET-14209Sales50042020-01-0105/07/20USD11.67558.35
31535BullseyeTransformers Age of Extinction Generations Del...T&G/TRA-20170Sales50042020-01-0112/22/19USD13.46680.76
51537Bullseye3x Anti-Spy Privacy Screen Protector Compatibl...CP&A/3X-00445Sales50042020-01-01Mon Jan 20 00:00:00 2020USD7.39859.12
.......................................
1404915581BullseyeAC Adapter/Power Supply&Cord for Lenovo 3000 G...E/AC-63975Sales50042020-01-31February 23 2020USD28.728229.76
1405015582BullseyeCisco Systems Gigabit VPN Router (RV320K9NA)E/CIS-74992Sales50042020-01-31January 21 2020USD33.39133.39
1405115583Understock.comPhilips AJ3116M/37 Digital Tuning Clock Radio ...E/PHI-08100Sales50042020-01-31March 22 2020USD4.1814.18
1405215584iBay.comNaNE/POL-61164Sales50042020-01-31June 25 2020USD4.7825119.50
1405315585Understock.comSirius Satellite Radio XADH2 Home Access Kit f...E/SIR-83381Sales50042020-01-31February 01 2020USD33.16266.32
\n", "

13986 rows × 12 columns

\n", "
" ], "text/plain": [ " InvoiceNo Channel \\\n", "0 1532 Shoppe.com \n", "1 1533 Walcart \n", "2 1534 Bullseye \n", "3 1535 Bullseye \n", "5 1537 Bullseye \n", "... ... ... \n", "14049 15581 Bullseye \n", "14050 15582 Bullseye \n", "14051 15583 Understock.com \n", "14052 15584 iBay.com \n", "14053 15585 Understock.com \n", "\n", " Product Name ProductID \\\n", "0 Cannon Water Bomb Balloons 100 Pack T&G/CAN-97509 \n", "1 LEGO Ninja Turtles Stealth Shell in Pursuit 79102 T&G/LEG-37777 \n", "2 NaN T&G/PET-14209 \n", "3 Transformers Age of Extinction Generations Del... T&G/TRA-20170 \n", "5 3x Anti-Spy Privacy Screen Protector Compatibl... CP&A/3X-00445 \n", "... ... ... \n", "14049 AC Adapter/Power Supply&Cord for Lenovo 3000 G... E/AC-63975 \n", "14050 Cisco Systems Gigabit VPN Router (RV320K9NA) E/CIS-74992 \n", "14051 Philips AJ3116M/37 Digital Tuning Clock Radio ... E/PHI-08100 \n", "14052 NaN E/POL-61164 \n", "14053 Sirius Satellite Radio XADH2 Home Access Kit f... E/SIR-83381 \n", "\n", " Account AccountNo Date Deadline Currency \\\n", "0 Sales 5004 2020-01-01 11/23/19 USD \n", "1 Sales 5004 2020-01-01 06/15/20 USD \n", "2 Sales 5004 2020-01-01 05/07/20 USD \n", "3 Sales 5004 2020-01-01 12/22/19 USD \n", "5 Sales 5004 2020-01-01 Mon Jan 20 00:00:00 2020 USD \n", "... ... ... ... ... ... \n", "14049 Sales 5004 2020-01-31 February 23 2020 USD \n", "14050 Sales 5004 2020-01-31 January 21 2020 USD \n", "14051 Sales 5004 2020-01-31 March 22 2020 USD \n", "14052 Sales 5004 2020-01-31 June 25 2020 USD \n", "14053 Sales 5004 2020-01-31 February 01 2020 USD \n", "\n", " Unit Price Quantity Total \n", "0 20.11 14 281.54 \n", "1 6.70 1 6.70 \n", "2 11.67 5 58.35 \n", "3 13.46 6 80.76 \n", "5 7.39 8 59.12 \n", "... ... ... ... \n", "14049 28.72 8 229.76 \n", "14050 33.39 1 33.39 \n", "14051 4.18 1 4.18 \n", "14052 4.78 25 119.50 \n", "14053 33.16 2 66.32 \n", "\n", "[13986 rows x 12 columns]" ] }, "execution_count": 49, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ledger_df.drop_duplicates(subset=['InvoiceNo', 'ProductID'])" ] }, { "cell_type": "markdown", "id": "anticipated-xerox", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "## Writing Excel files" ] }, { "cell_type": "code", "execution_count": 52, "id": "loaded-queens", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [], "source": [ "ledger_df.to_excel('JanQ1Sales.xlsx')" ] }, { "cell_type": "code", "execution_count": 53, "id": "personalized-hazard", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [], "source": [ "ledger_df.to_excel('JanQ1Sales.xlsx', sheet_name='Sales')" ] }, { "cell_type": "code", "execution_count": 54, "id": "transparent-referral", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [], "source": [ "ledger_df.to_excel('JanQ1Sales.xlsx', sheet_name='Sales', index=False)" ] }, { "cell_type": "code", "execution_count": null, "id": "speaking-asthma", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [], "source": [ "jan_sales_df = pd.read_excel('Q1Sales.xlsx', sheet_name='January')\n", "\n", "jan_sales_df" ] }, { "cell_type": "markdown", "id": "62ee0067", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "# Organizing sales data by channel" ] }, { "cell_type": "code", "execution_count": 57, "id": "provincial-liechtenstein", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [], "source": [ "jan_sales_df = pd.read_excel('data/Q1Sales.xlsx', sheet_name='January')\n", "feb_sales_df = pd.read_excel('data/Q1Sales.xlsx', sheet_name='February')\n", "mar_sales_df = pd.read_excel('data/Q1Sales.xlsx', sheet_name='March')" ] }, { "cell_type": "code", "execution_count": 58, "id": "critical-rebel", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [], "source": [ "sales_df = pd.concat([jan_sales_df, feb_sales_df, mar_sales_df], ignore_index=True)" ] }, { "cell_type": "code", "execution_count": 59, "id": "conscious-acrobat", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
InvoiceNoChannelProduct NameProductIDAccountAccountNoDateDeadlineCurrencyUnit PriceQuantityTotal
01532Shoppe.comCannon Water Bomb Balloons 100 PackT&G/CAN-97509Sales50042020-01-0111/23/19USD20.1114281.54
11533WalcartLEGO Ninja Turtles Stealth Shell in Pursuit 79102T&G/LEG-37777Sales50042020-01-0106/15/20USD6.7016.70
21534BullseyeNaNT&G/PET-14209Sales50042020-01-0105/07/20USD11.67558.35
31535BullseyeTransformers Age of Extinction Generations Del...T&G/TRA-20170Sales50042020-01-0112/22/19USD13.46680.76
41535BullseyeTransformers Age of Extinction Generations Del...T&G/TRA-20170Sales50042020-01-0112/22/19USD13.46680.76
.......................................
3770339235iBay.comNature's Bounty Garlic, 2000mg, Odor-Free, 120...H&PC/NAT-15470Sales50042020-03-31Thu Sep 17 00:00:00 2020USD5.55211.10
3770439216Shoppe.comFunko Wonder Woman POP HeroesT&G/FUN-03366Sales50042020-03-315-08-20USD28.56128.56
3770539219Shoppe.comMONO GS1 GS1-BTY-BLK-L Betty Long Guitar Strap...MI/MON-86723Sales50042020-03-3104/11/20USD3.3313.33
3770639238Shoppe.comNaNT&G/MAG-68412Sales50042020-03-313-20-20USD34.7610347.60
3770739239Understock.com3 Collapsible Bowl Set 32oz | 16oz | 4ozK&D/3 C-07383Sales50042020-03-31April 02 2020USD6.391595.85
\n", "

37708 rows × 12 columns

\n", "
" ], "text/plain": [ " InvoiceNo Channel \\\n", "0 1532 Shoppe.com \n", "1 1533 Walcart \n", "2 1534 Bullseye \n", "3 1535 Bullseye \n", "4 1535 Bullseye \n", "... ... ... \n", "37703 39235 iBay.com \n", "37704 39216 Shoppe.com \n", "37705 39219 Shoppe.com \n", "37706 39238 Shoppe.com \n", "37707 39239 Understock.com \n", "\n", " Product Name ProductID \\\n", "0 Cannon Water Bomb Balloons 100 Pack T&G/CAN-97509 \n", "1 LEGO Ninja Turtles Stealth Shell in Pursuit 79102 T&G/LEG-37777 \n", "2 NaN T&G/PET-14209 \n", "3 Transformers Age of Extinction Generations Del... T&G/TRA-20170 \n", "4 Transformers Age of Extinction Generations Del... T&G/TRA-20170 \n", "... ... ... \n", "37703 Nature's Bounty Garlic, 2000mg, Odor-Free, 120... H&PC/NAT-15470 \n", "37704 Funko Wonder Woman POP Heroes T&G/FUN-03366 \n", "37705 MONO GS1 GS1-BTY-BLK-L Betty Long Guitar Strap... MI/MON-86723 \n", "37706 NaN T&G/MAG-68412 \n", "37707 3 Collapsible Bowl Set 32oz | 16oz | 4oz K&D/3 C-07383 \n", "\n", " Account AccountNo Date Deadline Currency \\\n", "0 Sales 5004 2020-01-01 11/23/19 USD \n", "1 Sales 5004 2020-01-01 06/15/20 USD \n", "2 Sales 5004 2020-01-01 05/07/20 USD \n", "3 Sales 5004 2020-01-01 12/22/19 USD \n", "4 Sales 5004 2020-01-01 12/22/19 USD \n", "... ... ... ... ... ... \n", "37703 Sales 5004 2020-03-31 Thu Sep 17 00:00:00 2020 USD \n", "37704 Sales 5004 2020-03-31 5-08-20 USD \n", "37705 Sales 5004 2020-03-31 04/11/20 USD \n", "37706 Sales 5004 2020-03-31 3-20-20 USD \n", "37707 Sales 5004 2020-03-31 April 02 2020 USD \n", "\n", " Unit Price Quantity Total \n", "0 20.11 14 281.54 \n", "1 6.70 1 6.70 \n", "2 11.67 5 58.35 \n", "3 13.46 6 80.76 \n", "4 13.46 6 80.76 \n", "... ... ... ... \n", "37703 5.55 2 11.10 \n", "37704 28.56 1 28.56 \n", "37705 3.33 1 3.33 \n", "37706 34.76 10 347.60 \n", "37707 6.39 15 95.85 \n", "\n", "[37708 rows x 12 columns]" ] }, "execution_count": 59, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sales_df" ] }, { "cell_type": "code", "execution_count": 61, "id": "coordinated-prayer", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [], "source": [ "channel = 'Understock.com'\n", "channel_df = sales_df[sales_df['Channel'] == channel]\n", "channel_df = channel_df.sort_values(['Total', 'Quantity'], ascending=False)" ] }, { "cell_type": "code", "execution_count": 62, "id": "intimate-receipt", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
InvoiceNoChannelProduct NameProductIDAccountAccountNoDateDeadlineCurrencyUnit PriceQuantityTotal
2362025152Understock.comLarge Display Digital Thermometer LumiscopeH&PC/LAR-98606Sales50042020-02-21Sun Dec 22 00:00:00 2019USD64.32543473.28
3662138153Understock.comLarge Display Digital Thermometer LumiscopeH&PC/LAR-98606Sales50042020-03-26Sun Apr 19 00:00:00 2020USD64.32543473.28
47476279Understock.comAC Adapter/battery charger for GateWay Liteon ...E/AC-44106Sales50042020-01-102020/02/05USD14.882263362.88
47576162Understock.comAC Adapter/battery charger for GateWay Liteon ...E/AC-44106Sales50042020-01-102020/02/05USD14.882263362.88
49696397Understock.comAC Adapter/battery charger for GateWay Liteon ...E/AC-44106Sales50042020-01-102020/02/05USD14.882263362.88
.......................................
1567917002Understock.comUrban Rebounding Workout DVD, Compilation 1M&T/URB-83617Sales50042020-02-04July 02 2020USD1.6911.69
1287714409Understock.comMagic: the Gathering - Hydra Broodmaster (126/...T&G/MAG-22549Sales50042020-01-294-13-20USD0.06171.02
1585517387Understock.comMagic: the Gathering - Hydra Broodmaster (126/...T&G/MAG-22549Sales50042020-02-051-14-20USD0.06110.66
2401225544Understock.comMagic: the Gathering - Hydra Broodmaster (126/...T&G/MAG-22549Sales50042020-02-226-19-20USD0.06110.66
2402225466Understock.comMagic: the Gathering - Hydra Broodmaster (126/...T&G/MAG-22549Sales50042020-02-226-19-20USD0.06110.66
\n", "

13188 rows × 12 columns

\n", "
" ], "text/plain": [ " InvoiceNo Channel \\\n", "23620 25152 Understock.com \n", "36621 38153 Understock.com \n", "4747 6279 Understock.com \n", "4757 6162 Understock.com \n", "4969 6397 Understock.com \n", "... ... ... \n", "15679 17002 Understock.com \n", "12877 14409 Understock.com \n", "15855 17387 Understock.com \n", "24012 25544 Understock.com \n", "24022 25466 Understock.com \n", "\n", " Product Name ProductID \\\n", "23620 Large Display Digital Thermometer Lumiscope H&PC/LAR-98606 \n", "36621 Large Display Digital Thermometer Lumiscope H&PC/LAR-98606 \n", "4747 AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 \n", "4757 AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 \n", "4969 AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 \n", "... ... ... \n", "15679 Urban Rebounding Workout DVD, Compilation 1 M&T/URB-83617 \n", "12877 Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 \n", "15855 Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 \n", "24012 Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 \n", "24022 Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 \n", "\n", " Account AccountNo Date Deadline Currency \\\n", "23620 Sales 5004 2020-02-21 Sun Dec 22 00:00:00 2019 USD \n", "36621 Sales 5004 2020-03-26 Sun Apr 19 00:00:00 2020 USD \n", "4747 Sales 5004 2020-01-10 2020/02/05 USD \n", "4757 Sales 5004 2020-01-10 2020/02/05 USD \n", "4969 Sales 5004 2020-01-10 2020/02/05 USD \n", "... ... ... ... ... ... \n", "15679 Sales 5004 2020-02-04 July 02 2020 USD \n", "12877 Sales 5004 2020-01-29 4-13-20 USD \n", "15855 Sales 5004 2020-02-05 1-14-20 USD \n", "24012 Sales 5004 2020-02-22 6-19-20 USD \n", "24022 Sales 5004 2020-02-22 6-19-20 USD \n", "\n", " Unit Price Quantity Total \n", "23620 64.32 54 3473.28 \n", "36621 64.32 54 3473.28 \n", "4747 14.88 226 3362.88 \n", "4757 14.88 226 3362.88 \n", "4969 14.88 226 3362.88 \n", "... ... ... ... \n", "15679 1.69 1 1.69 \n", "12877 0.06 17 1.02 \n", "15855 0.06 11 0.66 \n", "24012 0.06 11 0.66 \n", "24022 0.06 11 0.66 \n", "\n", "[13188 rows x 12 columns]" ] }, "execution_count": 62, "metadata": {}, "output_type": "execute_result" } ], "source": [ "channel_df" ] }, { "cell_type": "code", "execution_count": 63, "id": "immune-disco", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [], "source": [ "channel_df.to_excel('Q1ChannelSales.xlsx', sheet_name='Understock.com', index=False)" ] }, { "cell_type": "code", "execution_count": 64, "id": "rapid-polish", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Bullseye\n", "iBay.com\n", "Shoppe.com\n", "Understock.com\n", "Walcart\n" ] } ], "source": [ "channels = ['Bullseye', 'iBay.com', 'Shoppe.com', 'Understock.com', 'Walcart']\n", "\n", "for channel in channels:\n", " print(channel)" ] }, { "cell_type": "code", "execution_count": 65, "id": "mechanical-wonder", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [], "source": [ "channels = ['Bullseye', 'iBay.com', 'Shoppe.com', 'Understock.com', 'Walcart']\n", "\n", "for channel in channels:\n", " channel_df = sales_df[sales_df['Channel'] == channel]\n", " channel_df = channel_df.sort_values(['Total', 'Quantity'], ascending=False)" ] }, { "cell_type": "code", "execution_count": 66, "id": "differential-assurance", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
InvoiceNoChannelProduct NameProductIDAccountAccountNoDateDeadlineCurrencyUnit PriceQuantityTotal
24613993WalcartLarge Display Digital Thermometer LumiscopeH&PC/LAR-98606Sales50042020-01-06Mon Jun 22 00:00:00 2020USD64.47231482.81
83329864WalcartAC Adapter/battery charger for GateWay Liteon ...E/AC-44106Sales50042020-01-182019/12/02USD14.491001449.00
1529316825WalcartAC Adapter/battery charger for GateWay Liteon ...E/AC-44106Sales50042020-02-042019/11/29USD14.491001449.00
1550916895WalcartAC Adapter/battery charger for GateWay Liteon ...E/AC-44106Sales50042020-02-042019/11/29USD14.491001449.00
2388825420WalcartAC Adapter/battery charger for GateWay Liteon ...E/AC-44106Sales50042020-02-212020/02/14USD14.491001449.00
.......................................
9362332WalcartVibrating Slim Jelly Dong with Suction Cup 7.5...H&PC/VIB-56252Sales50042020-01-02Thu Jan 2 00:00:00 2020USD2.1012.10
1122212754WalcartVibrating Slim Jelly Dong with Suction Cup 7.5...H&PC/VIB-56252Sales50042020-01-25Wed Dec 18 00:00:00 2019USD2.1012.10
1418615718WalcartBlackberry Q10 White 16GB Factory Unlocked, In...CP&A/BLA-26278Sales50042020-02-01Tue Mar 10 00:00:00 2020USD1.8711.87
1431115712WalcartBlackberry Q10 White 16GB Factory Unlocked, In...CP&A/BLA-26278Sales50042020-02-01Tue Mar 10 00:00:00 2020USD1.8711.87
3362735159WalcartRed Dragon VT 3-30 C 500,000 BTU Heavy Duty Pr...PL&G/RED-46561Sales50042020-03-1507/19/20USD0.1961.14
\n", "

4574 rows × 12 columns

\n", "
" ], "text/plain": [ " InvoiceNo Channel Product Name \\\n", "2461 3993 Walcart Large Display Digital Thermometer Lumiscope \n", "8332 9864 Walcart AC Adapter/battery charger for GateWay Liteon ... \n", "15293 16825 Walcart AC Adapter/battery charger for GateWay Liteon ... \n", "15509 16895 Walcart AC Adapter/battery charger for GateWay Liteon ... \n", "23888 25420 Walcart AC Adapter/battery charger for GateWay Liteon ... \n", "... ... ... ... \n", "936 2332 Walcart Vibrating Slim Jelly Dong with Suction Cup 7.5... \n", "11222 12754 Walcart Vibrating Slim Jelly Dong with Suction Cup 7.5... \n", "14186 15718 Walcart Blackberry Q10 White 16GB Factory Unlocked, In... \n", "14311 15712 Walcart Blackberry Q10 White 16GB Factory Unlocked, In... \n", "33627 35159 Walcart Red Dragon VT 3-30 C 500,000 BTU Heavy Duty Pr... \n", "\n", " ProductID Account AccountNo Date Deadline \\\n", "2461 H&PC/LAR-98606 Sales 5004 2020-01-06 Mon Jun 22 00:00:00 2020 \n", "8332 E/AC-44106 Sales 5004 2020-01-18 2019/12/02 \n", "15293 E/AC-44106 Sales 5004 2020-02-04 2019/11/29 \n", "15509 E/AC-44106 Sales 5004 2020-02-04 2019/11/29 \n", "23888 E/AC-44106 Sales 5004 2020-02-21 2020/02/14 \n", "... ... ... ... ... ... \n", "936 H&PC/VIB-56252 Sales 5004 2020-01-02 Thu Jan 2 00:00:00 2020 \n", "11222 H&PC/VIB-56252 Sales 5004 2020-01-25 Wed Dec 18 00:00:00 2019 \n", "14186 CP&A/BLA-26278 Sales 5004 2020-02-01 Tue Mar 10 00:00:00 2020 \n", "14311 CP&A/BLA-26278 Sales 5004 2020-02-01 Tue Mar 10 00:00:00 2020 \n", "33627 PL&G/RED-46561 Sales 5004 2020-03-15 07/19/20 \n", "\n", " Currency Unit Price Quantity Total \n", "2461 USD 64.47 23 1482.81 \n", "8332 USD 14.49 100 1449.00 \n", "15293 USD 14.49 100 1449.00 \n", "15509 USD 14.49 100 1449.00 \n", "23888 USD 14.49 100 1449.00 \n", "... ... ... ... ... \n", "936 USD 2.10 1 2.10 \n", "11222 USD 2.10 1 2.10 \n", "14186 USD 1.87 1 1.87 \n", "14311 USD 1.87 1 1.87 \n", "33627 USD 0.19 6 1.14 \n", "\n", "[4574 rows x 12 columns]" ] }, "execution_count": 66, "metadata": {}, "output_type": "execute_result" } ], "source": [ "channel_df" ] }, { "cell_type": "code", "execution_count": 67, "id": "impaired-purpose", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [], "source": [ "channels = ['Bullseye', 'iBay.com', 'Shoppe.com', 'Understock.com', 'Walcart']\n", "\n", "output_file = pd.ExcelWriter('Q1ChannelSales.xlsx')\n", "\n", "for channel in channels:\n", " channel_df = sales_df[sales_df['Channel'] == channel]\n", " channel_df = channel_df.sort_values(['Total', 'Quantity'], ascending=False)\n", " channel_df.to_excel(output_file, sheet_name=channel, index=False)\n", " \n", "output_file.save()" ] }, { "cell_type": "code", "execution_count": 68, "id": "freelance-damage", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [], "source": [ "with pd.ExcelWriter('Q1ChannelSales.xlsx') as output_file:\n", " for channel in channels:\n", " channel_df = sales_df[sales_df['Channel'] == channel]\n", " channel_df = channel_df.sort_values(['Total', 'Quantity'], ascending=False)\n", " channel_df.to_excel(output_file, sheet_name=channel, index=False)" ] }, { "cell_type": "code", "execution_count": 70, "id": "worst-credits", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [], "source": [ "sales_df = pd.concat([\n", " pd.read_excel('data/Q1Sales.xlsx', sheet_name='January'), \n", " pd.read_excel('data/Q1Sales.xlsx', sheet_name='February'), \n", " pd.read_excel('data/Q1Sales.xlsx', sheet_name='March')\n", " ], ignore_index=True)\n", "channels = ['Bullseye', 'iBay.com', 'Shoppe.com', 'Understock.com', 'Walcart']\n", "\n", "with pd.ExcelWriter('Q1ChannelSales.xlsx') as output_file:\n", " for channel in channels:\n", " channel_df = sales_df[sales_df['Channel'] == channel]\n", " channel_df = channel_df.sort_values(['Total', 'Quantity'], ascending=False)\n", " channel_df.to_excel(output_file, sheet_name=channel, index=False)" ] }, { "cell_type": "code", "execution_count": 71, "id": "naval-george", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "outputs": [], "source": [ "ledger_df.to_csv('JanQ1Sales.csv', index=False)" ] } ], "metadata": { "celltoolbar": "Slideshow", "jupytext": { "formats": "ipynb" }, "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.7" }, "rise": { "auto_select": "first", "autolaunch": false, "enable_chalkboard": true, "start_slideshow_at": "selected", "theme": "black" } }, "nbformat": 4, "nbformat_minor": 5 }