# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---



# ## Source: https://pandas.pydata.org/pandas-docs/stable/index.html
#
# ### What is Pandas? 
# Pandas is an open source, BSD-licensed library providing high-performance, easy-to-use data structures and data analysis tools for the Python programming language.
#
# ### What data structures does Pandas use?
# Pandas has 2 primary data structures - namely Series(1-D) and DataFrame(2-D). 
#
# ### What to expect from this tutorial/notebook? 
# This tutorial is intended to introduce you to Pandas from a high level and then expose you to
# - Data Acquisition 
# - Data Cleaning 
# - Data Filtering 
# - Data Aggregation 
# - Data Analysis (depending on time availability)
#
# ### How is this different from the countless other materials that are publicly available? 
# It is by no means exhaustive or extensive, rather you can consider it my share of learnings that I picked up and learned as I attempted to use Python. I will be sharing tips and tricks that I found to be helpful, but if you know something better, you are welcome to share it with me/us. 

# #### How to create data-structures in Pandas ? 

# Import required libraries 
import pandas as pd
import numpy as np 
import os 
import pickle  
import csv 
from datetime import datetime

# Creating a series 
my_numeric_series = pd.Series([2, 3, 5, 7], name="Primes_Under_10")
print(my_numeric_series)
my_character_series = pd.Series(["DesertPy", "SoCal_Python", "PyLadies_of_LA"], name="Some_Python_Meetups")
print(my_character_series)
my_mixed_series = pd.Series([2, "a", 4, "b"], name="Mixed_Series")
print(my_mixed_series)

# +
# Creating a data frame 
# Method 1 - from list of lists 
print("******************************")
print("Printing Dataframe from method 1")
print("******************************")

list_of_lists = [["<NAME>", "Arizona", 2023], ["<NAME>", "California", 2023], 
                 ["<NAME>", "Florida", 2023], ["<NAME>", "New York", 2022],
                 ["<NAME>", "Georgia", 2023]]
governors_in_the_news_df = pd.DataFrame(data=list_of_lists, columns=["Name", "State", "Term_Expiry"])
print(governors_in_the_news_df)

print("******************************")
print("Printing Dataframe from method 2")
print("******************************")

# Method 2 - from dictionary of lists
dict_of_lists = {"Name": ["<NAME>", "<NAME>", "<NAME>", "<NAME>"],
                "State": ["Washington", "Connecticut", "Kentucky", "North Carolina"],
                "Term_Expiry": [2021, 2023, 2023, 2021]}
governors_df = pd.DataFrame(data=dict_of_lists)
print(governors_df)

print("******************************")
print("Printing Dataframe from method 3")
print("******************************")

# Method 3 - from list of dictionaries 
list_of_dicts = [{'USA': 50, 'Brazil': 26, 'Canada':10}]
states_in_countries_df = pd.DataFrame(data=list_of_dicts, index=["State_Count"])
print(states_in_countries_df)

print("******************************")
print("Printing Dataframe from method 4")
print("******************************")

# Method 4 - from lists with zip 
stock_symbols = ["AAPL", "AMZN", "V", "MA"]
prices_i_wish_i_bought_them_at = [50, 10, 1, 78]
stocks_i_wanted_df = pd.DataFrame(data=list(zip(stock_symbols, prices_i_wish_i_bought_them_at)),
                                  columns=["Stock_Symobl", "Dream_Price"]) 
print(stocks_i_wanted_df)

print("******************************")
print("Printing Dataframe from method 5")
print("******************************")

# Method 5 - dict of pd.Series 
dict_of_series = {'Place_I_Wanted_To_Be' : 
                    pd.Series(["New Zealand", "Fiji", "Bahamas"], index =["January",    "February", "March"]),                  'Place_I_Am_At' : 
                    pd.Series(["Home", "Home", "Home"], index =["January", "February", "March"])} 
lockdown_mood_df = pd.DataFrame(dict_of_series)
print(lockdown_mood_df)

# Delete the temporary varibles and datasets to avoid cluttering of workspace - these will not be used below
del my_numeric_series, my_character_series, my_mixed_series, list_of_lists, governors_in_the_news_df, dict_of_lists
del governors_df, list_of_dicts, states_in_countries_df, stock_symbols, prices_i_wish_i_bought_them_at, stocks_i_wanted_df, dict_of_series, lockdown_mood_df
# -

