cleanPokemonTable

import pandas as pd
import numpy as np

pokemonTable = pd.read_csv("dataRaw/pokemonRaw.csv")

pokemonTable = pokemonTable.drop(columns= ['against_bug', 'against_dark', 'against_dragon', 'against_electric',
                    'against_fairy', 'against_fight', 'against_fire', 'against_fire', 'against_flying',
                    'against_ghost', 'against_grass', 'against_ground', 'against_ground', 'against_ice',
                    'against_normal', 'against_poison', 'against_psychic', 'against_rock', 'against_steel',
                    'against_water', 'base_egg_steps', 'base_happiness','base_total', 'capture_rate',
                     'experience_growth', 'height_m', 'percentage_male', 'pokedex_number'])

# From japanese_name column, extracts alphabetic characters into romanji column
pokemonTable['romanji'] = pokemonTable['japanese_name'].str.extract(f'([A-Za-z]+)')

# Fill empty cells in weight_kg column with 0 values
pokemonTable.weight_kg.fillna(0, inplace=True)



# Establish connection between abilities column and Abilities.csv

abilityTable = pd.read_csv("dataClean/Abilities.csv")

# Per row, replaces string storing all abilities into a list storing all abilities
pokemonTable.abilities = pokemonTable.abilities.str.findall(pat="'([^']+)'")

# Creates a dictionary mapping the ability name to the index found in the Abilities.csv
abilityIndexDict = dict()
for index, ability in enumerate(abilityTable.Name):
    # Remove spaces and capitalization to unify data
    ability = ability.replace(" ", "").lower()
    abilityIndexDict[ability] = index


# Since there is a m:n relationship between Pokemon and Abilities, create a new table mapping every PokemonId to their corresponding AbilityIds
pokemonAbilitiesTable = []

# Iterate through abilities column per Pokemon
for i, abilities in enumerate(pokemonTable.abilities):
    # Iterate through all possible abilities of one Pokemon
    for ability in abilities:
        # Translate abilityName to abilityIndex via abilityIndexDict
        abilityIndex = abilityIndexDict[ability.replace(" ", "").lower()]
        pokemonAbilitiesTable.append((i, abilityIndex))

# Create pokemonAbilitiesTable and store it as a separate CSV
pokemonAbilitiesTable = pd.DataFrame(pokemonAbilitiesTable, columns=["PokemonId", "AbilityId"])
pokemonAbilitiesTable.to_csv("dataClean/PokemonAbilitiesTable.csv")

# Drop abilities column since mapping has been extracted to separate PokemonAbilitiesTable
pokemonTable.drop("abilities", axis=1, inplace=True)

pokemonTable = pokemonTable.drop(columns=['japanese_name'])




# Move name-column to the start of the table
col = pokemonTable.pop("name")
pokemonTable.insert(0, col.name, col)

# Reorganize colummn order
pokemonTable.rename({"classfication": "classification"}, axis=1, inplace=True)
pokemonTable = pokemonTable[['name', 'romanji', 'type1', 'type2', 'classification', 'attack',
'defense', 'hp','sp_attack','sp_defense','speed','weight_kg','generation','is_legendary']]



pokemonTable.to_csv('dataClean/Pokemon.csv', index=False)