Module 6: Apply 5

Import data

# excel file
MyData <- read_excel("data/MyData.xlsx")
MyData

## # A tibble: 68,815 × 12
##    fiscal_year month_grouping month_abbv component            land_border_region
##          <dbl> <chr>          <chr>      <chr>                <chr>             
##  1        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
##  2        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
##  3        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
##  4        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
##  5        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
##  6        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
##  7        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
##  8        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
##  9        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
## 10        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
## # ℹ 68,805 more rows
## # ℹ 7 more variables: area_of_responsibility <chr>, aor_abbv <chr>,
## #   demographic <chr>, citizenship <chr>, title_of_authority <chr>,
## #   encounter_type <chr>, encounter_count <dbl>

Apply the following dplyr verbs to your data

Filter rows

filter(MyData, fiscal_year == 2020)

## # A tibble: 10,071 × 12
##    fiscal_year month_grouping month_abbv component            land_border_region
##          <dbl> <chr>          <chr>      <chr>                <chr>             
##  1        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
##  2        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
##  3        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
##  4        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
##  5        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
##  6        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
##  7        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
##  8        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
##  9        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
## 10        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
## # ℹ 10,061 more rows
## # ℹ 7 more variables: area_of_responsibility <chr>, aor_abbv <chr>,
## #   demographic <chr>, citizenship <chr>, title_of_authority <chr>,
## #   encounter_type <chr>, encounter_count <dbl>

Arrange rows

arrange(MyData, title_of_authority)

## # A tibble: 68,815 × 12
##    fiscal_year month_grouping month_abbv component            land_border_region
##          <dbl> <chr>          <chr>      <chr>                <chr>             
##  1        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
##  2        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
##  3        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
##  4        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
##  5        2020 FYTD           APR        Office of Field Ope… Northern Land Bor…
##  6        2020 FYTD           APR        Office of Field Ope… Southwest Land Bo…
##  7        2020 FYTD           APR        Office of Field Ope… Southwest Land Bo…
##  8        2020 FYTD           APR        Office of Field Ope… Southwest Land Bo…
##  9        2020 FYTD           APR        Office of Field Ope… Southwest Land Bo…
## 10        2020 FYTD           APR        Office of Field Ope… Southwest Land Bo…
## # ℹ 68,805 more rows
## # ℹ 7 more variables: area_of_responsibility <chr>, aor_abbv <chr>,
## #   demographic <chr>, citizenship <chr>, title_of_authority <chr>,
## #   encounter_type <chr>, encounter_count <dbl>

Select columns

select(MyData, demographic, citizenship)

## # A tibble: 68,815 × 2
##    demographic   citizenship               
##    <chr>         <chr>                     
##  1 FMUA          BRAZIL                    
##  2 FMUA          CANADA                    
##  3 Single Adults CANADA                    
##  4 Single Adults CANADA                    
##  5 Single Adults CHINA, PEOPLES REPUBLIC OF
##  6 Single Adults CHINA, PEOPLES REPUBLIC OF
##  7 Single Adults OTHER                     
##  8 Single Adults OTHER                     
##  9 Single Adults PHILIPPINES               
## 10 Single Adults RUSSIA                    
## # ℹ 68,805 more rows

Add Columns

MyData <- MyData %>%
    mutate(proportion_count = encounter_count/sum(encounter_count))

Summarize by groups

 yr_encounter <- group_by(MyData, fiscal_year, encounter_type)
summarise(yr_encounter, delay = mean(yr_encounter, na.rm = TRUE))

## # A tibble: 14 × 3
## # Groups:   fiscal_year [5]
##    fiscal_year encounter_type delay
##          <dbl> <chr>          <dbl>
##  1        2020 Apprehensions     NA
##  2        2020 Expulsions        NA
##  3        2020 Inadmissibles     NA
##  4        2021 Apprehensions     NA
##  5        2021 Expulsions        NA
##  6        2021 Inadmissibles     NA
##  7        2022 Apprehensions     NA
##  8        2022 Expulsions        NA
##  9        2022 Inadmissibles     NA
## 10        2023 Apprehensions     NA
## 11        2023 Expulsions        NA
## 12        2023 Inadmissibles     NA
## 13        2024 Apprehensions     NA
## 14        2024 Inadmissibles     NA