Pandas Find Row Values for Column Maximal

In Pandas, you can find the row values for the maximum value in a specific column using the idxmax() function along with the column selection. You can find out the row values for column maximal in pandas DataFrame by using either DataFrame.idxmax(), DataFrame.query() methods and DataFrame.loc[] property. You can also use DataFrame.nlargest() and DataFrame.nsmallest() to get maximum and minimum of columns.

In this article, I will explain how to find row values for column maximal of Pandas DataFrame with some examples.

Key Points –

• Use the idxmax() function to get the index of the row where the column has the maximum value.
• Use the .loc[] accessor with the index obtained from idxmax() to retrieve the entire row with the maximum value.
• Use the nlargest() function if you need more than just the maximum value, allowing you to retrieve the top N rows for a column.
• If you need to find the maximum value for multiple columns, apply idxmax() to each column separately.
• Use the query() method to filter rows where the column value matches the maximum.
• Use the max() function to get the maximum value in a column, which can be used for comparison or filtering.

Quick Examples of Find Rows Values for Column Maximal in Pandas

If you are in hurry, below are some quick examples of how to find out row values for column maximal in Pandas DataFrame.

# Quick examples of find rows values for column maximal

# Example 1: Using DataFrame.idxmax() method
df2=df['Fee'].idxmax()

# Example 2: Using DataFrame.loc[] property
df2=df.loc[df['Fee'].idxmax()]

# Example 3: Use DataFrame.query() 
# To get rows where 'Fee' is maximal
max_fee = df['Fee'].max()
df2 = df.query(f'Fee == {max_fee}')

# Example 4: Using DataFrame.query() method
df2=df.query('Fee == Fee.max()')

# Example 5: Using DataFrame.nlargest() function
df2=df.nlargest(2,['Fee'])

# Example 6: Using DataFrame.nsmallest() function
df2=df.nsmallest(2,['Fee'])

Now, let’s create a Pandas DataFrame with a few rows and columns and execute some examples and validate results. Our DataFrame contains column names Courses, Fee, Duration, and Discount.

# Create Pandas DataFrame
import pandas as pd
import numpy as np
technologies= {
    'Courses':["Spark","Spark","PySpark","JAVA","Hadoop",".Net","Python","AEM","Oracle","SQL DBA","C","WebTechnologies"],
    'Fee' :[22000,25000,23000,24000,26000,30000,27000,28000,35000,32000,20000,15000],
    'Duration':['30days','35days','40days','45days','50days','55days','60days','35days','30days','40days','50days','55days']
          }
df = pd.DataFrame(technologies)
print("Create DataFrame:\n",df)

Yields below output.

Using DataFrame.idxmax() to Get Row Index of Column Maximal

DataFrame.idxmax() method return index of the first occurrence of maximum over requested axis (rows or columns). The idxmax() method returns a Series with the index of the maximum value for each column. By specifying the column axis (axis='columns'), the idxmax() method returns a Series with the index of the maximum value for each row.

In the below example, df['Fee'].idxmax() is used to find the index of the maximum value in the ‘Fee’ column. Adjust the column name (‘Fee’ in this case) as needed for your DataFrame.

# Using DataFrame.idxmax() method
value=df['Fee'].idxmax()
print("Getting the row index of column maximal:",value)

 Yields below output.

This returns index 8 which contains the maximum value for a column Fee.

Get the Actual Maximum Value of Column

You can get row values of column maximal of pandas by using DataFrame.loc[] property and idxmax(). The loc[] property is used to access a group of rows and columns by label(s) or a boolean array.

# Using DataFrame.loc[] property
df2=df.loc[df['Fee'].idxmax()]
print(df2)

Yields below output.

# Output:
Courses     Oracle
Fee          35000
Duration    30days
Name: 8, dtype: object

Using DataFrame.query() to Get Rows Values Column Maximal

You can get the row value of the column maximal of pandas by using DataFrame.query() method. The query() method is used to query the columns of a DataFrame with a boolean expression. This returns the entire row.

In this example, df['Fee'].max() is used to find the maximum value in the ‘Fee’ column, and then df.query() is used to filter rows where the ‘Fee’ column is equal to the maximum value.

# Use DataFrame.query() 
# To get rows where 'Fee' is maximal
max_fee = df['Fee'].max()
df2 = df.query(f'Fee == {max_fee}')
print(df2)

# Using DataFrame.query() method
df2=df.query('Fee == Fee.max()')
print(df2)

Yields below output.

# Output:
  Courses    Fee Duration
8  Oracle  35000   30days

Using nlargest() to Get Column Maximum

You can find the column maximum of pandas using DataFrame.nlargest() function. The nlargest() function is used to get the first n rows ordered by columns in descending order. The columns that are not specified are returned as well, but not used for ordering.

# Using DataFrame.nlargest() function.
df2=df.nlargest(2,['Fee'])
print(df2)

Yields below output.

# Output:
   Courses    Fee Duration
8   Oracle  35000   30days
9  SQL DBA  32000   40days

In the same way, you can also use it to find the minimum values of row values of the column with DataFrame.nsmallest() function.

# Using DataFrame.nsmallest() function.
df2=df.nsmallest(2,['Fee'])
print(df2)

Yields below output.

# Output:
            Courses    Fee Duration
11  WebTechnologies  15000   55days
10                C  20000   50days

Complete Example – Rows Values of Column Maximal

Below are complete examples of row values of column minimal of pandas DataFrame.

# Create Pandas DataFrame.
import pandas as pd
import numpy as np
technologies= {
    'Courses':["Spark","Spark","PySpark","JAVA","Hadoop",".Net","Python","AEM","Oracle","SQL DBA","C","WebTechnologies"],
    'Fee' :[22000,25000,23000,24000,26000,30000,27000,28000,35000,32000,20000,15000],
    'Duration':['30days','35days','40days','45days','50days','55days','60days','35days','30days','40days','50days','55days']
          }
df = pd.DataFrame(technologies)
print(df)

# Using DataFrame.idxmax() method
df2=df['Fee'].idxmax()
print(df2)

# Using DataFrame.loc[] property
df2=df.loc[df['Fee'].idxmax()]
print(df2)

# Using DataFrame.query() method
df2=df.query('Fee == Fee.max()')
print(df2)

# Using DataFrame.nlargest() function
df2=df.nlargest(2,['Fee'])
print(df2)

# Using DataFrame.nsmallest() function
df2=df.nsmallest(2,['Fee'])
print(df2)

Frequently Asked Questions on Find Row Values for Column Maximal

Conclusion

In this article, You can find the row values to column maximal of Pandas DataFrame by using DataFrame.idxmax(), DataFrame.query() methods and DataFrame.loc[] properties. You can also use DataFrame.nlargest() and DataFrame.nsmallest() to get maximum and minimum of columns in the DataFrame with above examples.

Related Articles

• Pandas Explode Multiple Columns 
• Pandas Left Join Explained By Examples
• Pandas Convert String to Integer
• Pandas Add Multiple Columns to DataFrame
• Count NaN Values in Pandas DataFrame
• Sort Pandas DataFrame by Single Column
• Find Unique Values From Columns in Pandas
• Find Intersection Between Two Series in Pandas?
• Drop First/Last N Columns From Pandas DataFrame
• Pandas Drop Last N Rows From DataFrame
• Select Rows From List of Values in Pandas DataFrame 
• Delete/Drop First N Rows From Pandas DataFrame

References

• https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.max.html