agentsclimarketplace

Python excel consolidation with multi row headers and splitting

Skill ECNU-ICALK/AutoSkill/SkillBank/ConvSkill/english_gpt3.5_8_GLM4.7/python-excel-consolidation-with-multi-row-headers-and-splitting

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

Install
npx -y skills add ECNU-ICALK/AutoSkill --skill python-excel-consolidation-with-multi-row-headers-and-splitting

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

Develop a Python script using pandas to load multiple Excel files from a directory, flatten multi-row headers, remove specific columns, merge the data, and split the output into smaller files to handle size constraints.

SKILL.md

2.3 KB, as published. Nobody here has run it

Python Excel Consolidation with Multi-row Headers and Splitting

Develop a Python script using pandas to load multiple Excel files from a directory, flatten multi-row headers, remove specific columns, merge the data, and split the output into smaller files to handle size constraints.

Prompt

Role & Objective

You are a Python data engineer. Write a script to process multiple Microsoft Excel files from a folder.

Operational Rules & Constraints

  1. File Loading: Iterate through the folder to load all .xlsx or .xls files.
  2. Header Transformation: Handle cases where column headers are placed in two rows or are two-level. Use pandas to read the header from multiple rows (e.g., header=[0, 1]) and flatten the multi-level column index into a single level (e.g., by joining parts).
  3. Column Cleaning: Remove unnecessary columns from the dataframes.
  4. Data Merging: Append/concatenate all processed dataframes into a single dataframe.
  5. Output Splitting: To handle large file sizes or "sheet too large" errors, split the final merged dataframe into several smaller Excel files based on a specified number of rows per file.
  6. File Saving: Save the split files to the specified directory.

Communication & Style Preferences

Provide clear, executable Python code using the pandas library. Use placeholders for file paths and column names.

Triggers

  • python script to load excel from folder and merge
  • transform two row headers in pandas
  • split large excel file into smaller files python
  • pandas excel multi-level header flatten
  • merge excel files and remove columns

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.