Spaces:
Sleeping
Sleeping
| import gradio as gr | |
| import pandas as pd | |
| from openpyxl import load_workbook | |
| from openpyxl.utils.dataframe import dataframe_to_rows | |
| import tempfile | |
| def process_file(file): | |
| df = pd.read_excel(file.name) | |
| df.replace(0, pd.NA, inplace=True) | |
| with tempfile.NamedTemporaryFile(suffix=".xlsx", delete=False) as tmp: | |
| intermediate_file_path = tmp.name | |
| df.to_excel(intermediate_file_path, index=False) | |
| wb = load_workbook(intermediate_file_path) | |
| for sheet_name in wb.sheetnames: | |
| ws = wb[sheet_name] | |
| data = ws.values | |
| columns = next(data)[0:] | |
| df = pd.DataFrame(data, columns=columns) | |
| unique_states = df['Name of State'].unique() | |
| for state in unique_states: | |
| state_str = str(state) | |
| state_data = df[df['Name of State'] == state] | |
| unique_channels = state_data['Outlet Channel'].unique() | |
| mode_data = [] | |
| for channel in unique_channels: | |
| channel_str = str(channel) | |
| channel_data = state_data[state_data['Outlet Channel'] == channel] | |
| count_series = channel_data.count() | |
| mode_series = channel_data.mode().iloc[0] | |
| numeric_columns = channel_data.select_dtypes(include='number').columns | |
| max_series = channel_data[numeric_columns].max() | |
| min_series = channel_data[numeric_columns].min() | |
| combined_series = pd.concat([ | |
| count_series.rename('COUNT'), | |
| mode_series.rename('MODE'), | |
| max_series.rename('MAX'), | |
| min_series.rename('MIN') | |
| ], axis=1).T | |
| combined_series.loc[:,'STATE'] = state_str | |
| combined_series.loc[:,'CHANNEL'] = channel_str | |
| mode_data.append(combined_series) | |
| mode_df = pd.concat(mode_data) | |
| new_ws = wb.create_sheet(title=state_str) | |
| for r in dataframe_to_rows(mode_df, index=False, header=True): | |
| new_ws.append(r) | |
| output_file_path = 'state_and_channel_modes.xlsx' | |
| wb.save(output_file_path) | |
| return output_file_path | |
| def process_regional(file): | |
| df = pd.read_excel(file.name) | |
| df.replace(0, pd.NA, inplace=True) | |
| with tempfile.NamedTemporaryFile(suffix=".xlsx", delete=False) as tmp: | |
| intermediate_file_path = tmp.name | |
| df.to_excel(intermediate_file_path, index=False) | |
| wb = load_workbook(intermediate_file_path) | |
| for sheet_name in wb.sheetnames: | |
| ws = wb[sheet_name] | |
| data = ws.values | |
| columns = next(data)[0:] | |
| df = pd.DataFrame(data, columns=columns) | |
| unique_states = df['Region'].unique() | |
| for state in unique_states: | |
| state_str = str(state) | |
| state_data = df[df['Region'] == state] | |
| unique_channels = state_data['Outlet Channel'].unique() | |
| mode_data = [] | |
| for channel in unique_channels: | |
| channel_str = str(channel) | |
| channel_data = state_data[state_data['Outlet Channel'] == channel] | |
| count_series = channel_data.count() | |
| mode_series = channel_data.mode().iloc[0] | |
| numeric_columns = channel_data.select_dtypes(include='number').columns | |
| max_series = channel_data[numeric_columns].max() | |
| min_series = channel_data[numeric_columns].min() | |
| combined_series = pd.concat([ | |
| count_series.rename('COUNT'), | |
| mode_series.rename('MODE'), | |
| max_series.rename('MAX'), | |
| min_series.rename('MIN') | |
| ], axis=1).T | |
| combined_series.loc[:,'STATE'] = state_str | |
| combined_series.loc[:,'CHANNEL'] = channel_str | |
| mode_data.append(combined_series) | |
| mode_df = pd.concat(mode_data) | |
| new_ws = wb.create_sheet(title=state_str) | |
| for r in dataframe_to_rows(mode_df, index=False, header=True): | |
| new_ws.append(r) | |
| output_file_path = 'regional.xlsx' | |
| wb.save(output_file_path) | |
| return output_file_path | |
| def process_national(file): | |
| df = pd.read_excel(file.name) | |
| df.replace(0, pd.NA, inplace=True) | |
| with tempfile.NamedTemporaryFile(suffix=".xlsx", delete=False) as tmp: | |
| intermediate_file_path = tmp.name | |
| df.to_excel(intermediate_file_path, index=False) | |
| wb = load_workbook(intermediate_file_path) | |
| for sheet_name in wb.sheetnames: | |
| ws = wb[sheet_name] | |
| data = ws.values | |
| columns = next(data)[0:] | |
| df = pd.DataFrame(data, columns=columns) | |
| unique_states = df['Nation'].unique() | |
| for state in unique_states: | |
| state_str = str(state) | |
| state_data = df[df['Nation'] == state] | |
| unique_channels = state_data['Outlet Channel'].unique() | |
| mode_data = [] | |
| for channel in unique_channels: | |
| channel_str = str(channel) | |
| channel_data = state_data[state_data['Outlet Channel'] == channel] | |
| count_series = channel_data.count() | |
| mode_series = channel_data.mode().iloc[0] | |
| numeric_columns = channel_data.select_dtypes(include='number').columns | |
| max_series = channel_data[numeric_columns].max() | |
| min_series = channel_data[numeric_columns].min() | |
| combined_series = pd.concat([ | |
| count_series.rename('COUNT'), | |
| mode_series.rename('MODE'), | |
| max_series.rename('MAX'), | |
| min_series.rename('MIN') | |
| ], axis=1).T | |
| combined_series.loc[:,'STATE'] = state_str | |
| combined_series.loc[:,'CHANNEL'] = channel_str | |
| mode_data.append(combined_series) | |
| mode_df = pd.concat(mode_data) | |
| new_ws = wb.create_sheet(title=state_str) | |
| for r in dataframe_to_rows(mode_df, index=False, header=True): | |
| new_ws.append(r) | |
| output_file_path = 'national.xlsx' | |
| wb.save(output_file_path) | |
| return output_file_path | |
| iface = gr.Interface( | |
| fn=process_file, | |
| inputs=gr.File(file_types=[".xlsx"]), | |
| outputs=gr.File(), | |
| title="Excel File Processor", | |
| description="Upload an Excel file to process it and generate a new file with state and channel modes." | |
| ) | |
| iface2 = gr.Interface( | |
| fn=process_regional, | |
| inputs=gr.File(file_types=[".xlsx"]), | |
| outputs=gr.File(), | |
| title="Excel File Processor", | |
| description="Upload an Excel file to process it and generate a new file with regional and channel modes." | |
| ) | |
| iface3 = gr.Interface( | |
| fn=process_national, | |
| inputs=gr.File(file_types=[".xlsx"]), | |
| outputs=gr.File(), | |
| title="Excel File Processor", | |
| description="Upload an Excel file to process it and generate a new file with national and channel modes." | |
| ) | |
| gr.TabbedInterface([iface3, iface2, iface], tab_names=['National', 'Region', 'State']).launch(share=True) | |