Analyze very large sets of engineering data from Excel files

I am an electrical power engineer with some programming skills. My boss asked me to make a program which could analyze very large data, make some calculations and give the result.

The task looks like this:

1. I have an Excel file with a list of objects (Number of object, Name, Power, Switched on state decimal fraction, Switched off state decimal fraction).
2. The task was to compose groups. Each group must be unique, and there should be groups that contain starting from 1 element until the maximum possible number of elements. The power of every element must be summed.

I solved it like this:

Program generates Excel files starting from list of 1 element group, till N element group. In each file, there is a list of each group’s elements names and sum of all objects powers.

3. The total power of each group in each file must be compared to one another and must be done some calculations. The result will be some percentage for each power. I save this file separately as an final result file.

That was general description of my task and how I handle it. My program works, but it needs days, if not weeks to give the result. I can't use RAM because of very large information, so I do every operation on hard disk. I don't know how to speed up my program. Do you have any idea how to solve my problem, or another approach I can use?

coreInput = {}

debug = False

import itertools, os, time, sys
from math import factorial

title = raw_input("Enter input file title: ")

fileName = os.path.dirname(os.path.realpath(__file__)) + "\\" + title

numGens = input("Maximum number of outaged generations (enter 0 for calculation all of them): ")
print ""

totalLines = 0

coreFile  = open(fileName)
coreFile.close()

for i in range(0, len(coreContent)):
coreContent[i] = coreContent[i].replace("\n", "")
coreInput[i] = coreContent[i].split(",")
'''
0  1              2    3    4
-----------------------------
N, NAME,    P,   A,   U
1, *xx_1*, 260,0.91,0.09
...
-----------------------------
'''
del coreInput
if coreInput[len(coreInput)] == "":
del coreInput[len(coreInput)]

if debug:
for xx in coreInput:
print coreInput[xx]

elLimit = len(coreInput)

totalU = 1.00

for i in range(1, len(coreInput)+1):
totalU = totalU * float(coreInput[i])

if debug:
print "totalU = " + str(totalU)

Generators = []

for i in range(1, elLimit + 1):
Generators.append(i)

fileLocation = os.path.dirname(os.path.realpath(__file__))
saveFolder = "output"

if not os.path.exists(fileLocation+"\\"+saveFolder):
os.makedirs(fileLocation+"\\"+saveFolder)

if numGens == 0:
numGens = elLimit

for elNum in range (1, numGens + 1):
fileTitle = str(elNum) + " Generator.csv"
filePath = fileLocation+"\\"+saveFolder+"\\"+fileTitle

fileObject = open(filePath, "w")

#for i2 in range(1, elNum+1):
#    fileObject.write("N"+str(i2)+",")

#for i2 in range(1, elNum+1):
#    fileObject.write("Gen "+str(i2)+",")

fileObject.write("total P,")
fileObject.write("statistic\n")

AllCombinations = itertools.combinations(Generators, elNum)

progressTotal = factorial(elLimit)/(factorial(elNum)*factorial(elLimit-elNum))
progressStatus = 0.0

iStatus = 0

print str(elNum) + " Gen statistics in progress..."

for i in AllCombinations:

iStatus = iStatus + 1
curTotalP = 0.0
curTotalA = 1
curTotalU = 1

for i2 in range(0, len(i)):
if debug:
print " "
print " i2 = " + str(i2)
curTotalP = curTotalP + float(coreInput[i[i2]])
trash = curTotalA
curTotalA = curTotalA * float(coreInput[i[i2]])
if debug:
print "curTotalA = " + str(trash) + " * " + str(coreInput[i[i2]])
trash = curTotalU
curTotalU = curTotalU * float(coreInput[i[i2]])
if debug:
print "curTotalU = " + str(trash) + " * " + str(coreInput[i[i2]])

#for i2 in range(0, elNum):
#    fileObject.write(coreInput[i[i2]]+",")

#for i2 in range(0, elNum):
#    fileObject.write(coreInput[i[i2]]+",")

fileObject.write(str(curTotalP)+",")
result = curTotalA * totalU / curTotalU
if debug:
print result, " = ", curTotalA, " * ", totalU, " / ", curTotalU
fileObject.write(str(result)+"\n")

totalLines = totalLines + 1

progressStatus = 100 * iStatus / progressTotal

