Spaces:
Sleeping
Sleeping
File size: 6,391 Bytes
47b49e7 c53b63b 47b49e7 c53b63b 47b49e7 c74b3e5 47b49e7 c53b63b 47b49e7 c53b63b 47b49e7 c53b63b 47b49e7 211f41a 47b49e7 c53b63b 47b49e7 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 |
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()
|