<jupyter_start><jupyter_text>Data Analysis - Project---In this notebook we cover all the previous classes - go through what we learned Pandas functionality and apply them to real world data to analyze the USA presidents dataset and draw helpful insights based on analysis. After the data analysis process we discuss about the way how the results and what results must be presented and the possible improvements. At the very last part, we cover Pandas GUI - Graphical User Interface.$$$$ Lecture outline---* Fully fledged data analysis* Presenting results and insights* Discussion about improvements* Graphical User Interface for Pandas > The code is not optimized in any direction!!! DRY - Don't Repeat Yourself KISS - Keep It Stupid Simple<jupyter_code>import pandas as pd

import numpy as np

import matplotlib.pyplot as plt

import seaborn as sns

import plotly.express as px<jupyter_output><empty_output><jupyter_text>Data Processing---Usually, in this stage we clean and process data in order to have it in an appropriate form. Most of the time, data cleaning and processing takes 80% of data scientist's time and is the most tedious process. However, this is the step what we makes TRUE data scientists. Because, you can copy-paste code to build the Machine Learning models bu you cannot copy-paste code for data cleaning. This is where the true art starts.  Read Data<jupyter_code>df = pd.read_csv("data/presidents.csv")
df.shape # We have 44 rows and 8 columns
df.dtypes # All columns are represented as string
df.head()<jupyter_output><empty_output><jupyter_text>Process columns---Let deal with DataFrame columns. Rename them and remove leading and trailing spaces if any.<jupyter_code>df.columns # Columns contain leading and trailing spaces
df.columns = df.columns.str.strip() # Remove spaces - same as TRIM function in Excel


df.columns
column_mapping = {"#": "presidency_order",
                  "President": "president",
                  "Born": "birth_date",
                  "Age atstart of presidency": "age_at_start",
                  "Age atend of presidency": "age_at_end",
                  "Post-presidencytimespan": "post_presidency_timespan",
                  "Died": "death_date", "Age": "age_at_death"}



df = df.rename(column_mapping, axis=1) # Rename columns
df.head()<jupyter_output><empty_output><jupyter_text>Feature Description---* `presidency_order` - The order of presidency* `president` - First and last name of the president* `birth_date` - Date of birth* `age_at_start` - The age at the start of presidency* `age_at_end` - The age at the end of presidency* `post_presidency_timespan` - The period between death and presidency end* `death_date` - Death date* `age_at_death` - The age at the moment of death  Remove footnotes---Some columns contain footnote such as `[a]` in `birth_date` column or `[e]` in `age_at_end` column. We have to remove them as they do not carry any information and even might cause some issues.<jupyter_code>birth_date = (df["birth_date"].str.split("[", expand=True)
                              .drop(1, axis=1)
                              .rename({0: "birth_date"}, axis=1))


age_at_end = (df["age_at_end"].str.split("[", expand=True)
                              .drop(1, axis=1)
                              .rename({0: "age_at_end"}, axis=1))


post_presidency_timespan = (df["post_presidency_timespan"].str.split("[", expand=True)
                                                          .drop(1, axis=1)
                                                          .rename({0: "post_presidency_timespan"}, axis=1))<jupyter_output><empty_output><jupyter_text>We removed the footnotes but did not change the columns in the initial DataFrame. Note also, that we save processed columns as separate DataFrame. So we need to drop these columns from initial DataFrame and add processed ones instead.<jupyter_code>df = df.drop(["birth_date", "age_at_end", "post_presidency_timespan"], axis=1) # Drop columns
df
df = pd.concat([df, birth_date, age_at_end, post_presidency_timespan], axis=1) # Concatenate processed column
df<jupyter_output><empty_output><jupyter_text>Split Columns---The values of columns `age_at_start` and `age_at_end` consists of two parts: the first part is the age of the president and the second part is the date the president hold the office - White House and left the office, respectively. It's better to split these two columns into two parts, actual age and the date.To split these columns we have to figure out the common symbol or character on which we perform the split operation. If we observe, such a common character is `days` inside each value for each of those columns. Under common I mean the character or symbol which does not change across rows.<jupyter_code>df[["age_at_start", "age_at_end"]].head()
age_start = (df["age_at_start"].str.split("days", expand=True)
                               .rename({0: "age_at_start", 1: "presidency_start_date"}, axis=1))


age_end = (df["age_at_end"].str.split("days", expand=True)
                           .rename({0: "age_at_end", 1: "presidency_end_date"}, axis=1))<jupyter_output><empty_output><jupyter_text>Now, drop `age_at_start` and `age_at_end` columns and insert new derived columns instead.<jupyter_code>df = df.drop(["age_at_start", "age_at_end"], axis=1) # Drop columns
df = pd.concat([df, age_start, age_end], axis=1) # Add new columns
df.head()<jupyter_output><empty_output><jupyter_text>$$$$Some columns contain `days` component along with year. It's better to split these columns and will have year and days as a separate parts. That will make analysis process more smooth. Such columns are: `age_at_death`, `post_presidency_timespan`, `age_at_start`, `age_at_end`$$$$<jupyter_code>df[["age_at_death", "post_presidency_timespan", "age_at_start", "age_at_end"]].head()
age_at_death = (df["age_at_death"].str.rstrip("days")
                                  .str.split("years,", expand=True)
                                  .rename({0: "age_at_death_year",
                                           1: "age_at_death_days"},
                                          axis=1))
