Build a Simple Python Data Transformation Tool with a Web Interface (Step-by-Step Guide)

When working with data across different tools, it’s often necessary to transform the data to fit the format required by the new tool. In this blog, we’ll walk through building a simple Python script with a web interface that allows you to upload sales data in Excel format, transform it, and download the modified file. This solution will be especially helpful for cases where you need to manipulate data between systems.

Prerequisites

Before we start, make sure you have the following:

Basic knowledge of Python
Python installed on your machine
Flask, pandas, and openpyxl libraries

To install the required libraries, run the following command in your terminal:

pip install flask pandas openpyxl

Step 1: Setting Up the Flask Application

The first step is to create the Flask application, which will serve as our local web interface. Let’s create a new Python file called app.py and set up the basic structure of the Flask app.

`app.py`

from flask import Flask, render_template, request, send_file
import pandas as pd
import os
app = Flask(__name__)
# Ensure that uploads directory exists
if not os.path.exists("uploads"):
    os.makedirs("uploads")@app.route('/')
def index():
    return render_template('index.html')@app.route('/upload', methods=['POST'])
def upload_file():
    if 'file' not in request.files:
        return 'No file part'
    file = request.files['file']
    if file.filename == '':
        return 'No selected file'
    if file:
        # Save the uploaded file
        filepath = os.path.join('uploads', file.filename)
        file.save(filepath) # Process the file
        processed_file = process_file(filepath)return send_file(processed_file, as_attachment=True)def process_file(file_path):
    # Load the Excel file
    df = pd.read_excel(file_path)# Here, implement the data transformation logic, for example:
    # - Filtering rows
    # - Renaming columns
    # - Formatting dates, etc.
    df['Date'] = pd.to_datetime(df['Date'], format='%Y-%m-%d')
    df.rename(columns={'OldColumn': 'NewColumn'}, inplace=True) # Save the processed file
    output_file = file_path.replace('.xlsx', '_processed.xlsx')
    df.to_excel(output_file, index=False)
return output_fileif __name__ == '__main__':
    app.run(debug=True)

Explanation:

Flask App: This creates a basic Flask app with two routes:

/ serves the upload page (we’ll create this in HTML next).
/upload handles the file upload, performs the data transformation, and then serves the modified file for download.

File Processing: The process_file() function reads the uploaded Excel file using pandas, performs the necessary data transformations (such as renaming columns and formatting dates), and saves the processed file locally.

Step 2: Creating the Upload Form

Now, let’s create the HTML form to upload the Excel file. Create a folder named templates, and inside it, create a file called index.html. This will allow users to upload their Excel file via a simple form.

`index.html`

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Data Transformation Tool</title>
</head>
<body>
    <h1>Upload your Sales Data</h1>
    <form action="/upload" method="post" enctype="multipart/form-data">
        <input type="file" name="file" accept=".xlsx" required>
        <button type="submit">Upload and Transform</button>
    </form>
</body>
</html>

Explanation:

This form allows users to select an Excel file to upload. When the form is submitted, it sends the file to the /upload route in our Flask app for processing.

Step 3: Running the Flask App

Now that we have the Flask app and the HTML form set up, let’s run the application.

In your terminal, run the following command:

python app.py

Open your browser and go to http://127.0.0.1:5000/ to access the web interface.
Use the form to upload an Excel file. Once the file is processed, the transformed file will be available for download.

Step 4: Customizing Data Transformation Logic

The process_file() function is where you’ll define how the data should be transformed. Here are a few common transformations you might need:

Renaming columns:

df.rename(columns={'OldColumn': 'NewColumn'}, inplace=True)

Filtering rows:

df = df[df['column_name'] == 'some_value']

Formatting dates:

df['Date'] = pd.to_datetime(df['Date'], format='%Y-%m-%d')

Handling missing data:

df.fillna(0, inplace=True)  # Fill missing values with 0

You can add these transformations as needed to suit your data manipulation requirements.

Step 5: Saving the Processed Data

Once the file is transformed, it’s saved in the same directory as the original file with a new name (e.g., sales_data_processed.xlsx). This ensures that both the original and processed files are available locally for reference.

Conclusion

With just a few lines of code, you can create a Python script with a simple web interface that allows users to upload, transform, and download data. This approach can be adapted to fit a variety of use cases, from sales data to financial records or any other structured data that requires processing before being uploaded into a new system.

🔥 Found this blog post helpful? 🔥

If you enjoyed this article and found it valuable, please show your support by clapping 👏 and subscribing to my blog for more in-depth insights on web development and Next.js!

Subscribe here: click me

Your encouragement helps me continue creating high-quality content that can assist you on your development journey. 🚀

Build a Simple Python Data Transformation Tool with a Web Interface (Step-by-Step Guide)

Prerequisites

Step 1: Setting Up the Flask Application

`app.py`

Explanation:

Step 2: Creating the Upload Form

`index.html`

Explanation:

Step 3: Running the Flask App

Step 4: Customizing Data Transformation Logic

Step 5: Saving the Processed Data

Conclusion

Written by Sagar Sangwan

SUBSCRIBE to Newsletter