How to Use Excel's HOUR Function in Pandas

Excel's HOUR function extracts the hour as a number from a time value. It's especially useful when working with large datasets where you need to analyze data at an hourly granularity.

This page explains how to implement Excel's HOUR function in Python using pandas.

Understanding the Hour Extraction Formula in Excel#

The HOUR function in Excel returns the hour of a time value, ranging from 0 (12:00 AM) to 23 (11:00 PM).

=HOUR(serial_number)

HOUR Excel Syntax

Examples

Implementing the Hour Extraction function in Pandas#

Recreating Excel's HOUR function behavior in Python requires a combination of pandas operations. Here are some common implementations:

Extracting Hour from Datetime#

In Excel, if you have a datetime value, you might use the HOUR function directly to get the hour. Similarly, in pandas, you use the `.dt` accessor followed by the `hour` attribute.

For example, in Excel you might use =HOUR(A2). In pandas:

df['Hour'] = df['Datetime_Column'].dt.hour

Copy!

Converting string to datetime and then extracting hour#

Often, Pandas will infer the data type of your column as string, even if the data to you looks like a date, ie: 1/2/23. In these cases, you need to convert the string to datetime before extracting the hour.

To do this in pandas, first use `pd.to_datetime` to convert the column to a datetime column, and then extract the hour:

# Convert the string to datetime
df['Datetime_Column'] = pd.to_datetime(df['String_Column'])

# Extract the hour from the datetime column
df['Hour'] = df['Datetime_Column'].dt.hour

Copy!

Grouping Data by Hour#

There are situations where you want to aggregate data based on hours. In Excel, you might use a pivot table after extracting the hour. Similarly, in pandas, after extracting the hour, you can use the `groupby` method

For example, if you have a column called 'Date' and a column called 'Website Traffic', you might want to group the data by hour and sum the traffic for each hour.

df['Hour'] = df['Date'].dt.hour
grouped_data = df.groupby('Hour').agg({'Website Traffic': 'sum'}).reset_index()

Copy!

Common mistakes when using HOUR in Python#

While implementing the HOUR function equivalent in pandas, a few common pitfalls might occur. Here's how to navigate them.

Incorrect datatypes#

The `.dt` accessor is exclusive to pandas Series with datetime64 data types. Using it on non-datetime columns will raise an AttributeError.

For example, if you have a column called 'Date', but it actually has an object data type, you'll need to convert it to datetime before using the `.dt` accessor. You can check the data type of a column using `df.dtypes`.

# Ensure the column is of datetime dtype
df['Datetime_Column'] = pd.to_datetime(df['Datetime_Column'])
df['Hour'] = df['Datetime_Column'].dt.hour

Copy!

Ignoring Timezone Information#

If your datetime data contains timezone information, directly extracting hours without considering the timezone can lead to incorrect results. Before operating with data, you might want to convert it to a specific timezone.

Note that you can only convert to a specific timezone if your datetime data has timezone information. If it doesn't, you'll need to add timezone information first.

# First, localize the timestamps to a specific timezone (e.g., 'UTC')
df['Date'] = df['Date'].dt.tz_localize('UTC')

# Now, convert the timestamps to the desired timezone
df['Date'] = df['Date'].dt.tz_convert('US/Eastern')

Copy!

Forgetting to Handle Null Values#

If your dataset has missing or NaT (Not-a-Timestamp) values in the datetime column, trying to extract hours from them will result in NaN (Not a Number) values. Make sure to handle or filter them out as necessary.

# Drop rows with NaT values before extracting hour
df.dropna(subset=['Datetime_Column'], inplace=True)
df['Hour'] = df['Datetime_Column'].dt.hour

Copy!