Skip to content
TheChatArchiveThe Chat Archive
o1-mini-2024-09-12· OpenAIAi2 WildChat-4.8M (AI2)

I have an excel sheet which has these columns : Title,Link,Date and Content. It is a…

  1. User

    I have an excel sheet which has these columns : Title,Link,Date and Content. It is a result from news site scrapping. I want to use local AI models using ollama to take each content and condence it into a 1 to 3 min script, the script should be easily usable for TTS, while keeping all the important information as it i, put more weight in facutality as nothing is to be changed.If the content is already small, dont do much changes. After that, all the condenced scripts should be stored in the new excel file which has the original excel and appending the scripts in it.

  2. Assistant

    Certainly! Below is a comprehensive guide to help you achieve your goal of condensing news content using local AI models via Ollama and storing the results in a new Excel file. This guide includes step-by-step instructions, code examples (using Python), and best practices to ensure the process is efficient and reliable.

    Overview

    1. Prerequisites
    2. Setting Up the Environment
    3. Reading the Original Excel File
    4. Interfacing with Ollama
    5. Processing Each Content Entry
    6. Storing the Condensed Scripts
    7. Exporting to a New Excel File
    8. Complete Python Script
    9. Best Practices and Tips

    1. Prerequisites

    Before diving into the implementation, ensure you have the following:

    • Python Installed: Preferably Python 3.7 or later.
    • Ollama Installed and Configured: Ensure you have Ollama set up on your local machine with the desired AI model.
    • Necessary Python Libraries: pandas for Excel operations and subprocess for interfacing with Ollama.

    2. Setting Up the Environment

    1. Install Python Dependencies

      Open your terminal or command prompt and install the required Python libraries:

      pip install pandas openpyxl
      
      • pandas: For reading and writing Excel files.
      • openpyxl: Engine used by pandas to handle Excel files.
    2. Ensure Ollama is Accessible

      Make sure the ollama CLI is accessible from your terminal. You should be able to run ollama --help without errors.

    3. Reading the Original Excel File

    We'll use pandas to read the existing Excel file containing the columns: Title, Link, Date, and Content.

    import pandas as pd
    
    # Path to your original Excel file
    input_excel_path = 'path_to_your_input_file.xlsx'
    
    # Read the Excel file
    df = pd.read_excel(input_excel_path)
    
    # Display the first few rows to verify
    print(df.head())
    

    4. Interfacing with Ollama

    To interact with Ollama, we'll utilize Python's subprocess module to execute CLI commands. Here's a function to send content to Ollama and retrieve the condensed script.

    import subprocess
    
    def condense_content_with_ollama(content, model_name='your_model_name', max_tokens=300):
        """
        Sends content to Ollama for condensation and returns the script.
        
        Parameters:
        - content (str): The original news content.
        - model_name (str): The name of the Ollama model to use.
        - max_tokens (int): Approximate length of the condensed script.
    
        Returns:
        - script (str): The condensed script for TTS.
        """
        # Define your prompt
        prompt = (
            "Condense the following news content into a 1 to 3-minute script suitable for text-to-speech. "
            "Ensure all important information is retained with a strong emphasis on factual accuracy. "
            "If the content is already concise, make minimal changes."
            f"\n\nOriginal Content:\n{content}"
        )
        
        try:
            # Execute the Ollama command
            # Replace 'ollama_model_command' with the actual command to run your model
            # For example: ollama run <model_name> --prompt "<prompt>"
            result = subprocess.run(
                [
                    'ollama',
                    'run',
                    model_name,
                    '--prompt',
                    prompt,
                    '--max-tokens',
                    str(max_tokens)
                ],
                capture_output=True,
                text=True,
                check=True
            )
            
            # Extract the output
            script = result.stdout.strip()
            return script
        
        except subprocess.CalledProcessError as e:
            print(f"Error processing content: {e}")
            return ""
    

    Notes:

    • Model Name: Replace 'your_model_name' with the actual name of the model you intend to use in Ollama.
    • Max Tokens: Adjust max_tokens based on the desired length of the script. Since it's for a 1-3 minute TTS script, 300 tokens is a reasonable starting point.
    • Error Handling: The function includes basic error handling. Depending on the size of your dataset, you might want to implement more robust logging or retry mechanisms.

    5. Processing Each Content Entry

    Iterate through each row in the DataFrame, process the 'Content' column, and store the condensed script.

    import time
    
    # Initialize a list to store the scripts
    scripts = []
    
    # Iterate through each row in the DataFrame
    for index, row in df.iterrows():
        original_content = row['Content']
        
        # Check the length of the content
        # Define a threshold below which minimal changes are needed
        # For example, if content has fewer than 300 words, consider it already concise
        word_count = len(original_content.split())
        
        if word_count < 300:
            script = original_content  # No significant changes
        else:
            # Condense the content using Ollama
            script = condense_content_with_ollama(original_content)
            # Optional: Add a slight delay to prevent overloading the system
            time.sleep(1)  # Adjust based on your system's capability
        
        scripts.append(script)
        
        # Optional: Print progress
        if (index + 1) % 10 == 0:
            print(f"Processed {index + 1} rows")
    
    # Add the scripts to the DataFrame
    df['Script'] = scripts
    

    Considerations:

    • Threshold for Condensation: The example uses 300 words as a threshold. Adjust this based on your specific needs.
    • Rate Limiting: Depending on the performance of your local machine and Ollama, you might need to adjust the time.sleep(1) delay to prevent resource exhaustion.
    • Progress Monitoring: Printing progress every 10 rows helps in monitoring long-running processes.

    6. Storing the Condensed Scripts

    After processing, the scripts list is appended to the original DataFrame as a new column named 'Script'.

    # This step has already been done in the previous section
    # df['Script'] = scripts
    

    7. Exporting to a New Excel File

    Finally, save the updated DataFrame to a new Excel file, preserving the original data and adding the new 'Script' column.

    # Path to your output Excel file
    output_excel_path = 'path_to_your_output_file.xlsx'
    
    # Write the DataFrame to a new Excel file
    df.to_excel(output_excel_path, index=False)
    
    print(f"Condensed scripts have been saved to {output_excel_path}")
    

    8. Complete Python Script

    For convenience, here's the entire process consolidated into a single Python script. Ensure you customize paths and model names as per your setup.

    import pandas as pd
    import subprocess
    import time
    
    def condense_content_with_ollama(content, model_name='your_model_name', max_tokens=300):
        """
        Condenses content using Ollama.
        """
        prompt = (
            "Condense the following news content into a 1 to 3-minute script suitable for text-to-speech. "
            "Ensure all important information is retained with a strong emphasis on factual accuracy. "
            "If the content is already concise, make minimal changes."
            f"\n\nOriginal Content:\n{content}"
        )
        
        try:
            result = subprocess.run(
                [
                    'ollama',
                    'run',
                    model_name,
                    '--prompt',
                    prompt,
                    '--max-tokens',
                    str(max_tokens)
                ],
                capture_output=True,
                text=True,
                check=True
            )
            script = result.stdout.strip()
            return script
        except subprocess.CalledProcessError as e:
            print(f"Error processing content: {e}")
            return ""
    
    def main():
        # Paths to input and output Excel files
        input_excel_path = 'input_news.xlsx'
        output_excel_path = 'output_news_with_scripts.xlsx'
        
        # Read the original Excel file
        df = pd.read_excel(input_excel_path)
        
        # Initialize list to store scripts
        scripts = []
        
        # Iterate through each row
        for index, row in df.iterrows():
            original_content = row['Content']
            word_count = len(original_content.split())
            
            if word_count < 300:
                script = original_content
            else:
                script = condense_content_with_ollama(original_content)
                time.sleep(1)  # Delay to manage resource usage
            
            scripts.append(script)
            
            # Progress update
            if (index + 1) % 10 == 0:
                print(f"Processed {index + 1} rows")
        
        # Add the scripts to the DataFrame
        df['Script'] = scripts
        
        # Save to a new Excel file
        df.to_excel(output_excel_path, index=False)
        print(f"Condensed scripts have been saved to {output_excel_path}")
    
    if __name__ == "__main__":
        main()
    

    Instructions:

    1. Customize the Script:

      • Replace 'your_model_name' with your actual Ollama model name.
      • Update 'input_news.xlsx' and 'output_news_with_scripts.xlsx' with your actual file paths.
    2. Run the Script: Save the script to a file, e.g., condense_news.py, and execute it using:

      python condense_news.py
      

    9. Best Practices and Tips

    • Testing: Before running the script on the entire dataset, test it with a small subset to ensure everything works as expected.

    • Logging: Implement logging to capture detailed information, especially if processing a large number of entries. Python's logging module can be beneficial.

      import logging
      
      # Configure logging
      logging.basicConfig(filename='condensation.log', level=logging.INFO,
                          format='%(asctime)s:%(levelname)s:%(message)s')
      
      # Replace print statements with logging
      logging.info(f"Processed {index + 1} rows")
      logging.error(f"Error processing content: {e}")
      
    • Error Handling: Enhance error handling to manage specific exceptions and possibly retry failed attempts.

    • Parallel Processing: For very large datasets, consider parallelizing the process. However, be cautious with system resources as running multiple instances of Ollama can be resource-intensive.

    • Resource Management: Monitor CPU and memory usage during processing to ensure your system remains responsive.

    • Backup Data: Always keep a backup of your original Excel file to prevent data loss.

    • Validate Output: After processing, randomly check some of the condensed scripts to ensure quality and accuracy.


    By following this guide, you should be able to efficiently condense your news content using local AI models via Ollama and store the results in a new Excel file suitable for text-to-speech applications. Adjust the parameters and enhance the script as needed to fit your specific requirements.