Restructuring Data in MS Excel with Python Automation. Automation in data restructuring can greatly improve efficiency, especially when dealing with data stored in less structured formats. This article explains how to use Python and the pandas library to automate the process of restructuring data in Microsoft Excel.

We’ll walk through a sample project where the original data is in a single column, and then split it into multiple columns for better readability and usability.

Using pandas, a powerful data manipulation library, we’ll split the original column, clean the data, and save it in a structured format — all within a single Excel sheet. Let’s dive in!

Why Automate Data Restructuring in Excel?

Data restructuring involves changing the organization of data to make it more accessible and ready for analysis. Common tasks include:

1. Extracting information from unstructured data formats.
2. Standardizing and cleaning data for consistency.
3. Splitting or merging columns for easier access and analysis.

These operations, if performed manually, can be time-consuming and prone to errors. Automating with Python helps us quickly transform our data with just a few lines of code.

Requirements

To follow this guide, you’ll need the following Python packages:

• pandas: For data manipulation.
• openpyxl: To work with Excel files.

To install them, run:

pip install pandas openpyxl

Step-by-Step Guide to Restructuring Data in Excel

Let’s walk through the code step-by-step.

import pandas as pd

# Create the original DataFrame with a single "Data" column
data = {
    "Data": [
        "Person 1: 250", "Person 2: 400", "Person 3: 350", "Person 4: 275",
        "Person 5: 500", "Person 6: 320", "Person 7: 450", "Person 8: 380",
        "Person 9: 290", "Person 10: 410"
    ]
}

# Create DataFrame and save it as the starting column in Excel
df = pd.DataFrame(data)

Explanation:

• We start by creating a DataFrame with a single column, Data, which contains combined information in the format: "Person X: Sales Value".
• This data is stored in a dictionary format, then converted to a DataFrame using pd.DataFrame(data). This will allow us to manipulate the data more easily.

Step 2: Splitting the Data Column

Next, we’ll separate the name and sales values into two new columns.

# Split "Data" column into "Name" and "Sales" columns
df[['Name', 'Sales']] = df['Data'].str.split(': ', expand=True)
df['Sales'] = df['Sales'].astype(int)  # Convert "Sales" to integer

Explanation:

• The str.split(': ', expand=True) function splits the Data column on the ': ' delimiter and creates two new columns, Name and Sales.
• The expand=True argument ensures that the split results in two new columns in the DataFrame, rather than a single list.
• We then convert the Sales column to an integer data type using astype(int), ensuring that we can perform numerical operations on it later.

Step 3: Saving the Restructured Data in Excel

Finally, we’ll save the updated DataFrame to a new Excel file. This file will contain both the original Data column and the newly created Name and Sales columns in a single sheet.

# Save both the original "Data" column and new "Name" and "Sales" columns in a single Excel sheet
df.to_excel("combined_data.xlsx", index=False)

print("Data has been restructured and saved to 'combined_data.xlsx' in a single sheet.")

Explanation:

• The to_excel() function saves the DataFrame to an Excel file. We specify index=False to exclude the row index from the output file.
• After executing this code, you’ll have a single Excel file, combined_data.xlsx, with three columns: Data, Name, and Sales.

Final Output: The Excel File Structure

In the combined_data.xlsx file, the data will appear as follows:

Data	Name	Sales
Person 1: 250	Person 1	250
Person 2: 400	Person 2	400
Person 3: 350	Person 3	350
Person 4: 275	Person 4	275
Person 5: 500	Person 5	500
Person 6: 320	Person 6	320
Person 7: 450	Person 7	450
Person 8: 380	Person 8	380
Person 9: 290	Person 9	290
Person 10: 410	Person 10	410

Full source code:

import pandas as pd

# Create the original DataFrame with a single "Data" column
data = {
    "Data": [
        "Person 1: 250", "Person 2: 400", "Person 3: 350", "Person 4: 275",
        "Person 5: 500", "Person 6: 320", "Person 7: 450", "Person 8: 380",
        "Person 9: 290", "Person 10: 410"
    ]
}

# Create DataFrame and save it as the starting column in Excel
df = pd.DataFrame(data)

# Split "Data" column into "Name" and "Sales" columns
df[['Name', 'Sales']] = df['Data'].str.split(': ', expand=True)
df['Sales'] = df['Sales'].astype(int)  # Convert "Sales" to integer

# Save both the original "Data" column and new "Name" and "Sales" columns in a single Excel sheet
df.to_excel("combined_data.xlsx", index=False)

print("Data has been restructured and saved to 'combined_data.xlsx' in a single sheet.")

Explanation of the above code:

Import the pandas library:

• The pandas library is imported to enable data manipulation and Excel handling in Python.

Create a dictionary with sample data:

• A dictionary named data is created, containing a single key, "Data".
• The value associated with this key is a list of strings, where each entry includes a person’s name and their corresponding sales value in the format "Person X: Value".

Convert the dictionary into a DataFrame:

• The dictionary is then converted into a pandas DataFrame named df.
• Each entry in the list becomes a row in a single-column DataFrame labeled "Data", making it easy to manipulate further.

Split the "Data" column into two separate columns:

• The "Data" column is split into two new columns, "Name" and "Sales", based on the delimiter ": ".
• Using the expand=True argument, the split operation results in two new columns, rather than a list within a single column.

Convert the "Sales" column to an integer type:

• The "Sales" column, created from the split operation, initially contains string values.
• This step converts these values into integers, making the column suitable for numerical operations.

Save the DataFrame to an Excel file:

• The updated DataFrame is saved as an Excel file, combined_data.xlsx.
• By setting index=False, only the actual data columns are saved, avoiding the addition of row indices, and ensuring a clean, structured Excel output.

Display a confirmation message:

• A message is printed to confirm that the data restructuring and saving process has been completed successfully.

Output:

Restructuring data in MS Excel using Python automation is a highly efficient and powerful technique that brings a range of benefits for both data professionals and everyday users. As we’ve demonstrated, Python, particularly with the pandas library, allows us to automate data manipulation tasks that would otherwise be time-consuming and error-prone when done manually. This approach not only improves data accuracy and organization but also enables greater productivity by reducing repetitive tasks to a few lines of code.

Throughout the article, we explored a practical example where data initially stored in a single column was split into multiple columns, achieving a cleaner, more organized structure. With just a few functions from pandas, such as splitting columns, modifying data types, and exporting to Excel, we were able to transform unstructured data into a format that’s ready for analysis and reporting.

The approach demonstrated here is flexible and can be adapted to handle a wide variety of data restructuring needs, whether you are working with large datasets, multiple columns, or more complex transformations. Beyond restructuring, pandas offers extensive functionality for data cleaning, filtering, merging, and visualization, making it a cornerstone of Python’s data science ecosystem.

Using Python for data restructuring also fosters scalability and reproducibility. Once the automation code is in place, it can be reused for similar datasets, making it ideal for projects that require regular updates or handling of new incoming data. This automation framework is especially useful in fields like finance, marketing, and business intelligence, where managing and analyzing large volumes of data is essential.

In summary, restructuring data in Excel with Python empowers users to handle complex datasets with ease and precision. Mastering this skill can lead to more insightful analyses, higher data integrity, and significant time savings. As data-driven decision-making becomes more prevalent across industries, the ability to efficiently structure and prepare data using tools like Python will be invaluable, laying the foundation for robust and insightful analytics workflows.