Reading in a series of Excel files

Question

I needed to create a program which reads in a series of Excel files in folders typically structured something like this.

rootDir
   Control
      0hr.xls
     24hr.xls
     48hr.xls
     72hr.xls
   0.01um
      0hr.xls
     24hr.xls
     48hr.xls
     72hr.xls
   0.1um
      0hr.xls
     24hr.xls
     48hr.xls
     72hr.xls

The number of subdirectories (aka devices) under root and number of time intervals (e.g. 0hr.xls) can change.

I read the files in, do some calculations, then write the data to an Excel sheet, and create some excel plots. I'm wondering how I can improve it and if I should be using more of an object oriented approach.

#!/usr/local/bin/python

import pandas as pd # Pandas for data structures useful this program
import numpy as np # Numpy for various math structures and functions
from scipy import stats # Scipy statistics functions
import os, sys # Base functions for accessing data on the computer
import argparse # For parsing input arguments from the commandline
from natsort import natsorted
from xlsxwriter.utility import xl_rowcol_to_cell

class FullPaths(argparse.Action):
    """Expand user- and relative-paths"""
    def __call__(self, parser, namespace, values, option_string=None):
        setattr(namespace, self.dest, os.path.abspath(os.path.expanduser(values)))

def is_dir(dirname):
    """Checks if a path is an actual directory"""
    if not os.path.isdir(dirname):
        msg = "{0} is not a directory".format(dirname)
        raise argparse.ArgumentTypeError(msg)
    else:
        return dirname

def get_args():
    """Get CLI arguments and options"""
    parser = argparse.ArgumentParser()

    parser.add_argument('EXPROOTPATH',
                        help="path to files and folders for the experiment",
                        action=FullPaths, type=is_dir)

    parser.add_argument('--version', action='version', version='%(prog)s 1.0')
    results = parser.parse_args()

    return results

def read_angiotool_files():
    """ Read in the AngioTool data for each device"""
    # Retrive the file names and full file paths of the excel files with the data
    fpath = []
    fnames = []
    dnames = []

    for root, dirs, files in os.walk(EXPROOTPATH, topdown=False):
        for fn in files:
            if fn.endswith(".xls") and not fn.endswith("data.xls"):
                fpath.append(os.path.join(root, fn))
                fnames.append(fn)
        for dn in dirs:
            dnames.append(dn)

    # Remove the excel and plots directory from the list if it exists
    os.chdir(EXPROOTPATH)
    if os.path.isdir('plots'):
        dnames.remove('plots')

    if os.path.isdir('excel'):
        dnames.remove('excel')

    # Set device names based on directory names
    dev_names = dnames

    # Determine the Device Intervals from the excel file names
    dev_int = []
    for item in fnames:
        # Strip the .xls off the file names and use these as the intervals
        dev_int.append(os.path.splitext(item)[0])
    # Remove Duplicate Items
    dev_int = list(set(dev_int))
    # Sort the list
    dev_int = natsorted(dev_int)

    # Import the excel data
    # Create an index for the file paths to the excel files
    i_fp = 0
    # Create a blank dictionary to add the imported excel data to
    all_dict = {}

    # test to make sure there's the correct number of files to avoid issues
    if len(fpath) == len(dev_names) * len(dev_int):
        for item_dn in dev_names: # For each device
            for item_di in dev_int: # For each device interval
                item_fp = fpath[i_fp] # Get the file path based on the index
                all_dict[(item_di, item_dn)] = pd.read_excel(item_fp,
                                                             header=2,
                                                             convert_float=False,
                                                             na_values=['NA'])
                i_fp += 1 # increment the file path index
    else: # throw an error
        sys.exit("Error : The program has stopped because there's an excel file missing.\n"
                 "The total number of files for the devices does not equal the devices\n"
                 "mutltiplied by the intervals.  Please place a dummy file in the\n"
                 "appropriate directory.")

    # Place the dictionary data into a dataframe
    dev_data = pd.concat(all_dict, axis=0, names=['interval', 'device', 'well'])
    dev_data.columns.names = ['field']

    # Determine number of wells
    n_wells = len(dev_data.index.levels[2])

    # Replace Junctions values of 0 with 1 to avoid NaNs
    index = dev_data['Total Number of Junctions'] <= 0
    dev_data.loc[index, 'Total Number of Junctions'] = 1
    index = dev_data['Junctions density'] <= 0
    dev_data.loc[index, 'Junctions density'] = 1

    return dev_data, n_wells

def sort_nat(dev_data):
    """Sort dev_data naturally"""
    dev_data = dev_data.reindex(index=natsorted(dev_data.index))
    return dev_data

def rm_nan(df):
    """Get rid of data that isn't a number"""
    df = df.select_dtypes(include=[np.number])
    return df

def norm_to_0hr(df):
    """Normalize to the 0 hr value"""
    df = df.T.stack(level=['device', 'well'])
    df = df.divide(df['0hr'], axis=0).stack().unstack(-2)
    return df