sys.stdout.write('%3d%%\r' % progressStatus)

for i2 in range(1, elNum*2+3):
fileObject.write(",")
fileObject.write("\n")
fileObject.close()
print "     " + str(elNum) + " Gen statistics Finished"
print("\nGenerated Files: ")

onlyfiles = [ f for f in os.listdir(fileLocation+"\\"+saveFolder+"\\") if os.path.isfile(os.path.join(fileLocation+"\\"+saveFolder+"\\",f)) ]

for onlyfile in onlyfiles:
size = ""
size = "   {:,.0f}".format(os.stat(fileLocation+"\\"+saveFolder+"\\" + onlyfile).st_size/(1024)) + " KB"
print onlyfile + "   " + size

#raw_input ("Press ENTER to continue...")

print "\nAnalyzing results and generating final output file \n"

powers = {}
xSaveFile = open(fileLocation + "\Result.csv", "w")
xSaveFile.write("Power,Statistics\n")

floatPercent = 0.0
floatStep = 100.0 / len(onlyfiles)
sys.stdout.write('%3d%%\r' % int(floatPercent))

curLine = 0

for onlyfile in onlyfiles:

if debug:
print "opening: " + fileLocation + "\\" + saveFolder + "\\" + onlyfile
#i = 1
with open(fileLocation+"\\"+saveFolder+"\\"+onlyfile) as f:
print "Opening file: " + str(onlyfile)
for xLine in f:
curLine = curLine + 1
sys.stdout.write('%3d%%\r' % int(curLine*100/totalLines))
if debug:
print "*** xLine = " + xLine
xContent = xLine.replace("\n", "")
statistic = 0.0
#if xContent.startswith(","):
#    break
if not xContent.startswith("t") and not xContent.startswith(","):
xContentArr = xContent.split(",")

isNotInPower = True

if len(powers)>0:
for power in powers:
if powers[power] == xContentArr:
if debug:
print "powers[" + str(power) + "] == " + str(xContentArr)
isNotInPower = False
#break

if isNotInPower:
powers[len(powers)] = xContentArr
for onlyfile2 in onlyfiles:

if debug:
print ""
print "opening onlyfile2: " + onlyfile2
with open(fileLocation+"\\"+saveFolder+"\\"+onlyfile2) as z:
for zLine in z:
zContent = zLine.replace("\n", "")
#if zContent.startswith(","):
#    break
if not zContent.startswith("t") and not zContent.startswith(","):
if debug:
print zContent
zContentArr = zContent.split(",")
if float(zContentArr) >= float(xContentArr):
statistic = statistic + float(zContentArr)
if debug:
print "zContentArr = " + zContentArr + "  xContentArr = " + xContentArr +  "    statistic = " + str(statistic)
print ""
else:
if debug:
print "BAD! zContentArr = " + zContentArr + "  xContentArr = " + xContentArr +  "    statistic = " + str(statistic)

if debug:
print str(powers[len(powers)-1]) + "," + str(statistic) + "\n"
xSaveFile.write(str(powers[len(powers)-1]) + "," + str(statistic) + "\n")
if debug:
print "SAVED!"

#i=i+1

#xFile.close()

xSaveFile.close()
print "\n \nProces has finished successfuly"
raw_input ("Press ENTER to exit...")

Input looks like this:

N NAME    P   A   U
1 *x1*    260 0.91    0.09
2 *x2*    260 0.92    0.08
3 *x3*    260 0.88    0.12
4 *x4*    260 0.95    0.05
5 *x5*    260 0.81    0.19
6 *y1*    73.3    0.88    0.12
7 *y2*    73.3    0.9 0.1
8 *y3*    73.3    0.95    0.05
9 *z1*    8   0.951   0.049
10    *z2*    8   0.952   0.048
...

The output files of second stage looks like this:

total P   statistic
520   3.84E-96
520   2.45E-96
520   6.34E-96
520   1.42E-96
333.3 2.45E-96
333.3 3.00E-96
333.3 6.34E-96
268   6.48E-96
268   6.62E-96
264   6.77E-96
264   6.92E-96
264   7.08E-96
271   7.25E-96
271   7.43E-96
271   7.61E-96
271   7.81E-96
...

The final result file:

