How to Use Excel's COUNTIF Function in Pandas

Excel's COUNTIF function is used to count the number of cells that meet a single condition within a range. It's a fundamental function for basic data analysis tasks.

This page provides examples on using Excem's COUNTIF's functionality in Python using pandas.

Implementing the Count with Condition function in Pandas#

To replicate the COUNTIF functionality in Python with pandas, you can use conditional expressions combined with the `sum` method to count the occurrences that match your specified conditions.

Counting Based on a Single Condition#

In pandas, you can count the number of times a certain condition is met within a column using a simple comparison and the `sum` method.

For instance, the Excel formula =COUNTIF(A:A, ">5") is equivalent to the following pandas code.

This code snippet creates a boolean series where each item is True if the condition is met, and False otherwise. The sum method then counts the number of True values.

# Count the cells greater than 5
count = (df['A'] > 5).sum()

Copy!

Counting the number of times each value appears in a column#

Let's say that you have the columns 'Category', 'Price', and 'Food'. You want to count how many times each category appears

In Excel, you might create a list of unique categories in column D and then use the formula in column E: =COUNTIF(A:A, D1), dragging the formula down to the last category.

In pandas, you can achieve the same result using a pivot table

# Create same dataframe
df1 = pd.DataFrame({
    'Category': ['Fruit', 'Fruit', 'Vegetable', 'Vegetable', 'Vegetable'],
    'Price': ['Inexpensive', 'Expensive', 'Inexpensive', 'Inexpensive', 'Expensive'],
    'Food': ['Apple', 'Orange', 'Spinach', 'Onion', 'Kale'],
})

# Create a new table that has two columns: Category and Category Count
df2 = df1.pivot_table(index=['Category'], aggfunc={'Category': 'count'})

# Rename the Category column to Category Count
df2 = df2.rename(columns={'Category': 'Category Count'})

# Reset the index
df2 = df2.reset_index()

Copy!

Counting the number of times each values appears, with a condition applied#

Let's say again that you have the columns 'Category', Price', and 'Food'. This time, you want to count how many times each category appears, but only if the price is 'Inexpensive'.

In Excel, you would write the formula =COUNTIFS(A:A, D1, B:B, "Inexpensive") in column E, dragging the formula down to the last category.

In pandas, you can achieve the same result using a pivot table with a filter applied.

# Create same dataframe
df1 = pd.DataFrame({
    'Category': ['Fruit', 'Fruit', 'Vegetable', 'Vegetable', 'Vegetable'],
    'Price': ['Inexpensive', 'Expensive', 'Inexpensive', 'Inexpensive', 'Expensive'],
    'Food': ['Apple', 'Orange', 'Spinach', 'Onion', 'Kale'],
})

# Create a copy of the data first so we don't modify the original
temp_df = df1.copy()

# Filter the data to only include rows where the price is 'Inexpensive'
temp_df = temp_df[temp_df['Price'] == 'Inexpensive']

# Create a new table that has two columns: Category and Category Count
df2 = temp_df.pivot_table(index=['Category'], aggfunc={'Category': 'count'})

# Rename the Category column to Category Count
df2 = df2.rename(columns={'Category': 'Category Count'})

# Reset the index
df2 = df2.reset_index()

Copy!

Common mistakes when using COUNTIF in Python#

While using pandas to replicate the COUNTIF function

Using .count when you want to use .pivot_table with count aggregation#

Often in Excel, you'll use COUNTIF to create a table of values, not just a single value. For instance, you might create a list of unique categories in column D and then use the formula in column E: =COUNTIF(A:A, D1), dragging the formula down to the last category.

When you want to implement the same functionality in Python, you should use .pivot_table with a count aggreation instead of a single .count method.

The pivot table will return a new dataframe with the count applied to each bucket of grouped data, while the .count method will return a single value.

Expecting case insensitivity#

In Excel, COUNTIF is case insensitive. For instance, the formula =COUNTIF(A:A, "apple") will count both "apple" and "Apple".

In pandas, however, the comparison is case sensitive. If you want to replicate the case insensitivity, you'll need to convert the column to lowercase before comparing it to the value you're looking for.

# Convert to lowercase before comparing
df['A'] = df['A'].str.lower()

# Find the number of cells that contain 'apple'
count = (df['A'] == 'apple').sum()

Copy!

Understanding the Count with Condition Formula in Excel#

The COUNTIF function in Excel takes two arguments: a range of cells and a criterion that defines which cells to count. Excel's COUNTIF function is case insensitive.

=COUNTIF(range, criteria)

COUNTIF Excel Syntax

Examples