I currently use this python code to get the minimum value (expiry is datetime ns)…
- User
I currently use this python code to get the minimum value (expiry is datetime ns) df.groupby('date').expiry.min() I need to get the second sorted item from expiry. Say if unique expiries are 2021-01-01,2021-01-02,2021-01-03, I need to get the second expiry 2021-01-02
- Assistant
To get the second smallest expiry (sorted in ascending order) for each group in your DataFrame, you can use DuckDB's
NTH_VALUEwindow function orARRAY_AGGwith indexing. Here are two efficient ways to achieve this:
Method 1: Using
NTH_VALUE(Recommended for DuckDB)import duckdb query = """ SELECT date, NTH_VALUE(expiry, 2) OVER ( PARTITION BY date ORDER BY expiry ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS second_expiry FROM df GROUP BY date """ second_expiries = duckdb.sql(query).fetchdf() print(second_expiries)Explanation:
NTH_VALUE(expiry, 2)fetches the 2nd value in the sortedexpirycolumn.PARTITION BY dategroups by thedatecolumn.ORDER BY expiry ASCsorts expiries in ascending order.ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGensures the entire partition is considered.
Method 2: Using
ARRAY_AGG(Alternative)import duckdb query = """ SELECT date, (ARRAY_AGG(expiry ORDER BY expiry ASC))[2] AS second_expiry FROM df GROUP BY date """ second_expiries = duckdb.sql(query).fetchdf() print(second_expiries)Explanation:
ARRAY_AGG(expiry ORDER BY expiry ASC)collects expiries in ascending order.[2]extracts the 2nd element (0-based indexing would use[1], but DuckDB uses 1-based indexing for arrays).
Handling Edge Cases
- If a group has only 1 expiry, the result will be
NULL(since there is no second value). - If you want to skip NULLs, add a
HAVING COUNT(expiry) >= 2clause.
Comparison with Pandas
If you prefer Pandas, you can use:
second_expiries = ( df.sort_values('expiry') .groupby('date')['expiry'] .nth(1) # 0=first, 1=second, etc. )But DuckDB is faster for large datasets due to optimized SQL execution.
Let me know if you need adjustments! 🚀
- User
no this still returns the second expiry and not the second unique expiry
- Assistant
Got it! You want the second unique expiry per group, meaning if there are duplicate expiries, they should count as one. For example:
- If expiries are
[2021-01-01, 2021-01-01, 2021-01-02, 2021-01-03], the second unique expiry is2021-01-02(not the second occurrence).
Here’s how to do it in DuckDB (and a Pandas alternative for comparison):
Method 1: DuckDB (Using
DISTINCT+NTH_VALUE)import duckdb query = """ SELECT date, ( SELECT expiry FROM (SELECT DISTINCT expiry FROM df WHERE df.date = outer.date ORDER BY expiry ASC LIMIT 2) ORDER BY expiry DESC LIMIT 1 ) AS second_unique_expiry FROM df AS outer GROUP BY date """ second_unique = duckdb.sql(query).fetchdf() print(second_unique)Explanation:
- For each
date, we:- Get distinct expiries (
DISTINCT expiry). - Sort them in ascending order (
ORDER BY expiry ASC). - Take the first 2 (
LIMIT 2). - Then pick the last one (
ORDER BY expiry DESC LIMIT 1), which is the second unique expiry.
- Get distinct expiries (
Method 2: DuckDB (Using
ARRAY_AGG(DISTINCT))import duckdb query = """ SELECT date, (ARRAY_AGG(DISTINCT expiry ORDER BY expiry ASC))[2] AS second_unique_expiry FROM df GROUP BY date """ second_unique = duckdb.sql(query).fetchdf() print(second_unique)Explanation:
ARRAY_AGG(DISTINCT expiry)removes duplicates.ORDER BY expiry ASCsorts unique expiries.[2]picks the second element (1-based index in DuckDB).
Pandas Equivalent (For Reference)
If you prefer Pandas:
second_unique = ( df.drop_duplicates(['date', 'expiry']) # Keep only unique (date, expiry) pairs .sort_values(['date', 'expiry']) # Sort by expiry .groupby('date')['expiry'] # Group by date .nth(1) # Get the second unique expiry )
Edge Cases
- If a group has only 1 unique expiry, the result will be
NULL. - If you want to exclude groups with < 2 unique expiries, add
HAVING COUNT(DISTINCT expiry) >= 2in DuckDB.
Which is Best?
- DuckDB (Method 1) is the most efficient for large datasets.
- Pandas is simpler for small/medium data.
Let me know if you need adjustments! 🚀
- If expiries are
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.