Import your data

data <- readxl::read_xlsx("../00_data/Pokemon Data.xlsx")

Pivoting

wide to long form

pokemon_long <- data %>%
  pivot_longer(
    cols = c(hp, attack, defense, special_attack, special_defense, speed),
    names_to = "stat",
    values_to = "stat_value"
  )
knitr::kable(head(pokemon_long, 10))
id pokemon species_id height weight base_experience type_1 type_2 color_1 color_2 color_f egg_group_1 egg_group_2 url_icon generation_id url_image stat stat_value
1 bulbasaur 1 0.7 6.9 64 grass poison #78C850 #A040A0 #81A763 monster plant //archives.bulbagarden.net/media/upload/7/7b/001MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/001.png hp 45
1 bulbasaur 1 0.7 6.9 64 grass poison #78C850 #A040A0 #81A763 monster plant //archives.bulbagarden.net/media/upload/7/7b/001MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/001.png attack 49
1 bulbasaur 1 0.7 6.9 64 grass poison #78C850 #A040A0 #81A763 monster plant //archives.bulbagarden.net/media/upload/7/7b/001MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/001.png defense 49
1 bulbasaur 1 0.7 6.9 64 grass poison #78C850 #A040A0 #81A763 monster plant //archives.bulbagarden.net/media/upload/7/7b/001MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/001.png special_attack 65
1 bulbasaur 1 0.7 6.9 64 grass poison #78C850 #A040A0 #81A763 monster plant //archives.bulbagarden.net/media/upload/7/7b/001MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/001.png special_defense 65
1 bulbasaur 1 0.7 6.9 64 grass poison #78C850 #A040A0 #81A763 monster plant //archives.bulbagarden.net/media/upload/7/7b/001MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/001.png speed 45
2 ivysaur 2 1.0 13.0 142 grass poison #78C850 #A040A0 #81A763 monster plant //archives.bulbagarden.net/media/upload/a/a0/002MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/002.png hp 60
2 ivysaur 2 1.0 13.0 142 grass poison #78C850 #A040A0 #81A763 monster plant //archives.bulbagarden.net/media/upload/a/a0/002MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/002.png attack 62
2 ivysaur 2 1.0 13.0 142 grass poison #78C850 #A040A0 #81A763 monster plant //archives.bulbagarden.net/media/upload/a/a0/002MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/002.png defense 63
2 ivysaur 2 1.0 13.0 142 grass poison #78C850 #A040A0 #81A763 monster plant //archives.bulbagarden.net/media/upload/a/a0/002MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/002.png special_attack 80

long to wide form

pokemon_wide <- pokemon_long %>%
  pivot_wider(
    names_from = stat,
    values_from = stat_value
  )
knitr::kable(head(pokemon_wide, 10))
id pokemon species_id height weight base_experience type_1 type_2 color_1 color_2 color_f egg_group_1 egg_group_2 url_icon generation_id url_image hp attack defense special_attack special_defense speed
1 bulbasaur 1 0.7 6.9 64 grass poison #78C850 #A040A0 #81A763 monster plant //archives.bulbagarden.net/media/upload/7/7b/001MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/001.png 45 49 49 65 65 45
2 ivysaur 2 1.0 13.0 142 grass poison #78C850 #A040A0 #81A763 monster plant //archives.bulbagarden.net/media/upload/a/a0/002MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/002.png 60 62 63 80 80 60
3 venusaur 3 2.0 100.0 236 grass poison #78C850 #A040A0 #81A763 monster plant //archives.bulbagarden.net/media/upload/0/07/003MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/003.png 80 82 83 100 100 80
4 charmander 4 0.6 8.5 62 fire NA #F08030 NA NA monster dragon //archives.bulbagarden.net/media/upload/7/7d/004MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/004.png 39 52 43 60 50 65
5 charmeleon 5 1.1 19.0 142 fire NA #F08030 NA NA monster dragon //archives.bulbagarden.net/media/upload/b/be/005MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/005.png 58 64 58 80 65 80
6 charizard 6 1.7 90.5 240 fire flying #F08030 #A890F0 #DE835E monster dragon //archives.bulbagarden.net/media/upload/4/41/006MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/006.png 78 84 78 109 85 100
7 squirtle 7 0.5 9.0 63 water NA #6890F0 NA NA monster water1 //archives.bulbagarden.net/media/upload/9/97/007MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/007.png 44 48 65 50 64 43
8 wartortle 8 1.0 22.5 142 water NA #6890F0 NA NA monster water1 //archives.bulbagarden.net/media/upload/5/55/008MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/008.png 59 63 80 65 80 58
9 blastoise 9 1.6 85.5 239 water NA #6890F0 NA NA monster water1 //archives.bulbagarden.net/media/upload/1/12/009MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/009.png 79 83 100 85 105 78
10 caterpie 10 0.3 2.9 39 bug NA #A8B820 NA NA bug NA //archives.bulbagarden.net/media/upload/c/ca/010MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/010.png 45 30 35 20 20 45

Separating and Uniting

Unite two columns

