# IF YOU ARE RUNNING THIS NOTEBOOK VIA GOOGLE COLAB, PLEASE UNCOMMENT and RUN THIS CELL
# from google.colab import drive
# drive.mount('/content/drive')

import matplotlib.pyplot as plt
import numpy as np
import pandas as pd

import warnings
warnings.simplefilter(action='ignore', category=FutureWarning)

pd.set_option('display.max_columns', 500)
pd.set_option('display.max_rows', 500)

"""
Explanation: <a href="https://colab.research.google.com/github/crazy54/work-related/blob/master/%5Bwebinar%5D_Hands_on_Data_Analysis_with_Python_student_nb.ipynb" target="_parent"><img src="https://colab.research.google.com/assets/colab-badge.svg" alt="Open In Colab"/></a>
TITLE:  Karting Through Video Games History with Python: how to manipulate your data from Zelda to CoD
Author: Andrea Giussani - Data Scientist at Cloud Academy
Date: Nov 12, 2020
Have you ever wondered what is the most played video game in the last thirty years? How about which gaming platform is the most used in the last decade?
In this hands-on webinar, we are going to explore advanced data manipulation techniques that are typically used to translate raw data into insightful plots and charts — enabling you to answer these types of questions. To perform this analysis, we will use Python and we will explore two of the most important data analytics libraries: Pandas and Matplotlib.
To get the most out of this webinar, we encourage some familiarity with Python, although extensive experience with the language would be best. For those who are new to Python, you can get your feet wet with the following Cloud Academy courses:

Working with Python
Python Functions, Modules, and Packages
Data Wrangling with Pandas

By the end of this webinar, the participants will be able to:

Understand the main functionalities of Pandas for data manipulation
Plot raw data into nice looking charts in Python using Matplotlib
Complete a data analytics pipeline for exploratory data analysis

Participants are strongly encouraged to download the necessary data from the following Github repo.
You can follow along with the webinar by using either your favorite local Python or Google Colab environment. For more details, please check the readme on the aforementioned Github repo.
End of explanation
"""


import pandas as pd
data = pd.read_csv('/content/drive/My Drive/ca.webinars/zelda/data/vgsales.csv')

"""
Explanation: 1. Data Manipulation and Cleaning
1.1 Data Ingestion
We read the file 'data/vgsales.csv' using the pandas read_csv() function, and store it into the variable data.
End of explanation
"""


data.info()

"""
Explanation: We now check the overall composition of the dataset using the .info() method
End of explanation
"""


# -------------------------------------------
# TO BE FILLED
# -------------------------------------------

"""
Explanation: 1.2 Data Cleaning
We clearly see there exists a few null values among both the Publisher and Year columns. The strategy here is to remove them. <br>
Let us remove the null values using the pandas .dropna() method:
End of explanation
"""


# -------------------------------------------
# TO BE FILLED
# -------------------------------------------

"""
Explanation: The dataset was generated at the end of the year 2016. Hence, each observation with an occurrence greater than or equal to 2017 must be considered as either corrupted or incorrect, and hence must be removed.
End of explanation
"""


# -------------------------------------------
# TO BE FILLED
# -------------------------------------------

"""
Explanation: 1.3 Data Consistency
Before moving to a proper data analysis, we need to be sure the data is consistent. By consistency I mean an intrinsic characteristic of the data. For instance, the Publisher name might contain a typo, or sometimes a Publisher might be identified by several names. 
To investigate this, let us check how Sony appears inside our dataset: we hence access to the Publisher column, which is of type object, and try to check the different names Sony is used for:
End of explanation
"""


# DO NOT REMOVE! THIS HAS BEEN PREFILLED FOR YOU
def merging_info_publisher(data: pd.DataFrame, publisher: str):
    data.loc[data['Publisher'].str.contains(publisher, case=False), 'Publisher'] = publisher
    return data[data['Publisher'].str.contains(publisher, case=False)]['Publisher'].value_counts()

"""
Explanation: Obviously, the Sony Publisher identifier is not homogeneously identified among observations.
We might think of, say, changing “Sony Computer Entertainment”, “Sony Computer Entertainment America”, “Sony Computer Entertainment Europe”, “Sony Music Entertainment” and “Sony Online Entertainment” to “Sony”.
To do this, we basically create a custom method, called merging_info_publisher, that should be called whenever we wish to perform such kind of cleaning on our data.
End of explanation
"""


