Reading and Cleaning Excel Files¶

Feng Li

School of Statistics and Mathematics

Central University of Finance and Economics

feng.li@cufe.edu.cn

https://feng.li/python

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.

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.

Reading and writing Excel files¶

In [1]:
import pandas as pd
In [2]:
ledger_df_info = pd.ExcelFile('data/Q1Sales.xlsx')
ledger_df_info.sheet_names
Out[2]:
['January', 'February', 'March']
In [3]:
ledger_df = pd.read_excel('data/Q1Sales.xlsx') # read the first sheet
In [4]:
ledger_df
Out[4]:
InvoiceNo Channel Product Name ProductID Account AccountNo Date Deadline Currency Unit Price Quantity Total
0 1532 Shoppe.com Cannon Water Bomb Balloons 100 Pack T&G/CAN-97509 Sales 5004 2020-01-01 11/23/19 USD 20.11 14 281.54
1 1533 Walcart LEGO Ninja Turtles Stealth Shell in Pursuit 79102 T&G/LEG-37777 Sales 5004 2020-01-01 06/15/20 USD 6.70 1 6.70
2 1534 Bullseye NaN T&G/PET-14209 Sales 5004 2020-01-01 05/07/20 USD 11.67 5 58.35
3 1535 Bullseye Transformers Age of Extinction Generations Del... T&G/TRA-20170 Sales 5004 2020-01-01 12/22/19 USD 13.46 6 80.76
4 1535 Bullseye Transformers Age of Extinction Generations Del... T&G/TRA-20170 Sales 5004 2020-01-01 12/22/19 USD 13.46 6 80.76
... ... ... ... ... ... ... ... ... ... ... ... ...
14049 15581 Bullseye AC Adapter/Power Supply&Cord for Lenovo 3000 G... E/AC-63975 Sales 5004 2020-01-31 February 23 2020 USD 28.72 8 229.76
14050 15582 Bullseye Cisco Systems Gigabit VPN Router (RV320K9NA) E/CIS-74992 Sales 5004 2020-01-31 January 21 2020 USD 33.39 1 33.39
14051 15583 Understock.com Philips AJ3116M/37 Digital Tuning Clock Radio ... E/PHI-08100 Sales 5004 2020-01-31 March 22 2020 USD 4.18 1 4.18
14052 15584 iBay.com NaN E/POL-61164 Sales 5004 2020-01-31 June 25 2020 USD 4.78 25 119.50
14053 15585 Understock.com Sirius Satellite Radio XADH2 Home Access Kit f... E/SIR-83381 Sales 5004 2020-01-31 February 01 2020 USD 33.16 2 66.32

14054 rows × 12 columns

  • We could read a specified sheet of the Excel file
In [5]:
ledger_df = pd.read_excel('data/Q1Sales.xlsx', sheet_name='March')
In [6]:
jan_ledger_df = pd.read_excel('data/Q1Sales.xlsx')
feb_ledger_df = pd.read_excel('data/Q1Sales.xlsx', sheet_name='February')
mar_ledger_df = pd.read_excel('data/Q1Sales.xlsx', sheet_name='March')
In [9]:
ledger_df = pd.read_excel('data/Q1Sales.xlsx', sheet_name=0)
  • Let's inspect the data
In [10]:
ledger_df
Out[10]:
InvoiceNo Channel Product Name ProductID Account AccountNo Date Deadline Currency Unit Price Quantity Total
0 1532 Shoppe.com Cannon Water Bomb Balloons 100 Pack T&G/CAN-97509 Sales 5004 2020-01-01 11/23/19 USD 20.11 14 281.54
1 1533 Walcart LEGO Ninja Turtles Stealth Shell in Pursuit 79102 T&G/LEG-37777 Sales 5004 2020-01-01 06/15/20 USD 6.70 1 6.70
2 1534 Bullseye NaN T&G/PET-14209 Sales 5004 2020-01-01 05/07/20 USD 11.67 5 58.35
3 1535 Bullseye Transformers Age of Extinction Generations Del... T&G/TRA-20170 Sales 5004 2020-01-01 12/22/19 USD 13.46 6 80.76
4 1535 Bullseye Transformers Age of Extinction Generations Del... T&G/TRA-20170 Sales 5004 2020-01-01 12/22/19 USD 13.46 6 80.76
... ... ... ... ... ... ... ... ... ... ... ... ...
14049 15581 Bullseye AC Adapter/Power Supply&Cord for Lenovo 3000 G... E/AC-63975 Sales 5004 2020-01-31 February 23 2020 USD 28.72 8 229.76
14050 15582 Bullseye Cisco Systems Gigabit VPN Router (RV320K9NA) E/CIS-74992 Sales 5004 2020-01-31 January 21 2020 USD 33.39 1 33.39
14051 15583 Understock.com Philips AJ3116M/37 Digital Tuning Clock Radio ... E/PHI-08100 Sales 5004 2020-01-31 March 22 2020 USD 4.18 1 4.18
14052 15584 iBay.com NaN E/POL-61164 Sales 5004 2020-01-31 June 25 2020 USD 4.78 25 119.50
14053 15585 Understock.com Sirius Satellite Radio XADH2 Home Access Kit f... E/SIR-83381 Sales 5004 2020-01-31 February 01 2020 USD 33.16 2 66.32

14054 rows × 12 columns

In [11]:
ledger_df.head(3)
Out[11]:
InvoiceNo Channel Product Name ProductID Account AccountNo Date Deadline Currency Unit Price Quantity Total
0 1532 Shoppe.com Cannon Water Bomb Balloons 100 Pack T&G/CAN-97509 Sales 5004 2020-01-01 11/23/19 USD 20.11 14 281.54
1 1533 Walcart LEGO Ninja Turtles Stealth Shell in Pursuit 79102 T&G/LEG-37777 Sales 5004 2020-01-01 06/15/20 USD 6.70 1 6.70
2 1534 Bullseye NaN T&G/PET-14209 Sales 5004 2020-01-01 05/07/20 USD 11.67 5 58.35
In [12]:
ledger_df.tail(3)
Out[12]:
InvoiceNo Channel Product Name ProductID Account AccountNo Date Deadline Currency Unit Price Quantity Total
14051 15583 Understock.com Philips AJ3116M/37 Digital Tuning Clock Radio ... E/PHI-08100 Sales 5004 2020-01-31 March 22 2020 USD 4.18 1 4.18
14052 15584 iBay.com NaN E/POL-61164 Sales 5004 2020-01-31 June 25 2020 USD 4.78 25 119.50
14053 15585 Understock.com Sirius Satellite Radio XADH2 Home Access Kit f... E/SIR-83381 Sales 5004 2020-01-31 February 01 2020 USD 33.16 2 66.32
In [13]:
ledger_df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 14054 entries, 0 to 14053
Data columns (total 12 columns):
 #   Column        Non-Null Count  Dtype         
