Week 8: Apply it to your data 7

Import your data

data <- read_excel("data/myData.xlsx")

Pivoting

long to wide form

pokemon_long <- data %>%
    pivot_longer(cols = c(hp, attack, defense, special_attack, special_defense, speed),
                 names_to = "stat",
                 values_to = "value")

wide to long form

pokemon_long %>%
    pivot_wider(names_from = stat,
                values_from = value)

## # A tibble: 949 × 23
##    Column1    id pokemon  species_id height weight base_experience type_1 type_2
##      <dbl> <dbl> <chr>         <dbl>  <dbl>  <dbl>           <dbl> <chr>  <chr> 
##  1       1     1 bulbasa…          1    0.7    6.9              64 grass  poison
##  2       2     2 ivysaur           2    1     13               142 grass  poison
##  3       3     3 venusaur          3    2    100               236 grass  poison
##  4       4     4 charman…          4    0.6    8.5              62 fire   NA    
##  5       5     5 charmel…          5    1.1   19               142 fire   NA    
##  6       6     6 chariza…          6    1.7   90.5             240 fire   flying
##  7       7     7 squirtle          7    0.5    9                63 water  NA    
##  8       8     8 wartort…          8    1     22.5             142 water  NA    
##  9       9     9 blastoi…          9    1.6   85.5             239 water  NA    
## 10      10    10 caterpie         10    0.3    2.9              39 bug    NA    
## # ℹ 939 more rows
## # ℹ 14 more variables: color_1 <chr>, color_2 <chr>, color_f <chr>,
## #   egg_group_1 <chr>, egg_group_2 <chr>, url_icon <chr>, generation_id <dbl>,
## #   url_image <chr>, hp <dbl>, attack <dbl>, defense <dbl>,
## #   special_attack <dbl>, special_defense <dbl>, speed <dbl>

Separating and Uniting

Separate a column

pokemon_sep <- data %>%
    unite(col = "types", type_1, type_2, sep = "/") %>%
    separate(col = types, into = c("type_1", "type_2"), sep = "/")

Unite two columns

data %>%
    unite(col = "types", type_1, type_2, sep = "/")

## # A tibble: 949 × 22
##    Column1    id pokemon    species_id height weight base_experience types    hp
##      <dbl> <dbl> <chr>           <dbl>  <dbl>  <dbl>           <dbl> <chr> <dbl>
##  1       1     1 bulbasaur           1    0.7    6.9              64 gras…    45
##  2       2     2 ivysaur             2    1     13               142 gras…    60
##  3       3     3 venusaur            3    2    100               236 gras…    80
##  4       4     4 charmander          4    0.6    8.5              62 fire…    39
##  5       5     5 charmeleon          5    1.1   19               142 fire…    58
##  6       6     6 charizard           6    1.7   90.5             240 fire…    78
##  7       7     7 squirtle            7    0.5    9                63 wate…    44
##  8       8     8 wartortle           8    1     22.5             142 wate…    59
##  9       9     9 blastoise           9    1.6   85.5             239 wate…    79
## 10      10    10 caterpie           10    0.3    2.9              39 bug/…    45
## # ℹ 939 more rows
## # ℹ 13 more variables: attack <dbl>, defense <dbl>, special_attack <dbl>,
## #   special_defense <dbl>, speed <dbl>, color_1 <chr>, color_2 <chr>,
## #   color_f <chr>, egg_group_1 <chr>, egg_group_2 <chr>, url_icon <chr>,
## #   generation_id <dbl>, url_image <chr>

Missing Values

data %>%
    select(pokemon, generation_id, hp) %>%
    pivot_wider(names_from = generation_id, values_from = hp)

## # A tibble: 949 × 9
##    pokemon      `1`   `2`   `3`   `4`   `5`   `6`   `7`  `NA`
##    <chr>      <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
##  1 bulbasaur     45    NA    NA    NA    NA    NA    NA    NA
##  2 ivysaur       60    NA    NA    NA    NA    NA    NA    NA
##  3 venusaur      80    NA    NA    NA    NA    NA    NA    NA
##  4 charmander    39    NA    NA    NA    NA    NA    NA    NA
##  5 charmeleon    58    NA    NA    NA    NA    NA    NA    NA
##  6 charizard     78    NA    NA    NA    NA    NA    NA    NA
##  7 squirtle      44    NA    NA    NA    NA    NA    NA    NA
##  8 wartortle     59    NA    NA    NA    NA    NA    NA    NA
##  9 blastoise     79    NA    NA    NA    NA    NA    NA    NA
## 10 caterpie      45    NA    NA    NA    NA    NA    NA    NA
## # ℹ 939 more rows

data %>%
    complete(type_1, type_2)

## # A tibble: 1,118 × 23
##    type_1 type_2  Column1    id pokemon species_id height weight base_experience
##    <chr>  <chr>     <dbl> <dbl> <chr>        <dbl>  <dbl>  <dbl>           <dbl>
##  1 bug    bug          NA    NA <NA>            NA   NA     NA                NA
##  2 bug    dark         NA    NA <NA>            NA   NA     NA                NA
##  3 bug    dragon       NA    NA <NA>            NA   NA     NA                NA
##  4 bug    electr…     595   595 joltik         595    0.1    0.6              64
##  5 bug    electr…     596   596 galvan…        596    0.8   14.3             165
##  6 bug    electr…     737   737 charja…        737    0.5   10.5             140
##  7 bug    electr…     738   738 vikavo…        738    1.5   45               225
##  8 bug    electr…     924 10122 vikavo…        738    2.6  148.              225
##  9 bug    fairy       742   742 cutief…        742    0.1    0.2              61
## 10 bug    fairy       743   743 ribomb…        743    0.2    0.5             162
## # ℹ 1,108 more rows
## # ℹ 14 more variables: hp <dbl>, attack <dbl>, defense <dbl>,
## #   special_attack <dbl>, special_defense <dbl>, speed <dbl>, color_1 <chr>,
## #   color_2 <chr>, color_f <chr>, egg_group_1 <chr>, egg_group_2 <chr>,
## #   url_icon <chr>, generation_id <dbl>, url_image <chr>

data %>%
    fill(type_2, .direction = "down")

## # A tibble: 949 × 23
##    Column1    id pokemon  species_id height weight base_experience type_1 type_2
##      <dbl> <dbl> <chr>         <dbl>  <dbl>  <dbl>           <dbl> <chr>  <chr> 
##  1       1     1 bulbasa…          1    0.7    6.9              64 grass  poison
##  2       2     2 ivysaur           2    1     13               142 grass  poison
##  3       3     3 venusaur          3    2    100               236 grass  poison
##  4       4     4 charman…          4    0.6    8.5              62 fire   NA    
##  5       5     5 charmel…          5    1.1   19               142 fire   NA    
##  6       6     6 chariza…          6    1.7   90.5             240 fire   flying
##  7       7     7 squirtle          7    0.5    9                63 water  NA    
##  8       8     8 wartort…          8    1     22.5             142 water  NA    
##  9       9     9 blastoi…          9    1.6   85.5             239 water  NA    
## 10      10    10 caterpie         10    0.3    2.9              39 bug    NA    
## # ℹ 939 more rows
## # ℹ 14 more variables: hp <dbl>, attack <dbl>, defense <dbl>,
## #   special_attack <dbl>, special_defense <dbl>, speed <dbl>, color_1 <chr>,
## #   color_2 <chr>, color_f <chr>, egg_group_1 <chr>, egg_group_2 <chr>,
## #   url_icon <chr>, generation_id <dbl>, url_image <chr>