<jupyter_start><jupyter_text>Introduction  Use all the files to have a final datasets to have the following columns      questid    gender    age (age of applicant)    communeName (use data prep in commune file)    application_date (created at in quest file)    enroll_date (created_at in enroll file)    is_enroll (Yes/No) (use data prep in enroll file)    Education Level    Communication channels(hear_AA1)    Bootcamp Insterest (after_AA)    Payement Date (use ord and transaction files)    Payed (Yes/No)    list Technologies as columns based (use get_dummies)    list  Study domains (use get_dummies)    Job is formal    Have computer at home    Have internet at home     Project Setup   1. Install all requirements for the project<jupyter_code># !pip install numpy
# !pip install pandas
# !pip install matplotlib
# !pip install seaborn
!pip install dateparser<jupyter_output>Requirement already satisfied: dateparser in /Users/kesnel/opt/anaconda3/lib/python3.8/site-packages (1.0.0)
Requirement already satisfied: tzlocal in /Users/kesnel/opt/anaconda3/lib/python3.8/site-packages (from dateparser) (2.1)
Requirement already satisfied: python-dateutil in /Users/kesnel/opt/anaconda3/lib/python3.8/site-packages (from dateparser) (2.8.1)
Requirement already satisfied: regex!=2019.02.19 in /Users/kesnel/opt/anaconda3/lib/python3.8/site-packages (from dateparser) (2020.10.15)
Requirement already satisfied: pytz in /Users/kesnel/opt/anaconda3/lib/python3.8/site-packages (from dateparser) (2020.1)
Requirement already satisfied: six>=1.5 in /Users/kesnel/opt/anaconda3/lib/python3.8/site-packages (from python-dateutil->dateparser) (1.15.0)<jupyter_text>2. Importing Dependencies<jupyter_code>import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

import locale
import dateparser

from dateutil.parser import parse
locale.setlocale(locale.LC_TIME, 'fr_FR.UTF-8') 

import sys
from datetime import datetime as dt<jupyter_output><empty_output><jupyter_text>3. Define some usefull variables<jupyter_code>DOCS_PATH = 'docs/'
DATA_PATH = 'data/'<jupyter_output><empty_output><jupyter_text>4. Importing Datasets<jupyter_code>commune_df = pd.read_excel(DATA_PATH + "commune.xlsx")
enroll_df = pd.read_csv(DATA_PATH + "enroll.csv")
industry_df = pd.read_csv(DATA_PATH + "industry.csv")
ord_df = pd.read_csv(DATA_PATH + "ord.csv")
quest_df = pd.read_csv(DATA_PATH + "quest.csv")
studyd_df = pd.read_csv(DATA_PATH + "study_domain.csv")
tech_df = pd.read_csv(DATA_PATH + "technology.csv")
transaction_df = pd.read_csv(DATA_PATH + "transaction.csv")<jupyter_output><empty_output><jupyter_text>Data Processing   1. Transaction Dataset > Data Overview<jupyter_code># Displaying first three entries
transaction_df.head(3)
# Check the shape of the DataFrame
row, col = transaction_df.shape
print("This datasset have",row,"rows","and",col,'columns')
# Data info
transaction_df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 60 entries, 0 to 59
Data columns (total 6 columns):
 #   Column          Non-Null Count  Dtype 
---  ------          --------------  ----- 
 0   Unnamed: 0      60 non-null     int64 
 1   created_at      60 non-null     object
 2   modified_at     60 non-null     object
 3   user_id         60 non-null     int64 
 4   course_id       60 non-null     int64 
 5   transaction_id  60 non-null     int64 