---  ------        --------------  -----         
 0   InvoiceNo     14054 non-null  int64         
 1   Channel       14054 non-null  object        
 2   Product Name  12362 non-null  object        
 3   ProductID     14054 non-null  object        
 4   Account       14054 non-null  object        
 5   AccountNo     14054 non-null  int64         
 6   Date          14054 non-null  datetime64[ns]
 7   Deadline      14054 non-null  object        
 8   Currency      14054 non-null  object        
 9   Unit Price    14054 non-null  float64       
 10  Quantity      14054 non-null  int64         
 11  Total         14054 non-null  float64       
dtypes: datetime64[ns](1), float64(2), int64(3), object(6)
memory usage: 1.3+ MB
In [14]:
ledger_df.shape
Out[14]:
(14054, 12)

Work with Tables¶

In [15]:
ledger_df.columns
Out[15]:
Index(['InvoiceNo', 'Channel', 'Product Name', 'ProductID', 'Account',
       'AccountNo', 'Date', 'Deadline', 'Currency', 'Unit Price', 'Quantity',
       'Total'],
      dtype='object')
In [16]:
ledger_df['Product Name']
Out[16]:
0                      Cannon Water Bomb Balloons 100 Pack
1        LEGO Ninja Turtles Stealth Shell in Pursuit 79102
2                                                      NaN
3        Transformers Age of Extinction Generations Del...
4        Transformers Age of Extinction Generations Del...
                               ...                        
14049    AC Adapter/Power Supply&Cord for Lenovo 3000 G...
14050         Cisco Systems Gigabit VPN Router (RV320K9NA)
14051    Philips AJ3116M/37 Digital Tuning Clock Radio ...
14052                                                  NaN
14053    Sirius Satellite Radio XADH2 Home Access Kit f...
Name: Product Name, Length: 14054, dtype: object
In [17]:
ledger_df['Unit Price']
Out[17]:
0        20.11
1         6.70
2        11.67
3        13.46
4        13.46
         ...  
14049    28.72
14050    33.39
14051     4.18
14052     4.78
14053    33.16
Name: Unit Price, Length: 14054, dtype: float64
In [18]:
ledger_df['Unit Price']
Out[18]:
0        20.11
1         6.70
2        11.67
3        13.46
4        13.46
         ...  
14049    28.72
14050    33.39
14051     4.18
14052     4.78
14053    33.16
Name: Unit Price, Length: 14054, dtype: float64
In [19]:
ledger_df['Unit Price'].median()
Out[19]:
10.22
In [20]:
ledger_df[['ProductID', 'Product Name', 'Unit Price', 'Total']]
Out[20]:
ProductID Product Name Unit Price Total
0 T&G/CAN-97509 Cannon Water Bomb Balloons 100 Pack 20.11 281.54
1 T&G/LEG-37777 LEGO Ninja Turtles Stealth Shell in Pursuit 79102 6.70 6.70
2 T&G/PET-14209 NaN 11.67 58.35
3 T&G/TRA-20170 Transformers Age of Extinction Generations Del... 13.46 80.76
4 T&G/TRA-20170 Transformers Age of Extinction Generations Del... 13.46 80.76
... ... ... ... ...
14049 E/AC-63975 AC Adapter/Power Supply&Cord for Lenovo 3000 G... 28.72 229.76
14050 E/CIS-74992 Cisco Systems Gigabit VPN Router (RV320K9NA) 33.39 33.39
14051 E/PHI-08100 Philips AJ3116M/37 Digital Tuning Clock Radio ... 4.18 4.18
14052 E/POL-61164 NaN 4.78 119.50
14053 E/SIR-83381 Sirius Satellite Radio XADH2 Home Access Kit f... 33.16 66.32

14054 rows × 4 columns

In [21]:
column_names = ['ProductID', 'Product Name', 'Unit Price', 'Total']

ledger_df[column_names]
Out[21]:
ProductID Product Name Unit Price Total
0 T&G/CAN-97509 Cannon Water Bomb Balloons 100 Pack 20.11 281.54
1 T&G/LEG-37777 LEGO Ninja Turtles Stealth Shell in Pursuit 79102 6.70 6.70
2 T&G/PET-14209 NaN 11.67 58.35
3 T&G/TRA-20170 Transformers Age of Extinction Generations Del... 13.46 80.76
4 T&G/TRA-20170 Transformers Age of Extinction Generations Del... 13.46 80.76
... ... ... ... ...
14049 E/AC-63975 AC Adapter/Power Supply&Cord for Lenovo 3000 G... 28.72 229.76
14050 E/CIS-74992 Cisco Systems Gigabit VPN Router (RV320K9NA) 33.39 33.39
14051 E/PHI-08100 Philips AJ3116M/37 Digital Tuning Clock Radio ... 4.18 4.18
14052 E/POL-61164 NaN 4.78 119.50
14053 E/SIR-83381 Sirius Satellite Radio XADH2 Home Access Kit f... 33.16 66.32

14054 rows × 4 columns

In [22]:
products_df = ledger_df[['ProductID', 'Product Name', 'Unit Price', 'Total']]

products_df
Out[22]:
ProductID Product Name Unit Price Total
0 T&G/CAN-97509 Cannon Water Bomb Balloons 100 Pack 20.11 281.54
1 T&G/LEG-37777 LEGO Ninja Turtles Stealth Shell in Pursuit 79102 6.70 6.70
2 T&G/PET-14209 NaN 11.67 58.35
3 T&G/TRA-20170 Transformers Age of Extinction Generations Del... 13.46 80.76
4 T&G/TRA-20170 Transformers Age of Extinction Generations Del... 13.46 80.76
... ... ... ... ...
14049 E/AC-63975 AC Adapter/Power Supply&Cord for Lenovo 3000 G... 28.72 229.76
14050 E/CIS-74992 Cisco Systems Gigabit VPN Router (RV320K9NA) 33.39 33.39
14051 E/PHI-08100 Philips AJ3116M/37 Digital Tuning Clock Radio ... 4.18 4.18
14052 E/POL-61164 NaN 4.78 119.50
14053 E/SIR-83381 Sirius Satellite Radio XADH2 Home Access Kit f... 33.16 66.32

14054 rows × 4 columns

In [23]:
ledger_df[[name for name in ledger_df.columns if 'Product' in name]]
Out[23]:
Product Name ProductID
0 Cannon Water Bomb Balloons 100 Pack T&G/CAN-97509
1 LEGO Ninja Turtles Stealth Shell in Pursuit 79102 T&G/LEG-37777
2 NaN T&G/PET-14209
3 Transformers Age of Extinction Generations Del... T&G/TRA-20170
4 Transformers Age of Extinction Generations Del... T&G/TRA-20170
... ... ...
14049 AC Adapter/Power Supply&Cord for Lenovo 3000 G... E/AC-63975
14050 Cisco Systems Gigabit VPN Router (RV320K9NA) E/CIS-74992
14051 Philips AJ3116M/37 Digital Tuning Clock Radio ... E/PHI-08100
14052 NaN E/POL-61164
14053 Sirius Satellite Radio XADH2 Home Access Kit f... E/SIR-83381

