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
expirydate per group (e.g., grouped bydate) in a Pandas DataFrame, you can use a combination of sorting andgroupby().nth(1). Here's how you can do it:
✅ Your Goal:
Given:
df.groupby('date').expiry.min()You want:
- The second smallest
expiryperdategroup. - Example:
If
expiryvalues per group are:
You want2021-01-01, 2021-01-02, 2021-01-032021-01-02— the second earliest.
✅ Solution
# Make sure expiry is datetime just in case df['expiry'] = pd.to_datetime(df['expiry']) # Sort by expiry before grouping (important for nth to work correctly) df_sorted = df.sort_values(['date', 'expiry']) # Get the second expiry per group (0-based indexing, so use nth(1) for second) second_expiry = df_sorted.groupby('date').nth(1)['expiry'] print(second_expiry)
🧠 How It Works:
sort_values(['date', 'expiry'])ensures that the earliest expiry dates are at the top of each group.- Using
groupby(...).nth(1)retrieves the second row for each group (after sorting). - Select only the
expirycolumn if you want a Series with just that.
🔄 If You Need It as a Column:
If you want the result as a DataFrame, not a Series:
second_expiry_df = df_sorted.groupby('date').nth(1).reset_index()[['date', 'expiry']]
📌 Example Input:
import pandas as pd data = { 'date': ['2021-01-01'] * 3 + ['2021-01-02'] * 2, 'expiry': ['2021-01-01', '2021-01-02', '2021-01-03', '2021-01-01', '2021-01-05'] } df = pd.DataFrame(data) df['expiry'] = pd.to_datetime(df['expiry'])📌 Output:
date expiry 2021-01-01 2021-01-02 2021-01-02 2021-01-05Let me know if you want the N-th expiry in general or if
date/expiryhave more constraints (like duplicates, nulls, etc.). - The second smallest
- User
no this still returns the second expiry and not the second unique expiry
- Assistant
Ah, thank you for clarifying! You're 100% right.
You're looking for the second smallest unique
expiryperdategroup, not just the second row based on sortedexpiryvalues.
✅ Updated Goal
Given a DataFrame:
date expiry 0 2021-01-01 2021-01-01 1 2021-01-01 2021-01-02 2 2021-01-01 2021-01-02 <-- duplicate expiry 3 2021-01-01 2021-01-03You want to group by
dateand get the second smallest uniqueexpiryvalue per group, i.e.,2021-01-02in this case.
✅ Correct Approach
You can use
groupby().apply()withdrop_duplicates()inside each group:import pandas as pd # Ensure expiry is datetime df['expiry'] = pd.to_datetime(df['expiry']) # Get the second **unique** expiry per date second_unique_expiry = ( df.groupby('date')['expiry'] .apply(lambda x: sorted(x.drop_duplicates())[1] if x.nunique() > 1 else pd.NaT) ) print(second_unique_expiry)
🔍 Explanation
drop_duplicates()orx.unique()ensures unique expiry values.sorted(...)will give you the sorted unique expiries.[1]picks the second one.- Handles groups with <2 unique expiries by returning
NaT(Not a Time).
🧪 Example
import pandas as pd data = { 'date': [ '2021-01-01', '2021-01-01', '2021-01-01', '2021-01-02', '2021-01-02', '2021-01-03' ], 'expiry': [ '2021-01-01', '2021-01-02', '2021-01-02', '2021-01-03', '2021-01-05', '2021-01-03' ] } df = pd.DataFrame(data) df['expiry'] = pd.to_datetime(df['expiry']) second_unique_expiry = ( df.groupby('date')['expiry'] .apply(lambda x: sorted(x.unique())[1] if x.nunique() > 1 else pd.NaT) ) print(second_unique_expiry)📌 Output
date 2021-01-01 2021-01-02 2021-01-02 2021-01-05 2021-01-03 NaT Name: expiry, dtype: datetime64[ns]Let me know if you want the 3rd unique expiry, or to keep duplicates optionally!
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.