dtypes: int64(4), object(2)
memory usage: 2.9+ KB<jupyter_text>> Data Overview<jupyter_code># Make a new dataframe
transaction_df.columns
# Create a new Dataframe 
new_transaction_df = transaction_df[['created_at','user_id']]
# Rename 'Created' to 'Payment Date'
final_transaction_df = new_transaction_df.rename(columns={'created_at':'Payment_Date'})
# Display our new dataframe
final_transaction_df.head(2)<jupyter_output><empty_output><jupyter_text>2. Ord Dataset > Data Overview<jupyter_code># Displaying first entry
ord_df.head(1)
# Shape of the DataFrame
row, col = ord_df.shape
print("This datasset have",row,"rows","and",col,'columns')
# Data info
ord_df.info()
# Describe the dataset
ord_df.describe()<jupyter_output><empty_output><jupyter_text>> Data preparation<jupyter_code># See how many columns we are going to use.
ord_df.columns
# Create a new order dataframe
new_ord_df = ord_df[['created_at','user_id']]
# Rename Created at to Payment date
final_ord_df = new_ord_df.rename(columns={'created_at':'Payment_Date'})
# Concat both of the Payment DataFrame
payment_df = pd.concat([final_ord_df,final_transaction_df],ignore_index=True, axis=0)
# Preview our new dataframe
payment_df.head(3)
payment_df.shape<jupyter_output><empty_output><jupyter_text>3. Enroll Dataset > Data Overview<jupyter_code># Displaying first entry of Enroll dataset
enroll_df.head(1)
# Check the observations and variables of the DataFrame
row, col = enroll_df.shape
print("This datasset have",row,"rows","and",col,'columns')
# Data info
enroll_df.info()
# Describe the dataset
enroll_df.describe()
# How many missing values we have
print("This dataframe have", enroll_df.isna().sum().sum(), "missing values")<jupyter_output>This dataframe have 366 missing values<jupyter_text>> Data Preparation<jupyter_code># Create a new dataframe
new_enroll = enroll_df[['created_at','user_id','quest_id']]
# Rename 'created_at' to 'Enroll_Date'
new_enroll_df = new_enroll.rename(columns={'created_at':'Enroll_Date'})
new_enroll_df.head(3)
final_enroll = pd.merge(new_enroll_df,payment_df, on="user_id", how="outer")
final_enroll.head(2)
final_enroll.columns
# now let's delete all useless columns to have a clean dataframe with all Payment info
drop_fenroll = ['user_id']
final_enroll_df = final_enroll.drop(drop_fenroll, axis=1)
# Display our Final Enroll dataset
final_enroll_df.head(5)
final_enroll_df.shape<jupyter_output><empty_output><jupyter_text>4. Study Domain Dataset > Data Overview<jupyter_code># Displaying latest entries of the dataset
studyd_df.tail()
# Check the shape of the DataFrame
row, col = studyd_df.shape
print("This datasset have",row,"rows","and",col,'columns')
# Data info
studyd_df.info()
# Make sure our columns don't have duplicate values
print("This dataframe have", studyd_df.duplicated().sum(), "Duplicated value")
# Check if we have missing values in our columns
print("This dataframe have", studyd_df.isna().sum(), "missing values")<jupyter_output>This dataframe have Unnamed: 0    0
key           0
values        0
quest_id      0
dtype: int64 missing values<jupyter_text>**Conclusion:** just checking that there are no missing and duplicated values in the study domain dataset... > Data Preparation<jupyter_code>studyd_drop = ['Unnamed: 0']
dropped_studyd_df = studyd_df.drop(studyd_drop, axis=1)
# Find all duplicated values in the
dropped_studyd_df.head(2)
# Grouped all our study domain
dropped_studyd_df['Study_Domains'] = dropped_studyd_df.groupby('quest_id')['values'].transform(lambda x: ', '.join(x))
# Display the dataframe
dropped_studyd_df.head(4)
dropped_studyd_df.shape
# Drop all useless columns
study_domain_df = dropped_studyd_df.drop(columns=['key','values'])
# Let's check for some duplicated values
study_domain_df.duplicated().sum()
# Now let's drop all duplicate values in the dataset
final_studyd_df = study_domain_df.drop_duplicates()
# Display our fresh datadrame
final_studyd_df.head(5)
final_studyd_df.shape<jupyter_output><empty_output><jupyter_text>**Conclusion:** Now we have all our study domains values grouped together...  5. Tech Dataset > Data Overview<jupyter_code># Displaying first entries
tech_df.head()
# Check the dimensions of the DataFrame
row, col = tech_df.shape
print("This datasset have",row,"rows","and",col,'columns')
# Data info
tech_df.info()
# Looking for duplicated values
print("This dataframe have", tech_df.duplicated().sum(), "Duplicated value")
# Check if we have missing values in our columns
print("This dataframe have", tech_df.isna().sum(), "missing values")<jupyter_output>This dataframe have Unnamed: 0    0
key           0
values        0
quest_id      0
dtype: int64 missing values<jupyter_text>> Data Preparation<jupyter_code>tech_df.columns
# Drop column we will no longer use
tech_df_drop = ['Unnamed: 0']
dropped_tech_df = tech_df.drop(tech_df_drop, axis=1)
# Grouped all our study domain
dropped_tech_df['List_Technologies'] = dropped_tech_df.groupby('quest_id')['values'].transform(lambda x: ', '.join(x))
# Display our grouped dataframe
dropped_tech_df.head(5)
# Drop all useless columns now
final_tech = dropped_tech_df.drop(columns=['key','values'])
# Display dataframe
final_tech.head(5)
final_tech.shape
# Let's check for some duplicated values
final_tech.duplicated().sum()
# Remove all duplicates dummies values
final_tech_df = final_tech.drop_duplicates()
# Final Tech dataset
final_tech_df.head(5)
final_tech_df.shape
# Let's merge 'Technologies' with 'Study Domain'
studyd_tech = pd.merge(final_tech_df,final_studyd_df,on="quest_id",how="outer")
studyd_tech.head(3)<jupyter_output><empty_output><jupyter_text>**Conclusion:** Now we have all our list of technologies values grouped together...   6. Commune Dataset > Data Overview<jupyter_code># Displaying first two entries of the Commune dataset using DataFrame.head()
commune_df.head(2)
# Check the shape of the DataFrame
row, col = commune_df.shape
print("This datasset have",row,"rows","and",col,'columns')
# Data info
commune_df.info()
# Describe the dataset
commune_df.describe()<jupyter_output><empty_output><jupyter_text>> Data Preparation<jupyter_code># Drop all columns we will not use in this dataset
commune_to_drop = ['Commune_FR','Departement','ADM1_PCODE']
new_commune_df = commune_df.drop(commune_to_drop, axis = 1)
# Displaying our clean dataset
new_commune_df.head(3)<jupyter_output><empty_output><jupyter_text>7. Questions Dataset > Data Overview<jupyter_code># Displaying first entries
quest_df.head(2)
# Check the dimensions of the DataFrame
row, col = quest_df.shape
print("This datasset have",row,"rows","and",col,'columns')
# Data info
quest_df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 250 entries, 0 to 249
Data columns (total 17 columns):
 #   Column              Non-Null Count  Dtype 
