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






How to Use Excel's ISNA Function in Pandas

Excel's ISNA function checks if a value is #N/A (not available). In Python, the equivalent concept of missing data is represented by NaN (Not a Number). Knowing how to detect and handle missing data is crucial for data cleaning and analysis.

This page explains how to identify and manage missing data in Python using pandas, making it easier to transition and automate Excel processes in Python.

Detecting missing data in pandas is straightforward and provides more flexibility than Excel. Here are some common implementations to detect and manage missing values using pandas:

To check for missing values in a column (called a Series in pandas), you can use the `.isna()` method. This will return a series of the same shape with True for missing values and False otherwise.

In Excel, you would use `=ISNA(A1)`. In pandas, the process is similar but can be applied to entire columns at once.

The primary function to use in pandas is the `.isna()` method:

missing_values = df['Column'].isna()

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

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

To check for missing values in a Dataframe, you can again use the `.isna()` function, this time, on the entire dataframe. It will return a dataframe of the same shape with True for missing values and False otherwise.

missing_values = df['Column'].isna()

To filter out or drop rows with missing values from a DataFrame, use the `dropna()` method.

df_cleaned = df.dropna(subset=['Column'])

To count the number of missing values in a DataFrame or Series, you can chain the `.isna()` method with `sum()`. This works because `True` is treated as 1 and `False` as 0.

missing_count = df['Column'].isna().sum()

Outside of the Pandas dataframes and series, missing values are often represented in Python by `None`.

In Excel, if you want to check if a value is `None`, you would use `=ISNA(A1)`. Here's how you can do it in pandas:

x = None
if x is None:
    print('x is None')
    print('x is not None')

Handling missing data in pandas is common, but there are pitfalls to avoid. Here are some common mistakes and their solutions.

In pandas, `NaN` and `None` can both represent missing data, but they are distinct. `NaN` is a special floating-point value, while `None` is Python's representation of a null object. This distinction can cause issues in certain operations.

For instance, in Excel, there's a clear distinction between an empty cell and a cell with a value. In pandas, be cautious about the type of missing value you're dealing with.

A common mistake is to use methods that don't accurately count missing values. Using the length of a filtered DataFrame or the wrong method can lead to inaccuracies.

In Excel, you'd count `TRUE` values from `ISNA`. In pandas, chain `.isna()` with `sum()` for accurate counts.

missing_count = df['Column'].isna().sum()

The ISNA function in Excel returns TRUE if the value is #N/A, otherwise it returns FALSE.


ISNA Excel Syntax

ParameterDescriptionData Type
valueThe value you want to check.any


=ISNA(#N/A)Check if #N/A is #N/A.TRUE
=ISNA(100)Check if 100 is #N/A.FALSE
=ISNA(A1)Check if cell A1 is #N/A.TRUE or FALSE
=ISNA(VLOOKUP(100, A1:B10, 2, FALSE))Check if VLOOKUP returns a #N/A error.TRUE or FALSE

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