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

# # Pandas
#
#
# Pandas (Panel Datasets) is a data science Python library to load, clean, transform and analyze datasets.  
# Pandas uses `numpy` internally for the numeric calculation.  
#
# Pandas is installed with : `pip install pandas`
#
# A dataset is represented by a `DataFrame`, a 2-dimension array with named columns.  
# A `Series` is a 1-dimension array of data, used to represent a column or a row.  
#
#
# ## Dataframe creation
#
# Dataframes can be created from :
# - a python dict object (each key of the dict is a column of the dataframe)
# - some pandas column series
# - a file (CSV, TSV, JSON, Excel)
# - a database table or SQL query

# +
import pandas as pd

# Create a test dataframe from a dict
data = {
    'orderRef': ['A001', 'A007', 'A010', 'A011'],
    'client':   ['Bob', 'Alice', 'Bob', 'John'],
    'apples':   [3, 2, 0, 1], 
    'oranges':  [0, 3, 7, 2]
}
df = pd.DataFrame(data)
df = df.set_index('orderRef')   # we can set a column of the dataframe as the index
df
# -

# display the row with a given index as a Series
df.loc['A010']

# display the column with a given name as a Series
df['apples']
df.apples          # equivalent syntax (only work when there is no space in the name)

# Create a dataframe from several column series
col1 = pd.Series({'A001': 'Bob', 'A007': 'Alice', 'A010': 'Bob', 'A011': 'John'}, name='client')
col2 = pd.Series({'A001': 3, 'A007': 2, 'A010': 0, 'A011': 1}, name='apples')
col3 = pd.Series({'A001': 0, 'A007': 3, 'A010': 7, 'A011': 2}, name='oranges')
pd.concat([col1, col2, col3], axis='columns', sort=False)

# Import a dataset from a CSV file
# if the CSV has no header, pass the "names" array parameters with column names
# if the CSV has some values to treat as NaN (like 'Missing' or 'N/A' strings), use na_values param
df = pd.read_csv('data/purchases.csv', index_col="orderRef")
df

# +
# Export to CSV / TSV / Excel / JSON
df.to_csv('new_purchases.csv')
df.to_csv('new_purchases.tsv', sep='\t')
df.to_excel('new_purchases.xlsx')            # require module openpyxl installed
df.to_json('new_purchases.json')

# Also possible to export to a DB via SQL (need to install SQLAchemy and the package for our SQL DB)
from sqlalchemy import create_engine
import psycopg2                        # Python module for PostgreSQL
db_user           = 'dbuser'
db_password       = '<PASSWORD>'
db_server         = 'localhost'
db_port           = 5432
db_name           = 'sampledb'
db_connection_url = f'postgresql://{db_user}:{db_password}@{db_server}:{db_port}/{db_name}'
engine = create_engine(db_connection_url)
# df.to_sql('sample_table', engine, if_exists='replace')          # should work when using a real DB
# -

# ## Dataset overview

# +
# load a dataframe
df = pd.read_csv("data/IMDB-Movie-Data.csv", index_col="Title")

# print 5 first rows
df.head()

# print last 3 rows
df.tail(3)
# -

# number of rows and columns
df.shape

# data type of each column
df.dtypes

# list of the column names
df.columns

# row counts, non-null values for each column, column types, memory used...
df.info()

# ## Slicing / Selecting / Extracting

# +
# Select on columns
df['Rank']              # Series (rank column)
df[['Rank', 'Year']]    # DataFrame (sub-dataframe with 2 columns)
df[['Rank']]            # DataFrame (sub-dataframe with 1 column)

# Select on rows by index column
df.loc['Split']                                    # Series (row with "Split" title)
df.loc[['Split', 'Inception']]                     # Dataframe (rows with "Split" and 'Inception' index)
df.loc[['Split', 'Inception'], ['Rank', 'Genre']]  # Dataframe (rank and genre cols of rows "Split" and 'Inception')
df.loc['Split', 'Rank':'Rating']                   # Series (all cols from rank to rating for row "Split")

# Select on rows by integer index position
df.iloc[2]              # Series (3rd row)
df.iloc[[0, 2]]         # Dataframe (1st and 3rd row)
df.iloc[2:5]            # Sliced DataFrame (rows i to j excluded)
df.iloc[[0, 2], 2]      # Series (column 2 for rows 0 and 2)

