Code Editor for app.py

import streamlit as st
import pandas as pd
import numpy as np
from io import StringIO
import ast

st.title("Excel Processor with Custom Columns")

# Function to safely evaluate Python expressions
def safe_eval(expr, row):
    # Create a dict of allowed functions
    safe_dict = {
        'len': len,
        'sum': sum,
        'min': min,
        'max': max,
        'abs': abs,
        'round': round,
        'int': int,
        'float': float,
        'str': str,
        'np': np,
    }
    
    try:
        # Add row data to safe dict
        safe_dict.update(row.to_dict())
        # Evaluate expression
        return eval(expr, {"__builtins__": {}}, safe_dict)
    except:
        return None

# Initialize session state
if 'df' not in st.session_state:
    st.session_state.df = None
if 'column_expressions' not in st.session_state:
    st.session_state.column_expressions = {}

# File uploader
uploaded_file = st.file_uploader("Upload Excel file", type=['xlsx', 'xls'])

if uploaded_file is not None:
    # Read Excel file
    st.session_state.df = pd.read_excel(uploaded_file)
    
    # Display original dataframe
    st.subheader("Original Data")
    st.dataframe(st.session_state.df)
    
    # Column names for reference
    st.subheader("Available Columns")
    st.write(", ".join(st.session_state.df.columns))
    
    # Add new column section
    st.subheader("Add New Column")
    
    col1, col2 = st.columns(2)
    
    with col1:
        new_column_name = st.text_input("New Column Name")
    
    with col2:
        column_expression = st.text_area(
            "Description of calculation (in natural language)",
            help="Describe how to calculate the new column. For example: 'multiply Price by Quantity' or 'concatenate First Name and Last Name'"
        )
    
    if st.button("Add Column") and new_column_name and column_expression:
        st.session_state.column_expressions[new_column_name] = column_expression
    
    # Display and process all column expressions
    if st.session_state.column_expressions:
        st.subheader("Custom Columns")
        
        for col_name, expr in st.session_state.column_expressions.items():
            st.write(f"**{col_name}**: {expr}")
            
            # Here you would typically have more sophisticated natural language processing
            # This is a simple example that handles basic operations
            try:
                if 'multiply' in expr.lower():
                    cols = [col for col in st.session_state.df.columns 
                           if col.lower() in expr.lower()]
                    if len(cols) >= 2:
                        st.session_state.df[col_name] = st.session_state.df[cols[0]] * st.session_state.df[cols[1]]
                
                elif 'add' in expr.lower() or 'sum' in expr.lower():
                    cols = [col for col in st.session_state.df.columns 
                           if col.lower() in expr.lower()]
                    if len(cols) >= 2:
                        st.session_state.df[col_name] = st.session_state.df[cols[0]] + st.session_state.df[cols[1]]
                
                elif 'concatenate' in expr.lower():
                    cols = [col for col in st.session_state.df.columns 
                           if col.lower() in expr.lower()]
                    if len(cols) >= 2:
                        st.session_state.df[col_name] = st.session_state.df[cols[0]].astype(str) + ' ' + st.session_state.df[cols[1]].astype(str)
                
                elif 'average' in expr.lower() or 'mean' in expr.lower():
                    cols = [col for col in st.session_state.df.columns 
                           if col.lower() in expr.lower()]
                    if len(cols) >= 2:
                        st.session_state.df[col_name] = st.session_state.df[cols].mean(axis=1)
            
            except Exception as e:
                st.error(f"Error processing {col_name}: {str(e)}")
        
        # Display final dataframe
        st.subheader("Processed Data")
        st.dataframe(st.session_state.df)
        
        # Download button for processed data
        if st.button("Download Processed Excel"):
            output = StringIO()
            st.session_state.df.to_excel(output, index=False)
            output.seek(0)
            st.download_button(
                label="Click to Download",
                data=output,
                file_name="processed_data.xlsx",
                mime="application/vnd.ms-excel"
            )