Skip to content

Cleaning Functions

clean_column_names(df, hardcode_col_dict={}, errors='ignore', cols_no_change=['spend', 'date', 'currency', 'cohort', 'creative_name', 'group_id', 'engagements', 'created', 'ad_id', 'plays', 'saved', 'post_hastags', 'content_type', 'linked_content', 'post_id', 'video_duration', 'average_time_watched', 'total_time_watched', 'adset_targeting', 'completion_rate', 'targeting', 'cohort_new', 'video_completions', 'post_hashtags'])

Cleans the column names of an advertisement performance (organic or paid) dataset, commonly from Tracer but could also be from Sprout social. The column names will be standardized so then other functions in other libraries can work with the dataset.

Parameters:

Name Type Description Default
df DataFrame

Input dataframe containing a row item for each piece of creative or a day of advertising.

 required
hardcode_col_dict Dict[str, str]

A dictionary specifying exact transformations of column names from the key to the value.

 {}
errors str

How to handle errors during the conversion. Can be 'raise', 'ignore' or 'warn'

 'ignore'
cols_no_change List[str]

A list of column names to be left unchanged.

 ['spend', 'date', 'currency', 'cohort', 'creative_name', 'group_id', 'engagements', 'created', 'ad_id', 'plays', 'saved', 'post_hastags', 'content_type', 'linked_content', 'post_id', 'video_duration', 'average_time_watched', 'total_time_watched', 'adset_targeting', 'completion_rate', 'targeting', 'cohort_new', 'video_completions', 'post_hashtags']

Returns:

Name Type Description
DataFrame

Output dataframe with the column names standardized.
Source code in vee_clean/cleaning_functions.py 
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
def clean_column_names(df, hardcode_col_dict = {},errors= 'ignore',cols_no_change = ['spend', 'date', 'currency', 
                            'cohort', 'creative_name', 'group_id', 'engagements', 'created', 'ad_id',
                            'plays', 'saved', 'post_hastags', 'content_type', 'linked_content', 'post_id',
                            'video_duration', 'average_time_watched', 'total_time_watched',
                            'adset_targeting', 'completion_rate', 'targeting', 'cohort_new',
                            'video_completions', 'post_hashtags']):

    """Cleans the column names of an advertisement performance (organic or paid) dataset, commonly from
    Tracer but could also be from Sprout social. The column names will be standardized so
    then other functions in other libraries can work with the dataset.

    Args:
        df (DataFrame): Input dataframe containing a row item for each piece of creative or a day of advertising.
        hardcode_col_dict (Dict[str, str], optional): A dictionary specifying exact transformations of column names
                                                        from the key to the value.
        errors (str, optional): How to handle errors during the conversion.
                                Can be 'raise', 'ignore' or 'warn'
        cols_no_change (List[str], optional): A list of column names to be left unchanged.

    Returns:
        DataFrame: Output dataframe with the column names standardized."""

    new_columns = []
    for column in df.columns:
        column = column.lower()
        hardcode_col_dict = {k.lower():v for k,v in hardcode_col_dict.items()} #make sure the keys are lowercase
        #hardcoded columns, for those unusual columns
        if column in hardcode_col_dict.keys():
            column = hardcode_col_dict[column]
        if 'day' == column:
            column = 'date'
        #Columns not to be changed and that don't get called similar things
        elif column in cols_no_change:
            column = column
        elif ('created' in column) and (('date' in column) or ('time' in column) or ('video' in column)):
            column = 'date'  # for organic
        elif ('like' in column) or ('favorite' in column) or ('reaction' in column):
            column = 'likes'
        elif ('video' not in column) and ('impression' in column) and ('unique' not in column):
            column = 'impressions'
        elif (column == 'reach') or (('impressions' in column) and ('unique' in column)):
            column = 'reach'
        elif ('campaign'in column) and ('name' in column):
            column = 'campaign_name'
        elif (('adset' in column) or ('group' in column)) and ('name' in column):
            column = 'group_name'
        elif ('ad' in column) and ('name' in column):
            column = 'ad_name' # for tt_organic
        elif ('creative' in column) and ('name' in column):
            column = 'creative_name'
        elif ('video' in column) and ('impression' in column):                       
            column = 'video_impressions'
        elif ('shares' in column) or ('retweet' in column):
            column = 'shares'
        elif (('conversion' in column) or ('lifetime'in column)) and ('save' in column): #_1d_click_onsite_conversion_post_save in fb_ig_paid data
            column = 'saved'
        elif ('video' in column) and (('100' in column) or ('complet' in column) or('full' in column)):
            column = 'video_completions'
        elif ('video' in column) and ('view' in column) and ('0' not in column) and ('5' not in column): #don't include columsn with 25,50,75% completion
            column = 'video_views'
        elif ('organic' in column) and ('boosted' in column) or ( 'workstream' in column):
            column = 'workstream'
        elif 'currency' in column:
            column = 'currency'
        elif 'country' in column:
            column = 'country'
        elif ('replies' in column) or ('comment' in column):
            column = 'comments'
        elif (('page' in column) and('id' not in column)) or (column == 'profile') or ('business' in column) \
            or (('account' in column) and ('name' in column)) or (column == 'post_username'):
            column = 'account_name'  # for twitter organic
        elif ('caption' in column) or ('text' in column) or ('copy' in column) or ('message' in column) or(column == 'post') or (column == 'video_name'):
            column = 'message'
        elif (column == 'video_id') or ('post_id' in column) or ('ad_id' in column):
            column = 'post_id'
        elif ('url' in column) or (('link' in column) and ('clicks' not in column)):
            column = 'url'
        elif ('clicks' in column) and ('link' in column): #this is also equivalent to a swipe in Snapchat
            column = 'link_clicks'  # for all_plats_paid
        elif ('clicks' in column) and ('link' not in column):
            column = 'clicks'  # for all_plats_paid
        elif ('network' in column) or ('platform' in column):
            column = 'platform'  # for li_tt_igStories_organic
        elif (('media' in column) and ('product' in column) and ('type' in column)) or ('placement' in column):
            column = 'placement'
        elif (('type' in column) & ('post' in column)) or (('media' in column) and ('type' in column)) \
            or (('content' in column) and ('category' in column)): #creative media type for fb_ig_paid
            column = 'media_type'
        elif('cohort' in column):
            column = 'cohort'
        else:
            message = f'Column "{column}" is not handled in column cleaning function'
            if errors == 'raise':
                raise Exception(message)
            cleaning_logger.logger.info(message)

        new_columns.append(column)
    if len(new_columns) != len(set(new_columns)):
        cleaning_logger.logger.exception(f'Duplicate column names found {sorted(new_columns)}')
        raise ValueError
    df.columns = new_columns

    return df

