<jupyter_start><jupyter_text>Transform<jupyter_code>wiki_movies_df = pd.DataFrame(wiki_movies_raw)
wiki_movies_df.head()
wiki_movies_df.columns.tolist()
wiki_movies = [movie for movie in wiki_movies_raw 
               if ('Director' in movie or 'Directed by' in movie) 
               and 'imdb_link' in movie 
               and 'No. of episodes' not in movie]
len(wiki_movies)
wiki_movies_df = pd.DataFrame(wiki_movies)
wiki_movies_df.head()
def clean_movie(movie):
    movie = dict(movie) #non destructable copy
    alt_titles = {}
    
    # combine alternative titles into one list
    for key in ['Also known as', 'Arabic', 'Cantonese', 'Chinese', 'French', 'Hangul', 
                'Hebrew', 'Hepburn', 'Japanese', 'Literally', 'Mandarin', 'McCune-Reischauer', 
                'Original Title', 'Polish', 'Revised Romanization', 'Romanized', 'Russian', 
                'Simplified', 'Traditional', 'Yiddish']:
        if key in movie:
            alt_titles[key] = movie[key]
            movie.pop(key)
    if len(alt_titles) > 0:
        movie['alt_titles'] = alt_titles
    
    # merge column names
    def change_column_name(old_name, new_name):
        if old_name in movie:
            movie[new_name] = movie.pop(old_name)
    change_column_name('Adaptation by', 'Writer(s)')
    change_column_name('Country of Origin', 'Country')  
    change_column_name('Directed by', 'Director')
    change_column_name('Distributed by', 'Distributor')
    change_column_name('Edited by', 'Editor(s)')
    change_column_name('Length', 'Running time')  
    change_column_name('Original release', 'Release date')
    change_column_name('Music by', 'Composer(s)')
    change_column_name('Produced by', 'Producer(s)')
    change_column_name('Producer', 'Producer(s)')
    change_column_name('Productioncompanies ', 'Production company(s)')
    change_column_name('Productioncompany ', 'Production company(s)')
    change_column_name('Released', 'Release Date')
    change_column_name('Release Date', 'Release date')
    change_column_name('Screen story by', 'Writer(s)')
    change_column_name('Screenplay by', 'Writer(s)')
    change_column_name('Story by', 'Writer(s)')
    change_column_name('Theme music composer', 'Composer(s)')
    change_column_name('Written by', 'Writer(s)')
    
    return movie
clean_movies = [clean_movie(movie) for movie in wiki_movies]
wiki_movies_df = pd.DataFrame(clean_movies)
sorted(wiki_movies_df.columns.tolist())
wiki_movies_df.head()
wiki_movies_df['imdb_id'] = wiki_movies_df['imdb_link'].str.extract(r'(tt\d{7})')
print(len(wiki_movies_df))
wiki_movies_df.drop_duplicates(subset='imdb_id', inplace=True)
print(len(wiki_movies_df))
wiki_movies_df.head()
[[column, wiki_movies_df[column].isnull().sum()] for column in wiki_movies_df]
wiki_columns_to_keep = [column for column in wiki_movies_df.columns if wiki_movies_df[column].isnull().sum() < len(wiki_movies_df)*0.9]
wiki_movies_df = wiki_movies_df[wiki_columns_to_keep]
wiki_movies_df.columns.tolist()
wiki_movies_df.head()
wiki_movies_df.dtypes
box_office = wiki_movies_df['Box office'].dropna()
len(box_office)
def is_not_string(x):
    return type(x) != str
