Cleaning a Broken Orders CSV

Goal

You cleanse a broken CSV the customer gave you by the rules, and become able to explain with numbers what you threw away and why.

Why it matters

The most dangerous thing in a cleansing script is deleting quietly. The customer will later ask, "why are our sales only this much?", and if you cannot state the number of discarded rows and the reasons at that point, every number in that project comes under suspicion. That is why this lab has a separate step for counting the discarded rows.

One more thing. The cleansing rules are not something the engineer decides alone. You have to ask the business owner, "rows with a negative amount look like refunds — should we exclude them or count them separately?" In this lab the rules are given in advance, but remember where those rules come from in the field.

Conditions for a valid row — it must satisfy all three below.

Exactly 4 fields (id,customer,amount,date)
customer is not empty
amount is an integer of 0 or more (negatives, empty values, and non-numeric values are all excluded)

Steps

Create /root/output and save the result of cleansing /opt/data/orders.csv as /root/output/clean.csv.
The first line of clean.csv must be the original header id,customer,amount,date exactly as it is.
Keep only the valid rows so that clean.csv has 19 lines including the header.
Check that the remaining rows satisfy all three conditions above.
Write the number of discarded rows in /root/output/bad_count.txt.
Write the sum of the amounts of the remaining rows after cleansing in /root/output/total.txt.
Write the date with the largest sum of amounts in /root/output/top_day.txt in the form YYYY-MM-DD.
Create /root/output/by_day.csv. The first line is date,total, and after that, one line per date with that day's sum of amounts.

Notes

awk -F, 'NR>1 && NF==4 && $2!="" && $3 ~ /^[0-9]+$/' 원본 (the placeholder is the original file)
For the total use awk -F, 'NR>1{s+=$3} END{print s}', and for the running total by date use awk -F, 'NR>1{s[$4]+=$3} END{for(d in s) print d","s[d]}' | sort
Common mistake 1: in step 7, choosing the day with the most rows. The criterion is the sum of amounts.
Common mistake 2: editing the original /opt/data/orders.csv directly. Customer data is only read.

Create the cleansed result file

Create /root/output and save the result of cleansing /opt/data/orders.csv as /root/output/clean.csv.

Create the /root/output directory and make clean.csv inside it. The original is /opt/data/orders.csv.

Preserve the header

The first line of clean.csv must be the original header id,customer,amount,date exactly as it is.

You must leave the first line of the original as it is. Only the data rows are the target of cleansing.

Keep only the valid rows

Keep only the valid rows so that clean.csv has 19 lines including the header.

Keep only the rows that satisfy all three rules. It must come to 19 lines including the header.

Check that all remaining rows satisfy the rules

Check that the remaining rows satisfy all three conditions above.

Check each of the three: the number of fields, an empty customer name, and a negative or non-numeric amount. If even one remains, it fails.

Count the discarded rows

Write the number of discarded rows in /root/output/bad_count.txt.

A cleansing that deletes quietly is a time bomb. Subtract the number of remaining rows from the number of data rows in the original.

Compute the sum of amounts

Write the sum of the amounts of the remaining rows after cleansing in /root/output/total.txt.

Add up only the amounts of the rows remaining after cleansing. If you use awk's field separator option, it is done in one line.

Find the day with the highest sales

Write the date with the largest sum of amounts in /root/output/top_day.txt in the form YYYY-MM-DD.

The criterion is the sum of amounts, not the row count. Accumulate the amounts by date and then pick the largest day.

Build the per-day aggregation table

Create /root/output/by_day.csv. The first line is date,total, and after that, one line per date with that day's sum of amounts.

Start with the date,total header, with one line per date. It reads well if you sort by date.