# DO NOT REMOVE! THIS HAS BEEN PREFILLED FOR YOU
publishers = ['Sony', 'Nintendo', 'Ubisoft', 'Activision', 'Electronic Arts', 'Konami']
for publisher in publishers:
    merging_info_publisher(data, publisher)

"""
Explanation: Possibly, this pattern is repeated for different publishers as well. Here we identify a few Publishers that might have different labels inside the dataset, and we apply the merging_info_publisher method for each of them.
End of explanation
"""


# DO NOT REMOVE! THIS HAS BEEN PREFILLED FOR YOU
data[data['Publisher'].str.contains('Sony')]['Publisher'].value_counts()

"""
Explanation: Let us check now how "Sony" is mapped inside our dataset:
End of explanation
"""


# DO NOT REMOVE! THIS HAS BEEN PREFILLED FOR YOU
data.loc[data['Publisher'].str.contains('EA Sports', case=False), 'Publisher'] = 'Electronic Arts'

"""
Explanation: An extra control is to convert EA Sports to Electronic Arts.
End of explanation
"""


# DO NOT REMOVE! THIS HAS BEEN PREFILLED FOR YOU
namco_names = ['Bandai', 'Namco Bandai', 'Namco', 'Namco Bandai Games' ]
data.loc[data['Publisher'].str.contains('|'.join(namco_names), case=False), 'Publisher'] = 'Namco'

"""
Explanation: Also, convert ['Bandai', 'Namco Bandai', 'Namco', 'Namco Bandai Games' ] to Namco.
End of explanation
"""


# -------------------------------------------
# TO BE FILLED
# -------------------------------------------

"""
Explanation: Let us check the absolute distribution of the top 20 Publishers in our dataset:
End of explanation
"""


# -------------------------------------------
# TO BE FILLED
# -------------------------------------------

"""
Explanation: Finally, remove the Publisher called "Unknown": this is not obviously fine. Store the result into the global variable filtered_data
End of explanation
"""


# -------------------------------------------
# TO BE FILLED
# -------------------------------------------

"""
Explanation: 2. Total Games Released Each Year
We now want to know when the video game industry experienced a drastic development. Based on the number of games released each year, we might be able to find out when the video games boom happened. We store the distribution of video games releases by year inside the variable counter_df_by_year.
End of explanation
"""


# -------------------------------------------
# TO BE FILLED
# -------------------------------------------
ax.set_xlabel('Year')
ax.set_ylabel('Number of Games')
ax.set_title('Evolution of Video Games Industry')
# -------------------------------------------
# TO BE FILLED
# -------------------------------------------

"""
Explanation: Let us embed that dataframe into a graphical dimension:
End of explanation
"""


# DO NOT REMOVE! THIS HAS BEEN PREFILLED FOR YOU
total_sales_df = filtered_data[['Global_Sales', 'Publisher']].drop_duplicates().groupby('Publisher').sum()
top_10_sales = total_sales_df.sort_values(by='Global_Sales', ascending=False).head(10)

"""
Explanation: There was a significant boom in the late 2000s. Since then, the distinct number of release has shrunk possibly due to a more convergence to popular titles by both customers and developers.
3. Publisher Analysis with respect to Global Sales
Instead of considering absolute frequencies (with respect to the number of video games releases) of the top publishers, a better proxy is to consider the top publishers by Global Sales, identified by the columns "Global_sales".
End of explanation
"""


# -------------------------------------------
# TO BE FILLED
# -------------------------------------------

"""
Explanation: 3.2 Graphical Representation of the Publishers
We again embed the above dataset into a graphical dimension to better understand the data.
End of explanation
"""


# DO NOT REMOVE! THIS HAS BEEN PREFILLED FOR YOU
most_popular_platforms = filtered_data[['Name','Platform']].drop_duplicates().groupby('Platform').count()
most_popular_platforms.rename(columns={'Name': 'Total Observations'}, inplace=True)

