# Load multiple worksheets from Excel file to multiple DataFrames

The spreadsheet can be found just above the references section at this link.

https://www.sciencedirect.com/science/article/pii/S1872497316301429

It's a publication for STR DNA locus frequencies to calculate match probabilities. I'd like to build a match probability calculator and scale it up to do hundreds of thousands of calculations rather than just one. The first step is loading the frequencies into memory and is the subject of this code. I figured a dataframe for each population set would be alright.

The first row in each worksheet is just a population label. It can be ignored.

The second row is the header: The first column "Allele" is the STR allele call. This is what will be compared to for matching. This will typically be an integer but sometimes can have a decimal of .1, .2, or .3 most commonly. Sometimes they are a string with a greater than or less than symbol (e.g.; "<9.2", ">17") although I don't know if there are any in the excel file.

The 2nd through 25th columns are the different locations that are tested. Each is independent.

I will ignore anything after the 25th column and anything past the last row of frequencies. Most of the data points within these bounds are empty. They will be filled with what is called the minimum allele frequency. If you are interested in anything further I'd be happy to chat, but it's probably not pertinent to the discussion at hand.

I'm sure there is a more elegant way to do this and probably a quicker way to do it. That's why I am here. If there's a better structure to put this in, or a faster/more elegant way to do it, please let me know. This is my first stab at it. The frames take about 3 seconds to load all the data. I'm guessing it should be much quicker than that. Anyway, here's my code.

# -*- coding: utf-8 -*-
import pandas as pd

#create dataframes for population tables
caucasian_freq = pd.DataFrame()
swh_freq = pd.DataFrame()
seh_freq = pd.DataFrame()
agg_aa_freq = pd.DataFrame()
aa_freq = pd.DataFrame()
bah_freq = pd.DataFrame()
jam_freq = pd.DataFrame()
trin_freq = pd.DataFrame()
cham_freq = pd.DataFrame()
fili_freq = pd.DataFrame()
apa_freq = pd.DataFrame()
nav_freq = pd.DataFrame()

#get the file name
expanded_frequencies = '1-s2.0-S1872497316301429-mmc1.xlsx'

#for the number of columns containing frequencies
data_columns = [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25]
#for minimum allele frequency
min_freq = [0.0124, 0.0120, 0.0095, 0.0046, 0.012, 0.0159, 0.0141, 0.0316, 0.0263, 0.0550, 0.0130, 0.0175]

#import the different freq sets - ignore the first row, don't read anything past column 25

#truncate rows without data - the row after the last row of data is a duplicate of
#the header row. Use it to find the last row of data
caucasian_freq = caucasian_freq.truncate(after=caucasian_freq.loc[caucasian_freq['Allele'] == 'Allele'].index[0]-1)
swh_freq = swh_freq.truncate(after=swh_freq.loc[swh_freq['Allele'] == 'Allele'].index[0]-1)
seh_freq = seh_freq.truncate(after=seh_freq.loc[seh_freq['Allele'] == 'Allele'].index[0]-1)
agg_aa_freq = agg_aa_freq.truncate(after=agg_aa_freq.loc[agg_aa_freq['Allele'] == 'Allele'].index[0]-1)
aa_freq = aa_freq.truncate(after=aa_freq.loc[aa_freq['Allele'] == 'Allele'].index[0]-1)
bah_freq = bah_freq.truncate(after=bah_freq.loc[bah_freq['Allele'] == 'Allele'].index[0]-1)
jam_freq = jam_freq.truncate(after=jam_freq.loc[jam_freq['Allele'] == 'Allele'].index[0]-1)
trin_freq = trin_freq.truncate(after=trin_freq.loc[trin_freq['Allele'] == 'Allele'].index[0]-1)
cham_freq = cham_freq.truncate(after=cham_freq.loc[cham_freq['Allele'] == 'Allele'].index[0]-1)
fili_freq = fili_freq.truncate(after=fili_freq.loc[fili_freq['Allele'] == 'Allele'].index[0]-1)
apa_freq = apa_freq.truncate(after=apa_freq.loc[apa_freq['Allele'] == 'Allele'].index[0]-1)
nav_freq = nav_freq.truncate(after=nav_freq.loc[nav_freq['Allele'] == 'Allele'].index[0]-1)

