<jupyter_start><jupyter_text>Data cleaning GoalIn this notebook, we will be taking in raw.csv and cleaning/parsing its different columns. The notebook contains the transformations below in order:1. Read in the data2. Removing unused columns for this analysis3. Removing rows with certain null columns4. Cleaning of columns    * ad_age    * ad_impressions    * ad_clicks    * ad_creation_date    * ad_end_date    * ad_targeting_interests    * ad_targeting_people_who_match5. Writing to file6. Summary of lost rowsWe first import 3 packages which will be useful for our data cleaning:* pandas for handling csv as a table* numpy to handle mulitdimensional array operations* re to handle regular expression parsing<jupyter_code>import pandas as pd
import numpy as np
import re

# We read in the data
ads_df = pd.read_csv('../raw_data/raw.csv')

# Output first 2 rows
ads_df.head(2)<jupyter_output><empty_output><jupyter_text>Removing unused columnsOur first step will be to remove columns we will not be using for this analysis.| Column name | Reason for removal||-------------| ------------------|| Unnamed | Index column added by accident in the data production step. || ad_id | We will be using the file_name column as identifier. || ad_text | Although interesting its analysis is outside the scope of this project | | ad_landing_page | This will not be useful in answering our research questions. || ad_targeting_location | We will not be studying the impact of location for the ads || ad_targeting_custom_audience | This field doesn't contain information not already in the ad_targeting_interests column || ad_targeting_language | This field is almost always US English and is not present on most of the dataset || ad_targeting_placements | We will not be studying the impact of location on the page for the ads |<jupyter_code># Columns we will not be using
columns_to_remove = ['Unnamed: 0', 'ad_id', 'ad_text', 'ad_landing_page', 'ad_targeting_location', 'ad_targeting_custom_audience', 'ad_targeting_excluded_connections', 'ad_targeting_language', 'ad_targeting_placements']
ads_df = ads_df.drop(columns=columns_to_remove)

ads_df.head(2)<jupyter_output><empty_output><jupyter_text>Removing rows with null columnsWe will be removing rows with null for: ad_creation_date, ad_spend, ad_targeting_age, ad_impressions and ad_clicks. Our first step will be to create a dictionary which can keep track of the number of rows remaining after a given operation. We will be using this dictionary when summarizing the cleaning of the dataset and its repercussions on our analysis. We then create a function which remove the null values for a column name and adds the row count after removal to our summary dictionary.<jupyter_code># Dictionary to keep track of row removals
cleaning_summary_format = {'before_cleaning_count': len(ads_df)}

# Function to remove null rows for a given column
def remove_nulls(ads_df, column_name):
    # np.where returns a tuple, we want the first member (the indexes of rows)
    null_indexes = np.where(pd.isnull(ads_df[column_name]))[0]
    # We drop the list of indexes
    ads_df = ads_df.drop(null_indexes)
    # We add the entry to our summary
    cleaning_summary_format['null_' + column_name + '_count'] = len(ads_df)
    return ads_df

# We remove nulls for given columns
ads_df = remove_nulls(ads_df, 'ad_creation_date')
ads_df = remove_nulls(ads_df, 'ad_spend')
ads_df = remove_nulls(ads_df, 'ad_targeting_age')
ads_df = remove_nulls(ads_df, 'ad_impressions')
ads_df = remove_nulls(ads_df, 'ad_clicks')
print('''Before cleaning our dataset had {before_cleaning_count} columns.
After removing rows with null creation dates: {null_ad_creation_date_count} columns.
After removing rows with null ad spending: {null_ad_spend_count} columns.
After removing rows with null ad targeting age: {null_ad_targeting_age_count} columns.
After removing rows with null ad impressions: {null_ad_impressions_count} columns.
After removing rows with null ad clicks: {null_ad_clicks_count} columns.'''.format(**cleaning_summary_format))<jupyter_output>Before cleaning our dataset had 3517 columns.
After removing rows with null creation dates: 3497 columns.
After removing rows with null ad spending: 3497 columns.
After removing rows with null ad targeting age: 3497 columns.
After removing rows with null ad impressions: 3497 columns.
After removing rows with null ad clicks: 3497 columns.<jupyter_text>Cleaning ad_ageFirst we look at the values for the field and whether we will be able to leverage them in our analysis.<jupyter_code>ads_df.ad_targeting_age.value_counts().index<jupyter_output><empty_output><jupyter_text>The initial parsing for this field was not perfect... Let's simplify this bucketing by removing gender information. To do so we crop the string at 8 characters.<jupyter_code># Crop the ad_targeting_age to 8 characters
ads_df.ad_targeting_age = ads_df.ad_targeting_age.apply(lambda s: s if len(s)<=8 else s[0:8])