pokemon_united <- data %>%
  unite(
    col = "type_1",
    type_1,
    type_2,
    sep = ", ",
    na.rm = TRUE
  )
knitr::kable(head(pokemon_united, 10))
id pokemon species_id height weight base_experience type_1 hp attack defense special_attack special_defense speed color_1 color_2 color_f egg_group_1 egg_group_2 url_icon generation_id url_image
1 bulbasaur 1 0.7 6.9 64 grass, poison 45 49 49 65 65 45 #78C850 #A040A0 #81A763 monster plant //archives.bulbagarden.net/media/upload/7/7b/001MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/001.png
2 ivysaur 2 1.0 13.0 142 grass, poison 60 62 63 80 80 60 #78C850 #A040A0 #81A763 monster plant //archives.bulbagarden.net/media/upload/a/a0/002MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/002.png
3 venusaur 3 2.0 100.0 236 grass, poison 80 82 83 100 100 80 #78C850 #A040A0 #81A763 monster plant //archives.bulbagarden.net/media/upload/0/07/003MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/003.png
4 charmander 4 0.6 8.5 62 fire, NA 39 52 43 60 50 65 #F08030 NA NA monster dragon //archives.bulbagarden.net/media/upload/7/7d/004MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/004.png
5 charmeleon 5 1.1 19.0 142 fire, NA 58 64 58 80 65 80 #F08030 NA NA monster dragon //archives.bulbagarden.net/media/upload/b/be/005MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/005.png
6 charizard 6 1.7 90.5 240 fire, flying 78 84 78 109 85 100 #F08030 #A890F0 #DE835E monster dragon //archives.bulbagarden.net/media/upload/4/41/006MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/006.png
7 squirtle 7 0.5 9.0 63 water, NA 44 48 65 50 64 43 #6890F0 NA NA monster water1 //archives.bulbagarden.net/media/upload/9/97/007MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/007.png
8 wartortle 8 1.0 22.5 142 water, NA 59 63 80 65 80 58 #6890F0 NA NA monster water1 //archives.bulbagarden.net/media/upload/5/55/008MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/008.png
9 blastoise 9 1.6 85.5 239 water, NA 79 83 100 85 105 78 #6890F0 NA NA monster water1 //archives.bulbagarden.net/media/upload/1/12/009MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/009.png
10 caterpie 10 0.3 2.9 39 bug, NA 45 30 35 20 20 45 #A8B820 NA NA bug NA //archives.bulbagarden.net/media/upload/c/ca/010MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/010.png

Separate a column

pokemon_separated <- pokemon_united %>%
  separate(
    col = type_1,
    into = c("type_1", "type_2"),
    sep = ", ",
    fill = "right",
    extra = "drop"
  )
knitr::kable(head(pokemon_separated, 10))
id pokemon species_id height weight base_experience type_1 type_2 hp attack defense special_attack special_defense speed color_1 color_2 color_f egg_group_1 egg_group_2 url_icon generation_id url_image
1 bulbasaur 1 0.7 6.9 64 grass poison 45 49 49 65 65 45 #78C850 #A040A0 #81A763 monster plant //archives.bulbagarden.net/media/upload/7/7b/001MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/001.png
2 ivysaur 2 1.0 13.0 142 grass poison 60 62 63 80 80 60 #78C850 #A040A0 #81A763 monster plant //archives.bulbagarden.net/media/upload/a/a0/002MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/002.png
3 venusaur 3 2.0 100.0 236 grass poison 80 82 83 100 100 80 #78C850 #A040A0 #81A763 monster plant //archives.bulbagarden.net/media/upload/0/07/003MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/003.png
4 charmander 4 0.6 8.5 62 fire NA 39 52 43 60 50 65 #F08030 NA NA monster dragon //archives.bulbagarden.net/media/upload/7/7d/004MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/004.png
5 charmeleon 5 1.1 19.0 142 fire NA 58 64 58 80 65 80 #F08030 NA NA monster dragon //archives.bulbagarden.net/media/upload/b/be/005MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/005.png
6 charizard 6 1.7 90.5 240 fire flying 78 84 78 109 85 100 #F08030 #A890F0 #DE835E monster dragon //archives.bulbagarden.net/media/upload/4/41/006MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/006.png
7 squirtle 7 0.5 9.0 63 water NA 44 48 65 50 64 43 #6890F0 NA NA monster water1 //archives.bulbagarden.net/media/upload/9/97/007MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/007.png
8 wartortle 8 1.0 22.5 142 water NA 59 63 80 65 80 58 #6890F0 NA NA monster water1 //archives.bulbagarden.net/media/upload/5/55/008MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/008.png
9 blastoise 9 1.6 85.5 239 water NA 79 83 100 85 105 78 #6890F0 NA NA monster water1 //archives.bulbagarden.net/media/upload/1/12/009MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/009.png
10 caterpie 10 0.3 2.9 39 bug NA 45 30 35 20 20 45 #A8B820 NA NA bug NA //archives.bulbagarden.net/media/upload/c/ca/010MS6.png 1 https://raw.githubusercontent.com/HybridShivam/Pokemon/master/assets/images/010.png

Missing Values