Import data
# excel file
data <- read_excel("C:/Users/jessi/OneDrive - USNH/PSU_DAT3000_IntroToDa/01_module4/data/My Data_charts.xlsx",
sheet = "Total Employed by State")
data
## # A tibble: 3,434 × 7
## STATE RACE TYPE `ALL WORKERS` ARTISTS `ARTISTS SHARE` `LOCATION QUOTIENT`
## <chr> <chr> <chr> <dbl> <chr> <chr> <chr>
## 1 Alabama Afri… Acto… 581200 30 5.161734342739… 0.12670159687726373
## 2 Alabama Afri… Anno… 581200 265 4.559532002752… 1.0410684730034387
## 3 Alabama Afri… Arch… 581200 NA NA NA
## 4 Alabama Afri… Danc… 581200 0 NA NA
## 5 Alabama Afri… Desi… 581200 385 6.624225739848… 0.3065291344871363
## 6 Alabama Afri… Ente… 581200 60 1.032346868547… 0.366689327433022
## 7 Alabama Afri… Fine… 581200 80 1.376462491397… 0.2869121662690666
## 8 Alabama Afri… Land… 581200 NA NA NA
## 9 Alabama Afri… Musi… 581200 165 2.838953888506… 1.0394376807249026
## 10 Alabama Afri… Musi… 581200 440 7.570543702684… 0.6812026249347979
## # ℹ 3,424 more rows
Apply the following dplyr verbs to your data
Filter rows
filter(data, TYPE == "Fine Artists, Art Directors, And Animators")
## # A tibble: 260 × 7
## STATE RACE TYPE `ALL WORKERS` ARTISTS `ARTISTS SHARE` `LOCATION QUOTIENT`
## <chr> <chr> <chr> <dbl> <chr> <chr> <chr>
## 1 Alabama Afri… Fine… 581200 80 1.376462491397… 0.2869121662690666
## 2 Alabama Asian Fine… 33515 NA NA NA
## 3 Alabama Hisp… Fine… 88165 45 5.104066239437… 0.6078628168964766
## 4 Alabama Other Fine… 43075 0 NA NA
## 5 Alabama White Fine… 1496830 1310 8.751828865001… 0.5114433645395448
## 6 Alaska Afri… Fine… 13760 0 NA NA
## 7 Alaska Asian Fine… 26420 NA NA NA
## 8 Alaska Hisp… Fine… 26875 NA NA NA
## 9 Alaska Other Fine… 71330 240 0.003364643207… 1.831603675248008
## 10 Alaska White Fine… 253950 465 0.001831069108… 1.0700485119082856
## # ℹ 250 more rows
Fine.artists <- filter(data, TYPE == "Fine Artists, Art Directors, And Animators")
Arrange rows
arrange(data, desc(STATE), desc('ARTISTS'))
## # A tibble: 3,434 × 7
## STATE RACE TYPE `ALL WORKERS` ARTISTS `ARTISTS SHARE` `LOCATION QUOTIENT`
## <chr> <chr> <chr> <dbl> <chr> <chr> <chr>
## 1 Wyoming Afri… Acto… 2975 0 NA NA
## 2 Wyoming Afri… Anno… 2975 NA NA NA
## 3 Wyoming Afri… Arch… 2975 0 NA NA
## 4 Wyoming Afri… Danc… 2975 0 NA NA
## 5 Wyoming Afri… Desi… 2975 0 NA NA
## 6 Wyoming Afri… Ente… 2975 0 NA NA
## 7 Wyoming Afri… Fine… 2975 0 NA NA
## 8 Wyoming Afri… Land… 2975 0 NA NA
## 9 Wyoming Afri… Musi… 2975 0 NA NA
## 10 Wyoming Afri… Musi… 2975 0 NA NA
## # ℹ 3,424 more rows
Select columns
select(data, STATE, TYPE, ARTISTS)
## # A tibble: 3,434 × 3
## STATE TYPE ARTISTS
## <chr> <chr> <chr>
## 1 Alabama Actors 30
## 2 Alabama Announcers 265
## 3 Alabama Architects NA
## 4 Alabama Dancers And Choreographers 0
## 5 Alabama Designers 385
## 6 Alabama Entertainers 60
## 7 Alabama Fine Artists, Art Directors, And Animators 80
## 8 Alabama Landscape Architects NA
## 9 Alabama Music Directors And Composers 165
## 10 Alabama Musicians 440
## # ℹ 3,424 more rows
Add columns
data %>%
#group by state
group_by(STATE) %>%
#change artists column to numeric
filter(!ARTISTS %>% is.na()) %>%
filter(ARTISTS != "-") %>%
mutate(ARTISTS = ARTISTS %>% str_remove(",") %>% as.numeric()) %>%
#add Significance column
mutate(Significance = ARTISTS > 50) %>%
select(STATE, RACE, TYPE, ARTISTS, Significance)
## # A tibble: 3,433 × 5
## # Groups: STATE [57]
## STATE RACE TYPE ARTISTS Significance
## <chr> <chr> <chr> <dbl> <lgl>
## 1 Alabama African-American Actors 30 FALSE
## 2 Alabama African-American Announcers 265 TRUE
## 3 Alabama African-American Architects NA NA
## 4 Alabama African-American Dancers And Choreographers 0 FALSE
## 5 Alabama African-American Designers 385 TRUE
## 6 Alabama African-American Entertainers 60 TRUE
## 7 Alabama African-American Fine Artists, Art Directors, A… 80 TRUE
## 8 Alabama African-American Landscape Architects NA NA
## 9 Alabama African-American Music Directors And Composers 165 TRUE
## 10 Alabama African-American Musicians 440 TRUE
## # ℹ 3,423 more rows
Summarize by groups
data %>%
#group by state
group_by(STATE) %>%
#change artists column to numeric
mutate(ARTISTS = as.numeric(ARTISTS)) %>%
#calculate average artist employed
summarise(ARTISTS = mean(ARTISTS, na.rm = TRUE))
## # A tibble: 58 × 2
## STATE ARTISTS
## <chr> <dbl>
## 1 Alabama 433.
## 2 Alabama Subtotal 1435
## 3 Alaska 70.5
## 4 Alaska Subtotal 705
## 5 Arizona 816.
## 6 Arizona Subtotal 4540
## 7 Arkansas 326.
## 8 California 6848.
## 9 Colorado 903.
## 10 Connecticut 560.
## # ℹ 48 more rows