What is the fastest performance tuple for large data sets in python?

Question

Right now, I'm basically running through an excel sheet.

I have about 20 names and then I have 50k total values that match to one of those 20 names, so the excel sheet is 50k rows long, column B showing any random value, and column A showing one of the 20 names.

I'm trying to get a string for each of the names that show all of the values.

Name A: 123,244,123,523,123,5523,12505,142... etc etc. 
Name B: 123,244,123,523,123,5523,12505,142... etc etc. 

Right now, I created a dictionary that runs through the excel sheet, checks if the name is all ready in the dictionary, if it is, then it does a

strA = strA + "," + foundValue

Then it inserts strA back into the dictionary for that particular name. If the name doesn't exist, it creates that dictionary key and then adds that value to it.

Now, this was working all well at first.. but it's been about 15 or 20 mins and it is only on 5k values added to the dictionary so far and it seems to get slower as time goes on and it keeps running.

I wonder if there is a better way to do this or faster way to do this. I was thinking of building new dictionaries every 1k values and then combine them all together at the end.. but that would be 50 dictionaries total and it sounds complicated.. although maybe not.. I'm not sure, maybe it could work better that way, this seems to not work.

I DO need the string that shows each value with a comma between each value. That is why I am doing the string thing right now.

Answer 1

There are a number of things that are likely causing your program to run slowly.

String concatenation in python can be extremely inefficient when used with large strings.

Strings in Python are immutable. This fact frequently sneaks up and bites novice Python programmers on the rump. Immutability confers some advantages and disadvantages. In the plus column, strings can be used as keys in dictionaries and individual copies can be shared among multiple variable bindings. (Python automatically shares one- and two-character strings.) In the minus column, you can't say something like, "change all the 'a's to 'b's" in any given string. Instead, you have to create a new string with the desired properties. This continual copying can lead to significant inefficiencies in Python programs.

Considering each string in your example could contain thousands of characters, each time you do a concatenation, python has to copy that giant string into memory to create a new object.

This would be much more efficient:

strings = []
strings.append('string')
strings.append('other_string')
...
','.join(strings)

In your case, instead of each dictionary key storing a massive string, it should store a list, and you would just append each match to the list, and only at the very end would you do a string concatenation using str.join.

In addition, printing to stdout is also notoriously slow. If you're printing to stdout on each iteration of your massive 50,000 item loop, each iteration is being held up by the unbuffered write to stdout. Consider only printing every nth iteration, or perhaps writing to a file instead (file writes are normally buffered) and then tailing the file from another terminal.

Answer 2

This answer is based on OP's answer to my comment. I asked what he would do with the dict, suggesting that maybe he doesn't need to build it in the first place. @simon replies:

i add it to an excel sheet, so I take the KEY, which is the name, and put it in A1, then I take the VALUE, which is 1345,345,135,346,3451,35.. etc etc, and put that into A2. then I do the rest of my programming with that information...... but i need those values seperated by commas and acessible inside that excel sheet like that!

So it looks like the dict doesn't have to be built after all. Here is an alternative: for each name, create a file, and store those files in a dict:

files = {}
name = 'John'  # let's say
if name not in files:
    files[name] = open(name, 'w')

Then when you loop over the 50k-row excel, you do something like this (pseudo-code):

for row in 50k_rows:
    name, value_string = rows.split()  # or whatever
    file = files[name]
    file.write(value_string + ',')  # if already ends with ',', no need to add

Since your value_string is already comma separated, your file will be csv-like without any further tweaking on your part (except maybe you want to strip the last trailing comma after you're done). Then when you need the values, say, of John, just value = open('John').read().

Now I've never worked with 50k-row excels, but I'd be very surprised if this is not quite a bit faster than what you currently have. Having persistent data is also (well, maybe) a plus.

---

EDIT:

Above is a memory-oriented solution. Writing to files is much slower than appending to lists (but probably still faster than recreating many large strings). But if the lists are huge (which seems likely) and you run into a memory problem (not saying you will), you can try the file approach.

An alternative, similar to lists in performance (at least for the toy test I tried) is to use StringIO:

from io import StringIO  # python 2: import StringIO import StringIO

string_ios = {'John': StringIO()}  # a dict to store StringIO objects
for value in ['ab', 'cd', 'ef']:
    string_ios['John'].write(value + ',')
print(string_ios['John'].getvalue())

This will output 'ab,cd,ef,'

Answer 3

Instead of building a string that looks like a list, use an actual list and make the string representation you want out of it when you are done.

Answer 4

Depending on how you have read the excel file, but let's say that lines are read as delimiter-separated tuples or something:

d = {}
for name, foundValue in line_tuples:
    try:
        d[name].append(foundValue)
    except KeyError:
        d[name] = [foundValue]
d = {k: ",".join(v) for k, v in d.items()}

Alternatively using pandas:

import pandas as pd
df = pd.read_excel("some_excel_file.xlsx")
d = df.groupby("A")["B"].apply(lambda x: ",".join(x)).to_dict()

Answer 5

The proper way is to collect in lists and join at the end, but if for some reason you want to use strings, you could speed up the string extensions. Pop the string out of the dict so that there's only one reference to it and thus the optimization can kick in.

Demo:

>>> timeit('s = d.pop(k); s = s + "y"; d[k] = s', 'k = "x"; d = {k: ""}')
0.8417842664330237

>>> timeit('s = d[k];     s = s + "y"; d[k] = s', 'k = "x"; d = {k: ""}')
294.2475278390723