Power Statistics
260   1.30E-87
73.3  4.31E-87
8 8.42E-87
4 8.42E-87
11    8.42E-87
7.6   8.42E-87
12    8.42E-87
23    8.41E-87
37    8.04E-87
20    8.42E-87
9.6   8.42E-87
...

At the end, the final result file powers are unique.

• How big are we talking about? – TheBlackCat Sep 1 '15 at 11:22
• I don't have enough domain knowledge to understand what the code calculates, but if you need to locate the place in code which takes longest to execute, Python has a profiler: docs.python.org/2/library/profile.html. If your problem turns out to be lots of I/O, from what I could understand, you might want to look into Python Hadoop API: pypi.python.org/pypi/hdfs (on the other hand, it may be too complicated...) – wvxvw Sep 1 '15 at 13:18
• How big are the initial csv files, in terms of number of rows? – TheBlackCat Sep 1 '15 at 14:39
• Why not use numpy/scipy? – sebix Sep 1 '15 at 19:29

Some suggestions:

1. Follow the pep8 style guide
2. Use from __future__ import division, print_function. division in particular is important for numerical analysis like this.
3. You should be using the pandas python package for this. It is designed for this sort of thing.
4. Don't save intermediate or final results in csv files if you can avoid it, they are very slow. At least you should write to a csv file after everything is done.
5. It is better to break your code into functions
6. For checking what values you have already done, it is much faster to us a set with in.
7. Use os.path.join for joining paths.
8. You can use something like 'test %s' % 5, or better yet 'test {}'.format(5), to put numbers in strings, which is much cleaner than something like 'test ' + str(5).
9. Always use with for opening files.
10. You can use list(range(a, b, c)) to convert a list directly to a range without needing to do a loop.
11. You can use a += 1 to increment numbers, rather than a = a + 1.
12. print writes to stdout by default, so I don't think you need to specially call sys.stdout.write.
13. If you are going to loop over an index you won't use, it is generally considered good from to use _ as a throwaway variable, like for _ in range(x).
14. You can use continue to skip the rest of the current iteration of a for loop, rather than wrapping most of the loop in an if test.
15. For testing if a string starts with a letter you can just do mystr == 't'

So here is how I would write it (ignoring the functions part). I am putting intermediate values inside one HDF5 file, and then saving the final result to a csv file at the very end:

from __future__ import division, print_function

import os
from functools import partial
from itertools import chain, combinations
from math import factorial

import pandas as pd

debug = True

# Get paths
file_location = os.path.dirname(os.path.realpath(__file__))
save_folder = "output"
outpath = os.path.join(file_location, save_folder)
if not os.path.exists(outpath):
os.makedirs(outpath)

# Get the path of the file to store the results
# All the results will be stored in this file
outfile = os.path.join(outpath, 'data.h5')
if os.path.exists(outfile):
os.remove(outfile)

#title = raw_input("Enter input file title: ")
title = 'test.csv'
filename = os.path.join(file_location, title)

#n_gens = input("Maximum number of outaged generations (enter 0 for calculation all of them): ")
n_gens = 0
print("")

# Read the input file and save it to our output file
core_input.to_hdf(outfile, 'core_input')

if debug:
print(core_input)

# We don't need the name column anymore
del core_input['NAME']

# Pandas makes this easy
total_u = core_input.U.prod()

n_rows = len(core_input)

if debug:
print("total_u =", total_u)

if n_gens == 0:
n_gens = n_rows

def ncr(n, r):
return factorial(n)/(factorial(r)*factorial(n-r))

total_lines = 0
cnames = ['Total_Power', 'Statistics']
with pd.HDFStore(outfile) as store:
for el_num in range (1, n_gens + 1):
print(el_num, "Gen statistics in progress...")

progress_total = ncr(n_rows, el_num)

# We get all the combinations of row numbers, then use a generator to
# get those rows out of core_input as-needed
ind_combs = combinations(core_input.index, el_num)
dfs = (core_input.loc[inds, :] for inds in ind_combs)

# Get the columns, and put empty data in so we can append later
keyname = 'generators/el_num_{}'.format(el_num)
for i, idf in enumerate(dfs):
ptot = idf.P.sum()
res = idf.A.prod()*total_u/idf.U.prod()
idf2 = pd.DataFrame([[ptot, res]], columns=cnames, index=[i])
store.append(keyname, idf2, data_columns=['Total_Power'])
progressStatus = 100*i/progress_total
print('%3d%%' % progressStatus)

