Import data

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

data 
## # A tibble: 1,222 × 11
##     year months    state colon…¹ colon…² colon…³ colon…⁴ colon…⁵ colon…⁶ colon…⁷
##    <dbl> <chr>     <chr>   <dbl> <chr>     <dbl>   <dbl> <chr>   <chr>   <chr>  
##  1  2015 January-… Alab…    7000 7000       1800      26 2800    250     4      
##  2  2015 January-… Ariz…   35000 35000      4600      13 3400    2100    6      
##  3  2015 January-… Arka…   13000 14000      1500      11 1200    90      1      
##  4  2015 January-… Cali… 1440000 1690000  255000      15 250000  124000  7      
##  5  2015 January-… Colo…    3500 12500      1500      12 200     140     1      
##  6  2015 January-… Conn…    3900 3900        870      22 290     NA      NA     
##  7  2015 January-… Flor…  305000 315000    42000      13 54000   25000   8      
##  8  2015 January-… Geor…  104000 105000    14500      14 47000   9500    9      
##  9  2015 January-… Hawa…   10500 10500       380       4 3400    760     7      
## 10  2015 January-… Idaho   81000 88000      3700       4 2600    8000    9      
## # … with 1,212 more rows, 1 more variable: `Growth of colonies` <dbl>, and
## #   abbreviated variable names ¹​colony_n, ²​colony_max, ³​colony_lost,
## #   ⁴​colony_lost_pct, ⁵​colony_added, ⁶​colony_reno, ⁷​colony_reno_pct

State one question

where are bees the most endangered?

Plot data

plot_data <- data %>% group_by(year, state) %>% summarise(avg_pct = mean(colony_lost_pct, na.rm = TRUE)) %>% filter(year == 2021) %>% arrange(-avg_pct) %>% slice(1:10)

plot_data %>% ggplot(aes(x = avg_pct, y = fct_reorder(state, avg_pct))) + geom_col()

Interpret

Bees are most endangered in Colorado where the net bee gain or loss is as low as -700 bee colony. Colorado is therefore seen as a bad place for bees since this is where the highest rate of them are being killed.