The Challenge: An Urgent, Manual Task

My manager posted an urgent request on Slack. She needed help with a simple but very time-consuming admin task: collating data from a large folder of TSV files. The task was for a new workflow for external contractors and involved a lot of manual copy-pasting and renaming.

I saw that the files had a clear naming pattern, and I knew that doing it manually would be slow and could lead to mistakes. So, when I took on the task, I decided to build a simple Python script to automate it instead.

My Solution: A Simple Python Script

Using Python with the Pandas library, I wrote a script that automatically handles the entire job. I actually "vibe-coded" this one with Google AI Studio, guiding it to build the logic I needed to scan the folder, group all the files based on their names ('outcomes' or 'questions'), and then combine the data from each group.

The final output is a single, perfectly organized Excel file, with each group of data on its own neatly named sheet. What could have been a repetitive, manual task became a quick, one-click process that I could finish right away.

The Python Script

import pandas as pd
import os
import collections

def group_and_combine_tsvs(folder_path, output_filename):
    """
    Groups TSV files by a 4-digit prefix and type ('outcomes'/'questions'),
    combines the data for each group, and saves each group to a separate
    sheet in a single Excel file.
    """
    all_tsv_files = [f for f in os.listdir(folder_path) if f.endswith('.tsv')]
    
    grouped_files = collections.defaultdict(list)
    for filename in all_tsv_files:
        prefix = filename[:4]
        if 'outcomes' in filename:
            sheet_name = f"{prefix}-outcomes"
        elif 'questions' in filename:
            sheet_name = f"{prefix}-questions"
        else:
            continue
        
        full_path = os.path.join(folder_path, filename)
        grouped_files[sheet_name].append(full_path)

    with pd.ExcelWriter(output_filename, engine='openpyxl') as writer:
        for sheet_name in sorted(grouped_files.keys()):
            list_of_dfs = [pd.read_csv(f, sep='\t') for f in grouped_files[sheet_name]]
            combined_df = pd.concat(list_of_dfs, ignore_index=True)
            combined_df.to_excel(writer, sheet_name=sheet_name, index=False)
...

Data Collation Python Script

The Challenge: An Urgent, Manual Task

My Solution: A Simple Python Script

The Python Script

Project Overview