import pandas as pd
df = pd.read_excel('SampleData.xlsx')
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 |
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 |
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 |
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
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]"
50 + 28 + 46 + 87 + 80 + 11 + 94 + 28