Code School of Analytics
ExpertData Analysis50 points

Clean the messy order export

A marketplace merged order exports from three systems into orders_raw.csv and the data is dirty. Finance needs revenue per country after cleaning.

  • order_id: order code (may appear twice)
  • country: customer country, with random upper/lower case, extra spaces, or empty
  • category: product category (always clean)
  • units: whole number, but sometimes N/A, empty or padded with spaces
  • unit_price: USD whole number, sometimes empty or N/A

Task: input line 1 is an integer N, line 2 a category or ALL. Process rows in file order. A row is invalid if, after stripping spaces, its country is empty, or its units or unit_price is not made only of digits. Otherwise, if its order_id was already kept earlier, it is a duplicate (only the first valid row of an id is kept; an invalid row never blocks a later one). Print Kept: K, Duplicates dropped: D, Invalid dropped: V (these ignore the category filter). Then take the kept rows of the chosen category (all if ALL), add units x unit_price per country (country = stripped, lowercase, shown with .title()) and print the top N as RANK. Country REVENUE by revenue descending, ties by country name.

Files your program can read

orders_raw.csv183 lines
order_id,country,category,units,unit_price
R-5001,Japan,Books,8,23
R-5002,Canada,Books,11,20
R-5003,,Electronics,10,159
R-5004,MEXICO,Home,5,33
R-5005, Nigeria,Home,10,44
R-5006,Japan,Home,N/A,52
R-5007,Canada,Electronics,4,53
...

Open them by name, for example open("orders_raw.csv").

Sample test

Input

3
ALL

Expected output

Kept: 142
Duplicates dropped: 11
Invalid dropped: 29
1. United Kingdom 23867
2. Japan 12455
3. Germany 11505

Your answer is also checked against 5 hidden tests.

Need a hint?

str.isdigit() is False for an empty string and for 'N/A'. Check validity before the duplicate test.