You have a pandas dataframe with three columns filled with state names, city names, and arbitrary numbers, respectively. How do you retrieve the top two cities per state based on the maximum number in the third column?

Question

Accepted Answer

Retrieve top 2 cities per state based on max number in third column of pandas dataframe.

Group the dataframe by state column
Sort each group by the third column in descending order
Retrieve the top 2 rows of each group using head(2) function
Concatenate the resulting dataframes using pd.concat() function

Accepted Answer

import pandas as pd

# Create a sample DataFrame
data = {
    "State": ["California", "California", "California", 
              "Texas", "Texas", "Texas", 
              "New York", "New York", "New York"],
    "City": ["Los Angeles", "San Francisco", "San Diego", 
             "Houston", "Dallas", "Austin", 
             "New York City", "Buffalo", "Rochester"],
    "Value": [100, 120, 80, 200, 150, 180, 300, 50, 70]
}

df = pd.DataFrame(data)

#To find top 2 cities per state based on values in 3rd column
top_2_cities_by_state = df.sort_values(by=['State', 'Value'], ascending=[True,False]).groupby("State").head(1)
print(top_2_cities_by_state)

Accepted Answer

df.groupby('state')['city', 'number'].nlargest(2, 'number')

You have a pandas dataframe with three columns filled with state names, city names, and arbitrary numbers, respectively. How do you retrieve the top two cities per state based on the maximum number in the third column?

Affine Data Scientist interview questions & answers

Popular interview questions of Data Scientist