14054 rows × 2 columns

  • We could remove a column from the table (Not the Excel)
In [24]:
products_df = ledger_df[['ProductID', 'Product Name', 'Unit Price', 'Total']]
In [25]:
columns_to_remove = ['InvoiceNo', 'Account', 'AccountNo', 'Currency']
ledger_df.drop(columns_to_remove, axis='columns')
Out[25]:
Channel Product Name ProductID Date Deadline Unit Price Quantity Total
0 Shoppe.com Cannon Water Bomb Balloons 100 Pack T&G/CAN-97509 2020-01-01 11/23/19 20.11 14 281.54
1 Walcart LEGO Ninja Turtles Stealth Shell in Pursuit 79102 T&G/LEG-37777 2020-01-01 06/15/20 6.70 1 6.70
2 Bullseye NaN T&G/PET-14209 2020-01-01 05/07/20 11.67 5 58.35
3 Bullseye Transformers Age of Extinction Generations Del... T&G/TRA-20170 2020-01-01 12/22/19 13.46 6 80.76
4 Bullseye Transformers Age of Extinction Generations Del... T&G/TRA-20170 2020-01-01 12/22/19 13.46 6 80.76
... ... ... ... ... ... ... ... ...
14049 Bullseye AC Adapter/Power Supply&Cord for Lenovo 3000 G... E/AC-63975 2020-01-31 February 23 2020 28.72 8 229.76
14050 Bullseye Cisco Systems Gigabit VPN Router (RV320K9NA) E/CIS-74992 2020-01-31 January 21 2020 33.39 1 33.39
14051 Understock.com Philips AJ3116M/37 Digital Tuning Clock Radio ... E/PHI-08100 2020-01-31 March 22 2020 4.18 1 4.18
14052 iBay.com NaN E/POL-61164 2020-01-31 June 25 2020 4.78 25 119.50
14053 Understock.com Sirius Satellite Radio XADH2 Home Access Kit f... E/SIR-83381 2020-01-31 February 01 2020 33.16 2 66.32

14054 rows × 8 columns

  • Let's filter the data with given condition
In [26]:
ledger_df[ledger_df['Channel'] == 'Walcart']
Out[26]:
InvoiceNo Channel Product Name ProductID Account AccountNo Date Deadline Currency Unit Price Quantity Total
1 1533 Walcart LEGO Ninja Turtles Stealth Shell in Pursuit 79102 T&G/LEG-37777 Sales 5004 2020-01-01 06/15/20 USD 6.70 1 6.70
7 1539 Walcart Zen-Ray ED3 8x43 Binoculars C&P/ZEN-80993 Sales 5004 2020-01-01 6-28-20 USD 28.29 1 28.29
10 1542 Walcart Logisys Red 5 LED Lazer Light, 12V DC Input Vo... MI/LOG-93214 Sales 5004 2020-01-01 05/25/20 USD 19.49 1 19.49
11 1543 Walcart Magline GMK81UA4 Gemini Sr Convertible Hand Tr... I&S/MAG-94877 Sales 5004 2020-01-01 05/06/20 USD 18.42 4 73.68
15 1547 Walcart Totally Bamboo 20-8551 3-Tiered Salt Box K&D/TOT-99233 Sales 5004 2020-01-01 March 14 2020 USD 4.25 1 4.25
... ... ... ... ... ... ... ... ... ... ... ... ...
14012 15408 Walcart OXO Good Grips Large Silicone Drying Mat H&K/OXO-69937 Sales 5004 2020-01-31 2020/02/09 USD 10.10 1 10.10
14022 15427 Walcart Applied Nutrition Dual Action Cleanse Kit with... H&PC/APP-41898 Sales 5004 2020-01-31 Wed May 13 00:00:00 2020 USD 7.72 1 7.72
14028 15459 Walcart Update International WPP-1236 Rubber Wood Pizz... K&D/UPD-02237 Sales 5004 2020-01-31 June 13 2020 USD 3.91 1 3.91
14034 15471 Walcart Kikkerland Biodegradable Paper Straws, Gray an... K&D/KIK-89065 Sales 5004 2020-01-31 February 22 2020 USD 17.18 16 274.88
14039 15499 Walcart Anchor Hocking 4-Piece Palladian Cherry Window... K&D/ANC-42628 Sales 5004 2020-01-31 April 22 2020 USD 7.56 44 332.64

1851 rows × 12 columns

