Spaces:
Sleeping
Sleeping
| import os | |
| import pandas as pd | |
| import numpy as np | |
| import gradio as gr | |
| from openpyxl import Workbook, load_workbook | |
| from openpyxl.styles import PatternFill | |
| import random | |
| def generate_random_light_color(): | |
| min_brightness = 0.7 | |
| while True: | |
| r, g, b = [random.randint(128, 255) for _ in range(3)] | |
| brightness = (r * 0.299 + g * 0.587 + b * 0.114) / 255 | |
| if brightness >= min_brightness: | |
| return f'{r:02X}{g:02X}{b:02X}' | |
| def calculate_threshold(value, is_high_price=True): | |
| """ | |
| Determine the threshold based on the provided ranges. | |
| The function checks if it's for High Price or Low Price and returns the respective threshold. | |
| """ | |
| if 0 <= value <= 200: | |
| return 0.20 | |
| elif 201 <= value <= 500: | |
| return 0.70 | |
| elif 501 <= value <= 1000: | |
| return 1.0 | |
| elif 1001 <= value <= 2000: | |
| return 2.0 | |
| elif 2001 <= value <= 3000: | |
| return 3.0 | |
| elif 3001 <= value <= 4000: | |
| return 4.0 | |
| elif 4001 <= value <= 5000: | |
| return 5.0 | |
| else: | |
| return 5.0 | |
| def process_section(ws, start_row, end_row, col_index1, col_index2, output_col_index1, output_col_index2, high_threshold, low_threshold): | |
| colored_pairs = set() | |
| # Process first column | |
| for i in range(end_row, start_row - 1, -1): | |
| for j in range(i - 1, start_row - 1, -1): | |
| cell_value_i = ws.cell(i, col_index1).value | |
| cell_value_j = ws.cell(j, col_index1).value | |
| if not (isinstance(cell_value_i, (int, float)) and isinstance(cell_value_j, (int, float))): | |
| continue | |
| if abs(cell_value_i - cell_value_j) <= high_threshold: | |
| if (i, col_index1) not in colored_pairs and (j, col_index1) not in colored_pairs: | |
| color = generate_random_light_color() | |
| fill = PatternFill(start_color=color, end_color=color, fill_type="solid") | |
| ws.cell(i, col_index1).fill = fill | |
| ws.cell(j, col_index1).fill = fill | |
| colored_pairs.add((i, col_index1)) | |
| colored_pairs.add((j, col_index1)) | |
| output_value1 = max(int(cell_value_i), int(cell_value_j)) + 1 | |
| ws.cell(j, output_col_index1).value = output_value1 | |
| for k in range(j - 1, start_row - 1, -1): | |
| cell_value_k = ws.cell(k, col_index1).value | |
| if isinstance(cell_value_k, (int, float)) and cell_value_k > output_value1: | |
| if ws.cell(k, 6).value > max(cell_value_i, cell_value_j): | |
| ws.cell(j, output_col_index1).fill = fill | |
| break | |
| # Process second column | |
| for i in range(end_row, start_row - 1, -1): | |
| for j in range(i - 1, start_row - 1, -1): | |
| cell_value_i = ws.cell(i, col_index2).value | |
| cell_value_j = ws.cell(j, col_index2).value | |
| if not (isinstance(cell_value_i, (int, float)) and isinstance(cell_value_j, (int, float))): | |
| continue | |
| if abs(cell_value_i - cell_value_j) <= low_threshold: | |
| if (i, col_index2) not in colored_pairs and (j, col_index2) not in colored_pairs: | |
| color = generate_random_light_color() | |
| fill = PatternFill(start_color=color, end_color=color, fill_type="solid") | |
| ws.cell(i, col_index2).fill = fill | |
| ws.cell(j, col_index2).fill = fill | |
| colored_pairs.add((i, col_index2)) | |
| colored_pairs.add((j, col_index2)) | |
| output_value2 = min(int(cell_value_i), int(cell_value_j)) - 1 | |
| ws.cell(j, output_col_index2).value = output_value2 | |
| for k in range(j - 1, start_row - 1, -1): | |
| cell_value_k = ws.cell(k, col_index2).value | |
| if isinstance(cell_value_k, (int, float)) and cell_value_k < output_value2: | |
| if ws.cell(k, 6).value < min(cell_value_i, cell_value_j): | |
| ws.cell(j, output_col_index2).fill = fill | |
| break | |
| def highlight_pairs_with_rgb(input_file): | |
| df = pd.read_csv(input_file) | |
| df.columns = df.columns.str.strip() | |
| # Convert to Excel and add three empty columns | |
| excel_filename = input_file.replace(".csv", ".xlsx") | |
| with pd.ExcelWriter(excel_filename, engine='openpyxl') as writer: | |
| # Reorder and create Excel file with empty columns | |
| df_new = pd.concat([df.iloc[:, :8], pd.DataFrame(columns=['', '', '']), df.iloc[:, 8:]], axis=1) | |
| df_new.to_excel(writer, index=False, sheet_name="Sheet1") | |
| wb = load_workbook(excel_filename) | |
| ws = wb.active | |
| # Determine thresholds for High Price (Column 6) and Low Price (Column 7) | |
| high_price_col = df["HIGH PRICE"].dropna() | |
| low_price_col = df["LOW PRICE"].dropna() | |
| high_threshold = calculate_threshold(high_price_col.max(), is_high_price=True) | |
| low_threshold = calculate_threshold(low_price_col.min(), is_high_price=False) | |
| last_row = ws.max_row | |
| col_index1, col_index2 = 6, 7 # HIGH PRICE and LOW PRICE columns in Excel | |
| output_col_index1, output_col_index2 = 9, 10 # Empty columns added earlier | |
| ws.cell(1, output_col_index1).value = "High Result" | |
| ws.cell(1, output_col_index2).value = "Low Result" | |
| start_row = 2 | |
| while start_row <= last_row: | |
| end_row = start_row | |
| while end_row <= last_row and ws.cell(end_row, 1).value is not None: | |
| end_row += 1 | |
| end_row -= 1 | |
| process_section(ws, start_row, end_row, col_index1, col_index2, output_col_index1, output_col_index2, high_threshold, low_threshold) | |
| start_row = end_row + 2 | |
| wb.save(excel_filename) | |
| return excel_filename | |
| def gradio_interface(input_file): | |
| output_file = highlight_pairs_with_rgb(input_file.name) | |
| return output_file | |
| # Gradio app interface | |
| iface = gr.Interface( | |
| fn=gradio_interface, | |
| inputs=gr.File(label="Upload CSV File (.csv)", file_count="single"), | |
| outputs=gr.File(label="Download Processed Excel File"), | |
| title="CSV to Excel Processor with Cell Highlighting", | |
| description="Upload a CSV file with stock data, and download a processed Excel file with highlighted cells." | |
| ) | |
| if __name__ == "__main__": | |
| iface.launch() | |