age_at_death<jupyter_output><empty_output><jupyter_text>`post_presidency_timespan` column contains some uncommon values such as `1 year, 259 days` and `103 days`. So we could not use the same approach we used above. To deal such a situation we have to use `Regular Expression`.<jupyter_code>post_presidency_timespan = (df["post_presidency_timespan"].str.rstrip("days")
                                                          .str.replace("year[s]?", "", regex=True)
                                                          .str.split(",", expand=True)
                                                          .rename({0: "post_presidency_timespan_year",
                                                                   1: "post_presidency_timespan_days"},
                                                                  axis=1))


post_presidency_timespan.loc[10] = [np.nan, 103] # Swap the values for one row
age_at_start = (df["age_at_start"].str.split("years,", expand=True)
                                  .rename({0: "age_at_start_year",
                                           1: "age_at_start_days"},
                                          axis=1))
age_at_end = (df["age_at_end"].str.split("years,", expand=True)
                                  .rename({0: "age_at_end_year",
                                           1: "age_at_end_days"},
                                          axis=1))<jupyter_output><empty_output><jupyter_text>**drop old columns and add new ones**<jupyter_code>df.head()
df = df.drop(["age_at_death", "post_presidency_timespan", "age_at_start", "age_at_end"], axis=1) # Drop columns
df = pd.concat([df, age_at_death, post_presidency_timespan, age_at_start, age_at_end], axis=1) # Add new columns
df.head()<jupyter_output><empty_output><jupyter_text>Type casting---The columns are represented as sting objects. We have to convert them into appropriate type.<jupyter_code>df.dtypes<jupyter_output><empty_output><jupyter_text>DateTime objects---Pandas supports `datetime object` - meaning that we can convert string representation of date into appropriate type and then operate on this object by using different methods.The candidates for this conversion are: `death_date`, `birth_date`, `presidency_start_date`, and `presidency_end_date`<jupyter_code>df["death_date"] = pd.to_datetime(df["death_date"].str.strip().str.replace("(living)", "", regex=False))
df["birth_date"] = pd.to_datetime(df["birth_date"].str.strip())
df["presidency_start_date"] = pd.to_datetime(df["presidency_start_date"].str.strip())
df["presidency_end_date"] = pd.to_datetime(df["presidency_end_date"].str.strip())<jupyter_output><empty_output><jupyter_text>Numeric objects---We have columns which are clearly numeric. However, they are interpreted as strings by Pandas due to a fact that Pandas cannot type cast automatically.The candidates for numeric type are all columns except datetime columns and `president` column.<jupyter_code>numeric_cols = ["age_at_death_year", "age_at_death_days",
                "post_presidency_timespan_year", "post_presidency_timespan_days",
               "age_at_start_year", "age_at_start_days",
               "age_at_end_year", "age_at_end_days"]
df[numeric_cols] = df[numeric_cols].apply(lambda x: x.str.strip()) # Apply strip function to all columns
df[numeric_cols] = df[numeric_cols].apply(pd.to_numeric) # Apply type casting
df.dtypes<jupyter_output><empty_output><jupyter_text>Reorder Columns---Let reorder columns to have them in logical order<jupyter_code>df.head()
columnsTitles = ["presidency_order",
                 "president",
                 "presidency_start_date", 
                 "presidency_end_date",
                "birth_date",
                 "age_at_start_year",
                 "age_at_start_days",
                "age_at_end_year",
                 "age_at_end_days",
                "post_presidency_timespan_year",
                 "post_presidency_timespan_days",
                "death_date",
                 "age_at_death_year",
                 "age_at_death_days"]
df = df.reindex(columns=columnsTitles)
df.head()<jupyter_output><empty_output><jupyter_text>Add Party Affiliation and Birth Place---Pandas can read HTML tables from the website. Here, I use this functionality to enrich our data with the party affiliation and birth place of the USA presidents. However, these data is messy and it needs separate processing.<jupyter_code>party = pd.read_html("https://www.britannica.com/topic/Presidents-of-the-United-States-1846696")[0]
party.head()
birth_place = pd.read_html("https://en.wikipedia.org/wiki/List_of_presidents_of_the_United_States_by_home_state")[0]
birth_place.head()<jupyter_output><empty_output><jupyter_text>Write these data in `CSV` file<jupyter_code># party.to_csv("data/party.csv", index=False)