# Count rows for the different values
count_table = ads_df.ad_targeting_age.value_counts().to_frame()

# Rename the column for clarity
count_table.columns = ['Ad count']

# Output top 5
count_table.head(5)<jupyter_output><empty_output><jupyter_text>As per this table, almost all ads targeted voting age facebook users (18+). Bucketing the ads by age groups will not result in significant/interesting analysis. We drop the column.<jupyter_code>ads_df = ads_df.drop(columns=['ad_targeting_age'])<jupyter_output><empty_output><jupyter_text>Cleaning ad_impressions and ad_clicksBoth these columns are numerical and do not contain None and or NaN values. [The Oxford study](https://comprop.oii.ox.ac.uk/wp-content/uploads/sites/93/2018/12/IRA-Report.pdf), mention that ads without impressions or clicks where unlikely to have been shown to Facebook users.* We will first be parsing these fields that sometimes use . or , to separate thousands.* We will then remove 0 values.The studies mention that this removed quite a few entries.<jupyter_code># Parsing of string to integer
def format_string_to_integer(string):
    # Removing dots and commas and semicolons
    s = string.replace(',', '').replace('.', '').replace(';', '')
    
    # Removing typos betwee o, O (lower, upper letter o) and 0 (zero digit)
    s = s.replace('o', '0').replace('O', '0')
    
    # Accidental whitespace
    s = s.replace(' ', '')
    
    # Coerce string to integer
    return int(s)

# Remvoe rows with 0s and adds count after removal to summary
def remove_zeros(ads_df, column_name):
    ads_df = ads_df[ads_df[column_name] != 0]
    cleaning_summary_format['zeros_' + column_name + '_count'] = len(ads_df)
    return ads_df
# How many columns do we have before removing zeros
cleaning_summary_format['before_zeros_count'] = len(ads_df)

# Conversion to integers
ads_df['ad_clicks'] = ads_df['ad_clicks'].apply(format_string_to_integer)
ads_df['ad_impressions'] = ads_df['ad_impressions'].apply(format_string_to_integer)

# Removing zeros values
ads_df = remove_zeros(ads_df, 'ad_impressions')
ads_df = remove_zeros(ads_df, 'ad_clicks')

# Reporting
print('''Before removing 0 ad_impressions or ad_clicks our dataset had {before_zeros_count} columns.
After removing rows with 0 ad impressions: {zeros_ad_impressions_count} columns.
After removing rows with 0 ad clicks: {zeros_ad_clicks_count} columns.'''.format(**cleaning_summary_format))<jupyter_output>Before removing 0 ad_impressions or ad_clicks our dataset had 3497 columns.
After removing rows with 0 ad impressions: 2588 columns.
After removing rows with 0 ad clicks: 2450 columns.<jupyter_text>Parsing creation date and end dateCreation date and end date are written in a complex format: 04/13/16 07:48:33 AM PDT. Our analysis only requires the date. In this section, we will extract the first 8 characters mm/dd/yy and convert them to a datetime object. We take a look at the entries:<jupyter_code>ads_df['ad_creation_date']<jupyter_output><empty_output><jupyter_text>We find that sometimes the first few characters contain spaces. We write a regular expression for this and apply the removal of these white space as part of a function. We also need to complete the year to be 4 characters for later date parsing.<jupyter_code># We first compile our date extraction regex to improve performance
date_regex = re.compile(r'(?P<date>\d\s*\d\s*\/\s*\d\s*\d\s*\/\s*\d\s*\d)')

# Given a string beginning with mm/dd/yy we produce mm/dd/YYYY
# Function returns 'parse_error' on failure to parse and null if the
# input string was null
def extract_date_from_string(string):
    matches = None
    date = None
    
    # If the string is not null attempt to find matches
    if not pd.isnull(string):
        matches = date_regex.search(string)
    else:
        # null value for string in pandas
        date = np.nan    

    # If the ?P<date> group was found
    if matches and matches.groupdict():
        group_dict = matches.groupdict()
        date = group_dict.get('date')
        if date:
            # Remove whitespace
            date = date.replace(' ', '')
            # We prefix '20' to the year to make 01/01/17 -> 01/01/2017
            date = date[:6] + '20' + date[6:]
    
    # We identify parsing errors with the 'parse_error'
    return date if date else 'parse_error'<jupyter_output><empty_output><jupyter_text>We apply the function to every row and create a new column: 'ad_creation_date_parsed'<jupyter_code>ads_df['ad_creation_date_parsed'] = ads_df.ad_creation_date.apply(extract_date_from_string)<jupyter_output><empty_output><jupyter_text>We check how many dates could not be parsed:<jupyter_code>(ads_df['ad_creation_date_parsed'] == 'parse_error').sum()<jupyter_output><empty_output><jupyter_text>Since only one date could not be parsed we validate its value:<jupyter_code>row = ads_df[ads_df['ad_creation_date_parsed'] == 'parse_error']
row<jupyter_output><empty_output><jupyter_text>In this case the date should be 02/21/2017. An l was mistaken to a 1. We replace it manually.<jupyter_code>ads_df.loc[row.index, 'ad_creation_date_parsed'] = '02/21/2017'
ads_df.loc[row.index]<jupyter_output><empty_output><jupyter_text>Now that all dates have been parsed, we replace the original column with the parsed one and remove the temporary parsed column. Since no columns were lost in the process we will not be adding an entry to the summary.<jupyter_code># Replace original column with parsed
ads_df['ad_creation_date'] = ads_df['ad_creation_date_parsed']

# Drop temporary parsed column
ads_df = ads_df.drop(columns=['ad_creation_date_parsed'])<jupyter_output><empty_output><jupyter_text>We now execute the same steps for the end date.<jupyter_code>ads_df['ad_end_date_parsed'] = ads_df.ad_end_date.apply(extract_date_from_string)<jupyter_output><empty_output><jupyter_text>We check how many dates could not be parsed:<jupyter_code>(ads_df['ad_end_date_parsed'] == 'parse_error').sum()
ads_df['ad_end_date'] = ads_df['ad_end_date_parsed']
ads_df = ads_df.drop(columns=['ad_end_date_parsed'])<jupyter_output><empty_output><jupyter_text>Now that both ad_start_date and ad_end_date are properly parsed strings, we can apply a pandas function to transform them into datetime objects. This will make date handling easier during our analysis.<jupyter_code>ads_df.ad_creation_date = ads_df.ad_creation_date.apply(lambda date_string : pd.to_datetime(date_string, format='%m/%d/%Y'))
ads_df.ad_end_date = ads_df.ad_end_date.apply(lambda date_string : pd.to_datetime(date_string, format='%m/%d/%Y'))

# Output first 3 rows
ads_df.head(3)<jupyter_output><empty_output><jupyter_text>Parsing ad_spendSometimes the ad_spend field contains spaces, dots instead of commas to seperate thousands and the 'RUB' currency shorthand. We use a regular expression to extract the amount of the ad_spend field. We then convert the string to a float.<jupyter_code>ads_df['ad_spend']
# Pre compile regex for performance
amount_regex = re.compile(r'(?P<amount>([0-9]{1,3}(\.|,)?)+(\.|,)?[0-9]{2})')

# Function returns 'parse_error' on failure to parse and null if the
# input string was null or the string 'None'
def extract_amount_from_string(string):
    matches = None
    amount = None
    
    # If the string is not null or 'None' search for matches
    if not pd.isnull(string) and string != 'None':
        matches = amount_regex.search(string)
    else:
        # null value for string in pandas
        amount = np.nan

    # If the amount was found
    if matches and matches.groupdict():
        group_dict = matches.groupdict()
        amount = group_dict.get('amount')
        if amount:
            # Remove whitespace
            amount = amount.replace(' ', '')
            
            # Remove dots and commas
            amount = amount.replace('.', '').replace(',', '')
            
            # Add a dot two digits form the end
            amount = amount[:-2] + '.' + amount[-2:]
    
    # Return a parse_error if amount parsing failed
    return amount if amount else 'parse_error'<jupyter_output><empty_output><jupyter_text>We run the function over our dataset and output the number of parsing errors we've encountered.<jupyter_code>ads_df['ad_spend_parsed'] = ads_df.ad_spend.apply(extract_amount_from_string)
(ads_df['ad_spend_parsed'] == 'parse_error').sum()<jupyter_output><empty_output><jupyter_text>We validate nan values and remove them from the dataset.<jupyter_code>cleaning_summary_format['none_ad_spend_count'] = (~pd.isnull(ads_df['ad_spend_parsed'])).sum()
print('There are a total of ' + str(pd.isnull(ads_df['ad_spend_parsed']).sum()) + ' nan values.')
ads_df[pd.isnull(ads_df['ad_spend_parsed'])]
# Remove nulls
ads_df = ads_df[~pd.isnull(ads_df['ad_spend_parsed'])]

# Replace ad_spend with the parse column
ads_df['ad_spend'] = ads_df['ad_spend_parsed']

# Drop the parsed column
ads_df = ads_df.drop(columns=['ad_spend_parsed'])<jupyter_output><empty_output><jupyter_text>We transform the ad_spend field from string into a float.<jupyter_code>ads_df['ad_spend'] = ads_df['ad_spend'].astype(float)<jupyter_output><empty_output><jupyter_text>We validate that all values are positive and remove other values after validation.<jupyter_code>print('There are ' + str((ads_df['ad_spend'] > 0).sum()) + ' positive values and a total of ' + str(len(ads_df)) + ' entries.')
cleaning_summary_format['non_positive_ad_spend_count'] = (ads_df['ad_spend'] > 0).sum()
ads_df[ads_df['ad_spend'] <= 0]<jupyter_output><empty_output><jupyter_text>We remove the two entries with values equal to zero and print out the summary.<jupyter_code>ads_df = ads_df[ads_df['ad_spend'] > 0]

cleaning_summary_format['before_ad_spend_count'] = cleaning_summary_format['zeros_ad_clicks_count']

# Reporting
print('''Before formating ad_spend our dataset had {before_ad_spend_count} columns.
After removing rows with 'None': {none_ad_spend_count} columns.
After removing rows with 0 ad clicks: {non_positive_ad_spend_count} columns.'''.format(**cleaning_summary_format))<jupyter_output>Before formating ad_spend our dataset had 2450 columns.
After removing rows with 'None': 2442 columns.
After removing rows with 0 ad clicks: 2440 columns.<jupyter_text>Parsing ad_targeting_interests & ad_targeting_people_who_matchThe ad_targeting_interests column is split between its own column and a portion of the ad_targeting_people_who_match column's string. To make treatment of this column simpler, our first step will be to extract the 'interest' portion of ad_targeting_people_who_match. We will then parse the ad_targeting_interests column and combine the result.First we take a look at ad_targeting_people_who_match for entries with and without 'Interests'. We will investigate those without 'Interests' first.<jupyter_code>count_null = 0
count_interests = 0
count_other = 0
for s in ads_df['ad_targeting_people_who_match']:
    if pd.isnull(s):
        count_null += 1
    elif 'Interests' in s:
        count_interests += 1
    else:
        count_other +=1
        print(s)
print('Null:' + str(count_null) +
      ' Interests: ' + str(count_interests) +
      ' Other: ' + str(count_other) +
      ' Total: ' + str(count_null + count_interests + count_other))<jupyter_output>Null:822 Interests: 1243 Other: 375 Total: 2440<jupyter_text>From this print out, we see that although some rows belonging to the "Other" category have the 'interests' field missing, we can grab a proxy by taking the "like" groups. We can grab the value of the "like" groups correctly by taking the string after 'Friends of people who are connected to'. We've created the function treat_string_with_friends below to treat these entries.To treat the rows with 'Interests', we create the funciton treat_string_with_interest. After looking at a few of the raw_files, we see that ad_targeting_people_who_match sometimes contains other fields. To identify rows which had additonal fields, we looked for the number of ':' characters, we then identified patterns in those strings that didn't match the interests field. These patterns are used in the treat_string_with_interest function below.The crop_to_interest function was created to dynamically use the method when interests are present or not.<jupyter_code># Utility function to crop everything after a given word
def crop_everything_after(string, contains):
    return string[:string.index(contains)] if contains in string else string

# Returns a string containing everything after 'Friends of people who are connected to '
def treat_string_with_friends(string):
    friends_string = 'Friends of people who are connected to '
    start = string.index(friends_string)
    return string[start+len(friends_string):]

# Returns a string containing everything in the Interests: marker, but nothing in the other markers (see the crop_after variable)
def treat_string_with_interest(string):
    # Crop everything before 'Interests'
    string = string[string.index('Interests'):]

    # Strings identified by visual inspections of entries
    crop_after = [
        'And Must Also Match',
        'School:',
        'Behaviors:',
        'expansion:',
        'Job title:',
        'Multicultural Affinity:',
        'Politics:',
        'Employers:',
        'Field of study:'
    ]

    for to_crop in crop_after:
        string = crop_everything_after(string, to_crop)

    # Finally this substring had a typo
    if 'Stop Racism!:.' in string:
        string = string.replace('Stop Racism!:.', 'Stop Racism!!,')
    
    return string

# If Interests is part of the string use the interest
# method otherwise use the crop to like method.
def crop_to_interest(string):
    if not pd.isnull(string):
        
        if 'Interests' in string:
            string = treat_string_with_interest(string)
        elif 'Friends of people who are connected to ' in string:
            string = treat_string_with_friends(string)
        else:
            # pd.isnull or does not contain interests nor likes
            string = np.nan

    return string
ads_df['ad_targeting_people_who_match'] = ads_df['ad_targeting_people_who_match'].apply(crop_to_interest)<jupyter_output><empty_output><jupyter_text>During this operation we lost a few rows that could not be parsed as it did not contain interests.<jupyter_code>cleaning_summary_format['null_people_who_match_count'] = pd.isnull(ads_df['ad_targeting_people_who_match']).sum() - count_null
print(str(cleaning_summary_format['null_people_who_match_count']) + ' rows where lost.')<jupyter_output>29 rows where lost.<jupyter_text>The last cleaning step for this field is to remove the 'Interests' keyword which is sometimes followed by a colon. We use a regular expression to replace this string.<jupyter_code>interests_regex = re.compile(r'Interests\s*:?')

def remove_interests_marker(string):
    if not pd.isnull(string):
        string = interests_regex.sub('', string)
    return string
ads_df['ad_targeting_people_who_match'] = ads_df['ad_targeting_people_who_match'].apply(remove_interests_marker)
ads_df.head(3)<jupyter_output><empty_output><jupyter_text>We now do the same exercise with ad_targeting_interests. We first identify non-null rows that may contain an extra field. We do so by looking for the ':' character and printing out these rows.<jupyter_code>non_null_interests = ads_df[~pd.isnull(ads_df['ad_targeting_interests'])]['ad_targeting_interests']

for row_with_colon in non_null_interests[non_null_interests.str.contains(':')]:
    print(row_with_colon)<jupyter_output>BlackNews.com or HuffPost Black Voices Behaviors: African American (US)
BlackNews.com or HuffPost Black Voices Behaviors: African American (US)
Humanitarianism, Human rights or Humanitarian aid Behaviors: African American (US)
Black Power Behaviors: Multicultural Affinity: African American (US)
Human rights or Malcolm X Behaviors: African American (US)
BlackNews.com or HuffPost Black Voices Behaviors: African American (US)
BlackNews.com or HuffPost Black Voices Behaviors: African American (US)
BlackNews.com or HuffPost Black Voices Behaviors: African American (US)
BlackNews.com or HuffPost Black Voices Behaviors: African American (US)
BlackNews.com or HuffPost Black Voices Behaviors: African American (US)
BlackNews.com or HuffPost Black Voices Behaviors: African American (US)
Humanitarianism, Human rights or Humanitarian aid Behaviors: African American (US)
Muslims Are Not Terrorists. Islamism or Muslim Brotherhood Connections: People who like United Muslims of America
History Politics[...]<jupyter_text>Most of these rows seem to contain an additional field "Behaviors" we will remove it from the rows as we can easily use the interest part to identify the targeted demographic.<jupyter_code># Removes additional section part of the ad_targeting_interests string
def treat_interest(string):
    if not pd.isnull(string):
        # Strings identified by visual inspections of entries
        crop_after = [
            'And Must Also Match',
            'School:',
            'Behaviors:',
            'expansion:',
            'Job title:',
            'Multicultural Affinity:',
            'Politics:',
            'Employers:',
            'Field of study:',
            'Connections:',
            'Home Composition:'
        ]

        for to_crop in crop_after:
            string = crop_everything_after(string, to_crop)
    else:
        # pd.isnull value for strings
        string = np.nan
    
    return string

ads_df['ad_targeting_interests'] = ads_df['ad_targeting_interests'].apply(treat_interest)
non_null_interests = ads_df[~pd.isnull(ads_df['ad_targeting_interests'])]['ad_targeting_interests']
print('After treatment, there are ' + str(non_null_interests[non_null_interests.str.contains(':')].count()) +' rows with more than one field.')<jupyter_output>After treatment, there are 0 rows with more than one field.<jupyter_text>Now that both ad_targeting_interests and ad_targeting_people_who_match have been cleaned, we can now merge the two columns into one. First let's verify that there are no rows where both columns are non-null or both null.<jupyter_code>def interests_both_null(row):
    return (pd.isnull(row.ad_targeting_interests) and pd.isnull(row.ad_targeting_people_who_match))

def interests_both_non_null(row):
    return (not pd.isnull(row.ad_targeting_interests) and not pd.isnull(row.ad_targeting_people_who_match))
    
# How many rows have both columns as null      
cleaning_summary_format['null_all_interests_columns_count'] = ads_df.apply(interests_both_null, axis=1).sum()


# How many rows have both columns populated
non_null_count = ads_df.apply(interests_both_non_null, axis=1).sum()

print('We have a total of ' + str(both_null) + ' rows with both columns null and a total of ' + str(non_null_count) + ' rows which have both values set.')<jupyter_output>We have a total of 214 rows with both columns null and a total of 0 rows which have both values set.<jupyter_text>We drop rows that do not contain interests information in both columns. We will merge the other rows by replacing the values of ad_targeting_interests with ad_targeting_people_who_match.<jupyter_code>def merge_interests(row):
    return row.ad_targeting_interests if not pd.isnull(row.ad_targeting_interests) else row.ad_targeting_people_who_match

# Merge interests
ads_df['ad_targeting_interests'] = ads_df.apply(merge_interests, axis=1)

# Drop 'ad_targeting_people_who_match'
ads_df = ads_df.drop(columns=['ad_targeting_people_who_match'])

# Drop null columns
ads_df = ads_df[(~pd.isnull(ads_df['ad_targeting_interests']))]<jupyter_output><empty_output><jupyter_text>Writing to file<jupyter_code>ads_df.head(3)
ads_df.to_csv('../clean_data/clean_data.csv', index=None, header=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08626831835478908, 'fraction_numerical': 0.007651202331794996, 'mean_word_length': 4.982804553160571, 'pattern_counts': {'":': 0, '<': 126, '<?xml version=': 0, '>': 128, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '32', 'dir': 'jupyter-structured-clean-dedup', 'id': '667857', 'license': 'MIT', 'n_tokens_mistral': 7705, 'n_tokens_neox': 7437, 'n_words': 2467, 'path': 'src/data_cleaning.ipynb', 'repo_name': 'ALotOfData/data-512-a5', 'size': '24702'}