group by factor and return second lowest value of other column

user_3970455 0
Viewed 1053

I'd like to group this data frame by the values in zipcode column, and return in another (called rate) column the second lowest rate or the lowest rate or the max rate.

For example, from this df:

zipcode state   county_code name    rate_area_x plan_id metal_level rate    rate_area_y
36749   AL  1001    Autauga 11  52161YL6358432  Silver  245.82  6
36749   AL  1001    Autauga 11  01100AO4222848  Silver  271.77  5
36749   AL  1001    Autauga 11  24848KC5063721  Silver  264.84  1
36749   AL  1001    Autauga 11  89885YK0256118  Silver  269.11  8
36749   AL  1001    Autauga 11  65392ON5819785  Silver  305.02  12
30165   AL  1019    Cherokee    13  52161YL6358432  Silver  245.82  6
30165   AL  1019    Cherokee    13  01100AO4222848  Silver  271.77  5
30165   AL  1019    Cherokee    13  24848KC5063721  Silver  264.84  1
30165   AL  1019    Cherokee    13  89885YK0256118  Silver  269.11  8
30165   AL  1019    Cherokee    13  65392ON5819785  Silver  305.02  12
30165   AL  1019    Cherokee    13  90884WN5801293  Silver  323.25  2
30165   AL  1019    Cherokee    13  79113BU1788705  Silver  344.81  7

I'd expect:

zipcode rate
36749   245.82
30165   245.82

In R I'd do this to get the min value for each zipcode group:

grouped_df <- df %>%
              group_by(zipcode) %>%
              summarise(rate = min(rate))

But how to get the second lowest rate value using Python's Pandas?

pandas python
3 Answers

Edit: I give you both smallest and 2nd smallest for you using in general case. However, as @WenYoBen mentioned in the comment you probably only want the 2nd lowest. If that is the case, you just need to chain reset_index, drop, and drop_duplicates to get smallest or 2nd smallest as follows:

Get smallest:

df.groupby('zipcode').rate.nsmallest(2).reset_index().drop('level_1',1) \
  .drop_duplicates(subset=['zipcode'])

Out[2108]:
       zipcode    rate
    0    30165  245.82
    2    36749  245.82

Get 2nd smallest:

df.groupby('zipcode').rate.nsmallest(2).reset_index().drop('level_1',1) \
  .drop_duplicates(subset=['zipcode'], keep='last')

Out[2109]:
   zipcode    rate
1    30165  264.84
3    36749  264.84

Original:

groupby.nsmallest will give you smallest and 2nd smallest of each group

df.groupby('zipcode').rate.nsmallest(2)

Out[2083]:
zipcode
30165    5    245.82
         7    264.84
36749    0    245.82
         2    264.84
Name: rate, dtype: float64
user_101892140

To get the results into a Dataframe you can use the group_by method with to_frame. Note, to get the nth lowest (and not the [:nth] lowest) sort the df and select the n you require.

import pandas as pd

data="""zipcode state   county_code name    rate_area_x plan_id metal_level rate    rate_area_y
36749   AL  1001    Autauga 11  52161YL6358432  Silver  245.82  6
36749   AL  1001    Autauga 11  01100AO4222848  Silver  271.77  5
36749   AL  1001    Autauga 11  24848KC5063721  Silver  264.84  1
36749   AL  1001    Autauga 11  89885YK0256118  Silver  269.11  8
36749   AL  1001    Autauga 11  65392ON5819785  Silver  305.02  12
30165   AL  1019    Cherokee    13  52161YL6358432  Silver  245.82  6
30165   AL  1019    Cherokee    13  01100AO4222848  Silver  271.77  5
30165   AL  1019    Cherokee    13  24848KC5063721  Silver  264.84  1
30165   AL  1019    Cherokee    13  89885YK0256118  Silver  269.11  8
30165   AL  1019    Cherokee    13  65392ON5819785  Silver  305.02  12
30165   AL  1019    Cherokee    13  90884WN5801293  Silver  323.25  2
30165   AL  1019    Cherokee    13  79113BU1788705  Silver  344.81  7"""

# create dataframe
n_columns = 9
data = [data.split()[x:x+n_columns] for x in range(0, len(data.split()), n_columns)]
df = pd.DataFrame(data[1:], columns=data[0]).apply(pd.to_numeric, errors='ignore')

# ensure the dataframe is sorted
df = df.sort_values(['zipcode','rate'])

min_df = df.groupby('zipcode').rate.min().to_frame(name = 'rate').reset_index()

max_df = df.groupby('zipcode').rate.max().to_frame(name = 'rate').reset_index()

second_lowest_df = df.groupby('zipcode').rate.nth(1).to_frame(name = 'rate').reset_index()
user_52098910

sort then groupby + nth. This gives you the flexibility to choose any arbitrarily ranked values (by passing a list). Drop duplicates if you don't want to double-count the same value. 

df.sort_values(['rate']).groupby('zipcode').rate.nth([1])
#zipcode
#30165    264.84
#36749    264.84
#Name: rate, dtype: float64

If you want the smallest, fourth smallest and largest values:

df.sort_values(['rate']).groupby('zipcode').rate.nth([0, 3, -1])
#zipcode
#30165    245.82
#30165    271.77
#30165    344.81
#36749    245.82
#36749    271.77
#36749    305.02
#Name: rate, dtype: float64

Out of bounds selections are ignored in groups where they do not exist:

df.sort_values(['rate']).groupby('zipcode').rate.nth(5)
#zipcode
#30165    323.25
#Name: rate, dtype: float64

Redundant selectors are not double counted (both 6 and -1 refer to the max element in 30165)

df.sort_values(['rate']).groupby('zipcode').rate.nth([6, 6, -1])
#zipcode
#30165    344.81
#36749    305.02
#Name: rate, dtype: float64
user_43333590
Related