# Filter rows on a condition on a column
# Note : Pandas filters use &/|/~ instead of and/or/not   
df[df['Rating'] > 8.5]
df[(df['Rating'] > 8.5) | (df['Rating'] < 3)]
df[(df['Rating'] > 8.5) & (df['Director'] == '<NAME>')]
df[~(df['Director'] == '<NAME>')]

# We can also pass the filter to the df.loc[] instead :
df.loc[df['Rating'] > 8.5]        # equivalent to : df[df['rating'] > 8.5] 

# Filter rows on a regex on a column
df.loc[df['Director'].str.contains('Nolan|Burton', regex=True)]
# -

# ## Add/Update/Delete columns and rows

# #### Add rows

# +
# Add a Series with df.append()
# Instead of naming the series, we can use the "ignore_index" flag to auto-assign an index
new_row = pd.Series({'Genre': 'Action', 'Director': '<NAME>', 'Rating': 4.2}, name='My Movie 1')
df = df.append(new_row)

# Add a Series with df.loc[]
new_row = pd.Series({'Genre': 'Thriller', 'Director': '<NAME>', 'Rating': 5.2})
df.loc['My Movie 2'] = new_row

# Concatenate 2 dataframes
df2 = pd.DataFrame({
    'Title'    : ['My Movie 3', 'My Movie 4'],
    'Genre'    : ['Love Story', 'Thriller'],
    'Director' : ['<NAME>', '<NAME>'],
    'Rating'   : [3.2, 7.2] 
})
df2.set_index('Title', inplace=True)
df = pd.concat([df, df2])

df.tail()
# -

# #### Update rows

# +
# Replace an entire row (similar to row creation)
df.loc['Split'] = pd.Series({
    'Rank': 3,
    'Year': 2015,
    'Director': '<NAME>',
    'Description': 'Good Movie',
    'Rating': 7.2
})

# Replace a column for a specific row
df.loc['Split', 'Genre'] = 'Action'
df.at['Split', 'Genre'] = 'Action'     # equivalent syntax

# Modify the value of several columns for a given row in the dataframe
df.loc['Split', ['Genre', 'Rating']] = ['Thriller', 7.3]

# Modify the value of a column for several rows in the dataframe using a row filter
bad_rating_filter = df['Rating'] < 7.0
df.loc[bad_rating_filter, 'Metascore'] = 0.0

df.head()
# -

# #### Delete rows

# +
# Drop a row by index
df = df.drop(index=['My Movie 1'])
df.drop('Nine Lives', inplace=True)        # equivalent syntax

# Drop rows by condition
my_filter = df.index.str.startswith('My ')
df.drop(index=df[my_filter].index, inplace=True)

df.tail(3)
# -

# #### Add columns

# +
# Add a column to the dataframe by combining some columns
df['ref'] = df['Year'].astype(str).str[:4] + '-' + df.index.str.replace(' ', '-')

# Add a new column to the df by applying a function to another column
def get_grade(rating):
    if rating > 8:
        return 'S'
    elif rating > 7:
        return 'A'
    elif rating > 6:
        return 'B'
    else:
        return 'C'
    
df['Grade'] = df['Rating'].apply(get_grade)

df.head(3)
# -

# #### Update columns

# +
# Rename some column names
df.rename(columns={
    "Runtime (Minutes)": "Runtime",
    "Revenue (Millions)": "Revenue"
}, inplace=True)

# Rename all column names
df.columns = [col.lower() for col in df.columns]

# We can also use the str attributes to use StringMethods on the columns index to rename the columns
df.columns = df.columns.str.replace(' ', '_')

# Update all cells in a single column using some StringMethods
df['genre'] = df['genre'].str.lower()

# Replace the genre column by applying the function to all its cells (similar to column creation)
df['genre'] = df['genre'].apply(lambda x: x.replace(',', '/'))  

# Replace column specific values with some given values
# "replace" only modifies the specified values, while "map" replaces all others by NaN
df['director'].map(    {'<NAME>': '<NAME>'})    # All other directors become NaN
df['director'].replace({'<NAME>': '<NAME>'})    # All other directors are unchanged

df.head()

