merge_excel_xlsx_sources_from_folder
load xlsx files from ~/Documents/sales, merge them and add source_file column, print how many files loaded
Thinking
Considering code for data processing
I need to provide code one step at a time, clarifying the file flow as the user indicated a directory. I might consider using code glob to load and inspect, but since data isn't loaded yet, I want to be careful. The user specifically instructed to merge and count, so I'll focus on inspecting conflicts first. I’ll give code to load, merge, and count source data, also discussing the initial shape and required column display. I might need to incorporate read_excel for xlsx files.
</>Load sales spreadsheets
from pathlib import Path
import pandas as pd
folder = Path("~/Documents/sales").expanduser()
files = sorted(folder.glob("*.xlsx"))
frames = []
for file in files:
data = pd.read_excel(file)
data["source_file"] = file.name
frames.append(data)
df = pd.concat(frames, ignore_index=True, sort=False) if frames else pd.DataFrame()
print(f"Files loaded: {len(files)}")
print(f"Shape: {df.shape}")
columns = df.columns.tolist()
print(f"Columns ({len(columns)}):", columns if len(columns) <= 100 else columns[:100] + ["..."])
df.head()Files loaded: 12 Shape: (5866, 10) Columns (10): ['order_id', 'order_date', 'region', 'product', 'units', 'unit_price', 'revenue', 'source_file', 'Revenue', 'discount_pct']
| order_id | order_date | region | product | units | unit_price | revenue | source_file | Revenue | discount_pct | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | ORD-2025-01-0001 | 2025-01-01 | North | Wireless Mouse | 7 | 29.9 | 209.3 | 2025-01.xlsx | NaN | NaN |
| 1 | ORD-2025-01-0002 | 2025-01-01 | East | Standing Desk | 6 | 449.0 | 2694.0 | 2025-01.xlsx | NaN | NaN |
| 2 | ORD-2025-01-0003 | 2025-01-01 | North | Desk Lamp | 8 | 24.5 | 196.0 | 2025-01.xlsx | NaN | NaN |
| 3 | ORD-2025-01-0004 | 2025-01-01 | South | Laptop Stand | 19 | 45.0 | 855.0 | 2025-01.xlsx | NaN | NaN |
| 4 | ORD-2025-01-0005 | 2025-01-01 | North | Wireless Mouse | 8 | 29.9 | 239.2 | 2025-01.xlsx | NaN | NaN |
Loaded and merged 12 Excel files from ~/Documents/sales into df.
The merged dataset contains 5,866 rows and 10 columns. Each row includes a source_file field identifying its originating workbook.
count rows per source_file
</>Count rows by source file
source_counts = (
df.groupby("source_file", dropna=False)
.size()
.reset_index(name="row_count")
.sort_values("source_file")
.reset_index(drop=True)
)
source_counts| source_file | row_count | |
|---|---|---|
| 0 | 2025-01.xlsx | 503 |
| 1 | 2025-02.xlsx | 434 |
| 2 | 2025-03.xlsx | 520 |
| 3 | 2025-04.xlsx | 458 |
| 4 | 2025-05.xlsx | 518 |
| 5 | 2025-06.xlsx | 475 |
| 6 | 2025-07.xlsx | 400 |
| 7 | 2025-08.xlsx | 411 |
| 8 | 2025-09.xlsx | 501 |
| 9 | 2025-10.xlsx | 512 |
| 10 | 2025-11.xlsx | 589 |
| 11 | 2025-12.xlsx | 545 |
Row counts vary from 400 to 589 per source file. 2025-11.xlsx has the most rows (589), while 2025-07.xlsx has the fewest (400). The 12 files together account for 5,866 rows.
total revenue
Thinking
Calculating total revenue
I need to calculate revenue while addressing any ambiguity between lower and uppercase formats. It seems like the user wants to get the total revenue, likely through a DataFrame sum. I should inspect non-null values, ensuring that I identify and sum totals safely. The revenue might be across multiple files, so I need to combine data from several columns and avoid duplicates. In short, a concise code to calculate the unified amount will be necessary, ensuring overlap is checked.
Revenue is split across revenue and Revenue columns due to inconsistent capitalization across source files. This combines both without double-counting and returns the total.
</>Compute total revenue
revenue_values = df["revenue"].combine_first(df["Revenue"])
total_revenue = revenue_values.sum()
total_revenuenp.float64(4265097.600000001)
Total revenue across all 5,866 records is 4,265,097.60 USD.
This total combines values from both revenue and Revenue, accounting for the capitalization difference between source files.
2025-07.xlsx uses Revenue instead of revenue - combine them into one revenue column
Thinking
Normalizing code efforts
I need to focus on writing code for normalization while preserving the revenue assigned. I should verify the steps to ensure everything’s right. One way to keep it clean is to include no table in the final output—just a bare DataFrame should work. I’ll consider using “mutate” as part of the process, making sure the answer remains concise and straightforward. This way, I can ensure clarity in the results!
Combine the two variants into a single revenue column, then remove the redundant Revenue column.
</>Fill missing revenue values
df["revenue"] = df["revenue"].combine_first(df["Revenue"])
df = df.drop(columns="Revenue")
print(f"Remaining missing revenue values: {df['revenue'].isna().sum()}")
df.head()Remaining missing revenue values: 0
| order_id | order_date | region | product | units | unit_price | revenue | source_file | discount_pct | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | ORD-2025-01-0001 | 2025-01-01 | North | Wireless Mouse | 7 | 29.9 | 209.3 | 2025-01.xlsx | NaN |
| 1 | ORD-2025-01-0002 | 2025-01-01 | East | Standing Desk | 6 | 449.0 | 2694.0 | 2025-01.xlsx | NaN |
| 2 | ORD-2025-01-0003 | 2025-01-01 | North | Desk Lamp | 8 | 24.5 | 196.0 | 2025-01.xlsx | NaN |
| 3 | ORD-2025-01-0004 | 2025-01-01 | South | Laptop Stand | 19 | 45.0 | 855.0 | 2025-01.xlsx | NaN |
| 4 | ORD-2025-01-0005 | 2025-01-01 | North | Wireless Mouse | 8 | 29.9 | 239.2 | 2025-01.xlsx | NaN |
Revenue has been merged into the lowercase revenue column using the non-null value from either field. The redundant Revenue column was removed.
The combined dataset now has one revenue column with no missing values across all 5,866 rows, including the 400 rows from 2025-07.xlsx.
total revenue per source_file
Thinking
Preparing group table code
I’m considering that we can only provide the group table code. I think it’s crucial to note that “revenue” should be unified. Maybe I should ensure the currency format in the DataFrame is numerical. I also need to gather source files for aggregation. It sounds like a plan to consolidate everything into one table. Got to keep things organized for clarity and ease of understanding!
The unified revenue column is ready to aggregate by source file.
</>Compute revenue by source
revenue_by_source = (
df.groupby("source_file", as_index=False)["revenue"]
.sum()
.sort_values("source_file")
.rename(columns={"revenue": "total_revenue"})
)
revenue_by_source["total_revenue"] = revenue_by_source["total_revenue"].round(2)
revenue_by_source| source_file | total_revenue | |
|---|---|---|
| 0 | 2025-01.xlsx | 363879.3 |
| 1 | 2025-02.xlsx | 333427.2 |
| 2 | 2025-03.xlsx | 372907.1 |
| 3 | 2025-04.xlsx | 327703.8 |
| 4 | 2025-05.xlsx | 392805.8 |
| 5 | 2025-06.xlsx | 352402.4 |
| 6 | 2025-07.xlsx | 285281.0 |
| 7 | 2025-08.xlsx | 302814.3 |
| 8 | 2025-09.xlsx | 349619.0 |
| 9 | 2025-10.xlsx | 366804.0 |
| 10 | 2025-11.xlsx | 442347.5 |
| 11 | 2025-12.xlsx | 375106.2 |
Total revenue by source file ranges from 285,281.00 USD in 2025-07.xlsx to 442,347.50 USD in 2025-11.xlsx.
| Source file | Total revenue |
|---|---|
| 2025-01.xlsx | 363,879.30 USD |
| 2025-02.xlsx | 333,427.20 USD |
| 2025-03.xlsx | 372,907.10 USD |
| 2025-04.xlsx | 327,703.80 USD |
| 2025-05.xlsx | 392,805.80 USD |
| 2025-06.xlsx | 352,402.40 USD |
| 2025-07.xlsx | 285,281.00 USD |
| 2025-08.xlsx | 302,814.30 USD |
| 2025-09.xlsx | 349,619.00 USD |
| 2025-10.xlsx | 366,804.00 USD |
| 2025-11.xlsx | 442,347.50 USD |
| 2025-12.xlsx | 375,106.20 USD |
The twelve source files total 4,265,097.60 USD in revenue.
save merged file to the same directory, name it 2025-full-year.xlsx
Save the merged dataset with the standardized revenue column to the same sales directory.
</>Save dataframe to Excel
output_file = folder / "2025-full-year.xlsx"
df.to_excel(output_file, index=False)
print(f"Saved: {output_file}")Saved: /home/piotr/Documents/sales/2025-full-year.xlsx
The merged full-year workbook was saved in the requested directory:
/home/piotr/Documents/sales/2025-full-year.xlsx
It contains the unified revenue column with no missing values.