How to Use Excel's MODE Function in Pandas

Excel's MODE function finds the most common value in a set of numbers. This guide explains how to replicate Excel's MODE functionality in Python using pandas.

Implementing the Mode function in Pandas#

To mimic the MODE function from Excel in pandas, there are several approaches depending on your specific needs. Here are some common implementations:

Mode value in each row#

In Excel, to mode values in cells A1, B1, and C1, you'd use =MODE(A1, B1, C1).

In pandas, finding the mode of values across columns for a given row can be done similarly. Below is how you'd compute the mode of 'Col1', 'Col2', and 'Col3' for each row:

# Calculate the mode of Col1, Col2, Col3
df['Mode'] = df[['Col1', 'Col2', 'Col3']].mode(axis=1)[0]

Copy!

Entire column mode#

In Excel, to find the mode of an entire column, you'd use =MODE(A:A).

In pandas, you can use the mode method on the desired column to get a similar result:

# Calculate the mode of the entire column
col_median = df['Col1'].mode()[0]

Copy!

Entire dataframe mode#

In Excel, to find the mode of an entire table, you'd use =MODE(A1:D10).

In pandas, you can use the mode method on the entire dataframe to get a similar result:

# Flatten the DataFrame and get the mode
df_values = df.values.flatten().tolist()
mode_value = pd.Series(df_values).mode()[0]

Copy!

Common mistakes when using MODE in Python#

These are common mistakes (and solutions) that you might run into while calculating the mode in Python.

Expecting a Single Value#

In Excel, the MODE function always returns a single value. For example, =MODE(1, 1, 2, 2) returns 1 even though 1 and 2 occur the same number of times.

However, in pandas, the mode method returns a Series object. This is because there can be multiple values that occur the same number of times. For example, if you have a dataframe with the following values: [1, 1, 2, 2], the mode method will return a series with values [1, 2].

If you want to return a single value, you can use the iloc method to get the first value in the series:

# Calculate the mode of the entire column
df['Mode'] = df[['Col1', 'Col2', 'Col3']].mode(axis=1)[0]

Copy!

Ignoring Missing Values#

The `.mode()` function ignores NaN values similar to how Excel's MODE function disregards blank cells. That means that even if NaN is the most common value, it will not be returned by the mode method.

Understanding the Mode Formula in Excel#

The MODE function in Excel takes one or more arguments (ranges of numbers) and returns their mode.

=MODE(number1, [number2], ...)

MODE Excel Syntax

Examples