def update_c11_check_box(original_df: object, pre_valid: str): original_df['url'] = original_df.apply( lambda x: get_key_value_from_gsheet_info(gsheet_info=x['gsheet_info'], key='url') if x[ 'pre_valid'] == pre_valid else 'None', axis=1) original_df['itune_id'] = original_df.apply( lambda x: get_itune_id_region_from_itune_url(url=x['itune_album_url'])[0] if ( x['itune_album_url'] != '' and x['pre_valid'] == pre_valid) else x['itune_id'], axis=1) original_df['region'] = original_df.apply( lambda x: get_itune_id_region_from_itune_url(url=x['itune_album_url'])[1] if ( x['itune_album_url'] != '' and x['pre_valid'] == pre_valid) else x['region'], axis=1) original_df['checking_validate_itune'] = original_df.apply( lambda x: check_validate_itune(itune_album_id=x['itune_id'], itune_region=x['region']) if ( x['itune_album_url'] != '' and x['pre_valid'] == pre_valid) else x['checking_validate_itune'], axis=1) gsheet_infos = list(set(original_df.gsheet_info.tolist())) sheet_name = get_key_value_from_gsheet_info(gsheet_info=gsheet_infos[0], key='sheet_name') url = get_key_value_from_gsheet_info(gsheet_info=gsheet_infos[0], key='url') grid_range_to_update = f"{sheet_name}!AJ2" list_result = original_df[ ['itune_id', 'region', 'checking_validate_itune']].values.tolist() # transfer data_frame to 2D list update_value(list_result=list_result, grid_range_to_update=grid_range_to_update, gsheet_id=get_gsheet_id_from_url(url=url))
def check_validate(): original_df['itune_id'] = original_df['Itunes_Album_URL'].apply( lambda x: get_itune_id_region_from_itune_url(url=x)[0]) original_df['region'] = original_df['Itunes_Album_URL'].apply( lambda x: get_itune_id_region_from_itune_url(url=x)[1]) original_df['checking_validate_itune'] = original_df['itune_id'].apply( lambda x: check_validate_itune(x)) original_df['token_set_ratio'] = original_df.apply(lambda x: get_max_ratio( itune_album_id=x['itune_id'], input_album_title=x.AlbumTitle), axis=1) print(original_df)
def c11_filter(self): df = self.original_file if c11_checkbox(original_df=df, pre_valid=self.pre_valid): filter_df = df[ (df['pre_valid'] == self.pre_valid) & (df['itune_album_url'] != '') & (~df['content type'].str.contains('REJECT'))].reset_index() filter_df['itune_id'] = filter_df['itune_album_url'].apply( lambda x: get_itune_id_region_from_itune_url(url=x)[0]) filter_df['region'] = filter_df['itune_album_url'].apply( lambda x: get_itune_id_region_from_itune_url(url=x)[1]) filter_df = filter_df.drop_duplicates( subset=['itune_id', 'gsheet_info'], keep='first').reset_index() return filter_df
def s11_filter(self): df = self.original_file s11_checkbox(df=df) if s11_checkbox(df=df): filter_df = df[(df['itune_album_url'] != 'not found') & (df['itune_album_url'] != '')].drop_duplicates( subset=['itune_album_url', 'gsheet_info'], keep='first').reset_index() filter_df['itune_id'] = filter_df['itune_album_url'].apply( lambda x: get_itune_id_region_from_itune_url(url=x)[0]) filter_df['region'] = filter_df['itune_album_url'].apply( lambda x: get_itune_id_region_from_itune_url(url=x)[1]) return filter_df else: pass
def check_box_S_11_validate(gsheet_id: str): ''' S_11 = {"sheet_name": "S_11", "column_name": ["release_date", "album_title", "album_artist", "itune_album_url", "sportify_album_url"]} ''' sheet_info = sheet_type.S_11 sheet_name = sheet_info.get('sheet_name') column_name = sheet_info.get('column_name') S_11_df = get_df_from_speadsheet(gsheet_id=gsheet_id, sheet_name=sheet_name) S_11_df.columns = S_11_df.columns.str.replace('Release_date', 'release_date') S_11_df.columns = S_11_df.columns.str.replace('AlbumTitle', 'album_title') S_11_df.columns = S_11_df.columns.str.replace('AlbumArtist', 'album_artist') S_11_df.columns = S_11_df.columns.str.replace('Itunes_Album_URL', 'itune_album_url') S_11_df.columns = S_11_df.columns.str.replace('AlbumURL', 'sportify_album_url') S_11_df = S_11_df[column_name].head(10) # Step 2: check validate format check_format_album_wiki = S_11_df[~((S_11_df['itune_album_url'] == 'not found')| (S_11_df['itune_album_url'].str[:32] == 'https://music.apple.com/us/album'))] S_11_format_validate = check_format_album_wiki.album_title.str.upper().to_numpy().tolist() if S_11_format_validate: print(check_format_album_wiki) return S_11_format_validate # Step 3: check validate itune_url else: S_11_df['itune_id'] = S_11_df['itune_album_url'].apply( lambda x: get_itune_id_region_from_itune_url(url=x)[0] if x != 'not found' else 'None') S_11_df['region'] = S_11_df['itune_album_url'].apply( lambda x: get_itune_id_region_from_itune_url(url=x)[1] if x != 'not found' else 'None') S_11_df['checking_validate_itune'] = S_11_df['itune_id'].apply(lambda x: check_validate_itune(x) if x != 'None' else 'None') S_11_df['token_set_ratio'] = S_11_df.apply( lambda x: get_max_ratio(itune_album_id=x['itune_id'], input_album_title=x['album_title']) if x['itune_id'] != 'None' else 'None', axis=1) # Step 4 update value: column_name = ['itune_id', 'region', 'checking_validate_itune', 'token_set_ratio'] updated_df = S_11_df[column_name] list_result = updated_df.values.tolist() # transfer data_frame to 2D list list_result.insert_column(0, column_name) range_to_update = f"{sheet_name}!M1" update_value(list_result, range_to_update, gsheet_id) # validate_value type: object, int, category... NOT DATETIME
def update_s11_check_box(df: object): gsheet_infos = list(set(df.gsheet_info.tolist())) df['url'] = df['gsheet_info'].apply( lambda x: get_key_value_from_gsheet_info(gsheet_info=x, key='url')) df['itune_id'] = df['itune_album_url'].apply( lambda x: get_itune_id_region_from_itune_url(url=x)[0] if x != 'not found' else 'None') df['region'] = df['itune_album_url'].apply( lambda x: get_itune_id_region_from_itune_url(url=x)[1] if x != 'not found' else 'None') df['checking_validate_itune'] = df['itune_id'].apply( lambda x: check_validate_itune(x) if x != 'None' else 'None') df['token_set_ratio'] = df.apply( lambda x: get_max_ratio(itune_album_id=x['itune_id'], input_album_title=x['album_title']) if x['itune_id'] != 'None' else 'None', axis=1) # Update data for gsheet_info in gsheet_infos: url = get_key_value_from_gsheet_info(gsheet_info=gsheet_info, key='url') df_to_upload = df[df['url'] == url].reset_index() # print(df_to_upload) column_name = ['itune_id', 'region', 'checking_validate_itune', 'token_set_ratio'] updated_df = df_to_upload[column_name] update_value_at_last_column(df_to_update=updated_df, gsheet_id=get_gsheet_id_from_url(url), sheet_name=SheetNames.S_11)
def c11_checkbox(original_df: object, pre_valid: str = None): df = original_df[original_df['pre_valid'] == pre_valid].reset_index() df['itune_id'] = df['itune_album_url'].apply( lambda x: get_itune_id_region_from_itune_url(url=x)[0] if x not in ( 'None', '', 'not found', 'non', 'nan', 'Itunes_Album_Link') else 'None') df['url'] = df['gsheet_info'].apply( lambda x: get_key_value_from_gsheet_info(gsheet_info=x, key='url')) check_format_s11 = df[~(( (~df['content type'].str.contains('REJECT')) & (df['itune_album_url'].str[:24] == 'https://music.apple.com/') ) | ( (df['itune_album_url'] == '') & (df['content type'].str.contains('REJECT')) ))] if check_format_s11.empty: print(Fore.LIGHTYELLOW_EX + f"Pass check box" + Style.RESET_ALL) return True else: print(Fore.LIGHTYELLOW_EX + f"Not pass check box" + Style.RESET_ALL) print(check_format_s11.head(10)) return False
def checking_s11_crawler_status(df: object): original_df = df.copy() original_df['itune_id'] = original_df['itune_album_url'].apply( lambda x: get_itune_id_region_from_itune_url(url=x)[0] if x not in ('None', '', 'not found', 'non', 'nan', 'Itunes_Album_Link' ) else 'None') original_df['url'] = original_df['gsheet_info'].apply( lambda x: get_key_value_from_gsheet_info(gsheet_info=x, key='url')) gsheet_infos = list(set(original_df.gsheet_info.tolist())) for gsheet_info in gsheet_infos: gsheet_name = get_key_value_from_gsheet_info(gsheet_info=gsheet_info, key='gsheet_name') sheet_name = get_key_value_from_gsheet_info(gsheet_info=gsheet_info, key='sheet_name') PIC_taskdetail = f"{gsheet_name}_{sheet_name}" url = get_key_value_from_gsheet_info(gsheet_info=gsheet_info, key='url') original_df_split = original_df[original_df['url'] == url].reset_index() count = 0 while True and count < 300: checking_accuracy_result = get_df_from_query( get_s11_crawlingtask_info(pic=PIC_taskdetail)) checking_accuracy_result[ 'itune_album_id'] = checking_accuracy_result[ 'itune_album_id'].apply(lambda x: x.strip('"')) result = checking_accuracy_result[ ~((checking_accuracy_result['06_status'] == 'complete') & (checking_accuracy_result['e5_status'] == 'complete')) | (checking_accuracy_result['06_status'] == 'incomplete') | ((checking_accuracy_result['06_status'] == 'complete') & (checking_accuracy_result['e5_status'] == 'incomplete'))] checking = result.empty if checking == 1: print( Fore.LIGHTYELLOW_EX + f"File: {gsheet_name}, sheet_name: {sheet_name} has been crawled complete already" + Style.RESET_ALL) data_merge = pd.merge(original_df_split, checking_accuracy_result, how='left', left_on='itune_id', right_on='itune_album_id', validate='m:1').fillna(value='None') print(data_merge) # update data to gsheet data_updated = data_merge[checking_accuracy_result.columns] update_value_at_last_column( df_to_update=data_updated, gsheet_id=get_gsheet_id_from_url(url=url), sheet_name=sheet_name) # update data report: data_report = data_merge[~( ((data_merge['itune_album_url'].isin(['not found', ''])) & (data_merge['06_status'] == 'None') & (data_merge['e5_status'] == 'None')) | ((~data_merge['itune_album_url'].isin(['not found', ''])) & (data_merge['06_status'] == 'complete') & (data_merge['e5_status'] == 'complete')))] if data_report.empty: print(Fore.LIGHTYELLOW_EX + f"Accuracy: ok\nStatus: ok" + Style.RESET_ALL) else: print(Fore.LIGHTYELLOW_EX + f"Accuracy: not ok\nStatus: not ok" + Style.RESET_ALL) columns_data_report = ['itune_id'] + list( checking_accuracy_result.columns) data_report = data_report[columns_data_report] print(data_report) break else: count += 1 print( Fore.LIGHTYELLOW_EX + f"File: {gsheet_name}, sheet_name: {sheet_name} hasn't been crawled complete" + Style.RESET_ALL) time.sleep(10) print(count, "-----", result)
def checking_c11_crawler_status(original_df: object, pre_valid: str = None): original_df['itune_id'] = original_df.apply( lambda x: get_itune_id_region_from_itune_url(url=x['itune_album_url'])[ 0] if x['itune_album_url'] not in ('None', '', 'not found', 'non', 'nan', 'Itunes_Album_Link') else x[ 'itune_id'], axis=1) original_df['url'] = original_df['gsheet_info'].apply( lambda x: get_key_value_from_gsheet_info(gsheet_info=x, key='url')) gsheet_infos = list(set(original_df.gsheet_info.tolist())) for gsheet_info in gsheet_infos: gsheet_name = get_key_value_from_gsheet_info(gsheet_info=gsheet_info, key='gsheet_name') sheet_name = get_key_value_from_gsheet_info(gsheet_info=gsheet_info, key='sheet_name') PIC_taskdetail = f"{gsheet_name}_{sheet_name}_{pre_valid}" url = get_key_value_from_gsheet_info(gsheet_info=gsheet_info, key='url') original_df_split = original_df[original_df['url'] == url].reset_index() count = 0 while True and count < 300: checking_accuracy_result = get_df_from_query( get_s11_crawlingtask_info(pic=PIC_taskdetail)) checking_accuracy_result[ 'itune_album_id'] = checking_accuracy_result[ 'itune_album_id'].apply(lambda x: x.strip('"')) result = checking_accuracy_result[~( ((checking_accuracy_result['06_status'] == 'complete') & (checking_accuracy_result['E5_status'] == 'complete')) | (checking_accuracy_result['06_status'] == 'incomplete') | ((checking_accuracy_result['06_status'] == 'complete') & (checking_accuracy_result['E5_status'] == 'incomplete')))] checking = result.empty if checking == 1: print( Fore.LIGHTYELLOW_EX + f"File: {gsheet_name}, sheet_name: {sheet_name} has been crawled complete already" + Style.RESET_ALL) data_merge = pd.merge(original_df_split, checking_accuracy_result, how='left', left_on='itune_id', right_on='itune_album_id', validate='m:1').fillna(value='None') data_merge['06_id_x'] = data_merge.apply( lambda x: x['06_id_y'] if x['pre_valid'] == pre_valid else x['06_id_x'], axis=1) data_merge['06_status_x'] = data_merge.apply( lambda x: x['06_status_y'] if x['pre_valid'] == pre_valid else x['06_status_x'], axis=1) data_merge['e5_id'] = data_merge.apply( lambda x: x['E5_id'] if x['pre_valid'] == pre_valid else x['e5_id'], axis=1) data_merge['e5_status'] = data_merge.apply( lambda x: x['E5_status'] if x['pre_valid'] == pre_valid else x['e5_status'], axis=1) data_merge.columns = data_merge.columns.str.replace( '06_id_x', '06_id') data_merge.columns = data_merge.columns.str.replace( '06_status_x', '06_status') data_merge = data_merge[original_df_split.columns] # update data report: data_report = data_merge[data_merge['pre_valid'] == pre_valid] data_report = data_report[~( ((data_report['itune_album_url'].isin(['not found', ''])) & (data_report['06_status'] == 'None') & (data_report['e5_status'] == 'None')) | ((~data_report['itune_album_url'].isin(['not found', ''])) & (data_report['06_status'] == 'complete') & (data_report['e5_status'] == 'complete')))] if data_report.empty: print(Fore.LIGHTYELLOW_EX + f"Accuracy: ok\nStatus: ok" + Style.RESET_ALL) row_num = data_merge.index for i in row_num: if data_merge['pre_valid'].loc[i] == pre_valid: itune_album_id = data_merge['itune_id'].loc[i] seq = data_merge['track_title/track_num'].loc[i] format_id = get_format_id_from_content_type( content_type=data_merge['content type'].loc[i]) youtube_url = data_merge['contribution_link'].loc[ i] db_track = get_track_title_track_artist_by_ituneid_and_seq( itune_album_id=itune_album_id, seq=seq) if db_track: track_title = db_track.title track_id = db_track.id track_duration = db_track.duration_ms track_similarity = similarity( track_title=track_title, youtube_url=youtube_url, formatid=format_id, duration=track_duration).get('similarity') else: track_title = 'not found' track_id = 'not found' track_similarity = 'not found' data_merge.loc[i, 'track_title'] = track_title data_merge.loc[i, 'track_id'] = track_id data_merge.loc[i, 'similarity'] = track_similarity else: pass updated_columns = [ '06_id', '06_status', 'e5_id', 'e5_status', 'track_title', 'track_id', 'similarity' ] print(data_merge[updated_columns]) else: print(Fore.LIGHTYELLOW_EX + f"Accuracy: not ok\nStatus: not ok" + Style.RESET_ALL) updated_columns = [ '06_id', '06_status', 'e5_id', 'e5_status' ] # update data to gsheet data_updated = data_merge[updated_columns] grid_range_to_update = f"{sheet_name}!AM2" list_result = data_updated.values.tolist( ) # transfer data_frame to 2D list update_value(list_result=list_result, grid_range_to_update=grid_range_to_update, gsheet_id=get_gsheet_id_from_url(url=url)) break else: count += 1 print( Fore.LIGHTYELLOW_EX + f"File: {gsheet_name}, sheet_name: {sheet_name} hasn't been crawled complete" + Style.RESET_ALL) time.sleep(10) print(count, "-----", result)