<jupyter_start><jupyter_text>First HandsOn Session 17-08-2021  SandBox: Code as a groupThis notebook is a sandbox to code together as a group.Use it if you have not saved your notebook or if you have not the latest version  First steps<jupyter_code>import pandas as pd
pwd # Print current folder
# Install a library required to read excel files
# This step was included in this cell because we had, originally, no library to read excel files.
# In such cases, you will get a recommendation from the engine of which library to install

# Use `pip install LIBRARY` to install it directly from here
# Alternatively, you could also install it from the shell
!pip install openpyxl<jupyter_output>Requirement already satisfied: openpyxl in d:\anaconda\lib\site-packages (3.0.3)
Requirement already satisfied: jdcal in d:\anaconda\lib\site-packages (from openpyxl) (1.4.1)
Requirement already satisfied: et-xmlfile in d:\anaconda\lib\site-packages (from openpyxl) (1.0.1)<jupyter_text>Reading data<jupyter_code># Read the excel file located in the subfolder data
# Store the result in an object called df
df = pd.read_excel('data/NL_KPIs_since_2018.xlsx', #Filename 
                   header=7 #Header row in the file
                  )<jupyter_output><empty_output><jupyter_text>Accessing data<jupyter_code># Print the first n rows
df.head(2) 
# Print the last n rows}
#df.tail(n)<jupyter_output><empty_output><jupyter_text>Let's say you want to drop the column 'unnamed: 2' from your dataset<jupyter_code>df.columns<jupyter_output><empty_output><jupyter_text>We can have a look at the first row, to see the column name together with values<jupyter_code>df.iloc[1]<jupyter_output><empty_output><jupyter_text>CAUTION: In python, the first localizer is `0`!  By selecting `df.iloc[1]` you are basically looking at the second row of your dataset You can also print the data types of each column<jupyter_code>df.dtypes<jupyter_output><empty_output><jupyter_text>Here for instance, you can see how the Time columns (e.g. `Send Start Time`) are treated as `object` (=`string`). We should probably at some point, transform that into datetimes format.  Accessing data<jupyter_code># Say we want to drop a column from the dataframe
my_col_to_drop = None # Set column to drop
df.drop(columns={my_col_to_drop}) #Drop column
# Now let's call df again
df<jupyter_output><empty_output><jupyter_text>We see that df still have the NaN column in there! This is because you dropped the column but did not store the output of that operation somewhere.While it might seem counterintuitive at first, this makes sure that your operations are not affecting the data directly. In other words, you can test your code to get the output you want, and then only save it.  There are two ways one could save the results of an operation, such as dropping a column:<jupyter_code># Either overwrite the object with the code output
df_modif = df.drop(columns={my_col_to_drop})<jupyter_output><empty_output><jupyter_text>OR<jupyter_code># use Inplace=True to modify in place
df.drop(columns={my_col_to_drop}, inplace=True)<jupyter_output><empty_output><jupyter_text>CAUTION: * Using the inplace parameter means that you will be modifying the source object. * Here, df has been modified and the column has been dropped directly in there. * If you want to keep the past data, it is better to store each step in a separate objet, depending on the size of your data of course ;)  Analyzing data Good. Now we imported the data and had very quick glance at it using basic functions.Say you have been given this dataset without other instructions than exploring the features and running an Exploratory Data Analysis, to explain management what is in there. Let's go. * First data cleaning<jupyter_code># Look for useless columns, like the one we dropped earlier
# Name could be an indicator?
# Print columns names here

# But we should make sure no data is lost before removing the columns right?
# Look for NaNs in the data set using the function .isna()