clean_media_type(media_type)

Clean the media type into a standardised version of media type

Source code in vee_clean/cleaning_functions.py 
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
def clean_media_type(media_type):
    """Clean the media type into a standardised version of media type"""
    media_type = str(media_type)
    # replace multiple underscores with a single space
    media_type = re.sub(r'_+', ' ', media_type)
    media_type = media_type.title().strip()

    if (media_type == 'Photo') or (media_type == 'Image') or (media_type == 'Static'):
        media_type_standardised = 'Image'
    elif ('Gif' in media_type) or ('Animated' in media_type):
        media_type_standardised = 'Gif'

    elif ('Native Templates' in media_type):
        media_type_standardised = 'Event'

    elif ('Video' in media_type):
        media_type_standardised = 'Video'

    elif ('Carousel' in media_type) or ('Album' in media_type):
        media_type_standardised = 'Carousel'

    elif media_type == 'Nan' or media_type == 'None' or media_type == 'Null' or media_type == '':
        media_type_standardised = 'N/A'
    else: #else leave it as it was
        media_type_standardised = media_type
    return media_type_standardised

clean_placement(placement)

Clean a placement string into a standardised placement

Parameters:

Name Type Description Default
placement str

A string representing the placement of the post

required

Returns:

Name Type Description
placement str

The standardised placement of the post, if it is in 'Reel', 'Story' or 'Feed'
Source code in vee_clean/cleaning_functions.py 
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
def clean_placement(placement):
    """Clean a placement string into a standardised placement
    Args:
        placement (str): A string representing the placement of the post 
    Returns:
        placement (str): The standardised placement of the post, if it is in 'Reel', 'Story' or 'Feed'"""

    placement = str(placement).title().strip()
    if placement == 'Nan' or placement == 'None' or placement == 'Null' or placement == '':
        placement = 'Feed'
    elif ('Reel' in placement):
        placement = 'Reel'
    elif 'Story' in placement:
        placement = 'Story'
    else:
        placement = 'Feed'
    return placement

