When we work with data, especially in large quantities, it’s often necessary to organize it neatly in spreadsheets or excel sheets. Python is a powerful tool for automating this process.

Just, imagine you have a list of information, like names, ages, and locations, and they are all jumbled together. You want to split this raw data into rows and columns in an Excel sheet for better analysis or presentation. Python can help you do just that!

But How can Python do this?

The answer to this question is – By using libraries like openpyxl, you can write scripts to take your messy raw data and transform it into a structured Excel file. This process involves splitting the raw data into meaningful pieces, like individual names or numbers, and then arranging these pieces into rows and columns in the Excel sheet.

Now, let us go ahead and code in python for the same –

First create a file and save it with .py extension and copy the below code and paste it.

Note: You can take your own raw data or csv

Code:

from openpyxl import Workbook

# Predefined raw data
raw_data = "John,Doe,25,New York\nJane,Smith,30,Los Angeles\nTom,Williams,35,Chicago"

# Split the raw data into lines and then into individual pieces of data
rows = raw_data.split("\n")
data = [row.split(",") for row in rows]

# Create a new Excel workbook
wb = Workbook()
ws = wb.active

# Add headers
ws.append(["First Name", "Last Name", "Age", "City"])

# Add data to the Excel sheet
for row_data in data:
    ws.append(row_data)

# Save the Excel workbook
wb.save("output.xlsx")

Output:

Explanation of the above code:

1. from openpyxl import Workbook: Imports the Workbook class from the openpyxl module, which is used to create Excel files.
2. raw_data = "John,Doe,25,New York\nJane,Smith,30,Los Angeles\nTom,Williams,35,Chicago": Defines a string raw_data containing the predefined raw data. Each line of data is separated by \n.
3. rows = raw_data.split("\n"): Splits the raw_data string into a list of lines, using the newline character \n as the separator.
4. data = [row.split(",") for row in rows]: Splits each line in the rows list into individual pieces of data, using the comma , as the separator. This creates a list of lists, where each inner list represents a row of data.
5. wb = Workbook(): Creates a new Excel workbook.
6. ws = wb.active: Gets the active sheet from the workbook. By default, this is the first sheet.
7. ws.append(["First Name", "Last Name", "Age", "City"]): Adds a row to the Excel sheet with the headers “First Name”, “Last Name”, “Age”, and “City”.
8. for row_data in data: ws.append(row_data): Iterates over each row of data in the data list and appends it to the Excel sheet.
9. wb.save("output.xlsx"): Saves the Excel workbook as a file named “output.xlsx” in the current directory.

More on Python Automation

In conclusion,

Python provides powerful tools like the openpyxl library to automate the process of splitting and arranging raw data into Excel sheets. By following the steps outlined in this tutorial, you can efficiently manage large datasets, ensuring that your data is organized and accessible for further analysis. Automating this process not only saves time but also reduces the risk of manual errors, making it a valuable skill for anyone working with data in Excel.