Generate chromatogram from old akta excel files.

plot_ACTA.py

import pandas as pd
import matplotlib.pyplot as plt
from typing import List, Tuple
import os

def read_excel_file(file_path: str) -> pd.DataFrame:
    return pd.read_excel(file_path, index_col=False, skiprows=[0,1])

def rename_columns(df: pd.DataFrame) -> pd.DataFrame:
    df.columns = ['ml', 'mAU', 'ml_%B', '%B', 'ml_pH', 'pH', 'ml_Frac', 'Frac']
    return df

def create_plot(df: pd.DataFrame, title: str) -> Tuple[plt.Figure, plt.Axes]:
    fig, ax1 = plt.subplots(figsize=(15, 8))
    plt.subplots_adjust(top=0.85)
    plt.title(title, y=1.01)
    return fig, ax1

def plot_mau(ax: plt.Axes, df: pd.DataFrame) -> None:
    ax.plot(df['ml'], df['mAU'], color='blue', label='mAU')
    ax.set_xlabel('ml')
    ax.set_ylabel('mAU', color='blue')
    ax.tick_params(axis='y', labelcolor='blue')

def plot_b_percentage(ax: plt.Axes, df: pd.DataFrame) -> plt.Axes:
    ax2 = ax.twinx()
    ax2.plot(df['ml_%B'], df['%B'], color='red', label='%B')
    ax2.set_ylabel('%B', color='red')
    ax2.tick_params(axis='y', labelcolor='red')
    return ax2

def plot_ph(ax: plt.Axes, df: pd.DataFrame) -> plt.Axes:
    ax3 = ax.twinx()
    ax3.plot(df['ml_pH'], df['pH'], color='green', label='pH')
    ax3.set_ylabel('pH', color='green')
    ax3.tick_params(axis='y', labelcolor='green')
    ax3.spines['right'].set_position(('outward', 60))
    return ax3

def add_frac_info(ax: plt.Axes, df: pd.DataFrame) -> None:
    for _, row in df.iterrows():
        if pd.notna(row['Frac']):
            ax.axvline(x=row['ml_Frac'], color='gray', linestyle='--', alpha=0.5)
            ax.text(row['ml_Frac'], ax.get_ylim()[1], row['Frac'], rotation=90, va='top', ha='right')

def add_legend(ax1: plt.Axes, ax2: plt.Axes, ax3: plt.Axes) -> None:
    lines1, labels1 = ax1.get_legend_handles_labels()
    lines2, labels2 = ax2.get_legend_handles_labels()
    lines3, labels3 = ax3.get_legend_handles_labels()
    ax1.legend(lines1 + lines2 + lines3, labels1 + labels2 + labels3, loc='upper left')

def process_file(file_path: str) -> None:
    df: pd.DataFrame = read_excel_file(file_path)
    df = rename_columns(df)

    title = os.path.splitext(os.path.basename(file_path))[0]
    fig, ax1 = create_plot(df, title)
    plot_mau(ax1, df)
    ax2 = plot_b_percentage(ax1, df)
    ax3 = plot_ph(ax1, df)
    add_frac_info(ax1, df)
    add_legend(ax1, ax2, ax3)

    ax1.grid(True, linestyle='--', alpha=0.7)
    plt.tight_layout()
    
    # Save the plot as a PNG file
    output_file = f"{title}_plot.png"
    plt.savefig(output_file)
    plt.close(fig)  # Close the figure to free up memory
    print(f"Processed and saved plot for {file_path}")

def main() -> None:
    current_dir: str = os.getcwd()
    xls_files: List[str] = [f for f in os.listdir(current_dir) if f.endswith('.xls')]

    if not xls_files:
        print("No .xls files found in the current directory.")
        return

    for file in xls_files:
        file_path: str = os.path.join(current_dir, file)
        process_file(file_path)

    print(f"Processed {len(xls_files)} files.")

if __name__ == "__main__":
    main()