Australian Animals

Read in the data

kb <- read_csv("https://raw.githubusercontent.com/datascienceprogram/ids_course_data/master/koala_bilby.csv", show_col_types = FALSE)
kb

## # A tibble: 5 x 5
##   ID    koala_NSW koala_VIC bilby_NSW bilby_VIC
##   <chr>     <dbl>     <dbl>     <dbl>     <dbl>
## 1 grey         23        43        11         8
## 2 cream        56        89        22        17
## 3 white        35        72        13         6
## 4 black        28        44        19        16
## 5 taupe        25        37        21        12

Pivot to longer

kb <- kb %>% 
  pivot_longer(cols = koala_NSW:bilby_VIC, names_to = "label", values_to = "count")
kb

## # A tibble: 20 x 3
##    ID    label     count
##    <chr> <chr>     <dbl>
##  1 grey  koala_NSW    23
##  2 grey  koala_VIC    43
##  3 grey  bilby_NSW    11
##  4 grey  bilby_VIC     8
##  5 cream koala_NSW    56
##  6 cream koala_VIC    89
##  7 cream bilby_NSW    22
##  8 cream bilby_VIC    17
##  9 white koala_NSW    35
## 10 white koala_VIC    72
## 11 white bilby_NSW    13
## 12 white bilby_VIC     6
## 13 black koala_NSW    28
## 14 black koala_VIC    44
## 15 black bilby_NSW    19
## 16 black bilby_VIC    16
## 17 taupe koala_NSW    25
## 18 taupe koala_VIC    37
## 19 taupe bilby_NSW    21
## 20 taupe bilby_VIC    12

Separate

kb <- kb %>% 
  separate(label, into = c("animal", "state"), sep = "_")
kb

## # A tibble: 20 x 4
##    ID    animal state count
##    <chr> <chr>  <chr> <dbl>
##  1 grey  koala  NSW      23
##  2 grey  koala  VIC      43
##  3 grey  bilby  NSW      11
##  4 grey  bilby  VIC       8
##  5 cream koala  NSW      56
##  6 cream koala  VIC      89
##  7 cream bilby  NSW      22
##  8 cream bilby  VIC      17
##  9 white koala  NSW      35
## 10 white koala  VIC      72
## 11 white bilby  NSW      13
## 12 white bilby  VIC       6
## 13 black koala  NSW      28
## 14 black koala  VIC      44
## 15 black bilby  NSW      19
## 16 black bilby  VIC      16
## 17 taupe koala  NSW      25
## 18 taupe koala  VIC      37
## 19 taupe bilby  NSW      21
## 20 taupe bilby  VIC      12

Pivot to wide

kb <- kb %>% 
  pivot_wider(names_from = "state", values_from = "count")
kb

## # A tibble: 10 x 4
##    ID    animal   NSW   VIC
##    <chr> <chr>  <dbl> <dbl>
##  1 grey  koala     23    43
##  2 grey  bilby     11     8
##  3 cream koala     56    89
##  4 cream bilby     22    17
##  5 white koala     35    72
##  6 white bilby     13     6
##  7 black koala     28    44
##  8 black bilby     19    16
##  9 taupe koala     25    37
## 10 taupe bilby     21    12

Pivot again with animals as the columns

I’ve not been saving the intermediate dataframes so I do some weird munging to rebuild the version I need before the final pivot_wider()

kb %>% 
  pivot_wider(names_from = "animal", values_from = c("NSW", "VIC")) %>% 
  pivot_longer(cols = NSW_koala:VIC_bilby, names_to = "label", values_to = "count") %>% 
  separate(label, into = c("state", "animal"), sep = "_") %>% 
  pivot_wider(names_from = "animal", values_from = "count")

## # A tibble: 10 x 4
##    ID    state koala bilby
##    <chr> <chr> <dbl> <dbl>
##  1 grey  NSW      23    11
##  2 grey  VIC      43     8
##  3 cream NSW      56    22
##  4 cream VIC      89    17
##  5 white NSW      35    13
##  6 white VIC      72     6
##  7 black NSW      28    19
##  8 black VIC      44    16
##  9 taupe NSW      25    21
## 10 taupe VIC      37    12