# ## Takeaways-1
# #### From the above examples, it is helpful to identify a few takeaways: 
# 1. Series and DataFrame can represent most of the commonly used data sets. Constructing your data into a Series or a DataFrame allows you to leverage a lot of built-in functionality that Pandas offers 
# 2. Series and DataFrame support homogeneous and heterogeneous data - meaning they can handle same data types as well as different data types 
# 3. Series and DataFrame have an index property which defaults to an integer but can be set as desired (imagine time stamps, letters etc.)
# 4. Pandas 1.0.0 deprecated the testing module and limited to only assertion functions. While not advisable, if you are using a version < 1.0.0, pandas.util.testing offers close to 30 different built-in functions to whip up different data frames that make it easy to test. You can get the list of possible functions like so 
#
# ```
# import pandas.util.testing as tm 
# dataframe_constructor_functions = [i for i in dir(tm) if i.startswith('make')]
# print(dataframe_constructor_functions)
# ```
#
# 5. While all of these are good to know, a typical use-case would not require a user to create data, rather import/acquire data from several different data sources - which leads us to our first topic of Data Acquisition 

# ## Data Acquisition 
#
# #### One of the most powerful and appealing aspects of Pandas is its ability to easily acquire and ingest data from several different data sources including but not limited to: 
# - CSV
# - Text 
# - JSON 
# - HTML 
# - Excel
# - SQL
#
#   An exhaustive list can be found here - https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html

# +
# CSV 
print("******************************")
print("Printing Dataframe from CSV")
print("******************************")

current_directory = os.getcwd()
confimed_global_cases_file_path = os.path.join(current_directory, "covid-19_data","time_series_covid19_confirmed_global.csv") 
confirmed_global_cases_df = pd.read_csv(filepath_or_buffer=confimed_global_cases_file_path) 
print(confirmed_global_cases_df.head()) 

# JSON 
print("******************************")
print("Printing Dataframe from JSON")
print("******************************")
json_file_path = os.path.join(current_directory, "sample_json.json")
json_df = pd.read_json(json_file_path)
print(json_df)

# Excel
print("******************************")
print("Printing Dataframe from Excel")
print("******************************")
# excel_file_path = os.path.join(current_directory, "covid-19_data","time_series_covid19_confirmed_recovered.xlsx")
os.chdir("covid-19_data")
recovered_global_cases_df = pd.read_excel("time_series_covid19_recovered_global.xlsx")
os.chdir(current_directory)
print(recovered_global_cases_df.head())

# Delete the temporary varibles and datasets to avoid cluttering of workspace - these will not be used below
del json_file_path, json_df, recovered_global_cases_df
# -

# ## Takeaways-2
#
# #### Based on the few limited examples above: 
# 1. Pandas has several robust i/o parsers which makes it really easy to consume data from several different sources 
# 2. If you are going to work with pandas, it is best to use pandas to acquire the data, as long as parser exists because they are optimized to handle large sets of data 
# 3. What would have been a great example would be to consume SQL data, but since I don't have enough power on my machine to install a SQL Server program, I am forced to skip that - if you have access to a database, do try and consume data from the database. You would be needing either pyodbc or sqlalchemy or a similar package as a wrapper. 

# ## Data Cleaning  & Data Filtering (They are quite intertwined)
#
# #### Messy (or) Unorganized data is very common. Even if the data is organized and logically makes sense, it might have missing data or NaN's or NA's  or may not be organized in a way the user wants it, which could be a hindrance to smooth analysis of your data. 
#
# ### Tip #1 - If you are going to modify a data frame and want the original data, create a copy 

# +
# By copying a dataframe with deep flag turned on, a new data frame is created including a copy of the data and the indices. 
# Changes made to this new data frame are not reflected in the original data frame object and vice-versa 
confirmed_global_cases_copy_df = confirmed_global_cases_df.copy(deep=True)

# Let us we want to only look at countries where there is state/province-level data available 
confirmed_global_cases_copy_df = confirmed_global_cases_copy_df[confirmed_global_cases_copy_df["Province/State"].notnull()] # Method 1 of filtering data in dataframe 
# -

# Notice that the data frame index does not hold any special order now and it does not convey any meaning by itself, unless we reference it or compare with the original data frame. We can fix that by re-setting the index 
confirmed_global_cases_copy_df = confirmed_global_cases_copy_df.reset_index(drop=True) 
# drop flag prevents the column from being added back to the dataframe as a new column. also remember to assign the data frame back to your variable. resetting of index, returns an object and unless we capture it and re-assign to the same variable, the change is lost 