def calc_stats(df, n_wells):
    """Calculate Mean, StDev, and Standard Error"""
    df2 = pd.DataFrame(index=df.index, columns=['mean', 'stdev', 'stErr'])
    df2['mean'] = df.ix[:, 0:n_wells].mean(axis=1)
    df2['stdev'] = df.ix[:, 0:n_wells].std(axis=1)
    df2['stErr'] = df.ix[:, 0:n_wells].apply(stats.sem, axis=1)
    return df2

def norm_to_avg(df):
    """Normalize values to average of control"""
    df = df.unstack(['device', 'well']).stack(['field']).reorder_levels(['field', 'interval'])
    avg = df['Control'].mean(axis=1)
    df = df.divide(avg, axis=0)*100
    df = df.stack(['device'])
    df = df.reorder_levels(['field', 'device', 'interval'], axis=0)
    df = df.sort()
    return df

def t_test(df):
    """Perform a t-test"""
    df = df.reorder_levels(['field', 'interval', 'device']).sort()
    df2 = pd.DataFrame(index=df.index, columns=['p-value'])

    for i in set(df.index.get_level_values(0)):
        for j in set(df.index.get_level_values(1)):
            for k in set(df.index.get_level_values(2)):
                [t, p] = stats.ttest_ind(df.loc[(i, j, 'Control')],
                                         df.loc[(i, j, k)], 0, equal_var=True)
                df2.loc[(i, j, k), 'p-value'] = p
    df2 = df2.reorder_levels(['field', 'device', 'interval']).sort()
    return df2

def write_excel_data(dev_data, norm_to_ctrl, norm_to_mean):
    """Write data into a file"""

    # Define excel directory
    xls_dir = "./excel"

    # Change directory to EXPROOTPATH
    os.chdir(EXPROOTPATH)

    # Check to see if excel directory exists and if it doesn't make it
    try:
        os.makedirs(xls_dir)
    except OSError:
        if not os.path.isdir(xls_dir):
            raise

    # Reorder
    dev_data = dev_data.reorder_levels(['device', 'interval', 'well'])
    norm_to_ctrl = norm_to_ctrl.stack().unstack(-4).reorder_levels(['device', 'interval', 2]) #.sort_index(0)
    norm_to_mean = norm_to_mean.stack().unstack(-4).reorder_levels(['device', 'interval', 2])

    # Sort
    dev_data = dev_data.reindex(index=natsorted(dev_data.index))
    norm_to_ctrl = norm_to_ctrl.reindex(index=natsorted(norm_to_ctrl.index))
    norm_to_mean = norm_to_mean.reindex(index=natsorted(norm_to_mean.index))

    # Create the Excel Workbook
    writer = pd.ExcelWriter(xls_dir+"/"+'data.xlsx', engine='xlsxwriter')

    # Write the data to the Excel Workbook
    dev_data.to_excel(writer, sheet_name='Raw_Device_Data')
    norm_to_ctrl.to_excel(writer, sheet_name='Ratio_to_Control')
    norm_to_mean.to_excel(writer, sheet_name='Ratio_to_Control_2')

def write_excel_plots(plot_key, norm_to_ctrl):
    """plot data in excel"""

    # Define excel directory
    xls_dir = "./excel"

    # Change directory to EXPROOTPATH
    os.chdir(EXPROOTPATH)

    # Check to see if excel directory exists and if it doesn't make it
    try:
        os.makedirs(xls_dir)
    except OSError:
        if not os.path.isdir(xls_dir):
            raise

    # Set color palette
    #colors = ['#A8C16C', '#CA625C', '#5D93C5', '#9078AE', '#58B8CE']

    # Create the Excel Workbook
    writer = pd.ExcelWriter(xls_dir+"/"+'plots.xlsx', engine='xlsxwriter')

    for each in plot_key:

        df = norm_to_ctrl.loc[each].unstack().stack(0)
        df1 = df.xs('mean', level=1)
        df2 = df.xs('stdev', level=1)
        df = pd.concat([df1, df2],keys=['mean','stdev'])
        df = df.reindex(columns=natsorted(df.columns))

        df.to_excel(writer, sheet_name=each+'_Data')

        # Access the Workbook Object
        workbook = writer.book

        # Create a Chart Sheet
        chartsheet = workbook.add_chartsheet(each+' Line')

        i = 0
        n_intervals = len(df.columns)
        n_dev = len(df.reorder_levels([1,0]).sort().index.levels[0])
        dev_names = list(df.reorder_levels([1,0]).sort().index.levels[0])

        # Create a Chart
        chart = workbook.add_chart({'type': 'line'})


        for eachDevName in dev_names:

            # This mess is building a string to put into the plus / minus
            # values for the y error bars.  It won't take row line/numbers
            # like the other keys.  It requires excel syntax.
            err_bar_sheet = '\''+each+'_Data\'!'
            err_bar_start = xl_rowcol_to_cell(2+n_dev+i, 2, row_abs=True, col_abs=True)
            err_bar_end = xl_rowcol_to_cell(2+n_dev+i, 2+n_intervals, row_abs=True, col_abs=True)
            err_bar_ref = '='+err_bar_sheet+err_bar_start+':'+err_bar_end

            chart.add_series({
                'categories' : [each+'_Data', 0, 2, 0, 2+(n_intervals-1)],
                'values' : [each+'_Data', 2+i, 2, 2+i, 2+(n_intervals-1)],
                'name' : [each+'_Data', 2+i, 1],
                'marker': {'type': 'circle', 'size': 4},
                'line':   {'width': 2.0},
                'y_error_bars': {
                    'type': 'custom',
                    'plus_values': err_bar_ref,
                    'minus_values': err_bar_ref}
            })

            i += 1

        chart.set_x_axis({'text_axis': True})
        chart.set_y_axis({'name': each+' Relative to Control', 'min': 0})
        chart.set_legend({'position': 'bottom'})
        chart.set_title({'name': each+' Relative to Control'})

        # Place the Chart on the Chart Sheet
        chartsheet.set_chart(chart)

    # Write the Data to Disk
    writer.save()


