python-for-excel-users-basics
Read a file /home/piotr/Documents/sales/2025-01.xlsx
Thinking
Inspecting load patterns
I need to focus on loading and inspecting the data only. I should use the read_excel function and follow the exact pattern to ensure everything is accurate. It's essential to get this process right to avoid any errors. I’m thinking about how to handle the inspection efficiently while ensuring that all necessary elements are included. Let’s keep things straightforward and clear as I proceed with this task!
</>Load sales data
import pandas as pd
df = pd.read_excel("/home/piotr/Documents/sales/2025-01.xlsx")
print(f"Shape: {df.shape}")
columns = df.columns.tolist()
print(f"Columns ({len(columns)}):", columns if len(columns) <= 100 else columns[:100] + ["..."])
df.head()Shape: (503, 7) Columns (7): ['order_id', 'order_date', 'region', 'product', 'units', 'unit_price', 'revenue']
| order_id | order_date | region | product | units | unit_price | revenue | |
|---|---|---|---|---|---|---|---|
| 0 | ORD-2025-01-0001 | 2025-01-01 | North | Wireless Mouse | 7 | 29.9 | 209.3 |
| 1 | ORD-2025-01-0002 | 2025-01-01 | East | Standing Desk | 6 | 449.0 | 2694.0 |
| 2 | ORD-2025-01-0003 | 2025-01-01 | North | Desk Lamp | 8 | 24.5 | 196.0 |
| 3 | ORD-2025-01-0004 | 2025-01-01 | South | Laptop Stand | 19 | 45.0 | 855.0 |
| 4 | ORD-2025-01-0005 | 2025-01-01 | North | Wireless Mouse | 8 | 29.9 | 239.2 |
The Excel file contains 503 sales records with 7 fields: order ID, order date, region, product, units, unit price, and revenue. The preview shows order-level transactions dated January 2025, with revenue stored as numeric values.
show the last 5 rows
</>Show last rows
df.tail()| order_id | order_date | region | product | units | unit_price | revenue | |
|---|---|---|---|---|---|---|---|
| 498 | ORD-2025-01-0499 | 2025-01-31 | East | Wireless Mouse | 43 | 29.9 | 1285.7 |
| 499 | ORD-2025-01-0500 | 2025-01-31 | South | Monitor Arm | 2 | 79.9 | 159.8 |
| 500 | ORD-2025-01-0501 | 2025-01-31 | East | Desk Lamp | 3 | 24.5 | 73.5 |
| 501 | ORD-2025-01-0502 | 2025-01-31 | South | Standing Desk | 5 | 449.0 | 2245.0 |
| 502 | ORD-2025-01-0503 | 2025-01-31 | West | Laptop Stand | 14 | 45.0 | 630.0 |
The last five records are orders ORD-2025-01-0499 through ORD-2025-01-0503, all dated 31 January 2025. They include sales across East, South, and West regions, with revenues ranging from 73.50 USD for a desk lamp to 2,245.00 USD for a standing desk.
show only the order_date, product and revenue columns
</>Select key columns
df[["order_date", "product", "revenue"]]| order_date | product | revenue | |
|---|---|---|---|
| 0 | 2025-01-01 | Wireless Mouse | 209.3 |
| 1 | 2025-01-01 | Standing Desk | 2694.0 |
| 2 | 2025-01-01 | Desk Lamp | 196.0 |
| 3 | 2025-01-01 | Laptop Stand | 855.0 |
| 4 | 2025-01-01 | Wireless Mouse | 239.2 |
| ... | ... | ... | ... |
| 498 | 2025-01-31 | Wireless Mouse | 1285.7 |
| 499 | 2025-01-31 | Monitor Arm | 159.8 |
| 500 | 2025-01-31 | Desk Lamp | 73.5 |
| 501 | 2025-01-31 | Standing Desk | 2245.0 |
| 502 | 2025-01-31 | Laptop Stand | 630.0 |
503 rows × 3 columns
The displayed table now contains exactly the requested fields: order_date, product, and revenue, across all 503 orders. Revenue values are shown as numeric amounts, from examples such as 209.30 USD for a Wireless Mouse order on 1 January through 630.00 USD for a Laptop Stand order on 31 January.
show orders from the North region with revenue over 1000
</>Filter North high revenue rows
df.loc[(df["region"] == "North") & (df["revenue"] > 1000)]| order_id | order_date | region | product | units | unit_price | revenue | |
|---|---|---|---|---|---|---|---|
| 18 | ORD-2025-01-0019 | 2025-01-02 | North | Keyboard | 20 | 59.0 | 1180.0 |
| 36 | ORD-2025-01-0037 | 2025-01-03 | North | Keyboard | 19 | 59.0 | 1121.0 |
| 53 | ORD-2025-01-0054 | 2025-01-04 | North | Desk Lamp | 45 | 24.5 | 1102.5 |
| 55 | ORD-2025-01-0056 | 2025-01-04 | North | Standing Desk | 5 | 449.0 | 2245.0 |
| 87 | ORD-2025-01-0088 | 2025-01-07 | North | Desk Lamp | 45 | 24.5 | 1102.5 |
| 91 | ORD-2025-01-0092 | 2025-01-07 | North | Monitor Arm | 14 | 79.9 | 1118.6 |
| 95 | ORD-2025-01-0096 | 2025-01-07 | North | Office Chair | 7 | 189.0 | 1323.0 |
| 120 | ORD-2025-01-0121 | 2025-01-09 | North | Laptop Stand | 23 | 45.0 | 1035.0 |
| 132 | ORD-2025-01-0133 | 2025-01-09 | North | Monitor Arm | 20 | 79.9 | 1598.0 |
| 145 | ORD-2025-01-0146 | 2025-01-10 | North | Monitor Arm | 19 | 79.9 | 1518.1 |
| 173 | ORD-2025-01-0174 | 2025-01-12 | North | Wireless Mouse | 44 | 29.9 | 1315.6 |
| 178 | ORD-2025-01-0179 | 2025-01-12 | North | Monitor Arm | 18 | 79.9 | 1438.2 |
| 184 | ORD-2025-01-0185 | 2025-01-13 | North | Wireless Mouse | 48 | 29.9 | 1435.2 |
| 190 | ORD-2025-01-0191 | 2025-01-13 | North | Wireless Mouse | 47 | 29.9 | 1405.3 |
| 211 | ORD-2025-01-0212 | 2025-01-14 | North | Office Chair | 8 | 189.0 | 1512.0 |
| 219 | ORD-2025-01-0220 | 2025-01-15 | North | Monitor Arm | 22 | 79.9 | 1757.8 |
| 221 | ORD-2025-01-0222 | 2025-01-15 | North | Wireless Mouse | 37 | 29.9 | 1106.3 |
| 229 | ORD-2025-01-0230 | 2025-01-15 | North | Desk Lamp | 47 | 24.5 | 1151.5 |
| 249 | ORD-2025-01-0250 | 2025-01-16 | North | Wireless Mouse | 38 | 29.9 | 1136.2 |
| 251 | ORD-2025-01-0252 | 2025-01-16 | North | Keyboard | 20 | 59.0 | 1180.0 |
| 266 | ORD-2025-01-0267 | 2025-01-17 | North | Monitor Arm | 13 | 79.9 | 1038.7 |
| 272 | ORD-2025-01-0273 | 2025-01-17 | North | Standing Desk | 5 | 449.0 | 2245.0 |
| 284 | ORD-2025-01-0285 | 2025-01-18 | North | Standing Desk | 6 | 449.0 | 2694.0 |
| 285 | ORD-2025-01-0286 | 2025-01-18 | North | Monitor Arm | 18 | 79.9 | 1438.2 |
| 316 | ORD-2025-01-0317 | 2025-01-21 | North | Standing Desk | 6 | 449.0 | 2694.0 |
| 322 | ORD-2025-01-0323 | 2025-01-21 | North | Keyboard | 24 | 59.0 | 1416.0 |
| 324 | ORD-2025-01-0325 | 2025-01-21 | North | Wireless Mouse | 36 | 29.9 | 1076.4 |
| 331 | ORD-2025-01-0332 | 2025-01-21 | North | Keyboard | 25 | 59.0 | 1475.0 |
| 340 | ORD-2025-01-0341 | 2025-01-22 | North | Office Chair | 7 | 189.0 | 1323.0 |
| 343 | ORD-2025-01-0344 | 2025-01-22 | North | Monitor Arm | 17 | 79.9 | 1358.3 |
| 378 | ORD-2025-01-0379 | 2025-01-24 | North | Office Chair | 8 | 189.0 | 1512.0 |
| 381 | ORD-2025-01-0382 | 2025-01-24 | North | Desk Lamp | 41 | 24.5 | 1004.5 |
| 382 | ORD-2025-01-0383 | 2025-01-25 | North | Monitor Arm | 16 | 79.9 | 1278.4 |
| 395 | ORD-2025-01-0396 | 2025-01-26 | North | Keyboard | 21 | 59.0 | 1239.0 |
| 404 | ORD-2025-01-0405 | 2025-01-27 | North | Monitor Arm | 20 | 79.9 | 1598.0 |
| 406 | ORD-2025-01-0407 | 2025-01-27 | North | Office Chair | 8 | 189.0 | 1512.0 |
| 413 | ORD-2025-01-0414 | 2025-01-27 | North | Keyboard | 25 | 59.0 | 1475.0 |
| 424 | ORD-2025-01-0425 | 2025-01-28 | North | Monitor Arm | 19 | 79.9 | 1518.1 |
| 425 | ORD-2025-01-0426 | 2025-01-28 | North | Wireless Mouse | 43 | 29.9 | 1285.7 |
| 436 | ORD-2025-01-0437 | 2025-01-29 | North | Standing Desk | 3 | 449.0 | 1347.0 |
| 451 | ORD-2025-01-0452 | 2025-01-30 | North | Monitor Arm | 21 | 79.9 | 1677.9 |
| 458 | ORD-2025-01-0459 | 2025-01-30 | North | Wireless Mouse | 49 | 29.9 | 1465.1 |
| 459 | ORD-2025-01-0460 | 2025-01-30 | North | Monitor Arm | 21 | 79.9 | 1677.9 |
| 465 | ORD-2025-01-0466 | 2025-01-30 | North | Keyboard | 20 | 59.0 | 1180.0 |
North-region orders with revenue above 1000 USD are present across multiple dates. Examples include Keyboard orders of 1,180 USD and 1,121 USD, Standing Desk orders of 2,245 USD, Monitor Arm orders up to 1,598 USD, and Office Chair orders of 1,323 USD. The filtered result contains all qualifying North orders.
sort by revenue, highest first
</>Filter north high revenue rows
df.loc[(df["region"] == "North") & (df["revenue"] > 1000)].sort_values(
"revenue", ascending=False
)| order_id | order_date | region | product | units | unit_price | revenue | |
|---|---|---|---|---|---|---|---|
| 284 | ORD-2025-01-0285 | 2025-01-18 | North | Standing Desk | 6 | 449.0 | 2694.0 |
| 316 | ORD-2025-01-0317 | 2025-01-21 | North | Standing Desk | 6 | 449.0 | 2694.0 |
| 55 | ORD-2025-01-0056 | 2025-01-04 | North | Standing Desk | 5 | 449.0 | 2245.0 |
| 272 | ORD-2025-01-0273 | 2025-01-17 | North | Standing Desk | 5 | 449.0 | 2245.0 |
| 219 | ORD-2025-01-0220 | 2025-01-15 | North | Monitor Arm | 22 | 79.9 | 1757.8 |
| 459 | ORD-2025-01-0460 | 2025-01-30 | North | Monitor Arm | 21 | 79.9 | 1677.9 |
| 451 | ORD-2025-01-0452 | 2025-01-30 | North | Monitor Arm | 21 | 79.9 | 1677.9 |
| 404 | ORD-2025-01-0405 | 2025-01-27 | North | Monitor Arm | 20 | 79.9 | 1598.0 |
| 132 | ORD-2025-01-0133 | 2025-01-09 | North | Monitor Arm | 20 | 79.9 | 1598.0 |
| 424 | ORD-2025-01-0425 | 2025-01-28 | North | Monitor Arm | 19 | 79.9 | 1518.1 |
| 145 | ORD-2025-01-0146 | 2025-01-10 | North | Monitor Arm | 19 | 79.9 | 1518.1 |
| 211 | ORD-2025-01-0212 | 2025-01-14 | North | Office Chair | 8 | 189.0 | 1512.0 |
| 378 | ORD-2025-01-0379 | 2025-01-24 | North | Office Chair | 8 | 189.0 | 1512.0 |
| 406 | ORD-2025-01-0407 | 2025-01-27 | North | Office Chair | 8 | 189.0 | 1512.0 |
| 331 | ORD-2025-01-0332 | 2025-01-21 | North | Keyboard | 25 | 59.0 | 1475.0 |
| 413 | ORD-2025-01-0414 | 2025-01-27 | North | Keyboard | 25 | 59.0 | 1475.0 |
| 458 | ORD-2025-01-0459 | 2025-01-30 | North | Wireless Mouse | 49 | 29.9 | 1465.1 |
| 178 | ORD-2025-01-0179 | 2025-01-12 | North | Monitor Arm | 18 | 79.9 | 1438.2 |
| 285 | ORD-2025-01-0286 | 2025-01-18 | North | Monitor Arm | 18 | 79.9 | 1438.2 |
| 184 | ORD-2025-01-0185 | 2025-01-13 | North | Wireless Mouse | 48 | 29.9 | 1435.2 |
| 322 | ORD-2025-01-0323 | 2025-01-21 | North | Keyboard | 24 | 59.0 | 1416.0 |
| 190 | ORD-2025-01-0191 | 2025-01-13 | North | Wireless Mouse | 47 | 29.9 | 1405.3 |
| 343 | ORD-2025-01-0344 | 2025-01-22 | North | Monitor Arm | 17 | 79.9 | 1358.3 |
| 436 | ORD-2025-01-0437 | 2025-01-29 | North | Standing Desk | 3 | 449.0 | 1347.0 |
| 340 | ORD-2025-01-0341 | 2025-01-22 | North | Office Chair | 7 | 189.0 | 1323.0 |
| 95 | ORD-2025-01-0096 | 2025-01-07 | North | Office Chair | 7 | 189.0 | 1323.0 |
| 173 | ORD-2025-01-0174 | 2025-01-12 | North | Wireless Mouse | 44 | 29.9 | 1315.6 |
| 425 | ORD-2025-01-0426 | 2025-01-28 | North | Wireless Mouse | 43 | 29.9 | 1285.7 |
| 382 | ORD-2025-01-0383 | 2025-01-25 | North | Monitor Arm | 16 | 79.9 | 1278.4 |
| 395 | ORD-2025-01-0396 | 2025-01-26 | North | Keyboard | 21 | 59.0 | 1239.0 |
| 465 | ORD-2025-01-0466 | 2025-01-30 | North | Keyboard | 20 | 59.0 | 1180.0 |
| 251 | ORD-2025-01-0252 | 2025-01-16 | North | Keyboard | 20 | 59.0 | 1180.0 |
| 18 | ORD-2025-01-0019 | 2025-01-02 | North | Keyboard | 20 | 59.0 | 1180.0 |
| 229 | ORD-2025-01-0230 | 2025-01-15 | North | Desk Lamp | 47 | 24.5 | 1151.5 |
| 249 | ORD-2025-01-0250 | 2025-01-16 | North | Wireless Mouse | 38 | 29.9 | 1136.2 |
| 36 | ORD-2025-01-0037 | 2025-01-03 | North | Keyboard | 19 | 59.0 | 1121.0 |
| 91 | ORD-2025-01-0092 | 2025-01-07 | North | Monitor Arm | 14 | 79.9 | 1118.6 |
| 221 | ORD-2025-01-0222 | 2025-01-15 | North | Wireless Mouse | 37 | 29.9 | 1106.3 |
| 53 | ORD-2025-01-0054 | 2025-01-04 | North | Desk Lamp | 45 | 24.5 | 1102.5 |
| 87 | ORD-2025-01-0088 | 2025-01-07 | North | Desk Lamp | 45 | 24.5 | 1102.5 |
| 324 | ORD-2025-01-0325 | 2025-01-21 | North | Wireless Mouse | 36 | 29.9 | 1076.4 |
| 266 | ORD-2025-01-0267 | 2025-01-17 | North | Monitor Arm | 13 | 79.9 | 1038.7 |
| 120 | ORD-2025-01-0121 | 2025-01-09 | North | Laptop Stand | 23 | 45.0 | 1035.0 |
| 381 | ORD-2025-01-0382 | 2025-01-24 | North | Desk Lamp | 41 | 24.5 | 1004.5 |
The North-region orders above 1,000 USD are sorted from highest to lowest revenue. The highest entries are two Standing Desk orders—ORD-2025-01-0285 and ORD-2025-01-0317—each with revenue of 2,694 USD. They are followed by Standing Desk orders at 2,245 USD and Monitor Arm orders led by 1,757.80 USD.
add a column called revenue_with_vat that is revenue plus 23% VAT
</>Add VAT to revenue
df["revenue_with_vat"] = df["revenue"] * 1.23
df.head()| order_id | order_date | region | product | units | unit_price | revenue | revenue_with_vat | |
|---|---|---|---|---|---|---|---|---|
| 0 | ORD-2025-01-0001 | 2025-01-01 | North | Wireless Mouse | 7 | 29.9 | 209.3 | 257.439 |
| 1 | ORD-2025-01-0002 | 2025-01-01 | East | Standing Desk | 6 | 449.0 | 2694.0 | 3313.620 |
| 2 | ORD-2025-01-0003 | 2025-01-01 | North | Desk Lamp | 8 | 24.5 | 196.0 | 241.080 |
| 3 | ORD-2025-01-0004 | 2025-01-01 | South | Laptop Stand | 19 | 45.0 | 855.0 | 1051.650 |
| 4 | ORD-2025-01-0005 | 2025-01-01 | North | Wireless Mouse | 8 | 29.9 | 239.2 | 294.216 |
revenue_with_vat has been added using a 23% VAT multiplier: revenue × 1.23. For example, revenue of 209.30 USD becomes 257.439 USD, while 2,694.00 USD becomes 3,313.620 USD including VAT.
save the result as 2025-01-with-vat.xlsx
</>Export dataframe to Excel
df.to_excel("/home/piotr/Documents/sales/2025-01-with-vat.xlsx", index=False)The exported workbook contains the added revenue_with_vat column, calculated as revenue × 1.23. It was saved as 2025-01-with-vat.xlsx in /home/piotr/Documents/sales/.