# birth_place.to_csv("data/birth_place.csv", index=False)<jupyter_output><empty_output><jupyter_text>Process Political Party Affiliation<jupyter_code>party.head()<jupyter_output><empty_output><jupyter_text>Drop unnecessary columns<jupyter_code>party = party.drop(["Unnamed: 0", "no.", "birthplace", "term"], axis=1)
party<jupyter_output><empty_output><jupyter_text>Remove last three row as they contain extra redundant information.<jupyter_code>party = party.drop([44, 45, 46, 47], axis=0)<jupyter_output><empty_output><jupyter_text>Remove leading and trailing spaces<jupyter_code>party = party.apply(lambda x: x.str.strip())
party<jupyter_output><empty_output><jupyter_text>Merge `party` DataFrame with our initial DataFrame---The order is not preserved. Hence we merge these two DataFrames on index.<jupyter_code>df = df.merge(party["political party"], left_index=True, right_index=True)
df.head()<jupyter_output><empty_output><jupyter_text>Birth Place Data<jupyter_code>birth_place.head()<jupyter_output><empty_output><jupyter_text>Remove last two rows<jupyter_code>birth_place = birth_place.drop([43, 44, 45, 46], axis=0)<jupyter_output><empty_output><jupyter_text>Rename columns<jupyter_code>column_mapping = {"Date of birth": "birth_date", "President": "president",
                 "Birthplace": "city", "State† of birth": "state"}


birth_place = birth_place.rename(column_mapping, axis=1)<jupyter_output><empty_output><jupyter_text>Remove `†` character from the `state` column<jupyter_code>birth_place["state"] = birth_place["state"].str.strip("†").str.strip()<jupyter_output><empty_output><jupyter_text>Remove leading and trailing spaces<jupyter_code>birth_place = birth_place.apply(lambda x: x.str.strip())<jupyter_output><empty_output><jupyter_text>The order of rows in `birth_place` DataFrame is not set according to presidency order. Hence, we need to find at least one common column between `birth_place` and our initial DataFrame. That column could be `president` as it is represented in both DataFrame.<jupyter_code>birth_place.iloc[7]["president"] = "<NAME>" # Change value to have proper merge result
birth_place = birth_place.drop(["birth_date", "In office"], axis=1) # Drop unnecessary columns<jupyter_output><empty_output><jupyter_text>Merge `birth_place` DataFrame with our initial DataFrame<jupyter_code>df = df.merge(birth_place, how="inner", on="president")
df.head()<jupyter_output><empty_output><jupyter_text>Data Analysis...---Here we try to extract as much information from our data as possible.<jupyter_code>df.head()<jupyter_output><empty_output><jupyter_text>Sort DataFrame by `presidency_order`<jupyter_code>df = df.sort_values(by="presidency_order").reset_index(drop=True)<jupyter_output><empty_output><jupyter_text>Summary Statistics<jupyter_code>df.describe().round(2).T.iloc[1:, 1:]<jupyter_output><empty_output><jupyter_text>Political party and presidents distribution<jupyter_code>pd.DataFrame(df["political party"].value_counts())<jupyter_output><empty_output><jupyter_text>State and president distribution<jupyter_code>pd.DataFrame(df["state"].value_counts())<jupyter_output><empty_output><jupyter_text>What is the longest and shortest period between presidency start and end date? We can calculate it my taking difference between `age_at_start_year` and `age_at_end_year` then find the maximum and minimum value of this column.<jupyter_code>(df["age_at_end_year"] - df["age_at_start_year"]).max() # Max period of presidency is 12 years
df.iloc[(df["age_at_end_year"] - df["age_at_start_year"]).idxmax()]["president"]
(df["age_at_end_year"] - df["age_at_start_year"]).min() # Min period of presidency is 0 years. Maybe few days!!!
df.iloc[(df["age_at_end_year"] - df["age_at_start_year"]).idxmin()]["president"]<jupyter_output><empty_output><jupyter_text>Fact about [<NAME>](https://en.wikipedia.org/wiki/William_Henry_Harrison) Which President lived the longest and shortest after presidency end?<jupyter_code>df.iloc[df["post_presidency_timespan_year"].idxmax()]["president"] # The longest living president
df.iloc[df["post_presidency_timespan_year"].idxmin()]["president"] # The shortest living president<jupyter_output><empty_output><jupyter_text>Which president was the oldest and the youngest at the start of the presidency?<jupyter_code>df.iloc[df["age_at_start_year"].idxmax()]["president"] # The oldest president at the start of the presidency<jupyter_output><empty_output><jupyter_text>The youngest president at the start of the presidency<jupyter_code>df.iloc[df["age_at_start_year"].idxmin()]["president"]<jupyter_output><empty_output><jupyter_text>Which president died the oldest and the youngest?<jupyter_code>df.iloc[df["age_at_death_year"].idxmax()]["president"] # The oldest died president after presidency end
df.iloc[df["age_at_death_year"].idxmin()]["president"] # The yougest died president after presidency end<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10952861104108898, 'fraction_numerical': 0.005797832114948324, 'mean_word_length': 4.209783322390019, 'pattern_counts': {'":': 12, '<': 155, '<?xml version=': 0, '>': 156, 'https://': 3, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 31, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '38', 'dir': 'jupyter-structured-clean-dedup', 'id': '196107', 'license': 'MIT', 'n_tokens_mistral': 4953, 'n_tokens_neox': 4720, 'n_words': 1266, 'path': 'Lecture 7/Project_1.ipynb', 'repo_name': 'Okroshiashvili/Data_Analysis_Python', 'size': '15894'}