Count Bottles and Cans Across Pallets

Problem

You are given two DataFrames. `pallet` has columns `pallet_id`, `crate_id`, `bottle_qty`, `can_qty`; its `crate_id` may be missing when a pallet carries no crate. `crate` has columns `crate_id`, `bottle_qty`, `can_qty`.

For every pallet, its contribution is its own quantities plus the quantities of the crate it references (zero when it has no crate). Return the grand total bottles and cans across all pallets as a single row. Output columns: `bottle_qty`, `can_qty`.

Input data

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 1 row with the columns bottle_qty, can_qty.

Starter code (Pandas (Python))

import pandas as pd

def count_bottles_and_cans(pallet, crate) -> pd.DataFrame:
    # Your code here
    return pallet

Related Pandas questions

• 7-Day Rolling Signup AverageMedium
• Accounts Streaming From Two Devices at OnceMedium
• Agent Resolution RateMedium
• Agents With a Dominant Contact ChannelMedium
• Airbnb Popularity Bucket PricingMedium
• Amazon Unique Shipments per MonthMedium