Excel to Python: SECOND Function - A Complete Guide | Mito
Home Icon






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.

Mito is an open source library that lets you write Excel formulas in Python. Either write the formula directly in Python or use the SECOND formula in the Mito Spreadsheet and generate the equivalent Python code automatically.

Mito's SECOND function works exactly like it does in Excel. That means you don't need worry about managing data types, handling errors, or the edge case differences between Excel and Python formulas.

Install Mito to start using Excel formulas in Python.

# Import the mitosheet Excel functions
from mitosheet.public.v3 import *;

# Use Mito's SECOND function
# Note: No need to convert the Date column to a string because
# Mito's SECOND formula handles that automatically
df['Second'] = SECOND(df['Date'])

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

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

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

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

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

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

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


SECOND Excel Syntax

ParameterDescriptionData Type
serial_numberThe time value from which you want to extract the second.A valid Excel time


=SECOND("5/21/2021 9:30:05 PM")Extracts the second from the given time.5
=SECOND("21-May-2021 6:30:05 AM")Extracts the second from the given time.5
=SECOND("5/21/2021 9:59 PM")Extracts the second from the given time.0
=SECOND("5/21/2021")Extracts the second from the given time.0

Don't re-invent the wheel. Use Excel formulas in Python.

Install Mito

Don't want to re-implement Excel's functionality in Python?

Automate analysis with Mito