Pandas: Combine date and time columns into a single datetime column

Updated: February 21, 2024 By: Guest Contributor Post a comment

Introduction

In the world of data analysis and manipulation, dealing with date and time is inevitable. Often, datasets come with separate columns for dates and times which, for various analytical tasks, need to be combined into a single datetime column. Pandas, a popular Python data analysis and manipulation library, provides several ways to achieve this. In this guide, we will explore multiple methods to combine date and time columns into a single datetime column, starting from basic to more advanced techniques, complete with code examples and their outputs.

Understanding the Basics

Before we dive into the examples, it’s crucial to understand the data types involved. Pandas deals with dates and times using the datetime64 dtype, which is a versatile and powerful tool for datetime operations. Combining date and time columns typically involves converting strings or separate date and time columns into datetime64 dtype.

Example 1: Basic Combination

Let’s start with the most basic scenario where we have two columns in a DataFrame, Date and Time, both of string type.

import pandas as pd

df = pd.DataFrame({
    'Date': ['2023-04-01', '2023-04-02'],
    'Time': ['12:00:00', '13:30:15']
})

df['DateTime'] = pd.to_datetime(df['Date'] + ' ' + df['Time'])

print(df)

The output will show our original DataFrame now with an additional DateTime column combining the date and time:

         Date      Time            DateTime
0  2023-04-01  12:00:00 2023-04-01 12:00:00
1  2023-04-02  13:30:15 2023-04-02 13:30:15

This approach is straightforward and works well for simple concatenation tasks where date and time are well-formatted.

Handling Non-Standard Formats

In real-world data, dates and times may not always come in a standard format. Pandas’ to_datetime method offers flexibility to handle different formats.

Example 2: Custom Formats

If your date or time data does not come in the ISO format (‘YYYY-MM-DD HH:MM:SS’), you can use the format parameter to specify the correct format for combination.

df = pd.DataFrame({
    'Date': ['01/04/2023', '02/04/2023'],
    'Time': ['12-00-00', '13-30-15']
})

df['DateTime'] = pd.to_datetime(df['Date'] + ' ' + df['Time'], format='%d/%m/%Y %H-%M-%S')

print(df)

This method allows more control and can accommodate various date and time formats encountered in datasets.

Using Separated Date and Time Components

Sometimes, date and time are split across multiple columns, for example, year, month, day in separate columns, and hour, minute, second in others. Combining these into a single datetime column requires a different approach.

Example 3: Combining from Component Columns

df = pd.DataFrame({
    'Year': [2023, 2023],
    'Month': [4, 4],
    'Day': [1, 2],
    'Hour': [12, 13],
    'Minute': [0, 30],
    'Second': [0, 15]
})

pd.to_datetime(df[['Year', 'Month', 'Day', 'Hour', 'Minute', 'Second']])

Pandas allows direct conversion of multiple component columns into a datetime object, thus streamlining the process.

Advanced Techniques

For more complex scenarios, such as when dealing with large datasets or requiring optimization, several advanced strategies can improve performance and flexibility.

Example 4: Using apply and Custom Functions

When more control or preprocessing is needed before combining date and time, the apply method along with a custom function can be very useful.

def combine_datetime(row):
    return pd.to_datetime(f"{row['Date']} {row['Time']}")

df['DateTime'] = df.apply(combine_datetime, axis=1)

This method offers flexibility and can be particularly handy when preprocessing steps such as string manipulation or conditional logic are required before combining.

Conclusion

Combining date and time columns into a single datetime column is a common task in data analysis and manipulation. By using Pandas, we explored various methods from basic concatenation to more advanced techniques, accommodating a range of scenarios and data formats. Mastering these techniques will enhance your data pre-processing skills and enable you to handle datetime data more efficiently in your projects.