"""
Explanation: 4. Understand the most popular Platform by Year
We now want to go further and try to see which Platform was the most popular for each Year. To do so, we again use a proxy the total Global Sales with respect to video games for each specific Platform. 
However, this requires a little bit of data wrangling, and therefore we need to perform a few steps to be able to answer to this question.
First of all, we count the number of video games by Platform using the .groupby() method, and we store the result into the variable most_popular_platforms. This has been done for you.
End of explanation
"""


# DO NOT REMOVE! THIS HAS BEEN PREFILLED FOR YOU
top_20_platforms = most_popular_platforms.sort_values(by='Total Observations', ascending=False).head(20)
filtered_data_top20 = filtered_data[filtered_data['Platform'].isin(list(top_20_platforms.index))]

"""
Explanation: We then need two steps:
* store inside the variable top20_platforms the top 20 platform with respect to the column Total Observations;
* filter the filtered_data with those Platforms. Store the result into filtered_data_top20.
This has been done for you.
End of explanation
"""


# -------------------------------------------
# TO BE FILLED
# -------------------------------------------

"""
Explanation: We aggregate the data with respect to Global Sales using the pandas .pivot_table(). We would like to have, as columns, the platforms' vendor and, as index, the year. Store the result into the pivoted_data_df.
End of explanation
"""


# DO NOT REMOVE! THIS HAS BEEN PREFILLED FOR YOU
rows_arrangement = np.argsort(-pivoted_data_df.values, axis=1)

"""
Explanation: The next part is a little bit tricky and it has been already prefilled for you. 
We now want to find the Platform which has the top sell for each distinct year. To do that we employ the NumPy method argsort() which basically allows to sort, for each row, the observations in ascending order.
End of explanation
"""


# DO NOT REMOVE! THIS HAS BEEN PREFILLED FOR YOU
data_platform_by_year = pd.DataFrame(pivoted_data_df.columns[rows_arrangement], index=pivoted_data_df.index)

"""
Explanation: We then select the column names based on the sorting operation, so that in the first place we will find the platform with highest value with respect to the aggregated Global Sales.
End of explanation
"""


# -------------------------------------------
# TO BE FILLED
# -------------------------------------------

"""
Explanation: 4.1 Distribution of the most popular platforms during the last 40 years
Store the results inside the variable most_popular_platform_by_year.
End of explanation
"""


# DO NOT REMOVE! THIS HAS BEEN PREFILLED FOR YOU
most_popular_games = pd.DataFrame()

for _, row in most_popular_platform_by_year.iterrows():
    year = row['Year']
    platform = row['Platform']
    
    inner_df = filtered_data.query("Year == @year & Platform==@platform")
    
    pivoted_table_year_platform = inner_df.pivot_table(
        index = 'Year',
        columns='Name',
        values='Global_Sales',
        aggfunc='sum',
        fill_value=0
    )
    
    temp_col_max_value = pivoted_table_year_platform.max(axis=1).to_frame() # finds max value by row
    temp_col_max_value.rename(columns={0:'Total sells (ML of units)'}, inplace=True)
    
    temp_col_max = pivoted_table_year_platform.idxmax(axis=1).to_frame() # find the column with the greatest value on each row
    temp_col_max.rename(columns={0:'Most Wanted Title'}, inplace=True)
    
    merging_dfs = pd.concat([temp_col_max, temp_col_max_value], axis=1)
    
    most_popular_games = most_popular_games.append(merging_dfs)

"""
Explanation: 5. Which was the most popular game in each Year?
Store the resulting dataframe object into the variable most_popular_games.
End of explanation
"""


# -------------------------------------------
# TO BE FILLED
# -------------------------------------------

"""
Explanation: 6. Which was the most sold Title by Platform?
Create a new object, called most_popular_vg_by_platform, that join the information from most_popular_platfrom_by_year and most_popular_games. Print the result in console.
End of explanation
"""


# -------------------------------------------
# TO BE FILLED
# -------------------------------------------

"""
Explanation: 7. Which are the most sold videogames ever?
We are interesting in investigating which were the most sold titles in the last 40 years. To do so, we employ the .groupby() method, and store the result into the most_wanted_vg variable.
End of explanation
"""


