Performing Excel like countifs in Python Pandas

Last Updated : 22 Nov, 2021

In this article, we are performing an excel-like countifs in Pandas. In Excel the data is in the form of a Table, So we can perform many arithmetic operations like the sum of values, Average and count of rows, etc. by specifying the criterion on specified columns. Similarly, we can perform all those operations on Pandas DataFrame in Python. As DataFrame also maintains the data in Tabular Format.

Countifs

It is an operation used to find the count of rows by specifying one or more conditions (Same as applying a filter in an online shopping application) to get desired results. There are a few similar methods like count() such as sum(), mean(), etc. which are used to find the sum of data and average of data correspondingly.

Example 1: Performing an excel-like countifs in Pandas

Python3

# import necessary packages
import pandas as pd

# create a dataframe
costumes = pd.DataFrame({'Brand': ['Twills', 'Wrogn',
                                   'Twills', 'Trigger',
                                   'Twills', 'Wrogn', ],
                         'Costume_Type': ['Shirt', 'Shirt',
                                          'Shirt', 'Jeans',
                                          'T-Shirt', 'Jeans'],
                         'price': [1699, 1999, 1569,
                                   2000, 569, 2400]})

# DataFrame
print(costumes)

# find count of Twills Shirts
twills_Shirt_Count = costumes.query('Brand=="Twills" \
& Costume_Type=="Shirt"')['Costume_Type'].count()

print('Number of Twills Shirts-', end="")
print(twills_Shirt_Count)

Output:

Number of Twills Shirts-2

Explanation: As we have 3 Twills branded items but in that 3 we have 2 records in where costume type as shirts so it returned 2 as result.

Example 2: Here also we use the same above DataFrame but instead of finding the count of Twills branded shirt find Count of Shirts of any Brand.

Python3

# import necessary packages
import pandas as pd

# create a dataframe
costumes = pd.DataFrame({'Brand': ['Twills', 'Wrogn', 
                                   'Twills', 'Trigger',
                                   'Twills', 'Wrogn', ],
                         'Costume_Type': ['Shirt', 'Shirt',
                                          'Shirt', 'Jeans', 
                                          'T-Shirt', 'Jeans'],
                         'price': [1699, 1999, 1569, 
                                   2000, 569, 2400]})

# DataFrame
print(costumes)

# find count of Twills Shirts
Shirt_Count = costumes.query('Costume_Type=="Shirt"')
['Costume_Type'].count()

print('\nNumber of Shirts-', end="")
print(Shirt_Count)

Output:

Example 3: Using the above Costume DataFrame find the count of jeans whose price is less than or equal to 2000

Python3

# import necessary packages
import pandas as pd

# create a dataframe
costumes = pd.DataFrame({'Brand': ['Twills', 'Wrogn',
                                   'Twills', 'Trigger', 
                                   'Twills', 'Wrogn', ],
                         'Costume_Type': ['Shirt', 'Shirt', 
                                          'Shirt', 'Jeans', 
                                          'T-Shirt', 'Jeans'],
                         'price': [1699, 1999, 1569,
                                   2000, 569, 2400]})

# DataFrame
print(costumes)

# find count of Twills Shirts
Jeans_Count = costumes.query('Costume_Type=="Jeans" & price<=2000')[
    'Costume_Type'].count()

print('\nNumber of Jeans below or equals to Rs.2000-', end=" ")
print(Jeans_Count)