| Course | BU 451 Programming for Business Analytics |
|---|---|
| Unit | Unit 5 |
| Paper type | Aggregation and joins |
| Length | About 960 words, 6 pages |
| Format | APA 7 student paper |
| School | Herzing University |
| Program | BS in Business Analytics |
| Updated | September 2026 |
Free sample paper for BU 451 Unit 5
642 Site-Days In, 642 Out, and One Duplicate Weather Row Caught on the Way: Aggregation and Joins for a Composite Paddle Rental Company's Season
Student Name
Business, Herzing University
BU 451: Programming for Business Analytics
Instructor Name
March 11, 2026
642 Site-Days In, 642 Out, and One Duplicate Weather Row Caught on the Way: Aggregation and Joins for a Composite Paddle Rental Company's Season
The Level of the Analysis, Stated First
Script 05_combine.py brings together four sources for Riverbend Paddle Co., the invented Chattanooga rental business analyzed in this course: the 18,619 cleaned rentals, daily weather, the three launch sites and the fleet of 86 craft. Before any grouping or joining, the script states the level at which the combined table will be built: one row per launch site per day of the season, April 1 to October 31, 2025. At that level, the owners' questions can be asked directly, such as how weather affects rentals at each site and how much of each site's fleet is used on a typical day.
Stating the level first prevents the most common error in combining data, which is joining tables at different levels and letting rows multiply unnoticed. Wickham et al. (2023) advise checking the key of every table before a join, meaning the column or columns that identify one row, and confirming that the key in the lookup table is unique.
Step 1: Aggregating Rentals to Site-Days
Rentals are grouped by site and date, producing for each site-day the number of rentals, total revenue from paid rentals and total rental hours from rentals with a known duration. The grouping produced 612 rows. That is fewer than the season's site-days, because a site-day with no rentals leaves no rows to group. A table built only from those 612 rows would silently drop every day a site had no customers, which would make average daily rentals look higher than they were.
The script therefore builds a complete calendar first: every date from April 1 to October 31, 214 days, crossed with the three site codes, 642 rows. The aggregated rentals are joined to that calendar with a left join, so every site-day is kept whether or not it had rentals. Of the 642 calendar rows, 612 matched a site-day with rentals. The 30 that did not are accounted for: 21 are the seven days the whole company closed for high water and storms, flagged as closed, and 9 are open days at CHA-2 on which no one rented, set to zero rentals and zero revenue.
Step 2: Joining Weather, and the Row That Multiplied
Weather joins on date. Each date should appear once in the weather file, so the join should leave the row count unchanged at 642. The first run produced 645 rows. The row count printed after the join made the problem visible at once: the weather file had 215 rows, not 214, because September 14 appeared twice, with two readings taken at different times of day. Joining a table with a duplicated key to a table with three rows for that date produced three extra rows, which would have counted September 14's rentals twice in every total.
The fix was made before the join, not after it. The script now checks that the weather date is unique and, finding a duplicate, keeps the reading marked as the daily summary and drops the other, printing "Weather: 1 duplicate date removed." After the fix the join returned 642 rows, and every row matched a weather record.
| Join | Rows before | Lookup table rows | Key unique in lookup? | Rows after | Unmatched |
|---|---|---|---|---|---|
| Rentals to calendar (left) | 642 calendar rows | 612 site-days | Yes | 642 | 30, all explained |
| Weather, first attempt | 642 | 215 | No: September 14 twice | 645 | 0 |
| Weather, after fix | 642 | 214 | Yes | 642 | 0 |
| Sites | 642 | 3 | Yes | 642 | 0 |
| Fleet capacity by site | 642 | 3 | Yes, after grouping fleet by site | 642 | 0 |
Steps 3 and 4: Sites and Fleet
The sites table, three rows with site code, name, river mile and parking spaces, joins on site code and leaves 642 rows. The fleet table lists one row per craft, 86 rows, so it cannot be joined directly: joining it on site code would multiply each site-day by the number of craft at that site. The script first aggregates the fleet to one row per site, counting craft by type, which gives 34 craft at CHA-1, 22 at CHA-2 and 30 at CHA-3, and then joins. Aggregating before joining is the general rule the whole script follows: bring each table to the level of the analysis, confirm its key is unique, then join. McKinney (2022) describes the difference between one-to-one, many-to-one and many-to-many joins, and every join here is designed to be many-to-one.
Why Rentals Were Not Joined to Weather Directly
An easier route was available: join the weather to each of the 18,619 rentals by date, then add up. It would have worked for revenue, but it answers a different question. At the rental level, a rainy day with few customers contributes few rows, so rainy days would carry little weight in any average of weather against rentals; the days with the most rentals would dominate. At the site-day level, every day counts once, which is what the owners' question needs: on days like this, how many people rent? Choosing the level first, rather than joining whatever tables were convenient, is what exposed the need for the calendar and the zero-rental days. The rental-level join is kept as a separate, clearly named table for the pricing questions of a later unit, where each rental is the right unit of analysis.
The Combined Table and a First Look
The final table has 642 rows and 16 columns and is written to data/processed/site_days.csv. Summed across the season, it contains 8,210 rentals at CHA-1, 4,390 at CHA-2 and 6,019 at CHA-3, 18,619 in total, exactly the number of cleaned rentals, and revenue of $894,750. The script checks both totals against the cleaned rental table and stops if they differ, so no rental can be lost or doubled in the combination without the script saying so. Detailed analysis waits for the next unit; this script's job ends when the combined table is proven complete. Tidy data principles, one row per observation at a stated level, guided the design throughout (Wickham, 2014). Riverbend Paddle Co. and every count here are composites for this course.
References
McKinney, W. (2022). Python for data analysis: Data wrangling with pandas, NumPy, and Jupyter (3rd ed.). O'Reilly Media.
Wickham, H. (2014). Tidy data. Journal of Statistical Software, 59(10), 1-23. https://doi.org/10.18637/jss.v059.i10
Wickham, H., Çetinkaya-Rundel, M., & Grolemund, G. (2023). R for data science: Import, tidy, transform, visualize, and model data (2nd ed.). O'Reilly Media.
What the BU 451 Unit 5 instructions ask for
The fifth unit of BU 451 asks students to aggregate data and join tables from different sources. Common requirements include stating the level of analysis, grouping records to that level, joining on named keys, reporting row counts before and after each join, explaining unmatched records and verifying that totals are preserved. Many instructors ask which type of join to use and what to do when row counts change. Students should check that keys in lookup tables are unique before joining. Composite or supplied data are expected. The submission typically includes code, a join audit table and a written explanation in APA 7 that accounts for every record lost or gained along the way.
Inside the BU 451 Unit 5 example
The paper begins by stating the site-day level and why it matters. The aggregation section groups rentals, notes that only 612 site-days result and builds a full calendar so zero-rental days are kept, explaining all 30 unmatched rows. The weather section describes the first join producing 645 rows, the duplicated date behind it and the fix made before joining. A table audits five joins with rows before, lookup rows, key uniqueness, rows after and unmatched counts. A section explains aggregating the fleet before joining. A section explains why rentals were not joined to weather directly. The close reconciles totals and describes the saved table.
Where the points sit in the BU 451 Unit 5 rubric
Herzing's joins rubric typically weighs a stated level of analysis, correct aggregation, key checks, row count auditing and reconciliation, with writing and presentation. The level criterion earns full credit when stated before any join. Aggregation scores highest when grouping matches that level and missing groups are handled. Key checks reward confirming uniqueness in lookup tables. The row count criterion rewards printing counts on both sides of every join and explaining any change. Reconciliation earns points when totals after combining match the source. Instructors often credit students who show a problem their checks caught, since that demonstrates the checks work rather than merely claiming them. A short audit table of every join, with counts on both sides, often earns more credit than several paragraphs describing the same work.
BU 451 Unit 5 help before you submit
Aggregation and join exercises commonly lose points when rows multiply after a join and nobody notices. Another frequent problem is aggregating only the records present, so days or groups with no activity disappear and averages rise. Students often join tables at different levels, such as individual craft to site-days, without aggregating first. Some choose inner joins that silently drop unmatched records. Others never check that totals survive the combination. Explanations frequently skip unmatched rows or wave them away. Finally, joining tables from systems at work can mix confidential data into coursework; a supplied dataset shows the same technique without that exposure.
Get a BU 451 Unit 5 example written to your instructions
Forward whatever your classroom shows for BU 451 Unit 5, rubric included, along with the files the assignment asks you to combine. The custom example declares its output level, names each key, prints counts on both sides of every join, states the expected result first and counts what failed to match. First one free, 24 to 48 hours.
More BU 451 papers and business analytics samples
- BU 451 Unit 1: Environment and setup exercise
- BU 451 Unit 2: Data import and inspection script
- BU 451 Unit 3: Cleaning and transformation in code
- BU 451 Unit 4: Control flow and functions
- BU 451 Unit 6: Analysis script with output
- BU 451 Unit 7: Error handling and validation
- BU 451 Unit 8: Reproducible analysis submission
- BU 351 Unit 5: Predictive exercise
- BU 407 Unit 5: Sensitivity table analysis
- BU 454 Unit 5: Distribution and relationship charts
- BU 455 Unit 5: Distributed processing exercise
BU 451 Unit 5 questions, answered
Which join should the example use?
Whichever one answers the question the assignment asked, and the submission should say why. Keeping every record from one side is right when the question is about that population; keeping only matched records is right when a partner is required for the answer to mean anything. What loses credit is choosing without stating the reason, because then a marker cannot tell a decision from a default.
What if the row count changes after a join?
Write down what happened rather than adjusting until the number looks right. A rise means the key repeats somewhere you thought it was unique, and a fall means records found no partner; both are findings and both belong in the output with a count. Fixing the count silently is the version that costs marks, because the underlying data problem survives into the next unit.
Can I join two tables from systems I use at work?
Not for a graded artifact. Two internal tables joined together say more about a business than either does alone, and the combination is exactly the kind of thing an employer treats as confidential. Access granted to you for your job is not permission to publish, and a submitted file is published to the school. Work from what the unit supplies instead.
Where can I find a free BU 451 Unit 5 sample paper?
No payment is needed to read the BU 451 Unit 5 aggregation and joins example, which shows the join audit table, the calendar approach and margin notes. For different sources, send the details and a custom version is drafted at no charge, usually inside two days.
What if the row count changes after a join in BU 451 Unit 5?
Stop and find out why before going further. In the sample, 642 rows became 645 because the weather file listed one date twice; the duplicate was removed before joining, and the count returned to 642.