Clean and Aggregate Transactions for Finance Dashboard

transactions

id | user_id | amount | timestamp | category

1 | 1001 | 19.99 | 2023-01-01 09:00:00 | grocery

2 | 1001 | 5.50 | 2023-01-02 11:12:30 | coffee

3 | 1002 | 45.00 | 2023-01-03 14:45:10 | electronics

4 | 1003 | 12.00 | 2023-01-01 08:30:25 | transport

Scenario

You have a daily transactions dataset that must be cleaned and summarized for a finance dashboard.

Question

Using pandas, apply a lambda function to create a new column flagging high-value purchases (amount > $ 40).

Build a dictionary that maps the raw text in the category column to standardized names (e.g., 'grocery', 'food & bev', 'coffee' -> 'Food'). Loop through this dictionary to transform the DataFrame.

Aggregate total spend and number of transactions per user_id and return a tidy DataFrame.

Hints

Show vectorized pandas code; avoid row-by-row loops except for the dictionary mapping step.

Flag high-value purchases, normalize categories, and aggregate spend per user

You are given a transactions table. Write a SQL query that:

1. Flags high-value purchases with a new column is_high_value where amount > 40 (1 for high-value, 0 otherwise).
2. Normalizes the category column into a new column category_standardized using the following mapping:
   • 'grocery', 'food & bev', 'coffee' -> 'Food'
   • 'electronics' -> 'Electronics'
   • 'transport' -> 'Transport'
   • any other value -> 'Other'
3. Returns a tidy result set aggregated per user_id with total_spend (sum of amount) and transaction_count (number of rows), after applying the above cleaning steps. Order the final result by user_id.

Table transactions

column	type	constraints
id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL
amount	DECIMAL(10,2)	NOT NULL
timestamp	TIMESTAMP	NOT NULL
category	VARCHAR(50)	NOT NULL

Sample data:

amount	category	id	timestamp	user_id
19.99	grocery	1	2023-01-01 09:00:00	1001
5.5	coffee	2	2023-01-02 11:12:30	1001
45	electronics	3	2023-01-03 14:45:10	1002
12	transport	4	2023-01-01 08:30:25	1003

Expected output

A per-user aggregation showing total spend (sum of amount) and transaction count after creating a high-value flag and normalizing category text, ordered by user_id.

total_spend	transaction_count	user_id
25.49	2	1001
45	1	1002
12	1	1003

Hints

1. Use a CASE expression to create the is_high_value flag based on the amount column.
2. Use another CASE expression with LOWER(category) to map raw categories to standardized names.
3. Perform the per-user aggregation with GROUP BY user_id and compute SUM(amount) and COUNT(*).