clean_platform_name(platform)

Cleans platform name, deals with the fact that most platforms are in the 'title' case, i.e. First letter of each word is capitalised However there are some platforms that have are one word but are usually presented as having a capital letter midway through the word.

Source code in vee_clean/cleaning_functions.py 
201
202
203
204
205
206
207
208
209
210
211
212
def clean_platform_name(platform):
    """Cleans platform name, deals with the fact that most platforms 
    are in the 'title' case, i.e. First letter of each word is capitalised
    However there are some platforms that have are one word but are usually 
    presented as having a capital letter midway through the word."""
    platform = str(platform).lower()
    if 'tiktok' in platform:
        return 'TikTok'
    if 'linkedin' in platform:
        return 'LinkedIn'
    else:
        return platform.title()

clean_url(url)

Clean the url of the post to produce a string with just the important information for matching In he case of Tiktok remove everything after and including the ? This removes the utm parameters

Source code in vee_clean/cleaning_functions.py 
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
def clean_url(url):
    """Clean the url of the post to produce a string with just
        the important information for matching
        In he case of Tiktok remove everything after and including the ?
        This removes the utm parameters"""
    url = str(url)
    url = url.lower()
    url = url.strip()
    url = url.replace('https://', '')
    url = url.replace('http://', '')
    url = url.replace('www.', '')
    url = url.replace('/', '')
    if 'tiktok' in url:
        return url.split('?')[0]
    return url

extract_after_nth_occurrence(string, char, n)

Extracts the string between the nth and n+1th occurrence of the character

Source code in vee_clean/cleaning_functions.py 
302
303
304
305
306
307
def extract_after_nth_occurrence(string, char, n):
    """Extracts the string between the nth and n+1th occurrence of the character"""
    extract = string.split(char)[n]
    extract = extract.strip()
    extract = extract.title()
    return extract

extract_country_from_string(string, client_name, hardcode_dict)

Converts a string containing info identifying a certain

Parameters:

Name Type Description Default
string

str string to pass through perhaps in a lambda function, the country is detected from this

 required
client_name

str name of the client that is removed to make the detection of country easier, for example so you are not searching for 'de' to find Germany with 'indeed' in the string

 required

Returns:

Name Type Description
country_code

str country Tag abbreviation mostly following the ISO 3166-1 alpha-2 format
Source code in vee_clean/cleaning_functions.py 
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
def extract_country_from_string(string, client_name, hardcode_dict):
    """Converts a string containing info identifying a certain 
    Args:
        string : str 
            string to pass through perhaps in a lambda function, the country is detected from this
        client_name : str 
            name of the client that is removed to make the detection of country easier, 
            for example so you are not searching for 'de' to find Germany with 'indeed' in the string
    Returns:
        country_code : str 
            country Tag abbreviation mostly following the ISO 3166-1 alpha-2 format"""
    string = str(string).lower().strip()
    hardcode_dict = {k.lower():v for k,v in hardcode_dict.items()}
    client_name = client_name.lower()
    # remove all non-alphanumeric characters
    if string in hardcode_dict.keys():
        country_code =  hardcode_dict[string]
    string = re.sub(r'[^\w\s]', '', string)
    string = string.replace(client_name, '')
    if ('uk' in string):
        country_code =  'UK/IE'
    elif ('ie' in string) or ('ireland' in string):
        country_code = "UK/IE"
    elif ('au' in string) or ('australia' in string):
        country_code = "AU"
    elif 'fr' in string:
        country_code = "FR"
    elif 'it' in string:
        country_code = "IT"
    elif 'nl' in string:
        country_code = "NL"
    elif 'de' in string:
        country_code = "DE"
    elif 'ca' in string:
        country_code = "CA"
    elif 'us' in string:
        country_code = "US"
    else:
        country_code = "N/A"
    return country_code

extract_quarter_from_date(date)

Extracts the quarter from the date

Source code in vee_clean/cleaning_functions.py 
297
298
299
300
def extract_quarter_from_date(date):
    """Extracts the quarter from the date"""
    date = pd.to_datetime(date)
    return date.quarter

extract_region_from_country(country)