def main():
    """ Get and parse command line inputs EXPROOTPATH is the root
    directory where all your data is The path to the directory it's
    suggested it not have spaces in it.  For example the folder
    'SomeExperiment' is ok, but 'Some Experiment' in not preferred.
    This directory should contain folder with the device names.  For
    example: SomeExperiment should contain Device1, Device2, etc.
    Each Device Folder should contain the .xls files with output
    from angioTool.  The .xls files should be named based on the
    interval (e.g. 0hr.xls, 24hr.xls, etc.)"""

    global EXPROOTPATH

    results = get_args()
    EXPROOTPATH = results.EXPROOTPATH

    # Read the data in from excel
    [dev_data, n_wells] = read_angiotool_files()

    # Remove non-numbers
    dev_data = rm_nan(dev_data)

    #
    # Calculations and Stats
    #

    # Normalize Values to 0hr
    norm_to_ctrl = norm_to_0hr(dev_data)
    # Normalize Values to Mean of Wells
    norm_to_mean = norm_to_avg(dev_data)
    # Cacluate Stats on 0hr Normalized Values
    norm_to_ctrl_stats = calc_stats(norm_to_ctrl, n_wells)
    # Caculate Stats on Mean Normalized Vales
    norm_to_mean_stats = calc_stats(norm_to_mean, n_wells)
    # Perform a t-Test on Mean Normalized Values
    norm_to_ctrl_pval = t_test(norm_to_mean)

    #
    # Concatenate for writing to excel
    #
    norm_to_ctrl = pd.concat([norm_to_ctrl, norm_to_ctrl_stats], axis=1)
    norm_to_mean = pd.concat([norm_to_mean, norm_to_mean_stats, norm_to_ctrl_pval], axis=1)

    #
    # Write data out out excel sheet
    #
    write_excel_data(dev_data, norm_to_ctrl, norm_to_mean)
    # Write excel charts
    plot_keys = ['Total Vessels Length', 'Total Number of End Points', 'Total Number of Junctions']
    write_excel_plots(plot_keys, norm_to_ctrl)

if __name__ == "__main__":
    main()

Answer 1

I think there are a few tweaks that you could benefit from here and that would make your code more extensible, organized, and clean.

What you're doing here is packaging all of your program's functionality in discrete, uncategorized functions. This is fine if your program is small and its structure uncomplicated, but with larger systems such an approach is unfeasible. It makes your program's control flow confusing, and it limits others (or yourself) in their ability to extend your program. This is because in order to modify what you've written, the programmer must understand EVERYTHING about your code - what each function does and how it works. In contrast, an object-oriented approach allows others to understand your program by understanding how the parts (classes) interact with one another without getting caught up in the details of how each class is implemented.

Here are my suggestions:

1. Divide your program into logical, self-contained units. One part of the program can be devoted to handling Excel Files, one part for handling the data contained in those files, one part for handling filesystems, one part for creating plots, etc.

2. Create classes based on these units. For example, create a generic File class in which you define general methods such as create_file, read_from_file, write_to_file, etc. and general class variables such as filepath, file_extension, file_directory, etc. that all classes share. You can then create a subclass called ExcelFile that inherits from the File class. In this class you inherit all the general file properties, so you only have to worry about implementing excel-specific methods. For example, you might have methods called apply_formula_to_column, or generate_excel_data_graph. The advantage to this design strategy is that if in the future you decide you want your program to process data from another type of file, say, for example, files of the type generated by the open office excel counterpart. Then you can create a new class called OpenOfficeFile that inherits from ExcelFile and you won't have to touch any of the code you've already written.

3. You can make similar classes for other parts of your program. I would recommend creating an abstract Data class in which you define generic data-validation functions. Then you can subclass Data for each type of data that you extract from the excel file. You can also create a class related to graphics that can handle stuff like drawing plots of the data you have.

4. Doing all this should make your main loop very simple. All such a loop would have to do is initialize an ExcelFile class, call ExcelFile.read_data(), pass that result into the init method of the Data class, and then pass that to the graphics class. Then you could call File.write() to write the results of your calculations to a file.

Hope this helps!