excel-formulas-to-python

Read a file /home/piotr/Documents/sales/2025-01.xlsx

</>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 workbook contains 503 sales records across 7 fields: order identifier, order date, region, product, units, unit price, and revenue. The preview shows January 2025 orders with numeric unit and revenue values ready for analysis.

total revenue, average order value, and how many orders

Thinking

Calculating results

I need to create one code block to perform a calculation. Once I do that, I’ll just wait for the results to come in. It sounds straightforward enough, but I'm making sure that I set everything up properly before running it. I want to avoid any potential mix-ups with the output, so I'll double-check what I'm doing. Let’s get this right!

</>Summarize revenue metrics

summary = {
    "Total revenue (USD)": df["revenue"].sum(),
    "Average order value (USD)": df["revenue"].mean(),
    "Number of orders": df["order_id"].nunique(),
}

summary

{'Total revenue (USD)': np.float64(363879.3),
 'Average order value (USD)': np.float64(723.4180914512922),
 'Number of orders': 503}

Total revenue: 363,879.30 USD
Average order value: 723.42 USD
Orders: 503

The average is calculated across the 503 unique orders in the January 2025 file.

total revenue for the North region, and how many orders it had

</>Summarize North sales

north_summary = {
    "North total revenue (USD)": df.loc[df["region"].eq("North"), "revenue"].sum(),
    "North orders": df.loc[df["region"].eq("North"), "order_id"].nunique(),
}

north_summary

{'North total revenue (USD)': np.float64(114891.7), 'North orders': 152}

The North region generated 114,891.70 USD in total revenue from 152 orders.

total revenue for Keyboard orders in the North region

Thinking

Calculating product data

I need to respond with some calculations based on the loaded dataset. My main objective is to create a code that accurately processes keyboard orders, but I'm uncertain about the exact product we're looking for. I'll need to inspect samples to identify unique matching variants. It seems I should run a filter to find anything containing "Keyboard" and "North," then aggregate results. That should accomplish the task! I'll focus on creating that code now.

</>Sum North keyboard revenue

keyboard_north = df.loc[
    df["region"].eq("North") & df["product"].str.contains("Keyboard", case=False, na=False)
]

keyboard_north["revenue"].sum()

np.float64(17287.0)

Keyboard orders in the North region produced total revenue of 17,287.00 USD.

total revenue for every region, sorted highest first

Thinking

Looking into response code

I need to develop a response that uses a data frame, and it seems the current approach is to use a known method to request data for every region. I want to organize this into one table while using a groupby function to sort the information. It’s about making the data clear and accessible, ensuring the user can easily interpret the results with a structured format! Let’s see how to implement this effectively.

</>Summarize revenue by region

regional_revenue = (
    df.groupby("region", as_index=False)["revenue"]
      .sum()
      .sort_values("revenue", ascending=False)
      .rename(columns={"revenue": "total_revenue_usd"})
)

regional_revenue

	region	total_revenue_usd
1	North	114891.7
2	South	97687.4
3	West	83745.9
0	East	67554.3

Total revenue by region, highest first:

North: 114,891.70 USD
South: 97,687.40 USD
West: 83,745.90 USD
East: 67,554.30 USD

North generated the most revenue, exceeding South by 17,204.30 USD.