如何用Python创建映射cartId与购买商品的数据表（含数量统计）

Hey there! Let's tackle this problem step by step. You want to map cart IDs to product names (not their IDs) along with how many of each was purchased, then output a clean two-column table. Here's how to do this in Python, using pandas—the go-to library for tabular data tasks like this.

Step 1: Set Up Your Data #

First, let's assume you have two core datasets to work with:

• A cart transactions dataset (containing cart IDs, product IDs, and purchase quantities)
• A product lookup dataset (to map product IDs to human-readable names)

Here's sample data to simulate this:

import pandas as pd

# Sample cart transactions: cartId, productId, quantity
cart_data = pd.DataFrame({
    'cartId': [101, 101, 102, 103, 103, 103],
    'productId': [1, 2, 1, 2, 3, 3],
    'quantity': [2, 1, 3, 1, 2, 1]
})

# Sample product lookup: productId, productName
product_lookup = pd.DataFrame({
    'productId': [1, 2, 3],
    'productName': ['Wireless Headphones', 'USB-C Charger', 'Laptop Sleeve']
})

Step 2: Merge Data to Attach Product Names #

We need to link the cart data with the product names using productId as the common key:

# Merge the two DataFrames to add product names to cart entries
cart_with_product_names = pd.merge(
    cart_data,
    product_lookup,
    on='productId',
    how='left'  # Preserve all cart entries, even if a product name is missing
)

Step 3: Aggregate Quantities per Cart & Product #

If a single cart has multiple entries for the same product, we'll sum those quantities to get the total purchased:

# Group by cartId and productName, then calculate total quantity per group
aggregated_data = cart_with_product_names.groupby(
    ['cartId', 'productName'],
    as_index=False
)['quantity'].sum()

Step 4: Format as a Two-Column Table #

Now we can structure this into your requested two-column format. There are two practical options depending on your needs:

Option 1: One Row per Cart-Product Pair #

If you want each cart-product combination as a separate row (with cartId in one column, and "Product Name: Quantity" in the second):

# Combine product name and quantity into a single descriptive column
aggregated_data['Product & Quantity'] = aggregated_data.apply(
    lambda row: f"{row['productName']}: {row['quantity']}",
    axis=1
)

# Keep only the two desired columns
final_table = aggregated_data[['cartId', 'Product & Quantity']]

# Print the clean table
print(final_table.to_string(index=False))

Output:

cartId        Product & Quantity
    101  Wireless Headphones: 2
    101        USB-C Charger: 1
    102  Wireless Headphones: 3
    103        USB-C Charger: 1
    103       Laptop Sleeve: 3

Option 2: One Row per Cart (All Products in One Column) #

If you prefer each cart to be a single row, with all product-quantity pairs listed in the second column:

# Group by cartId and concatenate all product-quantity pairs into one string
cart_summary = aggregated_data.groupby('cartId').apply(
    lambda x: ', '.join([f"{row['productName']}: {row['quantity']}" for _, row in x.iterrows()])
).reset_index(name='Products & Quantities')

# Print the condensed table
print(cart_summary.to_string(index=False))

Output:

cartId                          Products & Quantities
    101  Wireless Headphones: 2, USB-C Charger: 1
    102                  Wireless Headphones: 3
    103        USB-C Charger: 1, Laptop Sleeve: 3

Step 5: Export to a File (Optional) #

If you want to save this table for later use, pandas makes it easy to export to CSV or Excel:

# Save to CSV
final_table.to_csv('cart_product_summary.csv', index=False)

# Save to Excel
final_table.to_excel('cart_product_summary.xlsx', index=False)

Just swap out the sample data with your actual datasets, and pick the formatting option that fits your workflow best!

内容的提问来源于stack exchange，提问作者Sami