In this article, we will use Pandas to perform an excel-like countifs.
Because the data in Excel is in the form of a table, we can conduct numerous arithmetic operations such as the sum of values, average and count of rows, and so on by specifying the condition on specified columns. In Python, we can also execute all of these actions on Pandas DataFrame. Because DataFrame also keeps the data in Tabular Format.
Countifs
It is an operation that is used to determine the number of rows by giving one or more conditions (similar to using a filter in an online shopping application) to obtain the desired results. There are a few methods similar to count() that are used to find the sum of data and the average of data, respectively.
Python Program to Perform Excel countifs using Pandas
Below are the examples of how to use countifs using Pandas in Python:
- Printing the Count of Android Phones
- Printing the Count of Android Phones
- Counting Android Phones whose price is less than 20000
Example-1: Printing the Count of Android Phones
Approach:
- Import the pandas module using the import, as keyword
- Create the pandas DataFrame using the DataFrame Function of the pandas module and store this in a Variable.
- Print electronicsDataframe DataFrame Value for acknowledgment.
- To find the count of a particular query we pass the Condition to the query() function and apply on the pandas on the above dataFrame.
- Apply the count() function to get the count.
- Print the count of the Android Phones by printing the above initialzed variable.
- The Exit of the Program.
Below is the Implementation:
# Import the pandas module using the import,as keyword import pandas as pd # Create the pandas DataFrame using the DataFrame Function of the pandas module and store this in a Variable electronicsDataframe = pd.DataFrame({'Brand': ['Oneplus', 'Apple', 'Realme', 'LG', 'Samsung', 'Boat','Oneplus' ], 'Type': ['Android Phones', 'IOS Phones','Android Phones', 'Refridgerator', 'Washing Machines', 'Headphones and Earphones','TV'], 'cost': [24999, 65999, 21999, 35000, 45000, 2500,22000]}) # Print electronicsDataframe DataFrame Value for acknowledgment print(electronicsDataframe) # To find the count of a particular query we pass the Condition to the query() # function and apply on the pandas DataFrame on the above dataFrame # Apply the count() function to get the count. phonesCount = electronicsDataframe.query('Type=="Android Phones" and Brand=="Oneplus"')['Type'].count() print() # Print the count of the Android Phones by printing the above initialzed variable print('Number of Android Phones of Oneplus : ', end="") print(phonesCount)
Output:
Brand Type cost 0 Oneplus Android Phones 24999 1 Apple IOS Phones 65999 2 Realme Android Phones 21999 3 LG Refridgerator 35000 4 Samsung Washing Machines 45000 5 Boat Headphones and Earphones 2500 6 Oneplus TV 22000 Number of Android Phones of Oneplus : 1
Example-2: Printing the Count of Android Phones
Approach:
- Import the pandas module using the import, as keyword.
- Create the pandas DataFrame using the DataFrame Function of the pandas module and store this in a Variable.
- Print electronicsDataframe DataFrame Value for acknowledgment.
- To find the count of a particular query we pass the Condition to the query() function and apply on the pandas on the above dataFrame.
- Apply the count() function to get the count.
- Print the count of the Android Phones by printing the above initialzed variable.
- The Exit of the Program.
Below is the Implementation:
# Import the pandas module using the import,as keyword import pandas as pd # Create the pandas DataFrame using the DataFrame Function of the pandas module and store this in a Variable electronicsDataframe = pd.DataFrame({'Brand': ['Oneplus', 'Apple', 'Realme', 'LG', 'Samsung', 'Boat','Oneplus' ], 'Type': ['Android Phones', 'IOS Phones','Android Phones', 'Refridgerator', 'Washing Machines', 'Headphones and Earphones','TV'], 'cost': [24999, 65999, 21999, 35000, 45000, 2500,22000]}) # Print electronicsDataframe DataFrame Value for acknowledgment print(electronicsDataframe) # To find the count of a particular query we pass the Condition to the query() # function and apply on the pandas DataFrame on the above dataFrame # Apply the count() function to get the count. androidPhonesCount = electronicsDataframe.query('Type=="Android Phones"')['Type'].count() print() # Print the count of the Android Phones by printing the above initialzed variable print('Number of Android Phones : ', end="") print(androidPhonesCount)
Output:
Brand Type cost 0 Oneplus Android Phones 24999 1 Apple IOS Phones 65999 2 Realme Android Phones 21999 3 LG Refridgerator 35000 4 Samsung Washing Machines 45000 5 Boat Headphones and Earphones 2500 6 Oneplus TV 22000 Number of Android Phones : 2
Example-3: Counting Android Phones whose price is less than 20000
Approach:
- Import the pandas module using the import, as keywords.
- Create the pandas DataFrame using the DataFrame Function of the pandas module and store this in a Variable.
- Print electronicsDataframe DataFrame Value for acknowledgment.
- To find the count of a particular query we pass the Condition to the query() function and apply on the pandas on the above dataFrame.
- Apply the count() function to get the count.
- Print the count of the Android Phones by printing the above initialzed variable.
Below is the Implementation:
# Import the pandas module using the import,as keyword import pandas as pd # Create the pandas DataFrame using the DataFrame Function of the pandas module and store this in a Variable electronicsDataframe = pd.DataFrame({'Brand': ['Oneplus', 'Apple', 'Realme', 'LG', 'Samsung', 'Boat','Oneplus' ], 'Type': ['Android Phones', 'IOS Phones','Android Phones', 'Refridgerator', 'Washing Machines', 'Headphones and Earphones','TV'], 'cost': [24999, 65999, 18999, 35000, 45000, 2500,22000]}) # Print electronicsDataframe DataFrame Value for acknowledgment print(electronicsDataframe) # To find the count of a particular query we pass the Condition to the query() # function and apply on the pandas DataFrame on the above dataFrame # Apply the count() function to get the count. result = electronicsDataframe.query('Type=="Android Phones" & cost<=20000')['Type'].count() print() # Print the count of the Android Phones by printing the above initialzed variable print('Number of Android Phones Whose Price less than 20000 : ', end="") print(result)
Output:
Brand Type cost 0 Oneplus Android Phones 24999 1 Apple IOS Phones 65999 2 Realme Android Phones 18999 3 LG Refridgerator 35000 4 Samsung Washing Machines 45000 5 Boat Headphones and Earphones 2500 6 Oneplus TV 22000 Number of Android Phones Whose Price less than 20000 : 1