# Let us say, we only want countries in the Northern Hemisphere - please note that this is just one method to create a new column. I want to show as many different options as possible 
def hemisphere_flag(df):
    if (df["Lat"] >= 0):
        return 1
    else:
        return 0 
confirmed_global_cases_copy_df["Northern_Hemisphere_Flag"] = confirmed_global_cases_copy_df.apply(hemisphere_flag, axis=1)
northern_hemisphere_confirmed_cases_df = confirmed_global_cases_copy_df.query("Northern_Hemisphere_Flag == 1") # Method 2 of filtering data in dataframe 
northern_hemisphere_confirmed_cases_df = northern_hemisphere_confirmed_cases_df.reset_index(drop=True)

# +
# Let us see how we can subset the data frame based on multiple conditions 
hubei_data_df = northern_hemisphere_confirmed_cases_df.loc[(northern_hemisphere_confirmed_cases_df["Country/Region"]=="China") & (northern_hemisphere_confirmed_cases_df["Province/State"]=="Hubei")] # Method 3 of filtering data in dataframe 

# Alternatively , if you wanted to generate a flag and not necessarily subset, you can use Numpy as follows 
northern_hemisphere_confirmed_cases_df['Alberta_Flag'] = np.where((northern_hemisphere_confirmed_cases_df["Country/Region"]=="Canada") & (northern_hemisphere_confirmed_cases_df["Province/State"]=="Alberta"), 1, 0) 

# +
# Let us look at the hubei data set, since we know that Hubei is in China and China is in the Northern Hemisphere, let us try to drop/delete the columns from the data set 
columns_to_drop_in_hubei_data = ["Country/Region", "Northern_Hemisphere_Flag"]
hubei_data_df.drop(columns_to_drop_in_hubei_data, inplace=True, axis=1)

# Alternatively, we could do the following, let us perform a similar action on Northern Hemisphere data 
columns_to_drop_in_northern_hemisphere = ["Alberta_Flag", "Northern_Hemisphere_Flag"]
northern_hemisphere_confirmed_cases_df.drop(columns=columns_to_drop_in_northern_hemisphere, inplace=True)

# +
# Let us delete a couple of the intermediate data frames that we created for demonstration purposes
del hubei_data_df, northern_hemisphere_confirmed_cases_df

# Now, let us use the copy we created to learn something else 
confirmed_global_cases_copy_df.info(verbose=True)
# -

# Changing the dtype of a column is another handy data cleansing technique that you would be using a lot 
confirmed_global_cases_copy_df.astype({'Lat': 'object', 'Long': 'object'}).dtypes

# If it instead made sense to case all your columns to a single data type you could simply do this
confirmed_global_cases_copy_df.astype('object').dtypes

# Now, let us look at "index", a very powerful and handy component of a Pandas data frame 
# While it does not need to be unique like a SQL primary key, it will definitely help optimize execution of a lot of the methods if the index is unique. 
china_confirmed_cases_df = confirmed_global_cases_copy_df[confirmed_global_cases_copy_df["Country/Region"].isin(["China"])]  
# We can see that and we have covered this before that index has been disrupted after the subset, so let us create a new index 
china_confirmed_cases_df.reset_index(drop=True, inplace=True)

# Now, let us assign one of the columns as the index 
china_confirmed_cases_df.set_index(keys=["Province/State"], drop=True, inplace=True)

china_confirmed_cases_df.index

# Ok, so how does setting the index really help ? 
china_subset_df = china_confirmed_cases_df.filter(like="Hubei", axis=0) # It allows filtering based on the unique index - especially powerful if data is time-series based
# Method 4 of filtering data in dataframe