#fill in nas with the minimum allele frequency
caucasian_freq.fillna(min_freq[0], inplace = True)
swh_freq.fillna(min_freq[1], inplace = True)
seh_freq.fillna(min_freq[2], inplace = True)
agg_aa_freq.fillna(min_freq[3], inplace = True)
aa_freq.fillna(min_freq[4], inplace = True)
bah_freq.fillna(min_freq[5], inplace = True)
jam_freq.fillna(min_freq[6], inplace = True)
trin_freq.fillna(min_freq[7], inplace = True)
cham_freq.fillna(min_freq[8], inplace = True)
fili_freq.fillna(min_freq[9], inplace = True)
apa_freq.fillna(min_freq[10], inplace = True)
nav_freq.fillna(min_freq[11], inplace = True)

• Please do not update the code in your question after receiving answers, doing so goes against the Question + Answer style of Code Review. This is not a forum where you should keep the most updated version in your question. Please see what you may and may not do after receiving answers.
– Mast
Aug 11 '20 at 11:10

## No need to create dataframes beforehand

The following code is not needed:

#create dataframes for population tables
caucasian_freq = pd.DataFrame()
swh_freq = pd.DataFrame()
seh_freq = pd.DataFrame()
agg_aa_freq = pd.DataFrame()
aa_freq = pd.DataFrame()
bah_freq = pd.DataFrame()
jam_freq = pd.DataFrame()
trin_freq = pd.DataFrame()
cham_freq = pd.DataFrame()
fili_freq = pd.DataFrame()
apa_freq = pd.DataFrame()
nav_freq = pd.DataFrame()


You do not have to create the dataframes at this point, they will be created in the moment you load the Excel files, that is here:

caucasian_freq = pd.read_excel(expanded_frequencies, "Caucasian", header=1, usecols= data_columns)


## Use range

Generally, instead of defining a long list like this manually

data_columns = [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25]


you can use the range function and then convert to a list

data_columns = list(range(26))


Depending on the application the conversion to list is not necessary.

Also check out the documentation of pandas' read_excel function. The use_cols parameter can take ranges, too, meaning you do not have to pass every single column index but just the start and end column.

You can load all the sheets at once by passing None to the sheet_name parameter:

all_sheets = pd.read_excel(expanded_frequencies, sheet_name=None, header=1, usecols= data_columns)


This outputs an ordered dictionary (search for OrderedDict).

You can then access the individual sheets like this, for example:

all_sheets['Caucasian']


## Use for loops

You should use for loops to avoid repetitions. Now that you have all the dataframes in one container variable (all_sheets) this has become a lot easier. For example the following block of code

caucasian_freq = caucasian_freq.truncate(after=caucasian_freq.loc[caucasian_freq['Allele'] == 'Allele'].index[0]-1)
swh_freq = swh_freq.truncate(after=swh_freq.loc[swh_freq['Allele'] == 'Allele'].index[0]-1)
seh_freq = seh_freq.truncate(after=seh_freq.loc[seh_freq['Allele'] == 'Allele'].index[0]-1)
agg_aa_freq = agg_aa_freq.truncate(after=agg_aa_freq.loc[agg_aa_freq['Allele'] == 'Allele'].index[0]-1)
aa_freq = aa_freq.truncate(after=aa_freq.loc[aa_freq['Allele'] == 'Allele'].index[0]-1)
bah_freq = bah_freq.truncate(after=bah_freq.loc[bah_freq['Allele'] == 'Allele'].index[0]-1)
jam_freq = jam_freq.truncate(after=jam_freq.loc[jam_freq['Allele'] == 'Allele'].index[0]-1)
trin_freq = trin_freq.truncate(after=trin_freq.loc[trin_freq['Allele'] == 'Allele'].index[0]-1)
cham_freq = cham_freq.truncate(after=cham_freq.loc[cham_freq['Allele'] == 'Allele'].index[0]-1)
fili_freq = fili_freq.truncate(after=fili_freq.loc[fili_freq['Allele'] == 'Allele'].index[0]-1)
apa_freq = apa_freq.truncate(after=apa_freq.loc[apa_freq['Allele'] == 'Allele'].index[0]-1)
nav_freq = nav_freq.truncate(after=nav_freq.loc[nav_freq['Allele'] == 'Allele'].index[0]-1)


can be replaced by a for loop like this:

for population in all_sheets:
current_sheet = all_sheets[population]
truncation_index = current_sheet.loc[current_sheet['Allele'] == 'Allele'].index[0]-1
all_sheets[population] = current_sheet.truncate(after=truncation_index)


This improves both readability and maintainability. The same should be done for the fillna operation in your code, I would put it in the same for loop. Also, I introduced an intermediate variable truncation_index to make things more readable.

Edit: Please note that the name all_sheets, that I used here, is not optimal when we are writing actual programs (instead of just examples). In my opinion, telling names like allele_frequencies would be preferable

I hope this helps.

• You are welcome. I made some small edits to my answer. Aug 11 '20 at 10:31