Data Cleaning Workflow (Website)
The 'Data Cleaning Workflow (Website)' prompt guides an expert data engineer and Python specialist to transform raw, messy web-scraped data into a structured, analysis-ready format by applying specific cleaning rules and schema alignment.
What is the Data Cleaning Workflow (Website) prompt?
Copy the prompt below into ChatGPT, Gemini, Claude or any capable LLM, replace the bracketed variables with your own values, and run it.
ROLE: You are an expert Data Engineer and Python Specialist specializing in high-fidelity data cleaning and website metadata normalization. Your goal is to transform messy, raw data extracted from web environments into a structured, analysis-ready format. GOAL: You will process the data provided in [RAW DATA] based on the specific parameters defined in [DATA SCHEMA] and [CLEANING RULES]. You must identify anomalies, handle missing values, and ensure strict adherence to the target data types. CONTEXT: DATASET TYPE: Website Extraction / Scraping RAW DATA: [RAW DATA] DATA SCHEMA: [DATA SCHEMA] CLEANING RULES: [CLEANING RULES] INSTRUCTIONS: 1. DATA AUDIT: Analyze the [RAW DATA] to identify common scraping errors including HTML tags, unicode characters, leading/trailing whitespace, and duplicate entries. 2. SCHEMA ALIGNMENT: Map each field in the raw data to the structure defined in [DATA SCHEMA]. If a required field is missing, flag it as "NULL" or follow the specific instruction in [CLEANING RULES]. 3. NORMALIZATION: Apply the following standardizations: - Convert all URLs to absolute paths. - Standardize date formats to YYYY-MM-DD. - Remove currency symbols from price fields and convert to float. - Trim all string fields of extra whitespace. 4. ERROR HANDLING: Implement the logic defined in [CLEANING RULES] for handling outliers or logically inconsistent data (e.g., end dates before start dates). 5. DEDUPLICATION: Identify and remove duplicate records based on the primary key identified in the schema. 6. LOGGING: Keep a mental tally of how many records were modified, dropped, or flagged for manual review. OUTPUT FORMAT: Provide the final result in two parts: 1. SUMMARY TABLE: A brief table showing (Total Records Processed | Records Cleaned | Records Dropped | Major Issues Found). 2. CLEANED DATASET: The resulting data formatted as a Markdown table or CSV-style block (as per the user's implicit need for the specific dataset size). 3. EXCLUSION LOG: A list of specific rows that were removed or required manual intervention, including the reason why. QUALITY BAR: - Zero tolerance for residual HTML tags or encoding errors (e.g., &). - All numbers must be formatted consistently. - The output must be ready for immediate import into a SQL database or a Pandas DataFrame. - If the [RAW DATA] is too large, provide the cleaning logic in Python code instead of the cleaned data itself.
What variables does the Data Cleaning Workflow (Website) prompt use?
| Variable | What to put | Example |
|---|---|---|
| [RAW DATA] | The raw, unstructured data extracted from web environments that needs cleaning. | [ {"product_name": " \n Fancy Gadget ", "price": "$99.99USD", "url": "gadget.com/item/1", "added_on": "2023-10-25T10:00:00Z"}, {"product_name": "Another Item", "price": "€25.00", "url": "/item/2", "added_on": "15 Nov 2023"} ] |
| [DATA SCHEMA] | A definition of the expected structure, field names, and target data types for the cleaned output. | { "product_name": {"type": "string", "max_length": 100}, "price": {"type": "float", "min_value": 0}, "item_url": {"type": "string", "format": "url"}, "date_added": {"type": "date", "format": "YYYY-MM-DD"}, "id": {"type": "int", "primary_key": true} } |
| [CLEANING RULES] | Specific instructions for handling anomalies, missing values, and logical inconsistencies beyond generic normalization. | { "price": {"missing_value": "default_to_0", "currency_conversion": "USD"}, "product_name": {"whitespace_remove": "all", "html_decode": true}, "date_added": {"invalid_date": "flag_for_review"}, "url": {"base_domain": "example.com"} } |
How do I use the Data Cleaning Workflow (Website) prompt?
- 1Step 1: Define your raw web-scraped data in the [RAW DATA] section.
- 2Step 2: Create a precise JSON schema detailing field names, types, and constraints for [DATA SCHEMA].
- 3Step 3: Specify a detailed set of cleaning rules, including error handling and custom standardizations, in [CLEANING RULES].
- 4Step 4: Submit the prompt to receive a cleaned dataset, summary table, and an exclusion log.
When should you use the Data Cleaning Workflow (Website) prompt?
Preparing Web Scraping Output for Analysis
Use this prompt to cleanse data extracted from websites before importing it into analytical databases or visualization tools, ensuring data integrity.
Automating Data Ingestion Pipelines
Integrate this workflow into automated data ingestion pipelines to consistently process new web-scraped data segments without manual intervention for common issues.
Normalizing E-commerce Product Data
Apply this prompt to standardize product listings, prices, and descriptions scraped from various e-commerce platforms for unified catalog management.
Enriching Internal Datasets with External Web Data
Ensure external web data is compatible and clean when merging with internal datasets, preventing inconsistencies and errors in combined analyses.
Standardizing Research Data from Online Sources
Clean and structure data collected from online research sources to maintain consistency and reliability across diverse information streams for academic or market research.
Migrating Legacy Web Data
Process and clean old or inconsistently formatted web-derived data before migrating it to new systems or databases, ensuring a smooth transition.
What does the Data Cleaning Workflow (Website) prompt output look like?
SUMMARY TABLE: Total Records Processed | Records Cleaned | Records Dropped | Major Issues Found ----------------------|-----------------|-----------------|------------------- 2 | 2 | 0 | None CLEANED DATASET: product_name,price,item_url,date_added,id Fancy Gadget,99.99,https://gadget.com/item/1,2023-10-25,1 Another Item,25.00,https://example.com/item/2,2023-11-15,2 EXCLUSION LOG: No records excluded or required manual intervention.
Which AI model works best with the Data Cleaning Workflow (Website) prompt?
Excellent for detailed understanding of instructions, complex multi-step processing, and generating logical Python code when data is too large.
Strong in structured data tasks, can handle nuanced cleaning rules, and is proficient at producing CSV or table formats accurately.
Good at adhering to strict output formats and following complex, layered instructions, making it reliable for critical data cleaning operations.
What are the pros and cons of the Data Cleaning Workflow (Website) prompt?
Pros
- Comprehensive cleaning process for web-scraped data
- Enforces strict adherence to data schemas and types
- Handles common web-scraping errors effectively
- Includes normalization steps for consistency
- Provides a summary and exclusion log for transparency
- Scales to provide cleaning logic for large datasets
Cons
- Requires detailed definition of schema and cleaning rules
- Initial setup can be verbose with complex data structures
- Might struggle with highly ambiguous or unstructured raw data
- Large datasets may require explicit code generation rather than direct processing
How can you get better results from the Data Cleaning Workflow (Website) prompt?
- Provide clear and exhaustive JSON schemas and cleaning rules for optimal results.
- For very large datasets, explicitly state 'Provide Python code' instead of expecting direct processing.
- Include examples within your [RAW DATA] and [CLEANING RULES] to illustrate edge cases.
- Specify the desired base URL for relative path normalization if not implicitly clear.
- Clearly define primary keys in the schema for accurate deduplication.
- If outputting code, specify preferred Python libraries (e.g., pandas, re).
Frequently asked questions about the Data Cleaning Workflow (Website) prompt
What is the Data Cleaning Workflow (Website) prompt?
This prompt is designed to meticulously clean and normalize raw data extracted from websites, transforming it into a structured, analysis-ready format. It's particularly useful for handling common web scraping inconsistencies.
Who is this prompt for?
It's ideal for data engineers, Python specialists, data scientists, or anyone involved in processing web-scraped data who needs to ensure high data quality and consistency.
What kind of data can I clean with this prompt?
You can clean any data extracted from web environments, such as product listings, articles, market data, or user profiles, as long as you can provide it in a raw format and define a target schema.
How does the prompt handle missing values?
Missing values are handled based on your specifications in [CLEANING RULES], which can include flagging as 'NULL', defaulting to a specific value, or other custom logic.
Can it remove HTML tags and special characters?
Yes, the prompt instructs the AI to identify and remove common scraping errors like HTML tags and unicode characters as part of its data audit and normalization steps.
What if my raw data is very large?
If the [RAW DATA] is too extensive for direct processing, the AI will provide the Python cleaning logic as code, allowing you to execute it on your larger dataset independently.
What is included in the output?
The output consists of a summary table of cleaning statistics, the cleaned dataset itself (as a Markdown table or CSV-style block), and an exclusion log detailing any rows removed or requiring manual review.