{"id":119,"date":"2025-11-05T21:52:26","date_gmt":"2025-11-05T21:52:26","guid":{"rendered":"http:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/?page_id=119"},"modified":"2025-11-05T21:52:26","modified_gmt":"2025-11-05T21:52:26","slug":"experience-cleaning-tabular-data-using-python","status":"publish","type":"page","link":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/experience-cleaning-tabular-data-using-python\/","title":{"rendered":"Experience cleaning tabular data using Python"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\"><strong>Exercise \u2014 Customers Data Cleaning &amp; KPI\u2011Driven Data Warehouse (Python Implementation) \u2014 Tasks Only<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Prepared for: Genoveva Vargas-Solar \u2014 Date: 05 Nov 2025<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Context &amp; Goal<\/h1>\n\n\n\n<p class=\"wp-block-paragraph\">You will implement, in Python, a complete workflow to: (1) clean the raw Excel dataset customers.xlsx, (2) compute KPIs from the cleaned data, and (3) design and populate a small star\u2011schema data warehouse to answer those KPIs using SQL. This document lists explicit tasks only (no starter code). Mirror the cleaning actions defined in the Talend exercise.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Materials &amp; Constraints<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Dataset: \/mnt\/data\/customers.xlsx (sheet 1)\u00a0<a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/1pZlT2gtJNeZk7Myo210JMhec6-tCPQ8t\/edit?usp=sharing&amp;ouid=106532920956410672496&amp;rtpof=true&amp;sd=true\">https:\/\/docs.google.com\/spreadsheets\/d\/1pZlT2gtJNeZk7Myo210JMhec6-tCPQ8t\/edit?usp=sharing&amp;ouid=106532920956410672496&amp;rtpof=true&amp;sd=true<\/a><\/li>\n\n\n\n<li>Notebook with the exercise [<strong><a href=\"https:\/\/gist.github.com\/gevargas\/feb23daf182a05e2b7cee8ac9ad59eb0\" target=\"_blank\" rel=\"noreferrer noopener\">GIST<\/a><\/strong>]<\/li>\n\n\n\n<li>Output (to produce): customers_clean.csv, SQL queries answering KPIs, short report.<\/li>\n\n\n\n<li>Implementation: Python (pandas), PostgreSQL.<\/li>\n\n\n\n<li>Do not hard\u2011code file paths; make your script\/notebook parameterizable.<\/li>\n<\/ul>\n\n\n\n<h1 class=\"wp-block-heading\">Part 0 \u2014 Reproducible Setup<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Create a project folder with subfolders: data\/, notebooks\/ or src\/, sql\/, reports\/.<\/li>\n\n\n\n<li>Record your environment (Python version; main library versions) in a README.<\/li>\n\n\n\n<li>Parameterise input\/output locations and database connection via environment variables or a config file.<\/li>\n\n\n\n<li>Log each processing step (start\/end time; counts of rows affected; warnings).<\/li>\n<\/ul>\n\n\n\n<h1 class=\"wp-block-heading\">Part 1 \u2014 Data Understanding &amp; Profiling<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Open customers.xlsx (first sheet) and list the columns found (exact names).<\/li>\n\n\n\n<li>Map original column names to normalised names you will use (e.g., First_Name \u2192 first_name).<\/li>\n\n\n\n<li>Produce a profiling table with: dtype, #non\u2011null, #nulls, %nulls, #unique for each column.<\/li>\n\n\n\n<li>Identify and list suspected quality issues with examples (e.g., invalid emails, malformed phones, ZIP padding, date parsing, duplicates).<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Deliverable: a one<\/strong><strong>\u2011page \u2018Data Quality Findings\u2019 note summarizing issues and initial hypotheses.<\/strong><\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Part 2 \u2014 Cleaning Tasks (Mirror the Talend Recipe)<\/h1>\n\n\n\n<p class=\"wp-block-paragraph\">Apply all tasks in order. Document any assumption or deviation.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Rename columns using the following mapping: Id\u2192id, First_Name\u2192first_name, Last_Name\u2192last_name, Gender\u2192gender, Age\u2192age, Occupation\u2192occupation, MaritalStatus_Out\u2192marital_status, Salary_Out\u2192salary_band, Address\u2192address, City\u2192city, State\u2192state, Zip\u2192zip, Phone\u2192phone, Email\u2192email, SubDate\u2192subscription_date, Number_of_rentals\u2192rentals_count.<\/li>\n\n\n\n<li>Trim whitespace and apply proper casing to text fields:\u00a0first_name, last_name, city, occupation, and\u00a0address.<\/li>\n\n\n\n<li>Normalise email: lowercase, correct common domains (e.g.,\u00a0gmal.com\u2192gmail.com, gnail.com\u2192gmail.com, hotmial.com\u2192hotmail.com, yaho.com\u2192yahoo.com, outlok.com\u2192outlook.com, icloud.cmo\u2192icloud.com), and flag\u00a0email_valid\u00a0via a regex check.<\/li>\n\n\n\n<li>Normalise phone: remove non\u2011digits, flag phone_valid if 10 digits, and store a canonical 10\u2011digit display format.<\/li>\n\n\n\n<li>Normalise state: uppercase 2\u2011letter code; create state_valid flag if in the accepted list.<\/li>\n\n\n\n<li>Normalise zip: store as 5\u2011character, left\u2011padded with zeros if needed (truncate if longer than 5).<\/li>\n\n\n\n<li>Parse\u00a0subscription_date; create signup_year\u00a0and\u00a0signup_month\u00a0derived attributes.<\/li>\n\n\n\n<li>Standardise gender values (e.g., Title Case).<\/li>\n\n\n\n<li>Standardise\u00a0marital_status; replace blanks with \u2018Unknown\u2019.<\/li>\n\n\n\n<li>Coerce\u00a0rentals_count\u00a0to non\u2011negative integer; replace invalid\/missing with 0; report how many values were changed.<\/li>\n\n\n\n<li>Create\u00a0full_name = first_name + &#8216; &#8216; + last_name.<\/li>\n\n\n\n<li>De\u2011duplicate records by non\u2011null email, keeping the first occurrence; report how many duplicates you removed.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Export the cleaned dataset as customers_clean.csv, including a short changelog with the row counts before\/after each step. Are the data clean enough for building a DW?<\/strong><\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Part 3 \u2014 KPIs (Compute from Cleaned Data)<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Total customers.<\/li>\n\n\n\n<li>Email contactable rate (%) = share of records with email_valid = true.<\/li>\n\n\n\n<li>Phone contactable rate (%) = share of records with phone_valid = true.<\/li>\n\n\n\n<li>Duplicates removed (count) during de\u2011duplication step.<\/li>\n\n\n\n<li>Average and median rentals (rentals_count).<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>High\u2011value share (%) = share of customers with rentals_count \u2265 20 (justify threshold if you adjust it).<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Valid state rate (%) among records with a state value present.<\/li>\n\n\n\n<li>Monthly new customers: counts grouped by subscription year\u2011month.<\/li>\n\n\n\n<li>Top 10 states by number of customers.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Deliverable: a one\u2011page KPI summary (table\/figure) with a 3\u20135 line interpretation.<\/strong><\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Part 4 \u2014 DW Modelling &amp; Construction (Use the Cleaned Dataset)<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Analyse the model a minimal star schema to answer all KPIs. Use four tables:\u00a0dim_date, dim_geo, dim_customer, fact_customer.<\/li>\n\n\n\n<li>Execute the DDL to create the schema (PostgreSQL preferred). Include FK constraints and helpful indexes (e.g., on date_key and geo_key).<\/li>\n\n\n\n<li>Populate dim_date using the min\/max of subscription_date; populate dim_geo from distinct (city, state, zip).<\/li>\n\n\n\n<li>Populate dim_customer from the cleaned dataset (one row per customer_key).<\/li>\n\n\n\n<li>Populate fact_customer by joining cleaned data to dim_geo and dim_date to resolve keys. Ensure referential integrity (no orphan FKs). \u2013\u00a0<strong>What do you observe regarding the status of your \u201ccleaned data set\u201d?<\/strong><\/li>\n\n\n\n<li>Verify row counts: #fact rows equals #cleaned customers; check FK join rates; report any anomalies.<\/li>\n\n\n\n<li><strong>Export your DDL and discuss (row counts, anomalies).<\/strong><\/li>\n<\/ul>\n\n\n\n<h1 class=\"wp-block-heading\">Part 5 \u2014 KPI Queries (Run on the DW)<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Write one SQL query per KPI to reproduce: monthly new customers, contactability rates, top states, high\u2011value share, average\/median rentals (if supported by SQL dialect).<\/li>\n\n\n\n<li>Execute the queries in your SQL client or from Python; save results as tables or figures in your report.<\/li>\n\n\n\n<li>Cross\u2011check SQL results vs Python KPI values from Part 3; explain any discrepancy in 3\u20136 lines.<\/li>\n<\/ul>\n\n\n\n<h1 class=\"wp-block-heading\">Part 6 \u2014 Packaging &amp; Submission<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Deliverables: (1) customers_clean.csv, (2) \u2018Data Quality Findings\u2019 note, (3) KPI summary, (4) SQL queries file (5) short README with run instructions.<\/li>\n\n\n\n<li>The GitHub repository structure is transparent and reproducible; scripts\/notebooks can be re-run end-to-end without manual edits.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Exercise \u2014 Customers Data Cleaning &amp; KPI\u2011Driven Data Warehouse (Python Implementation) \u2014 Tasks Only Prepared for: Genoveva Vargas-Solar \u2014 Date: 05 Nov 2025 Context &amp; Goal You will implement, in Python, a complete workflow to: (1) clean the raw Excel &hellip; <a href=\"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/experience-cleaning-tabular-data-using-python\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":11,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-119","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/pages\/119","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/comments?post=119"}],"version-history":[{"count":1,"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/pages\/119\/revisions"}],"predecessor-version":[{"id":121,"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/pages\/119\/revisions\/121"}],"wp:attachment":[{"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/media?parent=119"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}