fungtk/VIP_sequence

1import gradio as gr2# File_Name="Full BB Working (Aug 16-18).xlsx"#@param {type:"string"}3# Sheet1="Sheet1"#@param {type:"string"}4# Sheet2="Sheet2"#@param {type:"string"}5# Sheet3="Sheet3"#@param {type:"string"}6 7def clean_data(df):8    df = df.apply(lambda x: x.str.strip() if x.dtype == "object" else x)9    columns = df.columns.str.strip()10    df.columns = columns11 12    # Clean up the GMV column by removing any commas and dollar signs13    df['GMV'] = df['GMV'].str.replace(',', '',regex=True).str.replace('$', '',regex=True)14 15    # Convert the GMV column to numeric values, replacing any invalid values with NaN16    df['GMV'] = pd.to_numeric(df['GMV'], errors='coerce').fillna(-1).replace(0, -1)17    return df18 19def cat_brand_checking(df):20    # Loop through each row and compare the value of the "Main Cat" column with the previous 5 rows21    for i in range(2, len(df)):22        if all(df.iloc[i - j]["Main Cat"] == df.iloc[i - j - 1]["Main Cat"] for j in range(5)):23            # If the previous 5 rows have the same "Main Cat" value, find the nearest row from the next row to the last row24            # that has a different category than the current row and swap the order of the rows25            next_cat_index = i + 126            while next_cat_index < len(df) and df.iloc[next_cat_index]["Main Cat"] == df.iloc[i]["Main Cat"]:27                next_cat_index += 128            if next_cat_index == len(df):29                next_cat_index -= 130 31            temp = df.iloc[i].copy()32            df.iloc[i] = df.iloc[next_cat_index]33            df.iloc[next_cat_index] = temp34 35 36        # Check each row with the previous 2 rows to see if they have the same "Brand Name" value37        if i >= 2 and all(df.iloc[i - j]["Brand Name"] == df.iloc[i - j - 1]["Brand Name"] for j in range(2)):38            # If the previous 2 rows have the same "Brand Name" value, find a row from the next row to last row39            # that has a different "Brand Name" than the current row and swap the order of the rows40            next_brand_index = i + 141            while next_brand_index < len(df) and df.iloc[next_brand_index]["Brand Name"] == df.iloc[i]["Brand Name"]:42                next_brand_index += 143            if next_brand_index == len(df):44                next_brand_index -= 145            temp = df.iloc[i].copy()46            df.iloc[i] = df.iloc[next_brand_index]47            df.iloc[next_brand_index] = temp48            #df.iloc[i:swap_index+1] = df.iloc[swap_index:i-1:-1].values49 50    return df51 52from numpy import random53import pandas as pd54from random import shuffle55import os56import datetime57def sort_excel_file(filename,sheet1,sheet2, sheet3):58    filename =filename.name59    #Clean Highlight data60    # Load the CSV file into a Pandas DataFrame, stripping leading and trailing spaces from all columns and header row61    df = pd.read_excel(filename, sheet_name=sheet1, dtype=str)62    df = clean_data(df)63    # - Highlight SKU (Around 25隻, APN 放頭4, 另外嗰啲random 放, 唔同cat 唔可以連住)64    # Filter out rows with null values in "Main APP" column65    # df_highlight = df[df['MAIN APP'].notnull()]66    # Sort rows by "Main APP" column67    # df_highlight = df_highlight.sort_values('MAIN APP')68    #Part 1 - Highlight SKU (Around 25隻, APN 放頭4, 另外嗰啲random 放, 唔同cat 唔可以連住)69    # Move rows with "APN" in "Main APP" column to the top70    # apn_mask = df_highlight['MAIN APP'].str.contains('APN')71    # apn_rows= df_highlight[apn_mask]72    # apn_rows = cat_brand_checking(apn_rows)73    # non_apn_rows =df_highlight[~apn_mask]74    # non_apn_rows = cat_brand_checking(non_apn_rows)75    df_highlight_final = df76    # df_highlight_final = pd.concat([apn_rows, non_apn_rows])77    # Part 2 - Top 150: 3PL quota (20隻) / VIP store (50隻) / 街主畀嘅 sku (around 70-80) >> random 放78    df_top_150 = pd.read_excel(filename, sheet_name=sheet2, dtype=str)79    #Remove all sku with N- in column "#""80    df_top_150["#"] = df_top_150["#"].astype(str)81    yn_mask_150 = df_top_150["#"].str.contains("N-")82    df_top_150 = df_top_150[~yn_mask_150]83    #Clean data84    df_top_150 = clean_data(df_top_150).sample(frac=1).reset_index(drop=True)85    #& Check for the same 'category' in the previous 5 rows & Check for the same 'brand' in the previous row86    df_top_150 = cat_brand_checking(df_top_150)87    # Part 3 - GMV ranking (分4個 tier: >$100K | $50k - 100k | <$1 - 50K | No GMV) >> 每個tier 入面 random 放, category & brand 可以連住; 同 category 不多過5隻, 同 brand 不多過2隻88    df_gmv_ranking = pd.read_excel(filename, sheet_name=sheet3, dtype=str)89    #Remove all sku with N- in column "#""90    df_gmv_ranking["#"] = df_gmv_ranking["#"].astype(str)91    yn_mask_gmv_ranking = df_gmv_ranking["#"].str.contains("N-")92    df_gmv_ranking = df_gmv_ranking[~yn_mask_gmv_ranking]93    df_gmv_ranking = clean_data(df_gmv_ranking)94    # define the bin edges95    bins = [-1, 1, 50000, 100000, float('inf')]96    labels = ['No GMV', '$1 - 50K', '$50k - 100k', '>$100K']97    # use the cut function to group the GMV column98    df_gmv_ranking['GMV_group'] = pd.cut(df_gmv_ranking['GMV'], bins=bins, labels=labels, include_lowest=True)99    # Specify the order of the categories100    ordered_labels = ['>$100K', '$50k - 100k', '$1 - 50K', 'No GMV']101    df_gmv_ranking['GMV_group'] = pd.Categorical(df_gmv_ranking['GMV_group'], categories=ordered_labels, ordered=True)102    # group the data frame by the GMV_group column and index by the GMV_group column103    grouped_df = df_gmv_ranking.groupby('GMV_group')104    # randomly shuffle each group and concatenate them in a new order105    shuffled_groups = []106    for name, group in grouped_df:107        #& Check for the same "category" and "brand" in the previous rows108 109        shuffled_group = cat_brand_checking(group)110        shuffled_groups.append(shuffled_group)111    shuffled_df_GMV_ranking = pd.concat(shuffled_groups)112    df_combined = pd.concat([df_highlight_final,df_top_150, shuffled_df_GMV_ranking])113 114    # Delete duplicates115    unique_df = df_combined.drop_duplicates(subset="SKU ID (Y)",keep='first')116    unique_df.loc[:, "GMV"]=unique_df["GMV"].replace( -1,0)117    #output file118    current_datetime = datetime.datetime.now()119    file = "result-" + current_datetime.strftime("%Y-%m-%d_%H-%M-%S") + ".xlsx"120 121    unique_df.to_excel(file, index=False)122 123    return file124 125with gr.Blocks() as demo:126    with gr.Row():127        with gr.Column():128            File_Name = gr.File(label="File Name", file_types=[".xlsx", ".csv"])129        with gr.Column():130            Sheet1 = gr.Textbox(label="Sheet1", value="Sheet1")131            Sheet2 = gr.Textbox(label="Sheet2", value="Sheet2")132            Sheet3 = gr.Textbox(label="Sheet3", value="Sheet3")133    with gr.Row():134        greet_btn = gr.Button("BB sequence")135 136 137    with gr.Row():138        output = gr.File(label="Output Box")139        greet_btn.click(fn=sort_excel_file, inputs=[File_Name, Sheet1, Sheet2, Sheet3], outputs=output,140                        api_name="sort_excel_file")141 142 143 144 145 146 147demo.launch()