如何基于重复值合并数据行？家庭ID维度消费数据合并问询

Alright, let's solve this problem where you need to collapse duplicate Household ID rows into a single row, with all the spending values summed up for each category. Here are two straightforward methods using tools you're likely working with:

If you're working with Python, Pandas makes this aggregation super simple:

• First, import Pandas and load your dataset (swap your_data.csv with your actual file path or data source):

import pandas as pd

# Load your dataset
df = pd.read_csv("your_data.csv")

• Next, group the data by Household ID and sum up each of the spending columns. The as_index=False parameter keeps Household ID as a regular column instead of setting it as the DataFrame index:

# Aggregate spending totals per household
aggregated_df = df.groupby("Household ID", as_index=False).sum()

• You can preview the result with print(aggregated_df) to verify each Household ID has one row with total spending across all categories.

Quick note: If your spending columns have non-numeric values (like text or empty strings), clean them first using pd.to_numeric(col, errors='coerce') to convert invalid entries to NaN, which sum() will ignore.

If your data is stored in a database, a GROUP BY query will do the trick. Let's assume your table is named household_spending:

SELECT 
    "Household ID",
    SUM(restaurantspend) AS total_restaurant_spend,
    SUM(groceryspend) AS total_grocery_spend,
    SUM(foodtruckspend) AS total_foodtruck_spend
FROM household_spending
GROUP BY "Household ID";

• This query returns each unique Household ID alongside the total amount spent in each category. Adjust the column quoting (use backticks ` instead of double quotes if you're using MySQL) to match your SQL dialect.

Quick note: Ensure your spending columns are numeric types (INT, DECIMAL, FLOAT, etc.)—if they're stored as strings, the SUM function won't work correctly.

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