In [27]:
ledger_df[
    (ledger_df['Channel'] == 'Walcart') &
    (ledger_df['Quantity'] > 25)
]
Out[27]:
InvoiceNo Channel Product Name ProductID Account AccountNo Date Deadline Currency Unit Price Quantity Total
28 1560 Walcart Conntek 14422 RV Pigtail Adapter 15 Amp Male P... PL&G/CON-18732 Sales 5004 2020-01-01 03/26/20 USD 4.47 27 120.69
144 1676 Walcart [ Strip of 6 ] Energizer A76/LR44 (A76BP), SR4... I&S/[ S-49751 Sales 5004 2020-01-01 02/04/20 USD 11.83 61 721.63
213 1604 Walcart Conntek 14422 RV Pigtail Adapter 15 Amp Male P... PL&G/CON-18732 Sales 5004 2020-01-01 03/26/20 USD 4.47 27 120.69
237 1769 Walcart Child Construction Hats - 12 Pack - Yellow T&G/CHI-38293 Sales 5004 2020-01-01 3-31-20 USD 19.68 43 846.24
292 1824 Walcart Ultima Replenisher Lemonade 90 - Servings , 1... H&PC/ULT-64807 Sales 5004 2020-01-01 Thu Feb 27 00:00:00 2020 USD 13.34 62 827.08
... ... ... ... ... ... ... ... ... ... ... ... ...
13751 15144 Walcart Anchor Hocking 4-Piece Palladian Cherry Window... K&D/ANC-42628 Sales 5004 2020-01-31 April 22 2020 USD 7.56 44 332.64
13759 15291 Walcart AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 Sales 5004 2020-01-31 2020/01/05 USD 14.49 68 985.32
13827 15359 Walcart AKG Pro Audio K99 Perception Over-Ear Semi-Ope... MI/AKG-26798 Sales 5004 2020-01-31 02/16/20 USD 8.10 31 251.10
13897 15429 Walcart NaN H&PC/ULT-64807 Sales 5004 2020-01-31 Wed Jun 24 00:00:00 2020 USD 13.34 83 1107.22
14039 15499 Walcart Anchor Hocking 4-Piece Palladian Cherry Window... K&D/ANC-42628 Sales 5004 2020-01-31 April 22 2020 USD 7.56 44 332.64

86 rows × 12 columns

In [28]:
ledger_df['Channel'].isin(['Understock.com', 'iBay.com', 'Shoppe.com'])
Out[28]:
0         True
1        False
2        False
3        False
4        False
         ...  
14049    False
14050    False
14051     True
14052     True
14053     True
Name: Channel, Length: 14054, dtype: bool
In [29]:
ledger_df['Quantity'].between(10, 100)
Out[29]:
0         True
1        False
2        False
3        False
4        False
         ...  
14049    False
14050    False
14051    False
14052     True
14053    False
Name: Quantity, Length: 14054, dtype: bool
In [30]:
ledger_df.loc[
    ledger_df['Channel'] == 'Walcart', 
    ['Channel', 'Quantity', 'Total']
]
Out[30]:
Channel Quantity Total
1 Walcart 1 6.70
7 Walcart 1 28.29
10 Walcart 1 19.49
11 Walcart 4 73.68
15 Walcart 1 4.25
... ... ... ...
14012 Walcart 1 10.10
14022 Walcart 1 7.72
14028 Walcart 1 3.91
14034 Walcart 16 274.88
14039 Walcart 44 332.64

1851 rows × 3 columns

  • Sorting data
In [31]:
ledger_df.sort_values(by='Total')
Out[31]:
InvoiceNo Channel Product Name ProductID Account AccountNo Date Deadline Currency Unit Price Quantity Total
7613 9145 iBay.com Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 Sales 5004 2020-01-16 5-21-20 USD 0.06 15 0.90
12877 14409 Understock.com Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 Sales 5004 2020-01-29 4-13-20 USD 0.06 17 1.02
7810 9342 iBay.com Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 Sales 5004 2020-01-17 3-22-20 USD 0.06 26 1.56
7812 9270 iBay.com Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 Sales 5004 2020-01-17 3-22-20 USD 0.06 26 1.56
9822 11354 Understock.com Urban Rebounding Workout DVD, Compilation 1 M&T/URB-83617 Sales 5004 2020-01-22 June 23 2020 USD 1.69 1 1.69
... ... ... ... ... ... ... ... ... ... ... ... ...
4757 6162 Understock.com AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 Sales 5004 2020-01-10 2020/02/05 USD 14.88 226 3362.88
6163 7526 iBay.com Large Display Digital Thermometer Lumiscope H&PC/LAR-98606 Sales 5004 2020-01-13 Sun Jun 7 00:00:00 2020 USD 64.15 56 3592.40
6141 7673 iBay.com Large Display Digital Thermometer Lumiscope H&PC/LAR-98606 Sales 5004 2020-01-13 Sun Jun 7 00:00:00 2020 USD 64.15 56 3592.40
8006 9538 iBay.com Large Display Digital Thermometer Lumiscope H&PC/LAR-98606 Sales 5004 2020-01-17 Fri Feb 14 00:00:00 2020 USD 64.15 61 3913.15
5212 6744 iBay.com Large Display Digital Thermometer Lumiscope H&PC/LAR-98606 Sales 5004 2020-01-11 Tue Apr 28 00:00:00 2020 USD 64.15 68 4362.20

14054 rows × 12 columns

In [32]:
ledger_df.sort_values(by='Total', ascending=False)
Out[32]:
InvoiceNo Channel Product Name ProductID Account AccountNo Date Deadline Currency Unit Price Quantity Total
5212 6744 iBay.com Large Display Digital Thermometer Lumiscope H&PC/LAR-98606 Sales 5004 2020-01-11 Tue Apr 28 00:00:00 2020 USD 64.15 68 4362.20
8006 9538 iBay.com Large Display Digital Thermometer Lumiscope H&PC/LAR-98606 Sales 5004 2020-01-17 Fri Feb 14 00:00:00 2020 USD 64.15 61 3913.15
6141 7673 iBay.com Large Display Digital Thermometer Lumiscope H&PC/LAR-98606 Sales 5004 2020-01-13 Sun Jun 7 00:00:00 2020 USD 64.15 56 3592.40
6163 7526 iBay.com Large Display Digital Thermometer Lumiscope H&PC/LAR-98606 Sales 5004 2020-01-13 Sun Jun 7 00:00:00 2020 USD 64.15 56 3592.40
8797 10329 Understock.com AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 Sales 5004 2020-01-19 2020/01/06 USD 14.88 226 3362.88
... ... ... ... ... ... ... ... ... ... ... ... ...
9822 11354 Understock.com Urban Rebounding Workout DVD, Compilation 1 M&T/URB-83617 Sales 5004 2020-01-22 June 23 2020 USD 1.69 1 1.69
7810 9342 iBay.com Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 Sales 5004 2020-01-17 3-22-20 USD 0.06 26 1.56
7812 9270 iBay.com Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 Sales 5004 2020-01-17 3-22-20 USD 0.06 26 1.56
12877 14409 Understock.com Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 Sales 5004 2020-01-29 4-13-20 USD 0.06 17 1.02
7613 9145 iBay.com Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 Sales 5004 2020-01-16 5-21-20 USD 0.06 15 0.90

14054 rows × 12 columns

In [33]:
ledger_df.sort_values(by=['Quantity', 'Total'], ascending=[False, True])
Out[33]:
InvoiceNo Channel Product Name ProductID Account AccountNo Date Deadline Currency Unit Price Quantity Total
4747 6279 Understock.com AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 Sales 5004 2020-01-10 2020/02/05 USD 14.88 226 3362.88
4757 6162 Understock.com AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 Sales 5004 2020-01-10 2020/02/05 USD 14.88 226 3362.88
4969 6397 Understock.com AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 Sales 5004 2020-01-10 2020/02/05 USD 14.88 226 3362.88
8797 10329 Understock.com AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 Sales 5004 2020-01-19 2020/01/06 USD 14.88 226 3362.88
5499 7031 iBay.com NaN H&PC/ULT-64807 Sales 5004 2020-01-12 Mon Nov 25 00:00:00 2019 USD 13.28 184 2443.52
... ... ... ... ... ... ... ... ... ... ... ... ...
616 2148 Understock.com Kodak EasyShare Z990 12 MP Digital Camera with... C&P/KOD-32137 Sales 5004 2020-01-02 11-23-19 USD 166.30 1 166.30
4248 5780 Understock.com Kodak EasyShare Z990 12 MP Digital Camera with... C&P/KOD-32137 Sales 5004 2020-01-09 2-28-20 USD 166.30 1 166.30
4485 5839 Understock.com Kodak EasyShare Z990 12 MP Digital Camera with... C&P/KOD-32137 Sales 5004 2020-01-09 2-28-20 USD 166.30 1 166.30
6152 7684 Understock.com Kodak EasyShare Z990 12 MP Digital Camera with... C&P/KOD-32137 Sales 5004 2020-01-13 4-02-20 USD 166.30 1 166.30
6164 7531 Understock.com Kodak EasyShare Z990 12 MP Digital Camera with... C&P/KOD-32137 Sales 5004 2020-01-13 4-02-20 USD 166.30 1 166.30

14054 rows × 12 columns

Cleaning data¶

  • Dealing with missing values
In [34]:
ledger_df.iloc[2, 2]
Out[34]:
nan
  • Let's find missing values
In [35]:
ledger_df['Product Name'].isna()
Out[35]:
0        False
1        False
2         True
3        False
4        False
         ...  
14049    False
14050    False
14051    False
14052     True
14053    False
Name: Product Name, Length: 14054, dtype: bool
In [36]:
ledger_df[ledger_df['Product Name'].isna()]
Out[36]:
InvoiceNo Channel Product Name ProductID Account AccountNo Date Deadline Currency Unit Price Quantity Total
2 1534 Bullseye NaN T&G/PET-14209 Sales 5004 2020-01-01 05/07/20 USD 11.67 5 58.35
6 1538 Understock.com NaN S&O/TEX-91494 Sales 5004 2020-01-01 Thu Oct 31 00:00:00 2019 USD 31.36 9 282.24
16 1548 iBay.com NaN K&D/RES-27315 Sales 5004 2020-01-01 November 29 2019 USD 8.07 5 40.35
35 1567 Understock.com NaN T&G/(10-74529 Sales 5004 2020-01-01 6-03-20 USD 12.30 5 61.50
36 1568 Walcart NaN H&K/WAL-83087 Sales 5004 2020-01-01 2020/05/06 USD 3.52 1 3.52
... ... ... ... ... ... ... ... ... ... ... ... ...
14021 15553 iBay.com NaN H&K/GIF-60463 Sales 5004 2020-01-31 2020/07/23 USD 17.28 23 397.44
14025 15443 Understock.com NaN CP&A/VXI-50842 Sales 5004 2020-01-31 Fri May 8 00:00:00 2020 USD 16.49 103 1698.47
14036 15495 Bullseye NaN M&T/THE-90996 Sales 5004 2020-01-31 January 07 2020 USD 14.85 9 133.65
14040 15572 iBay.com NaN MI/AQU-84157 Sales 5004 2020-01-31 01/24/20 USD 16.76 6 100.56
14052 15584 iBay.com NaN E/POL-61164 Sales 5004 2020-01-31 June 25 2020 USD 4.78 25 119.50

1692 rows × 12 columns

In [37]:
ledger_df['Product Name'].isna().sum()
Out[37]:
1692
In [38]:
ledger_df.isna().sum()
Out[38]:
InvoiceNo          0
Channel            0
Product Name    1692
ProductID          0
Account            0
AccountNo          0
Date               0
Deadline           0
Currency           0
Unit Price         0
Quantity           0
Total              0
dtype: int64
  • Discarding missing values
In [39]:
ledger_df[ledger_df['Product Name'].notna()]
Out[39]:
InvoiceNo Channel Product Name ProductID Account AccountNo Date Deadline Currency Unit Price Quantity Total
0 1532 Shoppe.com Cannon Water Bomb Balloons 100 Pack T&G/CAN-97509 Sales 5004 2020-01-01 11/23/19 USD 20.11 14 281.54
1 1533 Walcart LEGO Ninja Turtles Stealth Shell in Pursuit 79102 T&G/LEG-37777 Sales 5004 2020-01-01 06/15/20 USD 6.70 1 6.70
3 1535 Bullseye Transformers Age of Extinction Generations Del... T&G/TRA-20170 Sales 5004 2020-01-01 12/22/19 USD 13.46 6 80.76
4 1535 Bullseye Transformers Age of Extinction Generations Del... T&G/TRA-20170 Sales 5004 2020-01-01 12/22/19 USD 13.46 6 80.76
5 1537 Bullseye 3x Anti-Spy Privacy Screen Protector Compatibl... CP&A/3X-00445 Sales 5004 2020-01-01 Mon Jan 20 00:00:00 2020 USD 7.39 8 59.12
... ... ... ... ... ... ... ... ... ... ... ... ...
14048 15580 iBay.com Lauri Toddler Tote T&G/LAU-88048 Sales 5004 2020-01-31 2-14-20 USD 14.46 1 14.46
14049 15581 Bullseye AC Adapter/Power Supply&Cord for Lenovo 3000 G... E/AC-63975 Sales 5004 2020-01-31 February 23 2020 USD 28.72 8 229.76
14050 15582 Bullseye Cisco Systems Gigabit VPN Router (RV320K9NA) E/CIS-74992 Sales 5004 2020-01-31 January 21 2020 USD 33.39 1 33.39
14051 15583 Understock.com Philips AJ3116M/37 Digital Tuning Clock Radio ... E/PHI-08100 Sales 5004 2020-01-31 March 22 2020 USD 4.18 1 4.18
14053 15585 Understock.com Sirius Satellite Radio XADH2 Home Access Kit f... E/SIR-83381 Sales 5004 2020-01-31 February 01 2020 USD 33.16 2 66.32

12362 rows × 12 columns

In [40]:
ledger_df.dropna(subset=['Product Name', 'ProductID'])
Out[40]:
InvoiceNo Channel Product Name ProductID Account AccountNo Date Deadline Currency Unit Price Quantity Total
0 1532 Shoppe.com Cannon Water Bomb Balloons 100 Pack T&G/CAN-97509 Sales 5004 2020-01-01 11/23/19 USD 20.11 14 281.54
1 1533 Walcart LEGO Ninja Turtles Stealth Shell in Pursuit 79102 T&G/LEG-37777 Sales 5004 2020-01-01 06/15/20 USD 6.70 1 6.70
3 1535 Bullseye Transformers Age of Extinction Generations Del... T&G/TRA-20170 Sales 5004 2020-01-01 12/22/19 USD 13.46 6 80.76
4 1535 Bullseye Transformers Age of Extinction Generations Del... T&G/TRA-20170 Sales 5004 2020-01-01 12/22/19 USD 13.46 6 80.76
5 1537 Bullseye 3x Anti-Spy Privacy Screen Protector Compatibl... CP&A/3X-00445 Sales 5004 2020-01-01 Mon Jan 20 00:00:00 2020 USD 7.39 8 59.12
... ... ... ... ... ... ... ... ... ... ... ... ...
14048 15580 iBay.com Lauri Toddler Tote T&G/LAU-88048 Sales 5004 2020-01-31 2-14-20 USD 14.46 1 14.46
14049 15581 Bullseye AC Adapter/Power Supply&Cord for Lenovo 3000 G... E/AC-63975 Sales 5004 2020-01-31 February 23 2020 USD 28.72 8 229.76
14050 15582 Bullseye Cisco Systems Gigabit VPN Router (RV320K9NA) E/CIS-74992 Sales 5004 2020-01-31 January 21 2020 USD 33.39 1 33.39
14051 15583 Understock.com Philips AJ3116M/37 Digital Tuning Clock Radio ... E/PHI-08100 Sales 5004 2020-01-31 March 22 2020 USD 4.18 1 4.18
14053 15585 Understock.com Sirius Satellite Radio XADH2 Home Access Kit f... E/SIR-83381 Sales 5004 2020-01-31 February 01 2020 USD 33.16 2 66.32

12362 rows × 12 columns

In [41]:
ledger_df.dropna(how='all')
Out[41]:
InvoiceNo Channel Product Name ProductID Account AccountNo Date Deadline Currency Unit Price Quantity Total
0 1532 Shoppe.com Cannon Water Bomb Balloons 100 Pack T&G/CAN-97509 Sales 5004 2020-01-01 11/23/19 USD 20.11 14 281.54
1 1533 Walcart LEGO Ninja Turtles Stealth Shell in Pursuit 79102 T&G/LEG-37777 Sales 5004 2020-01-01 06/15/20 USD 6.70 1 6.70
2 1534 Bullseye NaN T&G/PET-14209 Sales 5004 2020-01-01 05/07/20 USD 11.67 5 58.35
3 1535 Bullseye Transformers Age of Extinction Generations Del... T&G/TRA-20170 Sales 5004 2020-01-01 12/22/19 USD 13.46 6 80.76
4 1535 Bullseye Transformers Age of Extinction Generations Del... T&G/TRA-20170 Sales 5004 2020-01-01 12/22/19 USD 13.46 6 80.76
... ... ... ... ... ... ... ... ... ... ... ... ...
14049 15581 Bullseye AC Adapter/Power Supply&Cord for Lenovo 3000 G... E/AC-63975 Sales 5004 2020-01-31 February 23 2020 USD 28.72 8 229.76
14050 15582 Bullseye Cisco Systems Gigabit VPN Router (RV320K9NA) E/CIS-74992 Sales 5004 2020-01-31 January 21 2020 USD 33.39 1 33.39
14051 15583 Understock.com Philips AJ3116M/37 Digital Tuning Clock Radio ... E/PHI-08100 Sales 5004 2020-01-31 March 22 2020 USD 4.18 1 4.18
14052 15584 iBay.com NaN E/POL-61164 Sales 5004 2020-01-31 June 25 2020 USD 4.78 25 119.50
14053 15585 Understock.com Sirius Satellite Radio XADH2 Home Access Kit f... E/SIR-83381 Sales 5004 2020-01-31 February 01 2020 USD 33.16 2 66.32

14054 rows × 12 columns

In [42]:
ledger_df.dropna(how='any', axis='columns')
Out[42]:
InvoiceNo Channel ProductID Account AccountNo Date Deadline Currency Unit Price Quantity Total
0 1532 Shoppe.com T&G/CAN-97509 Sales 5004 2020-01-01 11/23/19 USD 20.11 14 281.54
1 1533 Walcart T&G/LEG-37777 Sales 5004 2020-01-01 06/15/20 USD 6.70 1 6.70
2 1534 Bullseye T&G/PET-14209 Sales 5004 2020-01-01 05/07/20 USD 11.67 5 58.35
3 1535 Bullseye T&G/TRA-20170 Sales 5004 2020-01-01 12/22/19 USD 13.46 6 80.76
4 1535 Bullseye T&G/TRA-20170 Sales 5004 2020-01-01 12/22/19 USD 13.46 6 80.76
... ... ... ... ... ... ... ... ... ... ... ...
14049 15581 Bullseye E/AC-63975 Sales 5004 2020-01-31 February 23 2020 USD 28.72 8 229.76
14050 15582 Bullseye E/CIS-74992 Sales 5004 2020-01-31 January 21 2020 USD 33.39 1 33.39
14051 15583 Understock.com E/PHI-08100 Sales 5004 2020-01-31 March 22 2020 USD 4.18 1 4.18
14052 15584 iBay.com E/POL-61164 Sales 5004 2020-01-31 June 25 2020 USD 4.78 25 119.50
14053 15585 Understock.com E/SIR-83381 Sales 5004 2020-01-31 February 01 2020 USD 33.16 2 66.32

14054 rows × 11 columns

  • Filling missing values
In [43]:
ledger_df['Product Name'].fillna('MISSING')
Out[43]:
0                      Cannon Water Bomb Balloons 100 Pack
1        LEGO Ninja Turtles Stealth Shell in Pursuit 79102
2                                                  MISSING
3        Transformers Age of Extinction Generations Del...
4        Transformers Age of Extinction Generations Del...
                               ...                        
14049    AC Adapter/Power Supply&Cord for Lenovo 3000 G...
14050         Cisco Systems Gigabit VPN Router (RV320K9NA)
14051    Philips AJ3116M/37 Digital Tuning Clock Radio ...
14052                                              MISSING
14053    Sirius Satellite Radio XADH2 Home Access Kit f...
Name: Product Name, Length: 14054, dtype: object
In [44]:
ledger_df['Product Name'].fillna(method='ffill')
Out[44]:
0                      Cannon Water Bomb Balloons 100 Pack
1        LEGO Ninja Turtles Stealth Shell in Pursuit 79102
2        LEGO Ninja Turtles Stealth Shell in Pursuit 79102
3        Transformers Age of Extinction Generations Del...
4        Transformers Age of Extinction Generations Del...
                               ...                        
14049    AC Adapter/Power Supply&Cord for Lenovo 3000 G...
14050         Cisco Systems Gigabit VPN Router (RV320K9NA)
14051    Philips AJ3116M/37 Digital Tuning Clock Radio ...
14052    Philips AJ3116M/37 Digital Tuning Clock Radio ...
14053    Sirius Satellite Radio XADH2 Home Access Kit f...
Name: Product Name, Length: 14054, dtype: object
In [45]:
ledger_df['Product Name'].fillna(method='bfill')
Out[45]:
0                      Cannon Water Bomb Balloons 100 Pack
1        LEGO Ninja Turtles Stealth Shell in Pursuit 79102
2        Transformers Age of Extinction Generations Del...
3        Transformers Age of Extinction Generations Del...
4        Transformers Age of Extinction Generations Del...
                               ...                        
14049    AC Adapter/Power Supply&Cord for Lenovo 3000 G...
14050         Cisco Systems Gigabit VPN Router (RV320K9NA)
14051    Philips AJ3116M/37 Digital Tuning Clock Radio ...
14052    Sirius Satellite Radio XADH2 Home Access Kit f...
14053    Sirius Satellite Radio XADH2 Home Access Kit f...
Name: Product Name, Length: 14054, dtype: object
  • Dealing with duplicate rows
In [46]:
ledger_df.duplicated()
Out[46]:
0        False
1        False
2        False
3        False
4         True
         ...  
14049    False
14050    False
14051    False
14052    False
14053    False
Length: 14054, dtype: bool
In [47]:
ledger_df.duplicated(keep=False)
Out[47]:
0        False
1        False
2        False
3         True
4         True
         ...  
14049    False
14050    False
14051    False
14052    False
14053    False
Length: 14054, dtype: bool
In [48]:
ledger_df.drop_duplicates()
Out[48]:
InvoiceNo Channel Product Name ProductID Account AccountNo Date Deadline Currency Unit Price Quantity Total
0 1532 Shoppe.com Cannon Water Bomb Balloons 100 Pack T&G/CAN-97509 Sales 5004 2020-01-01 11/23/19 USD 20.11 14 281.54
1 1533 Walcart LEGO Ninja Turtles Stealth Shell in Pursuit 79102 T&G/LEG-37777 Sales 5004 2020-01-01 06/15/20 USD 6.70 1 6.70
2 1534 Bullseye NaN T&G/PET-14209 Sales 5004 2020-01-01 05/07/20 USD 11.67 5 58.35
3 1535 Bullseye Transformers Age of Extinction Generations Del... T&G/TRA-20170 Sales 5004 2020-01-01 12/22/19 USD 13.46 6 80.76
5 1537 Bullseye 3x Anti-Spy Privacy Screen Protector Compatibl... CP&A/3X-00445 Sales 5004 2020-01-01 Mon Jan 20 00:00:00 2020 USD 7.39 8 59.12
... ... ... ... ... ... ... ... ... ... ... ... ...
14049 15581 Bullseye AC Adapter/Power Supply&Cord for Lenovo 3000 G... E/AC-63975 Sales 5004 2020-01-31 February 23 2020 USD 28.72 8 229.76
14050 15582 Bullseye Cisco Systems Gigabit VPN Router (RV320K9NA) E/CIS-74992 Sales 5004 2020-01-31 January 21 2020 USD 33.39 1 33.39
14051 15583 Understock.com Philips AJ3116M/37 Digital Tuning Clock Radio ... E/PHI-08100 Sales 5004 2020-01-31 March 22 2020 USD 4.18 1 4.18
14052 15584 iBay.com NaN E/POL-61164 Sales 5004 2020-01-31 June 25 2020 USD 4.78 25 119.50
14053 15585 Understock.com Sirius Satellite Radio XADH2 Home Access Kit f... E/SIR-83381 Sales 5004 2020-01-31 February 01 2020 USD 33.16 2 66.32

13987 rows × 12 columns

In [49]:
ledger_df.drop_duplicates(subset=['InvoiceNo', 'ProductID'])
Out[49]:
InvoiceNo Channel Product Name ProductID Account AccountNo Date Deadline Currency Unit Price Quantity Total
0 1532 Shoppe.com Cannon Water Bomb Balloons 100 Pack T&G/CAN-97509 Sales 5004 2020-01-01 11/23/19 USD 20.11 14 281.54
1 1533 Walcart LEGO Ninja Turtles Stealth Shell in Pursuit 79102 T&G/LEG-37777 Sales 5004 2020-01-01 06/15/20 USD 6.70 1 6.70
2 1534 Bullseye NaN T&G/PET-14209 Sales 5004 2020-01-01 05/07/20 USD 11.67 5 58.35
3 1535 Bullseye Transformers Age of Extinction Generations Del... T&G/TRA-20170 Sales 5004 2020-01-01 12/22/19 USD 13.46 6 80.76
5 1537 Bullseye 3x Anti-Spy Privacy Screen Protector Compatibl... CP&A/3X-00445 Sales 5004 2020-01-01 Mon Jan 20 00:00:00 2020 USD 7.39 8 59.12
... ... ... ... ... ... ... ... ... ... ... ... ...
14049 15581 Bullseye AC Adapter/Power Supply&Cord for Lenovo 3000 G... E/AC-63975 Sales 5004 2020-01-31 February 23 2020 USD 28.72 8 229.76
14050 15582 Bullseye Cisco Systems Gigabit VPN Router (RV320K9NA) E/CIS-74992 Sales 5004 2020-01-31 January 21 2020 USD 33.39 1 33.39
14051 15583 Understock.com Philips AJ3116M/37 Digital Tuning Clock Radio ... E/PHI-08100 Sales 5004 2020-01-31 March 22 2020 USD 4.18 1 4.18
14052 15584 iBay.com NaN E/POL-61164 Sales 5004 2020-01-31 June 25 2020 USD 4.78 25 119.50
14053 15585 Understock.com Sirius Satellite Radio XADH2 Home Access Kit f... E/SIR-83381 Sales 5004 2020-01-31 February 01 2020 USD 33.16 2 66.32

13986 rows × 12 columns

Writing Excel files¶

In [52]:
ledger_df.to_excel('JanQ1Sales.xlsx')
In [53]:
ledger_df.to_excel('JanQ1Sales.xlsx', sheet_name='Sales')
In [54]:
ledger_df.to_excel('JanQ1Sales.xlsx', sheet_name='Sales', index=False)
In [ ]:
jan_sales_df = pd.read_excel('Q1Sales.xlsx', sheet_name='January')

jan_sales_df

Organizing sales data by channel¶

In [57]:
jan_sales_df = pd.read_excel('data/Q1Sales.xlsx', sheet_name='January')
feb_sales_df = pd.read_excel('data/Q1Sales.xlsx', sheet_name='February')
mar_sales_df = pd.read_excel('data/Q1Sales.xlsx', sheet_name='March')
In [58]:
sales_df = pd.concat([jan_sales_df, feb_sales_df, mar_sales_df], ignore_index=True)
In [59]:
sales_df
Out[59]:
InvoiceNo Channel Product Name ProductID Account AccountNo Date Deadline Currency Unit Price Quantity Total
0 1532 Shoppe.com Cannon Water Bomb Balloons 100 Pack T&G/CAN-97509 Sales 5004 2020-01-01 11/23/19 USD 20.11 14 281.54
1 1533 Walcart LEGO Ninja Turtles Stealth Shell in Pursuit 79102 T&G/LEG-37777 Sales 5004 2020-01-01 06/15/20 USD 6.70 1 6.70
2 1534 Bullseye NaN T&G/PET-14209 Sales 5004 2020-01-01 05/07/20 USD 11.67 5 58.35
3 1535 Bullseye Transformers Age of Extinction Generations Del... T&G/TRA-20170 Sales 5004 2020-01-01 12/22/19 USD 13.46 6 80.76
4 1535 Bullseye Transformers Age of Extinction Generations Del... T&G/TRA-20170 Sales 5004 2020-01-01 12/22/19 USD 13.46 6 80.76
... ... ... ... ... ... ... ... ... ... ... ... ...
37703 39235 iBay.com Nature's Bounty Garlic, 2000mg, Odor-Free, 120... H&PC/NAT-15470 Sales 5004 2020-03-31 Thu Sep 17 00:00:00 2020 USD 5.55 2 11.10
37704 39216 Shoppe.com Funko Wonder Woman POP Heroes T&G/FUN-03366 Sales 5004 2020-03-31 5-08-20 USD 28.56 1 28.56
37705 39219 Shoppe.com MONO GS1 GS1-BTY-BLK-L Betty Long Guitar Strap... MI/MON-86723 Sales 5004 2020-03-31 04/11/20 USD 3.33 1 3.33
37706 39238 Shoppe.com NaN T&G/MAG-68412 Sales 5004 2020-03-31 3-20-20 USD 34.76 10 347.60
37707 39239 Understock.com 3 Collapsible Bowl Set 32oz | 16oz | 4oz K&D/3 C-07383 Sales 5004 2020-03-31 April 02 2020 USD 6.39 15 95.85

37708 rows × 12 columns

In [61]:
channel = 'Understock.com'
channel_df = sales_df[sales_df['Channel'] == channel]
channel_df = channel_df.sort_values(['Total', 'Quantity'], ascending=False)
In [62]:
channel_df
Out[62]:
InvoiceNo Channel Product Name ProductID Account AccountNo Date Deadline Currency Unit Price Quantity Total
23620 25152 Understock.com Large Display Digital Thermometer Lumiscope H&PC/LAR-98606 Sales 5004 2020-02-21 Sun Dec 22 00:00:00 2019 USD 64.32 54 3473.28
36621 38153 Understock.com Large Display Digital Thermometer Lumiscope H&PC/LAR-98606 Sales 5004 2020-03-26 Sun Apr 19 00:00:00 2020 USD 64.32 54 3473.28
4747 6279 Understock.com AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 Sales 5004 2020-01-10 2020/02/05 USD 14.88 226 3362.88
4757 6162 Understock.com AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 Sales 5004 2020-01-10 2020/02/05 USD 14.88 226 3362.88
4969 6397 Understock.com AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 Sales 5004 2020-01-10 2020/02/05 USD 14.88 226 3362.88
... ... ... ... ... ... ... ... ... ... ... ... ...
15679 17002 Understock.com Urban Rebounding Workout DVD, Compilation 1 M&T/URB-83617 Sales 5004 2020-02-04 July 02 2020 USD 1.69 1 1.69
12877 14409 Understock.com Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 Sales 5004 2020-01-29 4-13-20 USD 0.06 17 1.02
15855 17387 Understock.com Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 Sales 5004 2020-02-05 1-14-20 USD 0.06 11 0.66
24012 25544 Understock.com Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 Sales 5004 2020-02-22 6-19-20 USD 0.06 11 0.66
24022 25466 Understock.com Magic: the Gathering - Hydra Broodmaster (126/... T&G/MAG-22549 Sales 5004 2020-02-22 6-19-20 USD 0.06 11 0.66

13188 rows × 12 columns

In [63]:
channel_df.to_excel('Q1ChannelSales.xlsx', sheet_name='Understock.com', index=False)
In [64]:
channels = ['Bullseye', 'iBay.com', 'Shoppe.com', 'Understock.com', 'Walcart']

for channel in channels:
    print(channel)

Bullseye
iBay.com
Shoppe.com
Understock.com
Walcart
In [65]:
channels = ['Bullseye', 'iBay.com', 'Shoppe.com', 'Understock.com', 'Walcart']

for channel in channels:
    channel_df = sales_df[sales_df['Channel'] == channel]
    channel_df = channel_df.sort_values(['Total', 'Quantity'], ascending=False)
In [66]:
channel_df
Out[66]:
InvoiceNo Channel Product Name ProductID Account AccountNo Date Deadline Currency Unit Price Quantity Total
2461 3993 Walcart Large Display Digital Thermometer Lumiscope H&PC/LAR-98606 Sales 5004 2020-01-06 Mon Jun 22 00:00:00 2020 USD 64.47 23 1482.81
8332 9864 Walcart AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 Sales 5004 2020-01-18 2019/12/02 USD 14.49 100 1449.00
15293 16825 Walcart AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 Sales 5004 2020-02-04 2019/11/29 USD 14.49 100 1449.00
15509 16895 Walcart AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 Sales 5004 2020-02-04 2019/11/29 USD 14.49 100 1449.00
23888 25420 Walcart AC Adapter/battery charger for GateWay Liteon ... E/AC-44106 Sales 5004 2020-02-21 2020/02/14 USD 14.49 100 1449.00
... ... ... ... ... ... ... ... ... ... ... ... ...
936 2332 Walcart Vibrating Slim Jelly Dong with Suction Cup 7.5... H&PC/VIB-56252 Sales 5004 2020-01-02 Thu Jan 2 00:00:00 2020 USD 2.10 1 2.10
11222 12754 Walcart Vibrating Slim Jelly Dong with Suction Cup 7.5... H&PC/VIB-56252 Sales 5004 2020-01-25 Wed Dec 18 00:00:00 2019 USD 2.10 1 2.10
14186 15718 Walcart Blackberry Q10 White 16GB Factory Unlocked, In... CP&A/BLA-26278 Sales 5004 2020-02-01 Tue Mar 10 00:00:00 2020 USD 1.87 1 1.87
14311 15712 Walcart Blackberry Q10 White 16GB Factory Unlocked, In... CP&A/BLA-26278 Sales 5004 2020-02-01 Tue Mar 10 00:00:00 2020 USD 1.87 1 1.87
33627 35159 Walcart Red Dragon VT 3-30 C 500,000 BTU Heavy Duty Pr... PL&G/RED-46561 Sales 5004 2020-03-15 07/19/20 USD 0.19 6 1.14

4574 rows × 12 columns

In [67]:
channels = ['Bullseye', 'iBay.com', 'Shoppe.com', 'Understock.com', 'Walcart']

output_file = pd.ExcelWriter('Q1ChannelSales.xlsx')

for channel in channels:
    channel_df = sales_df[sales_df['Channel'] == channel]
    channel_df = channel_df.sort_values(['Total', 'Quantity'], ascending=False)
    channel_df.to_excel(output_file, sheet_name=channel, index=False)
    
output_file.save()
In [68]:
with pd.ExcelWriter('Q1ChannelSales.xlsx') as output_file:
    for channel in channels:
        channel_df = sales_df[sales_df['Channel'] == channel]
        channel_df = channel_df.sort_values(['Total', 'Quantity'], ascending=False)
        channel_df.to_excel(output_file, sheet_name=channel, index=False)
In [70]:
sales_df = pd.concat([
        pd.read_excel('data/Q1Sales.xlsx', sheet_name='January'), 
        pd.read_excel('data/Q1Sales.xlsx', sheet_name='February'), 
        pd.read_excel('data/Q1Sales.xlsx', sheet_name='March')
    ], ignore_index=True)
channels = ['Bullseye', 'iBay.com', 'Shoppe.com', 'Understock.com', 'Walcart']

with pd.ExcelWriter('Q1ChannelSales.xlsx') as output_file:
    for channel in channels:
        channel_df = sales_df[sales_df['Channel'] == channel]
        channel_df = channel_df.sort_values(['Total', 'Quantity'], ascending=False)
        channel_df.to_excel(output_file, sheet_name=channel, index=False)
In [71]:
ledger_df.to_csv('JanQ1Sales.csv', index=False)