XPMaster/data_automation
1
1import pandas as pd2import numpy as np3import re4import os5import warnings6import gradio as gr7import re8import zipfile9import datetime10import openpyxl11from openpyxl.styles import Font, PatternFill12from openpyxl.utils import column_index_from_string, get_column_letter13 14g_mapping = None15 16elems = """17#button {18/* Permalink - use to edit and share this gradient: https://colorzilla.com/gradient-editor/#f6e6b4+0,ed9017+100;Yellow+3D+%231 */19background: #f6e6b4; /* Old browsers */20background: -moz-linear-gradient(top, #f6e6b4 0%, #ed9017 100%); /* FF3.6-15 */21background: -webkit-linear-gradient(top, #f6e6b4 0%,#ed9017 100%); /* Chrome10-25,Safari5.1-6 */22background: linear-gradient(to bottom, #f6e6b4 0%,#ed9017 100%); /* W3C, IE10+, FF16+, Chrome26+, Opera12+, Safari7+ */23filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#f6e6b4', endColorstr='#ed9017',GradientType=0 ); /* IE6-9 */24text-shadow: 2px 2px 10px #000000;25}26"""27 28def download_csv_as_dataframe(url):29 import io30 import pandas as pd31 import requests32 if 'drive.google.com' in url:33 # Google Drive link34 file_id = url.split('/')[-2]35 download_url = f'https://drive.google.com/uc?id={file_id}'36 elif 'docs.google.com/spreadsheets' in url:37 # Google Sheets link38 file_id = url.split('/')[-2]39 download_url = f'https://docs.google.com/spreadsheets/d/{file_id}/export?format=csv'40 else:41 print('Invalid URL')42 return None43 # Send a GET request to download the file44 response = requests.get(download_url)45 # Read the content as CSV and convert to DataFrame46 content = response.content.decode('utf-8')47 df = pd.read_csv(io.StringIO(content))48 return df49 50def map_names(odf,fname):51 global g_mapping52 msg = None53 if g_mapping is None:54 g_mapping = download_csv_as_dataframe('https://docs.google.com/spreadsheets/d/1rVoLrrTEDzU79x2H2Z1lJ7-z_jRbt-NMUdTarjLvSGo/edit?usp=drive_link')55 mapping = g_mapping#pd.read_csv("data_automation_mapping.csv")56 fname = fname.lower()57 ftype = next((element for element in [x for x in list(mapping['type'].unique())] if element.lower() in fname), None)58 fcompany = next((element for element in [x for x in list(mapping['company'].unique())] if element.lower() in fname), None)59 mapped_frame = None60 if ftype is not None and fcompany is not None:61 print(fname,"has been successfully remapped")62 query_result = mapping[(mapping['type'].str.lower() == ftype.lower()) & (mapping['company'].str.lower() == fcompany.lower())]63 mapped_frame = query_result64 65 for index, row in mapped_frame.iterrows():66 original_val = row['original']67 rename_val = row['rename']68 odf = odf.replace(original_val, rename_val)69 #display(odf)70 mapped_frame = odf71 else:72 mapped_frame = odf73 msg = ' LOB has not been mapped for this file as name must have insurance line of business type (example: as_motor_summary.csv)'74 print(msg)75 return mapped_frame,msg76 77def get_lob(df):78 global g_mapping79 if g_mapping is None:80 g_mapping = download_csv_as_dataframe('https://docs.google.com/spreadsheets/d/1rVoLrrTEDzU79x2H2Z1lJ7-z_jRbt-NMUdTarjLvSGo/edit?usp=drive_link')81 mapping = g_mapping82 83 column_names = set(df.columns)84 best_match_col = None85 max_matches = 086 for pattern in ["lob", "market_segment", "product", "class_of_business", 'type']:87 matching_columns = {col for col in column_names if pattern in col.lower()}88 89 for col in matching_columns:90 matches = sum(df[col].isin(g_mapping['original']))91 if matches > max_matches:92 best_match_col = col93 max_matches = matches94 column_names -= matching_columns95 return best_match_col if max_matches > 0 else None96 97def get_paid_amount(df):98 for col in df.columns:99 # Replace "Gross" with "amount" in column name100 if "Gross" in col or "gross" in col:101 new_col = col.replace("Gross", "amount").replace("gross", "amount")102 else:103 new_col = col104 # If "paid" and "amount" are in the column name, return the column name105 if "paid" in new_col.lower() and "amount" in new_col.lower():106 return col107 # If "paid" and "claim" are in the column name, return the column name108 if "paid" in new_col.lower() and "claim" in new_col.lower():109 return col110 return None111 112def get_gross_os(df):113 for col in df.columns:114 if 'ri' in col.lower():115 continue116 new_col = col.replace("gross", "amount").replace("Gross", "Amount")117 if "amount" in new_col.lower() and "os" in new_col.lower():118 return col119 if "os" in new_col.lower() and "claim" in new_col.lower():120 return col121 return None122 123def get_recover_os(df):124 for col in df.columns:125 # If "recover" and "os" are in the column name, return the column name126 if "recover" in col.lower() and "os" in col.lower() and "ed" not in col.lower():127 return col128 return None129 130def get_gross_recoveries(df):131 for col in df.columns:132 # Replace "settled" with "amount" in column name133 new_col = col.replace("settled", "amount").replace("Settled", "Amount")134 # If "recover" and "amount" are in the column name, return the column name135 if "recover" in new_col.lower() and "amount" in new_col.lower():136 return col137 # If "gross" and "recover" are in the column name, return the column name138 if "gross" in new_col.lower() and "recover" in new_col.lower():139 return col140 return None141 142def get_claim_count(df):143 for col in df.columns:144 # If "claim" and "count" are in the column name, return the column name145 if "claim" in col.lower() and "count" in col.lower():146 return col147 return None148 149def get_quarter_bracket(df):150 columns = df.columns151 for col in columns:152 if col.lower() == "quarter_bracket":153 return col154 return None155 156def get_earned(df):157 for col in df.columns:158 # If "GEP" is in the column name, return the column name159 if "gep" in col.lower():160 return col161 # If "premium" and "earned" are in the column name, return the column name162 if "premium" in col.lower() and "earned" in col.lower():163 return col164 return None165 166def get_erp(df):167 for col in df.columns:168 # If "ERP" is in the column name, return the column name169 if "erp" in col.lower():170 return col171 return None172 173def quarters(df):174 valid_cols = []175 df = df.applymap(lambda x: str(int(x)) if isinstance(x, (int, float)) and str(x) != 'nan' else str(x))176 for col in df.columns:177 # Check if all values in column are either 'nan' or numeric178 if all(df[col].apply(lambda x: str(x).isnumeric() or str(x) == 'nan')):179 # Check if column has at least one value with length of 6180 if any(df[col].apply(lambda x: len(str(x))) == 6):181 # Check if all non-zero numeric values end with '03', '06', '09', or '12'182 filtered = df[df[col] != '0']183 filtered = filtered[filtered[col].apply(lambda x: str(x).isnumeric())]184 if filtered[col].apply(lambda x: x[-2:]).isin(['03', '06', '09', '12']).all():185 valid_cols.append(col)186 valid_cols = [elem for elem in valid_cols if "report" not in elem.lower() if "effect" not in elem.lower()]187 return valid_cols188 189def col_to_ints(df,columns_to_convert):190 for col in columns_to_convert:191 df[col] = df[col].apply(lambda x: str(int(x)) if isinstance(x, (int, float)) and str(x) != 'nan' else str(x))192 return df193 194def fill_missing_quarters(df, lob, acc, transaction):195 filled = []196 missing_count = 0197 lobs_dict = dict()198 print('accident',acc,'transaction',transaction)199 columns_to_convert = [acc,transaction] # Only affect acc and transaction200 201 print('Number of NaN values in', acc, ':', df[acc].isna().sum())202 print('Number of NaN values in', transaction, ':', df[transaction].isna().sum())203 for col in columns_to_convert:204 df[col] = df[col].apply(lambda x: str(int(x)) if isinstance(x, (int, float)) and str(x) != 'nan' else str(x))205 206 quarters = []207 start_year = 2017208 end_year = 2022209 # df_temp = df.copy(deep=True)210 # df_temp = df_temp.dropna()211 end_year = min(int(df[acc].max()[:4]), 2022)212 print("the end year", end_year)213 print("safe and sound")214 for year in range(start_year, end_year+1):215 for quarter in ['03', '06', '09', '12']:216 quarters.append(str(year) + quarter)217 # Find the missing quarters by LOB218 missing_quarters = []219 for l in df[lob].unique():220 l_df = df[df[lob] == l]221 l_quarters = set(quarters) - set(l_df[acc])222 l_missing_df = pd.DataFrame({acc: list(l_quarters),223 transaction: [str(end_year)+'12'] * len(l_quarters)})224 for col in df.columns: # Fill the missing225 #print("\n"*5,col,transaction)226 if col != lob: # These two checks are nesscary in case we are filling for the premium then we only fill it with the missing quarters without the 202212 for transactions227 if col == acc:228 l_missing_df[col] = list(l_quarters)229 elif str(col) == str(transaction):230 l_missing_df[col] = [str(end_year) + '12'] * len(l_quarters)231 else:232 # Pad233 l_missing_df[col] = 0.1234 # Count padding per lob235 if col not in lobs_dict:236 lobs_dict[col] = 0237 lobs_dict[col] = 0.1 + lobs_dict[col]238 # Count total paddings239 missing_count = missing_count + 1240 241 if len(l_quarters) > 0 :242 filled_warn = str(l)+' was filled with the dates '+str(l_quarters)243 print(filled_warn)244 filled.append(filled_warn)245 246 l_missing_df[lob] = l247 missing_quarters.append(l_missing_df)248 249 filled.append([lobs_dict.keys(),lobs_dict.values()])250 #filled.append("Total paddings (0.1): "+str(missing_count))251 print("=="*100)252 print('Unique values in', acc, 'for missing quarters:', l_missing_df[acc].unique())253 # Concatenate the original dataframe and the missing quarters dataframe254 filled_df = pd.concat([df] + missing_quarters, ignore_index=True)255 print('Number of NaN values in', acc, 'after concatenation:', filled_df[acc].isna().sum())256 257 print('Unique values in', acc, 'before conversion:', filled_df[acc].unique())258 # Convert the 'accident_quarter_bracket' column to datetime format259 filled_df[acc] = pd.to_datetime(filled_df[acc], format='%Y%m').dt.strftime('%Y%m')260 print('Unique values in', acc, 'after conversion:', filled_df[acc].unique())261 262 print("=="*100)263 # Sort the dataframe by quarter264 filled_df = filled_df.sort_values(acc)265 # Reset the index266 filled_df = filled_df.reset_index(drop=True)267 # Print the filled quarters or a message if there are no missing quarters268 filled_quarters = filled_df[acc].unique()269 filtered_quarters = [q for q in filled_quarters if q[:4] in [str(year1) for year1 in range(start_year, end_year + 1)]]270 if len(filtered_quarters) == 0:271 msg = "No missing quarters between "+start_year+"-"+str(end_year)272 print(msg)273 filled.append(msg)274 else:275 pass#print(filtered_quarters)276 277 #filled_df = filled_df[[acc, transaction] + [col for col in filled_df.columns if col not in [acc, transaction]]]278 return filled_df,filled279 280def drop_missing_rows(df, columns):281 #import sys282 removed_rows = df[df[columns].isnull().any(axis=1)]283 #display(removed_rows)284 print("LOB NAME", columns[0])285 #sys.exit()286 removed_rows = df[df[columns].isnull().any(axis=1)].dropna(subset=columns[0], how='any')287 removed_rows = removed_rows[removed_rows[columns].isnull().any(axis=1)].dropna(subset=columns[0], how='any')288 df = df.dropna(subset=columns, how='any')289 return df,removed_rows290 291 292# def write_log(sheet_data_dict):293# workbook = openpyxl.Workbook()294# max_sheet_name_length = 31295# for sheet_name, data_dict in sheet_data_dict.items():296# sheet_name = sheet_name[:max_sheet_name_length]297# sheet = workbook.create_sheet(title=sheet_name)298 299# col_index = 1 # Start from column 1 (A), column 0 does not exist in Excel300# adjacent_col_index = 2 # Initialize adjacent column index to 2 (B)301# row_index = 1 # Initialize row index to 1 to start writing from the first row302 303# for title, data in data_dict.items():304# lst, color = data[0], (data[1] if len(data) > 1 else None)305# adjacent = data[2] if len(data) > 2 else False306 307# if adjacent:308# write_col_index = adjacent_col_index # Use adjacent column309# adjacent_col_index += 1 # Increment adjacent column index for next adjacent data310# else:311# write_col_index = col_index # Use column 1 (A) for non-adjacent data312# row_index = sheet.max_row + 1 if sheet.max_row > 0 else 1 # Start from next available row in column 1313 314# # Write title315# title_cell = sheet.cell(row=row_index, column=write_col_index)316# title_cell.value = title317# title_cell.font = Font(size=14, bold=True)318 319# # Write list items and apply color320# for item_index, item in enumerate(lst, start=row_index + 1):321# cell = sheet.cell(row=item_index, column=write_col_index)322# cell.value = item323# if color:324# fill = PatternFill(start_color=color, end_color=color, fill_type="solid")325# cell.fill = fill326 327# # Adjust column width328# max_length = 0329# for cell in sheet[get_column_letter(write_col_index)]:330# try:331# if len(str(cell.value)) > max_length:332# max_length = len(cell.value)333# except:334# pass335# adjusted_width = (max_length + 2)336# sheet.column_dimensions[get_column_letter(write_col_index)].width = adjusted_width337 338# if "Sheet" in workbook.sheetnames:339# workbook.remove(workbook["Sheet"])340# workbook.save('Log.xlsx')341 342def write_log(sheet_data_dict):343 workbook = openpyxl.Workbook()344 max_sheet_name_length = 31345 346 for sheet_name, data_dict in sheet_data_dict.items():347 sheet_name = sheet_name[:max_sheet_name_length]348 sheet = workbook.create_sheet(title=sheet_name)349 350 col_index = 1351 adjacent_col_index = 1352 start_row_index = 1353 354 for title, data in data_dict.items():355 lst, color = data[0], (data[1] if len(data) > 1 else None)356 adjacent = data[2] if len(data) > 2 else False357 358 if adjacent:359 adjacent_col_index += 1 # Move to the next column for adjacent data360 write_col_index = adjacent_col_index # Write data in the adjacent column361 else:362 col_index = 1 # Reset to column 1 (A) for non-adjacent data363 adjacent_col_index = col_index # Reset adjacent column index364 write_col_index = col_index # Write data in column 1 (A)365 start_row_index = sheet.max_row + 1 if sheet.max_row > 0 else 1 # Start from the next available row in column 1 (A)366 367 # Write the title368 title_cell = sheet.cell(row=start_row_index, column=write_col_index)369 title_cell.value = title370 title_cell.font = Font(size=14, bold=True)371 372 # Write list items and apply color373 for item_index, item in enumerate(lst, start=start_row_index + 1):374 cell = sheet.cell(row=item_index, column=write_col_index)375 cell.value = item376 if color:377 fill = PatternFill(start_color=color, end_color=color, fill_type="solid")378 cell.fill = fill379 380 # Adjust the column width381 max_length = max(len(str(val)) for val in [title, *lst])382 adjusted_width = (max_length + 2)383 sheet.column_dimensions[get_column_letter(write_col_index)].width = adjusted_width384 385 if "Sheet" in workbook.sheetnames:386 workbook.remove(workbook["Sheet"])387 388 workbook.save('Log.xlsx')389 390def column_letter(index):391 """Convert a column index into a column letter"""392 letters = ""393 while index > 0:394 index, remainder = divmod(index - 1, 26)395 letters = chr(65 + remainder) + letters396 return letters397 398warnings = []399def is_found(c,text):400 global warnings401 if c[-1] == None:402 warnings.append(text+" was not found")403 404def get_alts(atype):405 if atype == 'claim':406 return ['lob','accident_quarter_bracket','transaction_quarter_bracket','paid_amount','gross_recoveries_settled','os_amount','gross_os_recoveries','claim_count']407 return ['lob','quarter_bracket','gross_premium_earned','ERP']408 409def filter_claims(df): 410 print("Sum of Null beginning: ",df.isnull().sum())411 print("Sum of Null beginning 2: ",(df == '').sum())412 print(df.dtypes)413 filled_warn = []414 global warnings415 warnings = []416 columns = []417 # Find lob418 columns.append(get_lob(df))419 is_found(columns,"lob")420 if None in columns:421 return None,None422 # Find quarters423 sublist = quarters(df)424 print("\n"*10,sublist,"\n"*10)425 columns.extend(sublist)426 # min_col = min(sublist, key=lambda col: df.dropna()[col].sum())427 # max_col = max(sublist, key=lambda col: df.dropna()[col].sum())428 min_col = df[sublist].sum().idxmin()429 max_col = [col for col in sublist if col != min_col][0]430 df,temp = drop_missing_rows(df,columns)431 print('missing: ',df[df.columns[1]].isnull().sum())432 #df.to_csv("gayassshit.csv")433 #temp.to_csv("gayassshit1.csv")434 #df.to_csv("before_filling.csv")435 #print("\n"*10,columns[0],min_col,max_col,"\n"*10)436 df, filled_warn = fill_missing_quarters(df,columns[0],min_col,max_col)437 #df.to_csv("after_filling.csv")438 #print(columns[0],min_col,max_col)439 #temp = fill_missing_quarters(temp,columns[0],min_col,max_col)440 df = col_to_ints(df,sublist)441 #df = df[[min_col, max_col] + [col for col in df.columns if col not in [min_col, max_col]]]442 #display(df)443 min_col_index = columns.index(min_col) # Find the index of min_col444 max_col_index = columns.index(max_col) # Find the index of max_col445 # Rearrange the columns list446 if min_col_index > max_col_index:447 columns.insert(max_col_index, columns.pop(min_col_index))448 449 is_found(columns,"quarters")450 # Find paid amount451 columns.append(get_paid_amount(df))452 is_found(columns,"paid amount")453 # Find gross recoveries454 columns.append(get_gross_recoveries(df))455 is_found(columns,"gross recoveries")456 # Find gross os457 columns.append(get_gross_os(df))458 is_found(columns,"gross os")459 # Find recover os460 columns.append(get_recover_os(df))461 is_found(columns,"recover os")462 # Find claims count463 columns.append(get_claim_count(df))464 is_found(columns,"claim count")465 # Warn466 for i,w in enumerate(warnings):467 print(str(i+1)+'-',w)468 469 #df = pd.concat([df, temp], ignore_index=True)470 471 df = df.replace('nan',0)472 df = df.fillna({col: 0 for col in df.columns if col not in sublist})473 return df,columns,temp,filled_warn474 475def filter_premiums(df):476 global warnings477 warnings = []478 columns = []479 filled_warn = []480 # Find lob481 columns.append(get_lob(df))482 is_found(columns,"lob")483 if None in columns:484 return None,None485 # Find quarter bracket486 columns.append(get_quarter_bracket(df))487 df,filled_warn = fill_missing_quarters(df,columns[0],columns[-1],columns[-1])488 is_found(columns,"quarter")489 # Find premium earned490 columns.append(get_earned(df))491 is_found(columns,"premium earned")492 # Find ERP493 columns.append(get_erp(df))494 is_found(columns,"ERP")495 # Warn496 for i,w in enumerate(warnings):497 print(str(i+1)+'-',w)498 return df,columns,filled_warn499 500css_code='body{background-image:url("https://picsum.photos/seed/picsum/200/300");}'501 502 503# def unzip_files(zip_file_path):504# file_extension = os.path.splitext(zip_file_path)[1]505# if file_extension == '.zip':506# with zipfile.ZipFile(zip_file_path, 'r') as zip_ref:507# file_list = zip_ref.namelist()508# csv_excel_files = [file for file in file_list if file.endswith(('.csv', '.xls', '.xlsx'))]509# return csv_excel_files510# else:511# return [zip_file_path]512 513def unzip_files(zip_file_path):514 file_extension = os.path.splitext(zip_file_path)[1]515 if file_extension == '.zip':516 with zipfile.ZipFile(zip_file_path, 'r') as zip_ref:517 file_list = zip_ref.namelist()518 csv_excel_files = [file for file in file_list if file.endswith(('.csv', '.xls', '.xlsx'))]519 extracted_files = []520 for file in csv_excel_files:521 zip_ref.extract(file)522 extracted_files.append(file)523 524 return extracted_files525 else:526 return [zip_file_path]527 528def zip_files(file_paths):529 530 current_date = datetime.datetime.now().strftime("%Y-%m-%d_%H-%M")531 new_file_name = f"processed_files_{current_date}.zip"532 533 with zipfile.ZipFile(new_file_name, 'w') as zipf:534 for file_path in file_paths:535 file_name = file_path.split('/')[-1]536 zipf.write(file_path, file_name)537 538 print(f"{len(file_paths)} files compressed and saved as '{new_file_name}'.")539 return new_file_name540 541 542def valid(text):543 file_extensions = [".zip", ".xlsx", ".csv"]544 pattern = r"\b({})\b".format("|".join(map(re.escape, file_extensions)))545 match = re.search(pattern, text, flags=re.IGNORECASE)546 return bool(match)547 548def op_outcome(name,msg):549 name = os.path.basename(name)550 return name+msg551 552def process(files,button):553 global warnings554 fail = ' ❌\n'555 passe = ' ✔️\n'556 warn = ' ⚠️\n'557 status = []558 cleaned_names = []559 if files is None:560 msg = 'No file provided'+fail561 return None, msg562 563 names = unzip_files(files.name)564 sheet_data = dict()565 566 for name in names:567 #name = os.path.basename(name)568 if valid(name):569 # return zip_files([files.name]),'Success'+passe570 temp = None571 columns = []572 filled_warn = []573 replacens = dict()574 print("Processing:", name)575 576 try:577 df = pd.read_csv(name)578 except:579 df = pd.read_excel(name)580 581 old_cols = df.columns582 old_olds = list(old_cols)583 sums_old = ['{:,.2f}'.format(df[col].sum()) if np.issubdtype(df[col].dtype, np.number) else "-" for col in old_cols]584 print("Before columns")585 print(old_olds)586 587 if "summ" in name:588 print("Summary:")589 df,columns,filled_warn = filter_premiums(df)590 if columns == None:591 print(name,'has no LOB column')592 print("--"*50)593 status.append(op_outcome(name,' has no LOB column'+fail))594 continue595 altnames = get_alts('summ')596 else:597 print("Claims:")598 df,columns,temp,filled_warn = filter_claims(df)599 if columns == None:600 print(name,'has no LOB column')601 print("--"*50)602 status.append(op_outcome(name,' has no LOB column'+fail))603 continue604 altnames = get_alts('claim')605 606 finalnames = []607 for ind,col in enumerate(columns):608 if col is not None:609 finalnames.append(columns[ind]+" ("+altnames[ind]+")")610 columns = [x for x in columns if x is not None]611 612 613 print("After columns")614 print(columns)615 616 df, msg = map_names(df,name)617 df = df[columns]618 print("temp",temp)619 if isinstance(temp,pd.DataFrame):620 temp, _ = map_names(temp,name)621 temp = temp[columns]622 temp = temp[temp.iloc[:, 3:].sum(axis=1) != 0]623 df = pd.concat([df, temp], ignore_index=True)624 column_mapping = dict(zip(columns, finalnames))625 df = df.rename(columns=column_mapping)626 # sum new627 ncols = df.columns628 sums_new = ['{:,.2f}'.format(df[col].sum()) if np.issubdtype(df[col].dtype, np.number) else "-" for col in ncols]629 #display(df)630 name = os.path.basename(name)631 #print(columns)632 #print(warnings)633 sheetwarnings = [['No warnings'],'00FF00']634 if len(warnings) > 0:635 sheetwarnings = [warnings,'FFA500']636 637 filled_warn.pop(-1)638 if len(filled_warn) == 0:639 filled_warn = ['No fillings']640 # else:641 # # tempt_list = [element for element in filled_warn[-2][0] if element in columns]642 # # filled_warn[-2] = "Padded columns "+str(list(tempt_list))+" with total of "+str(round(filled_warn[-2][1],3))+" each"643 # pass644 # fillings_amounts = filled_warn[-1][1]645 646 sheet_data[name] = {647 "Before columns": [old_olds],648 'Sum Before':[sums_old,None,True],649 "After columns": [ncols, '00FF00'],650 'Sum After':[sums_new,None,True],651 #'Filling amount':[fillings_amounts,None,True],652 'Fillings':[filled_warn,None],653 "Warnings": sheetwarnings654 } 655 656 c_name = name.split('.')[0]+'_cleaned.csv'657 df.to_csv(c_name,index=False)658 659 cleaned_names.append(c_name)660 661 formatted_warnings = ''662 if len(warnings) > 0:663 formatted_warnings = '📝:\n'+'\n'.join(warnings)664 if msg == None:665 status.append(op_outcome(name,' was processed'+passe+formatted_warnings))666 else:667 status.append(op_outcome(name,msg+warn+formatted_warnings))668 else:669 name = os.path.basename(name)670 status.append(op_outcome(name,' Failed (Only .csv, .xlsx, .zip are allowed)'+fail))671 672 if len(cleaned_names) > 0:673 write_log(sheet_data)674 cleaned_names.append('Log.xlsx')675 final_file = zip_files(cleaned_names)676 else:677 final_file = None678 msg = '\n'.join(f"{index + 1}.{value}" for index, value in enumerate(status))679 680 return gr.File.update(value=final_file,visible=True),msg681 #return(str(files)+'fole')682 683 684with gr.Blocks(css=elems) as demo:685 gr.Markdown(686 """687 <style>688 .inline-container {689 display: flex;690 align-items: center;691 }692 .zip-line {693 margin-top: 20px;694 position: relative;695 }696 .zip-line img {697 position: absolute;698 top: 0;699 left: 0;700 }701 </style>702 703 <div class="inline-container">704 <img src="https://mustafasa.com/uploads/excel_sheet.png" alt="Excel Sheet" width="50px">705 <h1>Upload a singular xlsx/csv file to clean</h1>706 </div>707 <div class="inline-container zip-line">708 <img src="https://mustafasa.com/uploads/zip_icon.png" alt="Zip Icon" width="50px">709 <img src="https://mustafasa.com/uploads/excel_sheet.png" alt="Excel Sheet" width="20px">710 <h1 style="margin-left: 50px;">Or upload multiple compressed into a zip file</h1>711 </div>712 """713 )714 715 with gr.Row():716 inp = gr.File(label='Input file/s')717 with gr.Row():718 bt = gr.Button(value='🧹 Clean',elem_id='button')719 #bt1 = gr.Button(value='Restart')720 721 for _ in range(2):722 with gr.Row():723 pass724 725 with gr.Row():726 out = gr.File(label='Cleaned files',visible=False)727 with gr.Row():728 log = gr.Textbox(label='Process log 📄',visible=True)729 730 bt.click(fn = process, inputs=[inp,bt], outputs=[out,log])731 732 733demo.launch(debug=True)