Chapter 1: Understanding Date Column Challenges

When working with Excel files in Pandas, users often face unexpected difficulties, particularly regarding date columns. Pandas might read dates with excess precision, leading to datetime objects that include microseconds.

The Production Project: Identifying the Problem

In one of my ongoing production projects, I developed an invoice generation system that processes invoices in Excel format, converts them to a format specified by clients, and transmits them via SFTP.

Given that each client has unique formatting needs, our system is designed to be adaptable. For instance, some clients prefer date formats like %Y-%m-%d %H:%M:%S, while others may require just %Y-%m-%d.

While processing Excel files for various clients, we observed that Pandas altered date columns in unexpected ways. For example, a date formatted as 2023-05-25 00:00:00 could end up reading as 2023-05-25 00:00:00.28000.

This issue can be resolved by specifying the desired format, such as %Y-%m-%d %H:%M:%S.%f, as shown below:

from datetime import datetime

def format_dummy_date(date: str, old_format: str, new_format: str) -> str:

# Transform a date from one format to another

return datetime.strptime(date, old_format).strftime(new_format)

It would be more efficient if Pandas consistently read all date values using the same format, which unfortunately was not the case.

The Production Project: Implementing a Solution

To maintain client-agnostic functionality, I had to devise a configurable and scalable solution. This involved establishing a set of rules that the system could interpret, rather than hard-coding specifics.

Given that there may be multiple date columns in an Excel file, and each client may have distinct format preferences, I developed a configuration system that would guide the processing. Each client would have a configuration set up as follows:

{

"date_cols": ["date_col1", "date_col2"],

"date_format": "%Y-%m-%d"

}

Using this configuration, I was able to resolve the formatting issues effectively:

from datetime import datetime

import pandas as pd

def format_dummy_date(

data: pd.DataFrame, date_cols: list[str], new_format: str

) -> pd.DataFrame:

for dt_col in date_cols:

data[dt_col] = pd.to_datetime(data[dt_col], errors="coerce")

# Format the datetime column to the specified format

data[dt_col] = data[dt_col].dt.strftime(new_format)

return data

def read_excel_file(file: bytes | str, config: dict):

# Accepts either a file path or bytes

# config is the external configuration

data = pd.read_excel(file, engine="openpyxl")

# Ensure dates are formatted correctly

data = format_dummy_date(

data, config.get("date_cols"), config.get("date_format")

)

The pivotal part of the solution is encapsulated within the format_dummy_date function:

def format_dummy_date(

data: pd.DataFrame, date_cols: list[str], new_format: str

) -> pd.DataFrame:

for dt_col in date_cols:

data[dt_col] = pd.to_datetime(data[dt_col], errors="coerce")

# Format the datetime column to the desired format

data[dt_col] = data[dt_col].dt.strftime(new_format)

return data

This function processes the DataFrame containing all the data read from the file, receives the relevant date columns, and applies the desired date format. The to_datetime method is utilized to convert a column to a date format, with errors="coerce" ensuring that any invalid dates are set to NaT (Not a Time).

Final Thoughts

After resolving this issue, I felt it was important to share this experience, hoping it might assist others facing similar challenges. Although using Polars for reading Excel files could be another solution, transitioning to a new tool while the system is already operational did not seem prudent at this time.

Have you encountered similar hurdles? It took considerable time and effort to navigate this issue, and I was quite frustrated with Pandas during the process.

Thank you for taking the time to read my article! If you found this information helpful and wish to receive similar content, consider joining our community at In Plain English 🚀.

Understanding Dates in Pandas

In this video, you will learn how to handle dates within Pandas DataFrames effectively, addressing common pitfalls and offering best practices.

Reading Excel Files with Pandas

This tutorial demonstrates the process of reading Excel files using Pandas, including tips for managing date formats and ensuring data integrity.