# Expense Data Cleaning - Reconciliation Report

## Row Count Reconciliation

- **Input rows**: 14
- **Output rows (cleaned.csv)**: 14
- **Status**: All source rows preserved in order as required

No rows were deleted or merged. All 14 source rows appear in cleaned.csv with original fields intact and normalized fields added.

---

## Data Quality Issues Summary

10 exception records were identified and logged in exceptions.csv:

### Automatic Transformations Applied (no decision needed)
1. **Row 1** - Person name whitespace trimming: " Riley Chen " → "Riley Chen"
2. **Row 2** - Cost center uppercase: "ops" → "OPS"
3. **Row 11** - Refund parentheses conversion: "(45.00)" → -45.0

### Issues Requiring Decisions
1. **Rows 5 & 6** - Exact business-record duplicate: Both records retained, flagged for review. Decision: determine if one should be removed.
2. **Rows 7 & 8** - Conflicting expense ID (001006) with different amounts ($120.00 vs $140.00). Both retained. Decision: reconcile and confirm correct amount.
3. **Row 9** - Date format ambiguous: 03/04/2026 could be DD/MM or MM/DD (locale not recorded). Original field unchanged. Decision: confirm locale, convert to YYYY-MM-DD.
4. **Row 10** - Missing currency value. Amount is 95.00 but currency unknown. Decision: confirm currency type.
5. **Row 13** - Amount is TBC (To Be Confirmed), awaiting invoice. Decision: provide final amount when invoice received.
6. **Row 14** - Amount format ambiguous: "1.234,50" uses European locale (. as thousands separator, , as decimal). Original field unchanged. Decision: confirm locale and convert.

---

## Numeric Totals (Scoped)

These totals include only rows with **unambiguous, fully clear numeric values** and exclude rows requiring decisions, as per the brief.

### USD Totals
**Included rows**: 1, 2, 4, 5, 11

| Row | Expense ID | Person | Amount | Notes |
|-----|-----------|--------|--------|-------|
| 1 | 001001 | Riley Chen | 1,250.00 | Annual software renewal |
| 2 | 001002 | Casey Patel | 85.50 | Taxi receipt |
| 4 | 001004 | Alex Morgan | 19.99 | Team lunch |
| 5 | 001005 | Jordan Lee | 65.00 | Printer supplies (first occurrence; row 6 is exact duplicate, excluded) |
| 11 | 001009 | Alex Morgan | -45.00 | Refund (parentheses converted to negative) |

**USD Scoped Total: 1,375.49**

### EUR Totals
**Included rows**: 3, 12

| Row | Expense ID | Person | Amount | Notes |
|-----|-----------|--------|--------|-------|
| 3 | 001003 | Sam Taylor | 240.00 | Workshop registration |
| 12 | 001010 | Jordan Lee | 180.00 | Training |

**EUR Scoped Total: 420.00**

---

## Rows Excluded from Scoped Totals and Why

| Row | Expense ID | Reason for Exclusion |
|-----|-----------|---------------------|
| 6 | 001005 | Exact duplicate of row 5; included row 5 only to avoid double-counting |
| 7 | 001006 | Conflicting amount with row 8 ($120.00 vs $140.00); decision needed to determine correct value |
| 8 | 001006 | Conflicting amount with row 7 ($140.00 vs $120.00); decision needed to determine correct value |
| 9 | 001007 | Date locale unknown (03/04/2026); row requires decision; retained with original amount in case needed, but excluded from scoped total |
| 10 | 001008 | Currency field is empty; cannot confirm currency or include in currency-specific totals |
| 13 | 001011 | Amount is TBC (To Be Confirmed); not yet finalized; awaiting invoice |
| 14 | 001012 | Amount locale unknown ("1.234,50"); cannot parse as definitive numeric value; decision needed |

---

## Normalized Fields

The cleaned.csv includes the following normalized fields alongside originals:

- **person_normalized**: Trimmed leading/trailing whitespace
- **cost_center_normalized**: Uppercase
- **date_normalized**: Kept in YYYY-MM-DD format; empty for ambiguous dates pending clarification
- **currency_normalized**: Preserved; empty if original was empty
- **amount_normalized**: Converted to float where unambiguous; refunds converted from parentheses to negative values; empty for non-numeric or ambiguous locale values

---

## Notes

- **No currency conversions**: USD and EUR totals are reported separately per brief requirements. No exchange rate conversion is applied.
- **Scoped totals only**: These are totals of clearly identified, eligible records for analysis purposes, not a final accounting total. Records requiring decisions are explicitly excluded.
- **Original preservation**: All original fields (date, amount, currency) are retained unchanged. Normalized fields allow for audit trail and decision-making.
- **Source row traceability**: The `source_row` column in cleaned.csv preserves the original row number for cross-reference with input.md.

---

## Outstanding Decisions Required

Before final reconciliation or payment processing, the following must be resolved:

1. **Rows 5 & 6**: Confirm whether the duplicate record should be removed or both retained (e.g., separate cost centers or timing).
2. **Rows 7 & 8**: Reconcile conflicting hotel adjustment amounts; determine which is correct or if both should be processed separately.
3. **Row 9**: Clarify date format (is 03/04/2026 March 4 or April 3?).
4. **Row 10**: Provide currency type for the $95.00 training expense.
5. **Row 13**: Obtain final invoice amount to replace TBC.
6. **Row 14**: Confirm amount locale (is 1.234,50 EUR = 1,234.50 or something else?).

No further changes will be applied until these decisions are made.