Import your data

data <- read_excel("../00_data/myData.xlsx")
set.seed(12345)
data_small <- data %>%
    select(attack, defense, type_1) %>%
    slice(1:5)

data_small
## # A tibble: 5 × 3
##   attack defense type_1
##    <dbl>   <dbl> <chr> 
## 1     49      49 grass 
## 2     62      63 grass 
## 3     82      83 grass 
## 4     52      43 fire  
## 5     64      58 fire

Pivoting

long to wide form

pokemon_long <- data_small %>%
    pivot_longer(cols = c(attack, defense),
                 names_to = "stat",
                 values_to = "value")

pokemon_long
## # A tibble: 10 × 3
##    type_1 stat    value
##    <chr>  <chr>   <dbl>
##  1 grass  attack     49
##  2 grass  defense    49
##  3 grass  attack     62
##  4 grass  defense    63
##  5 grass  attack     82
##  6 grass  defense    83
##  7 fire   attack     52
##  8 fire   defense    43
##  9 fire   attack     64
## 10 fire   defense    58

wide to long form

pokemon_wide <- pokemon_long %>%
    pivot_wider(names_from = stat,
                values_from = value)
## Warning: Values from `value` are not uniquely identified; output will contain list-cols.
## • Use `values_fn = list` to suppress this warning.
## • Use `values_fn = {summary_fun}` to summarise duplicates.
## • Use the following dplyr code to identify duplicates.
##   {data} |>
##   dplyr::summarise(n = dplyr::n(), .by = c(type_1, stat)) |>
##   dplyr::filter(n > 1L)
pokemon_wide
## # A tibble: 2 × 3
##   type_1 attack    defense  
##   <chr>  <list>    <list>   
## 1 grass  <dbl [3]> <dbl [3]>
## 2 fire   <dbl [2]> <dbl [2]>

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 = "/")

pokemon_sep
## # 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>

Unite two columns

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

data_united
## # 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_wide <- data %>%
    select(pokemon, generation_id, hp) %>%
    pivot_wider(names_from = generation_id, values_from = hp)

data_wide
## # 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 <- data %>%
    complete(type_1, type_2)

data_complete
## # 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_filled <- data %>%
    fill(type_2, .direction = "down")

data_filled
## # 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>