---  ------              --------------  ----- 
 0   Unnamed: 0          250 non-null    int64 
 1   gender              250 non-null    object
 2   dob                 244 non-null    object
 3   commune             250 non-null    object
 4   created_at          250 non-null    object
 5   modified_at         250 non-null    object
 6   department          249 non-null    object
 7   education_level     250 non-null    object
 8   university          250 non-null    object
 9   study_domain        250 non-null    object
 10  current_employed    250 non-null    object
 11  formal_sector_job   250 non-null    object
 12  have_computer_home  250 non-null    object
 13  internet_at_home    250 non-null    object
 14  hear_AA_1           250 non-null    object
 15  after_AA            250 non-null    object
 16  quest_id            250 no[...]<jupyter_text>> Data Preparation<jupyter_code>quest_df.columns
# Create a new dataframe with selected columns
quest_to_drop = ['Unnamed: 0','modified_at','department','university','current_employed']
dropped_quest_df = quest_df.drop(quest_to_drop, axis=1)
# Display our new columns
dropped_quest_df.columns
# Change all commune ID to uppercase before merging
dropped_quest_df.commune = dropped_quest_df['commune'].str.upper()
# Change the commune to Commune_ID to merge it with the commune dataset
rename_quest = dropped_quest_df.rename(columns={'commune':'Commune_Id'})
# Now let's merge our commune dataset with the Question's dataset
merged_quest_commune = pd.merge(new_commune_df,rename_quest, on="Commune_Id", how="inner")
# Drop the Commune ID, we will no longer use it in the project
quest_to_drop = ['Commune_Id']
final_quest = merged_quest_commune.drop(quest_to_drop, axis=1)
# Change also some columns name...
final_quest_df = final_quest.rename(columns={'Commune_en':'Commune_Name',
                                             'hear_AA_1':'Communication_Channels',
                                             'after_AA':'Bootcamp_Interest',
                                          'created_at':'Application_Date'})
