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 fromidxmax()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.
<a id="h-quick-examples-of-find-rows-values-for-column-maximal-in-pandas"></a>
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’screate 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.
<a id="h-using-dataframe-idxmax-to-get-row-index-of-column-maximal"></a>
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.
<a id="h-get-the-actual-maximum-value-of-column"></a>
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
<a id="h-using-dataframe-query-to-get-rows-values-column-maximal"></a>
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
<a id="h-using-nlargest-to-get-column-maximum"></a>
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
<a id="h-complete-example-rows-values-of-column-maximal"></a>
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)
<a id="h-frequently-asked-questions-on-find-row-values-for-column-maximal"></a>
Frequently Asked Questions on Find Row Values for Column Maximal
To find the row with the maximum value in a specific column in Pandas, you can use the idxmax() function along with the loc[] accessor.
There is an alternative to idxmax() for finding the index of the maximum value in a column. You can use the argmax() method. For example, df['Fee'].argmax() is used to find the index of the maximum value in the 'Score' column, and then df.loc[] is used to retrieve the entire row based on that index.
You can use the nlargest() method to get the entire row with the maximum value in a specific column. For example, df.nlargest(1,'Fee') is used to get the row with the largest value in the 'Score' column. The 1 specifies that you want the top 1 row, and 'Fee' specifies the column based on which you want to find the maximum.
You can use the query() method to filter rows based on the maximum value in a column. For example, df.query('Fee== Score.max()') filters the rows where the 'Fee' column is equal to its maximum value.
To find the maximum value in the entire DataFrame, you can use the max() function without specifying a column. For example, df.max().max() returns the maximum value across all columns and rows in the DataFrame.
If there are ties for the maximum value, the methods mentioned earlier (such as idxmax(), nlargest(), or query()) will return the first occurrence of the maximum value. If you want to handle ties and retrieve all rows with the maximum value, you may need to take additional steps.
<a id="h-conclusion"></a>
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.
<a id="h-related-articles"></a>
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
<a id="h-references"></a>