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






How to Use Excel's SIGN Function in Pandas

In Excel, the SIGN function is used to determine the sign of a number. It returns 1 for positive numbers, -1 for negative numbers, and 0 for zero.

This page explains how to replicate Excel's SIGN functionality in Python using pandas.

In Python's pandas library, determining the sign of values can be accomplished in various ways based on specific needs. Here are some common implementations:

Just like in Excel where you'd use =SIGN(A1) to determine the sign of a value in cell A1, in pandas, you can use the `sign` method.

The following code determines the sign of each value in the 'Column1' and stores the result in a new column 'Sign':

df['Sign'] = df['Column1'].apply(np.sign)

In some analytical scenarios, you might want to create binary indicator variable based on the sign of values.

For example, in Excel, you might use a formula like =IF(SIGN(A1) = 1, 1, 0) to create an indicator for positive values. To create the same bevahior in pandas, you can use the following code:

# If the number in Column1 is negative, 0 or NaN, label it 0.
# Otherwise, label it 1.
df['Is_Positive'] = (df['Column1'].apply(np.sign) == 1).astype(int)

Implementing the SIGN function in pandas has some potential pitfalls. Here's a common mistake and how to avoid it.

When working with datasets, there may be missing values (`NaN`). It's essential to be aware that the sign function in pandas will return NaN for these values, whereas Excel will return 0 for blank cells.

To implement the same behavior in pandas, you can use the `fillna` method to convert NaN values to 0 before determining the sign.

# Convert NaN values to 0 before determining the sign
df['Column1'].fillna(0, inplace=True)
df['Sign'] = df['Column1'].apply(np.sign)

The SIGN function in Excel simply requires one argument, which is the number you want to determine the sign for.


SIGN Excel Syntax

ParameterDescriptionData Type
numberThe number you want to determine the sign for.number


=SIGN(5)Determine the sign of the number 5.1
=SIGN(-10)Determine the sign of the number -10.-1
=SIGN(0)Determine the sign of the number 0.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