# +
# Practical example of manipulation of a column on the "director" column :
#   - keep only the last 2 words of the director name
#   - drop rows with a director name with a single word
#   - create a column for the first name and a column for the last name of the director

# Find all rows with a director with a name in more than 2 words
name_filter = df['director'].str.split().apply(len) > 2
df[name_filter]

# Keep only the last 2 words of director names
df['director'] = df['director'].str.split().apply(lambda l: ' '.join(l[-2:]))

# Drop all rows with a director name with a single word (McG)
name_filter = df['director'].str.split(' ').apply(len) == 1
df.drop(df[name_filter].index, inplace=True)

# Make a column for first name and a column for last name
# split() returns one Series with a value equal to the list of tokens
# with the expand flag, it splits instead into multiple Series (one Serie per token)
df[['dir_first_name','dir_last_name']] = df['director'].str.split(' ', expand=True)

df.head()
# -

# #### Delete columns

# +
# Delete a column from a df and return a new df
df.drop(columns=['actors'])

# Delete a column from a df and returns it as a Series
df.pop('actors')
# -

# ## Dealing with Null data
#
# Some rows may have some missing values for some columns.  
# There are 2 ways to address it :
# - remove the lines with missing data (if just a small number)
# - fill the missing values with a default value (usually mean or median of the series)

# +
# dataframe of boolean saying if the cell is null (None or NaN)
null_matrix = df.isnull()
null_matrix = df.isna()          # synonym method

# number of null for each column
df.isnull().sum()

# +
# drop rows with at least one null value (in a new dataframe, use inplace param to update the original)
df2 = df.dropna()

# drop columns with at least one null value
df3 = df.dropna(axis='columns')

# drop rows with at least one null value in some specific columns
df4 = df.dropna(subset=['revenue'])

# +
# replace null values by a given value
revenue_median = df['revenue'].median()
df['revenue'].fillna(revenue_median, inplace=True)

score_mean = df['metascore'].mean()
df['metascore'].fillna(score_mean, inplace=True)
# -

# Append a dataframe to another
tmp = df.append(df)
tmp.shape

# Remove duplicate rows
tmp.drop_duplicates(inplace=True, keep="first")  # first / last / False (drop them all)
tmp.shape

# ## Casting column to a different type

# Cast the year into and int type
# If the data has rows with NaN values, we must drop them or cast to float instead (as NaN is a float)
df.dropna(subset=['year'], inplace=True)
df['year'] = df['year'].astype(int)
df.dtypes

# ## Data exploration
#
# ### Dataframe level statistics

# individual statistic indicators on all numeric columns
df.median()
df.max()
df.mean()

# statistics on each numeric column (count, mean, std, min/max...)
df.describe()

# Check the correlation between columns
# Correlation are in [-1, 1] :
#   ->  1 means if one goes up the other too
#   -> -1 means if one goes up the other goes down
#   ->  0 means no correlation
#   -> no real correlation if |corr| < 0.5
df.corr()

# +
# Sort the dataframe by index
df.sort_index()

# Sort the dataframe on some columns
df.sort_values(by=['rating', 'year'], ascending=[False, True], inplace=True)

df.head(3)
# -

# #### Column-level statistics

# +
# individual statistics indicators
df['rating'].mean()
df['rating'].median()
df['rating'].min()
df['rating'].max()
df['rating'].unique()                      # list of distinct values
df['rating'].count()                       # number of non-null values
df['rating'].value_counts()                # number of occurences of each unique value
df['rating'].value_counts(normalize=True)  # freq of occurences of each unique value

# describe() on a numerical column gives statistics (count/mean/min/max...)
df['rating'].describe()
# -

# describe() on a categorical column gives count/unique/top...
df['genre'].describe()

# +
# N largest values of a column
df['revenue'].nlargest(3)

# N rows with largest value for a given column
df.nlargest(3, 'revenue')
# -

# ### Sorting the dataframe

# +
# Sort the dataframe by index
df.sort_index()

# Sort the dataframe on some columns
df.sort_values(by=['rating', 'year'], ascending=[False, True], inplace=True)

# sorted Series for one column
df['director'].sort_values()

df.head(3)
# -

