How to Use Excel's MAX Function in Pandas

Related Functions

MIN AVERAGE ABS

Excel's MAX function returns the largest value from the numbers provided. This page shows how to implement Excel's MAX function in Python using pandas.

Use Mito's MAX function

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

Mito's MAX 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 MAX function
df['Max_Value'] = MAX(df['A'], df['B'], 0)

Copy!

Implementing the Maximum Value function in Pandas#

There are a few common ways to use the MAX function. Below are copy-ready Python implementations for some of the most common use cases.

Finding the Maximum Value between two numbers#

To find the maximum between two numbers in Excel, you might use a formula like =MAX(A1, B1).

In pandas, to achieve the same, you'd typically use Python's built-in `max` function:

max_value = max(1, 2)

Copy!

Finding the Maximum Value Across Rows in dataframe#

To find the maximum values in each row of your pandas dataframe, you can use the `max` method along with specifying which columns to include in the row-wise calculation:

# Find the maximum value in column A, B, or C for each row
df['Max_Value'] = df[['A', 'B', 'C']].max(axis=1)

Copy!

Finding the Maximum Value in a Series#

In Excel, if you want to find the maximum value in a column, you'd use the formula =MAX(A:A). The formula returns the highest value in the range A:A.

Similarly in Pandas, you can use the `max` method on a Series to find the maximum value in a column:

# Find the maximum value in column A
max_value = df['A'].max()

Copy!

Finding the Maximum in a dataframe#

If you want to determine the highest value in an entire Excel sheet, you'd use the MAX function on the entire range. For example, if you had data in column A through G, you'd use the formula =MAX(A:G).

In pandas, to find the maximum value in an entire DataFrame, use the `max` function first to find the maximum value in each column, and again to find the maximum value among those results:

# Find the maximum value in the entire dataframe
max_value = df.max().max()

Copy!

Common mistakes when using MAX in Python#

While using the MAX function in pandas, here are some common mistakes to watch out for, and how to resolve them.

Mismatched Data Types#

Attempting to find the maximum in a series with mixed data types can lead to a TypeError. It's essential to ensure data consistency before performing such operations.

# Convert to numeric and then find max
df['Column1'] = pd.to_numeric(df['Column1'], errors='coerce')
max_value = df['Column1'].max()

Copy!

Ignoring NaN Values#

Just like Excel, by default, pandas excludes NaN values (aka missing values) when calculating the maximum. However, if the entire column or row contains only NaNs, the result will be NaN.

Understanding the Maximum Value Formula in Excel#

The MAX function in Excel takes one or more arguments (ranges of numbers or individual numbers) and returns the largest value among them.

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

MAX Excel Syntax

Parameter	Description	Data Type
number1	The first number you want to compare.	number
number2	(Optional) Additional numbers you want to compare.	number
...	(Optional) Add up to 255 numbers you want to compare.	number