Merging Files by Keyword with Python and Pandas

Introduction

A user in a Python community asked how to locate files whose names contain at least two identical keywords, extract the numeric values from the second column of each file, keep the first column unchanged, and then combine those numbers side‑by‑side in a new Excel sheet.

Implementation

The solution involves iterating over file names, extracting the common keyword segment (e.g., the characters at positions 2‑4), grouping files by that keyword, reading each file into a pandas DataFrame, and then concatenating the DataFrames horizontally before exporting to Excel.

for file_name in file_names:
    if file_name[2:4] = ?

Because the keyword length may vary, it is recommended to store the mapping in a dictionary such as {"file_name[2:4]": [df1, df2]}, ensuring each group’s DataFrames are appended to a list and later concatenated with pd.concat. The final step writes the combined DataFrame to an .xlsx file using to_excel.

Conclusion

The provided approach successfully merges files that share the same keyword, preserving the first column and aligning the extracted numeric values across columns, demonstrating a practical Python automation workflow for repetitive data‑processing tasks.