# Iterate on all rows of a dataframe
nolan_df = df.loc[df['director'].str.contains('Nolan')]
nolan_df = nolan_df.sort_values(by='year')
for index, row in nolan_df.iterrows():
    print(row['year'], index)

# ### Grouping and aggregations
#
# Pandas lets us group data by specific columns and analyze the data per group.  
# The `groupby` functionality will split data on some columns, apply a function on each group and combine the results.
#
# The result of `groupby` is a DataFrameGroupBy object, that contains a SeriesGroupBy for each column of the dataframe.

# +
# Group all movies per grade (S/A/B/C)
grade_grp = df.groupby(['grade'])     # DataFrameGroupBy object
grade_grp.get_group('S')              # DataFrame object filtering on S grade movies
grade_grp['genre']                    # SeriesGroupBy object

# The value counts on a SeriesGroupBy has 2 indices (the groupBy column and the value)
grade_grp['genre'].value_counts() 
# -

grade_grp['genre'].value_counts().loc['S']           # Series for group with grade S
grade_grp['genre'].value_counts().loc['S']['drama']  # nb of occurences of 'drama' in group of grade S

# We can apply a function to all individual series inside a SeriesGroupBy with the `apply()` method.  
# For example that can give us the number of rows in a dataframe verifying a given condition for each group.

# How many movies directed by <NAME> for each grade ?
grade_grp['director'].apply(lambda series: series.str.contains('<NAME>').sum())

# +
# Get one statistic indicator (mean/median/...) on a column for each groupby value
grade_grp['runtime'].median()

# get multiple statistic indicators on a column for each groupby value
grade_grp['runtime'].agg(['median', 'mean'])

# Get one statistic indicators on all numeric columns for each groupby value
grade_grp.median()
# -

# ## Using date/datetime columns
#
# The IMDB movies dataset does not have any time series column, so we will use here another dataset of ETH/USD transactions.

eth_df = pd.read_csv('data/ETH_1h.csv')
eth_df

# The dates are strings in a non-standard format, we need to convert it to a datetime column
eth_df['Date'] = pd.to_datetime(eth_df['Date'], format='%Y-%m-%d %I-%p')
eth_df.head()

# We can also read the dates directly from the csv file by providing a date parser
from datetime import datetime
date_parser = lambda x: datetime.strptime(x, '%Y-%m-%d %I-%p')
eth_df = pd.read_csv('data/ETH_1h.csv', parse_dates=['Date'], date_parser=date_parser)

# Just like the `str` property of a Pandas series gives access to string methods, the `dt` property lets us manipulate datetimes :

# Series created by applying a datetime method to a datetime column
eth_df['Date'].dt.day_name()

# Series of statistic indicators mean / median / min / max
eth_df['Date'].describe(datetime_is_numeric=True)

# Pandas datetimes are normal date, so we can get a delta between 2 datetimes
delta = eth_df['Date'].max() - eth_df['Date'].min()
delta

# We can also use datetimes in filters, for ex all rows in 2020
year_filter = eth_df['Date'].dt.year == 2018
eth_df.loc[year_filter].head()

# We can make the date column the index of the series
eth_df.set_index('Date', inplace=True)
eth_df.head()

# +
# When the index is a datetime, Pandas performs filtering when provided a partial date
eth_df.loc['2019-12']              # all rows in Dec 2019

# When the index is a datetime, Pandas allows slicing
eth_df.loc['2019-02':'2019-03']    # all rows in Feb or Mar 2019
# -

# We can resample some series indexed by a datetime to a different time scale
# For example we can get a Series with 1 value per day giving the max of the day
high_per_day = eth_df['High'].resample('D').max()
high_per_day

# We can also resample several columns by specifying the aggregation function for each column
resampled_df = eth_df.resample('W').agg({'High': 'max', 'Low': 'min', 'Volume': 'sum'})
resampled_df

# Plot the resampled series of high per day
high_per_day.plot()

# ## Plotting

# Scatter plot of one column VS another
# (use a ; at the end to hide the matplotlib trace)
df.plot(kind='scatter', x='rating', y='revenue', title='Revenue VS rating');

# Histogram for frequencies of one column
df['rating'].plot(kind='hist', title='Rating');

# similar plot using seaborn
import seaborn as sns
import numpy as np
sns.histplot(data=df, x='rating', bins=np.arange(0, 10, 0.5))

