How to Split Excel Files by Month Using Python Pandas

Introduction

In a Python community a user asked how to split an Excel file into separate files by month based on a date column. The data contains dates from January to August and performance scores.

Implementation

The provided solution uses pandas to read the Excel file, extract the month from the date column, iterate over unique months, and write each subset to a new Excel file.

import pandas as pd

df = pd.read_excel("C:/Users/pdcfi/Desktop/合并表格.xlsx")

df["月份"] = df["日期"].apply(lambda x: x.month)
for month in df["月份"].unique():
    df1 = df[df["月份"] == month]
    df1.to_excel(fr"C:/Users/pdcfighting/Desktop/绩效/" + str(month) + "月份的业绩.xlsx")
    print(f"{month}月份已经拆分完成!")

The script produces separate Excel files for each month, as shown in the result image.

Conclusion

This article demonstrates a practical Python automation technique for splitting Excel data by month, providing clear code and output to help users solve similar office automation tasks.