CoCalc Public FilesSharalee.ipynbOpen with one click!
Download, Raw, Embed
Author: Sharalee Jones
Views : 163
Compute Environment: Ubuntu 20.04 (Default)
In [3]:
import pandas as pd
In [4]:
df = pd.read_excel('SampleData.xlsx')
In [5]:
OrderDate Region Rep Item Units Unit Cost Total
0 2016-01-06 East Jones Pencil 95 1.99 189.05
1 2016-01-23 Central Kivell Binder 50 19.99 999.50
2 2016-02-09 Central Jardine Pencil 36 4.99 179.64
3 2016-02-26 Central Gill Pen 27 19.99 539.73
4 2016-03-15 West Sorvino Pencil 56 2.99 167.44
5 2016-04-01 East Jones Binder 60 4.99 299.40
6 2016-04-18 Central Andrews Pencil 75 1.99 149.25
7 2016-05-05 Central Jardine Pencil 90 4.99 449.10
8 2016-05-22 West Thompson Pencil 32 1.99 63.68
9 2016-06-08 East Jones Binder 60 8.99 539.40
10 2016-06-25 Central Morgan Pencil 90 4.99 449.10
11 2016-07-12 East Howard Binder 29 1.99 57.71
12 2016-07-29 East Parent Binder 81 19.99 1619.19
13 2016-08-15 East Jones Pencil 35 4.99 174.65
14 2016-09-01 Central Smith Desk 2 125.00 250.00
15 2016-09-18 East Jones Pen Set 16 15.99 255.84
16 2016-10-05 Central Morgan Binder 28 8.99 251.72
17 2016-10-22 East Jones Pen 64 8.99 575.36
18 2016-11-08 East Parent Pen 15 19.99 299.85
19 2016-11-25 Central Kivell Pen Set 96 4.99 479.04
20 2016-12-12 Central Smith Pencil 67 1.29 86.43
21 2016-12-29 East Parent Pen Set 74 15.99 1183.26
22 2017-01-15 Central Gill Binder 46 8.99 413.54
23 2017-02-01 Central Smith Binder 87 15.00 1305.00
24 2017-02-18 East Jones Binder 4 4.99 19.96
25 2017-03-07 West Sorvino Binder 7 19.99 139.93
26 2017-03-24 Central Jardine Pen Set 50 4.99 249.50
27 2017-04-10 Central Andrews Pencil 66 1.99 131.34
28 2017-04-27 East Howard Pen 96 4.99 479.04
29 2017-05-14 Central Gill Pencil 53 1.29 68.37
30 2017-05-31 Central Gill Binder 80 8.99 719.20
31 2017-06-17 Central Kivell Desk 5 125.00 625.00
32 2017-07-04 East Jones Pen Set 62 4.99 309.38
33 2017-07-21 Central Morgan Pen Set 55 12.49 686.95
34 2017-08-07 Central Kivell Pen Set 42 23.95 1005.90
35 2017-08-24 West Sorvino Desk 3 275.00 825.00
36 2017-09-10 Central Gill Pencil 7 1.29 9.03
37 2017-09-27 West Sorvino Pen 76 1.99 151.24
38 2017-10-14 West Thompson Binder 57 19.99 1139.43
39 2017-10-31 Central Andrews Pencil 14 1.29 18.06
40 2017-11-17 Central Jardine Binder 11 4.99 54.89
41 2017-12-04 Central Jardine Binder 94 19.99 1879.06
42 2017-12-21 Central Andrews Binder 28 4.99 139.72
In [8]:
df[df.Region == 'Central']
OrderDate Region Rep Item Units Unit Cost Total
1 2016-01-23 Central Kivell Binder 50 19.99 999.50
2 2016-02-09 Central Jardine Pencil 36 4.99 179.64
3 2016-02-26 Central Gill Pen 27 19.99 539.73
6 2016-04-18 Central Andrews Pencil 75 1.99 149.25
7 2016-05-05 Central Jardine Pencil 90 4.99 449.10
10 2016-06-25 Central Morgan Pencil 90 4.99 449.10
14 2016-09-01 Central Smith Desk 2 125.00 250.00
16 2016-10-05 Central Morgan Binder 28 8.99 251.72
19 2016-11-25 Central Kivell Pen Set 96 4.99 479.04
20 2016-12-12 Central Smith Pencil 67 1.29 86.43
22 2017-01-15 Central Gill Binder 46 8.99 413.54
23 2017-02-01 Central Smith Binder 87 15.00 1305.00
26 2017-03-24 Central Jardine Pen Set 50 4.99 249.50
27 2017-04-10 Central Andrews Pencil 66 1.99 131.34
29 2017-05-14 Central Gill Pencil 53 1.29 68.37
30 2017-05-31 Central Gill Binder 80 8.99 719.20
31 2017-06-17 Central Kivell Desk 5 125.00 625.00
33 2017-07-21 Central Morgan Pen Set 55 12.49 686.95
34 2017-08-07 Central Kivell Pen Set 42 23.95 1005.90
36 2017-09-10 Central Gill Pencil 7 1.29 9.03
39 2017-10-31 Central Andrews Pencil 14 1.29 18.06
40 2017-11-17 Central Jardine Binder 11 4.99 54.89
41 2017-12-04 Central Jardine Binder 94 19.99 1879.06
42 2017-12-21 Central Andrews Binder 28 4.99 139.72
In [9]:
df[(df.Item == 'Binder') & (df.Region == 'Central')]
OrderDate Region Rep Item Units Unit Cost Total
1 2016-01-23 Central Kivell Binder 50 19.99 999.50
16 2016-10-05 Central Morgan Binder 28 8.99 251.72
22 2017-01-15 Central Gill Binder 46 8.99 413.54
23 2017-02-01 Central Smith Binder 87 15.00 1305.00
30 2017-05-31 Central Gill Binder 80 8.99 719.20
40 2017-11-17 Central Jardine Binder 11 4.99 54.89
41 2017-12-04 Central Jardine Binder 94 19.99 1879.06
42 2017-12-21 Central Andrews Binder 28 4.99 139.72
In [12]:
sum df[(df.Item == 'Binder') & (df.Region == 'Central')]/[(df.Units)]
File "<ipython-input-12-ef626c7fc088>", line 1 sum df[(df.Item == 'Binder') & (df.Region == 'Central')]/[(df.Units)] ^ SyntaxError: invalid syntax
In [14]:
columns = df.columns(df.Units)
--------------------------------------------------------------------------- KeyError Traceback (most recent call last) <ipython-input-14-c6415e351ff5> in <module> ----> 1 sum = df[(df.Units)] /usr/local/lib/python3.8/dist-packages/pandas/core/frame.py in __getitem__(self, key) 2910 if is_iterator(key): 2911 key = list(key) -> 2912 indexer = self.loc._get_listlike_indexer(key, axis=1, raise_missing=True)[1] 2913 2914 # take() does not accept boolean indexers /usr/local/lib/python3.8/dist-packages/pandas/core/indexing.py in _get_listlike_indexer(self, key, axis, raise_missing) 1252 keyarr, indexer, new_indexer = ax._reindex_non_unique(keyarr) 1253 -> 1254 self._validate_read_indexer(keyarr, indexer, axis, raise_missing=raise_missing) 1255 return keyarr, indexer 1256 /usr/local/lib/python3.8/dist-packages/pandas/core/indexing.py in _validate_read_indexer(self, key, indexer, axis, raise_missing) 1296 if missing == len(indexer): 1297 axis_name = self.obj._get_axis_name(axis) -> 1298 raise KeyError(f"None of [{key}] are in the [{axis_name}]") 1299 1300 # We (temporarily) allow for some missing keys with .loc, except in KeyError: "None of [Int64Index([95, 50, 36, 27, 56, 60, 75, 90, 32, 60, 90, 29, 81, 35, 2, 16, 28,\n 64, 15, 96, 67, 74, 46, 87, 4, 7, 50, 66, 96, 53, 80, 5, 62, 55,\n 42, 3, 7, 76, 57, 14, 11, 94, 28],\n dtype='int64')] are in the [columns]"

I was trying to get it it to ouput just the sum of the Units coloums but I input something wrong

In [15]:
50 + 28 + 46 + 87 + 80 + 11 + 94 + 28
424

424 binders were sold in the central region