# Or we could directly use the .dropna() function to remove these columns<jupyter_output><empty_output><jupyter_text>* First feature: Opening Rate Opening rate is already provided as a column. But let's verify the accuracy of that data point by calculating the opening rate given the raw data:<jupyter_code># Assign a new column with calculated open rate
df = df.assign(open_rate_calc = df['Unique Emails Opened'] / df['Emails Sent'])<jupyter_output><empty_output><jupyter_text>We just add a column with the calculated `open rate`.Now, we can quickly plot it to look at the distribution<jupyter_code># Sanity check to verify the distribution of the calculated open rate
(df
.open_rate_calc
.plot(kind='hist', bins=100)
)<jupyter_output><empty_output><jupyter_text>* Note how each function is in a new row, while the overall statement is in (). * When possible, use this notation to make it easier to read your code Since we wanted to observe the accuracy of the provided open rate, let's plot the difference between the `provided open rate` and the `calculated open rate`:<jupyter_code># Visualize the degree of difference between the provided & calculated open rate
(df['Open Rate'] - df['open_rate_calc']).plot(kind='hist',bins=100)<jupyter_output><empty_output><jupyter_text>Now you reported to management that you can some differences in `open_rate`...very small, but still. And they ask you to remove these data points because they are a bug in the system that should not eneter your analysis. Let's remove them using `indexing`:  * Indexing is based on using a boolean array (True or False) to filter out the values * it is a fast and easy to implement method to select data based on criteria & threshold<jupyter_code># Let's create an index of the open rates that differ by > 0.01
mask = (df['Open Rate'] - df['open_rate_calc']) >= 0.01
# How many True values fo I have?
mask.sum()
# Let's not overwrite the data, and instead, let's store this first clean version in a separate object
df_clean = df.loc[~mask]<jupyter_output><empty_output><jupyter_text>Second HandsOn Session 31-08-2021<jupyter_code>df
df = df.assign(nsctrid_digits = (df
.Name # Select Name column
.str  # Turn to string for processing
.split(expand=True) # Expand each separate word in a column
.apply(lambda d: d
       .str                               # Turn so string for processing
       .extract('(\d+)', expand=False)    # Select only digits from the columns
       .dropna()                          # Drop NaN because otherwise error when turnin to integer
       .astype('int64')                   # Turn to integer, to get max()
      )
.max(axis=1)                              # Get maximum per row (axis = 1)
                           )
         )
country_dict = {'RS': 'Serbia', 
                'SI': 'Slovenia', 
                'HR': 'Croatia', 
                'RO': 'Romania',
                'HU': 'Hungary', 
                'SK': 'Slovakia', 
                'CZ': 'Czech', 
                'PL': 'Poland',
                'DE': 'Germany', 
                'AT': 'Austria', 
                'RU': 'Russia',
                'CHD': 'Switzerland', 
                'CHF': 'Switzerland'
               }
pat = r'({})'.format('|'.join(country_dict.keys()))
    extracted = pd.Series(dataf[col_name]).str.extract(pat, expand=False).reset_index(drop=True)#.dropna()
    dataf['country_code'] = extracted
(dataf.assign(country=dataf['country_code'].map(country_dict)))
# This is copy paste from Session 2
# Assign timing features
df_newsletter['send_start_time'] = pd.to_datetime(df_newsletter['send_start_time'])
df_newsletter = (df_newsletter
.assign(date = df_newsletter['send_start_time'].dt.date)
.assign(week = df_newsletter['send_start_time'].dt.week)
.assign(month = df_newsletter['send_start_time'].dt.month_name())
.assign(hour = df_newsletter['send_start_time'].dt.hour)
)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08600656731497854, 'fraction_numerical': 0.006567314978529932, 'mean_word_length': 4.089331619537275, 'pattern_counts': {'":': 0, '<': 60, '<?xml version=': 0, '>': 62, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 1}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '632344', 'license': 'MIT', 'n_tokens_mistral': 2380, 'n_tokens_neox': 2267, 'n_words': 935, 'path': 'notebooks/lecture/sandbox_notebook.ipynb', 'repo_name': 'juls-dotcom/python_workshop', 'size': '7918'}