How to filter a dataframe so that it finds the maximum value for 10 unique occurrences of another variable

user_15838622 0
Viewed 144

I have this dataframe here which I filter down to only include counties in the state of Washington and only include columns that are relevant for the answer I am looking for. What I want to do is filter down the dataframe so that I have 10 rows only, which have the highest Black Prison Population out of all of the counties in Washington State regardless of year. The part that I am struggling with is that there can't be repeated counties, so each row should include the highest Black Prison Populations for the top 10 unique county names in the state of Washington. Some of the counties have Null data for the populations for the black prison populations as well. for You should be able to reproduce this to get the updated dataframe.

library(dplyr)

incarceration <- read.csv("https://raw.githubusercontent.com/vera-institute/incarceration-trends/master/incarceration_trends.csv")
    
    blackPrisPop <- incarceration %>%
      select(black_prison_pop, black_pop_15to64, year, fips, county_name, state) %>%
      filter(state == "WA")

Sample of what the updated dataframe looks like (should include 1911 rows):

   fips   county_name state year black_pop_15to64 black_prison_pop
130 53005 Benton County    WA 2001             1008               25
131 53005 Benton County    WA 2002             1143               20
132 53005 Benton County    WA 2003             1208               21
133 53005 Benton County    WA 2004             1236               27
134 53005 Benton County    WA 2005             1310               32
135 53005 Benton County    WA 2006             1333               35
dplyr dataframe r
1 Answers

You can group_by the county county_name, and then use slice_max taking the row with maximum value for black_prison_pop. If you set n = 1 option you will get one row for each county. If you set with_ties to FALSE, you also will get one row even in case of ties.

You can arrange in descending order the black_prison_pop value to get the overall top 10 values across all counties.

library(dplyr)

incarceration %>%
  select(black_prison_pop, black_pop_15to64, year, fips, county_name, state) %>%
  filter(state == "WA") %>%
  group_by(county_name) %>%
  slice_max(black_prison_pop, n = 1, with_ties = FALSE) %>%
  arrange(desc(black_prison_pop)) %>%
  head(10)

Output

   black_prison_pop black_pop_15to64  year  fips county_name      state
              <dbl>            <dbl> <int> <int> <chr>            <chr>
 1             1845            73480  2002 53033 King County      WA   
 2              975            47309  2013 53053 Pierce County    WA   
 3              224             5890  2005 53063 Spokane County   WA   
 4              172            19630  2015 53061 Snohomish County WA   
 5              137             8129  2016 53011 Clark County     WA   
 6              129             5146  2003 53035 Kitsap County    WA   
 7              102             5663  2009 53067 Thurston County  WA   
 8               58              706  1991 53021 Franklin County  WA   
 9               50             1091  1991 53077 Yakima County    WA   
10               46             1748  2008 53073 Whatcom County   WA
user_34606700
Related