Extracts the region from the given country.

Parameters:

Name Type Description Default
country str

A string representing the country name.

 required

Returns:

Name Type Description
str

The region of the given country, if it is in 'UK/IE', 'NL', 'DE', 'FR', 'IT', 'BE', it returns "EMEA". If the country is 'CA' or 'US', it returns "N. America". If the country is not found, it returns None.

Example:

extract_region_from_country("FR") 'EMEA' extract_region_from_country("US") 'N. America' extract_region_from_country("XX") None

Source code in vee_clean/cleaning_functions.py 
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
def extract_region_from_country(country):
    """Extracts the region from the given country.

    Args:
        country (str): A string representing the country name.

    Returns:
        str: The region of the given country, if it is in 'UK/IE', 'NL', 'DE', 'FR', 'IT', 'BE',
            it returns "EMEA". If the country is 'CA' or 'US', it returns "N. America". If the
            country is not found, it returns None.
    Example:
    >>> extract_region_from_country("FR")
    'EMEA'
    >>> extract_region_from_country("US")
    'N. America'
    >>> extract_region_from_country("XX")
    None
    """
    if country == 'UK/IE' or country == 'NL' or country == 'DE' \
            or country == 'FR' or country == 'IT' or country == 'BE':
        return "EMEA"
    elif country == 'CA' or country == 'US':
        return "N. America"
    else:
        return None

strip_object_columns(df)

Strips leading and trailing whitespaces in columns containing strings. This stops effective duplications when two categories in a column are essentially the same but one just has a whitespace

Source code in vee_clean/cleaning_functions.py 
166
167
168
169
170
171
172
def strip_object_columns(df):
    """Strips leading and trailing whitespaces in columns containing strings. 
        This stops effective duplications when two categories in a column 
        are essentially the same but one just has a whitespace"""
    df_obj = df.select_dtypes(['object'])
    df[df_obj.columns] = df_obj.apply(lambda x: x.str.strip())
    return df

two_urls_per_post_to_1(x)

Sometimes there are identical posts posted on the same day. This can be used to return just the url of the post with the highest amount of 'impressions' or 'video views'

Source code in vee_clean/cleaning_functions.py 
331
332
333
334
335
336
337
338
339
340
341
342
343
def two_urls_per_post_to_1(x):
    """Sometimes there are identical posts posted on the same day. 
    This can be used to return just the url of the post with
    the highest amount of 'impressions' or 'video views'"""
    if x['platform'].iloc[0] != 'TikTok':
        target_col = 'impressions'
        max_score = x['impressions'].max()
    else:
        target_col = 'video_views'
        max_score = x['video_views'].max()

    # return pd.Series(d,index=list(d.keys()))
    return x[x[target_col] == max_score][['url','influencer?']]

updated_value_extract(url)

Extract the unique identifier for a post from the url This format of this code depends on the platform, sometimes it is a numerical code, sometimes it is alphanumeric

Source code in vee_clean/cleaning_functions.py 
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
def updated_value_extract(url):
    """Extract the unique identifier for a post from the url
        This format of this code depends on the platform, sometimes
        it is a numerical code, sometimes it is alphanumeric"""
    try:
        if 'facebook' in url:
            u_url = re.search(r"/(\d{16})/|\w{0,4}/*(\d{11,16})", url).group(0)
            try:
                str(u_url)
            except:
                u_url = re.search(r".*/(\d[9])/$").group(0)
            return u_url
        elif 'instagram' in url:
            u_url = re.search(r"/(.{11})/", url).group(0)
            return u_url
        else:
            u_url = re.search(r"(\d{19})", url).group(0)
            return u_url
    except:
        return 'N/A'

video_len_toseconds(len_string)

Video lengths come through tracer in the format 'minute:seconds', e.g. '1:20' or 80 seconds This function converts it into just seconds

Source code in vee_clean/cleaning_functions.py 
362
363
364
365
366
367
368
369
370
371
372
def video_len_toseconds(len_string):
    """Video lengths come through tracer in the format 'minute:seconds', e.g. '1:20' or 80 seconds
        This function converts it into just seconds"""
    try:
        if ':' not in len_string:
            return len_string
        minutes = int(len_string.split(":")[0])
        seconds = int(len_string.split(":")[1])
        return (minutes * 60) + seconds
    except:
        return None