Spreadsheet column membership test I've written the code below to compare the two columns of choice of two different excel files (new data and a reference file). If a value in the new data file is present in the reference file, "yes" is added in a new column behind the value in the new data file and "no" if not. The columns to compare can be anything you want and can be specified in the top of the script.

The script does the trick for small datasets (few 100 to few 1000 rows). However, I'm currently comparing two files where each files has ~300 000 columns. It has currently analysed +/- 56% of the new data file rows in ~18 hours. So the script needs to run nearly 36 hours to complete.  It appears that Python only processes ~2 rows per second, hence the time.

Is there a way to speed up this process? I was thinking that perhaps a list comprehension could add a little bit to the speed, but it seems complex to write this as a list comprehension. Any other suggestions?

PS: The error raises perhaps seem redundant but as I'm no Python expert I was experimenting with error raising.

#======SPECIFY COLUMNS TO COMPARE======#
data_column = "column name"
ref_column ="column name"


#=====================================#

import os

basedir = r'C:\path'
datadir = os.path.join(basedir, "data")
refdir = os.path.join(basedir, "ref_db")
exportdir = os.path.join(basedir, "export")


import pandas as pd

#select the excel file (with any name) in the data folder
data_list = [os.path.join(datadir,file) for file in os.listdir(datadir) if file.endswith(".xlsx")]

if len(data_list) == 0:
    raise ImportError("There is no excel file in the data folder")
elif len(data_list) &gt; 1:
    raise ImportError("There is more than 1 excel file in the data folder")

#data_list is a list of excel files in the folder. Select the one with index 0 as this should be the only one present.
new_data = pd.read_excel(data_list[0])

new_data[str("Comparison"+"_"+data_column)] = ""

#load the reference database in the export folder
ref_list = [os.path.join(refdir,file) for file in os.listdir(refdir) if file.endswith(".xlsx")]

if len(ref_list) == 0:
    raise ImportError("There is no excel file in the ref_db folder")
elif len(ref_list) &gt; 1:
    raise ImportError("There is more than 1 excel file in the ref_db folder")

ref_db = pd.read_excel(ref_list[0])

#check if specified columns occur in the excel files
if not data_column in new_data.columns:
    raise KeyError("specified data_column does not exist in the input data file")

if not ref_column in ref_db.columns:
    raise KeyError("specified ref_column does not exist in the input data file")

warn = []

for i,col in enumerate(new_data[data_column]): #iterate over each row in the specified column + save the index "i"

    if col in ref_db[ref_column].values: #check if the value col at index i of new_data occurs in the reference database:

        new_data[str("Comparison"+"_"+data_column)][i] = "yes"

        count = ref_db.groupby(ref_column).size()

        if count[col] &gt; 1:
          warn.append(["WARNING: value",col,"occurs multiple times in column",ref_column,"of the ref_db excel file"])
     #if no:   
    else: #assign "no" to the column
        new_data[str("Comparison"+"_"+data_column)][i] = "no"

    #print("Processing sequence",i,"of",len(new_data["sequence"])) #print the progress of the matching process
    print(round((i/len(new_data[data_column]))*100,2),"% of entries processed")

for i,j in enumerate(warn):
    print(*warn[i])

new_data.to_excel(os.path.join(exportdir, "compare_export.xlsx")) #save the file to the export folder
Unfortunately I am not experienced with Pandas especially on large files. But I would have tried a more 'native' solution like VBA but it is because I have some experience with it. There is a chance that VBA (in the form of a macro) would perform faster.

Perhaps you can try a hybrid approach, for example add some temporary columns to the files you are processing in Python, and fill those columns with formulae like COUNTIF etc.
The aim is to let Excel do the computationally-intensive stuff. And then you let your script loop row by one using the precalculations made by Excel.

When you are done, you remove the temporary columns or simply close the file discarding changes.
This may not be the more elegant approach, but I am not surprised that an interpreted language like Python with all the middleware involved is slow for this kind of task.

Another option is to dump the sheets to a database, MS Access or SQLite, add some indexes where appropriate and use SQL to fill out the gaps and then re-import the result to Excel.

This is more a hack than a real solution. My considerations would be:


is this a one-shot operation or a process that you will have to repeat ? This is to answer the question: how much time and energy are you willing to invest in this ?
You have not mentioned the purpose of the Excel files, and you've not shared the structure or a data sample. 300K columns for an Excel file is very big. I am wondering if by any change you are on assignment with some corporate client where clerks use Excel like a database. This is a very common scenario unfortunately. Then I would advise them to use a better solution. The whole process and usage is flawed and the solution can hardly be elegant.


Probably there are solutions already available on the market, including free and open source software. From experience there is little added value trying to patch a flawed process.

Everybody uses Excel, so Excel files proliferate everywhere and the result is poorly-structured data that is hard to exploit.
Using Excel is often the problem and the solution is to move away from Excel.