Performing Excel like countifs in Python Pandas

Performing Excel like countifs in Python Pandas

by

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:

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

 

Leave a Comment