# Display the dataset
final_quest_df.head(2)
final_quest_df.shape<jupyter_output><empty_output><jupyter_text>Combine our Enroll dataset with the question dataset<jupyter_code># Let's merge our Enroll dataset with the question dataset
quest_enroll = pd.merge(final_quest_df,final_enroll_df,on="quest_id",how="outer")
# Display our new 'Enroll' and 'Quest' dataframe
quest_enroll.head(2)
quest_enroll.shape<jupyter_output><empty_output><jupyter_text>Create our final dataset<jupyter_code># Reviewing our merged Quest and Enroll columns before merging it with the StudyDomain_Technlogy column, to have the final dataframe
quest_enroll.columns
# merged 'Study Domain' and 'Technologies' columns
studyd_tech.columns
# Merge all our dataframe to have a final dataset
final = pd.merge(quest_enroll,studyd_tech,on="quest_id",how="outer")
# Display our final Dataframe
final.head(1)<jupyter_output><empty_output><jupyter_text>A - Now we are going to make our new column ['Age']   but, before doing this we are going to:      Convert our application_date column to the same format (DD/MM/YYYY) as the dob column      Convert our dob column to datetime.<jupyter_code># 1. Convert the final['Application_Date'] to Datetime Type
final.Application_Date = pd.to_datetime(final.Application_Date)
final[['Application_Date']].info()
# 2.1 Convert the final['dob'] to Datetime Type
final.dob = final.dob.apply(lambda x: dateparser.parse(str(x)))
final[['dob']].info()
# Remove the timezone before the operation
app_date = final.Application_Date.dt.date
dob_date = final.dob.dt.date

# create a new Age dataframe ~ Calculate the age
final["Age"] = (app_date - dob_date)
final["Age"] = final.Age // np.timedelta64(1,"Y") 
# Remove the age as float to int
final.Age = final["Age"].astype('Int64')
# Display the Age columns
final[['Age']].head(3)<jupyter_output><empty_output><jupyter_text>B - Now we are going to create our new column ['is_enrolled'] and ['Payed']<jupyter_code>'''
1. Specify if the student enrolled to the Bootcamp or not. 

How we are going to do this:
- Based on the final dataframe, if the 'Enrolled_date' have NaN values, 
  our new 'Is_Enrolled' column will automatically display NO as value; and YES if the 'Enrolled_date' is not NaN
'''

final['is_enrolled'] = 'No'
final.loc[~final.Enroll_Date.isna(),["is_enrolled"]] = "Yes"
'''
2. Specify if the student paid the Bootcamp or not. 

How we are going to do this:
- Based on the final dataframe, if the 'Payment_Date' have NaN values, 
  our new 'Paid' column will automatically display NO as value; and YES if the 'Payment_Date' is not NaN
'''

final['Paid'] = 'No'
final.loc[~final.Payment_Date.isna(),["Paid"]] = "Yes"
# Display the dataset to see if it's working
final<jupyter_output><empty_output><jupyter_text>Final Dataframe<jupyter_code># Reviewing our final dataset columns
final.columns
# Reordering our columns to have a new dataframe
final_df = final[['quest_id',
                  'gender',
                  'Age',
                  'Commune_Name',
                  'Application_Date',
                  'Enroll_Date',
                  'is_enrolled',
                  'education_level',
                  'Communication_Channels',
                  'Bootcamp_Interest',
                  'Payment_Date',
                  'Paid',
                  'List_Technologies',
                  'Study_Domains',
                  'formal_sector_job',
                  'have_computer_home',
                  'internet_at_home']]<jupyter_output><empty_output><jupyter_text>Final Dataset<jupyter_code># Displaying first entry of Enroll dataset
final_df.head(1)
# Check the shape of the DataFrame
row, col = final_df.shape
print("This datasset have",row,"rows","and",col,'columns')
# Data info
final_df.info()
# Describe the dataset
final_df.describe()
# Make sure our columns don't have duplicate values
print("This dataframe have", final_df.duplicated().sum(), "Duplicated value")
# Check if we have missing values in our columns
print("This dataframe have", final_df.isna().sum(), "missing values")
# How many missing values we have
print("This dataframe have", final_df.isna().sum().sum(), "missing values")<jupyter_output>This dataframe have 991 missing values<jupyter_text>> Data Cleaning<jupyter_code># Dealing with our missing values on each columns
# clean_final_df = final_df.fillna(0)<jupyter_output><empty_output><jupyter_text>**Conclusion:** All our empty columns was filled with 0. Now, let's export our final dataset as csv file<jupyter_code># Exporting CSV file
final_df.to_csv(DATA_PATH + 'final/final-data.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10478616277692217, 'fraction_numerical': 0.01540101883663073, 'mean_word_length': 4.65405224380442, 'pattern_counts': {'":': 0, '<': 100, '<?xml version=': 0, '>': 117, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '26', 'dir': 'jupyter-structured-clean-dedup', 'id': '657602', 'license': 'MIT', 'n_tokens_mistral': 5624, 'n_tokens_neox': 5362, 'n_words': 1500, 'path': 'data-preparation.ipynb', 'repo_name': 'kesnel/Probability-and-Statistics', 'size': '16882'}