# ## Takeaways-3
# `
# #### We covered several data cleaning and data filtering techniques, key points to remeber are: 
# 1. Unless, we  want to modify the source data, it is a good idea to make a deep copy of a data frame before making any modifications 
# 2. There are several different options to create a new column in a data frame, we covered a couple of these
#       - Using the apply function on a user defined function 
#       - Using the np.where construct 
# 3. We also looked at different ways to drop unwanted columns in a given dataframe (users are welcome to choose the construct that makes most sense to them)
#       - Using the axis keyword argument
#       - Using the columns keyword argument 
# 4. Using the info method with verbose set to true, produces a lot of helpful 
# 5. Casting individual column(s) to the desired data type as well as entire data frame to a data type of choice is possible using the astype() 
# 6. Resetting, setting and filtering using index of a dataframe (what has not been covered here is MultiIndex. That is a very useful concept, nut I am still trying to get a handle and could not cover that today)
# 7. Different ways to filter data in a dataframe:
#       - Using built in methods like ```pd.DataFrame.notnull()``` or ```pd.DataFrame.isin()``` along with logical indexing 
#       - Using ``` pd.DataFrame.query() ```  
#       - Using boolean indexing (Method 1 can be considered a subset of this)
#       - Using ``` pd.DataFrame.filter() ```
#       - There are other methods which haven't been covered like ```pd.DataFrame.iloc()``` which is better suited when the indices of rows desired are known

# +
## Data Aggregation 

#### Source: https://data.open-power-system-data.org/household_data/
# -

# Import Time Series Household data from open power system platform 
os.chdir(current_directory)
household_data_df = pd.read_csv("household_data_15min.csv")

household_data_df.drop(columns=["cet_cest_timestamp"], inplace=True) # Dropping the localized timestamp column as we are not going to try and deep dive into that 
household_data_df["utc_timestamp"] = pd.to_datetime(household_data_df["utc_timestamp"], format="%Y-%m-%dT%H:%M:%SZ")
household_data_df.set_index("utc_timestamp", drop=True, inplace=True)

# We see that there are rows with NaN, lets clear them out 
household_data_df.dropna(how="all")

# Since the data seems to be agregated, let us calculate actual generation at each time stamp 
household_data_generation_df = household_data_df.copy(deep=True)
household_data_generation_df.drop(columns="interpolated", inplace=True)
household_data_generation_df.info(verbose=True) # before calculating generation, we want to ensure that all columns are actual numeric and there is no character data 

household_data_generation_df = household_data_generation_df.diff()

# To calculate hourly generation data - let us try a few things 
hour_grouping = household_data_generation_df.index.hour
hourly_sum_df = household_data_generation_df.groupby(hour_grouping).sum()
hourly_mean_df = household_data_generation_df.groupby(hour_grouping).mean()

# This result might have caught you by surprise, but if you pay close attention, the groups that we created were based on the unique values under that group, which in this case is hours. In order to calculate hourly totals or aggregates, we would need our group to contain all the things that they are different by leading up to the hourly level. So: 
hour_grouping_sel = [(household_data_generation_df.index.year), (household_data_generation_df.index.month), 
                     (household_data_generation_df.index.day), (household_data_generation_df.index.hour)]
hourly_data_df = household_data_generation_df.groupby(hour_grouping_sel).sum()
hourly_data_df.head()

# +
# Let us aggregate data by month and since there is 2 months of data for January, lets select only 2017 
household_data_generation_df["Year"] = household_data_generation_df.index.year
household_data_generation_df["Month"] = household_data_generation_df.index.month
data_2017_df = household_data_generation_df[household_data_generation_df["Year"] == 2017] 
desired_cols = list(set(data_2017_df.columns) - set(["Year", "Month"]))
monthly_data = pd.pivot_table(data_2017_df, index=["Year", "Month"], values=desired_cols, aggfunc=np.mean)

# Alternatively 
# monthly_grouping_sel = [(household_data_generation_df.index.year), (household_data_generation_df.index.month)]
# monthly_mean_df = data_2017_df.groupby(monthly_grouping_sel).mean()
monthly_data
# -

# ## Takeaways-4
#
# #### We covered several data aggregation techniques, key points to remeber are: 
# 1. There are several different options to aggregate and dis-aggregate data using pandas
# 2. It is possible to achieve the desired result using any technique, users are recommended to pick the techniques that are easiest to them 
# 3. ``` groupby ``` is an extremely powerful technique and by far one of the most versatile and important tool from pandas 
#       - Similar to Groupby from SQL
# 4. Re-shaping data whether as a part of aggregation or cleanup is a very important technique to get a good grip on. Some techniques offered are: 
#       - Using ``` groupby ```
#       - Using ``` pd.pivot_table ```
#       - Using ``` pd.pivot ```, ``` pd.stack ``` (not covered here)



StarcoderdataJupyterScriptsDedupFiltered