# box plot to show a column's distribution (similar to the result of "describe")
df['rating'].plot(kind='box');

# box plot of a column split by another column
# now the plot is done at the dataframe level
df.boxplot(column='revenue', by='grade')

# ## Study of a sample dataset from StackOverflow survey

# explore dataset from stackoverflow
survey_df = pd.read_csv('data/developer_survey_2019/survey_results_public.csv', index_col='Respondent')
schema_df = pd.read_csv('data/developer_survey_2019/survey_results_schema.csv', index_col='Column')
schema_df.sort_index(inplace=True)

# Questions asked in the survey
schema_df.head(3)

# Responses to the survey
survey_df.head(3)

# Several infos on the data
survey_df.info()
survey_df.describe()
survey_df.corr()
survey_df.shape

# +
# Set the max number of rows/columns displayed
pd.set_option('display.max_columns', 85)
pd.set_option('display.max_rows', 85)

pd.get_option('display.max_columns')
# -

# Simple analysis of a Yes/No column
survey_df['Hobbyist']                                  # Vector of Yes/No response
survey_df['Hobbyist'].value_counts()                   # Series of count for each value
survey_df['Hobbyist'].value_counts().plot(kind='bar')  # Bar plot for the count of Yes/No

# Series of the responses of the first respondent (first row)
survey_df.iloc[0]

# +
# People with salary above a threshold
high_salary_filter = survey_df['ConvertedComp'] > 100000
survey_df.loc[high_salary_filter, ['Country', 'LanguageWorkedWith', 'ConvertedComp']]

# People from a list of countries
countries = ['Japan', 'France']
country_filter = survey_df['Country'].isin(countries)
survey_df.loc[country_filter, ['Country', 'LanguageWorkedWith', 'ConvertedComp']]

# People knowing Python
# NOTE : We use string methods in the filter, with a default to FALSE for N/A values
python_filter = survey_df['LanguageWorkedWith'].str.lower().str.contains('python', na=False)
survey_df.loc[python_filter, ['Country', 'LanguageWorkedWith', 'ConvertedComp']]
# -

# Modify the value of a column for several rows in the dataframe
us_filter = survey_df['Country'] == 'United States'
survey_df.loc[us_filter, 'Country'] = 'USA'

not_null_filter = survey_df['ConvertedComp'].notnull()
survey_df.loc[not_null_filter].describe()

# What social medias are most popular ?
survey_df['SocialMedia'].value_counts(normalize=True)

# ### Social media analysis grouped by country

# +
country_grp = survey_df.groupby(by='Country')

# Value count of a column for a given group of the groupby
country_grp['SocialMedia'].get_group('France').value_counts()

# Value count of a column for each country
country_grp['SocialMedia'].value_counts()
# -

# Median of salary per country
country_grp['ConvertedComp'].median().sort_values(ascending=False)

# For each country, how many respondents know Python ?
country_grp['LanguageWorkedWith'].apply(lambda series: series.str.lower().str.contains('python').sum())

# +
# Create a new DF with number of respondents and how many of them know Python per country

# Create the column Series for total nb of respondents and nb of respondents who know Python
respondent_nb  = survey_df['Country'].value_counts()
know_python_nb = country_grp['LanguageWorkedWith'].apply(lambda x: x.str.contains('Python').sum())

# Make a new df from it and rename the columns
new_df = pd.concat([respondent_nb, know_python_nb], axis='columns', sort=False)
new_df.rename(columns={'Country': 'Respondents', 'LanguageWorkedWith': 'KnowPython'}, inplace=True)

# Create a new column calculated from the 2 others
new_df['KnowPythonPct'] = 100 * new_df['KnowPython'] / new_df['Respondents']
new_df

# +
# Find the average nb of years of experience of respondents

# Some values are string, we need to convert them to float before we cast to float
survey_df['YearsCode'].unique()    # to see the different values
survey_df['YearsCode'].replace({'Less than 1 year': 0, 'More than 50 years': 51}, inplace=True)

# Convert the column to float
survey_df['YearsCode'] = survey_df['YearsCode'].astype(float)

survey_df['YearsCode'].mean()

StarcoderdataJupyterScriptsDedupFiltered