store.create_table_index(keyname)
total_lines += i+1
print("     ", el_num, "Gen statistics Finished")

print("\nAnalyzing results and generating final output file \n")

with pd.HDFStore(outfile) as store:
# Get the columns, and put empty data in so we can append later
keyname = 'results_final'
cnames = ['Power', 'Statistics']

# We use this to keep track of the P values that were already done
doneps = set()

# Get the names of the data stored in the HDF5 file
keypaths = (key for key in store.keys() if '/generators/' in key)

# Get the rows from the previous results
p_select = partial(store.select, columns=['Total_Power'],
iterator=True, chunksize=1)
rows = (p_select(key) for key in keypaths)
rows = chain.from_iterable(rows)

for i, row in enumerate(rows):
ptot = row.iloc[0, 0]
if ptot in doneps:
continue

# We use this to filter out values that don't fit your criteria
whereterm  = pd.Term('Total_Power', '>', ptot)
res = 0.0
for key in keypaths:
res += store.select(key, columns=['Statistics'],
where=whereterm).sum().iloc
print(ptot, res)

idf = pd.DataFrame([[ptot, res]], columns=cnames, index=[i])
store.append(keyname, idf)

# Save the final results to a csv file

print("\n\nProcess has finished successfuly")

I'm not sure if this is enough of a review to meet the criteria for this site but thought I would point you to a best practice that will save you endless grief if you are going to work with large and possibly incorrectly formatted data.

When generating a CSV, always use a well tested CSV library. It's surprising the number of corner cases that can come up and the odd junk that makes it's way into spreadsheets. @TheBlackCat's excellent answer used the excellent CSV generation facilities in the pandas library, but even the basic csv module will do the trick.

With or without a CSV library, I would furthermore suggest that you use quoted fields to prevent an errant tab/comma from breaking your output. So the output would look like this:

"Power"   "Statistics"
"260" "1.30E-87"
"73.3"    "4.31E-87"

This can be quickly hacked in to your current script, although learning pandas should be your goal eventually.

I have a couple notes about performance.

Your for loop for replacing and splitting the coreContent list can be sped up with a list comprehension instead of a loop (see here). List comprehensions are one line for loops that run more efficiently. You use one later, you should try and use them as often as possible. Also note, assigning coreInput[i] makes it look as if you're assigning to a list, not a dict. Be careful about confusing syntax that might mislead a user.

coreContent = [content.replace('\n', '') for content in coreContent[i]]
coreInput = {i: content.split() for i, content in enumerate(coreContent)}

Also in case you're unfamiliar, enumerate is used in for loops to return the value of each list item with its index number. That way you can get i the index as well as content the items in the list simultaneously. Though if you don't need coreInput to be a dict I would recommend a list instead as that's a more efficient data type. From what I can see in your script you're actually wasting a lot of time trying to deal with it instead of just sticking to a list. And I suspect that you would save a lot and make clearer code by switching to a list and using list comprehensions and for item in coreInput in your for loops.

This part is a bit unclear, consider adding comments. But this use of len(coreInput) is a prime example of where a list would serve you better, as you could then just call coreInput[-1] to get the last element of the list and avoid 2 mildly expensive len calls.

del coreInput
if coreInput[len(coreInput)] == "":
del coreInput[len(coreInput)]

factorial is an expensive function and you're calling it on a constant number each time in the loop. Just call factorial(elLimit) before your loop and store it so you only need to call two factorials in this line.

progressTotal = factorial(elLimit)/(factorial(elNum)*factorial(elLimit-elNum))

Some minor Python style and usability notes. You should put all your import statements at the top of the file, before declaring variables.

import itertools, os, time, sys
from math import factorial

coreInput = {}
debug = False

Also it's not recommended to use input, instead you could use raw_input again and just parse the result as an int. If you set up a while loop and a try except to catch the error then you'll ensure it's always an integer.

while True:
numGens = raw_input("Maximum number of outaged generations (enter 0 for calculating all of them): ")
try:
numGens = int(numGens)
break
except ValueError:
print numGens + " is not a valid integer."

Instead of using open() there's a syntax called with that will open a file object and always ensure it's safely closed even if errors occur. It will prevent files being corrupted and is always recommended. You do use it later but you should use it in almost all cases.

with open(fileName) as coreFile: