Write code to find the third-highest salary in a dataset using Pandas.

To find the third-highest distinct salary in a Pandas DataFrame, the most direct approach is to chain drop_duplicates(), nlargest(), and iloc.

Mechanics and Why

The primary method involves:

df['salary'].drop_duplicates(): This is crucial as "third-highest" typically refers to the third unique salary value, not the salary of the third person when sorted (which could be a duplicate of a higher salary).

.nlargest(3): This efficiently retrieves the top three distinct salary values without sorting the entire column, which is more performant for large datasets than a full sort.

.iloc[-1]: From these top three, selecting the last element gives the third-highest distinct salary.

An alternative using rank() involves df['salary'].rank(method='dense', ascending=False) to assign ranks to distinct salaries, then filtering for rank 3. The dense method ensures consecutive ranks without gaps, even with ties.

import pandas as pd
df = pd.DataFrame({'salary': [1000, 5000, 2000, 5000, 3000, 2000]})
# Finds the third highest distinct salary (2000)
third_highest_salary = df['salary'].drop_duplicates().nlargest(3).iloc[-1]

Trade-offs and Scalability

Edge Cases: If there are fewer than three distinct* salaries, nlargest(3) will return fewer than three elements. Accessing iloc[-1] in such a scenario will raise an IndexError. Robust code should handle this by checking the length of the result or using a try-except block. * Scalability: Pandas operates in-memory, meaning the entire DataFrame must fit into the RAM of a single machine. For datasets exceeding available memory, this approach will fail. * SQL Databases: For large datasets residing in a database, it's far more efficient to perform this operation directly in SQL using window functions like DENSE_RANK(). This leverages the database's optimized query engine and avoids transferring large amounts of data. For example, in Snowflake or PostgreSQL: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rn FROM employees) WHERE rn = 3; * PySpark: For truly massive, distributed datasets, PySpark is the appropriate tool. It can handle data larger than a single machine's memory by distributing computation across a cluster. The logic would be similar, using df.select("salary").dropDuplicates().orderBy(col("salary").desc()).limit(3).collect()[-1] or window functions, managing distributed operations like Spark shuffles efficiently.

In the interview, also mention…

Clarify with the interviewer whether "third-highest" means distinct values or if duplicate salaries should be counted (e.g., if two people earn the highest salary, is the next unique salary the "third highest" or the "second highest"?). Discuss handling edge cases (fewer than N unique salaries) and the critical scalability implications, suggesting SQL or PySpark for large-scale data.