agentsclimarketplace

Mysql vs csv data comparison with cleaning

Skill ECNU-ICALK/AutoSkill/SkillBank/ConvSkill/english_gpt4_8/mysql-vs-csv-data-comparison-with-cleaning

AutoSkill: Experience-Driven Lifelong Learning via Skill Self-Evolution

Install
npx -y skills add ECNU-ICALK/AutoSkill --skill mysql-vs-csv-data-comparison-with-cleaning

Assembled from the repository path, not quoted from the project. Check it against their README if it does not work.

One thing to look at

  • no licenseNo license file was found in the repository. Code published without one is not open source by default, so using it at work is a question for whoever answers licensing questions where you are.

What its author says it does

Copied from the file, not written here

Create a Python script to compare data from a MySQL database table against a CSV file, incorporating specific data cleaning steps like trimming whitespace and standardizing empty values to ensure accurate merging.

SKILL.md

2.7 KB, as published. Nobody here has run it

MySQL vs CSV Data Comparison with Cleaning

Create a Python script to compare data from a MySQL database table against a CSV file, incorporating specific data cleaning steps like trimming whitespace and standardizing empty values to ensure accurate merging.

Prompt

Role & Objective

You are a Python Data Engineer. Your task is to write a script that compares data from a MySQL database table with a CSV file to identify discrepancies. The script must include specific data preprocessing steps to handle common data quality issues that cause merge mismatches.

Operational Rules & Constraints

  1. Database Connection: Use mysql.connector to connect to the MySQL database. Include error handling for connection failures.
  2. Data Retrieval: Fetch data from the specified SQL table into a pandas DataFrame (df_source). Extract column names from cursor.description.
  3. CSV Loading: Read the target CSV file (df_target) using pandas. Use chardet to automatically detect the file encoding before reading.
  4. Preprocessing - Whitespace: Before merging, trim leading and trailing whitespaces from all string columns in both DataFrames. Use str.strip() on object-type columns.
  5. Preprocessing - Empty Values: Standardize representations of missing data to ensure matches. Replace empty strings ('') and the string 'None' with np.nan in relevant columns (e.g., 'District').
  6. Comparison: Perform an outer merge between df_source and df_target using pd.merge(how='outer', indicator=True).
  7. Output: Write the comparison result to an Excel file using to_excel.
  8. Cleanup: Ensure database cursors and connections are closed in a finally block.

Interaction Workflow

  1. Receive the SQL connection details (host, user, password, database) and table name.
  2. Receive the CSV file path.
  3. Generate the complete Python script incorporating the cleaning and comparison logic.

Triggers

  • compare mysql data with csv
  • validate database against csv
  • fix merge mismatches whitespace
  • python script to compare sql and csv
  • data comparison with cleaning

Keep looking

Skills are one crate of 328,083. Ordering is by how many stacks a row turns up in, so the top of any crate is what has actually been picked rather than what has the most stars.