Skip to content
The Chat Archive
deepseek-v3-0324· DeepSeek LMArena Human Preference 140K

I currently use this python code to get the minimum value (expiry is datetime ns)…

  1. 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

  2. Assistant

    To get the second smallest expiry (sorted in ascending order) for each group in your DataFrame, you can use DuckDB's NTH_VALUE window function or ARRAY_AGG with 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 sorted expiry column.
    • PARTITION BY date groups by the date column.
    • ORDER BY expiry ASC sorts expiries in ascending order.
    • ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ensures 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) >= 2 clause.

    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! 🚀

  3. User

    no this still returns the second expiry and not the second unique expiry

  4. 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 is 2021-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:

    1. 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.

    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 ASC sorts 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) >= 2 in 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! 🚀

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.