How to Use Excel's SECOND Function in Pandas

Excel's SECOND function extracts the second from a time value.

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

Understanding the Second Extraction Formula in Excel#

The SECOND function in Excel returns the second of a time value, ranging from 0 to 59.

=SECOND(serial_number)

SECOND Excel Syntax

Examples

Implementing the Second Extraction function in Pandas#

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

Extracting Second from Datetime#

In Excel, if you have a datetime value, you can use the SECOND function to return the second component. Similarly, in pandas, you can use the `.dt` accessor followed by the `second` attribute.

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

df['Second'] = df['Datetime_Column'].dt.second

Copy!

Converting string to datetime and then extracting the second#

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 12:30:05. In these cases, you need to convert the string to datetime before extracting the second.

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

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

# Extract the second from the datetime column
df['Second'] = df['Datetime_Column'].dt.second

Copy!

Common mistakes when using SECOND in Python#

While implementing the SECOND 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['Second'] = df['Datetime_Column'].dt.second

Copy!

Forgetting to Handle Null Values#

If your dataset has missing or NaT (Not-a-Timestamp) values in the datetime column, trying to extract the second 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 second
df.dropna(subset=['Datetime_Column'], inplace=True)
df['Second'] = df['Datetime_Column'].dt.second

Copy!