excel_chat.py

import gradio as gr
from mistralai.client import MistralClient
from mistralai.models.chat_completion import ChatMessage
import os
import pandas as pd
import numpy as np


def browse_folder(url):
    if url.lower().endswith(('docs', 'docs/')):
      return gr.update(choices=[])
    response = requests.get(url)
    response.raise_for_status()  # This will raise an exception if there's an error

    soup = BeautifulSoup(response.text, 'html.parser')

    excel_links = [a['href'] + '/' for a in soup.find_all('a', href=True) if a['href'].startswith(url)]

    return gr.update(choices=excel_links)


def chat_with_mistral(source_cols, dest_col, prompt, tdoc_name, excel_file, url):

    df = pd.read_excel(excel_file)
    api_key = os.environ["MISTRAL_API_KEY"]
    model = "mistral-small"  # Use "Mistral-7B-v0.2" for "mistral-tiny"


    client = MistralClient(api_key=api_key)

    source_columns = source_cols#.split(", ")  # Split input into multiple variables
    df[dest_col] = ""
    try:
      file_name = url.split("/")[-2] + ".xlsx"
    except:
      file_name = excel_file

    if tdoc_name != '':
        filtered_df = df[df['File'] == tdoc_name]
        if not filtered_df.empty:
            concatenated_content = "\n\n".join(f"{column_name}: {filtered_df[column_name].iloc[0]}" for column_name in source_columns)
            messages = [ChatMessage(role="user", content=f"Using the following content: {concatenated_content}"), ChatMessage(role="user", content=prompt)]
            chat_response = client.chat(model=model, messages=messages)
            filtered_df.loc[filtered_df.index[0], dest_col] = chat_response.choices[0].message.content
            # Update the DataFrame with the modified row
            df.update(filtered_df)
            # Write the updated DataFrame to the Excel file
            df.to_excel(file_name, index=False)
            return file_name, df.head(5)
        else:
            return file_name, df.head(5)
    else:
        for index, row in df.iterrows():
            concatenated_content = "\n\n".join(f"{column_name}: {row[column_name]}" for column_name in source_columns)
            # Check if the concatenated content is not empty
            print('test')
            if not concatenated_content == "\n\n".join(f"{column_name}: nan" for column_name in source_columns):
                print('c bon')
                messages = [ChatMessage(role="user", content=f"Using the following content: {concatenated_content}"), ChatMessage(role="user", content=prompt)]
                chat_response = client.chat(model=model, messages=messages)
                df.at[index, dest_col] = chat_response.choices[0].message.content

        df.to_excel(file_name, index=False)
        return file_name, df.head(5)


def get_columns(file):
    if file is not None:
        df = pd.read_excel(file)
        columns = list(df.columns)
        return gr.update(choices=columns), gr.update(choices=columns), gr.update(choices=columns), gr.update(choices=columns + [""]), df.head(5)
    else:
        return gr.update(choices=[]), gr.update(choices=[]), gr.update(choices=[]), gr.update(choices=[]), pd.DataFrame()



# Categories
categories = [
{
  "topic": "Confidentiality and Privacy Protection",
  "description": "This topic covers the protection of confidentiality, privacy, and integrity in security systems. It also includes authentication and authorization processes.",
  "experts": ["Mireille"]
},
{
  "topic": "Distributed Trust and End-User Trust Models",
  "description": "This topic focuses on distributed trust models and how end-users establish trust in secure systems.",
  "experts": ["Mireille", "Khawla"]
},
{
  "topic": "Secure Element and Key Provisioning",
  "description": "This topic involves the secure element in systems and the process of key provisioning.",
  "experts": ["Mireille"]
},
{
  "topic": "Residential Gateway Security",
  "description": "This topic covers the security aspects of Residential Gateways.",
  "experts": ["Mireille"]
},
{
  "topic": "Standalone Non-Public Network (SNPN) Inter-Connection and Cybersecurity",
  "description": "This topic focuses on the inter-connection of Standalone Non-Public Networks and related cyber-security topics.",
  "experts": ["Khawla"]
},
{
  "topic": "Distributed Ledger and Blockchain in SNPN",
  "description": "This topic covers the use of distributed ledger technology and blockchain in securing Standalone Non-Public Networks.",
  "experts": ["Khawla"]
},
{
  "topic": "Distributed Networks and Communication",
  "description": "This topic involves distributed networks such as mesh networks, ad-hoc networks, and multi-hop networks, and their cyber-security aspects.",
  "experts": ["Guillaume"]
},
{
  "topic": "Swarm of Drones and Unmanned Aerial Vehicles Network Infrastructure",
  "description": "This topic covers the network infrastructure deployed by Swarm of Drones and Unmanned Aerial Vehicles.",
  "experts": ["Guillaume"]
},
{
  "topic": "USIM and Over-the-Air Services",
  "description": "This topic involves USIM and related over-the-air services such as Steering of Roaming, roaming services, network selection, and UE configuration.",
  "experts": ["Vincent"]
},
{
  "topic": "Eco-Design and Societal Impact of Technology",
  "description": "This topic covers eco-design concepts, including energy saving, energy efficiency, carbon emissions, and the societal impact of technology.",
  "experts": ["Pierre"]
},
{
  "topic": "Service Requirements of New Services",
  "description": "This topic involves defining service requirements for new services, detecting low signals of new trends and technologies, and assessing their impact on USIM services or over-the-air services.",
  "experts": ["Ly-Thanh"]
},
{
  "topic": "Satellite and Non Terrestrial Networks",
  "description": "This topic covers satellite networks, Non Terrestrial Networks, Private Networks, IoT, Inter Satellite communication, and Radio Access Network.",
  "experts": ["Nicolas"]
},
{
  "topic": "Public Safety and Emergency Communication",
  "description": "This topic involves Public Safety Communication, Military Communication, Emergency Calls, Emergency Services, Disaster Communication Access, and other related areas.",
  "experts": ["Dorin"]
}
]