Pandas Date Difference Using NumPy timedelta64()

What Is numpy.timedelta64()? 🔝

When one Pandas datetime column is subtracted from another, the result is a Timedelta value.

We can divide that duration by a NumPy timedelta64 unit to express the difference as a numeric value in days, weeks, hours, minutes, seconds, milliseconds, microseconds or nanoseconds.

For example:

difference / np.timedelta64(1, 'D')

returns the duration as a number of days.

Create the Sample DataFrame 🔝

First, create a DataFrame containing start and end dates.

import pandas as pd
import numpy as np

my_dict={
    'NAME':[
        'Ravi',
        'Raju',
        'Alex'
    ],

    'dt_start':[
        '1/1/2020',
        '2/1/2020',
        '5/1/2020'
    ],

    'dt_end':[
        '6/15/2022',
        '7/22/2022',
        '11/15/2023'
    ]
}

my_data=pd.DataFrame(
    data=my_dict
)

my_data['dt_start']=pd.to_datetime(
    my_data['dt_start']
)

my_data['dt_end']=pd.to_datetime(
    my_data['dt_end']
)

print(my_data)

   NAME   dt_start     dt_end
0  Ravi 2020-01-01 2022-06-15
1  Raju 2020-02-01 2022-07-22
2  Alex 2020-05-01 2023-11-15

The dt_start and dt_end columns are converted to Pandas datetime values using to_datetime().

Calculate the Difference Between Two Date Columns 🔝

Subtract the starting date from the ending date.

my_data['difference']=(
    my_data['dt_end']
    -
    my_data['dt_start']
)

print(my_data)

   NAME   dt_start     dt_end difference
0  Ravi 2020-01-01 2022-06-15   896 days
1  Raju 2020-02-01 2022-07-22   902 days
2  Alex 2020-05-01 2023-11-15  1293 days

The difference column contains Pandas Timedelta values rather than ordinary integers.

Difference in Days 🔝

Divide the Timedelta by one NumPy day:

my_data['diff_days']=(
    my_data['difference']
    /
    np.timedelta64(
        1,
        'D'
    )
)

print(
    my_data[
        [
            'NAME',
            'diff_days'
        ]
    ]
)

   NAME  diff_days
0  Ravi      896.0
1  Raju      902.0
2  Alex     1293.0

The result is numeric, so normal numerical comparisons can now be used.

Filter Rows by Date Difference 🔝

For example, select records where the date difference is greater than 900 days.

filtered=my_data[
    my_data['diff_days'] > 900
]

print(
    filtered[
        [
            'NAME',
            'diff_days'
        ]
    ]
)

   NAME  diff_days
1  Raju      902.0
2  Alex     1293.0

Difference in Weeks 🔝

Use 'W' to convert the duration to weeks.

my_data['diff_weeks']=(
    my_data['difference']
    /
    np.timedelta64(
        1,
        'W'
    )
)

print(
    my_data[
        [
            'NAME',
            'diff_weeks'
        ]
    ]
)

   NAME  diff_weeks
0  Ravi  128.000000
1  Raju  128.857143
2  Alex  184.714286

Whole Weeks

If only completed whole weeks are required, the result can be converted to integer.

my_data['whole_weeks']=(
    my_data['diff_weeks']
    .astype(int)
)

print(
    my_data[
        [
            'NAME',
            'whole_weeks'
        ]
    ]
)

   NAME  whole_weeks
0  Ravi          128
1  Raju          128
2  Alex          184

Converting a positive floating-point value to integer removes the fractional part.

Difference in Hours 🔝

Use lowercase 'h' for hours.

my_data['diff_hours']=(
    my_data['difference']
    /
    np.timedelta64(
        1,
        'h'
    )
)

print(
    my_data[
        [
            'NAME',
            'diff_hours'
        ]
    ]
)

   NAME  diff_hours
0  Ravi     21504.0
1  Raju     21648.0
2  Alex     31032.0

Difference in Minutes 🔝

Use 'm' for minutes.

my_data['diff_minutes']=(
    my_data['difference']
    /
    np.timedelta64(
        1,
        'm'
    )
)

print(
    my_data[
        [
            'NAME',
            'diff_minutes'
        ]
    ]
)

   NAME  diff_minutes
0  Ravi     1290240.0
1  Raju     1298880.0
2  Alex     1861920.0

Difference in Seconds 🔝

Use 's' for seconds.

my_data['diff_seconds']=(
    my_data['difference']
    /
    np.timedelta64(
        1,
        's'
    )
)

print(
    my_data[
        [
            'NAME',
            'diff_seconds'
        ]
    ]
)

   NAME  diff_seconds
0  Ravi    77414400.0
1  Raju    77932800.0
2  Alex   111715200.0

Milliseconds, Microseconds and Nanoseconds 🔝

Smaller fixed time units can also be used.

Milliseconds

my_data['diff_ms']=(
    my_data['difference']
    /
    np.timedelta64(
        1,
        'ms'
    )
)

Microseconds

my_data['diff_us']=(
    my_data['difference']
    /
    np.timedelta64(
        1,
        'us'
    )
)

Nanoseconds

my_data['diff_ns']=(
    my_data['difference']
    /
    np.timedelta64(
        1,
        'ns'
    )
)

What About Months and Years? 🔝

Months and years are different from units such as days or hours because they do not have a fixed duration.

A month can contain 28, 29, 30 or 31 days, and a year can contain 365 or 366 days.

For this reason, current Pandas Timedelta arithmetic does not support dividing a Timedelta by:

np.timedelta64(1, 'M')
np.timedelta64(1, 'Y')

These are ambiguous calendar units rather than fixed durations.

Calendar-Based Month and Year Calculations

For adding or subtracting calendar components in Pandas, use DateOffset().

For calendar differences involving years, months and days, see the relativedelta() tutorial.

Using Pandas .dt.days 🔝

If you only need the number of whole days from a Pandas Timedelta Series, Pandas provides a simpler approach through .dt.days.

my_data['days']=(
    my_data['difference']
    .dt.days
)

print(
    my_data[
        [
            'NAME',
            'days'
        ]
    ]
)

   NAME  days
0  Ravi   896
1  Raju   902
2  Alex  1293

This is convenient when only whole days are required.

Using NumPy timedelta64 is useful when the same Timedelta needs to be expressed in several fixed units such as hours, minutes or seconds.

Supported Units for Pandas Timedelta Division 🔝

Units Not Suitable Here

For Pandas Timedelta division, avoid treating the following as fixed durations:

'M'  # calendar month
'Y'  # calendar year

For calendar-based months and years, use a calendar-aware approach instead.

Summary 🔝

• Subtracting two Pandas datetime columns creates a Timedelta Series.
• Divide a Timedelta by np.timedelta64(1, 'D') to get numeric days.
• Use 'W' for weeks.
• Use 'h' for hours.
• Use 'm' for minutes.
• Use 's' for seconds.
• Milliseconds, microseconds and nanoseconds are available through 'ms', 'us' and 'ns'.
• Numeric duration columns can be used directly in Pandas filtering conditions.
• .dt.days is a simple way to extract whole days from a Pandas Timedelta Series.
• Months and years are not fixed-duration units.
• Do not divide a Pandas Timedelta by np.timedelta64(1, 'M') or np.timedelta64(1, 'Y') for calendar month or year calculations.
• Use DateOffset() for adding or subtracting calendar date components.
• Use relativedelta() when a calendar difference in years, months and days is required.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: