How to Use Excel's AVERAGE Function in Pandas

Excel's AVERAGE function calculates the mean of a range of values. This guide explains how to replicate Excel's AVERAGE functionality in Python using pandas.

Understanding the Average/Mean Formula in Excel#

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

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

AVERAGE Excel Syntax

Examples

Implementing the Average/Mean function in Pandas#

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

Average value in each row#

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

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

# Calculate the average of Col1, Col2, Col3
df['Row_Average'] = df[['Col1', 'Col2', 'Col3']].mean(axis=1)

Copy!

Entire column average#

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

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

# Calculate the average of the entire column
col_avg = df['Column1'].mean()

Copy!

Entire dataframe average#

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

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

# Calculate the average of the entire dataframe
df.fillna(0).mean().mean()

Copy!

Weighted Average#

Weighted averages are useful when you want to compute an average that gives more importance to some values than others. For example, if you wanted to calculate the average return of your stock portfolio, you'd want to apply more weight to the stocks that you own more of to get an accurate understanding of your overall return.

To calculate a weighted average, multiply each value by a weight and then sum up the products before dividing by the sum of weights.

To calculate a weighted average in Excel, you'd use the formula =SUMPRODUCT(A1:A10, B1:B10) / SUM(B1:B10).

In Pandas, we can use the following code:

# Calculate the weighted average in a dataframe
weighted_avg = (df['values'] * df['weights']).sum() / df['weights'].sum()

Copy!

Common mistakes when using AVERAGE in Python#

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

Averaging Over Strings#

Attempting to calculate the average on columns with string values raises a TypeError. You must convert these strings to numeric types before computing the average.

For example, if you have values with the format $1.99, $2.99, etc. you must convert them to float values before calculating the average

# Convert $ values to float
df['Column'] = df['Column'].str.replace('$', '').astype(float)

# Calculate the average
avg = df['Column'].mean()

Copy!

Ignoring Missing Values#

Not managing `NaN` values correctly can lead to inaccurate averages. By default, pandas skips `NaN` values, similar to how Excel's AVERAGE function disregards blank cells. However, you can also choose to treat `NaN` values as 0 when calculating the average, or return NaN if any of the values are NaN.

# Create a sample dataframe
df = pd.DataFrame({'Column': [1, 2, 3, None]})

# Calculate average with NaN values
df['Column'].mean(skipna=False)        # Resut: NaN

# Calculate average without NaN values
df['Column'].mean(skipna=True)         # Result: 2.0

# Calculate average with NaN values treated as 0
df['Column'].fillna(0).mean()          # Result: 1.5

Copy!