box_office[box_office.map(is_not_string)]
box_office[box_office.map(lambda x: type(x) != str)]
box_office = box_office.apply(lambda x: ' '.join(x) if type(x) == list else x)
box_office = box_office.str.replace(r'\$.*[-—–](?![a-z])', '$', regex=True)
#first form of captureing box_office numbers
form_one = r'\$\s*\d+\.?\d*\s*[mb]illi?on'
# second form
form_two = r'\$\s*\d{1,3}(?:[,\.]\d{3})+(?!\s[mb]illion)'
matches_form_one = box_office.str.contains(form_one, flags=re.IGNORECASE, na=False)
matches_form_two = box_office.str.contains(form_two, flags=re.IGNORECASE, na=False)
# this will throw an error
box_office[~matches_form_one & ~matches_form_two]
box_office.str.extract(f'({form_one}|{form_two})')
def parse_dollars(s):
    # if s is not a string, return NaN
    if type(s) != str:
        return np.nan
    
    # if input is of the form $###.# million
    if re.match(r'\$\s*\d+\.?\d*\s*milli?on', s, flags=re.IGNORECASE):
        # remove dollar sign and 'million'
        s = re.sub('\$|\s|[a-zA-Z]','', s)
        
        # convert to float
        s = float(s) * 10**6
        
        return s
    elif re.match(r'\$\s*\d+\.?\d*\s*billi?on', s, flags=re.IGNORECASE):
        s = re.sub('\$|\s|[a-zA-Z]','', s)
        s= float(s) * 10**9
        return s
    elif re.match(r'\$\s*\d{1,3}(?:[,\.]\d{3})+(?!\s[mb]illion)', s, flags=re.IGNORECASE):
        s = re.sub('\$|,','',s)
        s = float(s)
        return s
    else:
        return np.nan
wiki_movies_df['box_office'] = box_office.str.extract(f'({form_one}|{form_two})', flags=re.IGNORECASE)[0].apply(parse_dollars)
wiki_movies_df['box_office']
wiki_movies_df.drop('Box office', axis=1, inplace=True)
budget = wiki_movies_df['Budget'].dropna()
budget = budget.map(lambda x: ' '.join(x) if type(x) == list else x)
budget = budget.str.replace(r'\$.*[-—–](?![a-z])', '$', regex=True)
matches_form_one = budget.str.contains(form_one, flags=re.IGNORECASE, na=False)
matches_form_two = budget.str.contains(form_two, flags=re.IGNORECASE, na=False)
budget = budget.str.replace(r'\[\d+\]\s*', '')
budget[~matches_form_one & ~matches_form_two]
wiki_movies_df['budget'] = budget.str.extract(f'({form_one}|{form_two})', flags=re.IGNORECASE)[0].apply(parse_dollars)
wiki_movies_df.drop('Budget', axis=1, inplace=True)
release_date = wiki_movies_df['Release date'].dropna().apply(lambda x: ' '.join(x) if type(x) == list else x)
form_one = r'(?:January|February|March|April|May|June|July|August|September|October|November|December)\s[123]?\d+,\s\d{4}'
form_two = r'\d{4}.[01]\d.[0123]\d'
form_three = r'(?:January|February|March|April|May|June|July|August|September|October|November|December)\s\d{4}'
form_four = r'\d{4}'
release_date.str.extract(f'({form_one}|{form_two}|{form_three}|{form_four})', flags=re.IGNORECASE)
wiki_movies_df['release_date'] = pd.to_datetime(release_date.str.extract(f'({form_one}|{form_two}|{form_three}|{form_four})')[0], infer_datetime_format=True)
running_time = wiki_movies_df['Running time'].dropna().apply(lambda x: ' '.join(x) if type(x) == list else x)
running_time.str.contains(r'^\d*\s*minutes$', flags=re.IGNORECASE, na=False).sum()
running_time[running_time.str.contains(r'^\d*\s*minutes$', flags=re.IGNORECASE, na=False) != True]
running_time.str.contains(r'^\d*\s*m', flags=re.IGNORECASE, na=False).sum()
running_time[running_time.str.contains(r'(\d+)\s*ho?u?r?s?\s*(\d*)|(\d+)\s*m', flags=re.IGNORECASE, na=False) != True]
running_time_extract = running_time.str.extract(r'(\d+)\s*ho?u?r?s?\s*(\d*)|(\d+)\s*m')
running_time_extract = running_time_extract.apply(lambda col: pd.to_numeric(col, errors='coerce')).fillna(0)
wiki_movies_df['running_time'] = running_time_extract.apply(lambda row: row[0]*60 + row[1] if row[2] == 0 else row[2], axis=1)
wiki_movies_df.drop('Running time', axis=1, inplace=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17943951958821847, 'fraction_numerical': 0.0062911066628538745, 'mean_word_length': 5.077324066029539, 'pattern_counts': {'":': 0, '<': 6, '<?xml version=': 0, '>': 6, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '562926', 'license': 'MIT', 'n_tokens_mistral': 2758, 'n_tokens_neox': 2635, 'n_words': 429, 'path': '.ipynb_checkpoints/load_data-checkpoint.ipynb', 'repo_name': 'apaneser/Movies-ETL', 'size': '6994'}