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) |
Output:
