File Upload Fix

app/api/routers/file_upload.py

@@ -1,14 +1,18 @@
 from typing import Annotated
-from fastapi import APIRouter, UploadFile, Depends
+from fastapi import APIRouter, UploadFile, Depends, HTTPException
 from app.categorization.file_processing import process_file, save_results
 from app.schema.index import FileUploadCreate
 import asyncio
 import os
 import csv
+import codecs
+from io import StringIO
+import pandas as pd
 
 from app.engine.postgresdb import get_db_session
 from sqlalchemy.ext.asyncio import AsyncSession
 
+
 file_upload_router = r = APIRouter(prefix="/api/v1/file_upload", tags=["file_upload"])
 
 @r.post(
@@ -21,23 +25,85 @@ file_upload_router = r = APIRouter(prefix="/api/v1/file_upload", tags=["file_upl
 )
 async def create_file(input_file: UploadFile, db: AsyncSession = Depends(get_db_session)):
     try:
+        result = {"file_name": input_file.filename, "output": "", "result": "", "processed_file": "", "error": ""}
+
          # Create directory to store all uploaded .csv files
         file_upload_directory_path = "data/tx_data/input"
+        output_csv_file_path = os.path.join(file_upload_directory_path, input_file.filename)
         if not os.path.exists(file_upload_directory_path):
             os.makedirs(file_upload_directory_path)
 
-        # Write items of .csv filte to directory
-        with open(os.path.join(file_upload_directory_path, input_file.filename)) as output_file:
-            [output_file.write(" ".join(row)+'\n') for row in csv.reader(input_file)]
-        output_file.close()
+        # input_csv_file = open(output_csv_file_path, "a")
+
+        try:
+            if input_file.filename.endswith(".csv"):
+                input_contents = await input_file.read()
+                buffer = StringIO(input_contents.decode('utf-8'))
+                read_csv = csv.reader(buffer)
+            
+                # Write items of .csv filte to directory
+                with codecs.open(output_csv_file_path, 'w+', encoding='utf-8') as csv_file:
+                    [csv_file.write(" ".join(row)+'\n') for row in read_csv]
+                csv_file.close()
+
+                new_output_csv_file_path = os.path.join(file_upload_directory_path, f"new_{input_file.filename}")
+                
+                with codecs.open(output_csv_file_path, 'r', encoding='utf-8') as csv_file:
+                    with codecs.open(new_output_csv_file_path, 'w+', encoding='utf-8') as new_csv_file:
+                        csvreader = csv.reader(csv_file)
+                        header = next(csvreader)[0]
+                        new_header = header.replace(" ", ",")
+                        # print(f"header: \n{header} headerItem: {header[0]} new_header: {new_header}\n")
+                        new_csv_file.write(" ".join(new_header)+'\n')
+                        for row in csvreader:
+                           new_row_items = row[0].split(' ')
+                           new_row = ""
+                           description = ""
+                           for idx, element in enumerate(new_row_items):
+                            # Since our string has been delimited by spaces, we want to get all the words of the description which comes after the 'date' index (0) and 
+                            # before the second to last 'type' index
+                            if idx > 0 and idx < len(new_row_items) - 2:
+                                description = description + f" {element}"
+                            else:
+                                delim = ", " if idx > 0 else ""
+                                item = element if len(description) == 0 else f"{description}, {element}"
+                                new_row = new_row + delim + item
+                                description = ""
+
+                        #    print(f"row: {row} rowType: \n{type(row)} new_row: {new_row}\n")
+                           new_csv_file.write(" ".join(new_row)+'\n')
+                        new_csv_file.close()
+                csv_file.close()
+
+                df = pd.read_csv(new_output_csv_file_path, names=['transaction_date', 'name_description', 'type', 'amount'], header=0, dtype=str, encoding='utf-8', engine='python')
+                # df = df.reindex(columns=['transaction_date', 'name_description', 'type', 'amount'])
+                # transaction_date_index = df.columns.get_loc('transaction_date')
+                result["output"] = df
+                # print(f"column: \n{df['transaction_date']} \n")
+                
+
+                # With the newly created file and it's path, process and save it for embedding
+                # processed_file = await process_file(new_output_csv_file_path)
+                processed_file = await process_file(new_output_csv_file_path, df)
+                result['processed_file'] = processed_file
+                # print(f"\ncreate_file \n processed_file_type: {type(processed_file)} \n processed_file: \n {result['processed_file']}\n")
+
+                # result = await asyncio.gather(processed_file)
+                # result['result'] = result
+
+                # Maybe should be passing in the dataframe from the processed_file dict insead of processed_file
+                result = await save_results(db, processed_file)
+                print(f"\create_file result: {result}\n")
 
-        # With the newly created file and it's path, process and save it for embedding
-        processed_file = process_file(os.path.realpath(input_file.filename))
-        result = await asyncio.gather(processed_file)
-        await save_results(db, result)
+                buffer.close()
+                input_file.close()
+                   
+        except Exception as e:
+            result["error"] = str(e)
+            raise HTTPException(status_code = 500, detail=f"create_file inner exception: {result} \n")
 
-    except Exception:
-        return {"message": "There was an error uploading this file. Ensure you have a .csv file with the following columns:"
-        "\n transaction_date, type, category, name_description, amount"}
+    except Exception as e:
+        # detail=f"create_file outer exception: {result} \n"
+      raise HTTPException(status_code = 500)
 
     return {"message": f"Successfully uploaded {input_file.filename}"}

app/categorization/categorizer.py

@@ -1,4 +1,3 @@
-# Standard library imports
 import re
 import ast
 import json
@@ -6,7 +5,6 @@ import logging
 import os
 from typing import Any, List, Tuple, Optional, Dict, Union
 
-# Third-party library imports
 import numpy as np
 import pandas as pd
 import asyncio
@@ -14,12 +12,13 @@ from rapidfuzz import process
 from tenacity import retry, wait_random_exponential, stop_after_attempt
 from pydantic import ValidationError
 
-# Local application/library specific imports
+from fastapi import HTTPException
+
 from langchain_openai import ChatOpenAI
 from langchain.chains import LLMChain
 from langchain.output_parsers import PydanticOutputParser, OutputFixingParser
 from langchain.prompts import PromptTemplate
-import app.categorization.template as CATEGORY_TEMPLATE
+from app.categorization.template import CATEGORY_TEMPLATE
 from app.categorization.config import CATEGORY_REFERENCE_OUTPUT_FILE, TX_PER_LLM_RUN
 
 
@@ -27,7 +26,7 @@ def fuzzy_match_list_categorizer(
     description: str,
     descriptions: np.ndarray,
     description_category_pairs: pd.DataFrame,
-    threshold: int = 75,
+    threshold: int = 90,
 ) -> Optional[str]:
     """Find the most similar transaction description and return its category.
 
@@ -44,55 +43,81 @@ def fuzzy_match_list_categorizer(
     Returns:
         str or None: Category of the matched description, or None if no match found.
     """
+    result = {"description": description, "descriptions": descriptions, "description_category_pairs": description_category_pairs, "match_results": "", "error": ""}
+    try:
+        if description_category_pairs.empty:
+            return None
+
+         # Fuzzy-match this description against the reference descriptions
+        description_stripped = description.strip()
+        descriptions_stripped = [s.strip() for s in descriptions]
 
-    # Fuzzy-match this description against the reference descriptions
-    match_results = process.extractOne(
-        description, descriptions, score_cutoff=threshold)
+        match_results = process.extractOne(
+            description_stripped, descriptions_stripped, score_cutoff=threshold)
+        
+        result["match_results"] = match_results
+        # print(f"\pairs_index: {description_category_pairs.columns}\n nmatch_results0: {match_results[0]}")
 
-    # If a match is found, return the category of the matched description
-    if match_results:
-        return description_category_pairs.at[match_results[2], 'category']
+        # If a match is found, return the category of the matched description
+        if match_results and (description_category_pairs.index == match_results[0]).any:
+            return description_category_pairs.at[match_results[0], 'category']
 
-    return None
+        return None
+        
+    except Exception as e:
+        result["error"] = str(e)
+        raise HTTPException(status_code = 500, detail=f"fuzzy_match_list_categorizer result: {result}")
 
 
-async def llm_list_categorizer(tx_list: pd.DataFrame) -> pd.DataFrame:
+async def llm_list_categorizer(df: pd.DataFrame) -> pd.DataFrame:
     """Categorize a list of transactions using a language model.
 
     This function uses a Language Model (LLM) to categorize a list of transaction descriptions.
     It splits the input DataFrame into chunks and processes each chunk asynchronously to improve performance.
 
     Args:
-        tx_list (pd.DataFrame): DataFrame containing the transaction descriptions to categorize.
+        df (pd.DataFrame): DataFrame containing the transaction descriptions to categorize.
 
     Returns:
         pd.DataFrame: DataFrame mapping transaction descriptions to their inferred categories.
     """
+    
+    result = {"df": "", "results": "", "valid results": "", "valid outputs": "", "error": ""}
 
-    # Initialize language model and prompt
-    openai_api_key = os.environ['OPENAI_API_KEY']
-    llm = ChatOpenAI(temperature=0, model="gpt-3.5-turbo-0125",
-                     api_key=openai_api_key)
-    prompt = PromptTemplate.from_template(template=CATEGORY_TEMPLATE)
-    chain = LLMChain(llm=llm, prompt=prompt)
-
-    # Iterate over the DataFrame in batches of TX_PER_LLM_RUN transactions
-    tasks = [llm_sublist_categorizer(tx_list.attrs['file_name'], chain=chain, tx_descriptions="\n".join(chunk['name/description']).strip())
-             for chunk in np.array_split(tx_list, tx_list.shape[0] // TX_PER_LLM_RUN + 1)]
-
-    # Gather results and extract (valid) outputs
-    # The results variable is a list of 'results', each 'result' being the output of a single LLM run
-    results = await asyncio.gather(*tasks)
-
-    # Extract valid results (each valid result is a list of description-category pairs)
-    valid_results = [result['output'] for result in results if result['valid']]
-
-    # Flatten the list of valid results to obtain a single list of description-category pairs
-    valid_outputs = [
-        output for valid_result in valid_results for output in valid_result]
+    try:
+        # Initialize language model and prompt
+        openai_api_key = os.environ['OPENAI_API_KEY']
+        llm = ChatOpenAI(temperature=0, model="gpt-3.5-turbo-0125",
+                        api_key=openai_api_key)
+        prompt = PromptTemplate.from_template(template=CATEGORY_TEMPLATE)
+        chain = prompt | llm
+        # chain = LLMChain(llm=llm, prompt=prompt)
+
+        # Iterate over the DataFrame in batches of TX_PER_LLM_RUN transactions
+        tasks = [llm_sublist_categorizer(df.attrs['file_name'], chain=chain, tx_descriptions="\n".join(chunk['name_description']).strip())
+                for chunk in np.array_split(df, df.shape[0] // TX_PER_LLM_RUN + 1)]
+
+        # Gather results and extract (valid) outputs
+        # The results variable is a list of 'results', each 'result' being the output of a single LLM run
+        results = await asyncio.gather(*tasks)
+        result["results"] = results
+
+        # Extract valid results (each valid result is a list of description-category pairs)
+        valid_results = [result['output'] for result in results if result['valid']]
+        result["valid results"] = valid_results
+
+        # Flatten the list of valid results to obtain a single list of description-category pairs
+        valid_outputs = [
+            output for valid_result in valid_results for output in valid_result]
+        result["valid outputs"] = valid_outputs
+
+        # Return a DataFrame with the valid outputs
+        return pd.DataFrame(valid_outputs, columns=['name_description', 'category'])
+    
+    except Exception as e:
+        result["error"] = str(e)
+        raise HTTPException(status_code = 500, detail=f"llm_list_categorizer result: {result}")
 
-    # Return a DataFrame with the valid outputs
-    return pd.DataFrame(valid_outputs, columns=['name/description', 'category'])
 
 
 @retry(wait=wait_random_exponential(min=1, max=20), stop=stop_after_attempt(6))
@@ -115,7 +140,8 @@ async def llm_sublist_categorizer(
         dict: Dictionary containing a 'valid' flag and a list of categorized descriptions.
     """
 
-    raw_result = await chain.arun(input_data=tx_descriptions)
+    raw_result = await chain.ainvoke(tx_descriptions)
+    # raw_result = await chain.arun(input_data=tx_descriptions)
 
     logger = logging.getLogger(__name__)
     result = {'valid': True, 'output': []}
@@ -124,24 +150,30 @@ async def llm_sublist_categorizer(
         pattern = r"\['([^']+)', '([^']+)'\]"
 
         # Use it to extract all the correctly formatted pairs from the raw result
-        matches = re.findall(pattern, raw_result.replace("\\'", "'"))
+        matches = re.findall(pattern, raw_result.content.replace("\\'", "'"))
+
+        # print(f"llm_sublist_categorizer type_matches: {type(matches)} match: {matches}\n")
 
         # Loop over the matches, and try to parse them to ensure the content is valid
         valid_outputs = []
         for match in matches:
             try:
                 parsed_pair = ast.literal_eval(str(list(match)))
+                # print(f"llm_sublist_categorizer \n parsed_pair: {parsed_pair} \n match: {match}\n list(match): {list(match)}\n str(list(match): {str(list(match))} \n\n")
                 valid_outputs.append(parsed_pair)
             except Exception as e:
                 logger.log(logging.ERROR,
                            f"Parsing Error: {e}\nMatch: {match}\n")
                 result['valid'] = False
 
+        # print(f"llm_sublist_categorizer valid_outputs \n{valid_outputs}\n")
+
         result['output'] = valid_outputs
 
     except Exception as e:
-        logging.log(
-            logging.ERROR, f"| File: {file_name} | Unexpected Error: {e}\nRaw Result: {raw_result}")
+        # logging.log(
+        #     logging.ERROR, f"| File: {file_name} | Unexpected Error: {e}\nRaw Result: {raw_result}")
         result['valid'] = False
+        raise HTTPException(status_code = 500, detail=f"llm_sublist_categorizer result: {result}")
 
     return result

app/categorization/categorizer_list.py

@@ -1,16 +1,16 @@
-# Standard library imports
 import os
 from datetime import datetime
 
-# Third-party library imports
+from fastapi import HTTPException
+
 import pandas as pd
+import codecs
 
-# Local application/library specific imports
 from app.categorization.config import CATEGORY_REFERENCE_OUTPUT_FILE
 from app.categorization.categorizer import llm_list_categorizer, fuzzy_match_list_categorizer
 
 
-async def categorize_list(tx_list: pd.DataFrame) -> pd.DataFrame:
+async def categorize_list(df: pd.DataFrame) -> pd.DataFrame:
     """Asynchronously categorize a list of transactions.
 
     This function categorizes a list of transactions using a combination of fuzzy matching
@@ -20,51 +20,93 @@ async def categorize_list(tx_list: pd.DataFrame) -> pd.DataFrame:
     pairs are added to the reference file.
 
     Args:
-        tx_list (pd.DataFrame): The list of transactions to categorize.
+        df (pd.DataFrame): The list of transactions to categorize.
 
     Returns:
         pd.DataFrame: The original DataFrame with an additional column for the category.
     """
+    result = {"df": pd.DataFrame(), "description_category": "", "description_category_pairs": "", "uncategorized_descriptions": "", "categorized_descriptions": "", "error": ""}
 
-    if os.path.exists(CATEGORY_REFERENCE_OUTPUT_FILE):
-        # Read description-category pairs from the reference file
-        description_category_pairs = pd.read_csv(
-            CATEGORY_REFERENCE_OUTPUT_FILE, header=None, names=['name/description', 'category']
-        )
-
-        # Extract only descriptions for faster matching
-        descriptions = description_category_pairs['name/description'].values
-
-        # Use fuzzy matching to find similar descriptions and assign the category
-        tx_list['category'] = tx_list['name/description'].apply(
-            fuzzy_match_list_categorizer,
-            args=(descriptions, description_category_pairs),
-        )
-
-    # Filter out uncategorized transactions, deduplicate, and sort by description
-    uncategorized_descriptions = (
-        tx_list[tx_list['category'].isnull()]
-        .drop_duplicates(subset=['name/description'])
-        .sort_values(by=['name/description'])
-    )
-
-    # Ask the language model to categorize the remaining descriptions
-    if not uncategorized_descriptions.empty:
-        categorized_descriptions = await llm_list_categorizer(
-            uncategorized_descriptions[['name/description', 'category']]
-        )
-
-        categorized_descriptions.dropna(inplace=True)
-
-        # Update the category for uncategorized transactions based on the language model results
-        if not categorized_descriptions.empty:
-            tx_list['category'] = tx_list['category'].fillna(
-                tx_list['name/description'].map(
-                    categorized_descriptions.set_index('name/description')['category']
-                )
+    try:
+        if not os.path.exists(CATEGORY_REFERENCE_OUTPUT_FILE):
+            os.makedirs(CATEGORY_REFERENCE_OUTPUT_FILE)
+
+        if os.path.exists(CATEGORY_REFERENCE_OUTPUT_FILE):
+            # Read description-category pairs from the reference file
+
+            # TODO FIRST ISSUE: Must save categorize_list categorized_descriptions items to output file
+            description_category_pairs = pd.read_csv(
+                CATEGORY_REFERENCE_OUTPUT_FILE, names=['name_description', 'category'], header=0
+            )
+
+            print(f"\ncategorize_list description_category_pairs \n{description_category_pairs}\n")
+            df['name_description'] = df['name_description'].astype(str)
+            df['category'] = df['category'].astype(str)
+            df['type'] = df['type'].astype(str)
+            # df['amount'] = df['amount'].str.strip().replace("\s", "").astype(float)
+            # df['transaction_date'] = pd.to_datetime(df['transaction_date'].str.strip().replace("\s", ""), format='%d/%m/%Y')
+
+            # Extract only descriptions for faster matching
+            # description_category_pairs.columns = description_category_pairs.columns.str.strip()
+            # descriptions = description_category_pairs['name_description'].values
+            descriptions = df['name_description'].values
+            print(f"\ncategorize_list descriptions \n{descriptions}\n")
+
+            result['description_category'] = descriptions
+            result['df'] = df
+            temp_df = df
+
+            print(f"\ncategorize_list df \n{df}\n")
+
+            # Use fuzzy matching to find similar descriptions and assign the category
+            # TODO SECOND ISSUE: Why isn't category being updated
+            df['category'] = df['name_description'].apply(
+                fuzzy_match_list_categorizer,
+                args=(descriptions, description_category_pairs)
             )
-        
-        # Fill remaining NaN values in 'category' with 'Other'
-        tx_list['category'] = tx_list['category'].fillna('Other')
 
-    return tx_list
+            print(f"\ncategorize_list setting fuzzy match list: \n{df}\n")
+
+            # Filter out uncategorized transactions, deduplicate, and sort by description
+            uncategorized_descriptions = (
+                df[df['category'].isnull()]
+                .drop_duplicates(subset=['name_description'])
+                .sort_values(by=['name_description'])
+            )
+            result['uncategorized_descriptions'] = uncategorized_descriptions
+            print(f"\ncategorize_list uncategorized_descriptions: \n{uncategorized_descriptions}\n")
+
+            # Ask the language model to categorize the remaining descriptions
+            if not uncategorized_descriptions.empty:
+                categorized_descriptions = await llm_list_categorizer(
+                    uncategorized_descriptions[['name_description', 'category']]
+                )
+
+                categorized_descriptions.dropna(inplace=True)
+                result['categorized_descriptions'] = categorized_descriptions
+                print(f"\ncategorize_list categorized_descriptions: \n{categorized_descriptions}\n")
+
+                # Update the category for uncategorized transactions based on the language model results
+                if not categorized_descriptions.empty:
+                    # temp_df['category'] = temp_df['name_description'].map(categorized_descriptions.set_index('name_description')['category'])
+                    print(f"\ncategorize_list current dataframe:\n {temp_df}\n")
+
+                    # df['category'] = df['category'].fillna(
+                    #     df['name_description'].map(
+                    #         categorized_descriptions.set_index('name_description')['category']
+                    #     )
+                    # )
+                    # print(f"\ncategorize_list categorized_description df[category]: \n{df['category']}\n")
+                
+                # Fill remaining NaN values in 'category' with 'Other'
+                with pd.option_context("future.no_silent_downcasting", True):
+                    df['category'] = df['category'].fillna('Other').infer_objects(copy=False)
+                    # print(f"\ncategorize_list Fill remaining NaN df[category]: \n{df['category']}\n")
+
+        return df
+    
+    except Exception as e:
+        # Return an error indicator and exception info
+        print(f"ERROR categorizer_list: {str(e)}")
+        result["error"] = str(e)
+        raise HTTPException(status_code = 500, detail=f"categorize_list result: {result}")

app/categorization/file_processing.py

@@ -8,6 +8,8 @@ from datetime import datetime
 
 import pandas as pd
 from dateparser import parse
+from fastapi import  HTTPException
+import csv
 
 from app.categorization.categorizer_list import categorize_list
 from app.categorization.config import RESULT_OUTPUT_FILE, CATEGORY_REFERENCE_OUTPUT_FILE
@@ -18,37 +20,39 @@ from sqlalchemy.ext.asyncio import AsyncSession
 
 
 # Read file and process it (e.g. categorize transactions)
-async def process_file(file_path: str) -> Dict[str, Union[str, pd.DataFrame]]:
+async def process_file(file_path: str, df: pd.DataFrame) -> Dict[str, Union[str, pd.DataFrame]]:
     """
     Process the input file by reading, cleaning, standardizing, and categorizing the transactions.
 
     Args:
         file_path (str): Path to the input file.
+        df (dataframe): Dataframe representing input file
 
     Returns:
         Dict[str, Union[str, pd.DataFrame]]: Dictionary containing the file name, processed output, and error information if any
     """
 
     file_name = os.path.basename(file_path)
-    result = {"file_name": file_name, "output": pd.DataFrame(), "error": ""}
+    result = {'file_path': file_name, 'output': pd.DataFrame(), 'error': ''}
     try:
-        # Read file into standardized tx format: source, date, type, category, description, amount
-        tx_list = standardize_csv_file(file_path)
+        # Read file into standardized tx format: transaction_date, name_description, type, amount, category, source
+        tx_list = standardize_csv_file(file_path, df)
+        result["columns"] = tx_list.columns.tolist()
 
         # Categorize transactions
         result["output"] = await categorize_list(tx_list)
-        print(f"File processed sucessfully: {file_name}")
+        # print(f"File processed sucessfully: {file_name}")
 
     except Exception as e:
         # Return an error indicator and exception info
-        logging.log(logging.ERROR, f"| File: {file_name} | Unexpected Error: {e}")
-        print(f"ERROR processing file {file_name}: {e}")
+        logging.debug(logging.ERROR, f"| File: {file_name} | Unexpected Error: {e}")
         result["error"] = str(e)
+        raise HTTPException(status_code = 500, detail=f"\n\n process_file result: \n{result}")
 
     return result
 
 
-def standardize_csv_file(file_path: str) -> pd.DataFrame:
+def standardize_csv_file(file_path: str, d: pd.DataFrame) -> pd.DataFrame:
     """
     Read and prepare the data from the input file.
 
@@ -58,22 +62,62 @@ def standardize_csv_file(file_path: str) -> pd.DataFrame:
     Returns:
         pd.DataFrame: Prepared transaction data.
     """
-
-    tx_list = pd.read_csv(file_path, index_col=False)
-    tx_list.attrs["file_name"] = file_path
-    tx_list.columns = tx_list.columns.str.lower().str.strip()
-
-    # Standardize dates to YYYY/MM/DD format
-    tx_list["date"] = pd.to_datetime(tx_list["date"]).dt.strftime("%Y/%m/%d")
-
-    # Add source and reindex to desired tx format; category column is new and therefore empty
-    tx_list.loc[:, "source"] = os.path.basename(file_path)
-    tx_list = tx_list.reindex(columns=["date", "expense/income", "category", "name/description", "amount"])
-
-    return tx_list
+    result = {"csv_file": "", "file_path": file_path, "new_csv_file": "", "transaction_date": "", "tx_list_new_columns": "", "error": ""}
+    
+    try:
+        #1
+        # result["csv_file"] = csv_file.read()
+        # reader = csv.reader(csv_file)
+        # data = list(reader)
+        # df = pd.DataFrame(data, columns=data[0])
+
+        #2
+        # df = pd.read_csv(file_path)
+
+        #3
+        # with open(file_path, 'r', encoding='utf-8') as file:
+        #     csv_reader = csv.reader(file)
+        #     data = list(csv_reader)
+        
+        # df = pd.DataFrame(data)
+
+        headers = d.columns
+        df = pd.DataFrame(d.values[1:], columns=headers)
+
+        result["csv_file"] = df
+        df.attrs['file_name'] = file_path
+        # print(f"\n\n standardize_csv_file columns: \n{df} \n date:\n{df['transaction_date']} \n")
+        # tx_list.columns = ['transaction_date', 'name_description', 'type', 'amount', 'category']
+
+        # # Standardize dates to YYYY/MM/DD format
+        warnings.filterwarnings('ignore', 'Parsing dates', category=UserWarning)
+        # df['transaction_date'] =  pd.to_datetime(df['transaction_date']).dt.strftime('%d/%m/%Y')
+        
+        # Add source and reindex to desired tx format; category column is new and therefore empty
+        # tx_list.insert(0, "source", [os.path.basename(file_path) for i in range(len(tx_list.columns))])
+        # tx_list.insert(0, "category", [])
+
+        # df = df.reindex(columns=['transaction_date', 'name_description', 'type', 'amount', 'category'])
+        # tx_list['transaction_date'] = pd.to_datetime(tx_list['transaction_date']).dt.strftime('%Y/%m/%d')
+        # tx_list.set_index(['transaction_date', 'name_description', 'type', 'amount', 'category'])
+        df['source'] = pd.Series([os.path.basename(file_path)] * len(df.index))
+        df['category'] = pd.Series([])
+        result["new_csv_file"] = df
+        # print(f"\n\n standardize_csv_file result: \n{df} \n")
+       
+        return df
+    
+    except Exception as e:
+        # Return an error indicator and exception info
+        logging.debug("standardize_csv_file Error: {e}")
+        if hasattr(e, 'message'):
+            result["error"] = str(e.message)
+        else:
+            result["error"] = str(e)
+        raise HTTPException(status_code = 401, detail=f"standardize_csv_file exception: {result}")
 
 
-async def save_results(db: AsyncSession, results: List) -> None:
+async def save_results(db: AsyncSession, results: List) -> Dict[str, Union[List[str], List[TransactionCreate], pd.DataFrame]]:
     """
     Merge all interim results in the input folder and write the merged results to the output file.
 
@@ -84,45 +128,81 @@ async def save_results(db: AsyncSession, results: List) -> None:
     Returns:
         None
     """
+    result = {'ok_files': '', 'ko_files': '', 'error_messages': '', 'txn_list_to_save': '', 'old_ref_data': '', 'new_ref_data': ''}
+    try:
+        # Concatenate all (valid) results into a single DataFrame
+        # Print errors to console
+        ok_files = []
+        ko_files = []
+        error_messages = []
+        # print(f"save_results results: {results}")
+
+        col_list = ["transaction_date", "name_description", "type", "amount", "category"]
+        tx_list = pd.DataFrame(columns=col_list)
+        # print(f"save_results tx_list: {tx_list}")
+        for result in results:
+            if not result["error"]:
+                ok_files.append(result["file_name"])
+                print(f"save_results ok_files: {ok_files}\n")
+                result_df = result["output"]
+                print(f"save_results result_df: {result_df}\n")
+                result_df.columns = col_list
+                print(f"save_results result_df.columns: {result_df.columns}\n")
+                tx_list = pd.concat([tx_list, result_df], ignore_index=True)
+                print(f"save_results tx_list: {tx_list}\n")
+            else:
+                ko_files.append(result["file_name"])
+                print(f"save_results ko_files: {ko_files}\n")
+                error_messages.append(f"{result['file_name']}: {result['error']}")
+                print(f"save_results error_messages: {error_messages}\n")
+        
+        result['ok_files'] = ok_files
+        result['ko_files'] = ko_files
+        result['error_messages'] = error_messages
+
+        # Save to database
+        # FIXME: get user_id from session
+        txn_list_to_save = [TransactionCreate(**row.to_dict(), user_id=1) for _, row in tx_list.iterrows()]
+        result['txn_list_to_save'] = txn_list_to_save
+        print(f"save_results txn_list_to_save: {txn_list_to_save}\n")
+        await Transaction.bulk_create(db, txn_list_to_save)
+
+        new_ref_data = tx_list[["name_description", "category"]]
+
+        if not os.path.exists(CATEGORY_REFERENCE_OUTPUT_FILE):
+            os.makedirs(CATEGORY_REFERENCE_OUTPUT_FILE)
+
+        if os.path.exists(CATEGORY_REFERENCE_OUTPUT_FILE):
+            # If it exists, add master file to interim results
+            old_ref_data = pd.read_csv(CATEGORY_REFERENCE_OUTPUT_FILE, names=["name_description", "category"], header=0)
+            new_ref_data = pd.concat([old_ref_data, new_ref_data], ignore_index=True)
+            print(f"save_results new_ref_data: {new_ref_data}\n")
+            print(f"save_results old_ref_data: {old_ref_data}\n")
+
+        # Drop duplicates, sort, and write to create new Master File
+        new_ref_data.drop_duplicates(subset=["name_description"]).sort_values(by=["name_description"]).to_csv(
+            CATEGORY_REFERENCE_OUTPUT_FILE, mode="w", index=False, header=True
+        )
+
+        result['old_ref_data'] = old_ref_data
+        result['new_ref_data'] = new_ref_data
+
+        print(f"\nsave_results result:\n {result}\n")
+
+        # Summarize results
+        # print(f"\nProcessed {len(results)} files: {len(ok_files)} successful, {len(ko_files)} with errors\n")
+        logging.debug(f"\nProcessed {len(results)} files: {len(ok_files)} successful, {len(ko_files)} with errors\n")
+        if len(ko_files):
+            print(f"Errors in the following files:")
+            for message in error_messages:
+                print(f"  {message}")
+            print("\n")
 
-    # Concatenate all (valid) results into a single DataFrame
-    # Print errors to console
-    ok_files = []
-    ko_files = []
-    error_messages = []
-
-    col_list = ["transaction_date", "type", "category", "name_description", "amount"]
-    tx_list = pd.DataFrame(columns=col_list)
-    for result in results:
-        if not result["error"]:
-            ok_files.append(result["file_name"])
-            result_df = result["output"]
-            result_df.columns = col_list
-            tx_list = pd.concat([tx_list, result_df], ignore_index=True)
-        else:
-            ko_files.append(result["file_name"])
-            error_messages.append(f"{result['file_name']}: {result['error']}")
-
-    # Save to database
-    # FIXME: get user_id from session
-    txn_list_to_save = [TransactionCreate(**row.to_dict(), user_id=1) for _, row in tx_list.iterrows()]
-    await Transaction.bulk_create(db, txn_list_to_save)
-
-    new_ref_data = tx_list[["name/description", "category"]]
-    if os.path.exists(CATEGORY_REFERENCE_OUTPUT_FILE):
-        # If it exists, add master file to interim results
-        old_ref_data = pd.read_csv(CATEGORY_REFERENCE_OUTPUT_FILE, names=["name/description", "category"], header=0)
-        new_ref_data = pd.concat([old_ref_data, new_ref_data], ignore_index=True)
-
-    # Drop duplicates, sort, and write to create new Master File
-    new_ref_data.drop_duplicates(subset=["name/description"]).sort_values(by=["name/description"]).to_csv(
-        CATEGORY_REFERENCE_OUTPUT_FILE, mode="w", index=False, header=True
-    )
-
-    # Summarize results
-    print(f"\nProcessed {len(results)} files: {len(ok_files)} successful, {len(ko_files)} with errors\n")
-    if len(ko_files):
-        print(f"Errors in the following files:")
-        for message in error_messages:
-            print(f"  {message}")
-        print("\n")
+    except Exception as e:
+        # Return an error indicator and exception info
+        # print(f"standardize_csv_file exception: {e}")
+        logging.debug("save_results Error: {e}")
+        result["error"] = str(e)
+        raise HTTPException(status_code = 401, detail=f"save_results exception: {error_messages}")
+
+    return result

app/categorization/template.py

@@ -53,4 +53,5 @@ CATEGORY_TEMPLATE = """
     
     <financial_transactions>
     {input_data}
-    </financial_transactions>"""
+    </financial_transactions>
+    """

app/settings.py

@@ -1,5 +1,6 @@
 import os
 from typing import Dict
+from dotenv import load_dotenv
 from llama_index.core.settings import Settings
 
 

app/transactions_rag/transactions_2024.csv

@@ -1,29 +0,0 @@
-Date,Name / Description,Expense/Income,Amount
-2023-12-30,Comcast Internet,Expense,9.96
-2023-12-30,Lemonade Home Insurance,Expense,17.53
-2023-12-30,Monthly Appartment Rent,Expense,2000.0
-2023-12-30,Staples Office Supplies,Expense,12.46
-2023-12-29,Selling Paintings,Income,13.63
-2023-12-29,Spotify,Expense,12.19
-2023-12-23,Target,Expense,27.08
-2023-12-22,IT Consulting,Income,541.57
-2023-12-22,Phone,Expense,10.7
-2023-12-20,ML Consulting,Income,2641.93
-2023-12-19,Chipotle,Expense,18.9
-2023-12-18,Cold Brew Coffee,Expense,17.67
-2023-12-18,Gas,Expense,8.80
-2023-12-18,CA Property Tax,Expense,1670.34
-2022-11-26,Salary,Income,4000.36
-2022-11-26,Cellphone,Expense,19.27
-2022-11-26,Gym Membership,Expense,24.71
-2022-11-25,Wholefoods,Expense,17.35
-2022-11-24,Freelancing,Income,2409.55
-2022-11-19,Spotify,Expense,20.76
-2022-10-25,Blogging,Income,4044.27
-2022-10-24,Uber Taxi,Expense,18.9
-2022-10-23,Uber Taxi,Expense,27.54
-2022-10-22,Apple Services,Expense,41.25
-2022-10-21,Netflix,Expense,22.8
-2022-01-16,Amazon Lux,Expense,24.11
-2022-01-15,Burger King,Expense,30.08
-2022-01-14,Amazon,Expense,11.0

migration/versions/e8e988ebb7c7_.py

@@ -0,0 +1,30 @@
+"""
+
+Revision ID: e8e988ebb7c7
+Revises: 4e76691ab103
+Create Date: 2024-06-08 18:13:46.160432
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'e8e988ebb7c7'
+down_revision: Union[str, None] = '4e76691ab103'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    # ### commands auto generated by Alembic - please adjust! ###
+    pass
+    # ### end Alembic commands ###
+
+
+def downgrade() -> None:
+    # ### commands auto generated by Alembic - please adjust! ###
+    pass
+    # ### end Alembic commands ###

tests/test_file_upload.py

@@ -0,0 +1,20 @@
+from datetime import datetime
+from typing import List
+from pathlib import Path
+from fastapi import Depends
+from fastapi.testclient import TestClient
+import pytest
+
+from app.model.transaction import Transaction
+from app.schema.index import TransactionType, TransactionCreate
+
+from sqlalchemy.ext.asyncio import AsyncSession
+from app.engine.postgresdb import get_db_session
+
+@pytest.mark.asyncio
+async def test_file_upload(client: TestClient, get_db_session_fixture: AsyncSession) -> None:
+    _test_upload_file = Path('/Users/patrickalexis/Documents/codepath-ai-course/codepath-group-project/backend/app/transactions_rag/transactions_2024.csv', 'new-index.json')
+    _files = {'input_file': _test_upload_file.open('rb')}
+
+    response = client.post(("/api/v1/file_upload/"),files=_files)
+    assert response.status_code == 201

transactions_2024.csv

@@ -0,0 +1,29 @@
+transaction_date,name_description,type,amount
+20/11/2023,Comcast Internet,Expense,9.96
+21/04/2023,Lemonade Home Insurance,Expense,17.53
+22/05/2023,Monthly Appartment Rent,Expense,2000.0
+14/06/2023,Staples Office Supplies,Expense,12.46
+17/07/2023,Selling Paintings,Income,13.63
+19/12/2023,Spotify,Expense,12.19
+05/10/2023,Target,Expense,27.08
+03/08/2023,IT Consulting,Income,541.57
+06/04/2023,Phone,Expense,10.7
+02/01/2023,ML Consulting,Income,2641.93
+05/04/2023,Chipotle,Expense,18.9
+18/05/2023,Cold Brew Coffee,Expense,17.67
+13/03/2023,Gas,Expense,8.80
+19/07/2023,CA Property Tax,Expense,1670.34
+20/08/2023,Salary,Income,4000.36
+20/09/2023,Cellphone,Expense,19.27
+06/10/2023,Gym Membership,Expense,24.71
+02/01/2023,Wholefoods,Expense,17.35
+11/06/2023,Freelancing,Income,2409.55
+12/11/2023,Spotify,Expense,20.76
+20/03/2023,Blogging,Income,4044.27
+12/02/2023,Uber Taxi,Expense,18.9
+23/07/2023,Uber Taxi,Expense,27.54
+01/09/2023,Apple Services,Expense,41.25
+07/05/2023,Netflix,Expense,22.8
+09/08/2023,Amazon Lux,Expense,24.11
+17/01/2023,Burger King,Expense,30.08
+14/01/2023,Amazon,Expense,11.0